1.建表语句
%建表语句
create table book (
bookid int(4) primary key,
name varchar(20) not null,
authorid int(4) not null,
publicic int(4) not null,
typeid int(4) not null
);
%王数据库里面插入相应的值
insert into book values (1,'tjava',1,1,2);
insert into book values (2,'tc',2,1,2);
insert into book values (2,'tc',2,1,2);
insert into book values (4,'math',4,2,3);
2.相关表的操作
2.1
查询authorid=1且typeid为2或3的bid
(1)没有使用索引的情况
explain select bookid from book where authorid = 1 and typeid in (2,3);
执行情况分析:没有使用到任何索引,使用到了回表查询(usingwhere)和文件内排序(usingfilesort)
(2)建立一个顺序不符合查询顺序的索引
create index index_bat on book (bookid,authorid,typeid);
%分析执行情况
explain select bookid from book where authorid = 1 and typeid in (2,3) order by typeid;
分析执行情况:使用到的索引类型为index,但是由于sql语句是从from开始执行的,select是最后执行的,所以建立的索引并不符合顺序,所以使用到了usingfilesort
(3)建立一个符合顺序的索引
drop index index_bat on book;
create index index_atb on book (authorid,typeid,bookid);
explain select bookid from book where authorid = 1 and typeid in (2,3) order by typeid;
分析执行情况:使用到的索引是ref提高了执行效率,而且没有了usingfilesort,执行效率大大提高。注意:在对表进行优化时,要先把之前的索引进行删除,防止对新建的索引造成影响。本例中同时出现了usingwhere和usingindex,说明同时需要回表查询,原因是authorid不需要回表查询,直接在索引中查询,但是typeid是in的范围查询,使用in的范围查询会使索引失效,从而进行回表查询。
将in换成等于就不会再用刀回表查询,示例如下:
explain select bookid from book where authorid = 1 and typeid =3 order by typeid;
结果分析:只有usingindex
总结:
(1)最佳左前缀,保证索引的定义和使用的顺序是一致的
(2)索引需要逐步优化
(3)将in的范围查询放到where的最后,防止失效。