mysql慢日志查詢及處理

慢日志查詢作用

慢日志查詢的主要功能就是，記錄sql語句中超過設定的時間門檻值的查詢語句。例如，一條查詢sql語句，我們設定的門檻值為1s，當這條查詢語句的執行時間超過了1s，則将被寫入到慢查詢配置的日志中. 慢查詢主要是為了我們做sql語句的優化功能.

慢日志查詢配置項說明

打開mysql，通過以下指令檢視相關配置：

mysql> show variables like '%query%';
+------------------------------+--------------------------------------------------+
| Variable_name                | Value                                            |
+------------------------------+--------------------------------------------------+
| binlog_rows_query_log_events | OFF                                              |
| ft_query_expansion_limit     | 20                                               |
| have_query_cache             | NO                                               |
| long_query_time              | 10.000000                                        |
| query_alloc_block_size       | 8192                                             |
| query_prealloc_size          | 8192                                             |
| slow_query_log               | OFF                                              |
| slow_query_log_file          | /usr/local/mysql/data/seandeMacBook-Pro-slow.log |
+------------------------------+--------------------------------------------------+
8 rows in set (0.00 sec)      

其中需要關注的配置項為：

1. slow_query_log 該配置項是決定是否開啟慢日志查詢功能，配置的值有ON或者OFF.
2. slow_query_log_file 該配置項是慢日志查詢的記錄檔案,需要手動建立.
3. long_query_time 該配置項是設定慢日志查詢的時間門檻值，當超過這個門檻值時，慢日志才會被記錄.配置的值有0(任何的sql語句都記錄下來)，或者>0(具體的門檻值).該配置項是以秒為機關的，并且可以設定為小數.
4. log-queries-not-using-indexes 該配置項是為了記錄未使用到索引的sql語句.

如何配置慢日志查詢

配置慢查詢功能的方式有兩種，一種是使用mysql的配置檔案配置,另外一種是使用mysql指令配置.這裡建議使用配置檔案配置，因為在指令配置的過程中發現有時候配置項在set指令的時候是成功了，但是查詢還是沒設定。

1、配置檔案配置

// 找到[mysqld],在其下面添加如下代碼即可.
slow_query_log=ON
slow_query_log_file=/usr/local/mysql/var/localhost-slow.log
long_query_time=0
log-queries-not-using-indexes = 1
// 配置好後，重新開機mysql服務      

2、使用指令配置

// 這裡就簡單些一個配置項就行了，其他的配置項均按照此方法配置
mysql> set slow_query_log=ON;
// 如果執行失敗，可能是由于沒有添加global


mysql> set global slow_query_log=ON;      

配置好之後，檢視mysql慢查詢日志是否配置成功：

mysql> show variables like '%query%';
+------------------------------+--------------------------------------------------+
| Variable_name                | Value                                            |
+------------------------------+--------------------------------------------------+
| binlog_rows_query_log_events | OFF                                              |
| ft_query_expansion_limit     | 20                                               |
| have_query_cache             | NO                                               |
| long_query_time              | 10.000000                                        |
| query_alloc_block_size       | 8192                                             |
| query_prealloc_size          | 8192                                             |
| slow_query_log               | ON                                               |
| slow_query_log_file          | /usr/local/mysql/data/seandeMacBook-Pro-slow.log |
+------------------------------+--------------------------------------------------+
8 rows in set (0.01 sec)      

在mysql中操作有時候跟上下文業務并不相關，可以結合spring boot和mybaties進行。

服務配置

# slow-sql
# 基于interceptor
slow-sql-interceptor.enabled=true
# 設定逾時時間ms
slow-sql.timeout=10
# 日志是否插入資料庫
slow-sql.insertDB=true      

日志實體類

@Data
public class SqlLog {
    // 主鍵
    private Long id;
    // 調用方法名稱
    private String methodName;
    // 參數清單
    private String params;
    // 方法執行時間
    private Long runTime;
    // 執行SQL
    private String executeSql;
    // SQL類型
    private String sqlType;
    // 記錄建立時間
    private Date createTime;
}      

SQL攔截器

@Intercepts({@Signature(type = StatementHandler.class, method = "query", args = {Statement.class, ResultHandler.class}),
             @Signature(type = StatementHandler.class, method = "update", args = {Statement.class}),
             @Signature(type = StatementHandler.class, method = "batch", args = {Statement.class})})
@Component
@Slf4j
// 配置檔案讀取是否啟用此配置
@ConditionalOnProperty(prefix = "slow-sql-interceptor", name = "enabled", havingValue = "true")
public class SqlExecuteTimeInterceptor implements Interceptor {


    // 日志記錄mapper
    @Autowired
    private LogMapper logMapper;


    // 是否插入資料庫辨別
    @Value("${slow-sql.insertDB}")
    private boolean isInsertDB;


    // 逾時時間
    @Value("${slow-sql.timeout}")
    private Long timeout;


    /**
     * @Author thailandking
     * @Date 2020/2/16 16:44
     * @LastEditors thailandking
     * @LastEditTime 2020/2/16 16:44
     * @Description sql攔截器
     */
    @Override
    public Object intercept(Invocation invocation) throws Throwable {
        Object target = invocation.getTarget();
        long begin = System.currentTimeMillis();
        StatementHandler statementHandler = (StatementHandler) target;
        try {
            return invocation.proceed();
        } finally {
            long end = System.currentTimeMillis();
            // 判斷逾時
            if ((end - begin) > timeout) {
                // 方法名稱、類型
                MetaObject metaObject = MetaObject.forObject(statementHandler, SystemMetaObject.DEFAULT_OBJECT_FACTORY, SystemMetaObject.DEFAULT_OBJECT_WRAPPER_FACTORY, new DefaultReflectorFactory());
                MappedStatement mappedStatement = (MappedStatement) metaObject.getValue("delegate.mappedStatement");
                String methodName = mappedStatement.getId();
                String sqlType = mappedStatement.getSqlCommandType().toString();
                // 控制台列印日志
                BoundSql boundSql = statementHandler.getBoundSql();
                String sql = boundSql.getSql();
                // 參數map
                Object parameterObject = boundSql.getParameterObject();
                // 參數清單
                List<ParameterMapping> parameterMappingList = boundSql.getParameterMappings();
                // 格式化sql語句，去除換行符，替換參數
                sql = formatSQL(sql, parameterObject, parameterMappingList);
                log.error("執行 SQL：[ {} ], 執行耗時[ {} ms ]", sql, (end - begin));
                // 判斷是否寫入資料庫
                if (isInsertDB) {
                    // 轉換參數map為String
                    String params = changeSqlMapParams(parameterObject, parameterMappingList);
                    SqlLog log = new SqlLog();
                    log.setMethodName(methodName);
                    log.setParams(params);
                    log.setRunTime((end - begin));
                    log.setExecuteSql(sql);
                    log.setSqlType(sqlType);
                    logMapper.insertSqlLog(log);
                }
            }
        }
    }


    private String changeSqlMapParams(Object parameterObject, List<ParameterMapping> parameterMappingList) {
        StringBuilder sb = new StringBuilder();
        Map<String, Object> params = (Map<String, Object>) parameterObject;
        for (ParameterMapping pm : parameterMappingList) {
            if (pm.getMode().name().equals("IN")) {
                String param = ",[" + params.get(pm.getProperty()).toString() + "]";
                sb.append(param);
            }
        }
        return sb.length() > 0 ? sb.substring(1) : null;
    }


    private String formatSQL(String sql, Object parameterObject, List<ParameterMapping> parameterMappingList) {
        if (sql == null || sql.length() == 0) {
            return "";
        }
        // 去除換行符
        sql = sql.replaceAll("[\\s\n ]+", "  ");
        // 替換參數
        Map<String, Object> params = (Map<String, Object>) parameterObject;
        for (ParameterMapping pm : parameterMappingList) {
            if (pm.getMode().name().equals("IN")) {
                sql = sql.replaceFirst("\\?", params.get(pm.getProperty()).toString());
            }
        }
        return sql;
    }


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


    @Override
    public void setProperties(Properties properties) {


    }
}      

效果展示