mysql学习——mysql优化order by

在某些情况中，MySQL可以使用一个索引来满足ORDER BY子句，而不需要额外的排序。

即使ORDER BY不确切匹配索引，只要WHERE子句中的所有未使用的索引部分和所有额外的ORDER BY 列为常数，就可以使用索引。通过EXPLAIN SELECT ...ORDER BY，可以检查MySQL是否可以使用索引来解决查询。如果Extra列内有Using filesort，则索引不能解决。

下面的查询使用索引来解决ORDER BY部分：

SELECT * FROM t1 ORDER BY key_part1,key_part2,... ;

show create table a\G;;
*************************** 1. row ***************************
       Table: a
Create Table: CREATE TABLE `a` (
  `c1` int(11) NOT NULL AUTO_INCREMENT,
  `c2` char(2) DEFAULT NULL,
  `c3` char(4) DEFAULT NULL,
  PRIMARY KEY (`c1`),
  KEY `a_c2_c3_idx` (`c2`,`c3`)
) ENGINE=MyISAM AUTO_INCREMENT=7 DEFAULT CHARSET=utf8


explain select * from a force index(a_c2_c3_idx) order by c2,c3;
+----+-------------+-------+-------+---------------+-------------+---------+------+------+-------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+-------+-------+---------------+-------------+---------+------+------+-------+
| 1 | SIMPLE | a | index | NULL | a_c2_c3_idx | 20 | NULL | 6 | |
+----+-------------+-------+-------+---------------+-------------+---------+------+------+-------+

SELECT * FROM t1 WHERE key_part1=constant ORDER BY key_part2;

explain select * from a force index(a_c2_c3_idx) where c2=3 order by c2,c3;
+----+-------------+-------+-------+---------------+-------------+---------+------+------+-------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+-------+-------+---------------+-------------+---------+------+------+-------------+
| 1 | SIMPLE | a | index | a_c2_c3_idx | a_c2_c3_idx | 20 | NULL | 6 | Using where |
+----+-------------+-------+-------+---------------+-------------+---------+------+------+-------------+

SELECT * FROM t1 ORDER BY key_part1 DESC, key_part2 DESC;

explain select * from a force index(a_c2_c3_idx) order by c2 desc,c3 desc;
+----+-------------+-------+-------+---------------+-------------+---------+------+------+-------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+-------+-------+---------------+-------------+---------+------+------+-------+
| 1 | SIMPLE | a | index | NULL | a_c2_c3_idx | 20 | NULL | 6 | |
+----+-------------+-------+-------+---------------+-------------+---------+------+------+-------+

SELECT * FROM t1 WHERE key_part1=1 ORDER BY key_part1 DESC, key_part2 DESC;

explain select * from a force index(a_c2_c3_idx) where c2=3 order by c2 desc,c3 desc;
+----+-------------+-------+-------+---------------+-------------+---------+------+------+-------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+-------+-------+---------------+-------------+---------+------+------+-------------+
| 1 | SIMPLE | a | index | a_c2_c3_idx | a_c2_c3_idx | 20 | NULL | 6 | Using where |
+----+-------------+-------+-------+---------------+-------------+---------+------+------+-------------+

以上情况就是利用有序索引进行排序，实际上就是当我们 Query 的 ORDER BY 条件和 Query 的执行计划中所利用的 Index 的索引键（或前面几个索引键）完全一致，且索引访问方式为 rang、 ref 或者 index 的时候，MySQL 可以利用索引顺序而直接取得已经排好序的数据。这种方式的 ORDER BY 基本上可以说是最优的排序方式了，因为 MySQL 不需要进行实际的排序操作。

 

————————————————————分割线———————————————————————

 

不能使用索引解决的部分：

对不同的关键字使用ORDER BY：

SELECT * FROM t1 ORDER BY key1, key2；

explain select * from a order by c1,c3;
+----+-------------+-------+------+---------------+------+---------+------+------+----------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+-------+------+---------------+------+---------+------+------+----------------+
| 1 | SIMPLE | a | ALL | NULL | NULL | NULL | NULL | 6 | Using filesort |
+----+-------------+-------+------+---------------+------+---------+------+------+----------------+

关于 Using filesort:

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

数据在内存中进行排序,所使用的内存区域也就是我们通过 sort_buffer_size 系统变量所设置的排序区。这个排序区是每个 Thread 独享的，所以说可能在同一时刻在 MySQL 中可能存在多个 sort buffer 内存区域。

MySQL 中 filesort 的实现算法实际上是有两种的，一种是首先根据相应的条件取出相应的排序字段和可以直接定位行数据的行指针信息，然后在 sort buffer 中进行排序。另外一种是一次性取出满足条件行的所有字段，然后在 sort buffer 中进行排序。

在 MySQL4.1 版本之前只有第一种排序算法，第二种算法是从 MySQL4.1开始的改进算法，主要目的是为了减少第一次算法中需要两次访问表数据的 IO 操作，将两次变成了一次，但相应也会耗用更多的 sort buffer 空间。当然，MySQL4.1开始的以后所有版本同时也支持第一种算法，MySQL 主要通过比较我们所设定的系统参数 max_length_for_sort_data 的大小和 Query 语句所取出的字段类型大小总和来判定需要使用哪一种排序算法。如果 max_length_for_sort_data 更大，则使用第二种优化后的算法，反之使用第一种算法。所以如果希望 ORDER BY 操作的效率尽可能的高，一定要主义 max_length_for_sort_data 参数的设置。

对关键字的非连续元素使用ORDER BY：

SELECT * FROM t1 WHERE key2=constant ORDER BY key_part2；

混合ASC和DESC：

SELECT * FROM t1 ORDER BY key_part1 DESC, key_part2 ASC；

用于查询行的关键字与ORDER BY中所使用的不相同：

SELECT * FROM t1 WHERE key2=constant ORDER BY key1；

你正联接许多表，并且ORDER BY中的列并不是全部来自第1个用于搜索行的非常量表。(这是EXPLAIN输出中的没有const联接类型的第1个表）。

有不同的ORDER BY和GROUP BY表达式。

使用的表索引的类型不能按顺序保存行。例如，对于HEAP表的HASH索引情况即如此。

如果想要增加ORDER BY的速度，首先看是否可以让MySQL使用索引而不是额外的排序阶段。如果不能，可以尝试下面的策略：

增加sort_buffer_size变量的大小。

增加read_rnd_buffer_size变量的大小。

更改tmpdir指向具有大量空闲空间的专用文件系统。该选项接受几个使用round-robin(循环)模式的路径。在Unix中路径应用冒号(‘:’)区间开，在Windows、NetWare和OS/2中用分号(‘；’)。可以使用该特性将负载均分到几个目录中。注释：路径应为位于不同物理硬盘上的文件系统的目录，而不是同一硬盘的不同的分区。