今天接到业务告警,说慢日志比较频繁。查了一下环境,发现SQL是一条看起来很简单的语句。环境在MySQL5.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)这个时候不列出结构有点草率,结构已经被删除了。创建表`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'0reject1apply2agree',`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,没想到索引选择错误是orderby中的CreateTime导致的,执行成本差异很大。那么在这里,我们如何定性这个问题:1)是不是orderby导致的?2)是不是time字段的排序导致的?3)是不是限位操作导致的?4)是不是因为userid本身的数据过滤效果不好导致的?对于这些问题,我们可以通过几条对比SQL来快速验证。通过下面的SQL可以看出orderby不是selectOrgId->from`testcomm`.apply_join_org->whereIfDel=1andApplyStatus=1andUserId=12345678ORDERBYCreateTime;Emptyset(0.01secorderbysorting不是selectOrgId->from的主要原因`testcomm`.apply_join_org->whereIfDel=1andApplyStatus=1andUserId=12345678ORDERBYCreateTimedesc;Emptyset(0.01sec)orderby排序+limit10也不是最主要的原因selectOrgIdfrom`testcomm`.apply_join_orgwhereIfDel=1andApplyStatus=1andUserId=12345678ORDERBYCreateTimedescLIMIT10;Emptyset(0.01sec)orderSortingby+limit2不是主要原因selectOrgId->from`testcomm`.apply_join_org->whereIfDel=1andApplyStatus=1andUserId=12345678ORDERBYCreateTimedescLIMIT2;Emptyset(0.01sec)经过这些比较,主要加了limit1,索引选择情况是会变的,我们抓一个limit2的执行计划来看,可以很明显的看到type是ref,ref部分有很大的不同(const)。>explainsselectOrgIdfrom`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",这个问题的分析主要在于成本的评估方法。显然,在目前的测试中,额外增加了一个orderbysorting的操作,导致成本略高,而且在优化器的评估中,很明显这部分缺失了一些信息,导致误判。修复的方法有以下几种:1)补充一个完整的复合索引,userid和CreateTime可以互补。该方案经过同构环境测试,可以达到预期。可更改的`testcomm`.apply_join_orgdropkeyIndexRTUser;可更改的`testcomm`.apply_join_orgaddkey`IndexRTUser2`(UserId,CreateTime);2)使用forceindex的hint方式强制索引,当然对业务有侵入性3)调整SQL逻辑模式,是否可以使用其他方式代替这种限制1的使用方式。从长远来看,其实整个评估中的优化器还是比较弱的。对于指数选择的判断依据,如果有直方图等辅助信息,整个过程就如虎添翼。这部分内容将在8.0进行模拟测试,测试结果将在后面给出。本文转载自微信公众号《杨建荣的学习笔记》,可通过以下二维码关注。转载本文请联系杨建荣学习笔记公众号。
