mysql优化order by_MySQL优化之order by

如何进行更有效排序

前言

在我们使用order by排序的时候，如果用explain分析一下sql语句，会发现有的情况下Extra列会出现Using filesort。1

2

3

4

5

6

7

8

9

10

11

12

13

14EXPLAIN SELECT * FROM tbl_organization where id > 'SQ0000' ORDER BY site_id;

Name |Value |

-------------|---------------------------|

id |1 |

select_type |SIMPLE |

table |tbl_organization |

type |range |

possible_keys|PRIMARY |

key |PRIMARY |

key_len |62 |

ref | |

rows |492 |

Extra |Using where; Using filesort|

关于

什么是filesort呢？首先引用官方手册中的描述：MySQL must do an extra pass to find out how to retrieve the rows in sorted order. The sort is done by going through all rows according to the join type and storing the sort key and pointer to the row for all rows that match the WHERE clause.

中文手册：Mysql需要额外的一次传递，以找出如何按排序顺序检索行，通过根据联接类型浏览所有行并为所有匹配where子句的行保存排序关键字和行的指针来完成排序，然后关键字被排序，并按排序顺序检索行。

总而言之，filesort是一种比较慢的外部排序，应该尽量using index，避免using filesort。

避免

以下条件均会使用filesort，尽量避免：where语句与order by语句，使用了不同的索引

检查的行数过多，且没有使用覆盖索引

ORDER BY中的列不包含在相同的索引，也就是使用了不同的索引

对索引列同时使用了ASC和DESC

where语句或者ORDER BY语句中索引列使用了表达式，包括函数表达式

where 语句与ORDER BY语句组合满足最左前缀，但where语句中使用了条件查询。查见第10句,虽然where与order by构成了索引最左有缀的条件，但是where子句中使用的是条件查询。

优化

在MySQL中filesort 的实现算法实际上是有两种：

双路排序：是首先根据相应的条件取出相应的排序字段和可以直接定位行数据的行指针信息，然后在sort buffer 中进行排序。

单路排序：是一次性取出满足条件行的所有字段，然后在sort buffer中进行排序。

双路排序过程：根据表的索引或者全表扫描，读取所有满足条件的记录。

对于每一行，存储一对值到缓冲区(排序列，行记录指针)，一个是排序的索引列的值，即order by用到的列值，和指向该行数据的行指针，缓冲区的大小为sort_buffer_size大小。

当缓冲区满后，运行一个快速排序(qsort)来将缓冲区中数据排序，并将排序完的数据存储到一个临时文件，并保存一个存储块的指针，当然如果缓冲区不满，则不会重建临时文件了。

重复以上步骤，直到将所有行读完，并建立相应的有序的临时文件。

对块级进行排序，这个类似与归并排序算法，只通过两个临时文件的指针来不断交换数据，最终达到两个文件，都是有序的。

重复5直到所有的数据都排序完毕。

采取顺序读的方式，将每行数据读入内存，并取出数据传到客户端，这里读取数据时并不是一行一行读，读如缓存大小由read_rnd_buffer_size来指定。

采取的方法为：快速排序 + 归并排序，但有一个问题，就是，一行数据会被读两次，第一次是where条件过滤时，第二个是排完序后还得用行指针去读一次。

单路排序过程：读取满足条件的记录

对于每一行，记录排序的key和数据行指针，并且把要查询的列也读出来

根据索引key排序

读取排序完成的文件，并直接根据数据位置读取数据返回客户端，而不是去访问表

这也有一个问题：当获取的列很多的时候，排序起来就很占空间，因此，max_length_for_sort_data变量就决定了是否能使用这个排序算法。

建议：对于使用filesort的慢查询，可以改小一些max_length_for_sort_data来使用第一个方法

对于想要加快order by 的顺序，有以下一些策略：增加sort_buffer_size的大小，如果大量的查询较小的话，这个很好，就缓存中就搞定了

增加read_rnd_buffer_size大小，可以一次性多读到内存中

列的长度尽量小些

改变tmpdir，使其指向多个物理盘(不是分区)的目录，这将机会循环使用做为临时文件区