Mysql慢查询: INNER JOIN + ORDER BY causes filesort

Question

我正在尝试优化此查询:

SELECT `posts`.* FROM `posts` INNER JOIN `posts_tags`      ON `posts`.id = `posts_tags`.post_id      WHERE  (((`posts_tags`.tag_id = 1)))      ORDER BY posts.created_at DESC;

表的大小是 38k 行，31k 和 mysql 使用“文件排序”，所以它变得相当慢。我尝试使用不同的索引，但没有成功。

CREATE TABLE `posts` (  `id` int(11) NOT NULL auto_increment,  `created_at` datetime default NULL,  PRIMARY KEY  (`id`),  KEY `index_posts_on_created_at` (`created_at`),  KEY `for_tags` (`trashed`,`published`,`clan_private`,`created_at`)) ENGINE=InnoDB AUTO_INCREMENT=44390 DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ciCREATE TABLE `posts_tags` (  `id` int(11) NOT NULL auto_increment,  `post_id` int(11) default NULL,  `tag_id` int(11) default NULL,  `created_at` datetime default NULL,  `updated_at` datetime default NULL,  PRIMARY KEY  (`id`),  KEY `index_posts_tags_on_post_id_and_tag_id` (`post_id`,`tag_id`)) ENGINE=InnoDB AUTO_INCREMENT=63175 DEFAULT CHARSET=utf8

+----+-------------+------------+--------+--------------------------+--------------------------+---------+---------------------+-------+-----------------------------------------------------------+| id | select_type | table      | type   | possible_keys            | key                      | key_len | ref                 | rows  | Extra                                                     |+----+-------------+------------+--------+--------------------------+--------------------------+---------+---------------------+-------+-----------------------------------------------------------+|  1 | SIMPLE      | posts_tags | index  | index_post_id_and_tag_id | index_post_id_and_tag_id | 10      | NULL                | 24159 | Using where; Using index; Using temporary; Using filesort | |  1 | SIMPLE      | posts      | eq_ref | PRIMARY                  | PRIMARY                  | 4       | .posts_tags.post_id |     1 |                                                           | +----+-------------+------------+--------+--------------------------+--------------------------+---------+---------------------+-------+-----------------------------------------------------------+2 rows in set (0.00 sec)

我需要定义什么样的索引来避免 mysql 使用文件排序？ order字段不在where子句中是否可以？

更新:分析结果:

mysql> show profile for query 1;+--------------------------------+----------+| Status                         | Duration |+--------------------------------+----------+| starting                       | 0.000027 | | checking query cache for query | 0.037953 | | Opening tables                 | 0.000028 | | System lock                    | 0.010382 | | Table lock                     | 0.023894 | | init                           | 0.000057 | | optimizing                     | 0.010030 | | statistics                     | 0.000026 | | preparing                      | 0.000018 | | Creating tmp table             | 0.128619 | | executing                      | 0.000008 | | Copying to tmp table           | 1.819463 | | Sorting result                 | 0.001092 | | Sending data                   | 0.004239 | | end                            | 0.000012 | | removing tmp table             | 0.000885 | | end                            | 0.000006 | | end                            | 0.000005 | | query end                      | 0.000006 | | storing result in query cache  | 0.000005 | | freeing items                  | 0.000021 | | closing tables                 | 0.000013 | | logging slow query             | 0.000004 | | cleaning up                    | 0.000006 | +--------------------------------+----------+

更新2:

真实查询(更多 bool 字段，更多无用索引)

SELECT `posts`.* FROM `posts` INNER JOIN `posts_tags`    ON `posts`.id = `posts_tags`.post_id    WHERE ((`posts_tags`.tag_id = 7971))        AND (((posts.trashed = 0)        AND (`posts`.`published` = 1        AND `posts`.`clan_private` = 0))        AND ((`posts_tags`.tag_id = 7971)))     ORDER BY created_at DESC LIMIT 0, 10; 

空集(1.25秒)

没有 ORDER BY — 0.01 秒。

+----+-------------+------------+--------+-----------------------------------------+-----------------------+---------+---------------------+-------+--------------------------+| id | select_type | table      | type   | possible_keys                           | key                   | key_len | ref                 | rows  | Extra                    |+----+-------------+------------+--------+-----------------------------------------+-----------------------+---------+---------------------+-------+--------------------------+|  1 | SIMPLE      | posts_tags | index  | index_posts_tags_on_post_id_and_tag_id  | index_posts_tags_...  | 10      | NULL                | 23988 | Using where; Using index | |  1 | SIMPLE      | posts      | eq_ref | PRIMARY,index_posts_on_trashed_and_crea | PRIMARY               | 4       | .posts_tags.post_id |     1 | Using where              | +----+-------------+------------+--------+-----------------------------------------+-----------------------+---------+---------------------+-------+--------------------------+

解决方案

1. 查询已更新为“ORDER BY posts_tags.created_at DESC”(应用代码中有两处小变化)
2. 已添加索引:index_posts_tags_on_created_at。

仅此而已!

Answer 1

您需要稍微反规范化，并将 posts.created_at 字段复制到 post_tags 表中(我将其称为 post_created_at，您可以按照自己的意愿命名):

CREATE TABLE `posts_tags` (
  `id` int(11) NOT NULL auto_increment,
  `post_id` int(11) default NULL,
  `tag_id` int(11) default NULL,
  `post_created_at` datetime default NULL,
  `created_at` datetime default NULL,
  `updated_at` datetime default NULL,
  PRIMARY KEY  (`id`),
  KEY `index_posts_tags_on_post_id_and_tag_id` (`post_id`,`tag_id`)
) ENGINE=InnoDB;

然后将索引添加到 posts_tags 上

(tag_id, post_created_at)

这将允许查询以正确的顺序获取某个标签的所有帖子，而无需进行文件排序。