InnoDB索引

最新推荐文章于 2024-10-09 10:34:09 发布

bhying

最新推荐文章于 2024-10-09 10:34:09 发布

阅读量783

点赞数

分类专栏： mysql 文章标签：数据库 java sql

本文链接：https://blog.csdn.net/bhying/article/details/127210093

版权

mysql 专栏收录该内容

8 篇文章 0 订阅

订阅专栏

每个InnoDB表都有一个聚簇索引，聚簇索引使用B+树构建，叶子节点存储的数据是整行记录。一般情况下，聚簇索引等同于主键索引，当一个表没有创建主键索引时，InnoDB会自动创建一个ROWID字段来构建聚簇索引。InnoDB创建索引的具体规则如下:

1. 在表上定义主键 PRIMARY KEY ， InnoDB 将主键索引用作聚簇索引。

2. 如果表没有定义主键， InnoDB 会选择第一个不为 NULL 的唯一索引列用作聚簇索引。

3. 如果以上两个都没有， InnoDB 会使用一个 6 字节长整型的隐式字段 ROWID 字段构建聚簇索引。该ROWID 字段会在插入新行时自动递增。

除聚簇索引之外的所有索引都称为辅助索引。在中InnoDB，辅助索引中的叶子节点存储的数据是该行的主键值。在检索时，InnoDB使用此主键在聚簇索引中搜索行记录。

下面我们一起来看一看这两种索引的实现：

CREATE TABLE `t_user_innodb` (
     `id` int(11) NOT NULL AUTO_INCREMENT, 
    `username` varchar(20) DEFAULT NULL,
    `age` int(11) DEFAULT NULL, 
    PRIMARY KEY (`id`) USING BTREE,
    KEY `idx_age` (`age`) USING BTREE 
) ENGINE=InnoDB;

InnoDB的数据和索引存储在一个文件 t_user_innodb.ibd 中。 InnoDB 的数据组织方式，是聚簇索引。

主键索引

InnoDB要求表必须有一个主键索引，主键索引的叶子节点会存储数据行。

等值查询数据

select * from t_user_innodb where id=30

1. 先在主键树中从根节点开始检索，将根节点加载到内存，比较30<56 ，走左路。（1次磁盘 IO ）

2. 将左子树节点加载到内存中，比较 20<30<49 ，向下检索。（1次磁盘 IO ）

3. 检索到叶节点，将节点加载到内存中遍历，比较 20<30 ， 30=30 。查找到值等于 30 的索引项，直接可以获取整行数据。将改记录返回给客户端。（1次磁盘 IO ）

磁盘IO次数：3次。

范围查询数据

select * from t_user_innodb where id between 30 and 49;

1. 先在主键树中从根节点开始检索，将根节点加载到内存，比较 30<56 ，走左路。（

1 次磁盘 IO ）

2. 将左子树节点加载到内存中，比较 20<30<49 ，向下检索。（1次磁盘 IO ）

3. 检索到叶节点，将节点加载到内存中遍历比较 20<30 ， 30<=30<49 。查找到值等于 30 的索引项。获取行数据缓存到结果集中。（1次磁盘 IO ）

4. 向后遍历底层叶子链表，将下一个节点加载到内存中，遍历比较， 30<49<=49 ，获取行数据缓存到结果集中。（1次磁盘 IO ）

5. 最后得到 2 条符合筛选条件，将查询结果集返给客户端。

磁盘IO次数：2次+检索叶子节点数量。

辅助索引

除聚簇索引之外的所有索引都称为辅助索引，InnoDB的辅助索引只会存储主键值而非磁盘地址。

以表t_user_innodb的age列为例，age索引的索引结果如下图：

使用辅助索引需要检索两遍索引：首先检索辅助索引获得主键，然后使用主键到主索引中检索获得记录。

等值查询数据

select * from t_user_innodb where age=22;

1. 先在索引树中从根节点开始检索，将根节点加载到内存，比较 22<77 ，走左路。（1次磁盘 IO ）

2. 将左子树节点加载到内存中，比较 22<34 ，向下检索。（1次磁盘 IO ）

3. 检索到叶节点，将节点加载到内存中从前往后遍历比较。（1次磁盘 IO ）

第一项5： 5<22 不符合要求，丢弃。

第二项22：等于22 ，符合要求，获取主键 id=18 ，去主键索引树中检索id=18的数据放入结果集中。（回表：3次磁盘IO ）。

第三项22：等于 22 ，符合要求，获取主键 id=49 ，去主键索引树中检索 id=49 的数据放入结果集中。（回表：3 次磁盘 IO ）

22=22,22=22。查找到值等于30 的索引项，直接可以获取整行数据。将改记录返回给客户端。

4. 向后遍历底层叶子链表，将下一个节点加载到内存中，遍历比较。（1次磁盘 IO ）

第一项34： 34>22 不符合要求，丢弃。查询结束。

5. 最后得到 2 条符合筛选条件，将查询结果集返给客户端。

根据在辅助索引树中获取的主键id ，到主键索引树检索数据的过程称为回表查询：

磁盘IO次数：2次+检索叶子节点数量+记录数*3。

范围查询数据

辅助索引的范围查询流程和等值查询基本一致，先使用辅助索引到叶子节点检索到第一个符合条件的索引项，然后向后遍历，直到遇到第一个不符合条件的索引项，终止。

检索过程中需要将符合筛选条件的id 值，依次到主键索引检索将检索的数据放入结果集中。

最后将查询结果返回客户端。

组合索引

组合索引存储结构

表结构：

CREATE TABLE `t_multiple_index` (
 `id` int(11) NOT NULL AUTO_INCREMENT,
 `a` int(11) DEFAULT NULL, 
 `b` int(11) DEFAULT NULL, 
 `c` varchar(10) DEFAULT NULL, 
 `d` varchar(10) DEFAULT NULL, 
  PRIMARY KEY (`id`) USING BTREE,
  KEY `idx_abc` (`a`,`b`,`c`) 
) ENGINE=InnoDB;

联合索引idx_abc(a,b,c) ，构建的 B+ 树索引树中节点中的索引项按照（a， b ， c ）的顺序从大到小排列，先按照 a 列排序， a 列相同时按照b列排序， b 列相同按照 c 列排序。在最地城的叶子节点中，如果两个索引项的 a ， b ， c 三列都相同，索引项按照主键id 排序：

查找方式

select * from t_multiple_index where a=13 and b=16 and c=4;

1. 先在索引树中从根节点开始检索，将根节点加载到内存，先比较 a 列， a=14 ， 14>13, 走左路。（1 次磁盘IO）

2. 将左子树节点加载到内存中，先比较 a 列， a=13 ，比较 b 列 b=14 ， 14<16 ，走右路，向下检索。（1次磁盘IO ）

3. 达到叶节点，将节点加载到内存中从前往后遍历比较。（1次磁盘 IO ）

第一项（13,14,3,id=4）：先比较 a 列， a=13 ，比较 b 列 b=14 ， b!=16不符合要求，丢弃。

第二项（13,14,4,id=1）：一样的比较方式，a=13 ， b=16 ， c=4 满足筛选条件。取出索引 data 值即主键id=1 ，再去主键索引树中检索 id=1 的数据放入结果集中。（回表： 3 次磁盘 IO ）

第三项（13,14,5,id=3）： a=13 ， b=16 ， c!=4 不符合要求，丢弃。查询结束。

4. 最后得到 1 条符合筛选条件，将查询结果集返给客户端。

最左前缀匹配原则

最左前缀匹配原则和组合索引的索引存储结构和检索方式是有关系的。

在组合索引树中，最底层的叶子节点按照第一列a 列从左到右递增排列，但是 b 列和 c 列是无序的， b 列只有在a 列值相等的情况下小范围内递增有序，而 c 列只能在 a ， b 两列相等的情况下小范围内递增有序。

所以当我们使用 where a=13 and b=16 and c=4 去查询数据的时候， B+ 树会先比较 a 列来确定下一步应该搜索的方向，往左还是往右。如果a 列相同再比较 b 列。但是如果查询条件没有 a 列， B+ 树就不知道第一步应该从哪个节点查起。

组合索引的最左前缀匹配原则：使用组合索引查询时，mysql 会一直向右匹配直至遇到范围查询 (> 、 < 、 between 、 like) 就停止之后的索引匹配

组合索引创建原则

1. 频繁出现在 where 条件中的列，建议创建组合索引。

2. 频繁出现在 order by 和 group by 语句中的列，建议按照顺序去创建组合索引。 order by a,b 需要组合索引列顺序（ a,b ）。如果索引的顺序是（ b,a ），是用不到索引的。

3. 常出现在 select 语句中的列，也建议创建组合索引。

对于第1 种情况和第 3 种情况，组合索引创建的顺序对其来说是等价的，这种情况下组合索引中 的顺序，是很重要的。由于组合索引会使用到最左前缀原则，使用频繁的列在创建索引时排在前 面。

覆盖索引

前面我们提到，根据辅助索引树查询数据时，首先通过辅助索引找到主键值，然后需要再根据主键值到主键索引中找到主键对应的数据。这个过程称为回表。

但回表是否是必须的呢？当然不是必须的。

select中列数据，如果可以直接在辅助索引树上全部获取，也就是说索引树已经“覆盖”了我们的查询需求，这时MySQL就不会白费力气的回表查询，这中现象就是覆盖索引。使用explain工具查看执行计划，可以看到extra中“Using index”，代表使用了覆盖索引。

我们将查询语句修改为以下语句时，执行过程是什么样子呢？

SELECT a,b FROM t_multiple_index

上面的查询语句用到了覆盖索引进行全表扫描。MySQL基于成本考虑，会使用了覆盖索引进行全表扫描。因为覆盖索引相比与主键索引一个索引项占用的空间少，覆盖索引一个叶子节点中的就可以比主键索引存放更多的数据量，相应的存放数据用到的总叶子树很少一些，所以使用覆盖索引可以减少了磁盘IO次数，显著提升查询性能。

索引条件下推ICP

索引条件下推（Index Condition Pushdown）,简称ICP 。是 MySQL5.7对使用索引从表中检索行的一种优化。可以通过参数optimizer_switch 控制 ICP 的开始和关闭。

 mysql> show VARIABLES like 'optimizer_switch'\G; 
#关闭ICP 
SET optimizer_switch = 'index_condition_pushdown=off'; 
#开启ICP 
SET optimizer_switch = 'index_condition_pushdown=on';

不使用ICP，最左前缀的索引条件的比较是在存储引擎层进行的，不满足最左前缀的索引条件和非索引条件的比较是在Server 层进行的。

使用ICP，所有的索引条件的比较是在存储引擎层进行的（包括不满足最左前缀的索引条件），非索引条件的比较是在Server 层进行的。