记一次慢查询优化sql

sql语句优化(慢查询日志)

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

  1. 部分网页查询较慢,考虑在数据库中开启慢查询,查看慢查询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语句

  2. 分析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。最终考虑新建一个表来记录支付表和退款表的数据

    ​ ,并写为存储过程供定时任务调用(公司代码原因做存储过程,可酌情参考)。

上一篇:钱诚9.15外汇黄金期货原油行情趋势解读及美盘操作建议


下一篇:9.11黄金原油下周怎么投资操作?白银TD最新操作策略