【案例】BNL演算法導致效能下降一則
前面介紹了BNL演算法,相信不少人會認為BNL會有利於資料庫效能的提升(我也是這麼認為滴)，本文講述一例生產上因為升級使用BNL 演算法導致效能下降的案例。
一 背景
生產上將一例項MySQL版本從5.5升級到5.6,一條sql在5.5版本的MySQL執行只需要零點幾秒,而在5.6 版本的環境下則需要10多秒,這個問題定位是5.6的最佳化器策略與5.5不同,導致了SQL執行計劃發生變化，進而導致了sql的效能急劇下降.
二 案例
1) 5.5的最佳化器策略：
index_merge=on,index_merge_union=on,index_merge_sort_union=on,index_merge_intersection=on,engine_condition_pushdown=on
2) 5.6的最佳化器策略:
index_merge=on,index_merge_union=on,index_merge_sort_union=on,index_merge_intersection=on,engine_condition_pushdown=on,
index_condition_pushdown=on,mrr=on,mrr_cost_based=on,block_nested_loop=on,batched_key_access=on,materialization=on,semijoin=on,loosescan=on,firstmatch=on,subquery_materialization_cost_based=on,use_index_extensions=on
mysql> show global variables like '%optimizer_switch%';
+------------------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
| Variable_name | Value |
+------------------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------|
optimizer_switch | index_merge=on,index_merge_union=on,index_merge_sort_union=on,index_merge_intersection=on,engine_condition_pushdown=on,index_condition_pushdown=on,mrr=on,
mrr_cost_based=on,block_nested_loop=on,batched_key_access=off,materialization=on,semijoin=on,loosescan=on,firstmatch=on,subquery_materialization_cost_based=on,
use_index_extensions=on |
+------------------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------1 row in set (0.00 sec)
5.6版本的執行的執行計劃如下所示:
mysql> explain SELECT *
-> FROM s_gm_info this_
-> LEFT OUTER JOIN s_gm_item gmitem2_ ON this_.itemId = gmitem2_.id
-> LEFT OUTER JOIN s_gm_group gmgroup3_ ON gmitem2_.groupId =gmgroup3_.id
-> LEFT OUTER JOIN stm_info teaminfo4_ ON this_.guestId = teaminfo4_.id
-> LEFT OUTER JOIN s_lgue_info lgueinfo5_ ON teaminfo4_.lgueId =lgueinfo5_.id
-> LEFT OUTER JOIN stm_info teaminfo6_ ON this_.homeId = teaminfo6_.id
-> LEFT OUTER JOIN s_lgue_info lgueinfo7_ ON this_.lgueId =lgueinfo7_.id
-> LEFT OUTER JOIN s_area_info areainfo8_ ON lgueinfo7_.areaId =areainfo8_.id
-> LEFT OUTER JOIN s_lgue_group lguegrou9_ ON lgueinfo7_.groupId =lguegrou9_.id
-> LEFT OUTER JOIN s_lgue_item lgueitem10_ ON lgueinfo7_.itemId =lgueitem10_.id
-> ORDER BY this_.id ASC LIMIT 20;
this_ 表原來可以透過主鍵來獲取資料,在使用了BNL演算法之後卻導致全表掃描。
關閉BNL最佳化器
新的執行計劃如下:
關閉該特性之後 ,執行計劃選擇了正確的索引,執行時間大幅度下降。
三 總結
透過這個例子，想告訴大家對線上資料庫的升級操作,最好做必要的壓測。先升級日常環境，然後選擇升級線上環境。對於MySQL新的最佳化點有必要深入研究，瞭解其原理，多做測試。才能發現其中可能隱藏的問題。
一 背景
生產上將一例項MySQL版本從5.5升級到5.6,一條sql在5.5版本的MySQL執行只需要零點幾秒,而在5.6 版本的環境下則需要10多秒,這個問題定位是5.6的最佳化器策略與5.5不同,導致了SQL執行計劃發生變化，進而導致了sql的效能急劇下降.
二 案例
1) 5.5的最佳化器策略：
index_merge=on,index_merge_union=on,index_merge_sort_union=on,index_merge_intersection=on,engine_condition_pushdown=on
2) 5.6的最佳化器策略:
index_merge=on,index_merge_union=on,index_merge_sort_union=on,index_merge_intersection=on,engine_condition_pushdown=on,
index_condition_pushdown=on,mrr=on,mrr_cost_based=on,block_nested_loop=on,batched_key_access=on,materialization=on,semijoin=on,loosescan=on,firstmatch=on,subquery_materialization_cost_based=on,use_index_extensions=on
mysql> show global variables like '%optimizer_switch%';
+------------------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
| Variable_name | Value |
+------------------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------|
optimizer_switch | index_merge=on,index_merge_union=on,index_merge_sort_union=on,index_merge_intersection=on,engine_condition_pushdown=on,index_condition_pushdown=on,mrr=on,
mrr_cost_based=on,block_nested_loop=on,batched_key_access=off,materialization=on,semijoin=on,loosescan=on,firstmatch=on,subquery_materialization_cost_based=on,
use_index_extensions=on |
+------------------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------1 row in set (0.00 sec)
5.6版本的執行的執行計劃如下所示:
mysql> explain SELECT *
-> FROM s_gm_info this_
-> LEFT OUTER JOIN s_gm_item gmitem2_ ON this_.itemId = gmitem2_.id
-> LEFT OUTER JOIN s_gm_group gmgroup3_ ON gmitem2_.groupId =gmgroup3_.id
-> LEFT OUTER JOIN stm_info teaminfo4_ ON this_.guestId = teaminfo4_.id
-> LEFT OUTER JOIN s_lgue_info lgueinfo5_ ON teaminfo4_.lgueId =lgueinfo5_.id
-> LEFT OUTER JOIN stm_info teaminfo6_ ON this_.homeId = teaminfo6_.id
-> LEFT OUTER JOIN s_lgue_info lgueinfo7_ ON this_.lgueId =lgueinfo7_.id
-> LEFT OUTER JOIN s_area_info areainfo8_ ON lgueinfo7_.areaId =areainfo8_.id
-> LEFT OUTER JOIN s_lgue_group lguegrou9_ ON lgueinfo7_.groupId =lguegrou9_.id
-> LEFT OUTER JOIN s_lgue_item lgueitem10_ ON lgueinfo7_.itemId =lgueitem10_.id
-> ORDER BY this_.id ASC LIMIT 20;
-
+----+-------------+---------------+--------+---------------+---------+---------+-----------------------------+--------+----------------------------------------------------+
-
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
-
+----+-------------+---------------+--------+---------------+---------+---------+-----------------------------+--------+----------------------------------------------------+
-
| 1 | SIMPLE | this_ | ALL | NULL | NULL | NULL | NULL | 257312 | Using temporary; Using filesort |
-
| 1 | SIMPLE | gmitem2_ | eq_ref | PRIMARY | PRIMARY | 4 | app_db.this_.itemId | 1 | NULL |
-
| 1 | SIMPLE | gmgroup3_ | ALL | PRIMARY | NULL | NULL | NULL | 6 | Using where; Using join buffer (Block Nested Loop) |
-
| 1 | SIMPLE | teaminfo4_ | eq_ref | PRIMARY | PRIMARY | 4 | app_db.this_.guestId | 1 | NULL |
-
| 1 | SIMPLE | lgueinfo5_ | eq_ref | PRIMARY | PRIMARY | 4 | app_db.teaminfo4_.lgueId | 1 | NULL |
-
| 1 | SIMPLE | teaminfo6_ | eq_ref | PRIMARY | PRIMARY | 4 | app_db.this_.homeId | 1 | NULL |
-
| 1 | SIMPLE | lgueinfo7_ | eq_ref | PRIMARY | PRIMARY | 4 | app_db.this_.lgueId | 1 | NULL |
-
| 1 | SIMPLE | areainfo8_ | eq_ref | PRIMARY | PRIMARY | 4 | app_db.lgueinfo7_.areaId | 1 | NULL |
-
| 1 | SIMPLE | lguegrou9_ | eq_ref | PRIMARY | PRIMARY | 4 | app_db.lgueinfo7_.groupId | 1 | NULL |
-
| 1 | SIMPLE | lgueitem10_ | eq_ref | PRIMARY | PRIMARY | 4 | app_db.lgueinfo7_.itemId | 1 | NULL |
- +----+-------------+---------------+--------+---------------+---------+---------+-----------------------------+--------+----------------------------------------------------+
關閉BNL最佳化器
-
mysql> set optimizer_switch='index_merge=on,index_merge_union=on,index_merge_sort_union=on,index_merge_intersection=on,engine_condition_pushdown=on,
-
index_condition_pushdown=on,mrr=on,mrr_cost_based=on,block_nested_loop=off,batched_key_access=on,materialization=on,semijoin=on,loosescan=on,firstmatch=on,
- subquery_materialization_cost_based=on,use_index_extensions=on'
-
+----+-------------+---------------+--------+---------------+---------+---------+-----------------------------+------+-------+
-
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
-
+----+-------------+---------------+--------+---------------+---------+---------+-----------------------------+------+-------+
-
| 1 | SIMPLE | this_ | index | NULL | PRIMARY | 4 | NULL | 20 | NULL |
-
| 1 | SIMPLE | gmitem2_ | eq_ref | PRIMARY | PRIMARY | 4 |app_db.this_.itemId | 1 | NULL |
-
| 1 | SIMPLE | gmgroup3_ | eq_ref | PRIMARY | PRIMARY | 4 | app_db.gmitem2_.groupId | 1 | NULL |
-
| 1 | SIMPLE | teaminfo4_ | eq_ref | PRIMARY | PRIMARY | 4 | app_db.this_.guestId | 1 | NULL |
-
| 1 | SIMPLE | lgueinfo5_ | eq_ref | PRIMARY | PRIMARY | 4 | app_db.teaminfo4_.lgueId | 1 | NULL |
-
| 1 | SIMPLE | teaminfo6_ | eq_ref | PRIMARY | PRIMARY | 4 | app_db.this_.homeId | 1 | NULL |
-
| 1 | SIMPLE | lgueinfo7_ | eq_ref | PRIMARY | PRIMARY | 4 | app_db.this_.lgueId | 1 | NULL |
-
| 1 | SIMPLE | areainfo8_ | eq_ref | PRIMARY | PRIMARY | 4 | app_db.lgueinfo7_.areaId | 1 | NULL |
-
| 1 | SIMPLE | lguegrou9_ | eq_ref | PRIMARY | PRIMARY | 4 | app_db.lgueinfo7_.groupId | 1 | NULL |
-
| 1 | SIMPLE | lgueitem10_ | eq_ref | PRIMARY | PRIMARY | 4 | app_db.lgueinfo7_.itemId | 1 | NULL |
- +----+-------------+---------------+--------+---------------+---------+---------+-----------------------------+------+-------+
三 總結
透過這個例子，想告訴大家對線上資料庫的升級操作,最好做必要的壓測。先升級日常環境，然後選擇升級線上環境。對於MySQL新的最佳化點有必要深入研究，瞭解其原理，多做測試。才能發現其中可能隱藏的問題。
來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/22664653/viewspace-1692235/，如需轉載，請註明出處，否則將追究法律責任。
相關文章
- 效能調整一則：buffer busy waits導致主要issueAI
- MongoDB 分片叢集均衡器導致的效能下降MongoDB
- 故障分析 | 大量短時程式導致 cpu 負載過高案例一則負載
- LGWR寫操作會導致效能全域性卡頓案例分析
- undo truncate 導致qps下降分析
- 非索引列直方圖的丟失導致sql效能急劇下降索引直方圖SQL
- linux 下filesystemio_options disk_asynch_io 導致的I/O效能下降Linux
- 一條sql導致資料庫整體效能下降的診斷和解決的全過程(轉)SQL資料庫
- 因為init_command 導致的 mysql 5.6 主從連線失敗的案例一則。MySql
- 【MySQL】檔案描述符導致報警一則MySql
- 效能分析（5）- 軟中斷導致 CPU 使用率過高的案例
- 效能分析（7）- 未利用系統快取導致 I/O 緩慢案例快取
- sql導致資料庫整體效能下降的診斷和解決的全過程SQL資料庫
- 一條insert語句導致的效能問題分析（一)
- 記一次儲存問題導致的rac故障案例
- Oracle優化案例-join列索引缺失導致的sql效能問題（二十六）Oracle優化索引SQL
- MySQL中的Join 的演算法（NLJ、BNL、BKA）MySql演算法
- 一個RMAN備份時導致系統慢解決的案例
- IPA：冠狀病毒導致英國營銷預算大幅下降
- SCHEDULER呼叫XDB程式導致效能問題
- ORACLE analyse table方式收集表統計資訊導致SQL執行計劃不準確而效能下降OracleSQL
- 一條insert語句導致的效能問題分析（二)
- 【案例】MySQL count操作優化案例一則MySql優化
- oracle啟動案例一則Oracle
- IO優化案例一則優化
- 【MySQL】MySQL效能優化之Block Nested-Loop Join(BNL)MySql優化BloCOOP
- MySQL 網路導致的複製報錯案例MySql
- 隱形轉換導致全表掃描案例
- swap空間不足導致mysql被OOM kill案例MySqlOOM
- 序列異常導致災備端應用異常處理一則
- Python+sklearn使用DBSCAN聚類演算法案例一則Python聚類演算法
- MySQL8.0 view導致的效能問題MySqlView
- Code Cache滿導致應用效能降低
- 效能：驗證數字簽名導致卡死
- 【MySQL】MySQL效能最佳化之Block Nested-Loop Join(BNL)MySqlBloCOOP
- AMDU資料抽取案例一則
- ORA-04031錯誤導致當機案例分析
- 儲存多路徑故障導致資料庫死掉案例資料庫