Day 11——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