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

    最近，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和<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执行计划，此时生产环境执行计划与测试环境相同 --问题sql执行速度由原先的近5分钟到现在的1秒左右