mysql 查询优化 exists_Mysql查询优化order by /exists/group 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 对于排序来说，多个相等条件也是范围查询