最近,旧系统向新系统迁移工程刚刚结束。开发完成后,测试阶段也是好好休息了一把。接到一个需求,由于内部员工使用的网站部分功能加载时间很长,所以需要去优化系统的一些功能。大致流程如下:
部分网页查询较慢,考虑在数据库中开启慢查询,查看慢查询sql日志(写慢sql开启命令,各命令参数)
慢查询是mysql提供的,一种用来监控执行时间超过阈值的sql语句。本质上是一种日志记录。可以将日志记录到文件种,也支持将日志记录到表中。但不需要查看慢查询sql的时候不建议开启,会对性能有一定影响。
配置:
临时配置:重启数据库后会将慢查询恢复成默认状态
show variables like '%slow_query_log%';(查看慢查询开启状态)
show variables like '%long_query_time%'; (查看慢查询时间配置)
show variables like '%query%';(查看慢查询日志更全参数)
set global slow_query_log='ON';(设置慢查询开启状态)
set global slow_query_log_file='/var/lib/mysql/instance-1-slow.log';(给输出的日志设置路径)
set global long_query_time=2;(设置慢查询时间,执行时间大于等于该参数的sql会被记录到日志中。时间默认为10,可根据需求进行设置)
ps:如果设置完以后没有变化,可以尝试重连数据库。
永久配置:
在mysql的配置文件中进行修改
/etc/mysql/conf.d/mysql.cnf
[mysqld]
slow_query_log = ON
slow_query_log_file = /var/lib/mysql/instance-1-slow.log
long_query_time = 2(时间默认为10,可根据需求进行设置)
ps:如果设置完以后没有变化,可以尝试重连数据库。
参数:
start_time :开始时间,通过该参数,配合系统出问题的时间。
long_query_time:该sql 的执行时间
rows_sent:发送的结果数
rows_examined:查询扫过的行数
sql语句
分析sql,从哪几方面考虑进行优化
SELECT IFNULL(SUM(total.cityPayMoneySum),0) AS cityPayMoneySum,total.cityCode as cityCode FROM ( SELECT pay_sum_money AS cityPayMoneySum , city_code AS cityCode FROM TD_R_DEPT_GOODS_DAY_TRADE_SUM inner join TD_S_DEPARTMENT on dept_code = TD_S_DEPARTMENT.code WHERE TD_S_DEPARTMENT.company_code = 'TDSCPY15416539690705' AND TD_S_DEPARTMENT.valid = 1 UNION ALL SELECT -refund_sum_money AS cityPayMoneySum, city_code AS cityCode FROM TD_R_DEPT_GOODS_DAY_TRADE_REFUND_SUM inner join TD_S_DEPARTMENT on dept_code = TD_S_DEPARTMENT.code WHERE TD_S_DEPARTMENT.company_code = 'TDSCPY15416539690705' AND TD_S_DEPARTMENT.valid = 1) AS total Group BY total.cityCode; sql的含义:从每日支付表(TD_R_DEPT_GOODS_DAY_TRADE_SUM)中查询交易额和城市code,从每日退款表(TD_R_DEPT_GOODS_DAY_TRADE_REFUND_SUM)中查询退款额和城市code,然后合并结果集。
根据网上查到的一些优化sql的资料,大多数是建立索引,替换某些关键字之类的操作。在我这次优化的语句中并不适用
方案一:将长复杂的sql拆分为多个短且易读的sql,拆分子查询(子查询消耗性能)。
方案二:使用left join union right join 来代替mysql中没有的full join(mysql不支持full join 。通过left join union right join 来实现full join)
方案三:将每日交易额进行汇总,改为每月交易汇总,这样每一个月就会少三十条数据。两百万条数据会少很多
方案四:最终在线上查看执行时间是union all导致的(早就该在线上看的),将3s的查询增加到了10s。最终考虑新建一个表来记录支付表和退款表的数据
,并写为存储过程供定时任务调用(公司代码原因做存储过程,可酌情参考)。