表及相關(guān)數(shù)據(jù)

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

優(yōu)化方案

方法1:

索引的順序是 bid,typeid,authorid

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

分析結(jié)果

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);

分析結(jié)果

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 的出現(xiàn) Using filesort, 而 typeid,authorid,bid卻沒有出現(xiàn) Using filesort?

總結(jié)

以上是生活随笔為你收集整理的MySQL倒序如何避免filesort_MySQL Using filesort 疑问?的全部內(nèi)容，希望文章能夠幫你解決所遇到的問題。

如果覺得生活随笔網(wǎng)站內(nèi)容還不錯(cuò)，歡迎將生活随笔推薦給好友。