深入浅出mysql索引总结(下) 一文就OK

往期文章推荐

• mysql那些事儿|深入浅出mysql索引(上)
• mysql那些事儿|深入浅出mysql索引(下)
• 为什么Mysql用B+树做索引而不用B-树或红黑树？
• mysql那些事儿|mysql事务隔离机制及其原理
• 校招mysql那些事儿|日志模块binlog/redolog/undolog
• 校招mysql那些事|MVCC原理机制
• mysql那些事儿|mysql锁总结

目录

• 聚集索引和非聚集索引

  • 聚集索引
  • 聚集索引使用场景
  • 非聚集索引
  • 非聚集索引使用场景
• 什么是回表
• 覆盖索引
• 索引失效问题
• 索引最左匹配原则
• 索引总结

聚集索引和非聚集索引

《数据库原理》里面的解释：聚集索引的顺序就是数据的物理存储顺序，而非聚集索引的顺序和数据物理排列无关。因为数据在物理存放时只能有一种排列方式，所以一个表只能有一个聚集索引。在SQL SERVER中，索引是通过二叉树的数据结构来描述的；我们可以如此理解这个两种索引：聚集索引的叶节点就是数据节点，而非聚集索引的叶节点仍然是索引节点，只不过其包含一个指向对应数据块的指针。

聚集索引

聚集索引中键值的逻辑顺序决定了表中相应行的物理顺序。

聚集索引确定表中数据的物理顺序。聚集索引类似于电话簿，后者按姓氏排列数据。由于聚集索引规定数据在表中的物理存储顺序，因此一个表只能包含一个聚集索引。但该索引可以包含多个列（组合索引），就像电话簿按姓氏和名字进行组织一样。

聚集索引对于那些经常要搜索范围值的列特别有效。使用聚集索引找到包含第一个值的行后，便可以确保包含后续索引值的行在物理相邻。

例如，如果应用程序执行 的一个查询经常检索某一日期范围内的记录，则使用聚集索引可以迅速找到包含开始日期的行，然后检索表中所有相邻的行，直到到达结束日期。这样有助于提高此 类查询的性能。

同样，如果对从表中检索的数据进行排序时经常要用到某一列，则可以将该表在该列上聚集（物理排序），避免每次查询该列时都进行排序，从而节省成本。

当索引值唯一时，使用聚集索引查找特定的行也很有效率。例如，使用唯一雇员 ID 列 emp_id 查找特定雇员的最快速的方法，是在 emp_id 列上创建聚集索引或 PRIMARY KEY 约束。

聚集索引使用场景

• 此列包含有限数目的不同值
• 查询的结果返回一个区间的值
• 查询的结果返回某值相同的大量结果集

非聚集索引

一种索引，该索引中索引的逻辑顺序与磁盘上行的物理存储顺序不同。

索引是通过二叉树的数据结构来描述的，我们可以这么理解聚簇索引：索引的叶节点就是数据节点。而非聚簇索引的叶节点仍然是索引节点，只不过有一个指针指向对应的数据块。

非聚集索引指定了表中记录的逻辑顺序，但记录的物理顺序和索引的顺序不一致，聚集索引和非聚集索引都采用了B+树的结构，但非聚集索引的叶子层并不与实际的数据页相重叠，而采用叶子层包含一个指向表中的记录在数据页中的指针的方式。

非聚集索引比聚集索引层次多，添加记录不会引起数据顺序的重组。

非聚集索引使用场景

• 此列包含了大量数目不同的值
• 查询的结束返回的是少量的结果集
• order by 子句中使用了该列

什么是回表

假设，我们有一个主键列为ID的表，表中有字段k，并且在k上有索引。

这个表的建表语句是：

mysql> 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)，两棵树的示例示意图如下

SQL语句 select * from T where k between 3 and 5 执行过程：

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

在这个过程中，回到主键索引树搜索的过程，我们称为回表。可以看到，这个查询过程读了 k 索引树的 3 条记录（步骤 1、3 和 5），回表了两次（步骤 2 和 4）。

也就是说，基于非主键索引的查询需要多扫描一棵索引树。因此，我们在应用中应该尽量使用主键查询。

覆盖索引

如果执行一条SQL语句 select ID from T where k = 3 ，这时只需要查找到iD的值即可，而ID值恰好存在与k索引树上，不需要进行回表。也就是说，在这个查询里面，索引 k 已经“覆盖了”我们的查询需求，我们称为覆盖索引。

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

索引失效问题

• 不在索引列上做任何操作（计算、函数、（自动or手动）类型转换），会导致索引失效而转向全表扫描

EXPLAIN SELECT * FROM employees WHERE name = 'LiLei';
EXPLAIN SELECT * FROMemployees WHERE left(name,3) = 'LiLei';

• 给hire_time增加一个普通索引：

ALTER TABLE `employees`
ADD INDEX `idx_hire_time` (`hire_time`) USING BTREE 

EXPLAIN  select * from employees where date(hire_time) ='2018-09-30';

转化为日期范围查询，会走索引：

EXPLAIN  select * from employees where hire_time >='2018-09-30 00:00:00'  and hire_time <='2018-09-30 23:59:59';

• 存储引擎不能使用索引中范围条件右边的列

EXPLAIN SELECT * FROM employees WHERE name= 'LiLei' AND age = 22 AND position ='manager';
EXPLAIN SELECT * FROM employees WHERE name= 'LiLei' AND age > 22 AND
position ='manager';

• mysql在使用不等于（！=或者<>）的时候无法使用索引会导致全表扫描

EXPLAIN SELECT * FROM employees WHERE name != 'LiLei';

• is null,is not null 也无法使用索引

EXPLAIN SELECT * FROM employees WHERE name is null

• like以通配符开头（'$abc...'）mysql索引失效会变成全表扫描操作

EXPLAIN SELECT * FROM employees WHERE name like '%Lei'

EXPLAIN SELECT * FROMemployees WHERE name like 'Lei%'

问题：解决like'%字符串%'索引不被使用的方法？

1. 使用覆盖索引，查询字段必须是建立覆盖索引字段

EXPLAIN SELECT name,age,position FROM employees WHERE name like '%Lei%';

1. 如果不能使用覆盖索引则可能需要借助搜索引擎

• 字符串不加单引号索引失效

EXPLAIN SELECT * FROM employees WHERE name = '1000';
EXPLAIN SELECT * FROM employees WHERE name = 1000;

• or 连接索引失效

explain select * from user where name = ‘2000’ or age = 20 or pos =‘cxy’;

• order by

正常（索引参与了排序），没有违反最左匹配原则。

explain select * from user where name = 'zhangsan' and age = 20 order by age,pos;

违反最左前缀法则，导致额外的文件排序（会降低性能）。

explain select name,age from user where name = 'zhangsan' order by pos;

• group by

正常（索引参与了排序）。

explain select name,age from user where name = 'zhangsan' group by age;

违反最左前缀法则，导致产生临时表（会降低性能）。

explain select name,age from user where name = 'zhangsan' group by pos,age;

索引最左匹配原则

最左前缀匹配原则：在MySQL建立联合索引时会遵守最左前缀匹配原则，即最左优先，在检索数据时从联合索引的最左边开始匹配。

要想理解联合索引的最左匹配原则，先来理解下索引的底层原理。索引的底层是一颗B+树，那么联合索引的底层也就是一颗B+树，只不过联合索引的B+树节点中存储的是键值。由于构建一棵B+树只能根据一个值来确定索引关系，所以数据库依赖联合索引最左的字段来构建。

举例：创建一个（a,b）的联合索引，那么它的索引树就是下图的样子。

可以看到a的值是有顺序的，1，1，2，2，3，3，而b的值是没有顺序的1，2，1，4，1，2。但是我们又可发现a在等值的情况下，b值又是按顺序排列的，但是这种顺序是相对的。这是因为MySQL创建联合索引的规则是首先会对联合索引的最左边第一个字段排序，在第一个字段的排序基础上，然后在对第二个字段进行排序。所以b=2这种查询条件没有办法利用索引。

索引总结

假设index(a,b,c)

like KK%相当于=常量，%KK和%KK% 相当于范围

文章也会持续更新，可以微信搜索「 迈莫coding 」第一时间阅读。每天分享优质文章、大厂经验、大厂面经，助力面试，是每个程序员值得关注的平台。