【强烈建议收藏:MySQL面试必问系列之慢SQL优化专题】

一.知识回顾

学习本篇文章之前呢，我们可以先看一下【强烈建议收藏:MySQL面试必问系列之SQL语句执行专题】，看完这篇文章再来学习本篇文章可谓是如虎添翼。好的，那我们也不讲太多的废话，直接开始。

二.如何做慢SQL查询优化呢？

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

1. slow_query_log：是否开启慢查询日志，ON(1)表示开启, OFF(0) 表示关闭。
2. slow-query-log-file：新版（5.6及以上版本）MySQL数据库慢查询日志存储路径。
3. long_query_time： 慢查询阈值，当查询时间多于设定的阈值时，记录日志。
4. log_output: 是指定日志的存储方式。
5. log-queries-not-using-indexes：未使用索引的查询也被记录到慢查询日志中。如果调优的话，建议开启这个选项。

2.2 慢查询配置方式

1. 默认情况下slow_query_log的值为OFF，表示慢查询日志是禁用的,我们可以通过以下的命令查看当前慢查询日志是否已经开发

mysql> show variables like '%slow_query_log%';
+---------------------+------------------------------+
| Variable_name       | Value                        |
+---------------------+------------------------------+
| slow_query_log      | OFF                           |
| slow_query_log_file | /var/lib/mysql/test-slow.log |
+---------------------+------------------------------+

2. 可以通过设置slow_query_log的值来开启，命令实操如下所示:

mysql> set global slow_query_log=1;

3. 使用 set global slow_query_log=1命令开启了慢查询日志，此时只对当前数据库生效，MySQL重启后则会失效。如果要永久生效，就必须修改配置文件my.cnf（其它系统变量也是如此）
   特别说明:以下实操是在Linux操作系统上完成的。
   打开my.cnf配置文件

# 编辑配置
vim /etc/my.cnf

添加如下内容

slow_query_log =1
slow_query_log_file=/var/lib/mysql/ruyuan-slow.log

配置成功后，重启MySQL

service mysqld restart
mysql> show variables like '%slow_query%';
+---------------------+--------------------------------+
| Variable_name       | Value                          |
+---------------------+--------------------------------+
| slow_query_log      | ON                             |
| slow_query_log_file | /var/lib/mysql/ruyuan-slow.log |
+---------------------+--------------------------------+

4. 开启了慢查询日志后，什么样的SQL才会记录到慢查询日志里面呢？ 这个是由参数 long_query_time控制，默认情况下long_query_time的值为10秒。

mysql> show variables like 'long_query_time';
+-----------------+-----------+
| Variable_name   | Value     |
+-----------------+-----------+
| long_query_time | 10.000000 |
+-----------------+-----------+mysql> set global long_query_time=5;
Query OK, 0 rows affected (0.00 sec)mysql>  show variables like 'long_query_time';
+-----------------+-----------+
| Variable_name   | Value     |
+-----------------+-----------+
| long_query_time | 10.000000 |
+-----------------+-----------+

5. 修改了变量long_query_time，但是查询变量long_query_time的值还是5，我们修改了，为什么没有显示呢？
   注意：使用命令 set global long_query_time=5 修改后，需要重新连接或新开一个会话才能看到修改值。

mysql> show variables like 'long_query_time';
+-----------------+----------+
| Variable_name   | Value    |
+-----------------+----------+
| long_query_time | 5.000000 |
+-----------------+----------+

6. log_output 参数是指定日志的存储方式。log_output='FILE' 表示将日志存入文件，默认值是’FILE’。log_output='TABLE' 表示将日志存入数据库，这样日志信息就会被写入到 mysql.slow_log 表中。

mysql> SHOW VARIABLES LIKE '%log_output%';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| log_output    | FILE  |
+---------------+-------+

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

7. 系统变量 log-queries-not-using-indexes：未使用索引的查询也被记录到慢查询日志中，默认是关闭的。

mysql> show variables like 'log_queries_not_using_indexes';
+-------------------------------+-------+
| Variable_name                 | Value |
+-------------------------------+-------+
| log_queries_not_using_indexes | OFF   |
+-------------------------------+-------+mysql> set global log_queries_not_using_indexes=1;
Query OK, 0 rows affected (0.00 sec)mysql> show variables like 'log_queries_not_using_indexes';
+-------------------------------+-------+
| Variable_name                 | Value |
+-------------------------------+-------+
| log_queries_not_using_indexes | ON    |
+-------------------------------+-------+

2.3 慢查询测试

1. 执行 test_index.sql 脚本,监控慢查询日志内容

[root@localhost mysql]# tail -f /var/lib/mysql/ruyuan-slow.log 
/usr/sbin/mysqld, Version: 5.7.30-log (MySQL Community Server (GPL)). started with:
Tcp port: 0  Unix socket: /var/lib/mysql/mysql.sock
Time                 Id Command    Argument

2. 执行下面的SQL,执行超时 ，超过我们刚才设置的5s， 我们去查看慢查询日志

SELECT * FROM test_index WHERE  name = 'jack' OR id = '1' OR name = 'tom' OR id = '2';

3. 查看日志内容并对日志做分析

我们得到慢查询日志后，最重要的一步就是去分析这个日志。我们先来看下慢日志里到底记录了哪些内容。

如下所示该内容是慢日志里其中一条SQL的记录内容，可以看到有时间戳，用户，查询时长及具体的SQL等信息

# Time: 2022-02-23T13:50:45.005959Z
# User@Host: root[root] @ localhost []  Id:     3
# Query_time: 6.724273  Lock_time: 0.000371 Rows_sent: 5  Rows_examined: 5000000
SET timestamp=1842325245;
SELECT * FROM test_index WHERE  name = 'jack' OR id = '1' OR name = 'tom' OR id = '2';

• Time: 执行时间
• User: 用户信息 ,Id信息
• Query_time: 查询时长
• Lock_time: 等待锁的时长
• Rows_sent:查询结果的行数
• Rows_examined: 查询扫描的行数
• SET timestamp: 时间戳
• SQL的具体信息

2.4 慢查询SQL优化思路

2.4.1 SQL性能下降的原因

1. 等待时间长：锁表导致查询一直处于等待状态，之前的文章中我们学习过MySQL锁的机制，需要的同学可以看一下之前的文章。
2. 执行时间长：导致执行时间长的原因也有很多，比如说我们的查询语句写的有问题、查询没有走索引，导致失效失效、 关联查询过多、服务器调优及各个参数的设置等方面。

2.4.2 慢查询优化思路

1. 优先选择优化高并发执行的SQL,因为高并发的SQL发生问题带来后果更严重。

   比如下面两种情况:
   SQL1: 每小时执行10000次, 每次40个IO 优化后每次35个IO,每小时节省5万次IO
   SQL2: 每小时执行10次,每次40000个IO,每次优化减少5000个IO,每小时节省5万次IO
   SQL2更难优化,SQL1更好优化.但是第一种属于高并发SQL,更急需优化 成本更低

2. 定位优化对象的性能瓶颈(一定要在优化之前了解性能瓶颈在哪？不能没有目标的瞎优化)

   在优化SQL时,选择优化分方向有三个:
   1.IO(数据访问消耗的了太多的时间,查看是否正确使用了索引) ,
   2.CPU(数据运算花费了太多时间, 数据的运算分组 排序是不是有问题)
   3.网络带宽(加大网络带宽)

3. 明确优化目标

   需要根据数据库当前的状态、数据库中与该条SQL的关系、当前SQL的具体功能 、最好的情况消耗的资源,最差情况下消耗的资源,优化的结果只有一个给用户一个好的体验。说到底，还是要写出好的SQL语句。

4. explain执行计划分析慢SQL语句

   explain可以展示当前SQL的执行状态，具体的一些细节我们后面展开学习。

5. 永远用小的结果集驱动大的结果集
   小的数据集驱动大的数据集,减少内层表读取的次数，类似于嵌套循环。如果小的循环在外层,对于数据库连接来说就只连接10次,进行10000次操作,如果1000在外,则需要进行1000次数据库连接,从而浪费资源，增加消耗.这就是为什么要小表驱动大表。

   for(int i = 0; i < 10; i++){for(int i = 0; i < 1000; i++){//具体操作}
   }
   

6. 尽可能在索引中完成排序

   排序操作用的比较多,order by 后面的字段如果在索引中,索引本来就是排好序的,所以速度很快,没有索引的话,就需要从表中拿数据,在内存中进行排序,如果内存空间不够还会发生落盘操作

7. 不要使用select *,只获取自己需要的列

   不要使用select * ,select * 很可能不走索引,而且数据量过大

8. 只使用最有效的过滤条件

   误区 where后面的条件越多越好,但实际上是应该用最短的路径访问到数据

9. 尽可能避免复杂的join和子查询

   每条SQL的JOIN操作 建议不要超过三张表
   将复杂的SQL, 拆分成多个小的SQL 单个表执行,获取的结果 在程序中进行封装
   如果join占用的资源比较多,会导致其他进程等待时间变长

10. 合理设计并利用索引
    后续我们会专门开一个专题来学习MySQL索引这块的知识内容。此处先做一个简单的了解。

    问题：如何判定是否需要创建索引?
    1.较为频繁的作为查询条件的字段应该创建索引.
    2.唯一性太差的字段不适合单独创建索引，即使频繁作为查询条件.（唯一性太差的字段主要是指哪些呢？如状态字段，类型字段等等这些字段中的数据可能总共就是那么几个几十个数值重复使用）（当一条Query所返回的数据超过了全表的15%的时候，就不应该再使用索引扫描来完成这个Query了）.
    3.更新非常频繁的字段不适合创建索引.（因为索引中的字段被更新的时候，不仅仅需要更新表中的数据，同时还要更新索引数据，以确保索引信息是准确的）.
    4.不会出现在WHERE子句中的字段不该创建索引.
    问题2:如何选择合适索引?
    1.对于单键索引，尽量选择针对当前Query过滤性更好的索引.
    2.选择联合索引时,当前Query中过滤性最好的字段在索引字段顺序中排列要靠前.
    3.选择联合索引时,尽量索引字段出现在where中比较多的索引.

三.上面提到的explain详细讲讲，有哪些主要字段？

3.1 explain分析SQL语句的性能瓶颈

使用 explain 关键字可以模拟优化器来执行SQL查询语句，从而知道MySQL是如何处理我们的SQL语句的。分析出查询语句或是表结构的性能瓶颈。

MySQL查询过程

通过explain我们可以获得以下信息：

• 表的读取顺序
• 数据读取操作的操作类型
• 哪些索引可以被使用
• 哪些索引真正被使用
• 表的直接引用
• 每张表的有多少行被优化器查询了

Explain使用方式: explain+sql语句, 通过执行explain可以获得sql语句执行的相关信息

explain select * from users;

3.2 explain解释执行后的SQL语句有哪些字段？

1. id：select 查询序列号。id相同，执行顺序由上至下；id不同，id值越大优先级越高，越先被执行。
2. select_type：查询数据的操作类型，其值如下：

   simple：简单查询，不包含子查询或 union
   primary:包含复杂的子查询，最外层查询标记为该值
   subquery：在 select 或 where 包含子查询，被标记为该值
   derived：在 from 列表中包含的子查询被标记为该值，MySQL 会递归执行这些子查询，把结果放在临时表
   union：若第二个 select 出现在 union 之后，则被标记为该值。若 union 包含在 from 的子查询中，外层 select 被标记为 derived
   union result：从 union 表获取结果的 select

3. table：显示该行数据是关于哪张表
4. partitions：匹配的分区
5. type：表的连接类型，其值，性能由高到底排列如下：
   (1)type字段显示的是表示的是用什么样的方式来获取数据，它描述了找到所需数据所使用的扫描方式, 是较为重要的一个指标。
   (2)一般来说,需要保证查询至少达到 range级别,最好能到ref,否则就要就行SQL的优化调整。
   下面介绍type字段不同值表示的含义:

type类型	解释
system	不进行磁盘IO,查询系统表,仅仅返回一条数据
const	查找主键索引,最多返回1条或0条数据. 属于精确查找
eq_ref	查找唯一性索引,返回数据最多一条, 属于精确查找
ref	查找非唯一性索引,返回匹配某一条件的多条数据,属于精确查找,数据返回可能是多条.
range	查找某个索引的部分索引,只检索给定范围的行,属于范围查找. 比如: > 、 < 、in 、between
index	查找所有索引树,比ALL快一些,因为索引文件要比数据文件小.
ALL	不使用任何索引,直接进行全表扫描

6. possible_keys：显示 MySQL 理论上使用的索引，查询涉及到的字段上若存在索引，则该索引将被列出，但不一定被查询实际使用。如果该值为 NULL，说明没有使用索引，可以建立索引提高性能
7. key：显示 MySQL 实际使用的索引。如果为 NULL，则没有使用索引查询
8. key_len：表示索引中使用的字节数，通过该列计算查询中使用的索引的长度。在不损失精确性的情况下，长度越短越好 显示的是索引字段的最大长度，并非实际使用长度
9. ref：显示该表的索引字段关联了哪张表的哪个字段
10. rows：根据表统计信息及选用情况，大致估算出找到所需的记录或所需读取的行数，数值越小越好
11. filtered：返回结果的行数占读取行数的百分比，值越大越好
12. extra：包含不合适在其他列中显示但十分重要的额外信息，常见的值如下：
    extra是 explain输出中另外一个很重要的列，该列显示MySQL在查询过程中的一些详细信息，如下所示:

extra类型	解释
Using filesort	MySQL中无法利用索引完成的排序操作称为 “文件排序”
Using index	表示直接访问索引就能够获取到所需要的数据（覆盖索引），不需要通过索引回表
Using index condition	搜索条件中虽然出现了索引列，但是有部分条件无法使用索引， 会根据能用索引的条件先搜索一遍再匹配无法使用索引的条件。
Using join buffer	使用了连接缓存, 会显示join连接查询时,MySQL选择的查询算法
Using temporary	表示MySQL需要使用临时表来存储结果集，常见于排序和分组查询
Using where	意味着全表扫描或者在查找使用索引的情况下，但是还有查询条件不在索引字段当中

四.如何进行分页查询优化？

4.1 分页查询格式

一般的分页查询使用简单的 limit 子句就可以实现。limit格式如下：

SELECT * FROM 表名 LIMIT [offset,] rows

• 第一个参数指定第一个返回记录行的偏移量，注意从0开始；
• 第二个参数指定返回记录行的最大数目；
• 如果只给定一个参数，它表示返回最大的记录行数目；

4.2 偏移量和返回的数目对分页查询效率的影响

思考1：如果偏移量固定，返回记录量对执行时间有什么影响？

select * from user limit 10000,1;
select * from user limit 10000,10;
select * from user limit 10000,100;
select * from user limit 10000,1000;
select * from user limit 10000,10000;

结果：在查询记录时，返回记录量低于100条，查询时间基本没有变化，差距不大。随着查询记录量越大，所花费的时间也会越来越多。

思考2：如果查询偏移量变化，返回记录数固定对执行时间有什么影响？

select * from user limit 1,100;
select * from user limit 10,100;
select * from user limit 100,100;
select * from user limit 1000,100;
select * from user limit 10000,100;

结果：在查询记录时，如果查询记录量相同，偏移量超过100后就开始随着偏移量增大，查询时间急剧的增加。

4.3 分页查询优化

优化1: 通过索引进行分页

1. 直接进行limit操作会产生全表扫描,速度很慢。Limit限制的是从结果集的M位置处取出N条输出,其余抛弃。
2. 假设ID是连续递增的,我们根据查询的页数和查询的记录数可以算出查询的id的范围，然后配合 limit使用

EXPLAIN SELECT * FROM user WHERE id  >= 100001 LIMIT 100;

优化2：利用子查询优化

1. 首先定位偏移位置的id，通过子查询找到比较的值，使用覆盖索引进行优化。

# 根据获取到的id值向后查询
EXPLAIN SELECT * FROM user_contacts WHERE id >=
(SELECT id FROM user_contacts LIMIT 100000,1) LIMIT 100;

特别感谢:部分引用来自马士兵教育