mysql设置慢文件_mysql慢日志查询及处理

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 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 parameterMappingList) {

StringBuilder sb = new StringBuilder();

Map params = (Map) 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 parameterMappingList) {

if (sql == null || sql.length() == 0) {

return "";

}

// 去除换行符

sql = sql.replaceAll("[\\s\n ]+", " ");

// 替换参数

Map params = (Map) 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) {

}

}

效果展示