mysql慢查询

数据库的慢查询是影响项目性能的一大因素，对于数据库我们要优化SQL，首先要找到需要优化的SQL，这就需要我们知道sql执行时间等信息，除了使用SHOW PROFILES;外，mysql也提供了“慢查询日志”功能，用来记录查询时间超过某个设定值的SQL，这将极大程度帮助我们快速定位到症结所在，以便对症下药。

一、慢查询配置

关于慢查询日志，主要涉及三个参数：

• slow_query_log ：是否开启慢查询日志功能（必填）

• long_query_time ：超过设定值，将被视作慢查询，并记录至慢查询日志文件中（必填）

• slow_query_log_file ：慢查询日志文件（不必填），可以通过show variables like '%slow_query%';查询日志位置

打开慢查询日志有两种方式：

1. 通过命令行

不需要重启命令行，临时性的，退出mysql终端就失效。

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

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
# 记录没有使用索引的sql，默认为OFF
log_queries_not_using_indexes = ON
# mysql5.6.5版本开始新增一个参数用来表示每分钟允许记录到慢查询日志的没有使用索引的sql语句次数，防止过多语句被记录，导致日志文件不断增大
log_throttle_queries_not_using_indexes

二、慢日志查询

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

# 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

当慢查询日志不断增大时，mysqldumpslow命令可以帮助DBA更方便的格式化查询日志，比如下面这个就是得到执行时间最长的10条sql语句：

# mysqldumpslow -s al -n 10 raspberrypi-slow.log 

Reading mysql slow query log from raspberrypi-slow.log
Count: 2  Time=0.01s (0s)  Lock=2.33s (4s)  Rows_sent=0.0 (0), Rows_examined=1.0 (2), Rows_affected=1.0 (2), wangjun[wangjun]@localhost
  update tes set fromN=N where d=N

Count: 4  Time=0.08s (0s)  Lock=0.07s (0s)  Rows_sent=1.0 (4), Rows_examined=4.0 (16), Rows_affected=0.0 (0), root[root]@localhost
  SELECT count(*) FROM mysql.user WHERE user='S' and password='S' and plugin='S'

Count: 25  Time=0.02s (0s)  Lock=0.01s (0s)  Rows_sent=5.3 (133), Rows_examined=5.3 (133), Rows_affected=0.0 (0), 3users@localhost
  show variables like 'S'
 ......

mysql5.0.1开始将慢查询的日志放在mysql.slow_log表中。

> show create table mysql.slow_log;
+----------+--------------------------------------------------------------------------------------------------------+
| Table    | Create Table                                                                           
+----------+--------------------------------------------------------------------------------------------------------+
| slow_log | CREATE TABLE `slow_log` (
  `start_time` timestamp(6) NOT NULL DEFAULT current_timestamp(6) ON UPDATE current_timestamp(6),
  `user_host` mediumtext NOT NULL,
  `query_time` time(6) NOT NULL,
  `lock_time` time(6) NOT NULL,
  `rows_sent` int(11) NOT NULL,
  `rows_examined` int(11) NOT NULL,
  `db` varchar(512) NOT NULL,
  `last_insert_id` int(11) NOT NULL,
  `insert_id` int(11) NOT NULL,
  `server_id` int(10) unsigned NOT NULL,
  `sql_text` mediumtext NOT NULL,
  `thread_id` bigint(21) unsigned NOT NULL,
  `rows_affected` int(11) NOT NULL
) ENGINE=CSV DEFAULT CHARSET=utf8 COMMENT='Slow log' |

参数log_output指定了慢查询日志的输出格式，默认为file，改为TABLE就可以将慢查询日志存储到mysql.slow_log了。

> show variables like '%log_output%';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| log_output    | FILE  |
+---------------+-------+
1 row in set (0.004 sec)