乐趣区

关于java:从天而降一口锅MySQL慢查询的这几个坑你踩过没真是防不胜防

jiezi

今日分享开始啦，请大家多多指教~

明天这篇文章给大家介绍一下 MySQL 的慢查问，全名是慢查问日志，是 MySQL 提供的一种日志记录，用来记录在 MySQL 中响应工夫超过阀值的语句。通过这篇文章，心愿你 Get 到了一些剖析 MySQL 慢查问的办法和心得。慢查问，在 MySQL 中始终是绕不开的话题，慢的形式多种多样，如果你想完全避免慢查问？年轻人，我倡议你耗子尾汁~~ 话不多说，注释开始啦！

一、慢查问配置

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、开启慢查问命令：

步骤 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] 下减少如下配置开启慢查问，如下图


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

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

![上传中 …]()
如上图，是执行工夫超过 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 '% 欣欣 %' ;
+----+-------------+-----------------------+------------+------+---------------+------+---------+------+---------+----------+-------------+
| id | select_type | table                 | partitions | type | possible_keys | key  | key_len | ref  | rows    | filtered | Extra       |
+----+-------------+-----------------------+------------+------+---------------+------+---------+------+---------+----------+-------------+
|  1 | SIMPLE      | vio_basic_domain_info | NULL       | ALL  | NULL          | NULL | NULL    | NULL | 1377809 |    11.11 | Using where |
+----+-------------+-----------------------+------------+------+---------------+------+---------+------+---------+----------+-------------+
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 '欣欣 %' ;
+----+-------------+-----------------------+------------+-------+---------------+--------------+---------+------+------+----------+-----------------------+
| id | select_type | table                 | partitions | type  | possible_keys | key          | key_len | ref  | rows | filtered | Extra                 |
+----+-------------+-----------------------+------------+-------+---------------+--------------+---------+------+------+----------+-----------------------+
|  1 | SIMPLE      | vio_basic_domain_info | NULL       | range | idx_app_name  | idx_app_name | 515     | NULL |  141 |   100.00 | Using index condition |
+----+-------------+-----------------------+------------+-------+---------------+--------------+---------+------+------+----------+-----------------------+
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 '欣欣 %' ;
+----+-------------+-----------------------+------------+-------+---------------+--------------+---------+------+------+----------+--------------------------+
| id | select_type | table                 | partitions | type  | possible_keys | key          | key_len | ref  | rows | filtered | Extra                    |
+----+-------------+-----------------------+------------+-------+---------------+--------------+---------+------+------+----------+--------------------------+
|  1 | SIMPLE      | vio_basic_domain_info | NULL       | range | idx_app_name  | idx_app_name | 515     | NULL |  141 |   100.00 | Using where; Using index |
+----+-------------+-----------------------+------------+-------+---------------+--------------+---------+------+------+----------+--------------------------+
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 服务器将在存储引擎检索行后再进行过滤；即没有用到索引，回表查问。

可能的起因：

  1. 查问的列未被索引笼罩；
  2. 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;
+----+-------------+-----------------------+------------+------+---------------+------+---------+------+---------+----------+-------+
| id | select_type | table                 | partitions | type | possible_keys | key  | key_len | ref  | rows    | filtered | Extra |
+----+-------------+-----------------------+------------+------+---------------+------+---------+------+---------+----------+-------+
|  1 | SIMPLE      | vio_basic_domain_info | NULL       | ALL  | NULL          | NULL | NULL    | NULL | 1377809 |   100.00 | NULL  |
+----+-------------+-----------------------+------------+------+---------------+------+---------+------+---------+----------+-------+
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;
+----+-------------+-----------------------+------------+-------+---------------+--------------+---------+------+---------+----------+-------------+
| id | select_type | table                 | partitions | type  | possible_keys | key          | key_len | ref  | rows    | filtered | Extra       |
+----+-------------+-----------------------+------------+-------+---------------+--------------+---------+------+---------+----------+-------------+
|  1 | SIMPLE      | vio_basic_domain_info | NULL       | index | NULL          | idx_app_name | 515     | NULL | 1377809 |   100.00 | Using index |
+----+-------------+-----------------------+------------+-------+---------------+--------------+---------+------+---------+----------+-------------+
1 row in set, 1 warning (0.00 sec)

思路二：优化 offset

  无奈用上笼罩索引，那么重点是想方法疾速过滤掉前 100w 条数据。咱们能够利用自增主键有序的条件，先查问出第 1000001 条数据的 id 值，再往后查 10 行；实用于主键 id 自增的场景。耗时：0.471s

SELECT * from vio_basic_domain_info where 
  id >=(SELECT id from vio_basic_domain_info ORDER BY id limit 1000000,1) limit 10;

原理：先基于索引查问出第 1000001 条数据对应的主键 id 的值，而后间接通过该 id 的值间接查问该 id 前面的 10 条数据。下方 EXPLAIN 剖析后果中大家能够看到这条 SQL 的两步执行流程。

mysql> EXPLAIN SELECT * from vio_basic_domain_info where id >=(SELECT id from vio_basic_domain_info ORDER BY id limit 1000000,1) limit 10;
+----+-------------+-----------------------+------------+-------+---------------+---------+---------+------+---------+----------+-------------+
| id | select_type | table                 | partitions | type  | possible_keys | key     | key_len | ref  | rows    | filtered | Extra       |
+----+-------------+-----------------------+------------+-------+---------------+---------+---------+------+---------+----------+-------------+
|  1 | PRIMARY     | vio_basic_domain_info | NULL       | range | PRIMARY       | PRIMARY | 8       | NULL |      10 |   100.00 | Using where |
|  2 | SUBQUERY    | vio_basic_domain_info | NULL       | index | NULL          | PRIMARY | 8       | NULL | 1000001 |   100.00 | Using index |
+----+-------------+-----------------------+------------+-------+---------------+---------+---------+------+---------+----------+-------------+
2 rows in set, 1 warning (0.40 sec)

办法三：“提早关联”

耗时：0.439s 提早关联实用于数量级较大的表，SQL 如下；

SELECT * from vio_basic_domain_info inner join (select id from vio_basic_domain_info order by id limit 1000000,10) as myNew using(id);

这里咱们利用到了笼罩索引 + 提早关联查问，相当于先只查问 id 列，利用笼罩索引疾速查到该页的 10 条数据 id，而后再把返回的 10 条 id 拿到表中通过主键索引二次查问。（表数据增速快的状况对该办法影响较小。）

mysql> EXPLAIN SELECT * from vio_basic_domain_info inner join (select id from vio_basic_domain_info order by id limit 1000000,10) as myNew using(id);
+----+-------------+-----------------------+------------+--------+---------------+---------+---------+----------+---------+----------+-------------+
| id | select_type | table                 | partitions | type   | possible_keys | key     | key_len | ref      | rows    | filtered | Extra       |
+----+-------------+-----------------------+------------+--------+---------------+---------+---------+----------+---------+----------+-------------+
|  1 | PRIMARY     | <derived2>            | NULL       | ALL    | NULL          | NULL    | NULL    | NULL     | 1000010 |   100.00 | NULL        |
|  1 | PRIMARY     | vio_basic_domain_info | NULL       | eq_ref | PRIMARY       | PRIMARY | 8       | myNew.id |       1 |   100.00 | NULL        |
|  2 | DERIVED     | vio_basic_domain_info | NULL       | index  | NULL          | PRIMARY | 8       | NULL     | 1000010 |   100.00 | Using index |
+----+-------------+-----------------------+------------+--------+---------------+---------+---------+----------+---------+----------+-------------+
3 rows in set, 1 warning (0.00 sec)

3-2、排查索引没起作用的状况

含糊查问尽量避免用通配符’%’ 结尾，会导致数据库引擎放弃索引进行全表扫描。如下：

SELECT * FROM t WHERE username LIKE '% 欣欣 %'

优化形式：尽量在字段前面应用含糊查问。如下：

SELECT * FROM t WHERE username LIKE '欣欣 %'

如果需要是要在后面应用含糊查问，

  • 应用 MySQL 内置函数 INSTR(str,substr) 来匹配，作用相似于 java 中的 indexOf()，查问字符串呈现的角标地位。
  • 应用 FullText 全文索引，用 match against 检索
  • 数据量较大的状况，倡议援用 ElasticSearch、solr，亿级数据量检索速度秒级
  • 当表数据量较少（几千条儿那种），别整花里胡哨的，间接用 like‘%xx%’。

  但不得不说，MySQL 含糊匹配大字段是硬伤，毕竟保障事务的 ACID 个性消耗了太多性能，因而，如果理论场景中有相似业务需要，倡议果决更换大数据存储引擎如 ElasticSearch、Hbase 等。这里和情怀无关~

2. 尽量避免应用 not in，会导致引擎走全表扫描。倡议用 not exists 代替，如下：

-- 不走索引
SELECT * FROM t WHERE name not IN ('提莫','队长');
-- 走索引
select * from t as t1 where not exists (select * from t as t2 where name IN ('提莫','队长') and t1.id = t2.id);

3. 尽量避免应用 or，会导致数据库引擎放弃索引进行全表扫描。如下：

SELECT * FROM t WHERE id = 1 OR id = 3

优化形式：能够用 union 代替 or。如下：

SELECT * FROM t WHERE id = 1
   UNION
SELECT * FROM t WHERE id = 3

4. 尽量避免进行 null 值的判断，会导致数据库引擎放弃索引进行全表扫描。如下：

SELECT * FROM t WHERE score IS NULL

优化形式：能够给字段增加默认值 0，对 0 值进行判断。如下：

SELECT * FROM t WHERE score = 0

5. 尽量避免在 where 条件中等号的左侧进行表达式、函数操作，会导致数据库引擎放弃索引进行全表扫描。

能够将表达式、函数操作挪动到等号右侧。如下：

-- 全表扫描
SELECT * FROM T WHERE score/10 = 9
-- 走索引
SELECT * FROM T WHERE score = 10*9

6. 当数据量大时，防止应用 where 1= 1 的条件。通常为了不便拼装查问条件，咱们会默认应用该条件，数据库引擎会放弃索引进行全表扫描。如下：

SELECT username, age, sex FROM T WHERE 1=1

优化形式：用代码拼装 sql 时进行判断，没 where 条件就去掉 where，有 where 条件就加 and。

7. 查问条件不要用 <> 或者 !=

应用索引列作为条件进行查问时，须要防止应用 <> 或者!= 等判断条件。如的确业务须要，应用到不等于符号，须要在从新评估索引建设，防止在此字段上建设索引，改由查问条件中其余索引字段代替。

8.where 条件仅蕴含复合索引非前导列

  如：复合（联结）索引蕴含 key_part1，key_part2，key_part3 三列，但 SQL 语句没有蕴含索引前置列 ”key_part1″，依照 MySQL 联结索引的最左匹配准则，不会走联结索引。

-- 不走索引
select col1 from table where key_part2=1 and key_part3=2
-- 走索引
select col1 from table where key_part1 =1 and key_part2=1 and key_part3=2

9. 隐式类型转换造成不应用索引

  如下 SQL 语句因为索引队列类型为 varchar，但给定的值为数值，波及隐式类型转换，造成不能正确走索引。

select col1 from table where col_varchar=123;

小结：

咱们须要的是及时发现并解决慢查问，其实很多慢查问是被动呈现的，比方因为某业务数据量猛增数量级变动、因为业务需要变动而改了字段或操作了既有索引等。尽管不是你的错，但这锅可能还得你来背~

今日份分享已完结，请大

退出移动版