MySQL Using filesort 疑问?

Question

表及相关数据

create table book(
    bid int auto_increment primary key,
    `name` varchar(20) not null,
    authorid int not null,
    publicid int not null,
    typeid int not null 
);
insert into book values(1,'tjava',1,1,2);
insert into book values(2,'tc',2,1,2);
insert into book values(3,'wx',3,2,1);
insert into book values(4,'math',4,2,3);

需求

查询 authorid =1 且 typeid为2或3的 bid

优化方案

方法1:

索引的顺序是 bid,typeid,authorid

alter table book add index idx_bta (bid,typeid,authorid);

分析结果

explain select bid from book where typeid in(2,3) and authorid=1 order by typeid desc;
+----+-------------+-------+------------+-------+---------------+---------+---------+------+------+----------+------------------------------------------+
| id | select_type | table | partitions | type  | possible_keys | key     | key_len | ref  | rows | filtered | Extra                                    |
+----+-------------+-------+------------+-------+---------------+---------+---------+------+------+----------+------------------------------------------+
|  1 | SIMPLE      | book  | NULL       | index | NULL          | idx_bta | 12      | NULL |    4 |    25.00 | Using where; Using index; Using filesort |
+----+-------------+-------+------------+-------+---------------+---------+---------+------+------+----------+------------------------------------------+

方法2:

索引的顺序是 typeid,authorid,bid

alter table book add index idx_tab (typeid,authorid,bid);

分析结果

explain select bid from book where typeid in(2,3) and authorid=1 order by typeid desc;
+----+-------------+-------+------------+-------+---------------+---------+---------+------+------+----------+--------------------------+
| id | select_type | table | partitions | type  | possible_keys | key     | key_len | ref  | rows | filtered | Extra                    |
+----+-------------+-------+------------+-------+---------------+---------+---------+------+------+----------+--------------------------+
|  1 | SIMPLE      | book  | NULL       | range | idx_tab       | idx_tab | 8       | NULL |    2 |   100.00 | Using where; Using index |
+----+-------------+-------+------------+-------+---------------+---------+---------+------+------+----------+--------------------------+

疑问

为啥索引的顺序是 bid,typeid,authorid 的出现 Using filesort, 而 typeid,authorid,bid却没有出现 Using filesort?

Answer 1

方法1，查询方式并不符合左查询规则，因此explain的possible_keys值是NULL，但查询字段bid在索引idx_bta中，在索引内部即可完成查询，即Using index。type是index，表示full index scan，扫描全索引，rows是4

方法2，查询符合左查询规则，正确使用示范。type是range，区间查询

建议

1. 在InnoDB中，主键被二级索引包含，方法2中索引中不需要有bid
2. Order By结果中建议加入主键排序。否则MySQL可以返回不确定的结果

If multiple rows have identical values in the ORDER BY columns, the server is free to return those rows in any order, and may do so differently depending on the overall execution plan. In other words, the sort order of those rows is nondeterministic with respect to the nonordered columns.