MySQL 慢 sql 监控怎么做

1、思路

之前用 mysql 一直没有考虑到这点，mysql 慢 sql 监控是很重要的，它能帮我们梳理我们的业务 sql 到底是哪里处了问题，那么慢 sql 监控怎么做呢？

有两种思路来实现：

• 1. 在应用层做，比如我们的系统使用 mybatis，则可以使用 mybatis 的拦截器，在想要监控的 sql 前后进行即时，超时后打 error 日志。如果日志接入日志平台，那么日志平台可以配置报警邮件，直接发邮件提醒开发。
• 2. 在 mysql 层做，mysql 开启慢日志，记录慢查询 sql。可以读取慢查询日志，分析哪些 sql 属于慢查询，然后发邮件提醒等。这等于做了一个慢 sql 预警系统，一般大公司都会有这样的一套系统（https://github.com/wxisme/slowsql-monitor 类似于这种）。

开启 mysql 慢查询日志的方法如下（https://github.com/wxisme/slowsql-monitor 可以参照这个工程）：

1.First configure the MySQL slow query log,you need to log in to the mysql client. Turn on MySQL slow query log switch:
show variables like 'slow_query_log';
set global slow_query_log='ON';

2.Set the long query time in seconds:
show variables like 'long_query_time';
set global long_query_time=1;

3.Set the log queries not using indexes:
show variables like 'log_queries_not_using_indexes';
set global log_queries_not_using_indexes='ON';

4.show slow log location:
show variables like 'slow_query_log_file';

mysql 的 slowlog 的内容如下：

/usr/local/mysql/bin/mysqld, Version: 5.7.28-log (MySQL Community Server (GPL)). started with:
Tcp port: 3306  Unix socket: /tmp/mysql.sock
Time                 Id Command    Argument
# Time: 2021-04-10T07:22:09.260226Z
# User@Host: root[root] @ localhost [127.0.0.1]  Id:  7405
# Query_time: 0.247682  Lock_time: 0.025775 Rows_sent: 103  Rows_examined: 103
use deepwise;
SET timestamp=1618039329;
/* ApplicationName=DataGrip 2018.3.1 */ SELECT t.* FROM deepwise.DW_AI_RESULT t
LIMIT 501;
# Time: 2021-04-10T07:22:37.999494Z
# User@Host: root[root] @ localhost [127.0.0.1]  Id:  7406
# Query_time: 0.018695  Lock_time: 0.017633 Rows_sent: 0  Rows_examined: 0
SET timestamp=1618039357;
/* ApplicationName=DataGrip 2018.3.1 */ SELECT t.* FROM deepwise.dw_ai_result_process t
LIMIT 501;
# Time: 2021-04-10T07:23:04.504722Z
# User@Host: root[root] @ localhost [127.0.0.1]  Id:  7404
# Query_time: 0.008424  Lock_time: 0.002104 Rows_sent: 0  Rows_examined: 103
SET timestamp=1618039384;
/* ApplicationName=DataGrip 2018.3.1 */ select * from DW_AI_RESULT where PATIENT_ID like '%4468%';

剩下的就是解析这个 log 日志，然后把数据给前端进行页面渲染什么的。

2、mybatis 拦截器方案

package com.best.ecboss.util;

import org.apache.commons.logging.Log;
import org.apache.commons.logging.LogFactory;
import org.apache.ibatis.executor.Executor;
import org.apache.ibatis.mapping.MappedStatement;
import org.apache.ibatis.plugin.*;
import org.apache.ibatis.session.ResultHandler;
import org.apache.ibatis.session.RowBounds;
import org.springframework.beans.BeanUtils;

import java.beans.PropertyDescriptor;
import java.util.Date;
import java.util.Map;
import java.util.Properties;

@Intercepts(
        @Signature(type = Executor.class, method = "query",
                args = {MappedStatement.class, Object.class, RowBounds.class, ResultHandler.class})
)
public class QueryDaoMybatisInterceptor implements Interceptor {

    private final Log logger = LogFactory.getLog(QueryDaoMybatisInterceptor.class);

    @Override
    public Object intercept(Invocation invocation) throws Throwable {
        Object[] args = invocation.getArgs(); //方法参数
        Date begin = new Date();
        Object ret = invocation.proceed();
        Date end = new Date();
        try {
            MappedStatement mappedStatement = (MappedStatement) args[0];
            // 方法参数
            Object params = args[1];
            // mapper 中的方法名称，如 “com.xushu.mysql.slowsql.dao.AccountMapper.selectList”
            String mapperId = mappedStatement.getId();
            // 获取 sql 语句，并格式化 sql，将很多空格替换成一个空格
            String sql = formatSql(mappedStatement.getBoundSql(params).getSql());

            log(mapperId, sql, params, begin, end);
        } catch (Exception e) {
        }
        return ret;
    }

    private String formatSql(String sql) {
        return sql.replaceAll("\\s+", " ");
    }

    private void log(String statementId, String sql, Object params, Date begin, Date end) {
        // 这边可以记录自己想要的查询方法，比如 “selectList”
        if (statementId == null || !statementId.contains(".selectList")) {
            return;
        }
        long ms = end.getTime() - begin.getTime();
        String paramStr = parseParams(params);
        if (ms > 10000) {
            logger.error("ms:[" + ms + "],sql:" + sql + ",param:[" + paramStr + "]");
        } else if (ms > 1000) {
            logger.warn("ms:[" + ms + "],sql:" + sql + ",param:[" + paramStr + "]");
        } else {
            logger.info("ms:[" + ms + "],sql:" + sql + ",param:[" + paramStr + "]");
        }
    }

    // 格式化传参
    private String parseParams(Object params) {
        StringBuilder sb = new StringBuilder();
        try {
            if (params instanceof Map) {
                Map map = (Map) params;
                map.forEach((k, v) -> {
                    sb.append(",").append(k).append(":").append(v);
                });
            } else if (BeanUtils.isSimpleProperty(params.getClass())) {
                sb.append(",").append(params.getClass().getSimpleName()).append(":").append(params).append(",");
            } else {
                // 反射getter属性
                PropertyDescriptor[] pds = BeanUtils.getPropertyDescriptors(params.getClass());
                for (PropertyDescriptor pd : pds) {
                    if (pd.getReadMethod() != null && !"class".equals(pd.getName())) {
                        String name = pd.getName();
                        Object value = null;
                        value = pd.getReadMethod().invoke(params);
                        sb.append(",").append(name).append(":").append(value);
                    }
                }
            }
        } catch (Exception e) {
        }
        if (sb.length() > 0) {
            return sb.toString().substring(1);
        }
        return "";
    }

    @Override
    public Object plugin(Object o) {
        return Plugin.wrap(o, this);
    }

    @Override
    public void setProperties(Properties properties) {

    }
}