SQL语句单表的优化

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的最后，防止失效。