开发者社区> 北在南方> 正文

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


版权声明:本文内容由阿里云实名注册用户自发贡献,版权归原作者所有,阿里云开发者社区不拥有其著作权,亦不承担相应法律责任。具体规则请查看《阿里云开发者社区用户服务协议》和《阿里云开发者社区知识产权保护指引》。如果您发现本社区中有涉嫌抄袭的内容,填写侵权投诉表单进行举报,一经查实,本社区将立刻删除涉嫌侵权内容。

相关文章
《算法技术手册》一2.4.7 性能不明显的计算
本节书摘来华章计算机《算法技术手册》一书中的第2章 ,第2.4.7节, George T.Heineman Gary Pollice Stanley Selkow 著 杨晨 曹如进 译 译更多章节内容可以访问云栖社区“华章计算机”公众号查看。
922 0
一致性哈希算法的php实现与分析-算法
<?php/** 一致性哈希算法* 过程:* 1,抽象一个圆,然后把服务器节点按一定算法得到整数有序顺时针放到圆上,圆环用2^32 个点来进行均匀切割。* hash函数的结果应该均匀分布在[0,2^32-1]区间* 2,由于服务器少,在圆上分布不均匀会造成数据倾斜,所以我们使用虚拟节点代替服务器的节点,一个服务器生成32个虚拟节点,或者更多。
1391 0
一致性哈希算法应用与分析
  一致性哈希算法主要使用在分布式数据存储系统中,按照一定的策略将数据尽可能均匀分布到所有的存储节点上去,使得系统具有良好的负载均衡性能和扩展性。感觉一致性哈希与数据结构中的“循环队列”还是有一点联系的。
786 0
阿里巴巴达摩院夺得首届“马栏山杯”国际音视频算法优化大赛【画质损伤修复赛道】冠军
首届“马栏山杯”国际音视频算法优化大赛颁奖盛典暨高峰论坛于9月8日举行。这场由中国工业与应用数学学会、中国网络社会组织联合会作为指导单位,湖南省互联网信息办公室、湖南省科学技术协会主办,中国(长沙)马栏山视频文创产业园、芒果TV承办的算法盛事,云集了全球优秀的算法精英。一大批来自高校、科研院所、互联网企业才子才女们,共1294支队伍报名参赛,其中北京大学34支,清华大学25支,麻省理工学院等国外顶级名校37支。
614 0
【愚公系列】2021年11月 C#版 数据结构与算法解析 for和foreach性能分析
【愚公系列】2021年11月 C#版 数据结构与算法解析 for和foreach性能分析
26 0
常见的一致性哈希算法#Java实现#
    之前参与过缓存框架的封装与测试工作,并对一致性哈希算法进行了相关的调研。通过对spymemcached与jedis等客户端源码的阅读对一致性哈希算法的Java实现进行调研: 1. 使用TreeMap实现,TreeMap本身继承NavigatableMap,因此具备节点导航的特点 2. 通
2256 0
+关注
640
文章
0
问答
文章排行榜
最热
最新
相关电子书
更多
JS零基础入门教程(上册)
立即下载
性能优化方法论
立即下载
手把手学习日志服务SLS,云启实验室实战指南
立即下载