Day11——8个sql优化讲解

灰太狼 2022-11-27 08:53 67阅读 0赞

### 文章目录 ###

*  一. 回顾
 *  二. 8个sql优化讲解

# 一. 回顾 #

前面学习了[Day10——排序分组优化][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

[Day10]: https://blog.csdn.net/qq_40634846/article/details/108004237