mysql慢日志查询及处理
三丰 soft张三丰
慢日志查询作用
慢日志查询的主要功能就是，记录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) {
}
}
效果展示
