详解Mysql---索引

前言

mysql的innodb引擎主要有B+树索引和hash索引，当然在我们工作中大多数场景使用的都是B+树索引，所以本文也只是介绍B+树。主要内容包含以下几点：

• 聚簇索引的逻辑结构以及查询过程详解
• 二级索引的逻辑结构以及查询过程详解
• 联合索引相关的知识点
• 索引条件下推（index condition pushdown）

事例数据结构以及所有的数据

 

聚簇索引

图1-聚簇索引逻辑结构

• 非叶子节点

         节点与节点之间由双向链表相连，非叶子节点节点内部会有多条目录项,目录项与目录项之前是按照主键值的大小以单向链表相连，每个目录项实际有两部分组成主键值和页号,主键范围​​​​​​在对应的页号以此类推；

        以图1为例,根节点中的id值在[1,17)范围的数据都在id=1所对应的页号2中

• 叶子节点

       叶子几点与叶子节点之间由双向链表相连，叶子节点保存的都是完整数据行

• 查询过程

        以查询id=58的值为例，在页0中从头开始遍历，id=1对应的范围是[1,17)不满足，一直遍历到id=49对应的范围是[49,+)所以在页5中;

        在页5中重复页0的查询操作最终找到id=58在id=57对应范围[57,61)中，及页20中；

        在页20中遍历最终找到id=58的数据

二级索引

图2 - 二级索引逻辑结构图

•  非叶子节点

         节点与节点之间由双向链表相连，非叶子节点节点内部会有多条目录项,目录项与目录项之前是按照索引列的大小以单向链表相连，每个目录项实际有两部分组成索引列,主键值以及页号,索引值范围​​​​​​在对应的页号以此类推；

        以图1为例,根节点中的索引值name在[1,17)范围的数据都在name=ak所对应的页号2中

• 叶子节点

       叶子几点与叶子节点之间由双向链表相连，叶子节点保存的是索引值name和主键值id

• 查询过程

        以查询name=yq的值为例，在页0中从头开始遍历，name=ak对应的范围是[ak,hh)不满足，一直遍历到，name=wp对应的范围是[wp,+)所以在页5中;

        在页5中重复页0的查询操作最终找到name=yq,在name=yh对应范围[yh,yx)中，及页20中；

        在页20中遍历最终找到name=yq的数据有两条，并且按照主键排序

        补充：在页中（无论主键还是二级索引）的查找实际是通过数据槽进行二分查找，并不是遍历整个页的数据

联合索引

        联合索引属于二级索引，只是一种特殊的形式，它的索引值包含了多个列；如联合索引索引idx_key_part(key1,key2,key3); 

        实际的存储结构也和二级索引一致，区别在于存储了多个列的值，按照key1值升序，key1相同时按照key2升序以此类推；

        联合索引命中不一定要where条件中同时存在key1,key2,key3，但是满足最左匹配原则;

查询条件	是否命中索引	使用列
where key1 ='aa'	是	key1
where key1 like 'aa%'	是	key1
where key2 like 'aa%'	否
where key2 = 'aa'	否
where key1 = 'aa'  and  key2 like 'aa%'	是	key1,key2
where key1 = 'aa'  and  key3 like 'aa%'	是	key1
where key1 = 'aa'  and  key2 = 'aa'  and key3 ='aa'	是	key1,key2

索引条件下推（index condition pushdown）

        mysql分为数据层和存储引擎层，当我们通过索引查询+未命中索引条件查询时正常的步骤是:

        1、通过索引查询到对应的id；

        2、通过二级索引查到id进行回表操作查询，返回数据server层；

        3、server层判断是否满足其他条件；

        在第1步时候可能存在能判断出未命中索引的条件是否满足从而减少回表的数据量

        举例: 联合索引索引idx_key_part(key1,key2,key3);   查询语句 select * from xxx where key1 ='aa' and  key3 like'%aa%';

        因为idx_key_part是联合索引，最左匹配原则key1='aa'会命中该索引，但是  key3 like'%aa%'并不会命中。由于是联合索引

        key1,key2,key3的值在索引中都有存储，所以就可以直接存储引擎层判断出key3 like'%aa%'是否满足。

---

内容主要参考《MYSQL是这样运行的》一书