由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，希望可以幫助到遇到同樣問題的朋友們。
來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/28218939/viewspace-2140197/，如需轉載，請註明出處，否則將追究法律責任。
相關文章
- 一個由程式記憶體佈局異常引起的問題記憶體
- 由程式部署所引起的思考薦
- 介面詭異的404問題記錄
- 很詭異的博弈問題分析方法
- python 詭異問題求助各位大哥Python
- 一個看似詭異的Oracle連線問題Oracle
- 由row cache lock等待事件引起的效能問題事件
- 一次詭異的MySQL問題處理故事MySql
- 解密詭異併發問題的幕後黑手：可見性問題解密
- 由於內部連線引起的Oracle RAC效能問題Oracle
- 記錄一次詭異的拼接sql不生效問題SQL
- 串列埠使用Pipeline時詭異的ReadOnlySequence問題串列埠
- 記錄 openssl 證書驗證失敗的詭異問題
- SQL執行計劃異常引起的效能問題SQL
- SQL執行計劃異常 引起的效能問題SQL
- 『提問』聯合主鍵（compsite-id）所引起的問題
- 一次詭異的Oracle使用者無法su問題Oracle
- oracle交換分割槽所引起的索引失效問題探究測試Oracle索引
- 由於Spring的單態引起的執行緒阻塞的問題Spring執行緒
- 由一個C++版本猜數字遊戲引起的效率問題C++遊戲
- 【原創】由隱式轉換引起的資料庫效能問題資料庫
- PHP 由 empty 和 isset 面試題引起的思考PHP面試題
- 利用sys schema解決一次詭異的語句hang問題
- 從 V8 原始碼看 JS 陣列排序的詭異問題原始碼JS陣列排序
- C#由轉換二進位制所引起的思考，瞭解下？C#
- 詭異的”慢查詢“
- JavaScript 詭異的0.01JavaScript
- 一次詭異的線上資料庫的死鎖問題排查過程資料庫
- 一個詭異的 Pulsar InterruptedException 異常Exception
- 解決由無線干擾引起的 Wi-Fi 和藍芽問題藍芽
- 關於 SAP ABAP gateway OData 的一個詭異問題及解決辦法Gateway
- 由Linux核心bug引起SSH登入緩慢問題的排查與解決Linux
- 【恩墨學院】一次由查詢轉換引起的效能問題的分析
- GP詭異的查詢轉換
- 詭異的無線網路卡Down
- C語言之詭異字串C語言字串
- 【完結篇】 遇到的詭異問題，終於解決了，原來是因為鎖
- 使用RMAN report need backup命令檢查備份時遇到一個詭異的問題