一次奇怪的mysql查询优化发现mysql查询优化器的bug

        最近刚上线的一个sql出现了慢查，经过分析发现了一个很有意思的问题，具体表现为，select * from tablename where a=xx and b=xx and c=xxx and time<xxx order by time desc; 这条sql的查询很快的，在60ms以内，但是如果再最后加上了limit 10,查询就会要2s多。当这个慢查被运维拉出来的时候，感觉非常不能理解。

        我先把表结构尽量简化一下。

CREATE TABLE `tablename` (
  `id` int(11) unsigned NOT NULL AUTO_INCREMENT 
  `typeId` tinyint(1) unsigned NOT NULL DEFAULT '0' 
  `userId` int(11) unsigned NOT NULL COMMENT '用户id',
  `orderId` int(11) unsigned NOT NULL DEFAULT '0' COMMENT '订单id',
  `tradeStatus` tinyint(1) unsigned NOT NULL DEFAULT '0' COMMENT '交易状态 0.交易中 1.交易关闭 2.交易成功',
  `otherStatus` tinyint(1) unsigned NOT NULL DEFAULT '0' ,
  `addTime` int(10) unsigned NOT NULL COMMENT '添加时间',
  PRIMARY KEY (`id`),
  KEY `IDX_tradeStatus_otherStatus_addTime` (`tradeStatus`,`otherStatus`,`addTime`) USING BTREE,
  KEY `IDX_tradeStatus_otherStatus_typeId_orderId` (`tradeStatus`,`otherStatus`,`typeId`,`orderId`),
  KEY `IDX_addTime` (`addTime`),
  KEY `IDX_userId_tradeStatus_addTime` (`userId`,`tradeStatus`,`addTime`) USING BTREE,
) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8 COMMENT='test'

        我最初的查询sql是

select *
  from tablename
 where userid= 123
   and tradestatus= 2
   and orderid= 0
   and addTime< 1566139866
order by addtime desc limit 10

        看了一下执行计划，当没有limit的时候，执行计划走了最后一条索引，执行计划的type是ref，rows是22800。当加上limit以后，执行计划只走了addTime的索引，并且type退化成了range，rows几乎是全表了。

        这是一个很诡异的事情，研究了几分钟后发现搞不定，决定先优化它，再研究问题。然后发现了两种解决方案。

方案一，将查询语句和limit语句分隔开：

select *
  from(
select *
  from tablename
 where userid= 3307619
   and tradestatus= 2
   and orderid= 0
   and addTime< 1566139866
order by addtime desc) tab limit 10

方案二，使用强制索引语句：

select *
  from tablename force index(IDX_userId_tradeStatus_addTime)
 where userid= 123
   and tradestatus= 2
   and orderid= 0
   and addTime< 1566139866
order by addtime desc limit 10

        处理完后发现这两个方案其实都是在强行使用了mysql更好的索引，并且第一个解决方案因为避开了limit反而使用了正确的索引，所以我怀疑有可能是mysql的查询优化器在遇到limit以后会失效。带着这个疑问又去查询资料，终于被我找到一篇帖子。

        https://yq.aliyun.com/articles/51065/

        这里把bug产生的原因贴出来，具体的内容大家可以自己去查看。

• 第一阶段，优化器选择了索引 iabc，采用 range 访问；
• 第二阶段，优化器试图进一步优化执行计划，使用 order by 的列访问，并清空了第一阶段的结果；
• 第三阶段，优化器发现使用 order by 的列访问，代价比第一阶段的结果更大，但是第一阶段结果已经被清空了，无法还原，于是选择了代价较大的访问方式（index_scan），触发了bug。