单表优化
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 ;
alter table book add index idx_bta (bid,typeid,authorid);--加索引
优化索引
--索引一旦进行 升级优化，需要将之前废弃的索引删掉，防止干扰。
drop index idx_bta on book;
--根据SQL实际解析的顺序，调整索引的顺序：
alter table book add index idx_tab (typeid,authorid,bid);
--虽然可以回表查询bid，但是将bid放到索引中 可以提升使用using index ;
--再次优化（之前是index级别）：
思路。因为范围查询in有时会实现，因此交换 索引的顺序，将typeid in(2,3) 放到最后。
drop index idx_tab on book;
alter table book add index idx_atb (authorid,typeid,bid);
--最终SQL
explain select bid from book where authorid=1 and typeid in(2,3) order by typeid desc ;
1.最佳做前缀，保持索引的定义和使用的顺序一致性
2.索引需要逐步优化
3.将含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 ;
两表优化
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;
当编写 …on t.cid=c.cid 时，将数据量小的表 放左边（假设此时t表数据量小）
explain select *from teacher2 t left outer join course2 c
on t.cid=c.cid where c.cname='java';
索引往哪张表加？
- -小表驱动大表
- -索引建立经常使用的字段上
（本题 t.cid=c.cid可知，t.cid字段使用频繁，因此给该字段加索引） [一般情况对于左外连接，给左表加索引；右外连接，给右表加索引]
优化
alter table teacher2 add index index_teacher2_cid(cid) ;
alter table course2 add index index_course2_cname(cname);
三张表优化A B C
- -小表驱动大表
- -索引建立经常使用的字段上
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；
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
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 …
explain select * from test03 where a2=2 and a4=4 group by a2,a4 ;--没有using temporary
explain select * from test03 where a2=2 and a4=4 group by a3 ;
避免索引失效的一些原则
- 复合索引，不要跨列或无序使用（最佳左前缀）
- 复合索引，尽量使用全索引匹配
- 不要在索引上进行任何操作（计算、函数、类型转换），否则索引失效
- select …where A.x = … ; --假设A.x是索引
不要：select …where A.x*3 = … ;
explain select * from book where authorid = 1 and typeid = 2 ;--用到了at2个索引
explain select * from book where authorid = 1 and typeid*2 = 2 ;--用到了a1个索引
explain select * from book where authorid*2 = 1 and typeid*2 = 2 ;----用到了0个索引
explain select * from book where authorid*2 = 1 and typeid = 2 ;----用到了0个索引,原因：对于复合索引，如果左边失效，右侧全部失效。(a,b,c)，例如如果 b失效，则b c同时失效。
drop index idx_atb on book ;
alter table book add index idx_authroid (authorid) ;
alter table book add index idx_typeid (typeid) ;
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 ; - SQL优化，是一种概率层面的优化。至于是否实际使用了我们的优化，需要通过explain进行推测。
explain select * from book where authorid != 1 and typeid =2 ;
explain select * from book where authorid != 1 and typeid !=2 ;
体验概率情况(< > =) 原因是服务层中有SQL优化器，可能会影响我们的优化。
drop index idx_typeid on book;
drop index idx_authroid on book;
alter table book add index idx_book_at (authorid,typeid);
explain select * from book where authorid = 1 and typeid =2 ;--复合索引at全部使用
explain select * from book where authorid > 1 and typeid =2 ; --复合索引中如果有>，则自身和右侧索引全部失效。
explain select * from book where authorid = 1 and typeid >2 ;--复合索引at全部使用
----明显的概率问题---
explain select * from book where authorid < 1 and typeid =2 ;--复合索引at只用到了1个索引
explain select * from book where authorid < 4 and typeid =2 ;--复合索引全部失效
我们学习索引优化 ，是一个大部分情况适用的结论，但由于SQL优化器等原因 该结论不是100%正确。 一般而言， 范围查询（> < in），之后的索引失效。
- 尽量使用索引覆盖（using index）
（a,b,c）
select a,b,c from xx..where a= .. and b =.. ;
- like尽量以“常量”开头，不要以’%'开头，否则索引失效
select * from xx where name like '%x%' ; --name索引失效
explain select * from teacher where tname like '%x%'; --tname索引失效
explain select * from teacher where tname like 'x%';
explain select tname from teacher where tname like '%x%'; --如果必须使用like '%x%'进行模糊查询，可以使用索引覆盖 挽救一部分。
- 尽量不要使用类型转换（显示、隐式），否则索引失效
explain select * from teacher where tname = 'abc' ;
explain select * from teacher where tname = 123 ;//程序底层将 123 -> '123'，即进行了类型转换，因此索引失效
- 尽量不要使用or，否则索引失效
explain select * from teacher where tname ='' or tcid >1 ; --将or左侧的tname 失效。
-
exist和in
select …from table where exist (子查询) ;
select …from table where 字段 in (子查询) ;如果主查询的数据集大，则使用In ,效率高。
如果子查询的数据集大，则使用exist,效率高。
exist语法： 将主查询的结果，放到子查需结果中进行条件校验（看子查询是否有数据，如果有数据 则校验成功） ，
如果 复合校验，则保留数据；
select tname from teacher where exists (select * from teacher) ;
--等价于select tname from teacher
select tname from teacher where exists (select * from teacher where tid =9999) ;
in:
select ..from table where tid in (1,3,5) ;
扫一扫
936
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
