提供了编程的基础技术教程

zl程序教程

您现在的位置是：首页 >  数据库

当前栏目

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

SQL 优化 查询 速度 改成 派生
2023-09-11 14:19:52 时间

使用数据库：MYSQL 5.7.27

参考资料：

数据库~Mysql派生表注意的几点~关于百万数据的慢查询问题
https://blog.csdn.net/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

 

 

 

 
 

相关文章