深入浅出，一文吃透mysql索引

微客鸟窝

发布于 2021-11-02 16:40:10

4170

发布于 2021-11-02 16:40:10

文章被收录于专栏：Go语言指北Go语言指北

索引是什么

索引是为了提高数据查询效率的数据结构，类似于书的目录一样，可以根据目录而快速找到相关内容。

MySQL 8.0 版本中，InnoDB 存储引擎支持的索引有 B+ 树索引、全文索引、R 树索引，其中，B+ 树索引使用最为广泛。

B+树索引

每一个索引在 InnoDB 里面对应一棵 B+ 树。B+树索引的特点：基于磁盘的平衡树，树非常矮，一般为 3~4 层，所以访问效率非常高，从千万或上亿数据里查询一条数据，只用 3、4 次 I/O。

假设我们有如下表，ID是主键，字段 k 上有索引：

主键索引和非主键索引的示意图如下：

其中R代表一整行的值。

主键索引和非主键索引的区别是：

主键索引的叶子节点存放的是整行数据；
非主键索引的叶子节点存放的是主键的值；
非主键索引也被称为二级索引，而主键索引也被称为聚簇索引。

1、如果查询语句是

select * from table where ID = 100，以主键查询的方式，只需要搜索 ID 这棵 B+ 树。

2、如果查询语句是

select * from table where k = 1，以非主键的查询方式，则需要先搜索 k 索引树，得到 ID=100，再到 ID 索引树搜索一次，这个过程也被称为回表。

MySQL 中 B+ 树索引的管理

命令 EXPLAIN 查看是否使用索引。
查询表 mysql.innodb_index_stats 查看每个索引的大致情况。

字段	释义
database_name	数据库名
table_name	表名
index_name	索引名
last_update	统计信息最后一次更新时间
stat_name	统计信息名
stat_value	统计信息的值
sample_size	采样大小
stat_description	类型说明

查询表 sys.schema_unused_indexes 查看有哪些索引一直未被使用过，可以被废弃。

MySQL5.7 及以上的版本sys模式下
schema_redundant_indexes 和 schema_unused_indexes 两个视图

MySQL 存储数据和索引对象分析

索引组织表

数据的存储分为堆表和索引组织表，目前大部分数据库都支持索引组织表的存储方式。

堆表

如上图，堆表中的数据和索引是分开存储的，索引有序而数据是无序的，索引的叶子节点存的是数据在堆表中的地址。堆表中数据发生变更，其位置也会变，导致索引中的地址都需要更新，所以很影响性能。

索引组织表

数据根据主键排序存放在索引中，主键索引又叫聚集索引。在索引组织表中，数据即索引，索引即数据。InnoDB 存储引擎就是这样的数据组织方式。

二级索引

除了主键索引外，其他的索引都称之为二级索引，或非聚集索引，同样也是一颗 B+ 树索引，它和主键索引不同的是「叶子节点存放的是索引键值、主键值」。

当通过使用二级索引来查询数据时，通过二级索引先找到主键值，再通过主键索引进行查询数据，这种「二级索引通过主键索引进行再一次查询”的操作叫作回表」。

与堆表相比，这种索引组织表这样的二级索引，若有数据发生变更时，其他索引无须进行维护，除非记录的主键发生了修改，所以性能优势会非常明显。

覆盖索引

上面提到了，二级索引的叶子节点存放的是索引键值、主键值，

例如我们有如下表：

create table user (
id int primary key,
name varchar(20),
sex varchar(5),
index(name)
)engine=innodb;

索引覆盖

select id,name from user where name='ls';

能够命中name索引，索引叶子节点存储了主键id，通过name的索引树即可获取id和name，无需回表，符合索引覆盖，效率较高。

回表

select id,name,sex from user where name='ls';

能够命中name索引，索引叶子节点存储了主键id，但sex字段必须回表查询才能获取到，不符合索引覆盖，需要再次通过id值扫码聚集索引获取sex字段，效率会降低。

索引调优

函数索引

从 MySQL 5.7 版本开始，MySQL 开始支持创建函数索引（即索引键是一个函数表达式）。函数索引有两大用处：

优化业务 SQL 性能：

假如我们有一个注册日期字段 register_date，并对其创建了索引，现在有如下条件查询 where DATE_FORMAT(register_date,'%Y-%m') = '2021-10'，那么能不能命中索引呢？

答案是不能，索引只对 register_date 的数据排序，并没有对 DATE_FORMAT(register_date) 排序，因此不能使用到此索引。

我们可以使用函数索引解决这个问题，创建一个DATE_FORMAT(register_date) 的索引。

ALTER TABLE Testtable
ADD INDEX 
idx_func_register_date((DATE_FORMAT(register_date,'%Y-%m')));

配合虚拟列（Generated Column）。

例如有如下表：

CREATE TABLE User (
    userId BIGINT,
    userInfo JSON,
    mobile VARCHAR(255) AS (userInfo->>"$.mobile"),
    PRIMARY KEY(userId),
    UNIQUE KEY idx_mobile(mobile)
);

mobile 列就是一个虚拟列，由后面的函数表达式计算而成，本身这个列不占用任何的存储空间，而索引 idx_mobile 实质是一个函数索引。这样做的好处是在写 SQL 时可以直接使用这个虚拟列，而不用写冗长的函数：

-- 不用虚拟列
SELECT  *  FROM User
WHERE userInfo->>"$.mobile" = '15088888888'

-- 使用虚拟列
SELECT  *  FROM User 
WHERE mobile = '15088888888'

最左前缀原则

B+ 树这种索引结构，可以利用索引的“最左前缀”，来定位记录。

例如我们有字段 a 和 b，都为高频字段，为了减少回表，我们可以建立联合索引 (a,b)，这时不需要单独在 a 上建立索引了。

但是如果查询条件里面只有 b 的语句，是无法使用 (a,b) 这个联合索引的，这时候你不得不维护另外一个索引，如果 a 字段比 b 字段大可以创建 (a,b)、(b) 这两个索引，反之创建 (b,a)、(a) 这两个索引。

普通索引与唯一索引的选择

先说结论：业务代码已经保证不会写入重复数据”的情况下，建议尽量选择普通索引。

查询时：

普通索引，查找到满足条件的第一个记录后，还需要查找下一个记录，直到碰到第一个不满足条件的记录。
唯一索引，由于索引定义了唯一性，查找到第一个满足条件的记录后，就会停止继续检索。

上面的不同之处在性能差距上微乎其微。因为对于数据的读取不仅仅将需要读取的某一条数据从磁盘上读取出来，Innodb的数据是按照页为单位来进行读写的，每页的默认大小为16KB，所以对于普通索引来说，只是多做一次“查找和判断下一条记录”的操作，只需要一次指针寻找和一次计算，操作成本对于现在的 CPU 来说可以忽略不计。

更新时：

普通索引，则是将更新记录在 change buffer，语句执行就结束了。
唯一索引，需要将数据页读入内存，判断到没有冲突，插入这个值，语句执行结束。

「唯一索引的更新不能使用 change buffer，普通索引可以使用到」

什么是 change buffer？

❝

当对数据页进行更新时，如果数据页在内存中则直接更新，如果不在 Innodb 会将更新操作记录在 change buffer 中，免了去磁盘中读取数据页的过程，下次查询的时候，再将数据页读入内存，结合 change buffer 记录来返回数据，同时进行 merge 操作（将 change buffer 中的操作应用到原数据页）。
change buffer 在内存中有拷贝，也会被写入到磁盘上,它是可以持久化的数据的。

❞

对于唯一索引，更新时需要将数据页读取到内存中来判断是否违反了唯一性约束，数据页既然都已经读到内存中了，自然也就不需要 change buffer了；而普通索引，则是将更新记录在 change buffer。由于磁盘IO成本较高，不如使用 change buffer 对性能更加友好。

组合索引

组合索引（Compound Index）是指由多个列所组合而成的 B+树索引。

例如：

对组合索引（a，b），因为其对列 a、b 做了排序，所以此索引可以优化的的 SQL 有：

WHERE a = ?
WHERE a = ? AND b = ?
WHERE b = ? AND a = ?
WHERE a = ? ORDER BY b DESC

索引（a，b）排序不能得出（b，a）排序，所以下面 SQL 不能被优化：

WHERE b = ?
WHERE b = ？ORDER BY a DESC

使用 组合索引 进行 索引覆盖

若查询的字段在二级索引的叶子节点中，则可直接返回结果，无需回表。这种通过组合索引避免回表的优化技术也称为索引覆盖（Covering Index）。

利用组合索引包含多个列的特性，可以实现索引覆盖技术，提升 SQL 的查询性能。

图片及部分相关技术知识点来源于网络搜索，侵权删！

参考资料：

https://kaiwu.lagou.com/course/courseInfo.htm?courseId=869#/detail/pc?id=7324

https://time.geekbang.org/column/article/69236

https://www.cnblogs.com/luizw/p/12943411.html

https://mp.weixin.qq.com/s?__biz=MjM5ODYxMDA5OQ==&mid=2651962587&idx=1&sn=d197aea0090ce93b156e0774c6dc3019&chksm=bd2d09078a5a801138922fb5f2b9bb7fdaace7e594d55f45ce4b3fc25cbb973bbc9b2deb2c31&scene=21#wechat_redirect

https://blog.csdn.net/weixin_39931362/article/details/111695305

https://blog.csdn.net/weixin_39879665/article/details/113907944

本文参与腾讯云自媒体分享计划，分享自微信公众号。

原始发表：2021-10-22，如有侵权请联系 cloudcommunity@tencent.com 删除

https