sql - MySQL:使用 IN 和 ORDER BY 时避免文件排序

Question

假设我有下表(我们称它为 my_table):

CREATE TABLE `my_table` (
  `table_id` int(10) unsigned NOT NULL auto_increment,
  `my_field` int(10) unsigned NOT NULL default '0'
   PRIMARY KEY  (`table_id`),
   KEY `my_field` (`my_field`,`table_id`)
 ) ENGINE=MyISAM

my_table 的主键是 table_id(auto_increment)，我还有一个键是 my_field 和 table_id.

如果我测试这个查询...

EXPLAIN SELECT * FROM my_table
WHERE my_field = 28
ORDER BY table_id DESC;

...我得到:

id  select_type  table     type  possible_keys  key       key_len  ref    rows  Extra--- -----------  --------  ----  -------------  --------  -------  -----  ----  -----1   SIMPLE       my_table  ref   my_field       my_field  8        const  36

You can see that it's using the correct key (my_field).

But if I try this...

EXPLAIN SELECT * FROM my_table
WHERE my_field IN (1, 28, 20)
ORDER BY table_id DESC;

...我得到:

id  select_type  table     type  possible_keys  key     key_len  ref     rows  Extra--- -----------  --------  ----  -------------  ------  -------  ------  ----  ---------------------------1   SIMPLE       my_table  ALL   my_field       (NULL)  (NULL)   (NULL)  406   Using where; Using filesort

你可以看到它根本没有使用任何键，更糟糕的是，使用了 filesort。

即使我执行“FORCE INDEX (my_field)”，它仍然会执行文件排序。

有什么办法可以避免文件排序吗？

Answer 1

据我了解，MySQL 不能使用索引对这个查询进行排序。

如果索引恰好与您的查询排序方式相同，则 MySQL 只能使用该索引。假设您对 (table_id,my_field) 的记录是

(1,1), (2,28), (3,14), (4,20)

(my_field,table_id) 上的索引将像这样存储

(1,1), (14,3), (20,4), (28,2)

当从您的 IN 示例执行查询时 (为简单起见，我们会说您的 ORDER BY 是 ASCending)，MySQL 会发现

(1,1), (20,4), (28,2)

...按此顺序。无论如何，它都必须将它们分类为 (1,1),(28,2),(20,4)。那就是文件排序。这就是为什么如果查询是 ORDER BY my_field 或 ORDER BY my_field, table_id MySQL 只能使用该索引，因为索引已经按此顺序排列。这也是为什么它不能[目前，某些 future 版本可能允许您以混合顺序对复合索引进行排序] 如果您混合使用 ASC 和 DESC 则使用该索引。该索引按 ASC,ASC 排序，无论您以何种方式阅读，它的顺序都不正确。

请注意，“文件排序”没有任何问题，它是正常执行查询的一部分。它实际上也不使用文件，应该非常快。

如果您必须对数千行进行排序，使用小型派生表可能会获得更好的结果，尤其是当每一行都非常大时(很多字段、BLOB 等...)

  SELECT t.*
    FROM (
          SELECT table_id FROM my_table WHERE my_field IN (1, 28, 20)
         ) tmp
    JOIN my_table t USING (table_id)
ORDER BY t.table_id DESC

您将用文件排序换取派生表。在某些情况下，它的性能可能更高，而在其他情况下，性能可能略低。 YMMV