玖叶教程网

前端编程开发入门

2024年04月21日

记一次生产mysql数据库sql每周优化

概述

生产环境一般每周都会找一个库去做一次sql优化，简单记录如下：

1、慢查询配置

#设置慢查询阀值，单位为秒
long_query_time=120
slow_query_log=1         #开启mysql慢sql的日志
log_output=table,File    #日志输出会写表，也会写日志文件，为了便于程序去统计，所以最好写表
slow_query_log_file=/data/log/slow.log


2、慢查询日志分析

2.1、mysqldumpslow分析

-s 按照那种方式排序
    c：访问计数
    l：锁定时间
    r:返回记录
    al：平均锁定时间
    ar：平均访问记录数
    at：平均查询时间
-t 是top n的意思，返回多少条数据。
-g 可以跟上正则匹配模式，大小写不敏感。

2.2、执行分析脚本

echo "==============平均访问次数最多==================" >> /tmp/analyze.log ;
mysqldumpslow -s ar -t 10  slow.log  >> /tmp/analyze.log ;
echo "==================执行时间最慢==================" >> /tmp/analyze.log ;
mysqldumpslow -s t -t 10 slow.log >> /tmp/analyze.log;
echo "==================执行次数最多==================" >> /tmp/analyze.log ;
mysqldumpslow -s c -t 10 slow.log >> /tmp/analyze.log



3、问题sql分析

结构分析如下：



该sql整体耗时43s

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
	pm,
	pl,
	attendance,
	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('2020-07-01', '%Y-%m-%d' ) 
	AND DATE_FORMAT( pm.planned_date, '%Y-%m-%d' ) >= DATE_FORMAT('2020-07-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
	xxxx


4、执行计划分析

查看执行计划初步判断问题在t_ocs_production_management表上走了全表扫描。



5、建索引并改写sql

建索引

create index idx_work_date on t_ocs_employee_attendace(WORK_DATE);
create index idx_planned_date on t_ocs_production_management(planned_date);

改写sql结构如下，这里建索引后用left join去关联，缩小结果集，但结果并不理想，耗时70s

explain 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 (select * from xx where STATUS = 'Y' and is_delete = 'N' and product_type IN ('M', 'N') and planned_date >= date_format('2020-07-01',  '%Y-%m-%d') and actual_production IS NOT NULL ) pm
	left join (select * from xxx where is_delete = 'N' )pl on 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 pm.planned_date >= date_format(pl.start_time, 'yyyy-mm-dd hh24:mi:ss') 
   AND (pm.planned_date<= date_format(pl.end_time, 'yyyy-mm-dd hh24:mi:ss') 
         OR pl.end_time IS NULL) 
	left join (select * from xxx where is_delete = 'N' and work_date >= date_format('2020-07-01',  '%Y-%m-%d') )	   attendance on
	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 pm.planned_date= date_format(attendance.work_date, '%Y-%m-%d') 
	 AND pm.shifts = attendance.actual_shifts 
	 left join (select * from xxx where is_delete = 'N'  and  line_status != 'NOENABLE' ) dst on
    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 pm.planned_date >= date_format(dst.start_date, 'yyyy-mm-dd hh24:mi:ss') 
   AND (pm.planned_date <= date_format(dst.stop_date, 'yyyy-mm-dd hh24:mi:ss') 
         OR dst.stop_date IS NULL) 
   AND dst.car_type = pm.product 
 GROUP BY xxx



6、最终改写sql如下

改写后的sql还是用之前的方式，但修改了date_format写法及调整了条件顺序，最终sql耗时35s，并不理想。

--考虑改成如下sql:
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
	pm,
	pl,
	attendance,
	dst 
WHERE 
   pm.org_code = pl.ascription_org_code 
	AND pm.dept_code = pl.ascription_dept_code 
	xxxx
	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 pm.planned_date>=dst.start_date
	AND dst.car_type = pm.product
	AND pm.planned_date>= DATE_FORMAT('2020-07-01', '%Y-%m-%d' ) 
  AND attendance.work_date >= DATE_FORMAT('2020-07-01', '%Y-%m-%d' ) 
	AND 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 pm.actual_production IS NOT NULL 
	AND (pm.planned_date<=pl.end_time OR pl.end_time IS NULL ) 
	AND ( pm.planned_date<=  dst.stop_date OR dst.stop_date IS NULL )
GROUP BY
	xxx




7、以上结论

优化：由全表扫描改为索引范围扫描，一开始的sql需要扫28973行，目前只需要扫1524行。
存在问题：瓶颈应该是在四表关联上，一般MySQL不建议超过三表以上关联，主要是这里不是用hash连接算法。

建议：多表关联这种情况一般会有连接条件，并且连接条件上会有索引，建议放到service层去做，最快的方式是，先查A表，得到一个小的结果集，一次rpc，再根据结果集，拼凑出B表的查询条件，去B表查到一个结果集，再一次rpc，再把结果集拉回service层，再一次rpc，然后service层做合并，3次rpc，如果用数据库的join，关联结果拉回来，一次rpc，帮你省了两次rpc，当然数据库上做关联更快，对应到数据库就是一次blk nested loop join。

8、验证猜想，在mysql8上进行测试

考虑到MySQL 8实现了用于内连接查询的 hash join 方式,大多数情况下，hash join 比之前的 Block Nested-Loop 算法在没有索引时的等值连接更加高效,故基于不同版本对比同个sql的执行效率。

测试结果：sql耗时并没有改善，执行计划也没有变化。

--查看连接方式
EXPLAIN FORMAT=TREE sql

结果如下：
-> Table scan on <temporary>
    -> Temporary table with deduplication
        -> Nested loop inner join  (cost=687.91 rows=0)
            -> Nested loop inner join  (cost=687.07 rows=0)
                -> Nested loop inner join  (cost=687.02 rows=0)
                    -> Filter: ((pm.`status` = 'Y') and (pm.is_delete = 'N') and (pm.product_type in ('M','N')) and (pm.actual_production is not null) and (pm.org_code is not null) and (pm.dept_code is not null) and (pm.factory_code is not null) and (pm.business_segment is not null) and (pm.brand is not null) and (pm.production_line is not null) and (pm.product is not null))  (cost=686.06 rows=3)
                        -> Index range scan on pm using idx_planned_date, with index condition: (pm.planned_date >= <cache>(date_format('2020-07-01','%Y-%m-%d')))  (cost=686.06 rows=1524)
                    -> Filter: ((pm.planned_date >= pl.start_time) and (pl.is_delete = 'N') and ((pm.planned_date <= pl.end_time) or (pl.end_time is null)) and (pl.org_code is not null) and (pl.dept_code is not null) and (pl.section_code is not null) and (pl.group_code is not null))  (cost=0.25 rows=0)
                        -> Index lookup on pl using t_ocs_production_lines_n1 (ascription_org_code=pm.org_code, ascription_dept_code=pm.dept_code, factory_code=pm.factory_code, Business_segment=pm.business_segment, brand=pm.brand, production_line=pm.production_line, product=pm.product), with index condition: ((pm.org_code = pl.ascription_org_code) and (pm.dept_code = pl.ascription_dept_code) and (pm.product = pl.product))  (cost=0.25 rows=1)
                -> Filter: ((dst.car_type = pm.product) and (pm.planned_date >= dst.start_date) and (dst.line_status <> 'NOENABLE') and (dst.is_delete = 'N') and ((pm.planned_date <= dst.stop_date) or (dst.stop_date is null)))  (cost=0.29 rows=0)
                    -> Index lookup on dst using t_ocs_dst_apply_change_line_n1 (org_code=pl.org_code, dept_code=pl.dept_code, section_code=pl.section_code, group_code=pl.group_code)  (cost=0.29 rows=1)
            -> Filter: ((attendance.actual_shifts = pm.shifts) and (pm.planned_date = cast(attendance.work_date as datetime)) and (attendance.work_date >= <cache>(date_format('2020-07-01','%Y-%m-%d'))) and (attendance.is_delete = 'N'))  (cost=90.39 rows=0)
                -> Index lookup on attendance using t_ocs_production_lines_n1 (org_code=pl.org_code, dept_code=pl.dept_code, section_code=pl.section_code, group_code=pl.group_code)  (cost=90.39 rows=351)

--查看执行计划
EXPLAIN  sql




可以看出里面的代价在：

Filter: ((pm.`status` = 'Y') and (pm.is_delete = 'N') and (pm.product_type in ('M','N')) and (pm.actual_production is not null) and (pm.org_code is not null) and (pm.dept_code is not null) and (pm.factory_code is not null) and (pm.business_segment is not null) and (pm.brand is not null) and (pm.production_line is not null) and (pm.product is not null)) (cost=686.06 rows=3)

-> Index range scan on pm using idx_planned_date, with index condition: (pm.planned_date >= <cache>(date_format('2020-07-01','%Y-%m-%d'))) (cost=686.06 rows=1524)

除了代码上做改进，还有其他更好的优化方法吗，欢迎下方留言一起讨论~


标签：mysqlcast 

作者:gojiuye , 分类:技术精选 , 浏览:19 , 评论:0

发表评论:

控制面板
您好，欢迎到访网站！
  查看权限
网站分类
最新留言