MySQL:使用IN和ORDER BY时避免文件排序 [英] MySQL: Avoid filesort when using IN and ORDER BY
问题描述
假设我有下表(我们称它为my_table):
Let's suppose I have the following table (let's call it 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(自动增量)，我也有一个键，分别是my_field和table_id.
The primary key for my_table is table_id (auto_increment) and I also have a key with my_field and table_id.
如果我测试此查询...
If I test this query...
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).
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 .
You can see that it's not using any key at all, and worse, using filesort.
即使我执行"FORCE INDEX (my_field)"，它仍然会进行文件排序.
Even if I do "FORCE INDEX (my_field)", It still does the filesort.
有什么办法可以避免文件排序吗?
Is there any way to avoid the filesort?
推荐答案
据我了解，MySQL无法使用索引对该查询进行排序.
It is my understanding that MySQL cannot use the index to sort this query.
MySQL只能使用与查询相同的排序方式来使用索引.假设您的(table_id,my_field)记录是
MySQL can only use the index if it just happens to be sorted the same way as your query. Let's say that your records for (table_id,my_field) are
(1,1), (2,28), (3,14), (4,20)
(my_field,table_id)上的索引将像这样存储
An index on (my_field,table_id) would be stored like this
(1,1), (14,3), (20,4), (28,2)
在您的IN示例中执行查询时(为简单起见，我们将说您的ORDER BY是ASCending)，MySQL会找到
When executing the query from your IN example (for simplicity's sake, we'll say that your ORDER BY is ASCending), MySQL would find
(1,1), (20,4), (28,2)
...按此顺序.无论如何，都必须将它们分类为(1,1),(28,2),(20,4).那是文件排序.这就是为什么MySQL仅在查询为ORDER BY my_field或ORDER BY my_field, table_id时才使用该索引的原因，因为索引已经按照该顺序进行了.这也是为什么它不能 [当前，某些将来的版本可能允许您按混合顺序对复合索引进行排序] 如果将ASC和DESC混合使用的方法.索引以ASC，ASC排序，无论您以哪种方式阅读，索引的顺序都不正确.
...in this order. No matter what, it will have to sort those into (1,1),(28,2),(20,4). That's the filesort. That's why MySQL could only use that index if the query was ORDER BY my_field or ORDER BY my_field, table_id because the index is already in this order. That's also why it cannot [currently, some future version may allow you to sort composite indexes in mixed order] use the index if you mix ASC and DESC. The index is sorted in ASC,ASC and no matter which way you read it, it won't be in the right order.
请注意，文件排序"没有任何问题，这是正常执行查询的一部分.它实际上也不使用文件，应该很快.
Note that there's nothing wrong with "filesort", it's part of the normal execution of a query. It doesn't actually use files either and should be very fast.
如果必须对数千行进行排序，则使用较小的派生表可能会获得更好的结果，尤其是如果每一行都很大(很多字段，BLOB等)的情况下
If you have to sort thousands of rows, you may get better results by using a small derived table, especially if each row is really big (lots of fields, BLOBs, etc...)
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
You will trade the filesort for a derived table. In some cases, it can be much more performant and in others, slightly less. YMMV
这篇关于MySQL:使用IN和ORDER BY时避免文件排序的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
