返回顶部
首页 > 资讯 > 数据库 >MySQL 优化器参数derived_merge导致多表关联SQL性能及其低下
  • 438
分享到

MySQL 优化器参数derived_merge导致多表关联SQL性能及其低下

2024-04-02 19:04:59 438人浏览 泡泡鱼
摘要

    最近,Mysql维护中,遇到一个问题,通条sql语句,在mysql 5.6的测试环境中执行速度不到1秒,但是在 mysql 5.7生产环境中执行却要近5分钟,mysql 5

    最近,Mysql维护中,遇到一个问题,通条sql语句,在mysql 5.6的测试环境中执行速度不到1秒,但是在

mysql 5.7生产环境中执行却要近5分钟,mysql 5.7中同样的数据库同样的数据量,更新完表的统计信息后执行

速度还是要2分钟。本次问题的处理与sql语句本身没有关系,只跟mysql数据库自身的优化器参数相关,下面是

问题的分析排查过程,问题分析中的sql语句不需要显示,可以明确sql是多表join连接并且业务不允许更改。

    1、首先,查看测试环境和生产环境中,mysql的sql语句的执行计划

--测试环境,执行计划只需显示局部能说明问题即可

MySQL 优化器参数derived_merge导致多表关联SQL性能及其低下

--生产环境,执行计划只需显示局部能说明问题即可

MySQL 优化器参数derived_merge导致多表关联SQL性能及其低下

    2、从测试与生产环境sql语句的对比可以明显发现,sql的执行计划不一致,后续排查中发现

a、测试环境中sql涉及的表和索引的统计信息都是当天最新的,而生产环境中的相关表和索引的统计信息比较陈旧

b、测试环境 mysql大版本为5.6,生产环境mysql大版本为5.7

    3、问题处理

a、由于sql执行计划不一致,且生产环境统计信息比较旧,重新收集生产环境表的统计信息,收集后sql的执行速度没有

提高,相比测试依然很慢。

b、关注测试环境执行计划derived和<auto_key>,该特性与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执行计划,此时生产环境执行计划与测试环境相同

MySQL 优化器参数derived_merge导致多表关联SQL性能及其低下

--问题sql执行速度由原先的近5分钟到现在的1秒左右

MySQL 优化器参数derived_merge导致多表关联SQL性能及其低下



您可能感兴趣的文档:

--结束END--

本文标题: MySQL 优化器参数derived_merge导致多表关联SQL性能及其低下

本文链接: https://lsjlt.com/news/48290.html(转载时请注明来源链接)

有问题或投稿请发送至: 邮箱/279061341@qq.com    QQ/279061341

猜你喜欢
软考高级职称资格查询
编程网,编程工程师的家园,是目前国内优秀的开源技术社区之一,形成了由开源软件库、代码分享、资讯、协作翻译、讨论区和博客等几大频道内容,为IT开发者提供了一个发现、使用、并交流开源技术的平台。
  • 官方手机版

  • 微信公众号

  • 商务合作