使用文件排序的 MYSQL 性能变慢
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/12148943/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
MYSQL performance slow using filesort
提问by user1052096
I have a simple mysql query, but when I have a lot of records (currently 103,0000), the performance is really slow and it says it is using filesort, im not sure if this is why it is slow. Has anyone any suggestions to speed it up? or stop it using filesort?
我有一个简单的 mysql 查询，但是当我有很多记录（当前为 103,0000）时，性能真的很慢，并且它说它正在使用文件排序，我不确定这是否是它慢的原因。有没有人有任何建议可以加快速度？或停止使用文件排序？
MYSQL query :
MYSQL 查询：
SELECT adverts .*
FROM adverts
WHERE (
price >='0'
)
AND (
adverts.status = 1
)
AND (
adverts.approved = 1
)
ORDER BY date_updated DESC
LIMIT 19990 , 10
The Explain results :
解释结果：
id select_type table type possible_keys key key_len ref rows Extra
1 SIMPLE adverts range price price 4 NULL 103854 Using where; Using filesort
Here is the adverts table and indexes:
这是广告表和索引：
CREATE TABLE `adverts` (
`advert_id` int(10) NOT NULL AUTO_INCREMENT,
`user_id` int(10) NOT NULL,
`type_id` tinyint(1) NOT NULL,
`breed_id` int(10) NOT NULL,
`advert_type` tinyint(1) NOT NULL,
`headline` varchar(50) NOT NULL,
`description` text NOT NULL,
`price` int(4) NOT NULL,
`postcode` varchar(7) NOT NULL,
`town` varchar(60) NOT NULL,
`county` varchar(60) NOT NULL,
`latitude` float NOT NULL,
`longitude` float NOT NULL,
`telephone1` varchar(15) NOT NULL,
`telephone2` varchar(15) NOT NULL,
`email` varchar(80) NOT NULL,
`status` tinyint(1) NOT NULL DEFAULT '0',
`approved` tinyint(1) NOT NULL DEFAULT '0',
`date_created` datetime NOT NULL,
`date_updated` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
`expiry_date` datetime NOT NULL,
PRIMARY KEY (`advert_id`),
KEY `price` (`price`),
KEY `user` (`user_id`),
KEY `type_breed` (`type_id`,`breed_id`),
KEY `headline_keywords` (`headline`),
KEY `date_updated` (`date_updated`),
KEY `type_status_approved` (`advert_type`,`status`,`approved`)
) ENGINE=MyISAM AUTO_INCREMENT=103878 DEFAULT CHARSET=utf8
回答by Jocelyn
The problem is that MySQL only uses one index when executing the query. If you add a new index that uses the 3 fields in your WHEREclause, it will find the rows faster.
问题是 MySQL 在执行查询时只使用一个索引。如果添加使用WHERE子句中的 3 个字段的新索引，它将更快地找到行。
ALTER TABLE `adverts` ADD INDEX price_status_approved(`price`, `status`, `approved`);
According to the MySQL documentation ORDER BY Optimization:
根据 MySQL 文档ORDER BY Optimization：
In some cases, MySQL cannot use indexes to resolve the ORDER BY, although it still uses indexes to find the rows that match the WHERE clause. These cases include the following:
The key used to fetch the rows is not the same as the one used in the ORDER BY.
在某些情况下，MySQL 无法使用索引来解析 ORDER BY，尽管它仍然使用索引来查找与 WHERE 子句匹配的行。这些情况包括：
用于获取行的键与 ORDER BY 中使用的键不同。
This is what happens in your case.
As the output of EXPLAINtells us, the optimizer uses the key priceto find the rows. However, the ORDER BYis on the field date_updatedwhich does not belong to the key price.
这就是你的情况。正如 的输出EXPLAIN告诉我们的那样，优化器使用键price来查找行。但是，ORDER BY是在date_updated不属于 key的字段上price。
To find the rows faster AND sort the rows faster, you need to add an index that contains all the fields used in the WHEREand in the ORDER BYclauses:
要更快地查找行并更快地对行进行排序，您需要添加一个索引，其中包含在WHERE和ORDER BY子句中使用的所有字段：
ALTER TABLE `adverts` ADD INDEX status_approved_date_updated(`status`, `approved`, `date_updated`);
The field used for sorting must be in the last position in the index. It is useless to include pricein the index, because the condition used in the query will return a range of values.
用于排序的字段必须位于索引的最后位置。包含price在索引中是没有用的，因为查询中使用的条件将返回一个范围的值。
If EXPLAINstill shows that it is using filesort, you may try forcing MySQL to use an index you choose:
如果EXPLAIN仍然显示它正在使用文件排序，您可以尝试强制 MySQL 使用您选择的索引：
SELECT adverts.*
FROM adverts
FORCE INDEX(status_approved_date_updated)
WHERE price >= 0
AND adverts.status = 1
AND adverts.approved = 1
ORDER BY date_updated DESC
LIMIT 19990, 10
It is usually not necessary to force an index, because the MySQL optimizer most often does the correct choice. But sometimes it makes a bad choice, or not the best choice. You will need to run some tests to see if it improves performance or not.
通常不需要强制索引，因为 MySQL 优化器通常会做出正确的选择。但有时它做出了一个糟糕的选择，或者不是最好的选择。您将需要运行一些测试来查看它是否提高了性能。
回答by Fabian Barney
Remove the ticks around the '0'- it currently may prevent using the index but I am not sure.
Nevertheless it is better style since price is inttype and not a character column.
删除周围的刻度'0'- 它目前可能会阻止使用索引，但我不确定。不过它是更好的样式，因为价格是int类型而不是字符列。
SELECT adverts .*
FROM adverts
WHERE (
price >= 0
)
AND (
adverts.status = 1
)
AND (
adverts.approved = 1
)
ORDER BY date_updated DESC
LIMIT 19990 , 10
回答by bobwienholt
I have two suggestions. First, remove the quotes around the zero in your where clause. That line should be:
我有两个建议。首先，删除 where 子句中零周围的引号。该行应该是：
price >= 0
Second, create this index:
其次，创建这个索引：
CREATE INDEX `helper` ON `adverts`(`status`,`approved`,`price`,`date_created`);
This should allow MySQL to find the 10 rows specified by your LIMIT clause by using only the index. Filesort itself is not a bad thing... the number of rows that need to be processed is.
这应该允许 MySQL 仅使用索引来查找 LIMIT 子句指定的 10 行。Filesort 本身并不是一件坏事......需要处理的行数是。
回答by LSerni
Your WHEREcondition uses price, status, approvedto select, and then date_updatedis used to sort.
您的WHERE条件使用price, status,approved进行选择，然后date_updated用于排序。
So you need a singleindex with those fields; I'd suggest indexing on approved, status, priceand date_updated, in this order.
所以你需要一个单一的与这些领域的指标; 我建议索引上approved，status，price和date_updated，在这种秩序。
The general rule is placing WHERE equalities first, then ranges (more than, less or equal, between, etc), and sorting fields last. (Note that leaving one field out might make the index less usable, or even unusable, for this purpose).
一般规则是首先放置 WHERE 等式，然后是范围（大于、小于或等于、介于等），最后对字段进行排序。（请注意，为此目的，省略一个字段可能会使索引不太可用，甚至无法使用）。
CREATE INDEX advert_ndx ON adverts (approved, status, price, date_updated);
This way, access to the table data is only needed after LIMIThas worked its magic, and you will slow-retrieve only a small number of records.
这样，只有在发挥了LIMIT它的魔力后才需要访问表数据，并且您只会缓慢检索少量记录。
I'd also remove any unneeded indexes, which would speed up INSERTs and UPDATEs.
我还会删除任何不需要的索引，这会加速INSERTs 和UPDATEs。
回答by hol
MySQL does not make use of the key date_updatedfor the sorting but just uses the pricekey as it is used in the WHEREclause. You could try to to use index hints:
MySQL 不使用键date_updated进行排序，而只是使用price在WHERE子句中使用的键。您可以尝试使用索引提示：
http://dev.mysql.com/doc/refman/5.1/en/index-hints.html
http://dev.mysql.com/doc/refman/5.1/en/index-hints.html
Add something like
添加类似的东西
USE KEY FOR ORDER BY (date_updated)
