本文介绍了“如何理解MySQL限制导致的执行计划差异”的知识。很多人在实际案例的操作中会遇到这样的困难。让边肖带领你学习如何处理这些情况。希望大家认真阅读，学点东西！
今天接到业务报警，说明慢日志频繁。当我登上环境时，我发现SQL是一个看似简单的语句。环境在MySQL 5 . 7 . 16版本下，慢日志显示执行时间近一分钟。我从库中执行它，发现优化空间真的很大：
selectorgidfrom ` testcomm ` . apply _ join _ org where if del=1 andapplystatus=1 andserid=12345678 orderbycreatetimedescrimina 1；Emptyset(48.71sec)执行计划如下：
explaineselectorgid-from ` testcomm ` . apply _ join _ org-where if del=1 andapplystatus=1 andserid=12345678 orderbycreatetimedeslimit 1 \ G * * * * * * * * * * * * * * * * * * * * * * * * * * * * 1 . row * * * * * * * * * * * * * * * * * * * * * * id :1 select _ type : implement _ join _ orgpartitions : nulltype 3360 index may 3360 index _ key 3360 index rtuserkey 333333
createtable ` apply _ join _ org `(` applyjoinid ` int(11)NOTNULLAUTO _ INCREMENT，` RTId`int(11)DEFAULTNULL，` UserId`int(11)NOTNULL，` OrgId`int(11)NOTNULL，` applymsg ` varchar(100)DEFAULTNULL，` CreateTime`datetimeNOTNULL，` ReplyMemId`nb
sp;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导致的执行计划差异”的内容就介绍到这里了，感谢大家的阅读。如果想了解更多行业相关的知识可以关注网站，小编将为大家输出更多高质量的实用文章！
内容来源网络，如有侵权，联系删除，本文地址：https://www.230890.com/zhan/42408.html