【案例】BNL演算法導致效能下降一則

楊奇龍發表於2015-06-08
  前面介紹了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;

  1. +----+-------------+---------------+--------+---------------+---------+---------+-----------------------------+--------+----------------------------------------------------+
  2. | id | select_type | table         | type   | possible_keys | key     | key_len | ref                         | rows   | Extra                                              |
  3. +----+-------------+---------------+--------+---------------+---------+---------+-----------------------------+--------+----------------------------------------------------+
  4. | 1  | SIMPLE      | this_         | ALL    | NULL          | NULL    | NULL    | NULL                        | 257312 | Using temporary; Using filesort                    |
  5. | 1  | SIMPLE      | gmitem2_      | eq_ref | PRIMARY       | PRIMARY | 4       | app_db.this_.itemId         | 1      | NULL                                               |
  6. | 1  | SIMPLE      | gmgroup3_     | ALL    | PRIMARY       | NULL    | NULL    | NULL                        | 6      | Using where; Using join buffer (Block Nested Loop) |
  7. | 1  | SIMPLE      | teaminfo4_    | eq_ref | PRIMARY       | PRIMARY | 4       | app_db.this_.guestId        | 1      | NULL                                               |
  8. | 1  | SIMPLE      | lgueinfo5_    | eq_ref | PRIMARY       | PRIMARY | 4       | app_db.teaminfo4_.lgueId    | 1      | NULL                                               |
  9. | 1  | SIMPLE      | teaminfo6_    | eq_ref | PRIMARY       | PRIMARY | 4       | app_db.this_.homeId         | 1      | NULL                                               |
  10. | 1  | SIMPLE      | lgueinfo7_    | eq_ref | PRIMARY       | PRIMARY | 4       | app_db.this_.lgueId         | 1      | NULL                                               |
  11. | 1  | SIMPLE      | areainfo8_    | eq_ref | PRIMARY       | PRIMARY | 4       | app_db.lgueinfo7_.areaId    | 1      | NULL                                               |
  12. | 1  | SIMPLE      | lguegrou9_    | eq_ref | PRIMARY       | PRIMARY | 4       | app_db.lgueinfo7_.groupId   | 1      | NULL                                               |
  13. | 1  | SIMPLE      | lgueitem10_   | eq_ref | PRIMARY       | PRIMARY | 4       | app_db.lgueinfo7_.itemId    | 1      | NULL                                               |
  14. +----+-------------+---------------+--------+---------------+---------+---------+-----------------------------+--------+----------------------------------------------------+
this_ 表原來可以透過主鍵來獲取資料,在使用了BNL演算法之後卻導致全表掃描。
關閉BNL最佳化器

  1. mysql> set optimizer_switch='index_merge=on,index_merge_union=on,index_merge_sort_union=on,index_merge_intersection=on,engine_condition_pushdown=on,
  2. 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,
  3. subquery_materialization_cost_based=on,use_index_extensions=on'
新的執行計劃如下:
  1. +----+-------------+---------------+--------+---------------+---------+---------+-----------------------------+------+-------+
  2. | id | select_type | table         | type   | possible_keys | key     | key_len | ref                         | rows | Extra |
  3. +----+-------------+---------------+--------+---------------+---------+---------+-----------------------------+------+-------+
  4. | 1  | SIMPLE      | this_         | index  | NULL          | PRIMARY | 4       | NULL                        | 20   | NULL  |
  5. | 1  | SIMPLE      | gmitem2_      | eq_ref | PRIMARY       | PRIMARY | 4       |app_db.this_.itemId          | 1    | NULL  |
  6. | 1  | SIMPLE      | gmgroup3_     | eq_ref | PRIMARY       | PRIMARY | 4       | app_db.gmitem2_.groupId     | 1    | NULL  |
  7. | 1  | SIMPLE      | teaminfo4_    | eq_ref | PRIMARY       | PRIMARY | 4       | app_db.this_.guestId        | 1    | NULL  |
  8. | 1  | SIMPLE      | lgueinfo5_    | eq_ref | PRIMARY       | PRIMARY | 4       | app_db.teaminfo4_.lgueId    | 1    | NULL  |
  9. | 1  | SIMPLE      | teaminfo6_    | eq_ref | PRIMARY       | PRIMARY | 4       | app_db.this_.homeId         | 1    | NULL  |
  10. | 1  | SIMPLE      | lgueinfo7_    | eq_ref | PRIMARY       | PRIMARY | 4       | app_db.this_.lgueId         | 1    | NULL  |
  11. | 1  | SIMPLE      | areainfo8_    | eq_ref | PRIMARY       | PRIMARY | 4       | app_db.lgueinfo7_.areaId    | 1    | NULL  |
  12. | 1  | SIMPLE      | lguegrou9_    | eq_ref | PRIMARY       | PRIMARY | 4       | app_db.lgueinfo7_.groupId   | 1    | NULL  |
  13. | 1  | SIMPLE      | lgueitem10_   | eq_ref | PRIMARY       | PRIMARY | 4       | app_db.lgueinfo7_.itemId    | 1    | NULL  |
  14. +----+-------------+---------------+--------+---------------+---------+---------+-----------------------------+------+-------+
關閉該特性之後 ,執行計劃選擇了正確的索引,執行時間大幅度下降。

三 總結
  透過這個例子,想告訴大家對線上資料庫的升級操作,最好做必要的壓測。先升級日常環境,然後選擇升級線上環境。對於MySQL新的最佳化點有必要深入研究,瞭解其原理,多做測試。才能發現其中可能隱藏的問題。

來自 “ ITPUB部落格 ” ,連結:http://blog.itpub.net/22664653/viewspace-1692235/,如需轉載,請註明出處,否則將追究法律責任。

相關文章