首页 文章

MySQL：使用IN和ORDER BY时避免使用filesort

提问于
浏览 次
1

让's suppose I have the following table (let' s称之为 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

您可以看到它正在使用正确的密钥（ my_field ） .

但如果我试试这个......

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) ”，它仍然执行filesort .

有没有办法避免filesort？

sql mysql performance filesort

4 回答

  • 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示例执行查询时（为简单起见's sake, we' ll说你的ORDER BY是ASCending），MySQL会找到

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

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

    注意 there's nothing wrong with "filesort" ，它's part of the normal execution of a query. It doesn'实际上也使用文件，应该非常快 .

    如果你必须排序成千上万的行，你可以通过使用一个小的派生表来获得更好的结果，特别是如果每一行真的很大（很多字段，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

    您将为派生表交换文件排序 . 在某些情况下，它可以更高性能，而在其他情况下，稍微更少 . 因人而异

    回复于
  • 1

    您的查询有一个错误的“=”符号 . 像这样删除它：

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

    首先，我认为你的密钥在你的SQL中是倒退的 . 你不希望主键是 table_id 吗？

    这可能是你想要的：

    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`),
   INDEX `my_index` (`my_field`)
 ) ENGINE=MyISAM
    回复于
  • 12

    它不会使用键 my_field ，因为它是基于 table_id 和 my_field 的键 . 如果您仅基于 my_field 执行查询，则必须在 my_field 上独占索引 .

    在多个列上使用单个键并不意味着如果您按任何列搜索，则使用该键 . 仅当查询中的所有搜索列也在密钥中时，才使用特定密钥 .

    回复于

相关问题