优化案例

末蓝、 2023-10-06 13:02 17阅读 0赞

#### 文章目录 ####

*  单表优化
 *  两表优化
 *  三张表优化A B C

## 单表优化 ##

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

**总结：**  
1、如果 (a,b,c,d)复合索引 和使用的顺序全部一致(且不跨列使用)，则复合索引全部使用。如果部分一致(且不跨列使用)，则使用部分索引。  
select a,c where a = and b= and d=

2、where和order by 拼起来，不要跨列使用