mysql关联分组查询,【mysql】关联查询_子查询_排序分组优化

1. 关联查询优化

1.1 left join

结论：

①在优化关联查询时，只有在被驱动表上建立索引才有效！

②left join 时，左侧的为驱动表，右侧为被驱动表！

1.2 inner join

结论：inner join 时，mysql 会自己帮你把小结果集的表选为驱动表。

2. 子查询优化

结论： 在范围判断时，尽量不要使用not in 和not exists，使用left join on xxx is null 代替。

3. 排序分组优化

where 条件和on 的判断这些过滤条件，作为优先优化的部门，是要被先考虑的！其次，如果有分组和排序，那么

也要考虑grouo by 和order by。

3.1 无过滤，不索引

using filesort 说明进行了手工排序！原因在于没有where 作为过滤条件！

结论： 无过滤，不索引。where，limt 都相当于一种过滤条件，所以才能使用上索引！

3.2 顺序错，必排序

3.3 方向反，必排序

3.4 过滤和排序使用索引的选择

原因：所有的排序都是在条件过滤之后才执行的，所以如果条件过滤了大部分数据的话，几百几千条数据进行排序

其实并不是很消耗性能，即使索引优化了排序但实际提升性能很有限。相对的empno<101000 这个条件如果没

有用到索引的话，要对几万条的数据进行扫描，这是非常消耗性能的，使用empno 字段的范围查询，过滤性更好

(empno 从100000 开始)！

结论： 当范围条件和group by 或者order by 的字段出现二选一时，优先观察条件字段的过滤数量，如果过滤的

数据足够多，而需要排序的数据并不多时，优先把索引放在范围字段上。反之，亦然。

3.5 using filesort

mysql 的排序算法：

①双路排序

MySQL 4.1 之前是使用双路排序,字面意思就是两次扫描磁盘，最终得到数据，读取行指针和orderby 列，对他

们进行排序，然后扫描已经排序好的列表，按照列表中的值重新从列表中读取对应的数据输出。

从磁盘取排序字段，在buffer 进行排序，再从磁盘取其他字段。

简单来说，取一批数据，要对磁盘进行了两次扫描，众所周知，I\O 是很耗时的，所以在mysql4.1 之后，出现

了第二种改进的算法，就是单路排序。

②单路排序

从磁盘读取查询需要的所有列，按照order by 列在buffer 对它们进行排序，然后扫描排序后的列表进行输出，

它的效率更快一些，避免了第二次读取数据。并且把随机IO 变成了顺序IO,但是它会使用更多的空间，

因为它把每一行都保存在内存中了。

③单路排序的问题

由于单路是后出的，总体而言好过双路。但是存在以下问题：

在sort_buffer 中，方法B 比方法A 要多占用很多空间，因为方法B 是把所有字段都取出, 所以有可能取出的数

据的总大小超出了sort_buffer 的容量，导致每次只能取sort_buffer 容量大小的数据，进行排序(创建tmp 文件，多

路合并)，排完再取取sort_buffer 容量大小，再排……从而多次I/O。

结论：本来想省一次I/O 操作，反而导致了大量的I/O 操作，反而得不偿失。

如何优化：

①增大sort_butter_size 参数的设置

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

程的1M-8M 之间调整。

②增大max_length_for_sort_data 参数的设置

mysql 使用单路排序的前提是排序的字段大小要小于max_length_for_sort_data。

提高这个参数，会增加用改进算法的概率。但是如果设的太高，数据总容量超出sort_buffer_size 的概率就增大，

明显症状是高的磁盘I/O 活动和低的处理器使用率。(1024-8192 之间调整)。

③减少select 后面的查询的字段。

当Query 的字段大小总和小于max_length_for_sort_data 而且排序字段不是TEXT|BLOB 类型时，会用改进后的

算法——单路排序， 否则用老算法——多路排序。

两种算法的数据都有可能超出sort_buffer 的容量，超出之后，会创建tmp 文件进行合并排序，导致多次I/O，

但是用单路排序算法的风险会更大一些,所以要提高sort_buffer_size。

3.6 使用覆盖索引

覆盖索引：SQL 只需要通过索引就可以返回查询所需要的数据，而不必通过二级索引查到主键之后再去查询数据。

3.7 group by

group by 使用索引的原则几乎跟order by 一致，唯一区别是groupby 即使没有过滤条件用到索引，也可以直

接使用索引。

标签：sort,buffer,查询,索引,单路,过滤,分组,mysql,排序

来源： https://www.cnblogs.com/wjqhuaxia/p/12832657.html