6 优化案例****
单表优化、两表优化、三表优化
6.1单表优化****
create table book ( bid int(4) primary key, name varchar(20) not null, authorid int(4) not null, publicid 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(3,'wx',3,2,1) ; insert into book values(4,'math',4,2,3) ; commit; 查询authorid=1且 typeid为2或3的 bid explain select bid from book where typeid in(2,3) and authorid=1 order by typeid desc ; (a,b,c) (a,b)
1、优化：加索引****
alter table book add index idx_bta (bid,typeid,authorid);
索引一旦进行 升级优化，需要将之前废弃的索引删掉，防止干扰。
drop index idx_bta on book;
2、根据SQL实际解析的顺序，调整索引的顺序：
alter table book add index idx_tab (typeid,authorid,bid); --虽然可以回表查询bid，但是将bid放到索引中 可以提升使用using index ;
3、再次优化（之前是index级别）：****
思路。因为范围查询in有时会实现，因此交换 索引的顺序，将typeid in(2,3) 放到最后。
drop index idx_tab on book; alter table book add index idx_atb (authorid,typeid,bid); explain select bid from book where authorid=1 and typeid in(2,3) order by typeid desc ;
4、小结：****
a.最佳做前缀，保持索引的定义和使用的顺序一致性
b.索引需要逐步优化
c.将含In的范围查询 放到where条件的最后，防止失效。
本例中同时出现了Using where（需要回原表）; Using index（不需要回原表）：原因，where authorid=1 and typeid in(2,3)中authorid在索引(authorid,typeid,bid)中，因此不需要回原表（直接在索引表中能查到）；而typeid虽然也在索引(authorid,typeid,bid)中，但是含in的范围查询已经使该typeid索引失效，因此相当于没有typeid这个索引，所以需要回原表（using where）；
例如以下没有了In，则不会出现using where
explain select bid from book where authorid=1 and typeid =3 order by typeid desc ;
还可以通过key_len证明In可以使索引失效。
6.2两表优化****
create table teacher2 ( tid int(4) primary key, cid int(4) not null ); insert into teacher2 values(1,2); insert into teacher2 values(2,1); insert into teacher2 values(3,3); create table course2 ( cid int(4) , cname varchar(20) ); insert into course2 values(1,'java'); insert into course2 values(2,'python'); insert into course2 values(3,'kotlin'); commit; 左连接： explain select *from teacher2 t left outer join course2 c on t.cid=c.cid where c.cname='java';
1、索引往哪张表加？****
-小表驱动大表
-索引建立经常使用的字段上 （本题 t.cid=c.cid可知，t.cid字段使用频繁，因此给该字段加索引） [一般情况对于左外连接，给左表加索引；右外连接，给右表加索引]
小表：10
大表：300
where 小表.x 10 = 大表.y 300; --循环了几次？10
大表.y 300=小表.x 10 --循环了300次
小表:10
大表:300
select ...where 小表.x10=大表.x300 ; for(int i=0;i<小表.length10;i++) { for(int j=0;j<大表.length300;j++) { ... } } select ...where 大表.x300=小表.x10 ; for(int i=0;i<大表.length300;i++) { for(int j=0;j<小表.length10;j++) { ... } }
--以上2个FOR循环，最终都会循环3000次；但是 对于双层循环来说：一般建议 将数据小的循环 放外层；数据大的循环放内存。
--当编写 ..on t.cid=c.cid 时，将数据量小的表 放左边（假设此时t表数据量小）
alter table teacher2 add index index_teacher2_cid(cid) ; alter table course2 add index index_course2_cname(cname);
Using join buffer:extra中的一个选项，作用：Mysql引擎使用了 连接缓存。
6.3三张表优化A B C****
a.小表驱动大表****
b.索引建立在经常查询的字段上****
示例：
create table test03 ( a1 int(4) not null, a2 int(4) not null, a3 int(4) not null, a4 int(4) not null ); alter table test03 add index idx_a1_a2_a3_4(a1,a2,a3,a4) ; explain select a1,a2,a3,a4 from test03 where a1=1 and a2=2 and a3=3 and a4 =4 ; --推荐写法，因为 索引的使用顺序（where后面的顺序） 和 复合索引的顺序一致 explain select a1,a2,a3,a4 from test03 where a4=1 and a3=2 and a2=3 and a1 =4 ; --虽然编写的顺序 和索引顺序不一致，但是 sql在真正执行前 经过了SQL优化器的调整，结果与上条SQL是一致的。 --以上 2个SQL，使用了 全部的复合索引 explain select a1,a2,a3,a4 from test03 where a1=1 and a2=2 and a4=4 order by a3;
--以上SQL用到了a1 a2两个索引，该两个字段 不需要回表查询using index ;而a4因为跨列使用，造成了该索引失效，需要回表查询 因此是using where；以上可以通过 key_len进行验证
explain select a1,a2,a3,a4 from test03 where a1=1 and a4=4 order by a3;
--以上SQL出现了 using filesort(文件内排序，“多了一次额外的查找/排序”) ：不要跨列使用( where和order by 拼起来，不要跨列使用)
explain select a1,a2,a3,a4 from test03 where a1=1 and a4=4 order by a2 , a3; --不会using filesort
6.4总结：****
i.如果 (a,b,c,d)复合索引 和使用的顺序全部一致(且不跨列使用)，则复合索引全部使用。如果部分一致(且不跨列使用)，则使用部分索引。
select a,c where a = and b= and d=
ii.where和order by 拼起来，不要跨列使用
using temporary:需要额外再多使用一张表. 一般出现在group by语句中；已经有表了，但不适用，必须再来一张表。
解析过程：
from .. on.. join ..where ..group by ....having ...select dinstinct ..order by limit ... a. explain select * from test03 where a2=2 and a4=4 group by a2,a4 ;--没有using temporary b. explain select * from test03 where a2=2 and a4=4 group by a3 ;****
