单表优化

准备数据：
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) ;

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

explain select bid from book where typeid in(2,3) and 
authorid=1  order by typeid desc ;

进行优化：
1、加索引

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

索引一旦进行 升级优化，需要将之前废弃的索引删掉，防止干扰。
    drop index idx_bta on book;

2、根据SQL实际解析的顺序，调整索引的顺序：（到达index级别）

    alter table book add index idx_tab (typeid,authorid,bid);
--虽然可以回表查询bid，但是将bid放到索引中 可以提升使用using index ;

可以看到此处复合索引的顺序已经变了，因为查询语句是按照typeid、authorid来的，如果复合索引顺序不是这个，则会导致SQL语句性能降低或者导致索引失效

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 ;

这样即使索引typeid失效，索引authorid也能被使用。

小结：
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）；

两表优化

准备数据：
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');

示例：
将两张表进行左连接查询：

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字段使用频繁，因此给该字段加索引，当然c.cname是肯定要加的） [一般情况对于左外连接，给左表加索引；右外连接，给右表加索引]

2、有一个结论： 小表驱动大表

小表：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次；
但是 对于双层循环来说：一般建议 将数据小的循环 放外层；
数据大的循环放内存。

继续回到sql语句优化：

当编写..on t.cid=c.cid 时，将数据量小的表 放左边(假设此时t表数据量小)
    
alter table teacher2 add index index_teacher2_cid(cid) ;
alter table course2 add index index_course2_cname(cname);

此时优化级别就能达到ref了！！！

Using join buffer:extra中的一个选项
出现原因：你的sql语句写的太烂了。
作用：Mysql引擎使用了连接缓存。

三张表优化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