MySQL limit导致的执行计划差异

杨建荣的学习笔记 2020-09-15

875

这是学习笔记的第 2271篇文章

今天收到一个业务的报警，提示慢日志比较频繁，登上环境查看，发现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中进行一些模拟测试，稍后奉上测试结果。

QQ群号：763628645

QQ群二维码如下，添加请注明：姓名+地区+职位，否则不予通过

点在看，让更多人看到

mysql

文章转载自杨建荣的学习笔记，如果涉嫌侵权，请发送邮件至：contact@modb.pro进行举报，并提供相关证据，一经查实，墨天轮将立刻删除相关内容。

MySQL limit导致的执行计划差异

评论