如何理解MySQL limit导致的执行计划差异

共计 3976 个字符，预计需要花费 10 分钟才能阅读完成。

本篇内容介绍了“如何理解 MySQL limit 导致的执行计划差异”的有关知识，在实际案例的操作过程中，不少人都会遇到这样的困境，接下来就让丸趣 TV 小编带领大家学习一下如何处理这些情况吧！希望大家仔细阅读，能够学有所成！

今天收到一个业务的报警，提示慢日志比较频繁，登上环境查看，发现 SQL 是一条看起来很简单的语句，环境在 MySQL  5.7.16 版本下，慢日志里面执行时间显示是近 1 分钟，我在从库上面执行了一下，发现优化空间确实很大：

select OrgId from `testcomm`.apply_join_org where IfDel=1 and ApplyStatus=1 and UserId = 12345678 ORDER BY CreateTime desc LIMIT 1; Empty set (48.71 sec)

执行计划如下:

explain select OrgId -  from `testcomm`.apply_join_org -  where IfDel=1 and ApplyStatus=1 and UserId = 12345678 ORDER BY CreateTime desc LIMIT 1\G *************************** 1. row *************************** id: 1 select_type: SIMPLE table: apply_join_org partitions: NULL type: index possible_keys: IndexRTUser key: IndexCreateTime key_len: 5 ref: NULL rows: 4332 filtered: 0.00 Extra: Using where 1 row in set, 1 warning (0.00 sec)

到了这个时候，不上表结构有些草率了，结构有所删减。

CREATE TABLE `apply_join_org` ( `ApplyJoinId` int(11) NOT NULL AUTO_INCREMENT, `RTId` int(11) DEFAULT NULL, `UserId` int(11) NOT NULL, `OrgId` int(11) NOT NULL, `ApplyMsg` varchar(100) DEFAULT NULL, `CreateTime` datetime NOT NULL, `ReplyMemId` int(11) DEFAULT  0 , `ReplyTime` datetime NOT NULL, `ApplyStatus` tinyint(4) DEFAULT  1  COMMENT  0 拒绝 1 申请 2 同意 , `IfDel` tinyint(4) DEFAULT  1 , `UpdateTime` timestamp NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, `RP` int(11) DEFAULT  0  COMMENT  RP 值 , `sex` tinyint(1) DEFAULT NULL, `IfLeaguer` tinyint(1) NOT NULL DEFAULT  0 , PRIMARY KEY (`ApplyJoinId`), KEY `IndexOrgIdStatus` (`OrgId`,`ApplyStatus`,`IfDel`), KEY `IndexRTUser` (`UserId`), KEY `IndexCreateTime` (`CreateTime`) USING BTREE ) ENGINE=InnoDB AUTO_INCREMENT=22495957 DEFAULT CHARSET=utf8 1 row in set (0.00 sec)

此外涉及的这张表的数据量有 2000 万左右，从目前的执行效率来看，无疑于走了一个全表扫描。

其实这个问题到了这个还是比较好理解的。从语句的表现，结合表结构，我们可以感觉到：  整个 SQL 的执行过程中，原本是基于字段 UserId，没想到却因为 order by 中的 CreateTime，导致索引选择错误，执行代价差异很大。

所以到了这里，我们如何来定性这个问题：

1) 是因为 order by 导致的吗?

2) 是因为时间字段的排序导致的吗?

3) 是因为 limit 操作导致的吗?

4) 是因为 userid 本身的数据过滤效果差导致的吗?

对于这些疑问，我们可以很快通过几条对比 SQL 就能够快速验证。

通过如下的 SQL 可以看到 order by 不是最主要的原因

select OrgId -  from `testcomm`.apply_join_org -  where IfDel=1 and ApplyStatus=1 and UserId = 12345678 ORDER BY CreateTime ; Empty set (0.01 sec

order by 排序也不是最主要的原因

select OrgId -  from `testcomm`.apply_join_org -  where IfDel=1 and ApplyStatus=1 and UserId = 12345678 ORDER BY CreateTime desc ; Empty set (0.01 sec)

order by 排序 +limit 10 也不是最主要的原因

select OrgId from `testcomm`.apply_join_org where IfDel=1 and ApplyStatus=1 and UserId = 12345678 ORDER BY CreateTime desc LIMIT 10; Empty set (0.01 sec)

order by 排序 +limit 2 也不是最主要的原因

select OrgId -  from `testcomm`.apply_join_org -  where IfDel=1 and ApplyStatus=1 and UserId = 12345678 ORDER BY CreateTime desc LIMIT 2; Empty set (0.01 sec)

而经过这些对比，主要加入了 limit 1，索引选择情况就会发生变化。我们抓取一条 limit  2 的执行计划来看看。可以明显看到 type 为 ref, 此外 ref 部分差异很大 (const)。

explain select OrgId from `testcomm`.apply_join_org where IfDel=1 and ApplyStatus=1 and UserId = 12345678 ORDER BY CreateTime desc LIMIT 2\G *************************** 1. row *************************** id: 1 select_type: SIMPLE table: apply_join_org partitions: NULL type: ref possible_keys: IndexRTUser key: IndexRTUser key_len: 4 ref: const rows: 4854 filtered: 1.00 Extra: Using index condition; Using where; Using filesort 1 row in set, 1 warning (0.00 sec)

如果想得到更进一步的信息，可以使用如下的方式：

SET optimizer_trace= enabled=on  SELECT * FROM INFORMATION_SCHEMA.OPTIMIZER_TRACE\G

查看

reconsidering_access_paths_for_index_ordering 部分的信息会是关键所在。

index_provides_order : true,

order_direction : desc ,

而对于这个问题的分析，主要还是在于对于 cost 的评估方式，显然在目前的测试中，增加了额外的 order  by 排序操作，导致了代价会略微高一些，而在优化器中在评估中，显然这部分是缺失了一些信息导致判断失误。

有如下几种方式可以修复：

1) 补充完整的复合索引，userid 和 CreateTime 能够做到互补，该方案已经在同构环境中做了完整的模拟测试，能够达到预期

alter table `testcomm`.apply_join_org drop key IndexRTUser; alter table `testcomm`.apply_join_org add key `IndexRTUser2`(UserId,CreateTime);

2) 使用 force index 的 hint 方式来强制索引，当然对于业务具有一定的侵入性

3) 调整 SQL 逻辑模式，确实是否可以使用其他的方式来代替这种 limit 1 的使用模式。

而从长计议，其实整个评估中的优化器还是比较薄弱的，对于索引选择中的判断依据，如果有了直方图等辅助信息，整个过程会更加如虎添翼，这块的内容，准备在 8.0 中进行一些模拟测试，稍后奉上测试结果。

“如何理解 MySQL limit 导致的执行计划差异”的内容就介绍到这里了，感谢大家的阅读。如果想了解更多行业相关的知识可以关注丸趣 TV 网站，丸趣 TV 小编将为大家输出更多高质量的实用文章！