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 语句要符合最佳左前缀原则。

---

我们再来看，当我们的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，但是它会使用更多的空间，因为它把每一行数据都保存在了内存中。