Mysql 常见的性能分析手段

Lemon程序馆
210 阅读12分钟

高级感横屏壁纸｜ipad壁纸电脑壁纸_1_糖芋_来自小红书网页版.jpg

前言

在开发中对于 MySQL 的性能优化是我们必备的技能。本文将介绍一些核心的性能分析工具 —— EXPLAIN、慢查询日志和 PROFILE 等，有效提高性能分析优化的能力。

EXPLAIN

EXPLAIN 是 Mysql 中用于分析 SQL 查询语句执行计划的重要工具，可显示出 MYSQL 如何执 SQL 语句， 它模拟 MYSQL 优化器执行 SQL 查询，返回执行计划信息，并非实际执行查询。通过 EXPLAIN，我们可以了解到：

  • 表的读取顺序
  • 数据读取操作的类型
  • 哪些索引可能被使用
  • 哪些索引实际被使用
  • 表之间的引用关系
  • 每张表有多少行被优化器查询到

基本语法

EXPLAIN [EXTENDED|PARTITIONS] SELECT select_options;

其中 EXTENDED 提供额外的查询优化信息，PARTITIONS 显示查询涉及的分区信息。

输出字段说明

执行以下 sql，ws_user 表中 id 为主键，ws_album 表中通过 user_id 字段关联 user，该字段没有添加索引。

EXPLAIN SELECT * FROM `ws_user` wu 
JOIN `ws_album` wa 
ON wu.id = wa.user_id;

输出结果：

image.png

  1. id (查询标识符)

    id 列是一个有顺序的编号，通过该列可以确定的 SELECT 子句的执行顺序。id 相同，从上到下执行，id 不同， 值大的先执行，id 为 NULL，最后执行，通常表示 UNION 结果的合并操作。

    有三种典型情况：

    • id 全部相同：执行顺序从上到下（如简单 join 查询）
    • id 全部不同：id 越大越先执行（常见于子查询）
    • id 部分相同：相同 id 按顺序执行，不同 id 越大越先执行。

  2. select_type（查询类型）

    表示查询的类型，主要用于区别普通查询、联合查询、子查询等复杂查询。

    类型描述
    SIMPLE简单 SELECT 查询，不包含子查询或 UNION
    PRIMARY复杂查询中最外层的 SELECT
    SUBQUERY包含在 SELECT 或 WHERE 列表中的子查询
    DEPENDENT SUBQUERY依赖于外部查询的子查询
    DERIVEDFROM 列表中的子查询（派生表）
    UNIONUNION 中的第二个或后续的 SELECT
    UNION RESULTUNION 结果的合并操作

  3. table

    表名，显示当前行对应的 SELECT 正在访问哪个表，可能是：

    • 实际表名
    • <derivedN>: ID 为 N 的派生表结果
    • <unionM,N>：id 为 M 和 N 的 UNION 结果

  4. type

    连接类型，最重要的指标之一，表示 Mysql 如何查找表中的行，性能从最优到最差排序为：

    system > const > eq_ref > ref > fulltext > ref_or_null > index_merge > unique_subquery > index_subquery > range > index > ALL

    优化建议：至少达到 range 级别，最好能达到 ref。

    常见重要类型详解：

    • system: 表只有一行记录（系统表），const 类型的特例
    • const: 通过主键或唯一索引一次找到，如 where id = 1
    • eq_ref: 主键或唯一索引扫描，每个索引键对应表中的唯一记录（JOIN 中常见）
    • ref: 非唯一索引扫描，返回匹配某个值的所有行
    • range: 索引范围扫描（BETWEEN, IN, >, < 等）
    • index: 全索引扫描（比 ALL 快，因为只读索引）
    • ALL：全表扫描，性能最差，应当避免

  5. possible_keys

    查询可能使用的索引，实际上不一定用到。

  6. key

    实际使用的索引，为 NULL 表示未使用索引。

  7. key_len （索引长度）

    表示索引中使用的字节数，可用于判断组合索引的使用情况（值越小效果越好）。

    key_len显示 MySQL 在执行查询时实际使用的索引部分的长度。例如，如果索引包含 3 列但 key_len只显示前两列的长度，说明第三列未被使用。通过 key_len可以推断复合索引的前缀匹配情况，从而优化查询条件或索引设计。

    ​​key_len的计算规则​​

    • ​​数据类型基础长度​​：如 INT占 4 字节，BIGINT占 8 字节，CHAR(N)占 N×字符集字节数（如 utf8mb4为 4 字节/字符）

    • ​​NULL 标记​​：如果列允许为 NULL，则额外增加 1 字节

    • ​​变长类型开销​​：VARCHAR等变长类型需额外加 2 字节存储长度信息

    • ​​字符集影响​​：不同字符集下，字符占用的字节数不同（如 latin1为 1 字节/字符，utf8mb4为 4 字节/字符）

    常见类型的 key_len示例：

    列类型是否允许 NULL字符集key_len计算结果
    INT否-44
    INT是-4 + 15
    VARCHAR(10)否utf8mb410×4 + 242
    VARCHAR(10)是utf8mb410×4 + 2 + 143
    DATETIME否-5（MySQL 5.6.4+）5
    CHAR(1)是utf81×3 + 14

    key_len的优化意义​​

    • ​​复合索引使用深度​​：例如，复合索引 (a,b,c)的 key_len为 8（INT类型），说明仅用到前两列（a和 b）
    • ​​避免全表扫描​​：若 key_len过小（如远小于索引总长度），可能需调整查询条件或索引列顺序
    • ​​减少 NULL 开销​​：允许 NULL的列会增加 1 字节，可通过设为 NOT NULL优化

  8. ref （索引引用）

    显示哪些列或常量被用于查找索引列上的值，常见有：

    • const：常量值
    • 列名：其他表的列（JOIN 操作）

  9. rows （预估行数）

    Mysql 估计需要检查的行数（非精确值），值越小越好。

  10. Extra （额外信息）

    包含重要的执行细节：

    值含义优化建议
    Using index使用覆盖索引（直接从索引获取数据）良好性能表现
    Using where使用 WHERE 过滤可能需要添加索引
    Using filesort额外文件排序（ORDER BY 未用索引）为排序字段添加索引
    Using temporary使用临时表（GROUP BY 或复杂操作）优化查询结构或添加索引
    Using join buffer使用连接缓存（未用索引的表连接）为 JOIN 条件添加索引
    Impossible WHEREWHERE 条件总是 false检查 SQL 逻辑

高级用法与拓展

  1. EXPLAIN EXTENDED

    提供更多查询优化信息，执行后可通过 SHOW WARNINGS查看优化后的查询语句：

    EXPLAIN EXTENDED SELECT * FROM users WHERE age > 30;
SHOW WARNINGS;

  2. EXPLAIN PARTITIONS

    用于分析分区表，显示可能用到的分区。

  3. 索引优化实践

    组合索引使用示例：

    -- 表结构：t_demo(id, c1, c2, c3)，有组合索引(c1,c2,c3)
EXPLAIN SELECT * FROM t_demo WHERE c1='d1' AND c2='d2' AND c3='d3';
EXPLAIN SELECT * FROM t_demo WHERE c2='d2' AND c1='d1' AND c3='d3';

    分析：

    • 两个查询表现一致，优化器会调整条件顺序以利用索引
    • 但 WHERE c1='d1' AND c2>'d2' AND c3='d3'会导致范围查询右侧索引失效

优化建议总结

  1. ​​索引优化​​：

    • 为 WHERE、JOIN、ORDER BY 字段添加合适索引
    • 使用覆盖索引（查询列都在索引中）
    • 注意组合索引的最左前缀原则

  2. ​​查询重构​​：

    • 避免 SELECT *，只查询需要的列
    • 将复杂查询拆分为简单查询
    • 避免在 WHERE 子句中对字段进行函数操作

  3. ​​重点关注​​：

    • type 列：避免 ALL，争取达到 range/ref
    • Extra 列：消除 Using filesort 和 Using temporary
    • rows 列：减少扫描行数

查看各类型 SQL 执行频次

数据库中各类型 SQL 的执行频次，是做优化的必要上下文，假设某个数据库中基本上不用查询（SELECT），那么就没必要做 select 语句的优化了。

通过 show [session|global] status 命令可以查看服务器状态信息。通过以下命令，可以查看当前数据库的 INSERT、UPDATE、DELETE、SELECT 的访问频次。

SHOW GLOBAL STATUS LIKE 'Com_______';

image.png

慢查询日志

慢查询日志是 Mysql 提供的一种日志记录机制，主要用于记录执行时间较长的 SQL 语句。默认情况下，Mysql 没有开启慢查询日志，因为开启会带来一定的性能开销。

通过慢查询日志方便我们找到查询时间较长的 sql，分析其对数据库性能的影响，从而优化 SQL、索引或者数据库设计。慢查询日志会记录以下关键信息：

  • 执行时间：SQL 语句的实际执行时间
  • SQL 语句内容：具体的 SQL 语句
  • 锁等待时间： 如果 SQL 涉及锁操作，会记录锁等待时间
  • 用户信息： 执行 SQL 的用户名和来源 IP
  • 查询计划：在某些配置下会记录执行计划

开启慢查询

开启慢查询有两种方式：通过命令行临时设置或通过配置文件永久设置

命令行临时设置（重启后失效）：

-- 开启慢查询日志
SET GLOBAL slow_query_log = ON;

-- 设置慢查询时间阈值(单位：秒)
SET GLOBAL long_query_time = 1;

-- 设置慢查询日志文件位置
SET GLOBAL slow_query_log_file = '/var/log/mysql/slow-query.log';

-- 记录未使用索引的查询(可选)
SET GLOBAL log_queries_not_using_indexes = ON;

配置文件永久设置(需重启MySQL服务):

编辑MySQL配置文件(通常为my.cnf或my.ini)，在[mysqld]部分添加以下配置：

[mysqld]
slow_query_log = 1
slow_query_log_file = /var/log/mysql/slow-query.log
long_query_time = 2
log_queries_not_using_indexes = 1
log_timestamps = SYSTEM
log_output = FILE

参数说明：

  • slow_query_log：1或ON表示开启，0或OFF表示关闭
  • slow_query_log_file：指定慢查询日志的存储路径
  • long_query_time：设置慢查询的阈值，单位为秒，默认10秒
  • log_queries_not_using_indexes：记录未使用索引的查询
  • log_timestamps：控制日志时区，建议设为SYSTEM使用系统时区
  • log_output：日志输出方式，FILE表示输出到文件，TABLE表示输出到mysql.slow_log表

验证配置

配置完成后，可以通过以下命令验证慢查询日志是否已正确启用：

-- 查看慢查询日志状态
SHOW VARIABLES LIKE 'slow_query_log';

-- 查看当前慢查询阈值
SHOW VARIABLES LIKE 'long_query_time';

-- 查看慢查询日志文件位置
SHOW VARIABLES LIKE 'slow_query_log_file';

日志分析

慢查询日志格式

日志格式如下：

# Time: 2023-08-20T14:59:00.000000Z
# User@Host: root[root] @ localhost []
# Query_time: 3.500000 Lock_time: 0.000150 Rows_sent: 1 Rows_examined: 100000
SET timestamp=1692536340;
SELECT * FROM users WHERE created_at = '2023-08-20';

字段说明：

  • Time: SQL 语句的执行时间
  • User@Host: 执行 SQL 语句的用户名和来源 IP
  • Query_time: SQL 语句的执行时间，单位为秒
  • Lock_time: 锁等待时间，单位为秒
  • Rows_sent: 返回给客户端的行数
  • Rows_examined: 扫描的行数

使用分析工具

mysqldumpslow 工具

Mysql 自带的 mysqldumpslow 工具可以分析慢查询日志文件，生成统计报告。

常用命令示例：

# 查看最慢的10条SQL语句(按执行时间排序)
mysqldumpslow -s t -t 10 /var/log/mysql/slow-query.log

# 查看出现次数最多的10条SQL语句(按出现次数排序)
mysqldumpslow -s c -t 10 /var/log/mysql/slow-query.log

# 查看扫描行数最多的10条SQL语句(按扫描行数排序)
mysqldumpslow -s r -t 10 /var/log/mysql/slow-query.log

参数说明：

  • -s：排序方式

    • t：按执行时间排序
    • c：按出现次数排序
    • r：按扫描行数排序

  • -t：指定显示的条目数量

pt-query-digest 工具​​

Percona Toolkit中的pt-query-digest工具提供更强大的分析功能，可以生成更详细的报告：

pt-query-digest /var/log/mysql/slow-query.log

PROFILE

PROFILE 是 MySQL 提供的一种性能分析机制，它可以显示 SQL 语句执行的详细信息，包括系统锁、表锁等占用的时间，以及对 CPU 和 I/O 资源的消耗情况。通过 PROFILE，开发者可以获取查询执行的各个阶段耗时，如初始化、打开表、系统锁等待等。

启用 PROFILE 功能

PROFILE 默认是关闭的，需要手动开启：

-- 查看当前PROFILE状态
SELECT @@profiling;

-- 启用PROFILE
SET profiling = 1;  -- 或 SET profiling = ON;

在生产环境中通常建议保持关闭状态，以避免对性能造成影响

执行 SQL 查询

启用PROFILE后，执行需要分析的SQL语句

查看 PROFILE 结果

执行查询后，可以使用以下命令查看分析结果：

-- 查看所有已记录的查询及其Query_ID
SHOW PROFILES;

-- 查看特定查询的详细PROFILE(1为Query_ID)
SHOW PROFILE FOR QUERY 1;

-- 查看CPU和IO消耗
SHOW PROFILE CPU, BLOCK IO FOR QUERY 1;

PROFILE默认保留最近15条查询记录，可以通过以下命令调整记录数量：

SET profiling_history_size = 100;  -- 修改为保留100条

输出示例：

+----------------------+----------+
| Status               | Duration |
+----------------------+----------+
| starting             | 0.000065 |
| checking permissions | 0.000010 |
| Opening tables       | 0.000022 |
| init                 | 0.000018 |
| System lock          | 0.000011 |
| optimizing           | 0.000006 |
| statistics           | 0.000015 |
| preparing            | 0.000013 |
| executing            | 0.000003 |
| Sending data         | 0.000045 |
| end                  | 0.000004 |
| query end            | 0.000009 |
| closing tables       | 0.000008 |
| freeing items        | 0.000012 |
| cleaning up          | 0.000010 |
+----------------------+----------+

重点关注以下几个阶段：

  • ​​Sending data​​：数据传输耗时（可能涉及磁盘读取或复杂计算）
  • ​​System lock​​：锁等待时间
  • ​​Creating tmp table​​：临时表创建（可能导致性能问题）

PROFILE还可以显示多种类型的资源消耗信息：

  • CPU时间：执行查询所用的CPU时间
  • 内存使用：执行查询所用的内存
  • I/O操作：执行查询时的输入输出操作
  • 行数统计：查询返回的行数

虽然PROFILE功能强大，但也有以下限制需要注意：

  1. ​​平台限制​​：PROFILE需要调用系统的getrusage()函数，因此只在Linux/Unix类平台上可用，不能在Windows平台上使用
  2. ​​性能影响​​：使用PROFILE会产生额外的性能开销，特别是在处理大量查询时。因此，在生产环境中应谨慎使用，并在分析完成后及时关闭
  3. ​​会话限制​​：PROFILE的结果只对当前会话有效，一旦会话结束，结果将被清除
  4. ​​版本兼容性​​：从MySQL 5.7.7版本开始，PROFILE功能被标记为已弃用(deprecated)，并在以后的版本中完全移除。官方推荐使用Performance Schema来代替

结尾

MySQL 性能优化是一个持续迭代的过程，需要结合 EXPLAIN、慢查询日志和 PROFILE 等工具进行全方位分析。通过本文的学习，我们已经掌握了这些核心工具的使用方法和优化技巧，但真正的能力提升还需要在实际工作中不断实践和总结。

avatar
文章
阅读
粉丝