MySQLlimit导致的执行计划差异

　　这是学习笔记的第2271篇文章
　　今天收到一个业务的报警，提示慢日志比较频繁，登上环境查看，发现SQL是一条看起来很简单的语句，环境在MySQL5。7。16版本下，慢日志里面执行时间显示是近1分钟，我在从库上面执行了一下，发现优化空间确实很大：selectOrgIdfromtestcomm。applyjoinorgwhereIfDelandApplyStatusandUserId12345678ORDERBYCreateTimedescLIMIT；Emptyset（48。71sec）
　　执行计划如下：explainselectOrgIdfromtestcomm。applyjoinorgwhereIfDelandApplyStatusandUserId12345678ORDERBYCreateTimedescLIMITG1。rowid：selecttype：SIMPLEtable：applyjoinorgpartitions：type：indexpossiblekeys：IndexRTUserkey：IndexCreateTimekeylen：5ref：rows：4332filtered：0。00Extra：Usingwhererowinset，warning（0。00sec）
　　到了这个时候，不上表结构有些草率了，结构有所删减。CREATETABLEapplyjoinorg（ApplyJoinIdint（11）NOTAUTOINCREMENT，RTIdint（11）DEFAULT，UserIdint（11）NOT，OrgIdint（11）NOT，ApplyMsgvarchar（100）DEFAULT，CreateTimedatetimeNOT，ReplyMemIdint（11）DEFAULT0，ReplyTimedatetimeNOT，ApplyStatustinyintDEFAULT1COMMENT0拒绝1申请2同意，IfDeltinyintDEFAULT1，UpdateTimetimestampDEFAULTCURRENTTIMESTAMPONUPDATECURRENTTIMESTAMP，RPint（11）DEFAULT0COMMENTRP值，sextinyintDEFAULT，IfLeaguertinyintNOTDEFAULT0，PRIMARYKEY（ApplyJoinId），KEYIndexOrgIdStatus（OrgId，ApplyStatus，IfDel），KEYIndexRTUser（UserId），KEYIndexCreateTime（CreateTime）USINGBTREE）ENGINEInnoDBAUTOINCREMENT22495957DEFAULTCHARSETutf8rowinset（0。00sec）
　　此外涉及的这张表的数据量有2000万左右，从目前的执行效率来看，无疑于走了一个全表扫描。
　　其实这个问题到了这个还是比较好理解的。从语句的表现，结合表结构，我们可以感觉到：整个SQL的执行过程中，原本是基于字段UserId，没想到却因为orderby中的CreateTime，导致索引选择错误，执行代价差异很大。
　　所以到了这里，我们如何来定性这个问题：
　　1）是因为orderby导致的吗？
　　2）是因为时间字段的排序导致的吗？
　　3）是因为limit操作导致的吗？
　　4）是因为userid本身的数据过滤效果差导致的吗？
　　对于这些疑问，我们可以很快通过几条对比SQL就能够快速验证。
　　通过如下的SQL可以看到orderby不是最主要的原因selectOrgIdfromtestcomm。applyjoinorgwhereIfDelandApplyStatusandUserId12345678ORDERBYCreateTime；Emptyset（0。01sec
　　orderby排序也不是最主要的原因selectOrgIdfromtestcomm。applyjoinorgwhereIfDelandApplyStatusandUserId12345678ORDERBYCreateTimedesc；Emptyset（0。01sec）
　　orderby排序limit10也不是最主要的原因selectOrgIdfromtestcomm。applyjoinorgwhereIfDelandApplyStatusandUserId12345678ORDERBYCreateTimedescLIMIT10；Emptyset（0。01sec）
　　orderby排序limit2也不是最主要的原因selectOrgIdfromtestcomm。applyjoinorgwhereIfDelandApplyStatusandUserId12345678ORDERBYCreateTimedescLIMIT；Emptyset（0。01sec）
　　而经过这些对比，主要加入了limit1，索引选择情况就会发生变化。我们抓取一条limit2的执行计划来看看。可以明显看到type为ref，此外ref部分差异很大（const）。explainselectOrgIdfromtestcomm。applyjoinorgwhereIfDel1andApplyStatus1andUserId12345678ORDERBYCreateTimedescLIMIT2G1。rowid：selecttype：SIMPLEtable：applyjoinorgpartitions：type：refpossiblekeys：IndexRTUserkey：IndexRTUserkeylen：ref：constrows：4854filtered：1。00Extra：Usingindexcondition；Usingwhere；Usingfilesortrowinset，1warning（0。00sec）
　　如果想得到更进一步的信息，可以使用如下的方式：SEToptimizertraceenabledonSELECTFROMINFORMATIONSCHEMA。OPTIMIZERTRACEG
　　查看
　　reconsideringaccesspathsforindexordering部分的信息会是关键所在。
　　indexprovidesorder：true，
　　orderdirection：desc，
　　而对于这个问题的分析，主要还是在于对于cost的评估方式，显然在目前的测试中，增加了额外的orderby排序操作，导致了代价会略微高一些，而在优化器中在评估中，显然这部分是缺失了一些信息导致判断失误。
　　有如下几种方式可以修复：
　　1）补充完整的复合索引，userid和CreateTime能够做到互补，该方案已经在同构环境中做了完整的模拟测试，能够达到预期altertabletestcomm。applyjoinorgdropkeyIndexRTUser；altertabletestcomm。applyjoinorgaddkeyIndexRTUser2（UserId，CreateTime）；
　　2）使用forceindex的hint方式来强制索引，当然对于业务具有一定的侵入性
　　3）调整SQL逻辑模式，确实是否可以使用其他的方式来代替这种limit1的使用模式。
　　而从长计议，其实整个评估中的优化器还是比较薄弱的，对于索引选择中的判断依据，如果有了直方图等辅助信息，整个过程会更加如虎添翼，这块的内容，准备在8。0中进行一些模拟测试，稍后奉上测试结果。