Mysql慢查询日志

在IT行业这个发展更新速度很快的行业，只有不停止的学习，才不会被行业所淘汰。如果你是数据库学习者，那么本文《Mysql慢查询日志》就很适合你！本篇内容主要包括Mysql慢查询日志，希望对大家的知识积累有所帮助，助力实战开发！

Mysql慢查询日志

数据库的慢查询是影响项目性能的一大因素，对于数据库我们要优化SQL，首先要找到需要优化的SQL，这就需要我们知道sql执行时间等信息，除了使用

# 以下操作管理员才有权限

mysql> set global slow_query_log = ON;
Query OK, 0 rows affected (0.04 sec)

# 设置查询“超时”时间(这里为了方便日志打印，将超过0.001s的都作为慢查询)
mysql> set GLOBAL long_query_time = 0.001;
Query OK, 0 rows affected (0.00 sec)

2. 通过配置文件

需要重启mysql

# 慢日志相关配置
slow_query_log = ON
long_query_time = 0.001
slow_query_log_file = /usr/local/mysql/data/slow.log

二、慢日志查询

如果操作正确，那么在日志里面就会看到类似下面的：

# Time: 200303 14:54:38
# User@Host: wangjun[wangjun] @ localhost []
# Thread_id: 47  Schema: scujoo  QC_hit: No
# Query_time: 0.024923  Lock_time: 0.000130  Rows_sent: 3488  Rows_examined: 3488
# Rows_affected: 0  Bytes_sent: 354872
SET timestamp=1583218478;
select * from account;
/usr/sbin/mysqld, Version: 10.3.15-MariaDB-1-log (Raspbian testing-staging). started with:
Tcp port: 0  Unix socket: /run/mysqld/mysqld.sock
Time            Id Command    Argument
# Time: 200303 15:05:30
# User@Host: [root] @ localhost []
# Thread_id: 8  Schema: mysql  QC_hit: No
# Query_time: 0.001743  Lock_time: 0.000168  Rows_sent: 1  Rows_examined: 1
# Rows_affected: 0  Bytes_sent: 252
use mysql;
SET timestamp=1583219130;
show variables like 'datadir';
# User@Host: [root] @ localhost []
# Thread_id: 10  Schema:   QC_hit: No
# Query_time: 0.007002  Lock_time: 0.000238  Rows_sent: 36  Rows_examined: 69
# Rows_affected: 0  Bytes_sent: 2391
SET timestamp=1583219130;
select concat('select count(*) into @discard from `',
                    TABLE_SCHEMA, '`.`', TABLE_NAME, '`') 
      from information_schema.TABLES where TABLE_SCHEMA'INFORMATION_SCHEMA' and TABLE_SCHEMA'PERFORMANCE_SCHEMA' and ( ENGINE='MyISAM' or ENGINE='Aria' );
# Time: 200303 15:06:41

好了，本文到此结束，带大家了解了《Mysql慢查询日志》，希望本文对你有所帮助！关注golang学习网公众号，给大家分享更多数据库知识！