MySQL 索引选错的情况

DDghost123

已于 2022-10-23 22:34:26 修改

阅读量697

点赞数

分类专栏： mysql 文章标签： mysql 数据库 sql 慢查询索引选错

于 2022-10-23 22:33:21 首次发布

本文链接：https://blog.csdn.net/DDghsot/article/details/127477238

版权

mysql 专栏收录该内容

1 篇文章 0 订阅

订阅专栏

前言

对于数据量大的表进行查询，为表建立索引从而加快查询速度是一种常见优化方式，但建立了索引后，有时 MySQL 的查询速度还是不如人意，主要原因可能是 MySQL 并没有选择正确的索引进行查询，在这里给出一些常见的 MySQL 选错索引的情况。

测试环境

MySQL 5.7

初始化表的脚本

表中有 id, name, ctime 三个子段，其中id为主键， ctime和name都建了普通索引。
然后往表中插入500万数据，各条数据的ctime依次递增1，name为"aaa"、 “bbb”、 “ccc”、 “ddd”、 “eee”、 “fff”的数据各占10%，为"ggg" 的占 30%

DROP PROCEDURE IF EXISTS process_test;
DROP TABLE IF EXISTS temp_table;

CREATE TABLE temp_table (
    `id` BIGINT(20) unsigned NOT NULL AUTO_INCREMENT,
    `name` VARCHAR(64) COLLATE utf8mb4_unicode_ci NOT NULL DEFAULT '',
    `ctime` BIGINT(20) unsigned NOT NULL DEFAULT '0',
    PRIMARY KEY (`id`),
    KEY `idx_ctime` (`ctime`),
    KEY `idx_name` (`name`)
)ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

DELIMITER ;;
CREATE PROCEDURE `process_test`()
BEGIN
  declare n int;
  declare now_time BIGINT;
  set n = 1;
  set now_time = 1666488629;
  while n <= 5000000 do
	if n % 10 = 0 THEN
  		insert into temp_table(name, ctime) values("aaa", now_time+n);
	ELSEIF MOD(n, 10) = 1 THEN
		insert into temp_table(name, ctime) values("bbb", now_time+n);
	ELSEIF MOD(n, 10) = 2 THEN
		insert into temp_table(name, ctime) values("ccc", now_time+n);
	ELSEIF MOD(n, 10) = 3 THEN
		insert into temp_table(name, ctime) values("ddd", now_time+n);
	ELSEIF MOD(n, 10) = 4 THEN
		insert into temp_table(name, ctime) values("eee", now_time+n);
	ELSEIF MOD(n, 10) = 5 THEN
		insert into temp_table(name, ctime) values("fff", now_time+n);
	ELSE
		insert into temp_table(name, ctime) values("ggg", now_time+n);
  	END IF;
	set n = n + 1;
  	
  end while;
END;;
DELIMITER ;

CALL process_test();

MySQL选错索引的情况

情况1 查询范围过大

1.1 复现

如下 sql 所示，这两条 sql 的差别仅仅是 ctime 的查询范围，第一条 sql 的 ctime 范围是11万，第二条sql的ctime范围是10万，按理说这两条语句如果用 ctime 索引的话，那么查询时间应该差不多，因为扫描的数据行数仅仅差了1万行。（注意：复现可能并不一定成功，具体的阈值可能因环境不同而不同）

select * from temp_table where 1671488600 > ctime and ctime > 1671488600-110000 and name = "aaa";
select * from temp_table where 1671488600 > ctime and ctime > 1671488600-100000 and name = "aaa";

在实际运行后，第一条 sql 运行了接近一分钟，而第二条 sql 则只要一秒钟不到。
请添加图片描述
通过 explain 语句的执行计划，可以发现第一条 sql 居然是用了 name 索引进行查询。从之前的建表语句可以知道 name 为 “aaa” 的数据有100万条。

而第二条 sql 使用 ctime 索引进行查询，仅会扫描10万条数据。因此这两条 sql 的耗时差异巨大。
请添加图片描述

1.2 原因

根据 ctime 和 name 来进行查询时，当ctime的时间跨度稍大时，MySQL有可能认为使用 ctime 作为索引过滤掉的数据并不够多，因此改用 name 索引。

1.3 解决方案

既然已经知道 MySQL 选错了索引，可以直接使用 force index 来强制 MySQL 使用具体哪一个索引。

缺点：当该索引被删除或者重命名后，这个方法会失效，而且具体的阈值也非常难设置。
添加联合索引 “(ctime, name)” 或者 “(name, ctime)”，具体顺序要具体问题分析，一般查询更频繁以及区分度更高的字段放前面。一般查询都要限定时间范围，而且时间范围的区分度也很高，因为同一时间戳插入的数据相对较少。所以可以考虑新加入一个 “(ctime, name)” 联合索引，然后原来表中的 ctime 这个索引就冗余了，可以删掉。

缺点：需要对具体业务进行分析，分析出高频和区分度高的查询字段，同时也要避免建立的冗余索引。
一般查询都会加上 limit n 限制查询结果的行数，这时可以加上 “order by ctime” 让 MySQL 选择 ctime 索引进行查询（需要有limit）。explain 后可以看到，加上 “order by ctime” 和 “limit 100” 确实可以让MySQL选择 ctime 索引进行查询。

原理：当有 limit 和 order by 时，需要排序然后选出若干行数据，当加上 "order by ctime"后，MySQL判断如果使用 ”ctime“ 索引进行查询可以避免排序带来的花销，因此会选择使用 ctime 这个索引来查询。

缺点：当查询条件没有 ctime 时，查询耗时可能会更糟糕，因为此时用ctime 作为索引不能过滤任何数据行，下一种情况就是类似的例子。

情况2 使用了 order by 和 limit 对结果进行排序和限制行数

2.1 复现

如下 sql 所示，查询条件有 ctime 同时语句也加上了 limit 来限制获取的数据后行数，各条 sql 的不同之处在于 order by 的字段不同。如果这些 sql 都利用 ctime 索引来查询，耗时应该相当，因为按照不同的字段进行排序的耗时应该是较小的。

select * from temp_table where 1671488600 > ctime and ctime > 1671488600-100000 order by ctime limit 1000;
select * from temp_table where 1671488600 > ctime and ctime > 1671488600-100000 order by id limit 1000;
select * from temp_table where 1671488600 > ctime and ctime > 1671488600-100000 order by name limit 1000;

运行后，可以发现这三条语句的耗时差距很大，第一条 sql 不到0.1秒就执行完成了，而第三条则要半分钟。
在这里插入图片描述

2.2 原因

如情况1中的解决方案3中所说的，加入order by 和 limit 会让 MySQL 选择需要排序的字段的索引来进行查询。对这三条 sql 使用 explain 可以看到，第一条 sql 使用了 ctime 索引进行查询，第二条 sql 使用主键索引进行全表查询，而第三条则使用了 name 索引来进行查询。
请添加图片描述

耗时分析：

使用了 ctime 索引，不需要排序，只查了一千条就可以返回结果。
使用了主键索引全表扫描，需要查到符合条件的一千条结果才能返回，主键索引在查询的条件中完全没有出现，因此主键过滤不掉任何数据。而根据这个查询条件，实际查询的行数应该为490万左右。
使用了 name 索引全表扫描，需要查到符合条件的一千条结果才能返回。看上去2和3很类似，但为何还有十多倍的时间差距呢？由于 name 是普通索引，因此每条记录都需要回表。

2.3 解决方案

使用 force index。

缺点：和1.3中的解决方案1相同。
删掉 order by 的索引，直接避免 MySQL 使用该索引。

缺点：建的索引一般都是有用的，删除索引会影响别的使用该索引的查询语句。
sql 语句不加order by。

缺点：改变业务逻辑，有时我们就是需要排序。
如果确实希望根据对应的字段进行排序，为了避免 MySQL 节约排序的开销，可以增加排序的字段，让MySQL认为使用排序对应的索引无法节约排序开销，例如，将 ”order by name“ 改为 “order by name, ctime” 。

缺点：一般的业务需求很少要按照两个字段来排序，因此写了两个排序字段比较反直觉，后续维护代码需要添加注释才好理解。