记录一个MySQL性能调优问题

概述：因为工作接触到mysql数据库，这两天遇到了一个‘奇怪’的性能问题，一个简单的join查询效率非常的低，数据量过万几乎就能导致数据库瘫痪，通过研究以及查阅网上的资料，了解到是因为mysql5.7引入的derived_merge引起的，而通过将其设置derived_merge=off或者子查询中使用limit等关键字可以使其失效。

下面使从发现问题到解决问题的大概过程；

 

分别执行两个数据表的情况：

 

 

将这两个表进行关联查询：

 

是的，反复测试这么简单的查询结果就是这么让人绝望，于是我决定对这个问题进行刨根问底的优化，首先想到的是会不会因为时间或是字符串在没有索引下导致的MySQL查询效率低下的原因呢？下面的命令是更改关联条件为数值类型时的效果，而且实际业务有时确实需要嵌套使用，结果就是这么凄惨：

 

 

在反复尝试中无意发现这样写可以有效解决此问题：

 

这是一个很神奇的问题，好奇心驱使我继续研究下去，打开执行计划EXPLAIN：

下图的执行计划可以看出关联语句一需要检索数据的笛卡尔积为：rows--13516x10773=145607868也就是检索145607868次：

 

下图是关联语句二，需要检索数据的笛卡尔积为：rows--13516x10=135160

经过一番搜索得出mysql在嵌套查询时生成了派生表derived会带有索引，使得rows的值为10；

rows的定义： MySQL根据表统计信息及索引选用情况，估算的找到所需的记录所需要读取的行数

 

 

这样就不难解释为什么加了limit后查询速度大大加快的原因，但是最初的疑问还是没能得到解决，因为这只是一个非常简单的关联查询啊，以前sql server数据库中这是完全不会存在的，于是我又开始向度娘求索，找到了答案；

mysql5.7引入对子查询的优化参数 derived_merge=on，即派生表合并，它对关联查询无疑是致命的，所以，通过执行set session optimizer_switch='derived_merge=off';将它关闭，下面我们再看看那个比蜗牛还慢的sql语句在设置后的执行效果：

 

 

 

同时也通过网上了解到，除了limit，聚合函数，distinct，group by，having，union or union all等都可以使derived_merge失效

到了这里，这个查询性能问题也就彻底分析清楚并且得到了解决，通过这个问题了解到的东西展示的比较零散，主要还是表达下分析问题的思路。

本人初次接触mysql，如有不对的地方或者更好的见解希望大家多多指教。