Mysql查询优化order by /exists/group by

查询优化

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

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

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
当A表的数据集小于B表的数据集时，用exists优于in

• exists

select ... from table where exists (subquery)
# 该语法可以理解为： 将主查询的数据，放到子查询中做条件验证，根据验证结果(True or False),来决定主查询的数据结果是否得以保证。

• 提示：

1.exists(subquery)只返回True or False ,因此子查询中select * 也可以是select 1或是 select 'X' .官方说法是实际执行时会忽略select清单，因此没有区别。
2.exists子查询的实际执行过程可能经过了优化而不是我们理解上的逐条对比，如果担忧效率问题，可进行实际检验以确定是否有效率问题
3.exists子查询往往也可以用条件表达式，其他子查询或者 join来代替，何种最优需要具体问题具体分析。	

order by 子句

• order by 子句，尽量使用Index方式排序，避免使用FileSort方式排序。

  • 为了方便演示，先创建一个表并插入数据数据

  create table tblA(
  id int primary key not null auto_increment,
  age int,
  birth timestamp not null
  );
  
  insert into tblA(age,birth)values(22,now());
  insert into tblA(age,birth)values(23,now());
  insert into tblA(age,birth)values(24,now());
  ...
  
  # 创建索引
  create index idx_A_ageBirth on tblA(age,birth);
  

• 首先我们看几个order by执行计划

  

  不会产生filesort
  

  

  不会产生filesort,因为order by 排序是按照创建索引顺序排列的且覆盖全索引
  

  

  # 会产生 原因是age 为范围排序，导致后面索引birth失效。最后使用filesort
  

  

  会使用到filesort   order by 后面索引字段颠倒顺序
  

  

  order by 后面没有遵照最左前缀
  

  

  会使用filesort，与上例一样。order by 后面没有遵照最左前缀
  

  

  不会使用filesort
  

  

  会使用filesort,又升序又降序，导致MySQL无法理解
  

• MySQL支持两种方式排序，FileSort和Index,当然Index效率高，它指MySQL扫描索引本身完成的排序，而FileSort方式效率比较低。

  由上面例子，不难看出规律， order by 满足一下2种情况，会使用Index方式排序：

  order by 语句也遵循最左前缀原则
  使用where子句与order by子句条件列组合满足索引最左前列
  

• 索引我们有必要尽可能在索引列上完成排序操作，遵照索引建在最左前缀。如果不在索引列上，则会使用filesort，而filesort又有2种算法，mysql就要启动双路排序和单路排序、

• 双路排序？单路排序？又是什么鬼？

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

• 但是单路排序又存在着问题：

  在sort_buffer中，单路操作要比双路操作占用更多空间，因为单路操作是把所有字段取出，
  所以有可能取出的数据总大小超出了sort_buffer的容量，导致每次只能取sort_buffer容量大小的数据，进行排序（创建tmp文件，多路合并），排完再取sort_buffer容量大小，再排序... 从而多次I/O操作
  
  本想省一次I/O操作，反而导致了大量的I/O操作，反而得不偿失。
  

• 那么如何解决或是如何优化？

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

• order by 小总结：

  当我们索引  key a_b_c(a,b,c)
  	Mysql两种排序方式：文件排序Using filesort/有序索引Using index
  
  order by 能使用索引最左前缀情况：
  	order by a
  	order by a,b
  	order by a,b,c
  	order by a DESC,b DESC,c DESC
  如果有where如果where使用索引的最左前缀定义为常量，则order by 能使用索引：
  	where a=const order b,c
  	where a=const and b=const order by c
  	where a=const order by b,c
  	where a=const and b>const order by b,c
  不能使用索引进行排序情况：
      order by a ASC,b DESC,c DESC  排序不一致
      where g=const order by b,c    丢失a索引
      where a=const order by c      丢失b索引
      where a=const order by a,d    d不是索引的一部分
      where a in (...) order by b,c  对于排序来说，多个相等条件也是范围查询
  
  

group by 分组

• group by与order by 一样，group by 实质是先排序后进行分组，遵照索引建在最佳左前缀上，当无法使用索引列，增大max_length_for_sort_data参数的设置+增大sort_buffer_size参数的设置，where高于having，能写在where限定的条件就不要去having限定了。