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

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

selectOrgIdfrom`testcomm`.apply_join_orgwhereIfDel=1andApplyStatus=1andUserId=12345678ORDERBYCreateTimedescLIMIT1;Emptyset(48.71sec)

执行计划如下:

explainselectOrgId->from`testcomm`.apply_join_org->whereIfDel=1andApplyStatus=1andUserId=12345678ORDERBYCreateTimedescLIMIT1\G***************************1.row***************************id:1select_type:SIMPLEtable:apply_join_orgpartitions:NULLtype:indexpossible_keys:IndexRTUserkey:IndexCreateTimekey_len:5ref:NULLrows:4332filtered:0.00Extra:Usingwhere1rowinset,1warning(0.00sec)

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

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`int(11)DEFAULT'0',`ReplyTime`datetimeNOTNULL,`ApplyStatus`tinyint(4)DEFAULT'1'COMMENT'0拒绝1申请2同意',`IfDel`tinyint(4)DEFAULT'1',`UpdateTime`timestampNULLDEFAULTCURRENT_TIMESTAMPONUPDATECURRENT_TIMESTAMP,`RP`int(11)DEFAULT'0'COMMENT'RP值',`sex`tinyint(1)DEFAULTNULL,`IfLeaguer`tinyint(1)NOTNULLDEFAULT'0',PRIMARYKEY(`ApplyJoinId`),KEY`IndexOrgIdStatus`(`OrgId`,`ApplyStatus`,`IfDel`),KEY`IndexRTUser`(`UserId`),KEY`IndexCreateTime`(`CreateTime`)USINGBTREE)ENGINE=InnoDBAUTO_INCREMENT=22495957DEFAULTCHARSET=utf81rowinset(0.00sec)

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

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

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

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

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

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

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

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

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

selectOrgId->from`testcomm`.apply_join_org->whereIfDel=1andApplyStatus=1andUserId=12345678ORDERBYCreateTime;Emptyset(0.01sec

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

selectOrgId->from`testcomm`.apply_join_org->whereIfDel=1andApplyStatus=1andUserId=12345678ORDERBYCreateTimedesc;Emptyset(0.01sec)

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

selectOrgIdfrom`testcomm`.apply_join_orgwhereIfDel=1andApplyStatus=1andUserId=12345678ORDERBYCreateTimedescLIMIT10;Emptyset(0.01sec)

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

selectOrgId->from`testcomm`.apply_join_org->whereIfDel=1andApplyStatus=1andUserId=12345678ORDERBYCreateTimedescLIMIT2;Emptyset(0.01sec)

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

>explainselectOrgIdfrom`testcomm`.apply_join_orgwhereIfDel=1andApplyStatus=1andUserId=12345678ORDERBYCreateTimedescLIMIT2\G***************************1.row***************************id:1select_type:SIMPLEtable:apply_join_orgpartitions:NULLtype:refpossible_keys:IndexRTUserkey:IndexRTUserkey_len:4ref:constrows:4854filtered:1.00Extra:Usingindexcondition;Usingwhere;Usingfilesort1rowinset,1warning(0.00sec)

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

SEToptimizer_trace="enabled=on"SELECT*FROMINFORMATION_SCHEMA.OPTIMIZER_TRACE\G

查看

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

"index_provides_order": true,

"order_direction": "desc",

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

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

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

altertable`testcomm`.apply_join_orgdropkeyIndexRTUser;altertable`testcomm`.apply_join_orgaddkey`IndexRTUser2`(UserId,CreateTime);

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

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

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

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