② 优化2：使用了in有时候会导致索引失效，基于此有了如下一种优化思路。
将in字段放在最后面。需要注意一点：每次创建新的索引的时候，最好是删除以前的废弃索引，否则有时候会产生干扰(索引之间)。
# 删除以前的索引 drop index typeid_authorid_bid on book; # 再次创建索引 create index authorid_typeid_bid on book(authorid,typeid,bid); # 再次查看执行计划 explain select bid from book where authorid=1 and typeid in(2,3) order by typeid desc ;
结果如下：
结果分析：这里虽然没有变化，但是这是一种优化思路。
总结如下：
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 ;
结果如下：
3）两表优化
# 创建teacher2新表 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); # 创建course2新表 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');
结果如下：
案例：使用一个左连接，查找教java课程的所有信息。
explain select * from teacher2 t left outer join course2 c on t.cid=c.cid where c.cname='java';
结果如下：
① 优化
对于两张表，索引往哪里加？答：对于表连接，小表驱动大表。索引建立在经常使用的字段上。
为什么小表驱动大表好一些呢？
小表: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表数据量小，c表数据量大。）
我们已经知道了，对于两表连接，需要利用小表驱动大表。例如【…on t.cid=c.cid】，t如果是小表(10条)，c如果是大表(300条)，那么t每循环1次，就需要循环300次，即t表的t.cid字段属于经常使用的字段，因此需要给cid字段添加索引。
更深入的说明：一般情况下，左连接给左表加索引。右连接给右表加索引。其他表需不需要加索引，我们逐步尝试。
# 给左表的字段加索引 create index cid_teacher2 on teacher2(cid); # 查看执行计划 explain select * from teacher2 t left outer join course2 c on t.cid=c.cid where c.cname='java';
结果如下：
当然你可以下去接着优化，给cname添加一个索引。索引优化是一个逐步的过程，需要一点点尝试。
# 给cname的字段加索引 create index cname_course2 on course2(cname); # 查看执行计划 explain select t.cid,c.cname from teacher2 t left outer join course2 c on t.cid=c.cid where c.cname='java';
结果如下：
最后补充一个：Using join buffer是extra中的一个选项，表示Mysql引擎使用了连接缓存，即MySQL底层动了你的SQL，你写的太差了。
4）三表优化
大于等于2张表，优化原则一样；
小表驱动大表 ；
索引建立在经常查询的字段上；
7、避免索引失效的一些原则
① 复合索引需要注意的点
a.复合索引，不要跨列或无序使用(最佳左前缀)；
b.复合索引，尽量使用全索引匹配，也就是说，你建立几个索引，就使用几个索引；
② 不要在索引上进行任何操作(计算、函数、类型转换)，否则索引失效
explain select * from book where authorid = 1 and typeid = 2; explain select * from book where authorid*2 = 1 and typeid = 2 ;
结果如下：
③ 索引不能使用不等于（!= <>）或is null (is not null)，否则自身以及右侧所有全部失效(针对大多数情况)。复合索引中如果有>，则自身和右侧索引全部失效。
# 针对不是复合索引的情况 explain select * from book where authorid != 1 and typeid =2 ; explain select * from book where authorid != 1 and typeid !=2 ;
结果如下：
