由optimizer_switch所引起的詭異問題

作者  董紅禹

沃趣科技MySQL高階工程師

---

一、引數描述

MySQL中不同的版本最佳化器會有很多新特性，比如MRR、BKA等，其中optimizer_switch這個引數就是控制查詢最佳化器怎樣使用這些特性。很多情況下我們會根據自身的需求去設定optimizer_switch滿足我們的需求。

前段時間客戶的環境中遇到一個奇怪的問題，select count(*)顯示返回是有資料，但select * 返回是空結果集，最終的原因就是因為optimizer_switch設定引起了一個讓我們難以察覺的BUG。這裡和大家分享一下，希望大家在以後的工作如果遇到類似的問題能夠輕鬆應對。

二、案例分析

2.1 環境描述

資料庫版本MySQL5.6.35

2.2 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;

2.3 分析過程

凌晨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.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;

+----+-------------+------------+--------+------------------------+-------------------+---------+-------------------+-------+-----------------------------+

| id | select_type | table      | type   | possible_keys          | key               | key_len | ref               | rows  | Extra                       |

+----+-------------+------------+--------+------------------------+-------------------+---------+-------------------+-------+-----------------------------+

|  1 | PRIMARY     | <derived2> | ref    | <auto_key0>            | <auto_key0>       | 153     | const             |    10 | Using where; Using filesort |

|  2 | DERIVED     | o          | range  | idx_orderdatetime      | idx_orderdatetime | 6       | NULL              | 46104 | Using index condition       |

|  2 | DERIVED     | mm         | eq_ref | PRIMARY,idx_memberid   | PRIMARY           | 8       | mall.o.buyerid    |     1 | NULL                        |

|  2 | DERIVED     | ms         | ref    | idx_userid             | idx_userid        | 9       | mall.o.salerid    |     1 | NULL                        |

|  2 | DERIVED     | mmt        | eq_ref | PRIMARY,idx_merchantid | PRIMARY           | 8       | mall.o.salerid    |     1 | NULL                        |

|  2 | DERIVED     | ma         | eq_ref | PRIMARY                | PRIMARY           | 8       | mall.o.activityid |     1 | NULL                        |

|  2 | DERIVED     | md         | ref    | idx_activityid         | idx_activityid    | 8       | mall.ma.actid     |     1 | NULL                        |

+----+-------------+------------+--------+------------------------+-------------------+---------+-------------------+-------+-----------------------------+

7 rows in set (0.00 sec)

新系統

explain  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;

+----+-------------+------------+--------+------------------------+-------------------+---------+-------------------+-------+----------------------------------+

| id | select_type | table      | type   | possible_keys          | key               | key_len | ref               | rows  | Extra                            |

+----+-------------+------------+--------+------------------------+-------------------+---------+-------------------+-------+----------------------------------+

|  1 | PRIMARY     | <derived2> | ref    | <auto_key0>            | <auto_key0>       | 153     | const             |    10 | Using where; Using filesort      |

|  2 | DERIVED     | o          | range  | idx_orderdatetime      | idx_orderdatetime | 6       | NULL              | 46104 | Using index condition; Using MRR |

|  2 | DERIVED     | mm         | eq_ref | PRIMARY,idx_memberid   | PRIMARY           | 8       | mall.o.buyerid    |     1 | NULL                             |

|  2 | DERIVED     | ms         | ref    | idx_userid             | idx_userid        | 9       | mall.o.salerid    |     1 | NULL                             |

|  2 | DERIVED     | mmt        | eq_ref | PRIMARY,idx_merchantid | PRIMARY           | 8       | mall.o.salerid    |     1 | NULL                             |

|  2 | DERIVED     | ma         | eq_ref | PRIMARY                | PRIMARY           | 8       | mall.o.activityid |     1 | NULL                             |

|  2 | DERIVED     | md         | ref    | idx_activityid         | idx_activityid    | 8       | mall.ma.actid     |     1 | NULL                             |

+----+-------------+------------+--------+------------------------+-------------------+---------+-------------------+-------+----------------------------------+

7 rows in set (0.00 sec)

兩邊的執行計劃不同的地方就是新系統使用了MRR，資料庫的版本都是5.6.20之後的小版本號沒有相差很多，應該不會出現這種情況。 

想到了optimizer_switch這個引數可以設定mrr特性，是不是有人對其做了修改，對比了兩邊optimizer_switch這個引數發現mrr_cost_based這個值設定的不同。快速的將引數設定為一樣再次查詢:

set optimizer_switch='mrr_cost_based=on';

Query OK, 0 rows affected (0.00 sec)

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;

立刻就能夠返回資料，一切搞定。

三、總結

mrr_cost_based代表是否使用基於代價的方式去計算使用MRR特性，新的系統中將他設定為off代表不使用基於代價方式而是使用基於規則的，這樣設定的原因是考慮到MySQL基於代價的方式比較保守，不能使用到MRR這個特性。本身設定這個引數是沒有任何問題，只不過正好遇到mrr_cost_based設定為off時碰到了這麼詭異BUG，希望可以幫助到遇到同樣問題的朋友們。