MySql ORDER BY 索引优化

上一篇：MySql 索引优化

MySql 支持两种方式的排序，FileSort 和 Index ,Index 效率高，它指的是 MySql 扫描索引本身完成排序。FileSort 会对数据使用一个外部的索引排序，而不是按照表内的索引顺序进行读取。因此效率很低。在我们使用 ORDER BY 子句进行排序查找时，要使用 Index 的方式排序，避免 FileSort 方式排序。

我在 t_emp 表上建立了一个复合索引，其中包含 age 字段与 sex 字段。

+-------+------------+----------------+--------------+-------------+-----------+-------------+-
| Table | Non_unique | Key_name       | Seq_in_index | Column_name | Collation | Cardinality |
+-------+------------+----------------+--------------+-------------+-----------+-------------+-
| t_emp |          0 | PRIMARY        |            1 | id          | A         |           3 |
| t_emp |          1 | idx_emp_ageSex |            1 | age         | A         |           3 |
| t_emp |          1 | idx_emp_ageSex |            2 | sex         | A         |           3 |
+-------+------------+----------------+--------------+-------------+-----------+-------------+-

接下来我们就使用 EXPLAIN 关键字查看 Extra 中的值，判断 SQL 语句需不需要优化 (为了排版的问题，这里省略了 EXPLAIN 中的一些属性，下同)。当我们使用 age 排序时，可以知道使用了我们建立的索引，并且 Extra 中的值为：Using index condition 。

mysql> EXPLAIN SELECT* FROM t_emp WHERE age>20 ORDER BY age;
+-------+----------------+----------------+---------+------+------+----------+-----------------------+
| type  | possible_keys  | key            | key_len | ref  | rows | filtered | Extra                 |
+-------+----------------+----------------+---------+------+------+----------+-----------------------+
| range | idx_emp_ageSex | idx_emp_ageSex | 5       | NULL |    2 |   100.00 | Using index condition |
+-------+----------------+----------------+---------+------+------+----------+-----------------------+

下面这段 SQL 我们使用 age 与 sex 排序，同样使用了索引，Extra 中的值为：Using index condition 。

mysql> EXPLAIN SELECT* FROM t_emp WHERE age>20 ORDER BY age,sex;
+-------+----------------+----------------+---------+------+------+----------+-----------------------+
| type  | possible_keys  | key            | key_len | ref  | rows | filtered | Extra                 |
+-------+----------------+----------------+---------+------+------+----------+-----------------------+
| range | idx_emp_ageSex | idx_emp_ageSex | 5       | NULL |    2 |   100.00 | Using index condition |
+-------+----------------+----------------+---------+------+------+----------+-----------------------+

当我们按照 sex age 的顺序进行排序时，虽然使用了索引，但是 Extra 中却出现了 Using Filesort。这样的排序查找方式是不好的。

mysql> EXPLAIN SELECT* FROM t_emp WHERE age>20 ORDER BY sex,age;
+---------+------+------+----------+---------------------------------------+
| key_len | ref  | rows | filtered | Extra                                 |
+---------+------+------+----------+---------------------------------------+
| 5       | NULL |    2 |   100.00 | Using index condition; Using filesort |
+---------+------+------+----------+---------------------------------------+

从上面三条 SQL 的执行结果可以看出要想不使用 Using Filesort 排序，ORDER BY 语句要符合最佳左前缀原则。

</font size = 3> 我们再来看，当我们的 ORDER BY 语句不符合最佳左前缀法则时查询的结果是什么样的？下面的演示分别对 age 做固定查找与做范围查找，我们可以看出当使用范围查找时使用了 Filesort ，尽管在做固定查找时 ORDER BY 没有符合最佳左前缀原则，但是也是只使用了 Using index。

mysql> EXPLAIN SELECT* FROM t_emp WHERE age = 20 ORDER BY sex;
+------+----------------+----------------+---------+-------+------+----------+-----------------------+
| type | possible_keys  | key            | key_len | ref   | rows | filtered | Extra                 |
+------+----------------+----------------+---------+-------+------+----------+-----------------------+
| ref  | idx_emp_ageSex | idx_emp_ageSex | 5       | const |    1 |   100.00 | Using index condition |
+------+----------------+----------------+---------+-------+------+----------+-----------------------+

mysql> EXPLAIN SELECT* FROM t_emp WHERE age>20 ORDER BY sex;
+----------------+---------+------+------+----------+---------------------------------------+
| key            | key_len | ref  | rows | filtered | Extra                                 |
+----------------+---------+------+------+----------+---------------------------------------+
| idx_emp_ageSex | 5       | NULL |    2 |   100.00 | Using index condition; Using filesort |
+----------------+---------+------+------+----------+---------------------------------------+

因此，使用 WHERE 子句与 ORDER BY 子句条件列组合满足索引最佳左前缀法则也是可以提高查询效率的。但是 WHERE 子句查找条件不能使范围筛选。

下面我们就来看一看 Filesort 的两种排序算法：
双路排序：在 MySql 4.1 之前是使用双路排序，双路排序是指两次扫描磁盘，最终得到数据。读取行指针和 orderby 列，对它们进行排序，然后扫描已经排好序的列表，按照列表中的值重新获取列表中读取对应的数据输出。可见双路排序当操作一批数据时，会对磁盘进行两次扫描，由于 I/O 资源是很消耗时间的，因此在 MySql4.1 之后，出现了一种改进的算法，就是单路排序。

单路排序：从此磁盘中查询所需要的列，按照 orderby 列在 buffer 中对它们进行排序，然后扫描排序后的列表进行输出，它的效率更快一些，避免了第二次读取数据。并把随机 I/O 变成了顺序 I/O，但是它会使用更多的空间，因为它把每一行数据都保存在了内存中。