记一次慢查询优化sql

2022-12-07 07:45:44

sql语句优化（慢查询日志）

最近，旧系统向新系统迁移工程刚刚结束。开发完成后，测试阶段也是好好休息了一把。接到一个需求，由于内部员工使用的网站部分功能加载时间很长，所以需要去优化系统的一些功能。大致流程如下：

部分网页查询较慢，考虑在数据库中开启慢查询，查看慢查询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。最终考虑新建一个表来记录支付表和退款表的数据

，并写为存储过程供定时任务调用（公司代码原因做存储过程，可酌情参考）。

码农公寓

sql语句优化（慢查询日志）

相关文章