一文搞懂MySQL索引（实现原理加优化实战，面试必问）_索引优化实战教学

前言

本篇文章从数据结构，B+Tree的构建过程，MySQL索引实现，索引为什么那么快，MySQL有哪些索引，聚集索引和二级索引的区别，索引失效的原因，EXPLAIN关键字分析，索引实战，索引的优缺点，什么时候应该加索引，全方面帮助读者理解MySQL索引。

一、提出问题

1. MySQL索引有哪些实现方式
2. 什么是B+Tree索引，B+Tree索引的构建过程，跟二叉树、红黑树的对比
3. 为什么数据库存储用B+tree索引，局部性原理是什么，为什么推荐自增id
4. MySQL有哪些索引，聚集索引，二级索引，覆盖索引区别，回表是什么
5. 怎么加索引，索引的使用场景有哪些，加索引一定好吗
6. 索引失效的场景，引发什么后果
7. EXPLAIN关键字查看MySQL是否使用索引
8. 慢查询优化

接下来会循序渐进，让读者完全搞懂索引是什么

二、MySQL索引有哪些实现方式

MySQL索引实现方式有：B+tree索引、Hash索引、Full-text索引。

我们最常用的是B+tree索引，主键索引（也叫聚簇索引）本身就是一个B+tree索引树，非叶子节点存储主键id，叶子节点为一整行数据，叶子节点之间通过双向链表连接支持范围扫描，一般加的唯一索引，普通索引都是B+tree索引。

Hash索引只能在memory存储引擎下使用，这里不过多描述，优点是查询快，hash取模O(1)检索，缺点不支持范围查询，出现hash冲突性能会降低。

Full-text索引主要对varchar,text加索引，使用倒排索引的方式，与搜索引擎实现方式相似。

三、为什么使用B+tree索引

先说结论，主要因为磁盘读写速度远远低于内存速度，传统的机械硬盘大概慢一万倍，固态硬盘慢100倍，故减少磁盘I/O次数是提升索引性能的重点。

根据局部性原理和磁盘预读，Linux操作系统进行磁盘I/O时，一般顺序读写4KB到内存的Page Cache中，之后再在内存中找到对应的数据返回回去，Mysql的B+tree每个节点为16KB，我们可以把16kb当作磁盘IO的最小单元。

局部性原理表现为：时间局部性和空间局部性。时间局部性是指如果程序中的某条指令一旦执行，则不久之后该指令可能再次被执行；如果某数据被访问，则不久之后该数据可能再次被访问。空间局部性是指一旦程序访问了某个存储单元，则不久之后，其附近的存储单元也将被访问。

那么为什么选择B+tree作为索引呢，B+tree降低了磁盘IO次数吗？为什么不用红黑树或者hash索引呢。

关于B+tree的构建过程和红黑树的构建过程我之前写过一篇文章数据结构与算法，在后半段详细描述了二叉树，AVL树，B+tree的构建过程，这里有模拟数据结构构建过程的网站

红黑树每个节点容纳1个key，树的高度为O(log➋ N)，查询复杂度也是O(log➋ N)，1000000条数据，树的高度为1000，最差需要扫描1000次才能查询到对应数据。

B+tree每个节点容纳M个key，树的高度为O(logm N)，m越大，树高度越低，按照mysql一个page节点存储16kb来算，一个bigint主键是8个字节，一个节点可以容纳大概1000个主键（每个节点还存储了其他隐藏信息帮助节点内部检索），m就是1000，一千万条数据，树的高度大概是3层，只需要3次磁盘I/O加上几百次内存遍历查找，就可以快速定位到数据，这对查询性能的提升的巨大的，如果没有索引而进行全表扫描的话，大概需要上万次磁盘I/O。

故基于局部性原理和磁盘预读，B+tree适合在磁盘文件系统中做检索，红黑树更适合在内存中检索（比如java的hashmap，网络epoll的连接节点存储）。

为什么推荐使用自增ID作为主键呢，B+tree的构建过程是通过分裂和合并保持树的稳定的，如上图，若不是顺序插入的，树会进行频繁的分裂，导致额外的磁盘IO和CPU使用，可以使用此网站数据结构构建过程手动测试下。

另外mysql的除主键外的普通索引的叶子节点都是id，故id越小普通索引的占用磁盘空间越小，故推荐使用int或bigint来做主键（下文详细讲）。

三、Mysql索引有哪些类型

聚集索引，非聚集索引，唯一索引，普通索引，联合索引，覆盖索引

1. 聚集索引

聚集索引，也叫主键索引，简单来说，mysql一张表的所有数据就是一个主键索引，索引的非叶子节点存储主键key，叶子节点存储具体的一行数据，当我们通过id查询数据时就通过此索引检索，若表没有设置主键，mysql会用一个隐藏字段row_id设置为主键索引。

2. 非聚集索引

非聚集索引，也叫辅助索引，二级索引，是指除主键索引外的其他索引，比如我们给一张表加一个索引。

ALTER TABLE `user` 
ADD INDEX `idx_name`(`name` ASC) USING BTREE;
1
2

二级索引的key为我们加的字段，比如name，叶子节点是主键id。

当我们通过二级索引检索数据时，先通过二级索引检索到主键id，再拿着主键id到主键索引中查询到我们想要的数据，这个过程叫做回表

与回表对应的是覆盖索引，即当我们在二级索引树上即查询到我们要的所有数据时，就不需要再回表到主键索引查询数据，这个过程叫覆盖索引，当我们使用EXPLAIN关键字查看执行计划里有Using index就意味着我们使用了覆盖索引。

唯一索引就是有唯一约束，当表字段有两个相同的值插入时会报错。

联合索引就是对多个字段一起加索引，需要注意的是索引字段的顺序，因为联合索引的构建过程本质是先根据第一个字段进行比较排序，再根据第二个字段比较排序，以此类推。。。如果我们直接使用第二个字段查询数据，是无法使用该联合索引的，这就是最左前缀匹配原则。

若where b=1 and c=2 and a =3, 加联合索引(a,b,c)一样可以使用联合索引，mysql索引优化器会自动调整顺序，只要a=3在查询条件即可。

一般我们加联合索引是因为大部分场景都是使用这几个字段进行检索，加一个联合索引可以节省表空间，但必须保证第一个字段一定会使用检索，不然索引就失效了。

四、怎么加索引、加索引一定好吗

一般我们使用navicat可视化界面增加索引，对于varchar字段可以指定索引的键长度，减少索引的key大小，也可以指定索引的正序倒序，或选择多个字段建立索引（即联合索引）

ALTER TABLE `changlf`.`user_test_time` 
ADD INDEX `idx_username`(`username`(20) ASC) USING BTREE;
1
2

那什么场景需要我们加索引呢？加索引有什么缺点呢？

当我们写一个sql时，where后的条件，join on后面的条件，order by排序字段，group by分组字段，在可以检索的地方，都会使用索引，select，update，delete语句在查询条件下都会使用索引，那我们这些都加索引吗？

我们知道，二级索引的key是索引字段，value是主键id，是直接存储在磁盘里的，当我们加太多的索引，会导致表空间增大，增删改的时候需要重建索引降低效率，所以我们一张表一般加3-5个索引比较合适。

哪些字段适合加索引

首先我们加索引肯定是为了检索，平常查询字段常用的字段才会加索引，但是加索引也要注意效率，需要查看索引的区分度。

索引的区分度等于count(distinct 具体的列) / count( * )，表示字段不重复的比例

区分度越大，索引检索的效率越高，唯一索引的区分度是1，而像一些状态字段，性别年龄字段，区分度很低就不适合加索引，大部分都是相同的数据，树比较大小几乎使用不到。

五、索引失效的场景、EXPLAIN关键字查看执行计划

• 索引的失效场景：

1. 联合索引不满足最左前缀原则，刚刚说到必须使用第一个字段进行检索。
2. 对索引字段进行函数计算，如使用concat()，date_format()函数对索引进行修改，索引会失效，一般我们要将查询条件进行格式化使其匹配索引，而不是对索引字段进行修改。
3. like ‘%a%’，左%会导致范围检索，like 'a%'索引可以正常使用。
4. in, or, between and，这些范围检索函数可能会导致索引失效，mysql有索引优化器，当我们检索的数据比较少时，比如十几条，还是会使用索引，当检索到的数据量很大时，mysql认为直接查询主键索引效率更高时，就会放弃使用二级索引。
5. !=, not in, not null，所有取反的检索条件会导致索引失效

• EXPLAIN关键字查看执行计划

这里有一篇文章对EXPLAIN关键字使用讲解的很详细，就不过多讲解了。

大概使用就是在sql前面加上EXPLAIN，就可以查询执行时具体会使用哪些索引了。

六、总结

本文对MySQL索引从构建原理，到各类索引实现，性能调优，优缺点进行全方面讲解，希望对读者了解索引有个清晰的认识。

作者：龙猫帝
原文链接：https://blog.csdn.net/chang_mao/article/details/135955867?spm=1001.2014.3001.5502
版权所有，欢迎保留原文链接进行转载：)