【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 即使没有过滤条件用到索引,也可以直
接使用索引。
----尚硅谷_mysql_高级学习笔记