MySQL索引优化实战

一、创建包含十万条数据的测试数据库

参考文章：mp.weixin.qq.com/s/7LiDKcVjJ…

表结构如下：

DROP TABLE IF EXISTS `test_user`;
CREATE TABLE `test_user`  (
  `id` int(0) NOT NULL AUTO_INCREMENT COMMENT '主键id',
  `user_id` varchar(36) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NOT NULL COMMENT '用户id',
  `user_name` varchar(30) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NOT NULL COMMENT '用户名称',
  `phone` varchar(20) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NOT NULL COMMENT '手机号码',
  `lan_id` int(0) NOT NULL COMMENT '本地网',
  `region_id` int(0) NOT NULL COMMENT '区域',
  `create_time` datetime(0) NOT NULL COMMENT '创建时间',
  PRIMARY KEY (`id`) USING BTREE,
  INDEX `idx_user_id`(`user_id`) USING BTREE,
  INDEX `idx_phone_lan_region`(`phone`, `lan_id`, `region_id`) USING BTREE,
  INDEX `idx_create_time`(`create_time`) USING BTREE
) ENGINE = InnoDB AUTO_INCREMENT = 48970 CHARACTER SET = utf8mb4 COLLATE = utf8mb4_0900_ai_ci ROW_FORMAT = Dynamic;

表中数据总量：

二、索引优化实战

1、没有任何索引

select SQL_NO_CACHE * from test_user where phone='13462451077' and lan_id=434 and region_id=94;

可以看到，需要65ms的查询时间

2、创建联合索引

alter table test_user add index idx_phone_lan_region(phone,lan_id,region_id);

执行时间减少到5ms。使用explain分析语句执行情况

• type: 结果从最好到最坏依次是：system > const > eq_ref > ref > fulltext > ref_or_null > index_merge > unique_subquery > index_subquery  > range > index > ALL

  

• possible_keys和key: 可能使用的索引和使用的索引，这里使用了联合索引idx_phone_lan_region。

• key_len: 索引中使用的字节数。这里联合索引的长度为90。

3、索引使用原则

（1）最左前缀原则

对于联合索引(a,b,c),符合最左前缀匹配原则的字段，都可以使用索引。bc、c则不能。

• ab: 部分生效，索引长度为86，也就是使用ab组成的索引

• a: 部分生效，索引长度为82，使用了a组成的索引

• ac: 部分生效，索引长度为82，使用了a组成的索引

• bc：失效

• c：失效

（2）字符串索引也适用于最左匹配原则

• 模糊查询%like：失效

• 模糊查询like%: 生效 但是type是range,相当于范围查询

（3）范围查找字段放到最右边

• ab(范围查找)c：可以看到key_len为86,只使用了ab字段的索引
• abc(范围查找)：可以看到key_len为90，使用了abc三个字段的索引

• 计算
• 函数操作
• 类型转换

4、覆盖索引：select中查询字段包含在where索引字段中

`EXPLAIN select SQL_NO_CACHE * from test_user where phone='13831702224' and lan_id=407 and region_id=80 ;

EXPLAIN select SQL_NO_CACHE phone,lan_id,region_id from test_user where phone='13831702224' and lan_id=407 and region_id=80 ;`

可以看到使用覆盖索引的查询会更快一些，因为覆盖索引不需要回到主键索引取出整行数据，而是在辅助索引上查询到数据即可直接返回。

5、索引下推

在满足最左前缀原则时，最左前缀可以用于在索引中定位记录。那些不符合最左前缀原则的部分又会怎么样呢？

EXPLAIN select SQL_NO_CACHE * from test_user where phone like '138%' and lan_id=407 and region_id=80 ;

这个语句在搜索索引树时，只能使用'138'找到第一条满足条件的记录，记为IDX。在MySQL5.6以前，只能从 IDX开始一个个回表。到主键索引上找出数据行，再对比字段值。而 MySQL 5.6 引入的索引下推优化（index condition pushdown)， 可以在索引遍历过程中，对索引中包含的字段先做判断，直接过滤掉不满足条件的记录，减少回表次数。 我的mysql版本是8.0,所以使用了索引下推作为优化，减少了回表次数。

可以看到，key_len的长度为90，使用了abc三个字段的索引，其中bc两个字段用来先做判断，直接过滤掉不满足条件的记录。

6、利用索引完成排序

删掉联合索引idx_phone_lan_region, 执行SQL explain SELECT SQL_NO_CACHE create_time from test_user where create_time BETWEEN '2022-01-13 22:18:37' AND'2022-01-13 22:18:38' order by create_time;

• type: ALL,使用全表扫描

• rows: 必须检查的行数，为107087，将近全部数据行的数量

• filtered: 表示存储引擎返回的数据在server层过滤后，剩下多少满足查询的记录数量的比例。也就是说，存储引擎返回的数据，在server层过滤后，只剩下11.11%满足条件的记录。

• Extra: Using where: phone字段使用了where过滤，而lan_id字段使用了文件排序。性能从差到好的顺序为：Using filesort->Using temporary->Using index->Using where->Using join buffer->impossible where->select tables optimized away

  优化思路：为create_time字段建立索引，利用B+树索引的天然有序性完成排序功能，无需把数据加载到内存进行排序。

建立索引alter table test_user add index index_create_time(create_time);再次执行SQL语句

• type: range,范围扫描索引
• rows: 32
• filtered: 引擎层返回的数据无需在server层再次过滤，利用率100%。
• Extra: Using where: 使用了where过滤；Using index:表明利用索引进行排序。

小结：尽量避免Using filesort，因为外部排序需要把所有数据行加载内存，在内存进行排序，耗费一定的时间和空间成本，可以为需要排序的字段建立索引，直接在查找索引的同时完成排序。

7、总结

本文创建了一个记录数量为110000的test_user表，演示了如何使用索引以及什么情况下索引会失效，最左前缀原则其实就是最大程度符合索引结构的有序性，索引失效，就是对字段进行了一些操作，无法利用索引的快速定位功能；还有一系列的索引优化手段：覆盖索引(select的字段包含在where中的索引字段，无需回表)、索引下推(在引擎层利用不符合最左前缀的字段完成过滤，减少回表次数)、利用索引完成排序(避免外部排序)。