MySQL - 索引优化-阿里云开发者社区

1. 回表查询

首先，从 InnoDB 说起，InnoDB 的索引有两种，聚簇索引（clustered index）和辅助索引（secondary index）。

1.1 聚簇索引

聚集索引的 B+Tree 中的叶子节点存放的是整张表的行记录数据，MySQL InnoDB 类型的表必须明确声明一个主键。

若表定义了 PK ，那么 PK 就是聚簇索引；
若表没有定义 PK，那么第一个 not null unique 列就是聚簇索引
否则 InnoDB 会创建一个隐藏的 row-id 作为聚集索引。

因此PK查询非常快，直接定位行记录。

1.2 辅助索引

辅助索引的叶子节点并不包含行记录的全部数据，而是存储相应行数据的聚集索引键，即主键。

当通过辅助索引来查询数据时，InnoDB存储引擎会遍历辅助索引找到主键，然后再通过主键在聚集索引中找到完整的行记录数据。

1.3 执行过程

如果通过辅助索引所要查询的信息在包含在辅助索引中，就不会再通过主键索引的值去数据库中查询，而是直接可以返回结果，如果辅助索引中的信息不完整，则会通过主键索引去获取数据信息，这种情况就被称为回表查询。

先进行一次B+Tree查找，通过普通索引查找lisi对应的数据（叶子节点中）；
再通过叶子节点中，data域中保存的lisi对应的主键值，进行一次B+Tree查找，找到对应记录行；
将数据查找出来。

假如 id 为主键索引，username 为普通索引，通过主键 id 可以查询到一条记录，如果通过 username 查询主键 id 的值时，就不会发生回表，因为辅助索引 username 与主键索引 id 是绑定在一起的.

select id, username from table where username = 'value';

但是如果通过 username 查询 password 的话，就会发生回表，先要根据 username 查询到主键 id，在根据主键 id 获取当前记录的完整信息，然后将 password 查出返回，这就发生了回表。

select password from table where username = 'value';

如果想要通过 username 获取 password 而不发生回表，则可以把 username 和 password 做组合索引，这样就可以通过 username 直接获取 password 而不会发生回表了。

回表查询，先定位主键值，再定位行记录，它的性能较扫一遍索引树更低，应为两次查找Tree，磁盘IO较多。

也就是说一般情况下，只要使用普通索引，并且 select 字段不属于索引，单次普通索引 Tree 查找，无法获取满足的数据，会进行第二次 Tree 查找。

2. 覆盖索引

2.1 什么是覆盖索引

借用一下 SQL-Server 官网的说法：

MySQL官网，类似的说法出现在 explain 查询计划优化章节，即 explain 的输出结果 Extra 字段为 Using index 时，能够触发索引覆盖。

不管是SQL-Server官网，还是MySQL官网，都表达了：只需要在一棵索引树上就能获取 SQL 所需的所有列数据，无需回表，速度更快。

覆盖索引一般针对的是辅助索引，整个査询结果只通过辅助索引就能拿到结果，不需要通过辅助索引树找到主键，再通过主键去主键索引树里获取其它字段值。

覆盖索引是一种数据查询方式，不是索引类型；
在索引数据结构中，通过索引值可以直接找到要查询字段的值，而不需要通过主键值回表查询，那么就叫覆盖索引；
查询的字段被使用到的索引树全部覆盖到。

2.2 如何实现索引覆盖？

常见的方法是：将被查询的字段，建立到联合索引里去。

CREATE TABLE `user` (
  `id` int(11) NOT NULL AUTO_INCREMENT COMMENT '主键ID',
  `name` varchar(32) NOT NULL DEFAULT '' COMMENT '名称',
  `age` int(11) NOT NULL COMMENT '年龄',
  `sex` tinyint(4) NOT NULL DEFAULT '0' COMMENT '性别',
  PRIMARY KEY (`id`),
  KEY `idx_name` (`name`) USING BTREE
) ENGINE=InnoDB AUTO_INCREMENT=3 DEFAULT CHARSET=utf8mb4;

第一个 SQL 语句：

explain select id, name from user where name='shenjian';

Extra：Using index。

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

第二个 SQL 语句：

explain select id, name, sex from user where name='shenjian';

Extra：Using index condition。

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

如果把(name)单列索引升级为联合索引(name, sex)就不同了。

create index idx_name_sex on user(name, sex);

可以看到：

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

都能够命中索引覆盖，无需回表。

3. 最左前缀原则

MySQL建立联合索引时会遵守最左前缀匹配原则，即最左优先，在检索数据时从联合索引的最左边开始匹配，以最左边为起点任何连续的索引都能匹配上，如果从索引的第二列开始查找，索引将失效。

CREATE TABLE `user` (
  `id` int(11) NOT NULL AUTO_INCREMENT COMMENT '主键ID',
  `name` varchar(32) NOT NULL DEFAULT '' COMMENT '名称',
  `age` int(11) NOT NULL COMMENT '年龄',
  `sex` tinyint(4) NOT NULL DEFAULT '0' COMMENT '性别',
  PRIMARY KEY (`id`),
  KEY `idx_name_age_sex` (`name`, `age`, `sex`) USING BTREE
) ENGINE=InnoDB AUTO_INCREMENT=3 DEFAULT CHARSET=utf8mb4;

4. LIKE 查询

面试题：MySQL在使用like模糊查询时，索引能不能起作用？

回答：MySQL 在使用 like 模糊查询时，索引是可以被使用的，只有把%字符写在后面才会使用到索引。

# 不起作用
select * from user where name like '%张三%';
# 起作用
select * from user where name like '张三%';
# 不起作用
select * from user where name like '%张三';

5. NULL 查询

面试题：如果 MySQL 表的某一列含有 NULL 值，那么包含该列的索引是否有效？

对 MySQL 来说，NULL 是一个特殊的值，从概念上讲，NULL 意味着“一个未知值”，它的处理方式与其他值有些不同。比如：不能使用 =，<，> 这样的运算符，对 NULL 做算术运算的结果都是 NULL，count() 时不会包括 NULL 行等，NULL 比空字符串需要更多的存储空间等。

NULL 列需要增加额外空间来记录其值是否为 NULL。对于 MyISAM 表，每一个空列额外占用一位，四舍五入到最接近的字节。

虽然 MySQL 可以在含有 NULL 的列上使用索引，但 NULL 和其他数据还是有区别的，不建议列上允许为 NULL。最好设置 NOT NULL，并给一个默认值，比如 0 和 ‘’ 空字符串等，如果是 datetime 类型，也可以设置系统当前时间或某个固定的特殊值，例如’1970-01-01 00:00:00’。

总结：

在设计数据库的时候尽量还是给字段的默认值：

比如int、bigint类型默认值为-1/0
比如varchar类型默认值为空串
bigdecimal类型为0等等。

NULL值会有不少坑：

count(字段NULL) 会过滤统计的数据，sum() 这些函数也会
使用 >、< 的时候也会过滤掉为 NULL 的数据
group by 的时候会把所有为 NULL 的数据合并，可以随机生成UUID解决

6. 索引与排序

6.1 MySQL排序简介

MySQL 查询支持 filesort 和 index 两种方式的排序。

6.1.1 index

通过有序索引顺序扫描直接返回有序数据，不需要额外的排序，操作效率较高。

6.1.2 filesort

是先把结果查出，然后在缓存或磁盘进行排序操作，效率较低。

filesort 有两种排序算法：双路排序和单路排序。

双路排序

需要两次磁盘扫描读取，最终得到用户数据。第一次将排序字段读取出来，然后排序；第二次去读取其他字段数据。

单路排序

从磁盘查询所需的所有列数据，然后在内存排序将结果返回。如果查询数据超出缓存 sort_buffer，会导致多次磁盘读取操作，并创建临时表，最后产生了多次 IO，反而会增加负担。解决方案：少使用 select *；增加 sort_buffer_size 容量和 max_length_for_sort_data 容量。

6.1.3 优化

如果我们 explain 分析 SQL，结果中 Extra 属性显示 Using filesort，表示使用了 filesort 排序方式，需要优化。如果 Extra 属性显示 Using index 时，表示覆盖索引，也表示所有操作在索引上完成，也可以使用 index 排序方式，建议大家尽可能采用覆盖索引。

优化：尽量减少额外排序，通过索引直接返回有序的数据。where 条件和 order by 使用了相同的索引，并且 order by 的顺序和索引顺序相同，并且 order by 的字段都是升序或者降序，否则肯定需要额外的排序操作，这样就会出现 filesort。

6.2 filesort、index 排序场景

6.2.1 index 方式的排序

ORDER BY 子句索引列组合满足索引最左前列

// 对应(id)、(id, name) 索引有效
explain select id from user order by id;

WHERE子句 + ORDER BY子句索引列组合满足索引最左前列

// 对应 (age,name)索引
explain select id from user where age=18 order by name;

6.2.2 filesort 方式的排序

对索引列同时使用了 ASC 和 DESC

// 对应 (age,name)索引
explain select id from user order by age asc, name desc;

WHERE 子句和 ORDER BY 子句满足最左前缀，但 where 子句使用了范围查询（例如 >、<、in 等）

// 对应 (age,name)索引
explain select id from user where age>10 order by name;

ORDER BY 或者 WHERE + ORDER BY 索引列没有满足索引最左前列

// 对应(age,name)索引
explain select id from user order by name;

使用了不同的索引，MySQL 每次只采用一个索引，ORDER BY 涉及了两个索引

// 对应(name)、(age)两个索引
explain select id from user order by name, age;

WHERE 子句与 ORDER BY 子句，使用了不同的索引

// 对应 (name)、(age)索引
explain select id from user where name='tom' order by age;

WHERE 子句或者 ORDER BY 子句中索引列使用了表达式，包括函数表达式

// 对应(age)索引
explain select id from user order by abs(age);

7. 索引失效场景

7.1 查询条件包含or，可能导致索引失效

若or的字段都有索引则会走。
若or的字段包含没有索引的，那么没有索引的字段会进行全表扫描。

7.2 复合索引未用左列字段

在检索数据时从联合索引的最左边开始匹配。所以当我们创建一个联合索引的时候，如(key1, key2, key3)，相当于创建了（key1）、(key1,key2)、(key1,key2,key3)三个索引，这就是最左匹配原则。

联合索引不满足最左原则，索引一般会失效，但是这个还跟 Mysql 优化器有关的。

7.3 like查询以%开头，索引失效

7.4 类型转换，索引失效

因为类型转换后改变了值，索引的数据结构只能对原值做索引。
例如字符串不加单引号，会导致 MySQL 做隐式的类型转换导致索引失效。

7.5 对索引列运算（如，+、-、*、/），索引失效

运算后改变了值，索引的数据结构只能对原值做索引。

7.6 在索引列上使用 MySQL 的内置函数，索引失效

使用了函数后改变了值，索引的数据结构只能对原值做索引。

7.7 索引字段上使用（!= 、 < >、not in）时，可能会导致索引失效

not in 或者 != 会导致索引失效并不是绝对的，对于数据较为均匀的场景是会失效的，但是如果业务数据严重不均的字段加了索引的话是不一定失效的，MySQL 自己会做判断，并不是绝对判定不使用索引。

比如表A 性别列有男10000条、女20条，当 sex != ‘男’ 是可以使用索引的同样的如果你 sex = ‘男’ 反而不会使用索引，MySQL 自己会选择最优的检索方式。

7.8 索引字段上使用 is null、is not null，可能导致索引失效

在 MySQL 中，含有空值的列很难进行查询优化，因为它们使得索引、索引的统计信息以及比较运算更加复杂。

7.9 左连接查询或者右连接查询查询关联的字段编码格式不一样，可能导致索引失效

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

联合索引中范围之后的索引将会失效。

B+树排放顺序：会先按照第一层索引排序，如果第一层数据一样，则会按照第二层排序，如果第二层还是一样，则会继续按照第三层排序。

MySQL 索引底层原理以及为什么范围之后全失效

7.11 MySQL 估计使用全表扫描要比使用索引快，则不使用索引

当表的索引被查询，会使用最好的索引，除非优化器使用全表扫描更有效。优化器优化成全表扫描取决与使用最好索引查出来的数据是否超过表的30%的数据。

不要给’性别’等增加索引。如果某个数据列里包含了均是 “0/1” 或 “Y/N” 等值，即包含着许多重复的值，就算为它建立了索引，索引效果不会太好，还可能导致全表扫描。

MySQL 出于效率与成本考虑，估算全表扫描与使用索引，哪个执行快。这跟它的优化器有关，来看一下它的逻辑架构图：

MySQL - 索引优化