MySQL进阶--数据库慢查询日志

慢查询日志

（一）慢查询日志概念

MySQL的慢查询日志是MySQL提供的一种日志记录，它用来记录在MySQL中响应时间超过阈值的语句，具体指运行时间超过long_query_time值的SQL，则会被记录到慢查询日志中。long_query_time的默认值为10，意思是运行10S以上的语句。默认情况下，Mysql数据库并不启动慢查询日志，需要我们手动来设置这个参数，当然，如果不是调优需要的话，一般不建议启动该参数，因为开启慢查询日志会或多或少带来一定的性能影响。

（二）慢查询日志相关参数

MySQL 慢查询的相关参数解释：

slow_query_log ：是否开启慢查询日志，1表示开启，0表示关闭。

slow-query-log-file：（5.6及以上版本）MySQL数据库慢查询日志存储路径。可以不设置该参数，系统则会默认给一个缺省的文件host_name-slow.log

long_query_time ：慢查询阈值，当查询时间多于设定的阈值时，记录日志。

log_queries_not_using_indexes：未使用索引的查询也被记录到慢查询日志中（可选项）。

log_output：日志存储方式。

· log_output=‘FILE’表示将日志存入文件，默认值是’FILE’。

· log_output='TABLE’表示将日志存入数据库，这样日志信息就会被写入到mysql.slow_log表中。

MySQL数据库支持同时两种日志存储方式，配置的时候以逗号隔开即可，如：log_output=‘FILE,TABLE’。日志记录到系统的专用日志表中，要比记录到文件耗费更多的系统资源，因此对于需要启用慢查询日志，又需要能够获得更高的系统性能，那么建议优先记录到文件。

（三）慢查询日志配置

默认情况下slow_query_log的值为OFF，表示慢查询日志是禁用的：

查看慢查询日志配置情况：

mysql> show variables like '%slow_query_log%';

+---------------------+------------------------------------------+
| Variable_name       | Value                                    |
+---------------------+------------------------------------------+
| slow_query_log      | OFF                                      |
| slow_query_log_file | /usr/local/mysql/data/localhost-slow.log |
+---------------------+------------------------------------------+
2 rows in set (0.00 sec)

开启慢查询日志

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

mysql> show variables like '%slow_query_log%';
+---------------------+------------------------------------------+
| Variable_name       | Value                                    |
+---------------------+------------------------------------------+
| slow_query_log      | ON                                       |
| slow_query_log_file | /usr/local/mysql/data/localhost-slow.log |
+---------------------+------------------------------------------+
2 rows in set (0.00 sec)

-- 注意： 如果找不到慢查询日志的文件 可以自己设置
SET GLOBAL slow_query_log_file='E://demo/slow_query_log.log'; 
-- 注意路径分隔符 把\ 换成  /  避免被转义
-- 设置好文件路径后，如果没有慢查询语句  slow_query_log.log 文件不存在，当有慢查询语句需要被记录是，会自动创建这个文件

那么开启了慢查询日志后，什么样的SQL才会记录到慢查询日志里面呢？ 这个是由参数long_query_time控制，默认情况下long_query_time的值为10秒，可以使用命令修改。关于运行时间正好等于long_query_time的情况，并不会被记录下来。

也就是说，在mysql源码里是判断大于long_query_time，而非大于等于。如果记录到表里面，只会记录整数部分，不会记录微秒部分。

mysql> show variables like 'long_query_time%';
+-----------------+-----------+
| Variable_name   | Value     |
+-----------------+-----------+
| long_query_time | 10.000000 |
+-----------------+-----------+
1 row in set (0.00 sec)

设置慢查询时间

mysql> set global long_query_time=4;
Query OK, 0 rows affected (0.00 sec)

查看慢查询日志时间

mysql> show variables like 'long_query_time%';
+-----------------+-----------+
| Variable_name   | Value     |
+-----------------+-----------+
| long_query_time | 10.000000 |
+-----------------+-----------+
1 row in set (0.00 sec)

注意：使用命令 set global long_query_time=4修改后，需要重新连接或新开一个会话才能看到修改值。

用show variables like 'long_query_time’查看是当前会话的变量值。

你也可以不用重新连接会话，而是用show global variables like ‘long_query_time’;

mysql> show global variables like 'long_query_time';
+-----------------+----------+
| Variable_name   | Value    |
+-----------------+----------+
| long_query_time | 4.000000 |
+-----------------+----------+
1 row in set (0.00 sec)

在MySQL里面执行下面SQL语句，然后我们去检查对应的慢查询日志，就会发现类似下面这样的信息。

mysql> select sleep(5);
+----------+
| sleep(5) |
+----------+
|        0 |
+----------+
1 row in set (5.00 sec)

rted with:
Tcp port: 3306  Unix socket: /tmp/mysql.sock
Time                 Id Command    Argument
# Time: 2021-05-08T22:20:49.812037Z
# User@Host: root[root] @ localhost []  Id:    14
# Query_time: 5.000352  Lock_time: 0.000000 Rows_sent: 1  Rows_examined: 0
use demo;
SET timestamp=1620512444;
select sleep(5);

查看日志存储方式

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

log_output 参数是指定日志的存储方式。log_output=‘FILE’表示将日志存入文件，默认值是’FILE’。

log_output='TABLE’表示将日志存入数据库，这样日志信息就会被写入到mysql.slow_log表中。

MySQL数据库支持同时两种日志存储方式，配置的时候以逗号隔开即可，

log_output=‘FILE,TABLE’。日志记录到系统的专用日志表中，要比记录到文件耗费更多的系统资源，因此对于需要启用慢查询日志，又需要能够获得更高的系统性能，那么建议优先记录到文件。

mysql> set global log_output='TABLE';
Query OK, 0 rows affected (0.00 sec)

mysql> select * from mysql.slow_log;

慢查询日志使用流程：

​ 1.查看慢日志是否开启，和查看日志的存储路径

​ 2.打开慢日志

​ 3.设置日志文件的记录阈值 退出链接

​ 4. 进入数据库 执行一个慢查询语句，查看我们的日志文件

（四）查看和分析慢查询日志情况

获取执行计划 EXPLAIN select * from XXX

mysql> explain select * from employee\G;
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE    
        table: employee
   partitions: NULL
         type: ALL
possible_keys: NULL
          key: NULL
      key_len: NULL
          ref: NULL
         rows: 108
     filtered: 100.00
        Extra: NULL
1 row in set, 1 warning (0.01 sec)

id列：SELECT识别符。这是SELECT的查询序列号

select_type列：查询的类型
· simple 简单SELECT   不包含子查询或者 UNION查询
· primary 最外面的SELECT 查询中如果包含任何子查询,最外层的标记为primary.
· subquery 子查询 子查询中的第一个SELECT

table列：输出表的名称

type 列 mysql找到需要的数据行的方式（性能从高到低）
· system (性能高)：const连接类型,当查询表只有一行时。
· const：表中值有且只有一行匹配,或者利用where 查询某个常量的值,主键或唯一索引查询是效率最高的方式
		
· eq_ref：唯一索引或主键索引查找,对每个索引键,表中只有一条记录与之匹配
· ref：非唯一索引查找,返回匹配某个单独值的所有行 
· ref_or_null：类似于ref 增加了null值的查询
· index_merge：索引合并
· range：索引范围素描(between , > , <)
		在where语句中出现了between或者“>”这种符号的时候会出现
		这种范围扫描索引扫描比全表扫描要好，因为它只需要开始于索引的某一点，而结束语另一点，不用扫描全部索引
· index：先读索引，再读实际的行，按索引顺序去扫描，要承担按照索引次序读取整张表的开销
		order by id，id是这个表的索引，但是因为没有在where中出现任何的索引列，所以它也只是索引顺去扫描了全表
· all (性能最低)：全表扫描 没有用到任何的index，mysql就是从头到尾把整个表遍历一边，找到所需要的数据行。效率是最差的

key列
· possible_keys：可能会被使用到的索引
· keys：会被使用到的索引
· key_len 实际使用索引的最大长度 (字节)
· NULL 没有使用索引

ref列:列出哪些列被用于索引查找

rows列:预估扫描行数
· 如果使用全表扫描，那么rows就代表需要扫描的行数
· 如果使用索引，那么rows就代表预计扫描的行数

filtered列:返回行数与扫描行数的百分比(越高 就 性能越高)

extra列 包含MySQL解决查询的详细信息
· using filesort 常见使用order by 或者 group by 查找，
		说明mysql会对数据使用一个外部的索引排序，而不是按照表内的索引顺序进行读取。
· using index 使用了覆盖索引(直接通过索引获取数据,不访问表)，避免访问了表的数据行
· using temporary 查询结果进行排序的时候使用了一张临时表
· using where 使用了where 
	这说明服务器在存储引擎收到行后将进行过滤。

问：怎么优化数据库查询语句

通过慢查询日志定位查询慢的语句

explain 去分析语句

通分析结果，进行优化

​ 加索引

​ 是否是sql与编写有问题，针对吧sql编写进行优化