【案例】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;

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

    | 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新的优化点有必要深入研究，了解其原理，多做测试。才能发现其中可能隐藏的问题。