具有嵌套索引的 Mysql 性能

Question

我有一个带有嵌套索引(blog_id，已发布)的 mysql 表(文章)，并且性能不佳。我在我的慢速查询日志中看到了很多这样的东西:


- Query_time:23.184007 Lock_time:0.000063 Rows_sent:380 Rows_examined:6341
从文章中选择 id WHERE category_id = 11 AND blog_id IN (13,14,15,16,17,18,19,20,21,22,23,24,26,27,6330,6331,8269,12218,18889)按公布的 DESC LIMIT 380 排序；


我很难理解为什么 mysql 会遍历具有这些 blog_id 的 所有 行来找出我的前 380 行。我希望嵌套索引的全部目的是加快速度。至少，即使是一个天真的实现，也应该通过 blog_id 查找并获得它的前 380 行按发布排序。这应该很快，因为由于嵌套索引，我们可以计算出确切的 200 行。然后对得到的 19*200=3800 行进行排序。

如果要以最佳方式实现它，您可以从所有基于博客 ID 的流集中放置一个堆，然后选择具有 max(published) 的流并重复 200 次。每个操作都应该很快。

我肯定遗漏了一些东西，因为谷歌、Facebook、推特、微软和所有大公司都在使用 mysql 进行生产。有经验的吗？

编辑:根据 thieger 的回答进行更新。我尝试了索引提示，但它似乎没有帮助。结果附在下面，最后。 Mysql order by optimisation解决 theiger 提出的问题的声明:

I agree that MySQL might possibly use the composite blog_id-published-index, but only for the blog_id part of the query.

SELECT * FROM t1 WHERE key_part1=constant ORDER BY key_part2;

至少 mysql 似乎声称它可以在 WHERE 子句(查询的 blog_id 部分)之外使用。有什么帮助吗？

谢谢，-Prasanna[gmail.com 上的 myprasanna]

CREATE TABLE IF NOT EXISTS `articles` (  `id` int(11) NOT NULL AUTO_INCREMENT,  `category_id` int(11) DEFAULT NULL,  `blog_id` int(11) DEFAULT NULL,  `cluster_id` int(11) DEFAULT NULL,  `title` varchar(255) COLLATE utf8_unicode_ci DEFAULT NULL,  `description` text COLLATE utf8_unicode_ci,  `keywords` text COLLATE utf8_unicode_ci,  `image_url` varchar(511) COLLATE utf8_unicode_ci DEFAULT NULL,  `url` varchar(511) COLLATE utf8_unicode_ci DEFAULT NULL,  `url_hash` varchar(50) COLLATE utf8_unicode_ci DEFAULT NULL,  `author` varchar(255) COLLATE utf8_unicode_ci DEFAULT NULL,  `categories` varchar(255) COLLATE utf8_unicode_ci DEFAULT NULL,  `published` int(11) DEFAULT NULL,  `created_at` datetime DEFAULT NULL,  `updated_at` datetime DEFAULT NULL,  `is_image_crawled` tinyint(1) DEFAULT NULL,  `image_candidates` text COLLATE utf8_unicode_ci,  `title_hash` varchar(50) COLLATE utf8_unicode_ci DEFAULT NULL,  `article_readability_crawled` tinyint(1) DEFAULT NULL,  PRIMARY KEY (`id`),  KEY `index_articles_on_url_hash` (`url_hash`),  KEY `index_articles_on_cluster_id` (`cluster_id`),  KEY `index_articles_on_published` (`published`),  KEY `index_articles_on_is_image_crawled` (`is_image_crawled`),  KEY `index_articles_on_category_id` (`category_id`),  KEY `index_articles_on_title_hash` (`title_hash`),  KEY `index_articles_on_article_readability_crawled` (`article_readability_crawled`),  KEY `index_articles_on_blog_id` (`blog_id`,`published`)) ENGINE=InnoDB  DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci AUTO_INCREMENT=562907 ;

SELECT id from articles USE INDEX(index_articles_on_blog_id) WHERE category_id = 11 AND blog_id IN (13,14,15,16,17,18,19,20,21,22,23,24,26,27,6330,6331,8269,12218,18889) order by published DESC LIMIT 380;....380 rows in set (11.27 sec)explain SELECT id from articles USE INDEX(index_articles_on_blog_id) WHERE category_id = 11 AND blog_id IN (13,14,15,16,17,18,19,20,21,22,23,24,26,27,6330,6331,8269,12218,18889) order by published DESC LIMIT 380\G;*************************** 1. row ***************************           id: 1  select_type: SIMPLE        table: articles         type: rangepossible_keys: index_articles_on_blog_id          key: index_articles_on_blog_id      key_len: 5          ref: NULL         rows: 8640        Extra: Using where; Using filesort1 row in set (0.00 sec)

Answer 1

您是否尝试过 EXPLAIN 来查看您的索引是否被使用？您是否通过分析来更新索引统计信息？

我同意 MySQL 可能会使用复合 blog_id-published-index，但仅限于查询的 blog_id 部分。如果在 ANALYZE 之后没有使用索引，您可以尝试使用 USE INDEX 甚至 FORCE INDEX 给 MySQL 一个提示，但是 MySQL 优化器也可能正确地假设顺序扫描比使用索引更快。对于您的查询类型，我还建议在 category_id 和 blog_id 上添加索引并尝试使用它。