MySQL索引优化实战

·  阅读 1415
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;

复制代码

表中数据总量: image.png

二、索引优化实战

1、没有任何索引

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

image.png 可以看到,需要65ms的查询时间

2、创建联合索引

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

image.png

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

image.png

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

    image.png

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

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

3、索引使用原则

(1)最左前缀原则

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

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

image.png

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

image.png

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

image.png

  • bc:失效

image.png

  • c:失效

image.png

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

  • 模糊查询%like:失效

image.png

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

image.png

(3)范围查找字段放到最右边

  • ab(范围查找)c:可以看到key_len为86,只使用了ab字段的索引

image.png

  • abc(范围查找):可以看到key_len为90,使用了abc三个字段的索引

image.png

(4)其他一些导致索引失效的情况 关于索引失效的原理可以查看文章MYSQL索引失效的原理

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

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 ;`

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

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,所以使用了索引下推作为优化,减少了回表次数。

image.png 可以看到,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;

image.png

  • 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

    image.png

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

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

image.png

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

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

7、总结

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

分类:
后端
标签:
分类:
后端
标签:
收藏成功!
已添加到「」, 点击更改