theme: juejin

前序文章

1. MySQL架构（一）SQL 查询语句是如何执行的？
2. MySQL架构（二）SQL 更新语句是如何执行的？
3. MySQL架构（三）mysql的两阶段提交
4. MySQL索引（一）底层的数据结构
5. MySQl索引（二）如何看懂explain工具信息，使用explain工具来分析索引
6. MySQL索引（三）explain实践，优化 MySQL 数据库查询性能

本文索引优化包含对 MySQL索引（三）explain实践，优化 MySQL 数据库查询性能 的一些补充。

索引优化

创建示例表

CREATE TABLE `employees` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(24) NOT NULL DEFAULT '' COMMENT '姓名',
  `age` int(11) NOT NULL DEFAULT '0' COMMENT '年龄',
  `position` varchar(20) NOT NULL DEFAULT '' COMMENT '职位',
  `hire_time` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '入职时间',
  PRIMARY KEY (`id`),
  KEY `idx_name_age_position` (`name`,`age`,`position`) USING BTREE
) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8 COMMENT='员工记录表';

先创建了一个名为 employees 的表，包含 id、name、age、position 和 hire_time 等字段，表格使用 InnoDB 引擎，字符集为 utf 8，有一个自增的主键 id，并且定义了一个以 name、age 和 position 的组合索引。

插入数据

INSERT INTO employees(name, age, position, hire_time) VALUES('LiLei', 22, 'manager', NOW());
INSERT INTO employees(name, age, position, hire_time) VALUES('HanMeimei', 23, 'dev', NOW());
INSERT INTO employees(name, age, position, hire_time) VALUES('Lucy', 23, 'dev', NOW());

再向 employees 表中插入了示例数据，通过 INSERT 插入太繁琐。可以通过存储过程批量创建数据。

DROP PROCEDURE IF EXISTS insert_emp;
DELIMITER ;;
CREATE PROCEDURE insert_emp()
BEGIN
  DECLARE i INT;
  SET i = 1;
  WHILE(i <= 100000) DO
    INSERT INTO employees(name, age, position) VALUES (CONCAT('Ber', i), i, 'dev');
    SET i = i + 1;
  END WHILE;
END;;
DELIMITER ;
CALL insert_emp();

我们先定义了一个名为 insert_emp 的存储过程，用于循环插入员工信息到 employees 表中。存储过程中使用了一个变量 i 来计数，初始化为 1，然后通过循环插入 100000 条员工信息，每次插入时根据 i 的值生成 name 字段的值，并设置 age 为 i，position 为'dev'。调用了存储过程 insert_emp()，执行插入操作。

在完成数据表创建及一万多条数据插入后，后文中小鱼将通过具体的sql 查询语句来解析索引的各种情况。

索引优化实例

覆盖索引

组合索引中的第一个字段使用范围查找，可能不会走索引查找。

EXPLAIN SELECT * FROM employees WHERE name > "LiLei" AND age = 23 AND position = "dev";
EXPLAIN SELECT * FROM employees WHERE name < "LiLei" AND age = 23 AND position = "dev";

两个语句中都在索引第一个字段使用了范围查找，第一个使用了索引查找，而第二个没有使用索引查找。这是为什么？

原因在于第二个语句中，查询的结果集很大，MySQL 内部决策认为回表查询效率低，不如全表扫描效率快，所以使用了全表查找。

我们也可以通过 force 指令使得第二个查询语句强制走索引。

EXPLAIN SELECT * FROM employees force index(idx_name_age_position) WHERE name < "LiLei" AND age = 23 AND possition = "dev";

我们可以看到，第二个语句也走索引进行查找了，同时扫描行数值也减少了一半。但查询效率真的变高了吗？

我们可以通过dbeaver 工具对比下两个语句的查询耗时。

-- 关闭查询缓存
set global query_cache_size=0;
set global query_cache_type=0;

SELECT * FROM employees WHERE name < 'LiLei';
SELECT * FROM employees force index(idx_name_age_position) WHERE name < 'LiLei';

通过试验可以得知，强制走索引的语句实际花费时间比全表扫描还要长一些。

既然通过试验证明，强制走索引要慢于全表扫描，那么在项目中用到了索引第一个字段使用范围查找的情况该如何优化呢？

答案是覆盖索引，回顾覆盖索引：如何看懂explain工具信息，使用explain工具来分析索引。

EXPLAIN SELECT name,age,position FROM employees WHERE name < 'LiLei' AND age = 22 AND position ='manager';

为什么MySQL 内部决策认为此时索引查找要比全表查找快？

答案是：我们只查询了索引值，可以直接从索引树中找到，不需要再去找主键，从主键索引树中获取其他字段值。

索引下推

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

在字符串模糊查找时，按照最左前缀原则，age 和 position 字段部分数据是无须的，是没办法走索引查询的。而实际我们会发现该语句使用了索引，这里就涉及到索引下推的概念了。

MySQL 5.6 版本开始，引入了索引下推，具体就是在索引遍历过程中，会对索引中包含的字段进行判断，先过滤掉不符合条件的记录，再进行回表。在匹配name 为LiLei 开头后，还会对age 和position 字段进行过滤，剩下符合所有条件的主键id 再去回表查询其他字段，如此可以减少整体的回表的次数。

对于 innodb 引擎的表，索引下推只能对二级索引生效，因为innodb 的主键索引为聚簇索引，索引树的叶子节点保存的是整行数据（所谓回表查询是根据主键id 去主键索引进行查找），索引下推并不会起到减少查询全行数据的效果。

like %Li% 范围查找一般不会使用索引下推优化。

原因大概是 Mysql 内部决策时认为该范围查找过滤后的结果集太大，而 like KK% 绝大多数情况下过滤后的结果集比较小，所以 Mysql 内部决策时给 like KK% 使用了索引下推优化。

当然对于MySQL 来说，这也不是绝对的，在某些情况下 like KK% 也可能不会使用索引下推优化，主要由MySQL 内部决策那种方式更有效率。

使用合适的索引

EXPLAIN SELECT * FROM employees WHERE name < 'Li';

1. 针对二级索引需要回表查询其他字段的情况，可以使用覆盖索引优化。

EXPLAIN SELECT name,age,position FROM employees WHERE name < 'Li';

采用覆盖索引优化的目的起始在于减少回表查询的次数，既减少通过主键索引树查找其他字段的次数，从而达到优化查询速率的目的。

1. ORDER BY 和 GROUP BY

EXPLAIN SELECT * FROM employees WHERE name = 'LiLei' AND position = 'dev' ORDER BY age;

根据最左前缀原则，我们知道 name 字段是可以走索引查找的。因为语句对age 字段进行了排序，mysql 可以直接通过name 字段过滤索引找到符合索引条件的数据行后，再通过where 中其他条件过滤结果集就可以，所以该语句分析中 Extra 字段没有 using filesort。

EXPLAIN SELECT * FROM employees WHERE name = 'LiLei' ORDER BY position;

同样，这条查询语句也仅使用了索引 name 字段，但由于在排序的时候跳过了age 字段，所以分析中 Extra 字段里有 using filesort，表示使用了文件排序。

EXPLAIN SELECT * FROM employees WHERE name = 'LiLei' ORDER BY age,position;

但如果不跳过 age 字段，同样不会使用文件排序，如这条语句所示。

EXPLAIN SELECT * FROM employees WHERE name = 'LiLei' ORDER BY position,age;

同样，如果我们调换一下排序的顺序，发现也会使用文件排序，因为排序的顺序是不符合组合索引顺序的。

EXPLAIN SELECT * FROM employees WHERE name = 'LiLei' AND age = 22 ORDER BY position,age;

同样使用上一个排序顺序，如果age 变为常量，则不会使用文件排序，MySQL 以及作了优化，不会对常量数据在进行排序，即与 ORDER BY age, position 、ORDER BY position 效果是一样的。

EXPLAIN SELECT * FROM employees WHERE name = 'LiLei' ORDER BY age ASC,position DESC;

排序顺序与组合索引顺序一致，但是使用降序排序，我们发现也会使用文件排序，这是因为索引排序是升序的，ORDER BY 的排序方式与索引排序方式不一致。

对于索引排序方式，MySQL 8 版本中可以设置降序索引。

EXPLAIN SELECT * FROM employees WHERE name IN ('LiLei','zhangsan') ORDER BY age, position;

如果使用多个相等条件，根据索引查询得到的数据集中，age 字段是无序的，MySQL 也会使用到文件排序。

EXPLAIN SELECT * FROM employees WHERE name < 'Li' ORDER BY name;
--- 覆盖索引优化
EXPLAIN SELECT name,age,position FROM employees WHERE name < 'Li' ORDER BY name;

与上文提到的范围查找一样，在使用 ORDER BY 进行排序时，也是可以通过覆盖索引进行优化。

Using filesort 文件排序原理详解

Filesort文件排序有两种方式：单路排序和双路排序。

• 单路排序是将满足条件的行的字段一次性取出并在排序缓冲区进行排序。
  • 通过 trace 工具可以查看到 sort_mode 信息里显示 <sort_key, additional_fields> 或者 <sort_key, packed_additional_fields>
• 双路排序则是根据条件先取出需要排序的字段和行数据的行 ID，在排序缓冲区进行排序后再取回其他需要的字段。
  • 通过 trace 工具可以看到 sort_mode 信息里显示 <sort_key, rowid>

Filesort 文件排序是如何区分这两种方式的？

MySQL 内部通过比较查询字段的总长度和系统变量 max_length_for_sort_data 来判断使用哪种排序方式。 - 如果字段总长度小于 max_length_for_sort_data，则使用单路排序模式； - 如果字段总长度大于 max_length_for_sort_data，则使用双路排序模式。 max_length_for_sort_data 默认长度为 1024 字节。

ORDER BY、GROUP BY 总结

1. MySQL 支持两种排序方式：filesort 和 index。Using index 表示 MySQL 可以通过扫描索引本身完成排序，这种方式效率较高；而 using filesort 则需要通过在磁盘上进行排序，效率较低。
2. 使用 Using index 的情况有两种：
   • ORDER BY 子句使用了索引的最左前列。
   • 通过 where 子句与 order by 子句的条件列组合，可以满足索引的最左前列。
3. 尽量在索引列上完成排序，并满足最左前缀原则。
4. 如果 order by 的条件不在索引列上，将会使用 filesort 进行排序。
5. 如果满足查询要求，尽量使用覆盖索引，即索引包含了查询涉及的所有字段，避免进行额外的数据检索操作。
6. group by 和 order by 类似，本文就没有给出 GROUP BY 的实例，GROUP BY 实际上是在排序之后再进行分组，也要遵循索引的最左前缀法则。
   • 对于 group by 的优化，如果不需要排序结果，可以添加 order by null 来禁止排序。
   • 另外，要注意 where 子句的条件优先于 having 子句，尽量将限定条件写在 where 子句中而不是 having 子句中。

索引设计原则

1. 建表后再建索引：开发初期业务功能是相对不稳定的，而索引属于较上层的优化手段，并且需要对现有数据进行分析才能设计出较好的索引结构。所以在建立表结构之后，应该等到主体业务功能开发完毕后，再分析涉及该表的 SQL 语句，并据此建立索引。
2. 组合索引应当尽可能地覆盖查询条件：设计组合索引时，尽量包含 SQL 语句中的 where、order by 和 group by 字段，并尽可能地满足最左前缀原则。
3. 避免在小基数字段上建立索引：小基数字段指的是字段的值较少且有限，即字段有多少不同的值。比如性别（男、女）对应的字段基数就是 2。建立索引对于这些字段来说意义不大，因为无法进行快速地进行二分查找（B+树查找就是二分查找过程）。
4. 尽量对字段类型较小的字段设计索引：类型较小所占用的磁盘空间也小，在搜索索引时效率相对高一些。
5. 使用前缀索引处理长字符串：对于较大的字符串类型字段，可以考虑使用前缀索引。例如，可以只针对字段的前几个字符建立索引，以减少磁盘空间占用。比如对于 varchar(255) 类型我们可以对前 20 个字符建立索引 KEY index (name (20), age, position)，在 WHERE 条件中也需要通过前 20 个字符搜索。但在使用 ORDER BY 排序和 GROUP BY 时，是无法使用前缀索引的。
6. 解决 where 和 order by 冲突时优先考虑 where：当 where 条件和 order by 同时存在索引设计冲突时，优先考虑让 where 条件使用索引进行快速筛选，再进行排序。先进行 WHERE 筛选得到的数据集一般小很多，再进行排序成本就会小很多。
7. 基于慢 SQL 查询进行优化：通过监控慢查询，并针对这些慢查询进行特定的索引优化。根据慢查询的特点，调整索引设计来提升性能。