mysql - 避免使用 INNER JOIN + ORDER BY 进行文件排序

Question

我一直在阅读其他帖子，但未能解决我的问题。

使用 DESC 命令查询速度慢了 x20 倍，我必须改进它。这是查询:

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 顺序，它只使用 index 和 where，DESC 使用 index、where、temporary 和 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 顺序接收结果。

更新:我做了一个新查询，没有临时或文件排序，但类型:索引，过滤:7340.00。如果帖子位于表的末尾，则几乎与 ASC 命令一样快，但如果正在搜索的帖子位于表的开头，则速度较慢。所以看起来更好，但这还不够。

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

更新:解释来自 dened's answer 的查询:

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 一样慢。

改变表格顺序将是一个很好的方式，但正如我在评论中所说，我无法做到这一点。

Answer 1

您可以通过将所有过滤工作转移到仅访问索引的子查询(操作索引通常比操作其他数据快得多)并在最外层查询中获取其余数据来帮助 MySQL 优化器:

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 通常使用 < em>文件排序)。

附言您可以用主键替换 follow 表中的唯一键。