MySQL倒序如何避免filesort_mysql – 使用INNER JOIN ORDER BY避免使用filesort

我一直在阅读其他帖子,但我没有设法修复我的查询.

使用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顺序,它只使用索引,其中DESC使用索引,其中,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

如何在没有filesort和临时的情况下使查询以DESC顺序接收结果.

更新：我做了一个新的查询,没有临时或文件输出,但输入：index,filtered：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

id select_type table type possible_keys key key_len ref rows filtered Extra

1 PRIMARY 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一样慢.

改变表顺序将是一种神的方式,但正如我在评论中所说,我无法做到这一点.