MySQL 高级 -- 性能优化查询截取分析

👨‍🎓作者：Java 学术趴

🏦仓库：Github、Gitee

✏️博客：CSDN、掘金、InfoQ、云+社区

💌公众号：Java 学术趴

🚫特别声明：原创不易，未经授权不得转载或抄袭，如需转载可联系小编授权。

🙏版权声明：文章里的部分文字或者图片来自于互联网以及百度百科，如有侵权请尽快联系小编。微信搜索公众号 Java 学术趴联系小编。

☠️每日毒鸡汤：一件事你犹豫去不去做，那就是该立即动身做的。

4. 查询截取分析

4.1 优化步骤

• 慢查询的开启并捕获。

• explain + 慢 SQL 分析。

• show profile 查询 SQL 在 MySQL 服务器里面的执行细节和生命周期情况。

• SQL 数据库服务器的参数调优。

4.2 小表驱动大表

4.2.1 优化原则

优化原则：小表驱动大表，即小的数据集驱动大的数据集。

4.2.2 EXISTS

语法格式：

SELECT * FROM table WHERE EXISTS(subquery[子查询])

EXISTS 特点：

• EXISTS(subquery)只返回 TRUE 或者 FALSE，因为资产寻中的 SELECT * 也可以是 SELECT 1 或者 SELECT 'X'，官方说法是实际执行时会忽略 SELECT 清单，因此没有区别。

• EXISTS 子查询的实际执行过程可能经过了优化而不是我们理解上的逐条对比。

• EXISTS 子查询往往也可以用条件表达式、其他子查询或者 JOIN 来替代，何种最优需要根据场景具体分析。

4.2.3 IN 和 EXISTS 的区别

适用表的类型不同

• in 语句：是子查询为驱动表，外面的表为被驱动表，故适用于子查询结果集小而外面的表结果集大的情况。

• exists 语句：是外面的表为驱动表，子查询里面的表为被驱动表，故适用于外面结果集小而子查询结果集大的情况。

子查询关联不同

• exists 语句：一般都是关联子查询。对于关联子查询，必须先执行外层查询，接着对所有通过过滤条件的记录，执行内存查询。外层查询的内层查询相互依赖，一位外层查询会把数据传递给内层查询。

• in 语句：一般都是非关联子查询，非关联子查询查询则必须先完成内层查询，在执行外层查询。

执行次数不同

• in 语句：只执行一次，确定给定的值是否与子查询或列表中的值相匹配。in 子查询的时候，首先查询子查询的表，然后将内标和外表做一个笛卡尔积，然后按照条件进行筛选，所以相对内表比较小的时候，in 的速度较快。

• exists 语句：执行次数根据表的长度而定。只当一个子查询，检测行的存在，遍历循环外表，然后看外表中的记录有没有和内表的数据是一样的，匹配上的就将结果放入到结果集中。

4.3 ORDER BY 排序优化

4.3.1 ORDER BY 排序示例

排序时索引也用到了，只不过此时该索引不是用来查询，而是用来排序，explain 没有展示出来

此时生效的索引：c1,c2

c1,c2 索引都用到了，直接使用 c3 进行排序，此时和 c4 没有什么关系

此时生效的索引：c1,c2

c1,c2 索引都用到了，此时直接使用 c4 排序，导致 c3 出现断层，MySQL 优化器不能直接进行排序，在内部进行了一次 filesort 内排序 ，使 SQL 的性能下降。

c1 索引用到了，c2,c3 是按照顺序进行排序的，没有发生内排序，c2,c3 索引也用到了，只不过用在了排序上，而不是用在查询上。

c1 索引用到了，但是排序的索引顺序反了，发生了内排序。

第一个因为在条件中已经声明了索引 c2，所以在排序的时候会忽略这个 c2 索引，不会发生内排序现象。

第二个因为在条件中没有声明 c2，所以直接在排序中使用 c2 不会被忽略，c2、c3 索引顺序相反会发生内排序现象。

排序默认是升序排序，但是此时非要实现降序排序，这就会导致 MySQL 发生内排序(filesort)

ORDER BY a DESC,b DESC : 此时索引生效，此时都是降序。

ORDER BY a ASC,b ASC: 此时索引生效，此时都是升序。

ORDER BY a DESC,b ASC: 此时索引不生效，既存在升序又存在降序。

4.3.2 效率声明

• MySQL 支持两种方式的排序，FileSort 以及 Index，Index 效率高，它指 MySQL 扫描索引本身完成排序。FoleSort 方式效率较低。

4.3.3 ORDER BY 满足两种情况，会使用 Index 排序

• Order by 语句使用索引最左前列。

• 使用 Where 子句与 Order by 子句条件列组合满足索引最左前列。

4.3.4 如果不在索引列上，filesort 有两种算法

• 单路排序：从磁盘读取查询需要的所有列，按照 order by 列在 buffer 对他们进行排序，然后扫描排序后的列表进行输出，它的效率更快一些，避免了第二次读取数据。并且把随机 IO 变成了顺序 IO，但是它会使用更多的空间，因为它把每一行都保存在内存中。

• 双路排序：MySQL4.1 之前使用的是双路排序，字面意思就是两次扫描磁盘，最终得到数据，读取行指针和 order by 列，对他们进行排序，然后扫描已经排序好的列表，按照列表中的值重新从列表中读取对应的数据输出。从磁盘取排序字段，在 Buffer 进行排序，再从磁盘取其他字段。

4.3.5 提高 ODERY BY 的速度

• 存在 order by 时使用 select * 是一个大忌，我们只将自己想要的字段查出来即可。(存在两个问题)

• 当查询的字段大小总和小于 max_length_for_sort_data，而且排序字段不是 TEXT|BLOB 类型时，会用改进后的算法——单路排序，否则用老算法——多路排序。

• 两种算法的数据都有可能超出 sort_buffer 的容量，超出之后，会创建 tmp 临时文件进行合并排序，导致多次 I/O ，但是使用单路排序算法的风险会更大一些，所以要提高 sort_buffer_size。

• 尝试提高 sort_buffer_size

• 不管用哪种算法，提高这个参数都会提高效率，当然，要根据系统的能力去提高，因为这个参数是针对每个进程的。

• 尝试提高 max_length_for_sort_data

• 提高这个参数，会增加用改进算法的概率 。但是如果设的太高，数据总容量超出 sort_buffer_size 的概率就增大，明显症状是高的磁盘 I/O 活动和低的处理器使用率。

4.4 GROUP BY 分组优化

• GROUP BY 实质是先排序后进行分组，遵照索引建的最佳左前缀。

• 当无法使用索引列，增大 max_length_for_sort_data 参数的设置 + 增大 sort_buffer_size 参数的设置。

• where 高于 having，能写在 where 限定的条件就不要去 having 限定了。

• 其他的性质和 ORDER BY 一致。