mysql - 简单的 SELECT mysql 查询非常慢(使用相交)

Question

过去在生产服务器上运行良好的查询开始变得非常慢(在几个小时内)。

就是这样:

SELECT * FROM news_articles WHERE published = '1' AND news_category_id = '4' ORDER BY date_edited DESC LIMIT 1;

这最多需要 20-30 秒来执行(该表有大约 200.000 行)

这是EXPLAIN的输出:

+----+-------------+---------------+-------------+----------------------------+----------------------------+---------+------+------+--------------------------------------------------------------------------+
| id | select_type | table         | type        | possible_keys              | key                        | key_len | ref  | rows | Extra                                                                    |
+----+-------------+---------------+-------------+----------------------------+----------------------------+---------+------+------+--------------------------------------------------------------------------+
|  1 | SIMPLE      | news_articles | index_merge | news_category_id,published | news_category_id,published | 5,5     | NULL | 8409 | Using intersect(news_category_id,published); Using where; Using filesort |
+----+-------------+---------------+-------------+----------------------------+----------------------------+---------+------+------+--------------------------------------------------------------------------+

尝试使用它，我发现提示一个特定的索引 (date_edited) 可以让它更快:

SELECT * FROM news_articles USE INDEX (date_edited) WHERE published = '1' AND news_category_id = '4' ORDER BY date_edited DESC LIMIT 1;

这个需要毫秒来执行。

EXPLAIN 这个的输出是:

+----+-------------+---------------+-------+---------------+-------------+---------+------+------+-------------+
| id | select_type | table         | type  | possible_keys | key         | key_len | ref  | rows | Extra       |
+----+-------------+---------------+-------+---------------+-------------+---------+------+------+-------------+
|  1 | SIMPLE      | news_articles | index | NULL          | date_edited | 8       | NULL |    1 | Using where |
+----+-------------+---------------+-------+---------------+-------------+---------+------+------+-------------+

news_category_id、published 和 date_edited 列均已编入索引。

存储引擎是InnoDB。

这是表结构:

CREATE TABLE `news_articles` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `title` text NOT NULL,
  `subtitle` text NOT NULL,
  `summary` text NOT NULL,
  `keywords` varchar(500) DEFAULT NULL,
  `body` mediumtext NOT NULL,
  `source` varchar(255) DEFAULT NULL,
  `source_visible` int(11) DEFAULT NULL,
  `author_information` enum('none','name','signature') NOT NULL     DEFAULT 'name',
  `date_added` datetime NOT NULL,
  `date_edited` datetime NOT NULL,
  `views` int(11) DEFAULT '0',
  `news_category_id` int(11) DEFAULT NULL,
  `user_id` int(11) DEFAULT NULL,
  `c_forwarded` int(11) DEFAULT '0',
  `published` int(11) DEFAULT '0',
  `deleted` int(11) DEFAULT '0',
  `permalink` varchar(255) DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `user_id` (`user_id`),
  KEY `news_category_id` (`news_category_id`),
  KEY `published` (`published`),
  KEY `deleted` (`deleted`),
  KEY `date_edited` (`date_edited`),
  CONSTRAINT `news_articles_ibfk_3` FOREIGN KEY (`news_category_id`) REFERENCES `news_categories` (`id`) ON DELETE SET NULL ON UPDATE CASCADE,
  CONSTRAINT `news_articles_ibfk_4` FOREIGN KEY (`user_id`) REFERENCES `users` (`id`) ON DELETE SET NULL ON UPDATE CASCADE
) ENGINE=InnoDB AUTO_INCREMENT=192588 DEFAULT CHARSET=utf8

我可能会更改我的 Web 应用程序所做的所有查询以提示使用该索引。但这是一项相当大的工作。

有没有什么方法可以调整 MySQL，使第一个查询更有效率，而无需实际重写所有查询？

Answer 1

只是一些提示..

1 - 在我看来，已发布的字段和 news_category_id 是 INTEGER。如果是这样，请从您的查询中删除单引号。它可以在性能方面产生巨大的差异；

2 - 另外，我想说你的 published 字段没有很多不同的值(它可能是 1 - 是和 0 - 否，或类似的东西)。如果我是对的，这根本不是一个适合索引的领域。在这种情况下，解析器仍然必须遍历所有记录才能找到它要查找的内容；在这种情况下，将 news_category_id 移动到 WHERE 子句中的第一个字段。

3 - “不要忘记最左边的索引”。此确认对您的 SELECT、JOINS、WHERE、ORDER BY 有效。即使列在表格中的位置也很重要，请将索引列放在顶部。只要您知道如何使用索引，索引就是您的 friend 。

希望它能以某种方式帮助你..

SELECT * FROM news_articles WHERE published = '1' AND news_category_id = '4' ORDER BY date_edited DESC LIMIT 1;