概述

前段时间通过对数据库开启慢查询日志可以看到耗时超过128秒，抓了下sql出来，下面看下优化的过程吧。

慢sql

sql具体内容如下：

select * from ( SELECT
 pl.org_code,pl.dept_code,pl.section_code,pl.group_code,attendance.work_date,
 attendance.actual_shifts shifts,dst.car_type,pm.actual_production,dst.dst
 FROM
 t_ocs_production_management pm,
 t_ocs_production_lines pl,
 t_ocs_employee_attendace attendance,
 t_ocs_dst_apply_change_line dst
 WHERE
 pm.STATUS = 'Y'
 AND dst.line_status != 'NOENABLE'
 AND dst.is_delete = 'N'
 AND pm.is_delete = 'N'
 AND pl.is_delete = 'N'
 and attendance.is_delete = 'N'
 AND pm.product_type IN ('M', 'N')
 and DATE_FORMAT(attendance.work_date, '%Y-%m-%d') >= DATE_FORMAT('2019-05-01', '%Y-%m-%d')
 and DATE_FORMAT(pm.planned_date, '%Y-%m-%d') >= DATE_FORMAT('2019-05-01', '%Y-%m-%d') 
 AND pm.actual_production IS NOT NULL
 AND pm.org_code = pl.ascription_org_code
 AND pm.dept_code = pl.ascription_dept_code
 AND pm.factory_code = pl.factory_code
 AND pm.business_segment = pl.business_segment
 AND pm.brand = pl.brand
 AND pm.production_line = pl.production_line
 AND pm.product = pl.product
 AND DATE_FORMAT(pm.planned_date, '%Y-%m-%d') >= DATE_FORMAT(pl.start_time,'%Y-%m-%d')
 AND (DATE_FORMAT(pm.planned_date, '%Y-%m-%d') <= DATE_FORMAT(pl.end_time,'%Y-%m-%d') or pl.end_time is NULL)
 AND attendance.org_code = pl.org_code
 AND attendance.dept_code = pl.dept_code
 AND attendance.section_code = pl.section_code
 AND attendance.group_code = pl.group_code
 AND DATE_FORMAT(pm.planned_date, '%Y-%m-%d') = DATE_FORMAT(attendance.work_date,'%Y-%m-%d')
 AND pm.shifts = attendance.actual_shifts
 AND dst.org_code = pl.org_code
 AND dst.dept_code = pl.dept_code
 AND dst.section_code = pl.section_code
 AND dst.group_code = pl.group_code
 AND DATE_FORMAT(pm.planned_date, '%Y-%m-%d')>=DATE_FORMAT(dst.start_date, '%Y-%m-%d')
 AND (DATE_FORMAT(pm.planned_date, '%Y-%m-%d')<=DATE_FORMAT(dst.stop_date, '%Y-%m-%d') or dst.stop_date is NULL)
 AND dst.car_type = pm.product
 GROUP BY attendance.org_code,attendance.dept_code,attendance.section_code,attendance.group_code,attendance.company_attribute,
 attendance.work_date,attendance.actual_shifts,dst.car_type,pm.factory_code,pl.business_segment ,pl.brand ,pl.production_line ) auth WHERE 1=1;

优化过程

1、查看执行计划

2、查看涉及表的数据量

3、创建索引

create index idx_attendance on t_ocs_employee_attendace(is_delete,org_code,dept_code,section_code,group_code,work_date,actual_shifts,company_attribute)

4、调整sql顺序

select * from ( SELECT
 pl.org_code,pl.dept_code,pl.section_code,pl.group_code,attendance.work_date,
 attendance.actual_shifts shifts,dst.car_type,pm.actual_production,dst.dst
 FROM
 t_ocs_production_management pm,
 t_ocs_production_lines pl,
 t_ocs_employee_attendace attendance,
 t_ocs_dst_apply_change_line dst
 WHERE
 pm.STATUS = 'Y'
 AND dst.line_status != 'NOENABLE'
 AND dst.is_delete = 'N'
 AND pm.is_delete = 'N'
 AND pl.is_delete = 'N'
 and attendance.is_delete = 'N'
 AND pm.product_type IN ('M', 'N')
 AND attendance.org_code = pl.org_code
 AND attendance.dept_code = pl.dept_code
 AND attendance.section_code = pl.section_code
 AND attendance.group_code = pl.group_code
 and DATE_FORMAT(attendance.work_date, '%Y-%m-%d') >= DATE_FORMAT('2019-05-01', '%Y-%m-%d')
 and DATE_FORMAT(pm.planned_date, '%Y-%m-%d') >= DATE_FORMAT('2019-05-01', '%Y-%m-%d') 
 AND pm.actual_production IS NOT NULL
 AND pm.org_code = pl.ascription_org_code
 AND pm.dept_code = pl.ascription_dept_code
 AND pm.factory_code = pl.factory_code
 AND pm.business_segment = pl.business_segment
 AND pm.brand = pl.brand
 AND pm.production_line = pl.production_line
 AND pm.product = pl.product
 AND DATE_FORMAT(pm.planned_date, '%Y-%m-%d') >= DATE_FORMAT(pl.start_time,'%Y-%m-%d')
 AND (DATE_FORMAT(pm.planned_date, '%Y-%m-%d') <= DATE_FORMAT(pl.end_time,'%Y-%m-%d') or pl.end_time is NULL)
 AND DATE_FORMAT(pm.planned_date, '%Y-%m-%d') = DATE_FORMAT(attendance.work_date,'%Y-%m-%d')
 AND pm.shifts = attendance.actual_shifts
 AND dst.org_code = pl.org_code
 AND dst.dept_code = pl.dept_code
 AND dst.section_code = pl.section_code
 AND dst.group_code = pl.group_code
 AND DATE_FORMAT(pm.planned_date, '%Y-%m-%d')>=DATE_FORMAT(dst.start_date, '%Y-%m-%d')
 AND (DATE_FORMAT(pm.planned_date, '%Y-%m-%d')<=DATE_FORMAT(dst.stop_date, '%Y-%m-%d') or dst.stop_date is NULL)
 AND dst.car_type = pm.product
 GROUP BY attendance.org_code,attendance.dept_code,attendance.section_code,attendance.group_code,attendance.company_attribute,
 attendance.work_date,attendance.actual_shifts,dst.car_type,pm.factory_code,pl.business_segment ,pl.brand ,pl.production_line ) auth WHERE 2=2;

优化后为51秒

再次查看执行计划：

这里严格来说是没有优化成功的，具体的还去考虑，大家有什么好的想法可以在下方留言哦，一起探讨下，希望得到大家的批评~后面会分享更多devops和DBA方面的内容，感兴趣的朋友可以关注一下~