【MySQL】慢sql优化全流程解析

定位慢sql

工具排查慢sql

• 调试工具：Arthas
• 运维工具：Skywalking

通过以上工具可以看到哪个接口比较慢，并且可以分析SQL具体的执行时间，定位到哪个sql出了问题。

启用慢查询日志

慢查询日志记录了所有执行时间超过指定参数(long_query_time，单位:秒，默认10秒)的所有SQL语句的日志。

MySQL的慢查询日志默认没有开启，需要在MySQL的配置文件(/etc/my.cnf)中配置如下信息:

# 开启MySQL慢日志查询开关
slow_query_log=1

# 设置慢日志的时间为1秒，SQL语句执行时间超过1秒，就会视为慢查询，记录到慢查询日志中
long_query_time=2
1
2
3
4
5

配置完成后，重启MySQL服务保证配置生效。

慢查询日志一般的返回结果如下：

# Time:2024-08-01T12:00:00.123456Z
# User@Host: root[root] @ localhost [] Id: 	8
# Query time:2.345678 Lock_time:0.012345 Rows sent:10 Rows examined: 100
SET timestamp=1650000000;
SELECT * FROM orders WHERE status ='pending" ORDER BY gmt created DEsc;
1
2
3
4
5

需要关注以下内容：

• Query_time（查询时间）：查询执行的总时间，单位为秒。是关键的指标，用于判断查询的性能。

• Lock_time（锁定时间）：表被锁定的时间，单位为秒。可以帮助判断是否存在锁等待问题。

• Rows_sent（发送的行数）：查询返回的行数。

• Rows_examined（检查的行数）：查询过程中检查的行数，用于判断查询的效率。

分析慢sql

profile详情

SHOW PROFILE 是 MySQL 提供的一种用于查看查询语句执行的详细步骤和资源消耗的工具。使用 SHOW PROFILE 命令可以帮助找出查询语句的瓶颈，优化查询性能。

启用 Profiling

在使用 SHOW PROFILE 之前，需要先启用 Profiling：

SET profiling = 1;
1

执行查询

执行你想分析的查询语句：

SELECT * FROM your_table WHERE some_column = 'some_value';
1

查看 Profile 列表

使用以下命令查看刚才执行的查询的 Profile：

SHOW PROFILES;
1

这将显示一个查询 ID 列表及其对应的查询语句和总执行时间。

查看详细的 Profile 信息

使用 SHOW PROFILE 查看某个查询 ID 的详细信息：

SHOW PROFILE FOR QUERY query_id;
1

查看CPU信息

SHOW PROFILE CPU FOR QUERY query_id;
1

explain执行计划

explain 是 MySQL 提供的一种用于分析和调试 SQL 查询的工具。

通过使用 explain，可以了解 MySQL 在执行查询时采用的具体执行计划，包括访问数据表的方式、使用的索引、连接表的顺序等信息。这些信息对于优化查询性能至关重要。

基本概念

EXPLAIN 执行计划支持 SELECT、DELETE、INSERT、REPLACE 以及 UPDATE 语句。我们一般多用于分析 SELECT 查询语句，要获取一条sql语句的执行计划，只需要在语句前加上explain关键字即可。

explain + sql语句;
1

执行计划的返回结果一般是这样的：

+----+-------------+----------+------------+-------+-----------------+---------+---------+------+--------+----------+-------------+
| id | select_type | table    | partitions | type  | possible_keys   | key     | key_len | ref  | rows   | filtered | Extra       |
+----+-------------+----------+------------+-------+-----------------+---------+---------+------+--------+----------+-------------+
|  1 | PRIMARY     | dept_emp | NULL       | ALL   | NULL            | NULL    | NULL    | NULL | 331143 |   100.00 | Using where |
+----+-------------+----------+------------+-------+-----------------+---------+---------+------+--------+----------+-------------+
1
2
3
4
5

返回结果中各字段的含义解释如下：

字段释意

id

查询的序列标识符，用于表示查询的执行顺序。值越大，优先级越低，执行顺序越靠后。

select_type

查询的类型，主要用于区分普通查询、联合查询、子查询等复杂的查询，常见的值有：

• SIMPLE: 简单查询，不包含子查询或 UNION。
• PRIMARY: 最外层的 SELECT 查询。
• SUBQUERY: 子查询中的第一个 SELECT。
• DERIVED: 派生表（子查询中的 FROM 子句）。
• UNION: UNION 操作中的第二个或后续的 SELECT 查询。
• UNION RESULT: UNION 的结果集。

table

查询用到的表名。

type（重要）

查询执行的类型，描述了查询是如何执行的。常见的类型如下，这些类型的性能从最优到最差排序为：system > const > eq_ref > ref > range > index > ALL

• system：如果表使用的引擎对于表行数统计是精确的（如：MyISAM），且表中只有一行记录的情况下，访问方法是 system ，是 const 的一种特例。
• const：表中最多只有一行匹配的记录，一次查询就可以找到，常用于使用主键或唯一索引的所有字段作为查询条件。
• eq_ref：当连表查询时，前一张表的行在当前这张表中只有一行与之对应。是除了 system 与 const 之外最好的 join 方式，常用于使用主键或唯一索引的所有字段作为连表条件。
• ref：使用普通索引作为查询条件，查询结果可能找到多个符合条件的行。
• range：对索引列进行范围查询，执行计划中的 key 列表示哪个索引被使用了。
• index：查询遍历了整棵索引树，与 ALL 类似，只不过扫描的是索引，而索引一般在内存中，速度更快。
• ALL：全表扫描。

possible_keys

possible_keys 列表示 MySQL 执行查询时可能用到的索引。如果这一列为 NULL ，则表示没有索引可以使用。

key（重要）

key 列表示 MySQL 实际使用到的索引。如果为 NULL，则表示未用到索引。

Extra（重要）

这列包含了 MySQL 解析查询的额外信息，通过这些信息，可以更准确的理解 MySQL 到底是如何执行查询的。常见的值如下：

• Using index：表明查询使用了覆盖索引，不用回表，查询效率非常高。
• Using index condition：表示查询优化器选择使用了索引下推这个特性。
• Using where：表明查询使用了 WHERE 子句进行条件过滤。一般在没有使用到索引的时候会出现。
• Using filesort：在排序时使用了文件排序而不是索引排序，通常是因为无法使用索引进行排序。
• Using temporary：MySQL 需要创建临时表来存储查询的结果，常见于 ORDER BY 和 GROUP BY。
• Using join buffer (Block Nested Loop)：连表查询的方式，表示当被驱动表的没有使用索引的时候，MySQL 会先将驱动表读出来放到 join buffer 中，再遍历被驱动表与驱动表进行查询。

优化慢sql

sql优化方案

根据explain执行计划的返回结果，我们可以根据以下字段进行sql优化：

• 通过key和key_len检査是否命中了索引（索引本身存在是否有失效的情况）
• 通过type字段查看sql是否有进一步的优化空间，是否存在全索引扫描或全表扫描
• 通过extra字段判断，是否出现了回表的情况，如果出现了，可以尝试添加索引或修改返回字段来修复

深分页优化查询

传统分页

传统分页通常使用 OFFSET 和 LIMIT 来实现

SELECT * FROM table_name ORDER BY column_name LIMIT 10 OFFSET 1000;
1

这种方法对于小数据集或页数较小时效果较好，但在数据量非常大的情况下，OFFSET 的值越大，数据库需要扫描的行数就越多，性能会急剧下降。

深分页

深分页通过避免使用 OFFSET 来提高性能

1.覆盖索引+子查询： 这种方法通过子查询使用覆盖索引快速定位到分页的起始位置，外部查询从该位置获取实际数据，避免大量数据扫描和回表操作。

如本例中通过子查询定位到了第100001页的起始位置，向后获取100行数据。

SELECT * FROM users WHERE id > (SELECT id FROM users ORDER BY id LIMIT 100000, 1) LIMIT 100;
1

这种方法避免了大量数据扫描，适用于有索引列的情况。

2.存储分页结果： 另一种方法是将分页结果存储在缓存（如 Redis）或临时表中，从而避免频繁查询数据库。例如：

-- 第一次查询并缓存结果
SELECT * FROM table_name ORDER BY column_name LIMIT 1000;
-- 将结果缓存起来，随后从缓存中进行分页
1
2
3

这种方法适用于需要多次访问相同分页结果的场景。