MySQL索引的工作原理


扫描下方二维码或许微信查找大众号菜鸟飞呀飞,即可关注微信大众号,阅览更多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索引的工作原理

发表评论

提供最优质的资源集合

立即查看 了解详情