MySQL-深入浅出索引

Huuaaaaa

已于 2022-12-24 21:53:45 修改

阅读量74

点赞数 1

分类专栏： MySQL 文章标签： mysql

于 2022-12-24 00:42:47 首次发布

本文链接：https://blog.csdn.net/Huuaaaaa/article/details/128414329

版权

MySQL 专栏收录该内容

7 篇文章 0 订阅

订阅专栏

InnoDB 的索引模型

在InnoDB中，表都是根据主键顺序以索引的形式存放的，这种存储方式的表称为索引组织表。InnoDB使用B+树索引模型，所以数据都是存储在B+树中的。 每⼀个索引在InnoDB里面都对应⼀棵B+树。

假设，⼀个主键列为ID的表，表中有字段k，并且在k上有索引，建表语句是：

create table T(
id int primary key,
k int not null,
name varchar(16),
index (k) 
)engine=InnoDB;

设表中R1~R5的(ID,k)值分别为： (100,1)、(200,2)、(300,3)、(500,5)、(600,6)；该引擎下所生成的两个B+树分别为：

主键索引：主键索引的叶子节点存的是整⾏数据。在InnoDB⾥，主键索引也被称为聚簇索引。

非主键索引：非主键索引的叶子节点内容是主键的值。在InnoDB⾥，非主键索引也被称为⼆级索引。

基于上图的两种索引，如果执行：

select * from T where ID=500；

会使用主键查询方式，则只需要搜索ID这棵B+树；

select * from T where k=5 ；

会使用普通索引查询方式，则需要先搜索k索引树，得到ID的值为500，再到ID索引树搜索⼀次，这个过程称为回表。

因此，基于非主键索引的查询需要多扫描⼀棵索引树。在应用中应该尽量使用主键查询。

继续创建下表：

create table T (
ID int primary key,
k int NOT NULL DEFAULT 0,
s varchar(16) NOT NULL DEFAULT '',
index k(k) 
)engine=InnoDB;

insert into T values(100,1, 'aa'),(200,2,'bb'),(300,3,'cc'),(500,5,'ee'),(600,6,'ff'),(700,7,'gg');

该表的索引结构如下所示：

如果执行：

select * from T where k between 3 and 5；

该语句的执行流程为：

在k索引树上找到k=3的记录，取得 ID = 300；
再到ID索引树（主键索引）查到ID=300对应的R3；
在k索引树取下⼀个值k=5，取得ID=500；
再回到ID索引树查到ID=500对应的R4；
在k索引树取下⼀个值k=6，不满足条件，循环结束。

回到主键索引B＋树搜索的过程上面已经提到过了，称为回表操作。这个查询过程读了k索引树的3条记录（步骤1、3和 5），回表了两次（步骤2和4）。

如果执行：

select ID from T where k between 3 and 5；

注意区别，该语句只需要查ID的值，⽽ID的值已经在k索引树上了，因此可以直接提供查询结果，不需要回表。也就是说，在这个查询里面，索引 k 已经“覆盖了”我们的查询需求，称为覆盖索引。

覆盖索引可以减少树的搜索次数，显著提升查询性能，所以使用覆盖索引是⼀个常用的性能优化手段。

创建如下公民信息表：

CREATE TABLE `tuser` (
`id` int(11) NOT NULL,
`id_card` varchar(32) DEFAULT NULL,
`name` varchar(32) DEFAULT NULL,
`age` int(11) DEFAULT NULL,
`ismale` tinyint(1) DEFAULT NULL,
PRIMARY KEY (`id`),
KEY `id_card` (`id_card`),        
KEY `name_age` (`name`,`age`)    // 联合索引
) ENGINE=InnoDB

用（name,age）建立如下联合索引：

索引项是按照索引定义里面出现的字段顺序排序的，当你的逻辑需求是查到所有名字是“张三”的人时，可以快速定位到ID4，然后向后遍历得到所有需要的结果。

如果你要查的是所有名字第⼀个字是“张”的人，即SQL语句是： where name like ‘张%’ ，这时也能够用上这个索引，查找到第⼀个符合条件的记录是ID3，然后向后遍历，直到不满足条件为⽌。

因此B+树这种索引结构，可以利⽤索引的“最左前缀”，来定位记录,也称为最左前缀原则。

执行如下语句：

select * from tuser where name like '张%' and age=10 and ismale=1；

         根据最左前缀原则，语句在搜索索引树的时候，只能用 “张”，找到第⼀个满足条件的记录，也就是ID3。

        在MySQL 5.6之前，只能从ID3开始⼀个个回表。到主键索引上找出数据行，再对比字段值。

        MySQL 5.6 引入的索引下推优化，可以在索引遍历过程中，对索引中包含的字段先做判断，直接过滤掉不满足条件的记录，减少回表次数。

没有使用索引下推时，一个个拿到联合索引上的符合的主键值也就是ID，然后转到主键索引上继续匹配age, 和 ismale 字段（回表四次）：

使用索引下推时，对索引中包含的字段先做判断，也就是基于（name , age），该联合索引包含的字段age ，过滤掉 age不符合的情况，再进行回表（回表两次）：

索引维护

什么是自增主键：

自增主键是指自增列上定义的主键，在建表语句中⼀般是这么定义的： NOT NULL PRIMARY KEY AUTO_INCREMENT。 插入新记录的时候可以不指定ID的值，系统会获取当前ID最大值加1作为下⼀条记录的ID值。

也就是说，自增主键的插入数据模式，正符合了递增插入的场景。每次插入⼀条新记录，都是追加操作，不涉及到挪动其他记录，也不会触发叶子节点的分裂。

适用自增主键：

        假设表中确实有⼀个唯⼀字段，例如字符串类型的身份证号，那应该用身份证号做主键，还是用自增字段做主键：

        由于每个非主键索引的叶子节点上都是主键的值。如果用身份证号做主键，那么每个⼆级索引的叶子节点占用约20个字节(身份证号字符串占用字节数) ；

        如果用整型做主键，则只要4个字节，如果是⻓整型（bigint）则是8个字节。显然，主键⻓度越小，普通索引的叶⼦节点就越小，普通索引占⽤的空间也就越小。 所以，从性能和存储空间方面考量，自增主键往往是更合理的选择。

不适用自增主键：