最近,MySQL维护中,遇到一个问题,通条SQL语句,在mysql 5.6的测试环境中执行速度不到1秒,但是在
mysql 5.7生产环境中执行却要近5分钟,mysql 5.7中同样的数据库同样的数据量,更新完表的统计信息后执行
速度还是要2分钟。本次问题的处理与sql语句本身没有关系,只跟mysql数据库自身的优化器参数相关,下面是
问题的分析排查过程,问题分析中的sql语句不需要显示,可以明确sql是多表join连接并且业务不允许更改。
1、首先,查看测试环境和生产环境中,mysql的sql语句的执行计划
–测试环境,执行计划只需显示局部能说明问题即可
–生产环境,执行计划只需显示局部能说明问题即可
2、从测试与生产环境sql语句的对比可以明显发现,sql的执行计划不一致,后续排查中发现
a、测试环境中sql涉及的表和索引的统计信息都是当天最新的,而生产环境中的相关表和索引的统计信息比较陈旧
b、测试环境 mysql大版本为5.6,生产环境mysql大版本为5.7
3、问题处理
a、由于sql执行计划不一致,且生产环境统计信息比较旧,重新收集生产环境表的统计信息,收集后sql的执行速度没有
提高,相比测试依然很慢。
b、关注测试环境执行计划derived和,该特性与mysql 的参数derived_merge相关,查看该参数的设置情况
–测试环境
MySQL [(none)]> show global variables like ‘%switch%’;
+——————+————————————————————————————————————————————————————————————————————————————————————————————————————————————————–+
| Variable_name | Value |
+——————+————————————————————————————————————————————————————————————————————————————————————————————————————————————————–+
| optimizer_switch | index_merge=on,index_merge_union=on,index_merge_sort_union=on,index_merge_intersection=on,……. |
+——————+—————————————————————————–开发云主机域名———————————————————————————————————————————————————————————————————————————————————————+
1 row in set (0.00 sec)
MySQL [(none)]>
–生产环境,为了方便说明问题,省略了多余的参数显示
mysql> show variables like ‘%switch%’;
+——————+——————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————-+
| Variable_name | Value |
+——————+——————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————-+
| optimizer_switch | ……,
derived_merge=on
|
+——————+——————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————-+
1 row in set (0.00 sec)
mysql>
c、根据官方说明参数derived_merge是在mysql5.7版本中引入的,作用就是对join (select)表连接合并,本次问题sql有
大量的join (select),导致sql执行结果集合并,sql执行速度及其缓慢。
d、通过session级别临时取消该参数,观察sql执行计划,发现sql执行计划正常,且sql执行速度不到1秒与测试环境相近
–取消优化器derived_merge参数
mysql>
set optimizer_switch=”derived_merge=off”;
ERROR 2006 (HY000): MySQL server has gone away
No connection. Trying to reconnect…
Connection id: 8980
Current database: mysql
Query OK, 0 rows affected (0.02 sec)
mysql>
–观察问题sql执行计划,此时生产环境执行计划与测试环境相同
–问题sql执行速度由原先的近5分钟到现在的1秒左右
相关推荐: MySQL 5.7 performance_schema库和sys库常用SQL
performance_schema库常用SQL: 查看没有主键的表: SELECT DISTINCT t.table_schema, t.table_name FROM information_schema.tables AS t LEFT JOIN inf…
免责声明:本站发布的图片视频文字,以转载和分享为主,文章观点不代表本站立场,本站不承担相关法律责任;如果涉及侵权请联系邮箱:360163164@qq.com举报,并提供相关证据,经查实将立刻删除涉嫌侵权内容。