Mysql索引

最近一周的工作都集中在慢sql的治理上，大部分都是基于索引进行优化，所以做了下述的总结。 1. explain 介绍explain（执行打算），应用 explain 关键字能够模仿优化器执行sql查问语句，从而晓得 MySQL 是如何解决sql语句。explain 次要用于剖析查问语句或表构造的性能瓶颈。 通过 explain + sql 语句能够晓得如下内容： 表的读取程序。（对应id）数据读取操作的操作类型。（对应select_type）哪些索引能够应用。（对应possible_keys）哪些索引被理论应用。（对应key）表间接的援用。（对应ref）每张表有多少行被优化器查问。（对应rows）explain 执行打算蕴含字段信息如下：别离是 id、select_type、table、partitions、type、possible_keys、key、key_len、ref、rows、filtered、extra 12个字段。每个字段对应的介绍如下。能够先建几张表举例。上面建表各自举例子： xCREATE TABLE `blog` ( `blog_id` int NOT NULL AUTO_INCREMENT COMMENT '惟一博文id--主键', `blog_title` varchar(255) NOT NULL COMMENT '博文题目', `blog_body` text NOT NULL COMMENT '博文内容', `blog_time` datetime NOT NULL COMMENT '博文公布工夫', `update_time` timestamp NULL DEFAULT NULL ON UPDATE CURRENT_TIMESTAMP, `blog_state` int NOT NULL COMMENT '博文状态--0 删除 1失常', `user_id` int NOT NULL COMMENT '用户id', PRIMARY KEY (`blog_id`)) ENGINE=InnoDB AUTO_INCREMENT=17 DEFAULT CHARSET=utf8CREATE TABLE `user` ( `user_id` int NOT NULL AUTO_INCREMENT COMMENT '用户惟一id--主键', `user_name` varchar(30) NOT NULL COMMENT '用户名--不能反复', `user_password` varchar(255) NOT NULL COMMENT '用户明码', PRIMARY KEY (`user_id`), KEY `name` (`user_name`)) ENGINE=InnoDB AUTO_INCREMENT=17 DEFAULT CHARSET=utf8CREATE TABLE `discuss` ( `discuss_id` int NOT NULL AUTO_INCREMENT COMMENT '评论惟一id', `discuss_body` varchar(255) NOT NULL COMMENT '评论内容', `discuss_time` datetime NOT NULL COMMENT '评论工夫', `user_id` int NOT NULL COMMENT '用户id', `blog_id` int NOT NULL COMMENT '博文id', PRIMARY KEY (`discuss_id`)) ENGINE=InnoDB AUTO_INCREMENT=61 DEFAULT CHARSET=utf81. id示意查问中执行select子句或者操作表的程序，id的值越大，代表优先级越高，越先执行。针对上面sql例子： ...

并不是索引越多越好，索引是一种以空间换取工夫的形式，所以建设索引是要耗费肯定的空间，况且在索引的保护上也会耗费资源。本文首发我的集体博客mysql索引不失效 这里有张用户浏览商品表，建表语句： CREATE TABLE `product_view` ( `id` int(11) NOT NULL AUTO_INCREMENT, `user_id` int(11) NOT NULL, `product_id` int(11) NOT NULL, `server_id` int(11) NOT NULL, `duration` int(11) NOT NULL, `times` varchar(11) NOT NULL, `time` datetime NOT NULL, PRIMARY KEY (`id`), KEY `time` (`time`), KEY `user_product` (`user_id`,`product_id`) USING BTREE, KEY `times` (`times`) USING BTREE) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;能够看出目前这张表是有3个索引的： 我往这张表外面导入了10万多条记录。 mysql不走索引的状况1、like查问以“%”结尾(如果结尾、后果都有“%”，也不会应用索引，走的是全表扫描)； 我这里用了列出了4种状况，发现都是全表扫描，不走索引的。可能因为times取值不够离散，索引没有走索引。 2、or语句前后没有同时应用索引; 首先看看or语句前后同时应用索引： 查问走索引了。 再看看or语句前后没有同时应用索引，product_id是索引字段，server_id不是索引字段： 是没有走索引的。 3、组合索引中不是应用第一列索引；（不合乎最左匹配准则） 这张表索引为工夫time和一个组合索引： ...

最近收到一个 Sentry 报警，如下 SQL 查问超时了。 select * from order_info where uid = 5837661 order by id asc limit 1执行 show create table order_info 发现这个表其实是有加索引的： CREATE TABLE `order_info` ( `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT, `uid` int(11) unsigned, `order_status` tinyint(3) DEFAULT NULL, ... 省略其它字段和索引 PRIMARY KEY (`id`), KEY `idx_uid_stat` (`uid`,`order_status`),) ENGINE=InnoDB DEFAULT CHARSET=utf8实践上执行上述 SQL 会命中 idx_uid_stat 这个索引，但理论执行 explain 查看 explain select * from order_info where uid = 5837661 order by id asc limit 1能够看到它的 possible_keys（此 SQL 可能波及到的索引） 是 idx_uid_stat，但实际上（key）用的却是全表扫描。 ...

引言 内容为慕课网的《高并发 高性能 高可用 MySQL 实战》视频的学习笔记内容和集体整顿扩大之后的笔记，这一节的内容是对于InnoDb的存储构造进阶理解，同时介绍为什么会应用B+索引作为最终数据结构，然而实际上InnoDb在具体实现中也并没有齐全遵循B+的格局，而是在外部做了很多“手脚”，这也是所谓实践和实际之间的差别。 如果内容比拟难，能够追随《Mysql是怎么样运行》集体读书笔记专栏补补课，集体也在学习和同步更新中。 地址如下：https://juejin.cn/column/7024...。 索引组织表 InnoDb 的所有表都是索引组织表，索引组织表有如下的定义： 不是一种“组织表”，而是由“索引”组织的表，索引即数据数据即索引，InnoDb中表默认都会主键程序寄存，同时依照肯定的规定排序，默认的索引组织模式被称为聚簇索引。 什么是索引？ 索引能够简略了解为目录，相似于咱们书中的目录页，帮咱们疾速定位具体的内容，对于数据库某一列或者多列进行预排序的数据结构，留神这是一种数据结构目标是为了放慢数组的搜寻速度。 然而索引也有问题，那就是目录自身也须要占用存储空间并且随着数据的收缩而收缩，同时如果索引应用的不失当也会呈现问题，比方如果咱们的目录索引的内容全都是截然不同的会呈现“索引生效”问题，此时索引成果大打折扣，不如间接搜寻数据。 主键定义和主键索引 在Mysql的Inndb存储引擎中，应用的主键索引也被称为聚簇索引： InnoDb 的存储引擎表中每张表必须有一个主键，表中有一个非空惟一索引即为主键。如果存在多个非空惟一索引并且没有定义主键，抉择第一个定义的索引，若所有条件不满足则InnoDb在数据行中主动创立一个6个字节的指针暗藏列作为主键，并且这个主键外部是自增的使得记录能够依照程序进行存储。 上面用视频中的案例举例探讨的上面这个表主键是什么？ 从下面的截图能够看到，字段a没有定义惟一索引，尽管它是非空的然而并不是惟一的所以不是主键，b尽管是最先定义的，然而他不是非空所以也不能作为索引，而d和 c尽管都是惟一索引并且都是非空列，依据多个非空索引取第一个定义索引为主键的规定，最终主键为字段d。 B+树索引 B+树的索引构造是InnoDb的根底构造，上面是传统的B+树的构造： Btree 应用B+树作为索引的数据结构。B+树的高度为2-4层，查找数据非常快。B+树索引将非叶子节点所谓索引节点，叶子节点为数据节点，数据节点之间用链表串联实现优化范畴查问。Inndo的B+树和传统B+树的区别如下： InnoDb底层参考的是b+ 树，然而其实不完全相同，节点被称之为数据页和索引页，然而实际上索引页数据页除了数据类型不同基本一致，也就是索引即数据，数据即索引，索引它分为聚簇索引和辅助索引，聚簇索引最大特点是寄存键是主键ID，而主键ID依据肯定的规定生成或者在建表的时候指定，然而肯定会有一个主键索引，也就说一个表肯定存在主键。而辅助索引应用的是主键为索引字段的值，数值寄存的是索引主键。同层的数据页之间应用的是双向链表，索引页也是应用双向链表，这和B+树的数据结构是不一样的，传统B+ 树只在最底层的叶子节点为链表的设计。聚簇索引 聚簇索引指的是依据表的主键构建一个B+ 树。叶子节点间接寄存行数据而不是放指针，然而实际上叶子节点自身也是数据页只不过寄存的是指针而已。 上面的案例图仅仅为最毛糙的角度观察mysql的数据页设计，理论内容要远比这张图简单很多： 聚簇索引的特点非叶子节点存储的是索引，叶子节点则为数据，从左到右排序，在页决裂的时候，会把主键较大的值挪动到对应的数据页。索引页之间应用链表进行连贯，而叶子节点理论的数据存储区域，对立应用链条表进行串联。所以能够发现除开最顶层，所有的层级页和页之间是由链表之间链接的。每一个数据页蕴含infimum数据行代表以后数据页的第一个节点也就是最小值，supermum代表最初一个节点也就是最大值，这两个“行记录”是Mysql设计者的一个小把戏，目标是不便数据的查找和不同数据页之间的串联，也就是说每一个数据页默认至多有两个“虚构”数据行。所有的数据页号会组成一个页目录，依照最大数据的数据页号进行排序，页目录外面从小到大寄存了主键的id值，通过值找到对应的数据页内容，用于疾速定位数据所在的数据页。Innodb 默认为主键索引也就是聚簇索引。为什么要应用从大到小的程序进行排序？ 其实次要是为了应用二分查找办法疾速定位和查找数据页，进步查找的效率。留神因为晚期Mysql版本中的索引设计只能依照升序的形式进行排列，导致聚簇索引少数为升序的索引，在8.0的版本中失去优化。 辅助索引 辅助索引的存在模式： 和主键索引的设计一样，然而key寄存的是索引字段的值，值是主键值。辅助索引依据建设的索引除联结索引的状况外均为有几个索引建设几颗B+ 树。辅助索引相当于一颗新的B+ 树。 主键索引： 主键索引也叫聚簇索引，因为底层应用了B+ 树的设计构造，所以Mysql必然有主键并且以主键作为索引的模式。主键索引指的是键为主键，值为数据一种 索引模式。一旦创立表则零碎默认会存在一颗以主键索引的B+ 树。回表是什么？ 当辅助索引进行查问的时候因为查问的后果为主键的值，所以须要依据主键的值再去聚簇索引依据二分法查找一遍，这时候等于须要再查一遍聚簇索引，实质上是查了两次B+ 树，所以叫回表。 上面的示意图是一次回表操作： 假如咱们须要搜寻值为5的数据，首先会在二级索引通过二分遍历“槽”的模式找到具体所在的数据行，这个数据行保留索引值之外还存储了主键的值，所以这里须要拿到主键的值回到聚簇索引中找到理论存储的行记录。 然而如果查找条件和查找列都为索引值实际上会应用“笼罩索引”的查找形式，不须要回表操作。 索引算法 对于刚刚接触B+树的同学看到这些数据结构可能会懵圈，同时也不分明为什么要设计这么个简单的玩意，所以在课程中引入了各种数据结构来介绍为什么最终抉择了B+树的构造，上面咱们来简略比照各种常见的数据结构来理解为什么最初抉择了B+ 树这种数据结构。 ...

springboot实战电商我的项目mall4j （https://gitee.com/gz-yami/mall4j) java商城零碎源码 mysql-索引（InnoDB）InnoDB 会把存储的数据划分为若干个「页」，以页作为磁盘和内存交互的根本单位，一个页的默认大小为 16KB，它由七局部形成：File Header: 页的通用信息 Page Header: 页的专有信息 Infimum + Supremun: 零碎生产的记录，存储页内最大和最小的记录 User Records: 存储用户记录 Free Space: 页中为应用的空间 Page Directory: 存储页中记录的地位 File Trailer: 校验页是否残缺 // 查看页的大小（单位：字节）show status like 'innodb_page_size';页的数据数据排序次要是依附表的主键id，这也是创立表的时候倡议要创立一个主键，但这并不是强制性，理论创立表的过程中会发现没有指定主键也能胜利的创立一个表，其实用户没有指定主键的时候，InnoDb每一列的进行循环试图逐列去寻找一列所有元素都不反复的作为主键，如果切实找不到InnoDn就会保护一个暗藏列来作为主键。为什么创立表的时候会举荐应用整型的自增的主键？首先数据在页中是以单向链表的形式进行存储，如果应用uuid之类的作为主键，其自身是无序的而数据在页中的寄存是有序的，所以在每次进行插入的数据依据排序规定大概率会在链表的两头，如果这时该页的大小已满16k，就须要依据插入的数据来从新调整后续页的数据；与之相比应用整型的自增的主键就具备很显著的劣势了，因为自增的性质在失常状况下新插入的数据的主键id会大于上一条数据的主键id而直接插入到链表开端的地位，所以新的数据就不须要大规模的去调整已有页的数据。 在InnoDB中，表数据组织形式是主键汇集索引，并且因为一个表只能有一个主键， 所以也只能有一个汇集索引。其余索引的构造则是通过索引键值加主键值组合来惟一确定一条记录，这些数据在逻辑上间断的，但从从物理存储构造上来看，汇集索引的存储并不是间断的。这其中有两点：1、叶子节点中蕴含着列的数据，并且是通过双向链表进行链接的，而页依照主键的程序排序； 2、每个页中的记录也是通过双向链表进行保护的，物理存储上能够同样不依照主键存储。 汇集索引其实就是主键索引，InnoDB中的数据是面向主键索引进行数据存储的。而汇集索引就是依照每张表的主键来结构一棵B+Tree，同时叶子节点中存储的是整张表的行记录信息，也能够将汇集索引的叶子节点称为数据页。因而，汇集索引的这个个性，决定了索引组织表中的数据也是索引的一部分。和B+Tree的数据结构一样，每个数据页都通过一个双向的链表来进行链接。须要着重留神的是，在InnoDB的B+Tree索引数据结构中，只有在叶子节点上寄存的是残缺的每行记录，而在非数据页的索引页中，寄存的仅仅是主键值及指向数据页的偏移量，而不是一个残缺的行记录。主键索引结构图 二级索引结构图 Mysql中的检索分为两种，一种是索引检索，还有一种是全表检索。在有索引时mysql会优先应用索引进行检索，没有创立索引或索引生效时应用全表检索，应用索引进行检索毫无疑问是会比全表检索的速度更快，但数据表每减少一个索引都要开拓出一片空间来存储B+树索引数据结构，如果一张表的数据量很大而符合条件的后果又很少，那么不加索引会引起致命的性能降落。然而也不是什么状况都非得建索引不可，比方一些字段可能就只有两个值，建索引不仅没什么劣势，还会影响到更新速度，这被称为适度索引。主键索引叶子节点中蕴含了列的数据，在进行检索时检索的字段不会导致索引生效，并且在范畴检索时也能应用索引。而二级索引在检索时就须要留神检索的字段防止应用*，尽量只返回须要检索的数据。二级索引还可能通过联结索引来缩小索引创立的数量，能够应在查问时有多个字段总是同时呈现则这些字段就能够作为复合索引,造成索引笼罩能够进步查问的效率。springboot实战电商我的项目mall4j （https://gitee.com/gz-yami/mall4j) java商城零碎源码

建设索引与查问优化关键字：区分度组合索引创立【举荐】建组合索引的时候，区分度最高的在最右边。正例：如果 where a=? and b=?，a 列的简直靠近于惟一值，那么只须要单建 idx_a 索引即可。阐明： 存在非等号和等号混合判断条件时，在建索引时，请把等号条件的列前置。如： where c>? and d=? 那么即便 c 的区分度更高，也必须把 d 放在索引的最前列，即建设组合索引 idx_d_c。含糊查问4. 【强制】页面搜寻严禁左含糊或者全含糊，如果须要请走搜索引擎来解决。阐明：索引文件具备 B-Tree 的最左前缀匹配个性，如果右边的值未确定，那么无奈应用此索引。order by 查问【举荐】 如果有 order by 的场景，请留神利用索引的有序性。 order by 最初的字段是组合索引的一部分，并且放在索引组合程序的最初，避免出现 file_sort 的状况，影响查问性能。正例：where a=? and b=? order by c; 索引：a_b_c反例：索引如果存在范畴查问，那么索引有序性无奈利用，如：WHERE a>10 ORDER BY b; 索引 a_b 无法排序。笼罩索引优化波及概念：回表查问，汇集索引，一般索引笼罩索引介绍InnoDB： 有两大索引，汇集索引和一般索引；汇集索引存储行数据，一般索引存储主键值回表查问，以一般索引查问行的全副数据，必须走两张索引表，先到一般索引获取主键，再到汇集索引获取行数据。笼罩索引，须要查问的数据在索引信息外面曾经全副蕴含，不须要再回表。举例create table user (id int primary key,name varchar(20),sex varchar(5),index(name))engine=innodb; 表user的一般索引name，查问1，只有name信息，不必回表，因为nane曾经在索引中select name from user where name='xxx';查问2，sex信息须要从行记录里获取，须要回表select name,sex from user where name ='xxx'; 查问2的笼罩索引优化，对标user建设索引index(name, sex) ...

转自MySQL索引总结

《MySQL面试小抄》索引考点二面总结我是肥哥，一名不业余的面试官！ 我是囧囧，一名踊跃找工作的小菜鸟！ 囧囧示意：小白面试最怕的就是面试官问的知识点太抽象，本人无奈疾速定位到关键问题点！！！本期次要面试考点 面试官考点之谈谈索引保护过程？页决裂？页合并？面试官考点之简述一下查问时B+树索引搜寻过程？面试官考点之什么是回表？面试官考点之什么是索引笼罩？应用场景？面试官考点之什么状况下会索引生效？面试官考点之哪些状况下，可能会面临索引生效的问题？面试官考点之or走索引和索引生效别离是什么场景？面试官考点之哪些状况下须要创立索引？面试官考点之联结索引之最左前缀准则？面试官考点之索引下推场景？ 面试官考点之谈谈索引保护过程？页决裂？页合并？B+树为了保护索引有序性，在插入删除的时候须要做必要的保护，必要时候可能波及到页决裂，页合并过程！ 首先假如每个叶子节点（数据页或磁盘块）只能存储3条索引和数据记录，如图 状况1、新增行记录，ID=3，此时【数据页1】未满，只须要在data2后新增ID=3的行记录，B+树整体构造不须要进行调整 状况2、新增行记录，ID=8，此时【数据页2】已满，这时候须要申请一个新的数据页，而后移动局部数据过来。这个过程称为页决裂。页决裂过程耗费性能，同时空间利用率也升高了 有决裂就有合并，当相邻两个页因为删除了数据，利用率很低之后，会将数据页做合并。合并的过程，能够认为是决裂过程的逆过程。 当相邻两个页因为删除了数据，利用率很低之后，会将数据页做合并。合并的过程，能够认为是决裂过程的逆过程。 【数据页2】删除了ID=7,ID=8的行记录，此时【数据页2】【数据页3】利用率很低，将进行页合并。 面试官考点之简述一下查问时B+树索引搜寻过程？筹备一张用户表，其中id为主键，age为一般索引 CREATE TABLE `user` ( `id` int(11) PRIMARY KEY, `name` varchar(255) DEFAULT NULL, `age` int(11) DEFAULT NULL KEY `idx_age` (`age`) USING BTREE) ENGINE=InnoDB DEFAULT CHARSET=utf8;select * from user where age=22 简述一下B+树索引搜寻过程？ 假如要查问的记录 id=5,name="张三",age=22MySQL为每个索引别离保护了一棵B+Tree索引树， 主键索引非叶子节点保护了索引键，叶子节点存储行数据； 非主键索引也称为二级索引，非叶子节点存储主键； B+树索引搜寻过程 搜寻条件 age=22，可走idx_age索引，首先加载idx_age索引树，找到age=22的记录，获得id=5 回表搜寻，加载主键索引树，找到id=22的记录，获得整行数据 面试官考点之什么是回表？idx_age二级索引树找到主键id后，回到id主键索引搜寻的过程,就称为回表。 并非所有非主键索引搜寻，都须要进行回表搜寻，也就是上面要说的索引笼罩。 面试官考点之什么是索引笼罩？应用场景？在下面提到的例子中，因为查问后果所须要的数据只在主键索引上有，所以不得不回表。 如果在查问的数据列外面，间接从索引列就能取到想要的后果，就不须要再回表去查，也称之为索引笼罩！ 索引笼罩的长处 能够防止对Innodb主键索引的二次查问能够防止MyISAM表进行零碎调用能够优化缓存,缩小磁盘IO操作批改一下上述栗子，满足索引笼罩条件？select id, age from user where age=22查问的信息，id，age都能够间接在idx_age 索引树中获取，不须要回表搜寻。 因为笼罩索引能够缩小树的搜寻次数，显著晋升查问性能，所以应用笼罩索引是一个罕用的性能优化伎俩。 索引是一把双刃剑，提供疾速排序搜寻的同时，索引字段的保护也是要付出相应的代价的。 因而，在建设冗余索引来反对笼罩索引时就须要衡量思考了 ...

数据库为什么须要索引呢？咱们都是晓得数据库的数据都是存储在磁盘上的，当咱们程序启动起来的时候，就相当于一个过程运行在了机器的内存当中。所以当咱们程序要查问数据时，必须要从内存进去到磁盘外面去查找数据，而后将数据写回到内存当中。然而磁盘的io效率是远不如内存的，所有查找数据的快慢间接影响程序运行的效率。而数据库加索引的次要目标就是为了应用一种适合的数据结构，能够使得查问数据的效率变高，缩小磁盘io的次数，晋升数据查找的速率，而不再是愣头青式的全局遍历。 那索引为啥要用B+Tree的数据结构呢？如果咱们简略的想的话，想要疾速的查找到数据，感觉hash表是最快的，依据key，hash到某个槽位上，间接一次查找就能够精确的找到数据的地位，这多快呀。然而咱们在做业务时，往往只须要一条的数据需要很少，大部分的需要都是依据肯定的条件查问一部分的数据，这个时候hash显示不是很适合。 咱们再思考树，比方二叉树，均衡二叉树，红黑树，B树等，他们都是二分查找，找数也快，然而不论是均衡二叉树还是优化后的红黑树，说到底他们都是二叉树，当节点多了的时候，它们的高度就会高呀，我找一个数据。根节点不是，那就找下一层，下一层还没有我就再去找下一层，这样造成的结果就是我找一个数据可能要找好几次，而每一次都是执行了一次磁盘的io，而咱们的索引的目标就是要缩小磁盘io呀，这样设计可不行。那咱们是不是把高度变矮就能够了呢？所以咱们再思考下B树。首先简略介绍下B树的数据结构：首先看看B树的定义。 每个节点最多有m-1个关键字（能够存有的键值对）。根节点起码能够只有1个关键字。非根节点至多有m/2关键字。每个节点中的关键字都依照从小到大的顺序排列，每个关键字的左子树中的所有关键字都小于它，而右子树中的所有关键字都大于它。所有叶子节点都位于同一层，或者说根节点到每个叶子节点的长度都雷同。每个节点都存有索引和数据，也就是对应的key和value。所以，根节点的关键字数量范畴：1 <= k <= m-1，非根节点的关键字数量范畴：m/2 <= k <= m-1。 这里的m示意阶数，阶数示意了一个节点最多有多少个孩子节点，所以形容一颗B树时须要指定它的阶数。 咱们再举个例子来阐明一下下面的概念，比方这里有一个5阶的B树，根节点数量范畴：1 <= k <= 4，非根节点数量范畴：2 <= k <= 4。 上面，咱们通过一个插入的例子，解说一下B树的插入过程，接着，再解说一下删除关键字的过程。 1.2 B树插入插入的时候，咱们须要记住一个规定：判断以后结点key的个数是否小于等于m-1，如果满足，直接插入即可，如果不满足，将节点的两头的key将这个节点分为左右两局部，两头的节点放到父节点中即可。 例子：在5阶B树中，结点最多有4个key,起码有2个key（留神：上面的节点对立用一个节点示意key和value）。 插入18，70，50,40 插入22 插入22时，发现这个节点的关键字曾经大于4了，所以须要进行决裂，决裂的规定在下面曾经讲了，决裂之后，如下。 接着插入23，25，39 决裂，失去上面的。 所以B树每一层的节点数会变多，雷同的数据量的话，B树会比二叉树高度更低，须要的io次数就会变少，所以合乎咱们的索引需要。那MySQL最初为什么抉择了B+树呢，比B树更优的中央在哪里呢？咱们先看看B+树与B树不同的中央： B+树叶子节点蕴含了这棵树的所有键值，非叶子节点不存储数据，只存储索引，数据都存储在叶子节点。而B树是每个节点都存有索引和数据。B+树每个叶子结点都存有相邻叶子结点的指针，叶子结点自身依关键字的大小自小而大程序链接。如图： 第一点：当非叶子节点只存索引key而不存data时，就能够使得非叶子节点的占用空间变少，雷同容量的节点能够存储更多的索引，那同样是三层的B+树，阶数就会变多，就会比B树存更多的数据。第二点：B+树叶子节点存有相邻叶子节点的指针，想要了解这个指针的益处，咱们的先晓得磁盘读取数据时往往不是严格按需读取，而是每次都会预读，即便只须要一个字节，磁盘也会从这个地位开始，程序向后读取肯定长度的数据放入内存。这样做的理论依据是计算机科学中驰名的局部性原理： 当一个数据被用到时，其左近的数据也通常会马上被应用。程序运行期间所须要的数据通常比拟集中。预读的长度个别为页（page）的整倍数。页是计算机管理存储器的逻辑块，硬件及操作系统往往将主存和磁盘存储区宰割为间断的大小相等的块，每个存储块称为一页（在许多操作系统中，页得大小通常为4k），主存和磁盘以页为单位替换数据。当程序要读取的数据不在主存中时，会触发一个缺页异样，此时零碎会向磁盘收回读盘信号，磁盘会找到数据的起始地位并向后间断读取一页或几页载入内存中，而后异样返回，程序持续运行。 当初再看B+树叶子节点的指针，咱们就明确了它的用途，预读的时候能够保障间断读取的数据有序。 可能还有的同学提过B*树，它是在B+树根底上，为非叶子结点也减少链表指针。集体感觉没用B星树可能是感觉没必要吧，咱们在非叶子节点又不存data，data都在叶子节点，非叶子节点了链表指针用不上。 一些花里胡哨的概念聚簇索引和非聚簇索引：下面咱们提到B+树的叶子节点存了索引key的数据data，然而mysql不同的引擎存data的抉择是不一样的，MyISAM是将索引文件和实在的数据文件分两个文件各种寄存，索引文件中存的data是该索引key对应的数据在数据文件中的地址值，而InnoDB则是将正式的数据存在了叶子节点中。所以聚簇和非聚簇就是辨别叶子节点存的data是不是实在的（能够了解为叶子节点挤不挤？） 回表：回表也简略，然而得先明确主键索引和一般索引，下面咱们所的叶子节点存实在的数据，那是只有主键索引才是这么存的，一般索引它存的data是主键索引的key。那这样咱们就好了解了。比方我当初给一张表的name字段建了个一般索引，我想select * from table where name = 'test'，这个时候咱们找到test节点的时候，拿到的key只是这行数据对应的主键key，咱们要失去整行的数据只能拿着这个key再去主键索引树再找一次。这个操作就叫做回表。 最左匹配准则： 当咱们新建了一个组合索引时，比方（name+age），查问时应用 where name = xx and age = xx时会走组合索引，而where age = xx and name =xx则不会走。这是因为MySQL创立联结索引的规定是首先会对联结索引的最右边第一个字段排序，在第一个字段的排序根底上，而后在对第二个字段进行排序。

越致力，越侥幸，本文已珍藏在GitHub中JavaCommunity, 外面有面试分享、源码剖析系列文章，欢送珍藏，点赞https://github.com/Ccww-lx/JavaCommunity数据库索引在平时的工作是必备的，怎么建索引，怎么应用索引，能够进步数据的查问效率。而且在面试过程，数据库的索引也是必问的知识点，比方： 索引底层构造选型，那为什么抉择B+树？不同存储引擎的索引的体现模式有哪些？索引的类型 组合索引存储形式查问形式最左前缀匹配准则笼罩索引是什么？看着这些，能说出多少，了解多少呢？因而咱们须要去探索其内在原理。 那索引是什么？索引的目标为了减速检索数据而设计的一种扩散存储（索引经常很大，属于硬盘级的货色，所以是扩散存储）的数据结构,其原理以空间换工夫。而疾速检索的实现的实质是数据结构，通过不同数据结构的抉择，实现各种数据疾速检索，索引有哈希索引和B+树索引。 索引底层构造选型，那为什么抉择B+树？数据库索引底层选型归根到底就是为进步检索效率，那么就须要思考几个问题： 算法工夫复杂度是否存在排序磁盘IO与预读NOTE： 思考到磁盘IO是十分昂扬的操作，计算机操作系统做了一些优化，当一次IO时，不光把以后磁盘地址的数据，而是把相邻的数据也都读取到内存缓冲区内，因为部分预读性原理通知咱们，当计算机拜访一个地址的数据的时候，与其相邻的数据也会很快被拜访到。每一次IO读取的数据咱们称之为一页(page)。哈希表( Hash Table,散列表 )哈希表是依据键（Key）而间接拜访在内存存储地位的数据结构。 通过计算一个对于键值的函数，将所需查问的数据映射到表中一个地位来拜访记录，这放慢了查找速度。尽管查问工夫复杂度为O(1)，但存在着碰撞问题，最坏状况会导致工夫简单急剧减少; 而且哈希表其只适宜精准key（等于）检索，不适宜范畴式检索，范畴检索就须要一次把所有数据找进去加载到内存，没有效率，因而不适宜Mysql的底层索引的数据结构。 一般的二叉查找树为了优化高效范畴查问，且工夫复杂度小，引入二叉查找树 二叉查找树的工夫复杂度是 O(lgn)，因为数据已排序好了，所以范畴查问是能够高效查问， 但会存在的问题：左右子节点的深度可能相差很大，最极其的状况只有左子树或者右子树，此时查找的效率为O(n)，检索性能急剧下降，因而也不适宜Mysql的底层索引的数据结构。 均衡二叉树（AVL树）为了优化二叉树左右子树深度相差太大的问题，咱们引入了均衡二叉树，即左右子节点的深度差不超过1均衡二叉树看来如同适宜，能够实现： 能够实现范畴查找、数据排序查问性能良好O(logn) NOTE:上图中一个磁盘块，代表硬盘上的一个存储地位然而咱们还有一个最重要因素须要思考，磁盘IO与预读，且数据库查问数据的瓶颈在于磁盘 IO,应用均衡二叉树依据索引进行查找时，每读一个磁盘块就进行一次IO，这样没有实现计算机的预读，导致检索效率，总结出均衡二叉树作为索引的问题（上图中一个磁盘块，代表硬盘上的一个存储地位）： 太深了（即它只有二条路），深度越大进行的IO操作也就越多太小了，每一次IO才查问磁盘块这么一点数据，太节约IO了。操作系统规定一次IO最小4K，Mysql一次IO 16K，而图上的磁盘块能显著达不到4KB+树为了优化磁盘IO和预读，缩小IO操作，条路太少了，那么换成多条路，那么会想到应用B树和B+树，但B树每个节点限度最多存储两个 key，也会造成IO操作过于频繁，因而优化思路为：尽可能在一次磁盘 IO 中多读一点数据到内存，那么B+树也该出场： B+树一个节点能存很多索引，且只有B+树叶子节点存储数据相邻节点之间有一些前驱后继关系叶子节点是顺序排列的绝对于B树，B+树的劣势有： B+树扫库扫表的能力更强 B树的数据是寄存在每一个节点中的，节点所在的物理地址又是随机的，所以扫表的话，进行的是随机IOB+树的数据是寄存在叶子节点的，且在一个叶子节点中的数据是间断的，所以扫表的话，进行的绝对的程序IOB+树的磁盘读写能力更强，枝节点不保留数据，而保留更多的关键字。一次IO就能读出更多的关键字B+树的排序能力更强，B+树的叶子节点存储的数据是曾经排好序的索引的体现模式索引在不同的存储引擎中体现模式步一样， 最常见的是： Innodb 引擎中体现为汇集索引形式 （索引和数据是寄存在同一个文件的）Myisam引擎中体现为非汇集索引形式 （索引和数据是寄存在两个文件中的）汇集索引形式（InnoDB存储引擎）InnoDB存储引擎中，索引和数据是寄存在同一个文件的，属于汇集索引 。而且InnoDB会主动建设好主键 ID 索引树, 因而建表时要求必须指定主键的起因。 其中，主键索引（汇集索引）的叶子节点记录了数据，而不是数据的物理地址。辅助索引的叶子节点寄存的是主键key。所以当利用辅助索引查找数据时，实际上查了两遍索引（辅助索引和主键索引）: 先查问辅助索引树找出主键而后在主键索引树中依据主键查问数据 非汇集索引形式（Myisam存储引擎）Myisam存储引擎中，索引和数据是寄存在两个文件中的，属于非汇集索引 。不论是主键索引还是辅助索引，其叶子节点都是记录了数据的物理地址。 MySQL的索引类型MySQL索引能够分为： 一般索引(index): 减速查找惟一索引： 主键索引：primary key ：减速查找+束缚（不为空且惟一）惟一索引：unique：减速查找+束缚 （惟一）联结索引： primary key(id,name):联结主键索引unique(id,name):联结惟一索引index(id,name):联结一般索引全文索引full text :用于搜寻很长一篇文章的时候，成果最好。其中，次要了解一下联结索引的问题，存储构造，查问形式。 联结索引联结索引，多个列组成的索引叫做联结索引，单列索引是非凡的联结索引。其存储构造如下： <font color='red'>对于联结索引来说其存储构造只不过比单值索引多了几列，组合索引列数据都记录在索引树上，（不同的组合索引，B+树也是不同的），且存储引擎会首先依据第一个索引列排序后，其余列再顺次将相等值的进行排序。</font> NOTE:叶节点第一排，按程序排序好，第二列，会基于第一列排序好的，将第一列相等的再下一列再排序，顺次类推。<font color='red'>联结索引查问形式，存储引擎首先从根节点（个别常驻内存）开始查找，而后再顺次在其余列中查问，直到找到该索引下的data元素即ID值，再从主键索引树上找到最终数据。</font> 而且联结索引其抉择的准则： 最左前缀匹配准则（常常应用的列优先）离散度高的列优先宽度小的列优先最左前缀匹配准则最左前缀匹配准则和联结索引的索引构建形式及存储构造是有关系的。根据上述了解剖析，能够得出联结索引只能从多列索引的第一列开始查找索引才会失效，比方: 假如表user上有个联结索引(a,b,c)，那么 select * from user where b = 1 and c = 2将不会命中索引起因是联结索引的是存储引擎先按第一个字段排序，再按第二个字段排序，顺次排序。 ...

index索引用于疾速查找具备特定列值的行，如果没有索引，mysql必须从第一行开始，扫描全表找到对应的行。表越大，破费越多。如果表中有相干的索引，mysql能够疾速确定要在数据文件中查找的地位。大多数mysql索引（primary key，unique，index和fulltext）存储在B-trees。空间数据类型的索引应用R-trees；MEMORY表还反对hash indexes；InnoDB对fulltext应用倒排表。MySQL应用索引进行以下操作： 通过where条件疾速查问。最优匹配。如果能够在多个索引之间进行抉择，则MySQL通常会应用查找最小行数的索引。如果表具备多列索引，那么优化器能够应用索引的任何最左前缀来查找行。举例来说，如果你有一个三列的索引 (col1, col2, col3)，你能够索引的搜寻有(col1)， (col1, col2)以及(col1, col2, col3)。执行联接时从其余表中检索行。如果申明雷同的类型和大小，MySQL能够更无效地在列上应用索引。在这种状况下， VARCHAR与 CHAR被认为是雷同的，如果它们被申明为雷同的大小。例如， VARCHAR(10)和 CHAR(10)是雷同的大小，然而 VARCHAR(10)和 CHAR(15)不是。对于非二进制字符串列之间的比拟，两个列应应用雷同的字符集，不同的字符集将导致索引生效。例如，将utf8列与latin1列进行比拟会排除应用索引。如果不能不通过转换间接比拟值，则比拟不同的列（例如，将字符串列与工夫或数字列进行比拟）可能会阻止应用索引。对于给定的值，如数值列的值为1，它可能比拟等于在字符串列，例如任何数量的值 '1'，' 1'， '00001'，或'01.e1'，导致索引生效。在索引列应用MIN()或 MAX()。mysql预处理器将进行优化，预处理器会查看您是否正在索引呈现的所有要害局部上应用。在这种状况下，MySQL为每个表达式执行一次键查找，并将其替换为常量，所有表达式都用常量替换实现后，查问将立刻返回。排序或分组查问（order by， group by）应用最左匹配索引（order by key1, key2）；如果倒序排序（order by key1, key2 desc）,将按相同程序应用索引key。某些状况下，mysql会间接从索引中获取数据，而不必查问表；例如：只查问索引列数据。留神：当开启列长事务时，可能导致该优化生效，回表查问。当全表扫描快于走索引查问时，mysql也不会走索引。covering index查问的所有列都蕴含在索引中时，mysql不会扫描表（即不会回表查问），这种状况mysql定义为covering index；InnoDb引擎下，开启事务时，将不会应用这种优化查问。 参考资料8.5.2 Optimizing InnoDB Transaction Management

背景我置信大家在数据库优化的时候都会说到索引，我也不例外，大家也基本上能对数据结构的优化答复个一二三，以及页缓存之类的都能扯上几句，然而有一次阿里P9的一个面试问我：你能从计算机层面开始说一下一个索引数据加载的流程么？（就是想让我聊IO） 我当场就逝世了....因为计算机网络和操作系统的基础知识真的是我的盲区，不过前面我恶补了，废话不多说，咱们就从计算机加载数据聊起，讲一下换个角度聊索引。 如果感觉看完文章有所播种的话，能够关注我一下哦知乎：秃顶之路 b站：linux亦有归途每天都会更新咱们的公开课录播以及编程干货和大厂面经或者间接点击链接c/c++ linux服务器开发高级架构师来课堂上跟咱们讲师面对面交换须要大厂面经跟学习纲要的小伙伴能够加群973961276获取 注释MySQL的索引实质上是一种数据结构让咱们先来理解一下计算机的数据加载。 磁盘IO和预读： 先说一下磁盘IO，磁盘读取数据靠的是机械运动，每一次读取数据须要寻道、寻点、拷贝到内存三步操作。 寻道工夫是磁臂挪动到指定磁道所须要的工夫，个别在5ms以下； 寻点是从磁道中找到数据存在的那个点，均匀工夫是半圈工夫，如果是一个7200转/min的磁盘，寻点工夫均匀是600000/7200/2=4.17ms； 拷贝到内存的工夫很快，和后面两个工夫比起来能够忽略不计，所以一次IO的工夫均匀是在9ms左右。听起来很快，但数据库百万级别的数据过一遍就达到了9000s，显然就是劫难级别的了。 思考到磁盘IO是十分昂扬的操作，计算机操作系统做了预读的优化，当一次IO时，不光把以后磁盘地址的数据，而是把相邻的数据也都读取到内存缓冲区内，因为当计算机拜访一个地址的数据的时候，与其相邻的数据也会很快被拜访到。 每一次IO读取的数据咱们称之为一页(page)，具体一页有多大数据跟操作系统无关，个别为4k或8k，也就是咱们读取一页内的数据时候，实际上才产生了一次IO。 （忽然想到个我刚毕业被问过的问题，在64位的操作系统中，Java中的int类型占几个字节？最大是多少？为什么？） 那咱们想要优化数据库查问，就要尽量减少磁盘的IO操作，所以就呈现了索引。 索引是什么？MySQL官网对索引的定义为：索引(Index)是帮忙MySQL高效获取数据的数据结构。 MySQL 中罕用的索引在物理上分两类，B-树索引和哈希索引。 本次次要讲BTree索引。 BTree索引BTree又叫多路均衡查找树，一颗m叉的BTree个性如下： 树中每个节点最多蕴含m个孩子。除根节点与叶子节点外，每个节点至多有[ceil(m/2)]个孩子（ceil()为向上取整）。若根节点不是叶子节点，则至多有两个孩子。所有的叶子节点都在同一层。每个非叶子节点由n个key与n+1个指针组成，其中[ceil(m/2)-1] <= n <= m-1 。![](data:image/svg+xml;utf8,<?xml version="1.0"?><svg xmlns="http://www.w3.org/2000/svg" version="1.1" width="800" height="600"></svg>)这是一个3叉（只是举例，实在会有很多叉）的BTree结构图，每一个方框块咱们称之为一个磁盘块或者叫做一个block块，这是操作系统一次IO往内存中读的内容，一个块对应四个扇区，紫色代表的是磁盘块中的数据key，黄色代表的是数据data，蓝色代表的是指针p，指向下一个磁盘块的地位。 来模仿下查找key为29的data的过程： 1、依据根结点指针读取文件目录的根磁盘块1。【磁盘IO操作1次】 2、磁盘块1存储17，35和三个指针数据。咱们发现17<29<35，因而咱们找到指针p2。 3、依据p2指针，咱们定位并读取磁盘块3。【磁盘IO操作2次】 4、磁盘块3存储26，30和三个指针数据。咱们发现26<29<30，因而咱们找到指针p2。 5、依据p2指针，咱们定位并读取磁盘块8。【磁盘IO操作3次】 6、磁盘块8中存储28，29。咱们找到29，获取29所对应的数据data。 由此可见，BTree索引使每次磁盘I/O取到内存的数据都施展了作用，从而进步了查问效率。然而有没有什么可优化的中央呢？ 咱们从图上能够看到，每个节点中不仅蕴含数据的key值，还有data值。而每一个页的存储空间是无限的，如果data数据较大时将会导致每个节点（即一个页）能存储的key的数量很小，当存储的数据量很大时同样会导致B-Tree的深度较大，增大查问时的磁盘I/O次数，进而影响查问效率。 B+Tree索引B+Tree是在B-Tree根底上的一种优化，使其更适宜实现外存储索引构造。在B+Tree中，所有数据记录节点都是依照键值大小程序寄存在同一层的叶子节点上，而非叶子节点上只存储key值信息，这样能够大大加大每个节点存储的key值数量，升高B+Tree的高度。 ![](data:image/svg+xml;utf8,<?xml version="1.0"?><svg xmlns="http://www.w3.org/2000/svg" version="1.1" width="800" height="600"></svg>)B+Tree绝对于B-Tree有几点不同： 非叶子节点只存储键值信息， 数据记录都寄存在叶子节点中， 将上一节中的B-Tree优化，因为B+Tree的非叶子节点只存储键值信息，所以B+Tree的高度能够被压缩到特地的低。 具体的数据如下： InnoDB存储引擎中页的大小为16KB，个别表的主键类型为INT（占用4个字节）或BIGINT（占用8个字节），指针类型也个别为4或8个字节，也就是说一个页（B+Tree中的一个节点）中大略存储16KB/(8B+8B)=1K个键值（因为是估值，为不便计算，这里的K取值为〖10〗^3）。也就是说一个深度为3的B+Tree索引能够保护10^3 10^3 10^3 = 10亿 条记录。（这种计算形式存在误差，而且没有计算叶子节点，如果计算叶子节点其实是深度为4了） 咱们只须要进行三次的IO操作就能够从10亿条数据中找到咱们想要的数据，比起最开始的百万数据9000秒不晓得好了多少个华莱士了。 而且在B+Tree上通常有两个头指针，一个指向根节点，另一个指向关键字最小的叶子节点，而且所有叶子节点（即数据节点）之间是一种链式环构造。所以咱们除了能够对B+Tree进行主键的范畴查找和分页查找，还能够从根节点开始，进行随机查找。 数据库中的B+Tree索引能够分为汇集索引（clustered index）和辅助索引（secondary index）。 下面的B+Tree示例图在数据库中的实现即为汇集索引，汇集索引的B+Tree中的叶子节点寄存的是整张表的行记录数据，辅助索引与汇集索引的区别在于辅助索引的叶子节点并不蕴含行记录的全副数据，而是存储相应行数据的汇集索引键，即主键。 当通过辅助索引来查问数据时，InnoDB存储引擎会遍历辅助索引找到主键，而后再通过主键在汇集索引中找到残缺的行记录数据。 ![](data:image/svg+xml;utf8,<?xml version="1.0"?><svg xmlns="http://www.w3.org/2000/svg" version="1.1" width="800" height="600"></svg>)不过，尽管索引能够放慢查问速度，进步 MySQL 的解决性能，然而过多地应用索引也会造成以下弊病： 创立索引和保护索引要消耗工夫，这种工夫随着数据量的减少而减少。除了数据表占数据空间之外，每一个索引还要占肯定的物理空间。如果要建设聚簇索引，那么须要的空间就会更大。当对表中的数据进行减少、删除和批改的时候，索引也要动静地保护，这样就升高了数据的保护速度。留神：索引能够在一些状况下减速查问，然而在某些状况下，会升高效率。索引只是提高效率的一个因素，因而在建设索引的时候应该遵循以下准则： 在常常须要搜寻的列上建设索引，能够放慢搜寻的速度。在作为主键的列上创立索引，强制该列的唯一性，并组织表中数据的排列构造。在常常应用表连贯的列上创立索引，这些列次要是一些外键，能够放慢表连贯的速度。在常常须要依据范畴进行搜寻的列上创立索引，因为索引曾经排序，所以其指定的范畴是间断的。在常常须要排序的列上创立索引，因为索引曾经排序，所以查问时能够利用索引的排序，放慢排序查问。在常常应用 WHERE 子句的列上创立索引，放慢条件的判断速度。当初大家晓得索引为啥能这么快了吧，其实就是一句话，通过索引的构造最大化的缩小数据库的IO次数，毕竟，一次IO的工夫真的是太久了。。。 总结就面试而言很多常识其实咱们能够很容易就把握了，然而要以学习为目标，你会发现很多货色咱们得深刻到计算机根底上能力发现其中神秘，很多人问我怎么记住这么多货色，其实学习自身就是一个很无奈的货色，既然咱们不能不学那为啥不好好学？去学会享受呢？最近我也在恶补根底，前面我会开始更新计算机根底和网络相干的常识的。

1、前言MySQL数据库管理系统自身就是一个文件管理系统，尽管它的实现形式的确比较复杂，但实质上是要通过拜访磁盘能力实现数据的存储与检索。所以如果咱们想要进一步理解MySQL索引的的话，磁盘相干的操作也是须要大抵理解一下的。 2、硬盘的读写原理--------------------磁盘的硬件组成--------------------一般说来，硬盘都是由盘片、磁头、盘片主轴、管制电机、磁头控制器、数据转换器、接口、缓存等几个部份组成。--------------------硬盘的基本概念---------------------- 2.1 盘片硬盘由多个盘片组成，盘片个别用铝合金资料做基片，高速硬盘也可能用玻璃做基片，通常有2-3个盘片。-- 2.2 盘面每一个盘片都有两个盘面(Side)，即上、下盘面，个别每个盘面都会利用，都能够存储数据，成为无效盘片，也有极个别的硬盘盘面数为复数。每一个这样的无效盘面都有一个盘面号，按程序从上至下从0开始顺次编号。在硬盘零碎中，盘面号又叫磁头号，因为每一个无效盘面都有一个对应的读写磁头，因为磁盘通常有2-3个盘片，故盘面号为0-3或0-5。-- 2.3 磁头每一个无效盘面都有一个对应的读写磁头，作用就是将对盘面进行读写操作。-- 2.4 磁道磁盘在格式化时被划分成许多同心圆，这些同心圆轨迹叫做磁道(Track)。磁道从外向内从0开始程序编号。硬盘的每一个盘面有300-1024个磁道。-- 2.5 柱面所有盘面上的同一磁道形成一个圆柱，通常称做柱面(Cylinder)，每个圆面上的磁头由上而下从0开始编号。数据的读/写按柱面进行，即磁头读/写数据时首先在同一柱面内从0盘面开始进行操作，顺次向下在同一柱面的不同盘面上进行操作，只有当同一柱面上所有的盘面全副读/写结束后，磁头才转移到下一柱面(同心圆再往里的柱面)。为什么磁盘的读写操作是依照柱面进行？起因是选取盘面(磁头)只需通过电子切换即可，而选取柱面则必须通过机械切换。又因为电子切换比在机械上磁头向邻近磁道挪动快得多，所以磁盘的读写操作是依照柱面进行。也就是说，在进行写操作时，在某个柱面的某个盘面的磁道写满数据后，切换到同一柱面的下一个盘面的磁道上接着写，一个柱面写满后，才移到下一个柱面的0盘面。读数据也依照这种形式进行，这样就进步了硬盘的读/写效率。-- 2.6 扇区每个磁道被等分为若干个弧段，这些弧段便是硬盘的扇区(Sector)，扇区是硬盘的最小读写单元。一个扇区有两个次要局部：存储数据地点的标识符和存储数据的数据段。 标识符：包含组成扇区三维地址的三组数字 柱面号：扇区所在的磁道; 盘面号：扇区所在的盘面; 扇区号：扇区在磁道上的地位。也叫块号; 数据段：512个字节的数据-- 2.7 盘块操作系统与磁盘打交道的最小单位是磁盘块。因为扇区的内容比拟小，且数目泛滥，导致操作系统在寻址时比拟艰难，所以操作系统就将相邻的扇区(2的N次方个扇区)组合在一起，造成一个块，再对块进行整体的操作。--------------------磁盘的拜访流程--------------------1、获取数据的物理地址：当操作系统须要从磁盘读取数据时，操作系统会将数据的逻辑地址传给磁盘，磁盘的控制电路依照寻址逻辑将逻辑地址翻译成物理地址(柱面号、盘面号、扇区号)。2、从指定扇区读取数据：在获取了数据的物理地址之后，接下来从扇区上读取的数据。首先磁盘须要找到柱面与盘面，即磁头须要挪动对准相应柱面下的磁道，这个过程叫做寻道，所消耗工夫叫做寻道工夫。而后指标扇区旋转到磁头下，即磁盘旋转将指标扇区旋转到磁头下，这个过程消耗的工夫叫做旋转工夫。最初将扇区中的数据传输到操作系统的内存中，所消耗工夫叫做传输工夫。3、即一次访盘申请(读/写)实现过程由三个动作组成： 寻道提早（工夫）：指定磁头挪动定位到指定柱面的指定磁道 旋转提早（工夫）：期待指定扇区从磁头下旋转通过 数据传输（工夫）：数据在磁盘与内存之间的理论传输 --------------------磁盘的拜访原理--------------------零碎将文件存储到磁盘上时，按柱面、磁头、扇区的形式进行，即最先读/写第0磁道的第0磁头下(也就是第0盘面的第0磁道)的所有扇区。而后是同一柱面的下一磁头，……，一个柱面存储满后就推动到下一个柱面，直到把文件内容全副写入磁盘。文件的记录在同一盘组上寄存时，应先集中放在一个柱面上，而后再程序寄存在相邻的柱面上，对应同一柱面，则应该按盘面的秩序程序寄存。即：从上到下，而后从外到内。数据的读/写按柱面进行，而不按盘面进行。零碎也以雷同的程序读出数据。读出数据时通过通知磁盘控制器要读出扇区所在的柱面号、磁头号和扇区号（物理地址的三个组成部分）进行。磁盘控制器则 间接使磁头部件步进到相应的柱面，选通相应的磁头，期待要求的扇区挪动到磁头下。在扇区到来时，磁盘控制器读出每个扇区的头标，把这些头标中的地址信息与期待检出的磁头和柱面号做比拟（即寻道），而后，寻找要求的扇区号。待磁盘控制器找到该扇区头标时，依据其工作是写扇区还是读扇区，来决定是转换写电路， 还是读出数据和尾部记录。找到扇区后，磁盘控制器必须在持续寻找下一个扇区之前对该扇区的信息进行后处理。如果是读数据，控制器计算此数据的ECC码，然 后，把ECC码与已记录的ECC码相比拟。如果是写数据，控制器计算出此数据的ECC码，与数据一起存储。在控制器对此扇区中的数据进行必要解决期间，磁 盘持续旋转。--------------------局部性原理与磁盘预读--------------------因为存储介质的个性，磁盘自身存取就比主存慢很多，再加上机械运动消耗，磁盘的存取速度往往是主存的几百分分之一，因而为了提高效率，要尽量减少磁盘I/O。为了达到这个目标，磁盘往往不是严格按需读取，而是每次都会预读，即便只须要一个字节，磁盘也会从这个地位开始，程序向后读取肯定长度的数据放入内存。这样做的理论依据是计算机科学中驰名的局部性原理：　1、当一个数据被用到时，其左近的数据也通常会马上被应用。　2、程序运行期间所须要的数据通常比拟集中。因为磁盘程序读取的效率很高（不须要寻道工夫，只需很少的旋转工夫），因而对于具备局部性的程序来说，预读能够进步I/O效率。预读的长度个别为页（page）的整倍数。页是计算机管理存储器的逻辑块，硬件及操作系统往往将主存和磁盘存储区宰割为间断的大小相等的块，每个存储块称为一页（在许多操作系统中，页得大小通常为4k），主存和磁盘以页为单位替换数据。当程序要读取的数据不在主存中时，会触发一个缺页异样，此时零碎会向磁盘收回读盘信号，磁盘会找到数据的起始地位并向后间断读取一页或几页载入内存中，而后异样返回，程序持续运行。3、索引的根本应用--------------------索引简介--------------------数据库在保留数据时，除了保留自身之外，还保护着一个满足特定查找算法的数据结构，这些数据结构以某种形式指向数据，这样就能够在这些数据结构的根底上实现高级查找算法，这种数据结构就是索引。简而言之，索引就是帮忙MySQl高效获取数据的数据结构。--------------------索引语法------------------------------一般索引的创立语法------------ 形式一：间接创立索引CREATE INDEX index_name ON table_name (column_name);-- 形式二：批改表构造时增加索引ALTER TABLE table_name ADD INDEX index_name ON column_name;-- 形式三：创立表时间接增加索引CREATE TABLE dept ( dept_id INTEGER PRIMARY KEY AUTO_INCREMENT, dept_name VARCHAR(32) DEFAULT NULL, INDEX id_dept_deptname (dept_name))----------主键索引的创立语法------------ 数据库会主动为主键字段设置主键索引CREATE TABLE dept ( dept_id INTEGER PRIMARY KEY AUTO_INCREMENT, dept_name VARCHAR(32) DEFAULT NULL)--------------------惟一索引的创立语法---------------------- 形式一：间接创立索引CREATE UNIQUE INDEX index_name ON table_name (column_name);-- 形式二：批改表构造时增加索引ALTER TABLE table_name ADD UNIQUE INDEX index_name ON column_name;-- 形式三：创立表时间接增加索引-- 独自设置惟一索引CREATE TABLE dept ( dept_id INTEGER PRIMARY KEY AUTO_INCREMENT, dept_name VARCHAR(32) DEFAULT NULL, UNIQUE INDEX id_dept_deptname (dept_name))-- 字段增加惟一束缚之后，数据库会主动为存在惟一束缚的列设置惟一索引CREATE TABLE dept ( dept_id INTEGER PRIMARY KEY AUTO_INCREMENT, dept_name VARCHAR(32) UNIQUE DEFAULT NULL)----------组合索引的创立语法------------ 创立表时间接增加索引CREATE TABLE dept ( dept_id INTEGER PRIMARY KEY AUTO_INCREMENT, dept_name VARCHAR(32) DEFAULT NULL, manager_id INTEGER, INDEX idx_deptnameManagerid (dept_name,manager_id))-- 批改表构造时增加索引ALTER TABLE table_name ADD INDEX index_name (column_name,column_name);----------索引的查问删除语法------------ 查问索引SHOW INDEX FROM table_name;-- 删除索引DROP INDEX index_name ON table_name;

前言：这篇文章次要讲 explain 如何应用，还有 explain 各种参数概念，之后会讲优化一、Explain 用法模仿Mysql优化器是如何执行SQL查问语句的，从而晓得Mysql是如何解决你的SQL语句的。剖析你的查问语句或是表构造的性能瓶颈。 语法：Explain + SQL 语句; 如：Explain select * from user; 会生成如下 SQL 剖析后果，上面具体对每个字段进行详解 二、id是一组数字，代表多个表之间的查问程序，或者蕴含子句查问语句中的程序，id 总共分为三种状况，顺次详解 id 雷同，执行程序由上至下 id 不同，如果是子查问，id 号会递增，id 值越大优先级越高，越先被执行 id 雷同和不同的状况同时存在 三、select_typeselect_type 蕴含以下几种值 simpleprimarysubqueryderivedunionunion resultsimple简略的 select 查问，查问中不蕴含子查问或者 union 查问 primary如果 SQL 语句中蕴含任何子查问，那么子查问的最外层会被标记为 primary subquery在 select 或者 where 里蕴含了子查问，那么子查问就会被标记为 subQquery，同三.二同时呈现 derived在 from 中蕴含的子查问，会被标记为衍生查问，会把查问后果放到一个长期表中 union / union result如果有两个 select 查问语句，他们之间用 union 连起来查问，那么第二个 select 会被标记为 union，union 的后果被标记为 union result。它的 id 是为 null 的 四、table示意这一行的数据是哪张表的数据 五、typetype 是代表 MySQL 应用了哪种索引类型，不同的索引类型的查问效率也是不一样的，type 大抵有以下品种 ...

MySQL是一个关系型数据库管理系统，由瑞典MySQL AB 公司开发，属于 Oracle 旗下产品。MySQL 是最风行的关系型数据库管理系统之一，在 WEB 利用方面，MySQL是最好的 RDBMS (Relational Database Management System，关系数据库管理系统) 应用软件之一。 明天咱们就来聊聊MySQL的索引。只管MySQL有许多长处，然而在海量数据的状况下，性能方面的体现还是会让人捉急，这时候就轮到MySQL的索引出场了。我会以抛出问题而后解决问题的形式来进行本次分享。比方：什么是索引？索引能够做什么？为什么应用索引能够提高效率？MySQL反对哪些索引类型？什么状况下应不建或少建索引？什么是联结索引？为什么说B+比B树更适宜理论利用中操作系统的文件索引和数据库索引？ 一、什么是索引？官网解释：索引(Index)是帮忙MySQL高效获取数据的数据结构。 艰深了解：索引是一种非凡的文件（InnoDB 数据表上的索引是表空间的一个组成部分），它们蕴含着对数据表里所有记录的援用指针。 二、索引能够做什么？首先，索引不是万能的，索引能够放慢数据检索操作，但会使数据批改操作变慢。每次批改数据记录，索引就必须刷新一次。为了在某种程度上补救这一缺点，许多 SQL 命令都有一个 DELAY_KEY_WRITE 项。这个选项的作用是临时禁止 MySQL 在该命令每插入一条新记录和每批改一条现有之后立即对索引进行刷新，对索引的刷新将等到全副记录插入/批改结束之后再进行。在须要把许多新记录插入某个数据表的场合，DELAY_KEY_WRITE 选项的作用将非常明显。 三、为什么应用数据索引能提高效率？数据索引的存储是有序的在有序的状况下，通过索引查问一个数据是无需遍历索引记录的极其状况下，数据索引的查问效率为二分法查问效率，趋近于 log2(N)四、MySQL反对哪些索引类型？咱们这里说的索引类型并不是指“主键索引”、“外键索引”这些，而是索引底层的数据结构。MySQL的索引数据结构反对以下两种： B-Tree 索引。B+树是一个均衡的多叉树，从根节点到每个叶子节点的高度差值不超过1，而且同层级的节点间有指针互相链接，是有序的，如下图： Hash 索引。哈希索引就是采纳肯定的哈希算法，把键值换算成新的哈希值，检索时不须要相似B+树那样从根节点到叶子节点逐级查找，只需一次哈希算法即可,是无序的，如下图所示： * 哈希索引的劣势：等值查问，哈希索引具备绝对优势（前提是：没有大量反复键值，如果大量反复键值时，哈希索引的效率很低，因为存在所谓的哈希碰撞问题。）* 哈希索引不实用的状况： * 不反对范畴查问 * 不反对索引实现排序 * 不反对联结索引的最左前缀匹配规定五、什么状况下应不建或少建索引？咱们都晓得什么时候应该应用索引，那么，什么时候不应该应用索引呢？咱们下面说到，索引并不是万能的，所以，索引必定也有不实用的场景。以下几个场景的时候，咱们应该尽量不建或者说少建索引： 表记录太少。常常插入、删除、批改的表。数据反复且散布均匀的表字段，如果一个表有10万行记录，有一个字段A只有T和F两种值，且每个值的散布概率大概为50%，那么对这种表A字段建索引个别不会进步数据库的查问速度。常常和主字段一块查问但主字段索引值比拟多的表字段。六、什么是联结索引？联结索引是两个或更多个列上的索引。对于联结索引，MySQL反对从左到右的应用索引中的字段，一个查问能够只应用索引中的一部份，但只能是最左侧局部。例如索引是key index (a,b,c)，能够反对a 、 a,b 、 a,b,c 这3种组合进行查找，但不反对 b,c进行查找，当最左侧字段是常量援用时，索引就非常无效。 利用索引中的附加列，您能够放大搜寻的范畴，但应用一个具备两列的索引不同于应用两个独自的索引。复合索引的构造与电话簿相似，人名由姓和名形成，电话簿首先按姓氏对进行排序，而后按名字对有雷同姓氏的人进行排序。如果您晓得姓，电话簿将十分有用；如果您晓得姓和名，电话簿则更为有用，但如果您只晓得名不晓得姓，电话簿将没有用途。七、为什么说B+比B树更适宜理论利用中操作系统的文件索引和数据库索引？B+的磁盘读写代价更低。B+的外部结点并没有指向关键字具体信息的指针，因而其外部结点绝对B树更小。如果把所有同一外部结点的关键字寄存在同一盘块中，那么盘块所能包容的关键字数量也越多。一次性读入内存中的须要查找的关键字也就越多。相对来说IO读写次数也就升高了。B+-tree的查问效率更加稳固。因为非终结点并不是最终指向文件内容的结点，而只是叶子结点中关键字的索引。所以任何关键字的查找必须走一条从根结点到叶子结点的路。所有关键字查问的门路长度雷同，导致每一个数据的查问效率相当。八、结语看到这里，你应该曾经把MySQL的索引常识都过了个大略，心愿本文能帮到你，happy hacking。接下来听首听首歌放松一下吧。真的爱你 BEYOND - BEYOND