前面介紹了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;
+----+-------------+---------------+--------+---------------+---------+---------+-----------------------------+--------+----------------------------------------------------+
| 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 |
this_ 表原來可以通過主鍵來擷取資料,在使用了BNL算法之後卻導緻全表掃描。
關閉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新的優化點有必要深入研究，了解其原理，多做測試。才能發現其中可能隐藏的問題。
![【MySQL資料庫】資料庫索引事務1.索引2.事務[圖]](data:image/gif;base64,R0lGODlhAQABAIAAAP///wAAACwAAAAAAQABAAACAkQBADs=)