扫描下方二维码或许微信查找大众号菜鸟飞呀飞，即可关注微信大众号，阅览更多Spring源码分析、Java并发编程、Netty源码系列和MySQL作业原理文章。

MySQL索引的工作原理

索引是一种加快查询的数据结构，在 MySQL 中，索引的数据结构挑选的是 B+Tree，至于 B+Tree 是什么以及为什么 MySQL 为什么挑选 B+Tree 来作为索引，可以去查看大众号的前三篇文章。

  • 索引数据结构之 B-Tree 与 B+Tree（上篇）

  • 索引数据结构之 B-Tree 与 B+Tree（下篇）

  • MySQL 为什么不必数组、哈希表、二叉树等数据结构作为索引呢

    今日首要来聊聊 MySQL 中索引的作业原理，这一部分的常识，在作业中经常被运用到，在面试中也几乎是必问的。所以，不管是面试造火箭，仍是作业拧螺丝，把握索引的作业原理，都对错常有N B % % : p必要的。

首先需求阐明的是，本文的一切评论均是基于 InnoDB 存储引擎为条件。

示例表

为了便利阐明，咱们先创立一个B U i _ 1 g @示例表。建表句子如下

CREATETABLEv W { } : n O  wuser(
`id`BIGINT(11)NOTNULLAUTO_INCREMENT,
`name`VARCHAR(64)COMMENT'姓名',
`age`INT(4)COMMENT'年龄',
PRIMARYKEY(`id`),
INDEX(NAME)
)ENGINE=INNODBCOMMENT'用户表';

INSE4 N ` r QRTINTO`user`(`name`,`age`)VALUES(] H U b : Y V 1 E'AA',30),('BB',33),('CC',31),('DD',30),('E1 N s % , F U .E',29)

在上面的 SQL 句子中，创立了一p l a X L q 9 $ r张 user 表，表中有三个字段，id 是主键，name 和 age 别离表明用户的姓名和年龄，一起还为字段 namJ q 1e 创立了一个一般索引。为了便利后边A @ 2描绘，因而还向表中插入了 5 条数据，因为主键 id 是自增的，所以这s @ D z t f * r五行数据的 id 值分为是 1~5。

主键索引

主键索引又称之为聚簇索引（cluster index），它的特点是叶子结点中会寄存当前主键所对应行M S 8 j a 2 Z的数据。什么意思呢？拿上面的比如来阐明，在表 user 中，id 为主键索引，所以会有一棵 id 的索引树，在该索引树的叶子结点中，不只寄存了主键 id 的值，还寄存了 name 和 value 的值。例如：在 id=1 这一行的数据中，name 和 age 的值为 AA 和 30，那么在索引树中，在 id=1 的结点处，寄存的是(1,”AA”,30)这三个值。id 索引树的示意图如下。

MySQL索引的工作原理

下面看看这一条 SQL 句子i H ; $ y的履行流程：

select*fromuserwhereid=1;

该句子在 where 条件中加了 id=1 这个过滤条件，因而会运用到主键 id 的索引树。

  1. 挑选运用 id 主键索引树；
  2. 找到 id 索引树的榜首层结点（关键字 3、7 所在的结点q # V % g ` Z），因为 where 条件中 id=1，1 小于 3，o A * ; r d @ S所以进入到关键字 3 的左子树中查找；
  3. 进入到 id 索引树的第二层结点，第二w W ~ i v z j层结点是叶子结点，叶子结点中寄存了表的数据，并且存在 id=1 的关键字] 5 w j，所以将 R1 返回。（R1 表明的是 id=1 这; g %一行的数据）。

回表

一般索引又称之为非聚簇索引，也叫做二级索引，它的特点是叶子结点中也会寄存数据，与主键索引不同的是，一般索引中寄存的数据只要主键的值，而非整行记载的数据。例如上面的示例表中，name 便是一个一般索引，它的索引树中，在叶子结点中寄存的数据是主键 id 的值，示意图如S # C b K @ #下：

MySQL索引的工作原理

下面看看这一条 SQL 句子的履行流程：

select*fromuserwherename='BB';

该句子在 where 条D K K F件中加了 name=’BB’这个过滤[ z s条件，因为咱们在建表时为 name 字段创立了索引，因而会运用到 name 这棵索引树。另外，因为咱们运用的是 sele5 l ; ( } Gct * ，也便是查询表中的一切字@ Q V F 1 O x段的值，可是 name 索引树中只存有主键 id 的值，无法满意要查询一切字段的需求，而一切字段的数据都是^ # ; &寄存在主键 id 索引树上的，因而在 name 索引树上查到主键 id 的值d a ` H O _ Q l b后，还需求根据查到的 id 值，再去主键索引树上查找这一行记载中其他字段的值，这个进程咱们称之为回表。（从一般索引树回到主键索引树查找的进程就叫做回表）。
所以上面的 SQL 句子的履行流程如下：

  1. 挑选运用 name 索引树；
  2. 找到索引树的榜首层结点，Y _ * ] = d B因为 where 条件中’BB’的值小于榜首层结点中关键字’CC’的值，索引进入到关键字’CC’的左子树中查找；
  3. 进入到第二层的叶子结点，找到关键字’BB’，因为叶子结点中寄存了主键 id 的数据，所以返回’BB’中主键 id 的值 2；
  4. 根据主键 id=2，再去主键 id 的索引树中查找，找到 id=2 所对应的数据 R2；
  5. 在 name 索引树中持续向后查找，找到’BB’的下一个关键字’CC’，发现’CC’不等于 where 条件中的’BB’，所以完毕查找。

掩盖索引

对于上d | $ }面的第二个比如，因为 na} d – V i I ; / Kme=’BB’的只要一条记载，因而只回了一次表，那假如有多条记载一起满意 name=’BB’这* 4 – & w N B V个条件，那就得进行多次回表操作了。显着，回表次数越多，SQL{ V e M e W 履行的越慢，那有什么方法能防止回表呢？答案便是6 w 3掩盖索引。

掩盖索引究竟是个什么东西呢？在上面的第二个示例中，咱们运用了 sele4 ^ U | N 4 + Gct * 来查询一切字段，那假如咱们并不需求一切的字段呢，只需求 id 字段呢？例如 select id from user where name = ‘BB’; 因为在 name 索引树的叶子结点中现已存有了主键 id 的值，所以 name 索引树能直接满意咱们的查询要求，因而此刻是不要回表操作，这种状况咱们称之为掩盖索引。

掩盖索引可以显着的提升查询性能，因为它能显着削减大P } J U量的回表操作。掩盖索引对错常常用的一种 SQL 优化手段，运用起来也非常简单。咱们在开发进程中，通常( ? S 0 = e Y / c建议不要运用 select * 来查询数据，一方面是因为在数据量大时，seU B Olect * 可能会返回v [ z E c w E很多无用字段，浪费网络资源；另一方面也是出于尽量运用掩盖索引的考虑。

联合索引与最左匹配准则

假设咱们现在有个需求( V Y是要查询 user 表中，name=K k 9 6‘BB’的人的 name 和 age，咱们的 SQL 需求这样写：

selectname,agefromuserwherename='BB';

显着，此刻也会运用到= , [ C name 索引树，又因为 name 索引树中并没有寄存 age 字段的信息，因而需求R s 6 k ]进行– a E回表，回到主键 id 的索引树中取 age 字段的值。那么有什么方法能优化一下呢& b 1 g G { f n？让这次查询不需求进行回表。肯定有啊！运用掩盖索引啊。怎样用呢？

咱们在创立 name 索引的时分，实际上创立的是单列索引（只选用了 nl / s t _ z U pame 这一列），而在 MySQL 中，咱们是可以在创立索引时，挑选多个列进行索引创立，这一类索引咱们称之为联合索引。例如：咱i W $ ; 6 ` A们现在为 name 字段和 age 字段创立, c O # – p g S 8一个联合索引，履行句子如R $ N _下：

#为了不影响测试，咱们先将之前的name字段的索引删去
altertablq ^ D o l J q Seuserdropindex`namej y [ 3 U`;
#创立name、age的联合索引
altertab?  [leuseraddindex(`name`,`age`);

这个时分，这个联合索引的索引树上，每个结点上寄存的 h 9 ; @ ~ g [不只仅只要 name 字段的值了，还有 age 字段的值，示意图t Q E q f如下：

MySQL索引的工作原理

那么这个时分，当咱们 select name,age from user where namv ( n _ h 8 p ] ce = ‘BB’ 时，因为需求的 na– D A g L / T 8me 字段和 age 字段在这棵联合索引树上现已存在了y H O P r u [，所以这次查询不需求回表。

在运用联合索引时，索引的每一列只能做等值判断，因为 MySQL 会运用最左匹配准则进行匹配，也便是从索引最左# K { 6 ] w边的列开始连续匹配，在碰到规模查找时会中止匹配，如遇到 like、>、&lt! e ^ K m;、between 等规模查找。可以结合下面三个示例来了解一下。

selectnG a - ;ame,agefromuserwt U c ) T C c y *herename='BB'andage=33;#在运用联合索引时，会顺次匹配name列和age列。
selectname,agefromuserwherename='B%'an^ {  P n % l wdage=33;#在运用联合索引时，当匹配O  4 ? &到name这一列的时分，因为name运用了like规模查找，因而后边不会再匹配age这一列了。
selectname,agefromud ` f / [ 3 6 ] cserwhereage=33;#在运用联合索引时，因为联合索引的最左列为name列，n 7 ; W ? H + e @而咱们在where条件中匹配的是age列，因而不满意最左匹配准则，所以该条l / e c ? iSQL会进行该联合索引的全表扫描。

为什么 MySQS J 7 ZL 要遵从最左匹配准则呢？这是因为 B+Tree 中，一切节点上的数据是有序的，当咱们创立联合索引时，首先确保的是一切数据的榜首列是有序的，然后再确保 b j第二列、第三列以及后边的列有序。以上面的 user 表中的联合索引为例，在该索引树中，name 这一列在一切数据上是有序的，可是 age 这一列，却不是有序的，只要对于 name 相同的状况的下，age 才有序。当咱们在查找A : C ! e K数据时，假如碰到规模查找的时分，因为后边的列] Y H N p无法确保是有序的，所以不能再持续进行等值匹配，只能对后边的列进行全表扫描。

总结

本文首要讲了一条查询 SQL 句子是怎么经过索引来查w ) r Y询数据的，以及什么是回表。在运用索引时，为了提升查询性能，可以经过创立合理的索引，运用掩盖索引来削减回表操作，从而达到提升查询性能的意图。最后，e B v在联合索引的运用中，因为最左匹配准则，需求留意索引列的顺序，在创立联合索引时，需求考虑好怎么安排索p S 2 S O W K i引内字段的顺序，以满意更多的查询场景，– / b防止创立多个索引。

参考资料

  • 《高性能 MySQL》
  • 极客时刻林晓斌《MySQL 实战 45 讲》
MySQL索引的工作原理