MySQL索引相关概念：聚集索引、回表查询、覆盖索引、索引失效

---

前言

本文主要介绍mysql索引相关内容，包括索引的概念、索引的数据结构、聚集索引、非聚集索引、回表查询、覆盖索引、索引失效等。

---

以下是本篇文章正文内容，下面案例可供参考

一、索引是什么

• 索引是帮助数据库高效获取数据的有序数据结构；
• 提高数据检索的效率，降低数据库的IO成本（减少IO次数，减少IO的量）；
• 通过索引列对数据排序，降低数据排序成本，降低CPU消耗。

简单来说：索引用来提高查询效率，类似于字典；数据存储在磁盘，索引也存在磁盘。

二、索引的底层数据结构

MySQL默认的存储引擎InnoDB采用的是B+树数据结构来存储

• 叶子节点存具体的数据值，非叶子结点只存储指针key，不存数据；
• 一个磁盘块（数据页database）16kb，存储的数据是B树的万倍；
• 3到4层B+树足以支撑千万级的数据存储，key占用的空间越少越好。

一个表理论上索引的个数没有限制，但不是越多越好；一个索引一棵树，数据存一份。

三、聚集索引与回表查询

1.聚集索引

也叫聚簇索引：将数据与索引放在一起，索引结构的叶子结点保存了行数据，必须有且只有一个

聚集索引选取规则：有主键选主键 —> 没有则选唯一键 —> 都没有则自动生成6字节的rowid来存储

2.非聚集索引

也叫二级索引：将数据与索引分开存储，索引结构的叶子节点存储的是对应行的主键，可以有多个

3.回表查询

根据二级索引找到存二级索引的B+树，然后匹配叶子结点的主键值，根据主键值再去找存聚集索引的B+树，找到数据的过程就叫回表。即经过两个B+树查询：二级索引的树–主键索引的树。效率低，应尽量避免回表。

四、覆盖索引

是指查询使用了索引，并且需要返回的列，在该索引中已经全部能找到。

• 比如：有主键id，二级索引name，则二级索引的叶子结点已经存储了id，则可以直接返回
• mysql的超大分页也可用覆盖索引+子查询来解决

select id, name from user where name = ‘alpari’; --覆盖索引
select * from user u1, (select id from user order by id limit 90000000,10) u2 where u1.id = u2.id; --子查询

五、索引创建的原则

• 数据量较大，且查询比较频繁的表
• 控制索引的数量
• 常作为查询条件、排序、分组的字段
• 尽量使用联合索引
• 索引的字段尽量不要为空
• 频繁更新的字段不要索引

六、什么情况索引失效

• 违反最左前缀原则：例有联合索引a,b,c，相当于建立了a,ab,abc三个索引，有效索引为：where a and b ; a and b and c; 顺序可以交换如b and a，mysql会自动优化，不影响。但是如果a and c 则只能用a 的索引，c的索引失效；b或b and c则索引失效。
• 范围查询右边的列，不能用索引：例where a = 1 and b > 1 and c = 1，则只能命中ab索引，c失效
• 不要在索引列上进行运算操作
• 以%开头的like模糊查询
• 字符串不加单引号（类型隐式转化）
• 使用全表扫描比用索引快

总结

本篇仅仅介绍索引相关的概念，对于其中的相关场景，大家可以手动操作看下，比如在mysql中新建索引，使用explain查看sql的执行计划，主要关注其type、key、key_len、extra等，有时间的话会分享一篇mysql的执行计划。