MYSQL性能慢，使用了文件排序。

Question

MYSQL性能慢，使用了文件排序。

mysqlperformancedatabase-performancefilesort

18

我有一个简单的MySQL查询，但当有很多记录（目前有103,0000条）时，性能非常慢，并且它显示正在使用文件排序（filesort），我不确定这是否是导致它变慢的原因。有人有任何建议来加速它吗？或停止它使用文件排序吗？

MYSQL查询：

SELECT *    
FROM adverts
WHERE (price >= 0)
    AND (status = 1)
    AND (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

以下是广告表和索引：

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 DEFAULT CHARSET=utf8

- user1052096

1

如果省略 LIMIT 19990,10 子句，速度也很慢吗？如果将其替换为 LIMIT 10，则可能会得到更快的操作。在这种情况下，您可能需要弄清楚如何使用 SELECT 而不是 LIMIT 来检索这些行。 - O. Jones

1

我刚刚添加了索引 status_approved (status,approved,date_updated)。现在的解释显示它正在使用 where 而不是 filesort，查询时间只有0.04秒，而之前则需要2秒或更长时间，我认为这可能已经解决了问题。 - user1052096

4

一个不相关的想法……这些括号都不必要。 - Dan Grossman

5个回答

3

去掉'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

- Fabian Barney

1

MySQL不使用键date_updated进行排序，而只是使用price键，因为它在WHERE子句中被使用。您可以尝试使用索引提示：

http://dev.mysql.com/doc/refman/5.1/en/index-hints.html

添加类似的内容

USE KEY FOR ORDER BY  (date_updated)

- hol

1

我有两个建议。首先，在where子句中删除零周围的引号。那一行应该是：

price >= 0

其次，创建此索引：

CREATE INDEX `helper` ON `adverts`(`status`,`approved`,`price`,`date_created`);

这应该允许MySQL仅使用索引找到由LIMIT子句指定的10行。文件排序本身并不是坏事...需要处理的行数才是。

- bobwienholt

1

您的WHERE条件使用price，status，approved进行选择，然后使用date_updated进行排序。

因此，您需要一个带有这些字段的单个索引; 我建议按以下顺序编制索引：approved，status，price和date_updated。

通常规则是首先放置WHERE相等项，然后是范围（大于，小于或等于，之间等），最后是排序字段。（请注意，遗漏一个字段可能会使该目的的索引不易使用，甚至无法使用）。

CREATE INDEX advert_ndx ON adverts (approved, status, price, date_updated);

这样，只有在LIMIT发挥其作用后才需要访问表数据，您将仅检索少量记录。

我还会删除任何不必要的索引，这将加快INSERT和UPDATE的速度。

- LSerni

网页内容由stack overflow 提供, 点击上面的

可以查看英文原文，
原文链接

- Jocelyn · Accepted Answer

问题在于MySQL在执行查询时只使用一个索引。如果您添加一个新的索引，该索引使用WHERE子句中的3个字段，则可以更快地找到行。

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

根据MySQL文档 ORDER BY Optimization 的描述：

在某些情况下，MySQL无法使用索引来解析ORDER BY，尽管它仍然使用索引来查找与WHERE子句匹配的行。这些情况包括以下情况：
用于提取行的键与用于ORDER BY的键不同。

这就是您的情况。正如EXPLAIN输出所示，优化器使用键price来查找行。但是，ORDER BY是在字段date_updated上进行的，该字段不属于键price。

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

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

索引中用于排序的字段必须位于最后位置。将price包含在索引中是无用的，因为查询中使用的条件将返回一系列值。

如果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

通常情况下，不需要强制创建索引，因为MySQL优化器通常会做出正确的选择。但有时它会做出错误或不是最佳的选择。您需要运行一些测试来确定是否可以提高性能。