MySQL 慢查询，一口从天而降的锅！，java 程序开发基础彭政答案

极客good

关注

发布于: 刚刚

一、慢查询配置

======================================================================

1-1、开启慢查询

MySQL 支持通过

1、输入命令开启慢查询（临时），在 MySQL 服务重启后会自动关闭；
2、配置 my.cnf（windows 是 my.ini）系统文件开启，修改配置文件是持久化开启慢查询的方式。

方式一：通过命令开启慢查询

步骤 1、查询 slow_query_log 查看是否已开启慢查询日志：

show variables like '%slow_query_log%';

mysql> show variables like '%slow_query_log%';

+---------------------+-----------------------------------+

| Variable_name | Value |

+---------------------+-----------------------------------+

| slow_query_log | OFF |

| slow_query_log_file | /var/lib/mysql/localhost-slow.log |

+---------------------+-----------------------------------+

2 rows in set (0.01 sec)

步骤 2、开启慢查询命令：

set global slow_query_log='ON';

步骤 3、指定记录慢查询日志 SQL 执行时间得阈值（long_query_time 单位：秒，默认 10 秒）

如下我设置成了 1 秒，执行时间超过 1 秒的 SQL 将记录到慢查询日志中

set global long_query_time=1;

步骤 4、查询 “慢查询日志文件存放位置”

show variables like '%slow_query_log_file%';

mysql> show variables like '%slow_query_log_file%';

+---------------------+-----------------------------------+

| Variable_name | Value |

+---------------------+-----------------------------------+

| slow_query_log_file | /var/lib/mysql/localhost-slow.log |

+---------------------+-----------------------------------+

1 row in set (0.01 sec)

slow_query_log_file 指定慢查询日志的存储路径及文件（默认和数据文件放一起）

步骤 5、核对慢查询开启状态

需要退出当前 MySQL 终端，重新登录即可刷新；

配置了慢查询后，它会记录以下符合条件的 SQL：

查询语句

数据修改语句

已经回滚的 SQL

方式二：通过配置 my.cnf（windows 是 my.ini）系统文件开启

（版本：MySQL5.5 及以上）

在 my.cnf 文件的[mysqld]下增加如下配置开启慢查询，如下图

开启慢查询功能

slow_query_log=ON

指定记录慢查询日志 SQL 执行时间得阈值

long_query_time=1

选填，默认数据文件路径

slow_query_log_file=/var/lib/mysql/localhost-slow.log

重启数据库后即持久化开启慢查询，查询验证如下：

mysql> show variables like '%query%';

+------------------------------+-----------------------------------+

| Variable_name | Value |

+------------------------------+-----------------------------------+

| have_query_cache | YES |

| long_query_time | 1.000000 |

| slow_query_log | ON |

| slow_query_log_file | /var/lib/mysql/localhost-slow.log |

+------------------------------+-----------------------------------+

6 rows in set (0.01 sec)

1-2、慢查询日志介绍

如上图，是执行时间超过 1 秒的 SQL 语句（测试）

第一行：记录时间
第二行：用户名、用户的 IP 信息、线程 ID 号
第三行：执行花费的时间【单位：秒】、执行获得锁的时间、获得的结果行数、扫描的数据行数
第四行：这 SQL 执行的时间戳
第五行：具体的 SQL 语句

二、Explain 分析慢查询 SQL

================================================================================

分析 mysql 慢查询日志 ,利用 explain 关键字可以模拟优化器执行 SQL 查询语句，来分析 sql 慢查询语句，下面我们的测试表是一张 137w 数据的 app 信息表，我们来举例分析一下；

SQL 示例如下：

-- 1.185s

SELECT * from vio_basic_domain_info where app_name like '%陈哈哈 %' ;

这是一条普通的模糊查询语句，查询耗时：1.185s，查到了 148 条数据；

我们用 Explain 分析结果如下表，根据表信息可知：该SQL没有用到字段app_name上的索引，查询类型是全表扫描，扫描行数137w。

mysql> EXPLAIN SELECT * from vio_basic_domain_info where app_name like '%陈哈哈 %' ;

+----+-------------+-----------------------+------------+------+---------------+------+---------+------+---------+----------+-------------+

+----+-------------+-----------------------+------------+------+---------------+------+---------+------+---------+----------+-------------+

+----+-------------+-----------------------+------------+------+---------------+------+---------+------+---------+----------+-------------+

1 row in set, 1 warning (0.00 sec)

当这条 SQL 使用到索引时，SQL 如下：查询耗时：0.156s，查到 141 条数据

-- 0.156s

SELECT * from vio_basic_domain_info where app_name like '陈哈哈 %' ;

Explain 分析结果如下表；根据表信息可知：该 SQL用到了idx_app_name索引，查询类型是索引范围查询，扫描行数141行。由于查询的列不全在索引中（select *），因此回表了一次，取了其他列的数据。

mysql> EXPLAIN SELECT * from vio_basic_domain_info where app_name like '陈哈哈 %' ;

+----+-------------+-----------------------+------------+-------+---------------+--------------+---------+------+------+----------+-----------------------+

+----+-------------+-----------------------+------------+-------+---------------+--------------+---------+------+------+----------+-----------------------+

+----+-------------+-----------------------+------------+-------+---------------+--------------+---------+------+------+----------+-----------------------+

1 row in set, 1 warning (0.00 sec)

当这条 SQL使用到覆盖索引时，SQL 如下：查询耗时：0.091s，查到 141 条数据

-- 0.091s

SELECT app_name from vio_basic_domain_info where app_name like '陈哈哈 %' ;

Explain 分析结果如下表；根据表信息可知：和上面的 SQL 一样使用到了索引，由于查询列就包含在索引列中，又省去了 0.06s 的回表时间。

mysql> EXPLAIN SELECT app_name from vio_basic_domain_info where app_name like '陈哈哈 %' ;

+----+-------------+-----------------------+------------+-------+---------------+--------------+---------+------+------+----------+--------------------------+

+----+-------------+-----------------------+------------+-------+---------------+--------------+---------+------+------+----------+--------------------------+

+----+----

【一线大厂Java面试题解析+核心总结学习笔记+最新架构讲解视频+实战项目源码讲义】
浏览器打开：qq.cn.hn/FTf 免费领取

复制代码

---------+-----------------------+------------+-------+---------------+--------------+---------+------+------+----------+--------------------------+

1 row in set, 1 warning (0.00 sec)

那么是如何通过 EXPLAIN 解析结果分析 SQL 的呢？各列属性又代表着什么？一起往下看。

2-1、各列属性的简介：

id:SELECT 的查询序列号，体现执行优先级，如果是子查询，id 的序号会递增，id 值越大优先级越高，越先被执行
select_type:表示查询的类型。
table:输出结果集的表，如设置了别名，也会显示
partitions:匹配的分区
type:对表的访问方式
possible_keys:表示查询时，可能使用的索引
key:表示实际使用的索引
key_len:索引字段的长度
ref:列与索引的比较
rows:扫描出的行数(估算的行数)
filtered:按表条件过滤的行百分比
Extra:执行情况的描述和说明

以上标星的几类是我们优化慢查询时常用到的

2-2、慢查询分析常用到的属性

1、type：

对表访问方式，表示 MySQL 在表中找到所需行的方式，又称“访问类型”。

存在的类型有： ALL、index、range、 ref、eq_ref、const、system、NULL（从左到右，性能从低到高），介绍三个咱们天天见到的：

ALL：(Full Table Scan) MySQL 将遍历全表以找到匹配的行，常说的全表扫描

index: (Full Index Scan) index 与 ALL 区别为 index 类型只遍历索引树

range:只检索给定范围的行，使用一个索引来选择行

2、key

key 列显示了 SQL 实际使用索引，通常是 possible_keys 列中的索引之一，MySQL 优化器一般会通过计算扫描行数来选择更适合的索引，如果没有选择索引，则返回 NULL。当然，MySQL 优化器存在选择索引错误的情况，可以通过修改 SQL 强制 MySQL“使用或忽视某个索引”。

强制使用一个索引：FORCE INDEX (index_name)、USE INDEX (index_name)
强制忽略一个索引：IGNORE INDEX (index_name)

3、rows

rows 是 MySQL 估计为了找到所需的行而要读取（扫描）的行数，可能不精确。

4、Extra

这一列显示一些额外信息，很重要。

Using index

查询的列被索引覆盖，并且 where 筛选条件是索引的是前导列，Extra 中为 Using index。意味着通过索引查找就能直接找到符合条件的数据，无须回表。

注：前导列一般指联合索引中的第一列或“前几列”，以及单列索引的情况；这里为了方便理解我统称为前导列。

Using where

说明 MySQL 服务器将在存储引擎检索行后再进行过滤；即没有用到索引，回表查询。

可能的原因：

查询的列未被索引覆盖；
where 筛选条件非索引的前导列或无法正确使用到索引；

Using temporary

这意味着 MySQL 在对查询结果排序时会使用一个临时表。

Using filesort

说明 MySQL 会对结果使用一个外部索引排序，而不是按索引次序从表里读取行。

Using index condition

查询的列不全在索引中，where 条件中是一个前导列的范围

Using where;Using index

查询的列被索引覆盖，并且 where 筛选条件是索引列之一，但不是索引的前导列或出现了其他影响直接使用索引的情况（如存在范围筛选条件等），Extra 中为 Using where; Using index，意味着无法直接通过索引查找来查询到符合条件的数据，影响并不大。

三、一些慢查询优化经验分享

============================================================================

3-1、优化 LIMIT 分页

在系统中需要分页的操作通常会使用 limit 加上偏移量的方法实现，同时加上合适的 order by 子句。如果有对应的索引，通常效率会不错，否则 MySQL 需要做大量的文件排序操作。

一个非常令人头疼问题就是当偏移量非常大的时候，例如可能是 limit 1000000,10 这样的查询，这是 mysql 需要查询 1000000 条然后只返回最后 10 条，前面的 1000000 条记录都将被舍弃，这样的代价很高，会造成慢查询。

优化此类查询的一个最简单的方法是尽可能的使用索引覆盖扫描，而不是查询所有的列。然后根据需要做一次关联操作再返回所需的列。对于偏移量很大的时候这样做的效率会得到很大提升。

对于下面的查询：

-- 执行耗时：1.379s

SELECT * from vio_basic_domain_info LIMIT 1000000,10;

Explain 分析结果：

mysql> EXPLAIN SELECT * from vio_basic_domain_info LIMIT 1000000,10;

+----+-------------+-----------------------+------------+------+---------------+------+---------+------+---------+----------+-------+

+----+-------------+-----------------------+------------+------+---------------+------+---------+------+---------+----------+-------+

+----+-------------+-----------------------+------------+------+---------------+------+---------+------+---------+----------+-------+

1 row in set, 1 warning (0.00 sec)

该语句存在的最大问题在于 limit M,N 中偏移量 M 太大，导致每次查询都要先从整个表中找到满足条件的前 M 条记录，之后舍弃这 M 条记录并从第 M+1 条记录开始再依次找到 N 条满足条件的记录。如果表非常大，且筛选字段没有合适的索引，且 M 特别大那么这样的代价是非常高的。

那么如果我们下一次的查询能从前一次查询结束后标记的位置开始查找，找到满足条件的 10 条记录，并记下下一次查询应该开始的位置，以便于下一次查询能直接从该位置开始，这样就不必每次查询都先从整个表中先找到满足条件的前 M 条记录，舍弃掉，再从 M+1 开始再找到 10 条满足条件的记录了。

处理分页慢查询的方式一般有以下几种

思路一：构造覆盖索引

通过修改 SQL，使用上覆盖索引，比如我需要只查询表中的 app_name、createTime 等少量字段，那么我秩序在 app_name、createTime 字段设置联合索引，即可实现覆盖索引，无需全表扫描。适用于查询列较少的场景，查询列数过多的不推荐。

耗时：0.390s

mysql> EXPLAIN SELECT app_name,createTime from vio_basic_domain_info LIMIT 1000000,10;

发布于: 刚刚阅读数: 2

极客good

关注

还未添加个人签名 2021.03.18 加入

还未添加个人简介

发布

暂无评论

创作场景

MySQL 慢查询，一口从天而降的锅！，java 程序开发基础彭政答案

方式一：通过命令开启慢查询

方式二：通过配置 my.cnf（windows 是 my.ini）系统文件开启

开启慢查询功能

指定记录慢查询日志 SQL 执行时间得阈值

选填，默认数据文件路径

slow_query_log_file=/var/lib/mysql/localhost-slow.log

1、type：

2、key

3、rows

4、Extra

思路一：构造覆盖索引

极客good

评论