避免使用INNER JOIN + ORDER BY进行文件排序 [英] Avoid filesort with INNER JOIN + ORDER BY
问题描述
我一直在阅读其他帖子，但没有设法解决我的查询.
I've been reading other posts but I didn't managed to fix my query.
使用DESC命令使查询速度慢20倍，我必须对此进行改进.
这是查询:
Using DESC order the query is x20 times slower, I must improve that.
This is the query:
SELECT posts.post_id, posts.post_b_id, posts.post_title, posts.post_cont, posts.thumb, posts.post_user, boards.board_title_l, boards.board_title
FROM posts
INNER JOIN follow ON posts.post_b_id = follow.board_id
INNER JOIN boards ON posts.post_b_id = boards.board_id
WHERE follow.user_id =1
ORDER BY posts.post_id DESC
LIMIT 10
这些是表(已更新):
CREATE TABLE IF NOT EXISTS `posts` (
`post_id` int(11) NOT NULL AUTO_INCREMENT,
`post_b_id` int(11) unsigned NOT NULL,
`post_title` varchar(50) COLLATE utf8_bin NOT NULL,
`post_cont` text COLLATE utf8_bin NOT NULL,
`post_mintxt` varchar(255) COLLATE utf8_bin NOT NULL,
`post_type` char(3) COLLATE utf8_bin NOT NULL,
`thumb` varchar(200) COLLATE utf8_bin NOT NULL,
`post_user` varchar(16) COLLATE utf8_bin NOT NULL,
`published` enum('0','1') COLLATE utf8_bin NOT NULL,
`post_ip` varchar(94) COLLATE utf8_bin NOT NULL,
`post_ip_dat` int(11) unsigned NOT NULL,
`post_up` int(10) unsigned NOT NULL DEFAULT '0',
`post_down` int(10) unsigned NOT NULL DEFAULT '0',
PRIMARY KEY (`post_id`),
KEY `post_b_id` (`post_b_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_bin AUTO_INCREMENT=405 ;
CREATE TABLE IF NOT EXISTS `boards` (
`board_id` int(10) unsigned NOT NULL AUTO_INCREMENT,
`board_title_l` varchar(19) COLLATE utf8_bin NOT NULL,
`board_user_id` int(10) unsigned NOT NULL,
`board_title` varchar(19) COLLATE utf8_bin NOT NULL,
`board_user` varchar(16) COLLATE utf8_bin NOT NULL,
`board_txt` tinyint(1) unsigned NOT NULL,
`board_img` tinyint(1) unsigned NOT NULL,
`board_vid` tinyint(1) unsigned NOT NULL,
`board_desc` varchar(100) COLLATE utf8_bin NOT NULL,
`board_mod_p` tinyint(3) unsigned NOT NULL DEFAULT '0',
`board_ip` varchar(94) COLLATE utf8_bin NOT NULL,
`board_dat_ip` int(11) unsigned NOT NULL,
PRIMARY KEY (`board_id`),
UNIQUE KEY `board_title_l` (`board_title_l`),
KEY `board_user_id` (`board_user_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_bin AUTO_INCREMENT=89 ;
CREATE TABLE IF NOT EXISTS `follow` (
`user_id` int(10) unsigned NOT NULL,
`board_id` int(10) unsigned NOT NULL,
PRIMARY KEY (`user_id`,`board_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_bin;
使用默认的ASC顺序，它仅使用索引和位置，而DESC使用索引，位置，临时和文件排序.
Using default ASC order it only uses index and where, with DESC uses index, where, temporary and filesort.
id select_type table type possible_keys key key_len ref rows filtered Extra
1 SIMPLE follow ref user_id user_id 4 const 2 100.00 Using index; Using temporary; Using filesort
1 SIMPLE boards eq_ref PRIMARY PRIMARY 4 xxxx.follow.board_id 1 100.00
1 SIMPLE posts ref post_b_id post_b_id 4 xxxx.boards.board_id 3 100.00 Using where
如何使查询按DESC顺序接收结果，而无需进行文件排序和临时操作.
How I can make the query receiving the results in DESC order without filesort and temporary.
更新:我进行了新查询，没有临时或文件排序，但类型:索引，已过滤:7340.00.如果帖子位于表的末尾，则几乎与ASC顺序一样快，但是如果正在搜索的帖子位于开头，则速度慢于ASC顺序.所以看起来更好，但这还不够.
UPDATE: I made a new query, no temporary or filesort, but type: index, filtered: 7340.00. Almost as fast as ASC order if the posts are at the end of the table, but slow if the posts that is searching are at the beginning. So seems better but it's not enough.
SELECT posts.post_id, posts.post_b_id, posts.post_title, posts.post_cont, posts.thumb, posts.post_user, boards.board_title_l, boards.board_title
FROM posts INNER JOIN boards ON posts.post_b_id = boards.board_id
WHERE posts.post_b_id
IN (
SELECT follow.board_id
FROM follow
WHERE follow.user_id = 1
)
ORDER BY posts.post_id DESC
LIMIT 10
说明:
id select_type table type possible_keys key key_len ref rows filtered Extra
1 PRIMARY posts index post_b_id PRIMARY 8 NULL 10 7340.00 Using where
1 PRIMARY boards eq_ref PRIMARY PRIMARY 4 xxxx.posts.post_b_id 1 100.00
2 DEPENDENT SUBQUERY follow eq_ref user_id user_id 8 const,func 1 100.00 Using index
更新:解释来自拒绝的答案的查询:
id select_type table type possible_keys key key_len ref rows filtered Extra
1 PRIMARY <derived2>ALL NULL NULL NULL NULL 10 100.00
1 PRIMARY posts eq_ref PRIMARY,post_b_id PRIMARY 4 sq.post_id 1 100.00
1 PRIMARY boards eq_ref PRIMARY PRIMARY 4 xxxx.posts.post_b_id 1 100.00
2 DERIVED follow ref PRIMARY PRIMARY 4 1 100.00 Using index; Using temporary; Using filesort
2 DERIVED posts ref post_b_id post_b_id 4 xxxx.follow.board_id 6 100.00 Using index
时间:
Original query no order (ASC): 0.187500 seconds
Original query DESC: 2.812500 seconds
Second query posts at the end (DESC): 0.218750 seconds
Second query posts at the beginning (DESC): 3.293750 seconds
dened's query DESC: 0.421875 seconds
dened's query no order (ASC): 0.323750 seconds
有趣的笔记，如果我添加ORDER BY ASC的速度与DESC一样慢.
Interesting note, if I add ORDER BY ASC is as slow as DESC.
改变表的顺序将是一种神明的方式，但是正如我在评论中说的那样，我无法做到这一点.
Alter the table order will be a god way, but as I said in the comments I wasn't able to do that.
推荐答案
您可以通过将所有过滤工作移到仅访问索引的子查询(处理索引通常比处理其他数据快得多)并获取数据来帮助MySQL优化器最外层查询中的其余数据:
You can help MySQL optimizer by moving all the filtering work to a subquery what accesses only indices (manipulating indices is usually much faster than manipulating other data), and fetching rest of the data in the outermost query:
SELECT posts.post_id,
posts.post_b_id,
posts.post_title,
posts.post_cont,
posts.thumb,
posts.post_user,
boards.board_title_l,
boards.board_title
FROM (SELECT post_id
FROM posts
JOIN follow
ON posts.post_b_id = follow.board_id
WHERE follow.user_id = 1
ORDER BY post_id DESC
LIMIT 10) sq
JOIN posts
ON posts.post_id = sq.post_id
JOIN boards
ON boards.board_id = posts.post_b_id
请注意，我从外部查询中省略了ORDER BY posts.post_id DESC，因为在代码中对最终结果进行排序通常比使用MySQL查询进行排序要快(MySQL通常为此使用 filesort )
Note that I omit ORDER BY posts.post_id DESC from the outer query, because it is usually faster to sort the final result in your code rather than sorting using a MySQL query (MySQL often uses filesort for that).
P.S.您可以用主键替换follow表中的唯一键.
P.S. You can replace the unique key in the follow table with a primary key.
这篇关于避免使用INNER JOIN + ORDER BY进行文件排序的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
