mysql排序内存排序,mysql InnoDB的排序

利用索引排序

规则：

满足索引的最左前缀要求。

order by 字段要有索引。

如果是多列索引，索引的列顺序和ORDER BY子句的顺序完全一致，并且所有列的排序方向(倒序或正序)都一样时，MySQL才能够使用索引来对结果做排序。

如果有where，并且是多列索引，需要满足最左原则，where条件必须和索引的顺序一致，如果只用到单列则必须是最左列。

如果查询需要关联多张表，则只有当ORDER BY子句引用的字段全部为第一个表(驱动表)时，才能使用索引做排序。

example:

可以:

1、SELECT [column1],[column2],…. FROM [TABLE] ORDER BY [sort]

-- > 在[sort]这个栏位上建立索引就可以实现利用索引进行order by 优化。

2、SELECT [column1],[column2],…. FROM [TABLE] WHERE [columnX] = [value] ORDER BY [sort]

-- > 建立一个联合索引(columnX,sort)来实现order by 优化。

不可以：

1、KEY (realname,sex,age) : SELECT * FROM test WHERE realname = 'wen' ORDER BY sex DESC ,age ASC

-- 不可以(排序顺序不对)

2、KEY (realname,sex,age) : SELECT * FROM test WHERE realname = 'wen' ORDER BY age

-- 不能(不满足最左匹配)

3、in ：SELECT [column1],[column2],…. FROM [TABLE] WHERE [columnX] IN ([value1],[value2],…) ORDER BY[sort]

-- in多列索引不行；

4、SELECT * FROM test WHERE realname = 'wen' ORDER BY age

-- 不满足最左匹配

5、(key1),(key2) : SELECT * FROM t1 ORDER BY key1, key2

-- 对不同的索引键做 ORDER BY ：(key1,key2分别建立索引) 不行。

6、KEY (realname,sex,age) : SELECT * FROM test WHERE realname > 'wen' ORDER BY sex,age

-- 多列索引，查询在索引第一列上是范围条件不行。

复制代码

filesort排序

介绍：

当MySQL不能使用索引进行排序时，就会利用自己的排序算法(快速排序算法)在内存(sort buffer)中对数据进行排序，如果内存装载不下，它会将磁盘上的数据进行分块，再对各个数据块进行排序，然后将各个块合并成有序的结果集(实际上就是外排序)。

对于filesort，MySQL有两种排序算法。

(1)、两遍扫描算法(Two passes)：

实现方式是先将须要排序的字段和可以直接定位到相关行数据的指针信息取出，然后在设定的内存(通过参数sort_buffer_size设定)中进行排序，完成排序之后再次通过行指针信息取出所需的Columns。 -- 注：该算法是4.1之前采用的算法。

缺点：它需要两次访问数据，尤其是第二次读取操作会导致大量的随机I/O操作。

优点：内存开销较小。

(2)、一次扫描算法(single pass)

该算法一次性将所需的Columns全部取出，在内存中排序后直接将结果输出。-- 注：从 MySQL 4.1 版本开始使用该算法。

优点：它减少了I/O的次数，效率较高

缺点：内存开销也较大。如果我们将并不需要的Columns也取出来，就会极大地浪费排序过程所需要的内存。

总结：在 MySQL 4.1 之后的版本中，可以通过设置 max_length_for_sort_data 参数来控制 MySQL 选择第一种排序算法还是第二种。

当取出的所有大字段大小大于 max_length_for_sort_data 的设置时，MySQL 就会选择使用第一种排序算法，

反之，则会选择第二种。

为了尽可能地提高排序性能，我们自然更希望使用第二种排序算法，所以在 Query 中仅仅取出需要的 Columns 是非常有必要的。

当对连接操作进行排序时，如果ORDER BY仅仅引用第一个表的列，MySQL对该表进行filesort操作，然后进行连接处理，此时，EXPLAIN输出“Using filesort”；否则，MySQL必须将查询的结果集生成一个临时表，在连接完成之后进行filesort操作，此时，EXPLAIN输出“Using temporary;Using filesort”。