optimizer_switch引起的诡异问题

参数描述

MySQL中不同的版本优化器会有很多新特性，比如MRR、BKA等，optimizer_switch这个参数就是控制查询优化器怎样使用这些特性。很多情况下我们会根据自身的需求去设置optimizer_switch满足我们的需求。前段时间客户的环境中遇到一个奇怪的问题，select count(*)显示返回是有数据但是select * 返回是空结果集，最终的原因就是因为optimizer_switch设置引起了一个BUG。这里和大家分享下防止大家也遇到同样的坑。

案例分析

环境描述

数据库版本MySQL5.6.35

SQL语句

 
 
 

  
select * from (select o.orderid,o.orderdatetime,o.orderstatus,o.price,o.expway,o.paytype,o.fee,o.ordertype,o.realid,mm.account,ms.shopname,mmt.organcode,  o.activitype,o.channelcode, ma.activitytag,md.tagtip from mall_order o  left join mall_member mm on o.buyerid=mm.memberid left join mall_shop ms on o.salerid=ms.userid  left join mall_merchant mmt on mmt.merchantid=o.salerid left join mall_activity ma on o.activityid=ma.actid  left join mall_direct_activity md on ma.actid=md.actid where  1=1  and o.orderdatetime >= '2017-03-01 01:40:03' and o.orderdatetime <= '2017-03-25 01:40:03'  ) as  tab  where  tab.organcode = '805000' order by orderdatetime desc limit 10;
 

分析过程

当时凌晨4点左右客户打来电话告知数据库查询不到数据了非常着急，我们赶到了现场，当时的现象是这样的:

 
 
 

  
select * from (select o.orderid,o.orderdatetime,o.orderstatus,o.price,o.expway,o.paytype,o.fee,o.ordertype,o.realid,mm.account,ms.shopname,mmt.organcode,  o.activitype,o.channelcode, ma.activitytag,md.tagtip from mall_order o  left join mall_member mm on o.buyerid=mm.memberid left join mall_shop ms on o.salerid=ms.userid  left join mall_merchant mmt on mmt.merchantid=o.salerid left join mall_activity ma on o.activityid=ma.actid  left join mall_direct_activity md on ma.actid=md.actid where  1=1  and o.orderdatetime >= '2017-03-01 01:40:03' and o.orderdatetime <= '2017-03-25 01:40:03'  ) as  tab  where  tab.organcode = '805000' order by orderdatetime desc limit 10;
 

这条语句查询返回的结果集是空，但是开发人员和我们说数据库中是有数据的，我当时不相信就执行了一下:

 
 
 

  
select * from (select o.orderid,o.orderdatetime,o.orderstatus,o.price,o.expway,o.paytype,o.fee,o.ordertype,o.realid,mm.account,ms.shopname,mmt.organcode,  o.activitype,o.channelcode, ma.activitytag,md.tagtip from mall_order o  left join mall_member mm on o.buyerid=mm.memberid left join mall_shop ms on o.salerid=ms.userid  left join mall_merchant mmt on mmt.merchantid=o.salerid left join mall_activity ma on o.activityid=ma.actid  left join mall_direct_activity md on ma.actid=md.actid where  1=1  and o.orderdatetime >= '2017-03-01 01:40:03' and o.orderdatetime <= '2017-03-25 01:40:03'  ) as  tab  where  tab.organcode = '805000' order by orderdatetime desc limit 10;
  
Empty set (0.41 sec)
  

  

  
select count(*) from (select o.orderid,o.orderdatetime,o.orderstatus,o.price,o.expway,o.paytype,o.fee,o.ordertype,o.realid,mm.account,ms.shopname,mmt.organcode,  o.activitype,o.channelcode, ma.activitytag,md.tagtip from mall_order o  left join mall_member mm on o.buyerid=mm.memberid left join mall_shop ms on o.salerid=ms.userid  left join mall_merchant mmt on mmt.merchantid=o.salerid left join mall_activity ma on o.activityid=ma.actid  left join mall_direct_activity md on ma.actid=md.actid where  1=1  and o.orderdatetime >= '2017-03-01 01:40:03' and o.orderdatetime <= '2017-03-25 01:40:03'  ) as  tab  where  tab.organcode = '805000' order by orderdatetime desc limit 10;
  
+----------+
  
| count(*) |
  
+----------+
  
|      475 |
  
+----------+
  
1 row in set (0.41 sec)
 

当时也是比较慌张了，count(*)显示返回475条记录但是select *却返回空结果集。。。
想了一下SQL语句有一层嵌套，我看看里面这个SQL是否有问题，测试后发现内层语句可以正常返回，加上外层语句时就会出现这种情况。询问了应用人员系统刚迁移过来，在原系统没有这种情况，快速连到原系统上执行同样的语句对比一下两边的执行计划:
原系统

 
 
 

  
explain  select * from (select o.orderid,o.orderdatetime,o.orderstatus,o.price,o.expway,o.paytype,o.fee,o.ordertype,o.realid,mm.