Mysql---查询排序优化

1.小表驱动大表

即小的数据集驱动大的数据集

select * from A where id in(where id from B)

等价于：

select id from B
select *  from A where A.id=B.id

• 当B表的数据集小于A表的数据集时，用 in 优于exists

select  * from A where exists(select 1 from B where B.id=A.id)

等价于

select * from A
select * from B where B.id=A.id

• 当B表的数据集大于A表的数据集时，用exists 优于in

select ....from tableName where EXISTS(subquery)

该语法可以理解为:将主查询的数据，放到子查询中做条件验证，根据验证结果（True or False） 来决定主查询的数据是否得以保留。

2.order by 避免产生Using filesort

order by 也要遵循索引最左匹配原则才行的,使用where子句与order by 子句条件列租户满足索引最左匹配

3.order by排序条件和索引顺序不满足情况

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

从磁盘读取排序字段，在内存中进行排序，再从磁盘读取其他字段

取一批数据，要对磁盘进行两次扫描。比较消耗I\O，在mysql 4.1之后，就出现了改进算法：单路排序

• 单路排序：从磁盘读取查询需要的所有列，按照order by列在内存中对他们进行排序，然后扫描排序后的列进行输出（这样的效率快一些，避免了二次读取数据，但会使用更多空间，因为每一行的数据都要保存在内存中）

从磁盘中读取所有需要的列，在内存中进行排序

• 总结：

单路排序总体而言要好于双路排序，但单路也存在一些问题：由于单路排序比较占内存，如果读取的数据量很大，超高了内存的量，就需要每次只能读取少于内存量的数据，再进行排序（创建tmp文件，多路合并，再排序），需要多次读取排序，这样对产生跟多I\O操作，因此需要根据查询数据量而定。

• 优化策略：

（1）增大sort_buffer_size参数的设置（增大内存大小）；

（2）增大max_length_for_sort_data参数的设置；

（3）查询语句优化：使用order by 不要使用select *

4.