使用Filesort的MYSQL性能变慢 [英] MYSQL performance slow using filesort

问题描述

我有一个简单的mysql查询，但是当我有很多记录(当前为103,0000)时，性能确实很慢，并且它说它正在使用filesort，我不确定这是否是为什么它很慢.有没有人建议加快速度?还是使用filesort停止它?

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查询:

SELECT adverts .*    
FROM adverts
WHERE (
price >='0'
)
AND (
adverts.status = 1
)
AND (
adverts.approved = 1
)
ORDER BY date_updated DESC 
LIMIT 19990 , 10

说明结果:

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

这是adverts表和索引:

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

推荐答案

问题是MySQL在执行查询时仅使用一个索引.如果在WHERE子句中添加使用3个字段的新索引，它将更快地找到行.

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 WHERE clause, it will find the rows faster.

ALTER TABLE `adverts` ADD INDEX price_status_approved(`price`, `status`, `approved`);

根据MySQL文档按优化排序:

在某些情况下，MySQL仍然无法使用索引来解析ORDER BY，尽管它仍然使用索引来查找与WHERE子句匹配的行.这些情况包括:
用于获取行的密钥与在ORDER BY中使用的密钥不同.

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.

这就是您的情况. 正如EXPLAIN的输出告诉我们的那样，优化器使用键price查找行.但是，ORDER BY位于字段date_updated上，该字段不属于键price.

This is what happens in your case. As the output of EXPLAIN tells us, the optimizer uses the key price to find the rows. However, the ORDER BY is on the field date_updated which does not belong to the key price.

要更快地查找行并更快地对行进行排序，您需要添加一个索引，其中包含在WHERE和ORDER BY子句中使用的所有字段:

To find the rows faster AND sort the rows faster, you need to add an index that contains all the fields used in the WHERE and in the ORDER BY clauses:

ALTER TABLE `adverts` ADD INDEX status_approved_date_updated(`status`, `approved`, `date_updated`);

用于排序的字段必须位于索引的最后位置.在索引中包含price是没有用的，因为查询中使用的条件将返回值的范围.

The field used for sorting must be in the last position in the index. It is useless to include price in the index, because the condition used in the query will return a range of values.

如果EXPLAIN仍然显示它正在使用文件排序，则可以尝试强制MySQL使用您选择的索引:

If EXPLAIN still shows that it is using filesort, you may try forcing MySQL to use an index you choose:

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

通常不需要强制索引，因为MySQL优化器通常会做出正确的选择.但是有时它是一个错误的选择，或者不是最佳选择.您将需要运行一些测试，以查看它是否可以提高性能.

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.

这篇关于使用Filesort的MYSQL性能变慢的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！