7.2.9 MySQL 如何优化 ORDER BY 在一些情况下,MySQL可以直接使用索引来满足一个 ORDER BY 或 GROU
本文来源gaodai.ma#com搞#代!码网_
P BY 子句而无需做额外的排序。 尽管 ORDER BY 不是和索引的顺序准确匹配,索引还是可以被用到,只要不用的索引部分和所有的额外的 ORDER BY 字段在 WHERE 子
7.2.9 MySQL 如何优化 ORDER BY
在一些情况下,MySQL可以直接使用索引来满足一个 ORDER BY
或 GROUP BY
子句而无需做额外的排序。
尽管 ORDER BY
不是和索引的顺序准确匹配,索引还是可以被用到,只要不用的索引部分和所有的额外的 ORDER BY
字段在 WHERE
子句中都被包括了。下列的几个查询都会使用索引来解决 ORDER BY
或 GROUP BY
部分:
SELECT * FROM t1 ORDER BY <var>key_part1</var>,<var>key_part2</var>,... ;<br />SELECT * FROM t1 WHERE <var>key_part1</var>=<var>constant</var> ORDER BY <var>key_part2</var>;<br />SELECT * FROM t1 WHERE <var>key_part1</var>=<var>constant</var> GROUP BY <var>key_part2</var>;<br />SELECT * FROM t1 ORDER BY <var>key_part1</var> DESC, <var>key_part2</var> DESC;<br />SELECT * FROM t1<br /> WHERE <var>key_part1</var>=1 ORDER BY <var>key_part1</var> DESC, <var>key_part2</var> DESC;<br />
在另一些情况下,MySQL无法使用索引来满足 ORDER BY
,尽管它会使用索引来找到记录来匹配 WHERE
子句。这些情况如下:
- 对不同的索引键做
ORDER BY
:SELECT * FROM t1 ORDER BY <var>key1</var>, <var>key2</var>;
- 在非连续的索引键部分上做
ORDER BY
:SELECT * FROM t1 WHERE <var>key2</var>=<var>constant</var> ORDER BY <var>key_part2</var>;
- 同时使用了
ASC
和DESC
:SELECT * FROM t1 ORDER BY <var>key_part1</var> DESC, <var>key_part2</var> ASC;
- 用于搜索记录的索引键和做
ORDER BY
的不是同一个:SELECT * FROM t1 WHERE <var>key2</var>=<var>constant</var> ORDER BY <var>key1</var>;
- 有很多表一起做连接,而且读取的记录中在
ORDER BY
中的字段都不全是来自第一个非常数的表中(也就是说,在EXPLAIN
分析的结果中的第一个表的连接类型不是const
)。 - 使用了不同的
ORDER BY
和GROUP BY
表达式。 - 表索引中的记录不是按序存储。例如,
HASH
和HEAP
表就是这样。
通过执行 EXPLAIN SELECT ... ORDER BY
,就知道MySQL是否在查询中使用了索引。如果 Extra
字段的值是 Using filesort
,则说明MySQL无法使用索引。详情请看”7.2.1 EXPLAIN
Syntax (Get Information About a SELECT
)”。
当必须对结果进行排序时,MySQL 4.1 以前它使用了以下 filesort
算法:
- 根据索引键读取记录,或者扫描数据表。那些无法匹配
WHERE
分句的记录都会被略过。 - 在缓冲中每条记录都用一个‘对’存储了2个值(索引键及记录指针)。缓冲的大小依据系统变量
sort_buffer_size
的值而定。 - 当缓冲慢了时,就运行 qsort(快速排序)并将结果存储在临时文件中。将存储的块指针保存起来(如果所有的‘对’值都能保存在缓冲中,就无需创建临时文件了)。
- 执行上面的操作,直到所有的记录都读取出来了。
- 做一次多重合并,将多达
MERGEBUFF
(7)个区域的块保存在另一个临时文件中。重复这个操作,直到所有在第一个文件的块都放到第二个文件了。 - 重复以上操作,直到剩余的块数量小于
MERGEBUFF2
(15)。 - 在最后一次多重合并时,只有记录的指针(排序索引键的最后部分)写到结果文件中去。
- 通过读取结果文件中的记录指针来按序读取记录。想要优化这个操作,MySQL将记录指针读取放到一个大的块里,并且使用它来按序读取记录,将记录放到缓冲中。缓冲的大小由系统变量
read_rnd_buffer_size
的值而定。这个步骤的代码在源文件 `sql/records.cc' 中。