Day11——8个sql优化讲解

一. 回顾

前面学习了Day10——排序分组优化，今天学习8个sql优化讲解

二. 8个sql优化讲解

注意代码中的注释，如下：

#1、列出自己的掌门比自己年龄小的人员

#自己写的
SELECT c.* from t_emp c left join (
SELECT age, deptId  from t_emp a inner join t_dept b
on a.id = b.ceo) ab
on c.deptId = ab.deptId
where ab.age < c.age;
-----------------------------------------
#老师写的
SELECT a.name, a.age, c.name ceoname, c.age from t_emp a 
left join t_dept b on a.deptId = b.id
left join t_emp c on b.ceo = c.id
where a.age > c.age;

#清索引
call proc_drop_index("mydb", "emp");
call proc_drop_index("mydb", "dept");

#优化。将小表t_dept、t_emp换成大表dept、emp
explain SELECT SQL_NO_CACHE a.name, a.age, c.name ceoname, c.age 
from emp a 
left join dept b on a.deptId = b.id
left join emp c on b.ceo = c.id
where a.age > c.age;

================================================================================

#2、列出所有年龄低于自己门派平均年龄的人员

#自己写的
SELECT * from t_emp c left join (
SELECT deptId, avg(age) avgage from t_emp a 
where deptId is not null 
group by deptId) ab
on c.deptId = ab.deptId
where c.age < avgage;

----------------------------------------------------------------------------------
#老师写的
SELECT c.name, c.age, aa.avgage from t_emp c inner join
(SELECT a.deptid, avg(a.age) avgage from t_emp a where deptId is not null
group by a.deptId) aa 
on c.deptid = aa.deptid
where c.age < aa.avgage;

#优化
EXPLAIN SELECT SQL_NO_CACHE c.name, c.age, aa.avgage from emp c inner join
(SELECT a.deptid, avg(a.age) avgage from emp a where deptId is not null
group by a.deptId) aa 
on c.deptid = aa.deptid
where c.age < aa.avgage;

#清索引
call proc_drop_index("mydb", "emp");
call proc_drop_index("mydb", "dept");

create index idx_deptid on emp(deptid);#解决using filesort，给group by加索引
create index idx_deptid_age on emp(deptid, age);#解决using join buffer（使用了连接缓存）

================================================================================

#3、列出至少有2个年龄大于40岁的成员的门派
#自己写的
SELECT * from t_dept b inner join (
SELECT deptId, count(deptId) num from t_emp 
where age > 40
GROUP BY deptId)a
on b.id = a.deptId
where a.num >= 2;

#老师写的
SELECT b.deptName, b.id, count(*) from t_emp a inner join t_dept b 
on a.deptId = b.id
where a.age > 40
GROUP BY b.deptName, b.id
HAVING count(*) >= 2;

#优化
EXPLAIN SELECT SQL_NO_CACHE b.deptName, b.id, count(*) from emp a inner join dept b 
on a.deptId = b.id
where a.age > 40
GROUP BY b.deptName, b.id
HAVING count(*) >= 2;#mysql选择了a表作为驱动表，这与选择小表作为驱动表的原则不符合。

#怎么解决？使用straight_join，straight_join前面的是驱动表
EXPLAIN SELECT SQL_NO_CACHE b.deptName, b.id, count(*) from dept b STRAIGHT_JOIN emp a 
on a.deptId = b.id
where a.age > 40
GROUP BY b.deptName, b.id
HAVING count(*) >= 2;

#使用straight_join要注意2点：
#1.对straight_join的含义十分明白，straight_join前面的是驱动表，后面的是被驱动表
#2.对lia两张表s数据量十分清楚

#清索引
call proc_drop_index("mydb", "emp");
call proc_drop_index("mydb", "dept");

#建索引
create index idx_deptid_age on emp(deptid, age);#对a表的on、where条件子句建索引
create index idx_deptName on dept(deptName);#对a表的on、where条件子句建索引

================================================================================


#4、至少有2位非掌门人成员的门派
#自己写的
 SELECT * from (
 SELECT deptId, count(deptId) num from t_emp a inner join t_dept b
 on a.deptId = b.id
 where a.id != b.ceo
 GROUP BY deptId) ab inner join t_dept c 
 on ab.deptId = c.id
 where num >= 2;

#老师写的

SELECT c.deptName, c.id, count(*) from t_emp a 
inner join t_dept c on c.id = a.deptId
left join t_dept b on a.id = b.CEO
where b.id is null
GROUP BY c.deptName, c.id
HAVING count(*) >= 2;

#优化
EXPLAIN SELECT SQL_NO_CACHE c.deptName, c.id, count(*) from emp a 
inner join dept c on c.id = a.deptId
left join dept b on a.id = b.CEO
where b.id is null
GROUP BY c.deptName, c.id
HAVING count(*) >= 2;

#使用straight_join直连 优化
EXPLAIN SELECT SQL_NO_CACHE c.deptName, c.id, count(*) from dept c
straight_join emp a  on c.id = a.deptId
left join dept b on a.id = b.CEO
where b.id is null
GROUP BY c.deptName, c.id
HAVING count(*) >= 2;

#建索引
create index idx_deptid on emp(deptid);#对a表建索引
create index idx_CEO on dept(CEO);#对b表建索引
create index idx_deptName on dept(deptName);#对c表建索引

================================================================================


#5、列出全部人员，并增加一列备注“是否为掌门”，如果是掌门人显示是，不是掌门人显示否
#自己写的
SELECT a.*, b.*, b.ceo '是否掌门人' from t_emp a left join t_dept b
on a.deptId = b.id;

#老师写的
SELECT a.name, case when b.id is null then '否' else '是' end '是否为掌门'
from t_emp a left join t_dept b 
on a.id = b.CEO;

#总结：使用case when _condition then 'x' else 'x' end 'cloumnName'

================================================================================

#6、列出全部门派，并增加一列备注“老鸟or菜鸟”，若门派的平均值年龄>50显示“老鸟”，否则显示“菜鸟”
#老师写的
SELECT b.deptName, b.id,
if(avg(a.age)>50, '老鸟', '菜鸟') '老鸟or菜鸟'
from t_emp a inner join t_dept b
on a.deptid = b.id
GROUP BY b.deptName, b.id;

================================================================================

#7、显示每个门派年龄最大的人
#自己写的
SELECT * from t_emp c inner join (
SELECT deptId, max(age) maxage from t_emp a inner join t_dept b
on a.deptId = b.id
GROUP BY deptId) ab
on c.deptId = ab.deptId
where maxage = c.age;

#老师写的
SELECT c.name, c.age from t_emp c inner join (
SELECT a.deptId, max(a.age) maxage
from t_emp a where a.deptId is not null
group by a.deptId) aa
on c.deptId = aa.deptId and c.age = aa.maxage;

================================================================================

#8、显示每个门派年龄第二大的人

set @rank = 0;#1个@是自定义变量
set @last_deptid = 0;
SELECT a.deptid,a.name,a.age
 FROM( 
SELECT t.*,
if(@last_deptid=deptid,@rank:=@rank+1,@rank=1) as rk,
@last_deptid:=deptid as last_deptid
from t_emp t
ORDER BY deptId, age desc
)a WHERE a.rk=2;

------------------------------------------------------------
#如果有2个人的age是相同的，上面的代码会有bug，以下为解决方案：
UPDATE t_emp SET age=90 WHERE id =2;

SET @rank=0;
SET @last_deptid=0;
SET @last_age=0;#再设多一个判断条件，当deptid相等且age相等时，门派内的排名不变，否则排名加1

SELECT t.*,
IF(@last_deptid=deptid,
IF(@last_age = age,@rank,@rank:=@rank+1)
,@rank:=1) AS rk,
@last_deptid:=deptid AS last_deptid,
@last_age :=age AS last_age
FROM t_emp t
ORDER BY deptid,age DESC