关于MySQL数据库索引和ORDER BY子句的使用问题简介

精选转载

元鼎时代03 2012-10-30 14:07:10

文章标签 文章分类 数据库

在某些情况下，MySQL数据库能够直接利用索引来中意一个ORDER BY或GROUP BY子句而无需做额外的排序。尽管ORDER BY不是和索引的次序准确相称，索引还是能够被用到，因为凡是无须的索引局部和所有的额外的ORDER BY字段在WHERE子句中都被包括了。本文我们主要介绍MySQL数据库中的索引和Order By子句的使用问题，接下来我们开始介绍。

利用索引的MySQL Order By

下列的几个查询都会利用索引来处理 ORDER BY 或 GROUP BY 局部：

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

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

5 SELECT * FROM t1 WHERE key_part1=constant GROUP BY key_part2;

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

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

不利用索引的MySQL Order By 　

在另一些情形下，MySQL无法利用索引来中意 ORDER BY，尽管它会利用索引来找到登记来相称 WHERE 子句。这些情形如下：

对不同的索引键做ORDER BY ： SELECT * FROM t1 ORDER BY key1, key2;

在非继续的索引键局部上做 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 中的字段都不全是来自第一个极其数的表中（也即便说，在 EXPLAIN 分析的收获中的第一个表的连接种类不是 const）。

利用了不同的ORDER BY和GROUP BY表白式。

表索引中的登记不是按序存储。例如，HASH 和 HEAP 表即便这么。穿越厉行 EXPLAIN SELECT ... ORDER BY，就懂得MySQL是否在查询中利用了索引。万一 Extra 字段的值是 Using filesort，则解释MySQL无法利用索引。

当定然对收获举行排序时，MySQL 4.1过去它利用了以下 filesort 算法：

1. 依据索引键读取登记，可能扫描数据表。那些无法相称 WHERE 分句的登记都会被略过。

2. 在缓冲中每条登记都用一个‘对’存储了2个值（索引键及登记指针）。缓冲的大小依据系统变量 sort_buffer_size 的值而定。

3. 当缓冲慢了时，就运行 qsort（迅速排序）并将收获存储在临时文件中。将存储的块指针保留起来（万一所有的‘对’值都能保留在缓冲中，就无需创立临时文件了）。

4. 厉行上面的垄断，直到所有的登记都读取出来了。

5. 做顺次多重并合，将多达 MERGEBUFF（7）个区域的块保留在另一个临时文件中。重复这个垄断，直到所有在第一个文件的块都放到第二个文件了。

6. 重复以上垄断，直到富余的块数量小于 MERGEBUFF2 (15)。

7. 在最后顺次多重并合时，只有登记的指针（排序索引键的最后局部）写到收获文件中去。

8. 穿越读取收获文件中的登记指针来按序读取登记。想要优化这个垄断，MySQL将登记指针读取放到一个大的块里，并且利用它来按序读取登记，将登记放到缓冲中。缓冲的大小由系统变量 read_rnd_buffer_size 的值而定。这个环节的代码在源文件 `sqlgexinghua.org/费雪records.cc' 中。这个接近算法的一个问题是，数据库读取了2次登记：顺次是估价 WHERE 分句时，第二次是排序时。尽管第顺次都获胜读取登记了（例如，做了顺次全表扫描），第二次是随机的读取（索引键曾经排好序了，然而登记并未曾）。

在MySQL 4.1 及更新版本中，filesort 优化算法用于登记中不但包括索引键值和登记的位置，还包括查询中要求的字段。这么做避免了必需2次读取登记。改进的 filesort 算法做法大约如下：

1. 跟随前一样，读取相称 WHERE 分句的登记。

2. 相对于每个登记，都登记了一个对应的；‘元组’消息消息，包括索引键值、登记位置、以及查询中所必需的所有字段。

3. 依据索引键对‘元组’消息举行排序。

4. 按序读取登记，不过是从曾经排序过的‘元组’列表中读取登记，而非从数据表中再读取顺次。利用改进后的 filesort 算法相比本来的，‘元组’比‘对’必需挪借更长的空间，它们很少刚好合乎放在排序缓冲中（缓冲的大小是由 sort_buffer_size 的值定夺的）。因而，这就可能必需有更多的I/O垄断，导致改进的算法更慢。为了避免使之变慢，这种优化措施只用于排序‘元组’中额外的字段的大小总和超过系统变量 max_length_for_sort_data 的情形（这个变量的值设置太高的一个假象即便高磁盘负载低CPU负载）。