MySQL（二）——查询优化

一、exists和in

(1)当B表的数据集小于A表的数据集时，用in优于exists
      SELECT * from A WHERE id in (SELECT id from B)
等价于：
      for select id from B
      for select * from A where A.id = B.id

(2)当A表的数据集小于B表的数据集时，用exists优于in
      select * from A where exists (select 1 from B where B.id = A.id)
等价于
      for select * from A
      for select * from B where B.id = A.id

(3)小结

• exists

SELECT ... FROM table WHERE EXISTS(subquery)

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

• 提示
  1）EXISTS（subquery）只返回TRUE或FALSE，因此子查询中的select * 也可以是select 1 或 select ‘X’ ，官方说法是实际执行时会忽略select清单，因此没有区别。
  2）EXISTS子查询的实际执行过程可能经过了优化而不是我们理解上的逐条对比，如果担忧效率问题，可进行实际检验以确定是否有效率问题。
  3）EXISTS子查询往往也可以用条件表达式、其他子查询或JOIN来替代，何种最优要具体问题具体分析。

二、order by关键字优化

1、ORDER BY子句，尽量使用Index方式排序，避免使用FileSort方式排序

2、尽可能在索引列上完成排序操作，遵照索引建的最佳左前缀

3、如果不在索引列上，filesort有两种算法：mysql就要启动双路排序和单路排序

1）双路排序：
      MySQL4.1之前是使用双路排序，字面意思就是两次扫描磁盘，最终得到数据，读取行指针和orderby列，对它们进行排序，然后扫描已经排序好的列表，按照列表中的值重新从列表中读取对应的数据输出。从磁盘取排序字段，在buffer进行排序，再从磁盘取其他字段。
      取一批数据，要对磁盘进行了两次扫描，总所周知，I/O是很耗时的，所以在mysql4.1之后，出现了第二种改进的算法，就是单路排序。
2）单路排序：
      从磁盘读取查询需要的所有列，按照order by列在buffer对它们进行排序，然后扫描排序后的列表进行输出，它的效率更快一些，避免了二次读取数据。并且把随机IO变成了顺序IO，但是它会使用更多的空间，因为它把每一行都保存在内存中了。

4、优化策略

提高order by的速度
      1)Order by时select * 是一个大忌，只query需要的字段，这点非常重要。在这里的影响是：

• 当query的字段大小总和小于max_length_for_sort_data而且排序字段不是TEXT|BLOB类型时，会用改进后的算法——单路排序，否则用老算法——多路排序。
• 两种算法的数据都有可能超出sort_buffer的容量，超出之后，会创建tmp文件进行合并排序，导致多次I/O，但是用单路排序算法的风险会更大一些，所以要提高sort_buffer_size。
        2）尝试提高sort_buffer_size
• 不管用哪种算法，提高这个参数都会提高效率，当然，要根据系统的能力去提高，因为这个参数是针对每个进程的。
        3）尝试提高max_length_for_sort_data
• 提高这个参数，会增加用改进算法的概率。但是如果设的太高，数据总容量超出sort_buffer_size的概率就增大，明显症状是高的磁盘I/O活动和低的处理器使用率。

5、小总结

三、group by关键字优化

1、group by 实质是先排序后进行分组，遵照索引建的最佳左前缀。

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

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