SQL优化实战一

1.分别创建课程表，学生表，成绩表

CREATE TABLE Course (
	c_id int PRIMARY KEY,
	name varchar(10)
);

CREATE TABLE Student (
	id int PRIMARY KEY,
	name varchar(10)
);

CREATE TABLE SC (
	sc_id int PRIMARY KEY,
	s_id int,
	c_id int,
	score int
)

2.相关函数设计

###随机字符串产生函数：
delimiter $$
create function rand_string(n int) returns varchar(255)
 begin
   declare chars_str varchar(100) default 'abcdefghijklmnopqrstuvwxyz0123456789';
   declare return_str varchar(255) default '';
   declare i int default 0;
   while i<n do
   set return_str=concat(return_str,substring(chars_str,floor(1+rand()*36),1));
   set i=i+1; 
   end while;
   return return_str;
end $$

##随机产生成绩（0~100）
delimiter $$
 create function rand_sc() returns int(5)
 begin
   declare i int default 0;
   set i=floor(rand()*100);
 return i;
 end $$

###批量插入课程表数据：

delimiter $$
create procedure insert_cr(in start int(10),in max_num int(10))
begin
 declare i int default 0;
 /*把autocommit设置成0*/
 set autocommit= 0;
 repeat
 set i=i+1;
 insert into course(c_id,name)
 values((start+i),rand_string(6));
 until i=max_num end repeat;
commit;
end $$
##插入课程信息
call insert_cr(0,10);

###批量插入学生表数据
delimiter $$
create procedure insert_st(in start int(10),in max_num int(10))
begin
 declare i int default 0;
 /*把autocommit设置成0*/
 set autocommit= 0;
 repeat
 set i=i+1;
 insert into student(id,name)
 values((start+i),rand_string(10));
 until i=max_num end repeat;
commit;
end $$

##插入9万学生数据
call insert_st(0,90000);
#[SQL] call insert_st(0,90000);
#受影响的行: 0
#时间: 25.293s

###批量插入学生成绩
delimiter $$
create procedure insert_sc(in start int(10),in max_num int(10))
begin
 declare i int default 0;
 /*把autocommit设置成0*/
 set autocommit= 0;
 repeat
 set i=i+1;
 insert into sc(sc_id,s_id,c_id,score)
 values((start+i),FLOOR(rand()*90000),FLOOR(rand()*10),FLOOR(rand()*100));
 until i=max_num end repeat;
commit;
end $$
#插入学生成绩数据
call insert_sc(0,800000);
#[SQL] call insert_sc(0,800000);
#受影响的行: 0
#时间: 33.031s

3.执行sql

sql：select s.* from Student s where s.id in (select s_id from SC sc where sc.c_id = 0 and sc.score = 100 )

explain 查看该SQL的执行计划

如上图type全是all，没有使用到索引。那么我们就先给80万数据的sc表建立索引

CREATE index sc_id_index on SC(c_id);
CREATE index sc_score_index on SC(score);

再次执行之前的查询sql:

震惊。。。。。这优化速度也太夸张了！ 

再次查看它的执行计划explain 

ps：各参数说明

1.select_type  :查询类型
    simple简单查询   primary 主查询  UNION 第二个或者后面的查询语句。SUBQUERY : 子查询中的第一个select
2.table ：输出结果的表
3.type:查询级别
	type＝ALL　全表扫描，
	type＝index 索引全扫描，遍历整个索引来查询匹配的行
	type=range 索引范围扫描，常见于　<,<=,>,>=,between,in等操作符。
　　例 　explain select * from adminlog where id>0 , 
　　　　explain select * from adminlog where id>0 and id<=100
　　　　explain select * from adminlog where id in (1,2)  
	type=ref  使用非唯一索引或唯一索引的前缀扫描，返回匹配某个单独值的记录行。ref还经常出现在JOIN操作中
	type=eq_ref 类似于ref，区别就在使用的索引是唯一索引，对于每个索引键值，表中有一条记录匹配；简单来说，说是多表连接中使用　主建或唯一健作为关联条件
	type=const/system 单表中最多有一个匹配行。主要用于比较primary key [主键索引]或者unique[唯一]索引,因为数据都是唯一的，所以性能最优。条件使用=。 
type=NULL　不用访问表或者索引，直接就能够得到结果
4、possible_keys : 可能使用的索引列表.
5、key : 实现执行使用索引列表
6、key_len : 索引的长度
7、ref : 显示使用哪个列或常数与key一起从表中选择行。
8、row : 执行查询的行数，简单且重要，数值越大越不好，说明没有用好索引

 在这里使用explain extended；show warnings查看mysql具体优化后的sql;

select `test01`.`s`.`id` AS `id`,`test01`.`s`.`name` AS `name` from `test01`.`student` `s` where <in_optimizer>(`test01`.`s`.`id`,<exists>(select 1 from `test01`.`sc` where ((`test01`.`sc`.`score` = 100) and (`test01`.`sc`.`c_id` = 0) and (<cache>(`test01`.`s`.`id`) = `test01`.`sc`.`s_id`))))

在此分析之前我们先来了解下一个完整sql在具体执行过程中的执行顺序；

<SELECT clause> [<FROM clause>] [<WHERE clause>] [<GROUP BY clause>] [<HAVING clause>] [<ORDER BY clause>] [<LIMIT clause>]  

 开始->FROM子句->ON->JOIN->WHERE子句->GROUP BY子句->HAVING子句->ORDER BY子句->SELECT子句->LIMIT子句->最终结果 
每个子句执行后都会产生一个中间结果，供接下来的子句使用，如果不存在某个子句，就跳过。

区分in和exists，如果是exists，那么以外层表为驱动表，先被访问，如果是IN，那么先执行子查询。所以IN适合于外表大而内表小的情况；EXISTS适合于外表小而内表大的情况。

分析得上述SQL执行次数约为 90000*1

 

参考文档：https://mp.weixin.qq.com/s/Jy8qulviR9h7Up0Iy0fGYQ

                  https://www.cnblogs.com/yyjie/p/7788428.html