使用数据库: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