MySQL慢速查询:INNER JOIN + ORDER BY导致文件排序 [英] Mysql slow query: INNER JOIN + ORDER BY causes filesort
本文介绍了MySQL慢速查询:INNER JOIN + ORDER BY导致文件排序的处理方法，对大家解决问题具有一定的参考价值，需要的朋友们下面随着小编来一起学习吧！
问题描述
我正在尝试优化此查询:
I'm trying to optimize this query:
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使用"filesort"，因此它变得非常慢.我尝试使用不同的索引，没有运气.
The size of tables is 38k rows, and 31k and mysql uses "filesort" so it gets pretty slow. I tried to use different indexes, no luck.
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_ci
CREATE 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使用filesort?订单字段不在where子句中可以吗?
What kind of index I need to define to avoid mysql using filesort? Is it possible when order field is not in where clause?
更新: 分析结果:
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 |
+--------------------------------+----------+
update2:
真实查询(一些更多的布尔字段，更多无用的索引)
Real query (some more boolean fields, more useless indexes)
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.01s.
Without ORDER BY — 0.01s.
+----+-------------+------------+--------+-----------------------------------------+-----------------------+---------+---------------------+-------+--------------------------+
| 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 |
+----+-------------+------------+--------+-----------------------------------------+-----------------------+---------+---------------------+-------+--------------------------+
解决方案
- 查询已更新为"ORDER BY posts_tags.created_at DESC"(应用程序代码中有两个小的更改)
- 添加的索引:index_posts_tags_on_created_at.
仅此而已！
推荐答案
您需要进行非正规化处理，然后将posts.created_at字段复制到post_tags表中(我称其为post_created_at，您可以根据需要对其进行命名) :
You would need to denormalize a bit, and copy the posts.created_at field into the post_tags table (I called it post_created_at, you could name it how you want):
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;
，然后向
(tag_id, post_created_at)
这将允许查询以正确的顺序获取标签的所有帖子，而无需进行文件排序.
That will allow the query to get all the posts for a tag, in the correct order, without filesort.
这篇关于MySQL慢速查询:INNER JOIN + ORDER BY导致文件排序的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
查看全文
