使用orderby时经常会出现Usingfilesort，所以我们需要尽量优化这种SQL语句，让它尽可能的使用索引。那么，我们如何优化这类语句呢？因为这部分还是比较容易混淆的，所以做了个实验，相信大家和我一起实验后就明白了~1.环境准备droptableifexiststest;createtabletest(idintprimarykeyauto_increment,c1varchar(10),c2varchar(10),c3varchar(10),c4varchar(10),c5varchar(10))ENGINE=INNODBdefaultCHARSET=utf8;insertintotest(c1,c2,c3,c4,c5)values('a1','a2','a3','a4','a5');插入测试(c1,c2,c3,c4,c5)值('b1','b2','b3','b4','b5');插入测试(c1,c2,c3,c4,c5)values('c1','c2','c3','c4','c5');插入测试(c1,c2,c3,c4,c5)values('d1','d2','d3','d4','d5');插入测试(c1,c2,c3,c4,c5)值('e1','e2','e3','e4','e5');2。创建btree索引createindexidx_c1234ontest(c1,c2,c3,c4);showindexfromtest;3.范围扫描导致全表扫描explainselect*fromtestwherec1>'a1'orderbyc1;分析：在c1中，在c2、c3、c4上创建索引，在c1上直接使用range，导致索引失效。全表扫描：type=ALL，ref=Null。因为此时c1主要用于排序，而不是查询。使用c1排序，出现Usingfilesort。解决方法：使用覆盖索引。4.覆盖索引--》optimizeexplainselectc1fromtestwherec1>'a1'orderbyc1;分析：使用的是覆盖索引，而不是fullscan，使用的是indexrangescan，排序的时候是按照索引的顺序，所以不会出现使用filesort。这里不懂也没关系，后面我会分享索引的八个规则，保证你能看懂...5.不按最左边列索引排序explainselectc1fromtestwherec1>'a1'orderbyc2;分析：这里出现Usingfilesort是因为它是用来对c2进行排序的，与索引创建顺序不一致(c1,c2,c3,c4)6.索引列的排序顺序与索引创建顺序相反explainselectc1fromtestwherec1>'a1'orderbyc2,c1;分析：这里出现Usingfilesort，因为排序索引列(c2,c1)和索引创建顺序(c1,c2)相反，所以发生重排，并且出现usingfilesort7.orderbyindexcolumns的排序不一致explainselectc1fromtestwherec1>'a1'orderbyc1asc,c2desc;分析：虽然排序后的字段列和索引的顺序是一样的，并且order默认是升序，但是这里c2desc变成了降序，导致和索引的排序方式不一样，导致Usingfilesort。如果是orderbyc1asc,c2ascororderbyc1desc,c2desc会使用索引。实验总结1、MySQL支持两种排序方式：filesort和index，使用索引是指MySQL自己扫描索引来完成排序。索引是高效的，但文件排序是低效的。2.使用索引进行排序假设KEYtest(a,b,c)(1)orderby可以使用索引的最左边前缀-orderbya-orderbya,b-orderbya,b,c-orderbyaasc,basc,casc-orderbyadesc,bdesc,cdesc(2)如果where使用索引最左边的前缀作为常量定位，那么orderby可以使用索引-wherea=constorderbyb,c-wherea=constandb=constorderbyc-wherea=constandb>consstorderbyb,c(3)不能使用索引进行排序-orderbyaasc,bdesc,cdesc/*排序不一致*/-whereeg=constorderbyb,c/*lostaindex*/-wherea=constorderbyc/*lostbindex*/-wherea=constorderbya,d/*d不属于索引*/-whereain(....)orderbyb,c/*用于排序，多个相等条件也是范围查询*/3。filesort有两种排序算法：two-waysorting和one-waysortingTwo-waysorting：在MySQL4.1之前使用two-waysorting，意思是两次磁盘扫描得到最后的数据。读取行指针和按列排序，对它们进行排序，然后扫描排序后的链表，根据链表中的值重新读取链表输出的相应数据。即从磁盘中读取排序字段，在缓冲区中排序，然后从磁盘中取出其他字段。如果使用双向排序，则需要两次扫描磁盘才能获取一批数据。众所周知，I/O操作是耗时的。因此，在MySQL4.1之后，出现了一种改进的算法：单向排序。单向排序：从磁盘中查询出需要的列，在缓冲区中按列顺序排序，然后扫描排序后的列表输出。它更高效，避免了第二次读取数据，并将随机I/O转换为顺序I/O，但使用更多空间，因为它将每一行都保留在内存中。但是当读取的数据超过sort_buffer的容量时，会导致多次读取数据并创建临时表，最后多次合并，导致多次I/O，反而增加了它的I/O操作。解决方法：增加sort_buffer_size参数的设置。增加max_length_for_sort_data参数的设置。4.提高orderby速度使用orderby时，不要使用select*，只查询必填字段。因为当查询字段过多时，sort_buffer会不够用，所以会进行多路排序或多次I/O操作。增加sort_buffer_size。增加max_length_for_sort_data。5.优化groupbygroupby和orderby很相似。其实质是先排序后分组，遵循索引创建顺序最好左前缀规则。当索引列不能使用时，还需要调整sort_buffer_size和max_length_for_sort_data参数。注意where高于having，where可以写的条件不要受having的限制。
