MySQL - 进阶篇 - 2.5 SQL性能分析

---

MySQL - 进阶篇 - 2.5 SQL性能分析

在日常开发与数据库优化过程中，SQL 的执行效率往往决定了系统的整体性能。本节将从 执行频率、慢查询日志、Profile 详情、Explain 执行计划 四个角度入手，分析 SQL 的性能瓶颈与优化方法。

---

2.5.1 SQL执行频率

MySQL 客户端连接成功后，可以通过以下命令获取服务器的状态信息：

SHOW [SESSION|GLOBAL] STATUS;

• SESSION：查看当前会话的状态信息；

• GLOBAL：查看全局范围内的状态信息。

例如，查看数据库执行的增删改查频率：

SHOW GLOBAL STATUS LIKE 'Com_______';

常见关键参数：

• Com_insert：插入次数

• Com_delete：删除次数

• Com_update：更新次数

• Com_select：查询次数

👉 我们可以在数据库执行几次查询后，再次查看 Com_select 的值，就能验证查询频次是否增加。

通过这些参数，可以快速判断数据库的使用模式：

• 如果以 增删改 为主，则索引优化价值较低；

• 如果以 查询 为主，则需要重点考虑索引优化。

换句话说，SQL执行频率分析能帮助我们判断优化方向：到底是 写入压力大 还是 查询压力大。

---

2.5.2 慢查询日志

慢查询日志 会记录所有执行时间超过指定阈值（long_query_time，单位秒，默认 10s）的 SQL 语句。

查看是否开启慢查询日志：

SHOW VARIABLES LIKE 'slow_query_log';

开启慢查询日志需要修改 MySQL 配置文件（/etc/my.cnf）：

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

# 设置慢日志阈值为2秒
long_query_time=2

重启 MySQL 服务：

systemctl restart mysqld

日志文件路径：

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

测试：

SELECT * FROM tb_user;  -- 执行耗时 0.00 sec，不会被记录
SELECT COUNT(*) FROM tb_sku; -- 表含1000w数据，执行耗时 13.35 sec，会被记录

最终在慢查询日志中只会记录 执行时间超过 2 秒的 SQL，快速 SQL 不会记录。
👉 借助慢查询日志，我们就能定位出 效率低下的 SQL，从而进行针对性优化。

---

2.5.3 Profile详情

show profiles 命令可以帮助我们了解 SQL 执行时间的分布情况。

1. 查看 MySQL 是否支持 profile：

SELECT @@have_profiling;

1. 开启 profiling：

SET profiling = 1;

1. 执行一些 SQL 测试：

SELECT * FROM tb_user;
SELECT * FROM tb_user WHERE id = 1;
SELECT * FROM tb_user WHERE name = '白起';
SELECT COUNT(*) FROM tb_sku;

1. 查看 SQL 执行耗时列表：

SHOW PROFILES;

1. 查看指定 SQL 的耗时详情：

SHOW PROFILE FOR QUERY 16;

1. 查看指定 SQL 的 CPU 使用情况：

SHOW PROFILE CPU FOR QUERY 16;

👉 借助 Profile，我们可以清晰地看到 SQL 执行在哪些阶段耗时较多，从而精准定位瓶颈（如解析、优化、执行）。

---

2.5.4 Explain

EXPLAIN 或 DESC 可以查看 MySQL 执行 SELECT 语句时的执行计划，分析表的连接方式、索引使用情况等。

语法：

EXPLAIN SELECT 字段列表 FROM 表名 WHERE 条件;

执行计划字段解析

字段	含义
id	查询的执行顺序。id 值相同时，顺序由上到下；id 值不同，数值越大越先执行。
select_type	SELECT 类型，如 SIMPLE（简单查询）、PRIMARY（主查询）、SUBQUERY（子查询）、UNION（联合查询）等。
type	连接类型，性能从好到差依次为：NULL > system > const > eq_ref > ref > range > index > all。
possible_key	查询可能使用到的索引。
key	实际使用到的索引，如果为 NULL 表示未使用索引。
key_len	索引使用的字节数，越短越好。
rows	MySQL 预估需要扫描的行数。
filtered	返回结果占需读取行数的百分比，值越大越好。

👉 借助 EXPLAIN，可以快速定位 SQL 是否使用了索引，以及是否存在全表扫描等性能问题。

---

总结

• SQL执行频率：判断数据库是查询密集型还是写入密集型。

• 慢查询日志：定位执行耗时过长的 SQL。

• Profile：分析 SQL 执行的各个阶段耗时。

• Explain：查看执行计划，判断索引是否生效。

这一整套工具链，是 MySQL 性能优化的必备技能。

---

理解

1. 理论理解

SQL 性能分析的核心目标是：找到性能瓶颈，进而针对性优化。
这一过程通常包含四个层次：

1. SQL执行频率

   • 通过 show status 系统变量，了解数据库是查询压力大，还是写入压力大。

   • 不同类型的系统，优化策略不同：查询为主的系统要关注索引优化；写入为主的系统要关注批量写、表结构和锁机制。

2. 慢查询日志

   • 专注于“超过阈值的 SQL”，即拖慢系统的“坏家伙”。

   • 通过 slow_query_log 捕获执行时间长的语句，缩小优化范围。

3. Profile 分析

   • 定位 SQL 在解析、优化、执行等环节的耗时分布，回答“时间耗在哪”。

   • 类似于程序中的性能分析器，帮助我们识别 CPU 或 I/O 瓶颈。

4. Explain 执行计划

   • 揭示 SQL 实际走了哪个索引，是顺序扫描还是范围查询。

   • 帮助判断是否存在索引未命中、冗余回表、全表扫描等问题。

理论上，这四个工具形成一个闭环：从宏观（频率/慢日志）到微观（Profile/Explain），逐步逼近问题根源。

---

2. 大厂实战理解

在大厂场景里，SQL 性能分析不是临时调优，而是一个 长期工程，往往由 DBA、后端、数据平台团队协同完成。

1. 监控体系常态化

   • BAT、字节等公司会通过 监控面板（Grafana + Prometheus） 持续采集 Com_select、Com_update、QPS、TPS 等指标。

   • 一旦发现异常，例如查询量陡增或 update 卡顿，会触发告警。

2. 慢查询日志与采样

   • 大厂不会直接全量开启慢查询日志，因为会拖累性能。

   • 常见做法是 采样记录：只记录一部分慢 SQL，或在业务低峰期拉取日志。

   • 这些 SQL 会定期上传到 SQL 审核平台（如阿里的 DMS、字节的内部 SQL 审核系统），由自动优化引擎打分并给出改写建议。

3. Explain 与索引优化闭环

   • 线上遇到性能问题时，DBA 会让开发提供 EXPLAIN 结果。

   • 常见改进策略包括：

     • 新建或调整联合索引；

     • 改写 SQL，避免隐式类型转换或函数包裹；

     • 利用覆盖索引，减少回表。

   • 例如，订单查询如果只需要 id, status, create_time，那么就可以建立 (user_id, status, create_time) 索引，并通过覆盖索引直接返回结果。

4. Profile 定位复杂 SQL

   • 在数据报表、搜索推荐等复杂查询中，show profiles 可以精确定位是排序、连接、还是文件扫描耗时过高。

   • 大厂常会结合 执行计划可视化工具（如阿里内部的 SQL Explorer）来辅助分析。

5. 与架构结合

   • SQL 优化只是第一步，如果发现瓶颈来自架构层面，通常会：

     • 热点数据 → 放 Redis 缓存；

     • 大规模日志分析 → 落到 ClickHouse/Hive；

     • 搜索类需求 → 下放到 ElasticSearch。

   • 换句话说，大厂并不会死磕某条 SQL，而是会 结合业务特征重新分配存储与计算。

---

✅ 总结：

• 理论层面：SQL 性能分析是一个由浅入深的过程，从宏观（频率）到微观（执行计划）。

• 大厂实战：强调的是体系化建设——监控告警、慢日志采样、SQL 审核平台、Explain & Profile 闭环，再结合架构手段进行最终优化。

---

大厂面试题

面试题 1：SQL执行频率

问题：
在 MySQL 中，你如何判断当前数据库是以读操作为主，还是以写操作为主？这些信息有什么用？

参考答案：
可以通过以下命令：

SHOW GLOBAL STATUS LIKE 'Com_______';

其中：

• Com_select → 查询次数

• Com_insert → 插入次数

• Com_update → 更新次数

• Com_delete → 删除次数

通过对比这些指标，可以判断数据库是读多写少还是写多读少，从而指导优化方向：

• 如果读为主，应重点优化 索引、缓存、查询语句；

• 如果写为主，则要考虑 批量写入、分库分表、减少索引数量。
  在大厂，这类指标会被接入监控系统，用于动态调优和容量规划。

---

面试题 2：慢查询日志

问题：
如果业务反馈某些查询很慢，你如何使用 MySQL 的慢查询日志来定位？

参考答案：

1. 查看慢查询日志是否开启：

SHOW VARIABLES LIKE 'slow_query_log';

1. 在配置文件 /etc/my.cnf 开启并设置阈值：

slow_query_log=1
long_query_time=2

1. 重启 MySQL 并查看日志文件：

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

1. 通过慢日志定位执行超过 2 秒的 SQL，再结合 EXPLAIN 分析执行计划进行优化。

在大厂，慢日志往往结合采样和自动分析工具，避免性能损耗。

---

面试题 3：Profile 分析

问题：
在 SQL 优化中，show profiles 有什么作用？它和慢查询日志的区别是什么？

参考答案：

• show profiles 可以展示 SQL 在各个阶段的耗时分布，例如解析、优化、执行、排序等。

• 适合用于 精细化分析单条 SQL 的瓶颈。

• 慢查询日志只记录超过阈值的 SQL，无法展示 SQL 内部耗时分布。

大厂常用场景：复杂 SQL 或报表查询，通过 show profiles 精确定位是排序、JOIN，还是 I/O 成为瓶颈。

---

面试题 4：Explain 执行计划

问题：
你在 Explain 结果中看到 type=ALL，说明了什么问题？如何优化？

参考答案：

• ALL 表示 全表扫描，这是性能最差的连接方式。

• 出现原因：缺乏合适索引、索引未命中、条件函数包裹或隐式类型转换。

• 优化方法：

  1. 为 where 条件列建立索引；

  2. 改写 SQL，避免 WHERE DATE(create_time)=... 这种情况；

  3. 使用覆盖索引减少回表。

大厂面试常考这一点，因为它直接考察候选人是否能从执行计划中快速识别性能问题。

---

面试题 5：综合应用

问题：
如果你发现一条 SQL 在慢查询日志中频繁出现，但 Explain 显示已经使用了索引，你会如何进一步排查？

参考答案：

1. 检查索引选择是否合理（是否走了低选择性索引，如 gender）。

2. 查看是否存在 回表，导致大量随机 I/O。

3. 使用 show profiles 分析 SQL 执行耗时分布，确认瓶颈点。

4. 考虑建立联合索引或覆盖索引，避免额外的回表操作。

5. 在大数据量场景，可能需要 分表或引入缓存层（如 Redis）。

这是常见的综合题，考察候选人能否形成 慢日志 → Explain → Profile → 架构优化 的完整排查思路。

---

大厂场景题

场景题 1：电商搜索服务

背景：
某电商平台有 products 表，包含 id, name, category_id, price, stock 等字段。高峰期搜索请求量极大。监控显示：

• Com_select 激增；

• 慢查询日志中频繁出现 SELECT * FROM products WHERE name LIKE '%手机%';

问题：
你会如何优化？

参考答案：

• LIKE '%关键词%' 无法使用普通索引 → 考虑使用 全文索引 (FULLTEXT) 或引入 ElasticSearch；

• 对 category_id, price 添加组合索引 (category_id, price)，优化分类 + 价格筛选场景；

• 高频搜索结果可以缓存至 Redis。
  👉 大厂场景下，全文检索类需求往往会下放到 ES，而不是仅依赖 MySQL。

---

场景题 2：订单系统的写入瓶颈

背景：
订单表 orders 有 12 个索引，业务主要是高并发写入。DBA 监控发现：

• Com_insert 很高，但 TPS（事务每秒）明显偏低；

• Explain 分析常见 type=ALL。

问题：
你会如何处理？

参考答案：

• 减少冗余索引，只保留必要的联合索引 (user_id, status, create_time)；

• 将部分查询下放缓存层，降低对索引的依赖；

• 对历史冷数据分表/分区，降低写入表大小；

• 必要时采用 异步写入 + MQ 解耦业务写请求。
  👉 在大厂，写多读少的表，策略是“索引精简 + 分库分表 + 缓存补充”。

---

场景题 3：日志系统的分析性能

背景：
logs 表每天新增 5000 万条数据，常见查询：

SELECT * FROM logs 
WHERE app_id = 200 AND log_time BETWEEN '2025-09-01' AND '2025-09-07';

监控发现该 SQL 常出现在慢查询日志。

问题：
如何优化？

参考答案：

• 对 (app_id, log_time) 建立联合索引；

• 按时间分区表，只扫描目标分区；

• 将日志历史数据迁移到 ClickHouse/Hive；

• Profile 分析若发现耗时主要在排序，可加 (app_id, log_time, level) 覆盖索引。
  👉 大厂日志系统往往采取 冷热分层 + 分区表 + 大数据引擎 的方案。

---

场景题 4：Explain 输出分析

背景：
Explain 结果如下：

type=ALL, rows=5000000, key=NULL

问题：
如何解读？该如何优化？

参考答案：

• ALL 表示全表扫描，说明没有索引可用；

• rows=500w，表示需要扫描 500 万行，性能很差；

• key=NULL，说明实际未命中索引；

• 优化手段：

  • 检查 WHERE 条件是否对索引字段做了函数操作或类型转换；

  • 建立合适索引，例如 (user_id, status)；

  • 通过覆盖索引减少回表操作。
    👉 在大厂，type=ALL 往往是性能问题的直观信号，常作为 SQL 审核的重点。

---

场景题 5：报表查询优化

背景：
某 BI 报表系统 SQL：

SELECT user_id, COUNT(*) 
FROM orders 
WHERE create_time >= '2025-01-01' 
GROUP BY user_id;

运行 20 分钟未出结果。

问题：
如何优化？

参考答案：

• Explain 可能显示 type=range，扫描量极大；

• 为 (create_time, user_id) 建立联合索引；

• 增加 物化汇总表（预聚合），存储每天的 user_id 订单量；

• 利用分布式数仓（如 Hive/ClickHouse）处理长周期统计需求。
  👉 大厂优化思路：预计算 + 索引优化 + 架构迁移，而不是单靠 MySQL 硬扛。