sql优化-把派生表改成子查询，查询速度将变快

使用数据库：MYSQL 5.7.27

参考资料：

数据库~Mysql派生表注意的几点~关于百万数据的慢查询问题

https://blog.youkuaiyun.com/weixin_34146410/article/details/93984487

子查询：在一个查询中嵌套另一个查询，则另一个查询成为子查询，也叫内部查询

派生表：在FROM子句中使用子查询时，从子查询返回的结果集将用作临时表。 该表称为派生表

当查询的复杂sql数据量大时，把 派生表 改成 子查询， 查询速度将变快

因为派生表不能走索引，子查询可以走索引

 两张表：

ding_talk_employee 钉钉员工表： 总共408条数据

ding_talk_employee_analysis 钉钉员工统计年份分析表： 总共2259条数据

在数据量比较小时： 派生表查询速度比子查询快一倍

派生表sql：

SELECT
            t0.department_name,
            t0.department_id,
            -- 上一年度正式人员
            IFNULL(t1.beforeYearNormalCount,0) as beforeYearNormalCount,
            -- 当前正式人员
            IFNULL(t2.normalCount,0) as normalCount,
            -- 较上年新增正式人员数
            IFNULL(t2.normalCount,0)-IFNULL(t1.beforeYearNormalCount,0) as normalCompareBeforeYearCount,
            -- 试用人员
            IFNULL(t3.probationCount,0) as probationCount,
            -- 人员折算总计
            IFNULL(t4.sumCoefficient,0) as sumCoefficient
        from
            (
                select
                    MIN(a.department_name) as department_name,
                    a.department_id,
                    count(1)
                from ding_talk_employee a
                where a.department_name !='' -- and dimission_remarks !='不统计'
                group by a.department_id ORDER BY department_name
            ) as t0
                LEFT JOIN
            (
                -- 上一年度正式人员
                SELECT
                    a.department_id,
                    count( 1 ) AS beforeYearNormalCount
                FROM
                    ding_talk_employee a
                        INNER JOIN ding_talk_employee_analysis dtea ON a.userid = dtea.userid
                WHERE
                    dtea.count_year = DATE_ADD('2021-01-01 00:00:00', INTERVAL - 1 YEAR )
                  AND dtea.employee_status IN ( '3', '5' )
                GROUP BY a.department_id
            ) AS t1 on t1.department_id=t0.department_id
                LEFT JOIN
            (
                -- 当前正式人员
                SELECT
                    a.department_id,
                    count( 1 ) AS normalCount
                FROM
                    ding_talk_employee a
                        INNER JOIN ding_talk_employee_analysis dtea ON a.userid = dtea.userid
                WHERE
                    dtea.count_year = '2021-01-01 00:00:00'
                  AND dtea.employee_status IN ( '3', '5' )
                GROUP BY a.department_id
            ) AS t2 on t2.department_id=t0.department_id
                LEFT JOIN
            (
                -- 试用人员
                SELECT
                    a.department_id,
                    count(1) AS probationCount
                FROM
                    ding_talk_employee a
                        INNER JOIN ding_talk_employee_analysis dtea ON a.userid = dtea.userid
                WHERE
                    dtea.count_year = '2021-01-01 00:00:00'
                  AND dtea.employee_status = '2'
                GROUP BY a.department_name
            ) AS t3 on t3.department_id=t0.department_id
                LEFT JOIN
            (
                SELECT
                    a.department_id,
                    SUM(dtea.coefficient) AS sumCoefficient
                FROM
                    ding_talk_employee a
                        INNER JOIN ding_talk_employee_analysis dtea ON a.userid = dtea.userid
                WHERE
                    dtea.count_year = '2021-01-01 00:00:00'
                GROUP BY a.department_id
            ) AS t4 on t4.department_id=t0.department_id

不使用缓存的查询时间

派生表查询时间 0.047s

使用 explain 命令查看索引使用情况

总共5个派生表，看索引情况，派生表会自动创建索引

 查看具体耗时步骤：

使用命令：

show profiles (查询刚才执行sql的对应id)
show profile for query 26

派生表转换成子查询后的sql：

SELECT
    tu.department_name,
    tu.department_id,
    -- 上一年度正式人员
    tu.beforeYearNormalCount,
    -- 当前正式人员
    tu.normalCount,
    -- 较上年新增正式人员数
    IFNULL(tu.normalCount,0)-IFNULL(tu.beforeYearNormalCount,0) as normalCompareBeforeYearCount,
    -- 试用人员
    tu.probationCount,
    -- 人员折算总计
    tu.sumCoefficient
FROM (
    SELECT
            MIN(t0.department_name) as department_name,
            t0.department_id,
            
            
            IFNULL(
            (
                -- 上一年度正式人员
                SELECT
                    count( 1 ) AS beforeYearNormalCount
                FROM
                    ding_talk_employee a
                        INNER JOIN ding_talk_employee_analysis dtea ON a.userid = dtea.userid
                WHERE
                    a.department_id=t0.department_id
                    AND dtea.count_year = DATE_ADD('2021-01-01 00:00:00', INTERVAL - 1 YEAR )
                  AND dtea.employee_status IN ( '3', '5' )
                GROUP BY a.department_id
            )
            ,0) as beforeYearNormalCount,
            
            IFNULL(
            (
                -- 当前正式人员
                SELECT
                    count( 1 ) AS normalCount
                FROM
                    ding_talk_employee a
                        INNER JOIN ding_talk_employee_analysis dtea ON a.userid = dtea.userid
                WHERE
                    a.department_id=t0.department_id
                    AND    dtea.count_year = '2021-01-01 00:00:00'
                  AND dtea.employee_status IN ( '3', '5' )
                GROUP BY a.department_id
            )
            ,0) as normalCount,
            
            
            IFNULL(
            (
                -- 试用人员
                SELECT
                    count(1) AS probationCount
                FROM
                    ding_talk_employee a
                        INNER JOIN ding_talk_employee_analysis dtea ON a.userid = dtea.userid
                WHERE
                  a.department_id=t0.department_id
                    AND dtea.count_year = '2021-01-01 00:00:00'
                  AND dtea.employee_status = '2'
                GROUP BY a.department_name
            )
            ,0) as probationCount,
            
            IFNULL(
            (
                SELECT
                    SUM(dtea.coefficient) AS sumCoefficient
                FROM
                    ding_talk_employee a
                        INNER JOIN ding_talk_employee_analysis dtea ON a.userid = dtea.userid
                WHERE
                  a.department_id=t0.department_id
                    AND dtea.count_year = '2021-01-01 00:00:00'
                GROUP BY a.department_id
            )
            ,0) as sumCoefficient
            
        FROM
            ding_talk_employee t0
        WHERE 
            t0.department_name !='' -- and dimission_remarks !='不统计'
        GROUP BY t0.department_id 
        ORDER BY t0.department_name
) as tu

 

 使用子查询的查询时间0.068s