MySQL: MySQL 性能优化：慢查询分析与索引调优实战

最后更新：2026-08-26

Alice 的电商平台在年终促销时，数据库响应时间从 50ms 飙升到 5 秒，用户投诉页面打不开、订单提交超时。运维团队紧急排查发现，几条未优化的慢查询拖垮了整个系统。通过开启慢查询日志定位问题 SQL，用 EXPLAIN 分析执行计划，补充缺失索引并改写低效查询，再调优 InnoDB 配置参数，响应时间最终降回 100ms 以内。

1. 你将学到

慢查询日志的配置、采集与分析方法
EXPLAIN 执行计划各字段（type/key/rows/Extra）的深度解读
索引优化核心策略：避免失效、覆盖索引、联合索引最左前缀
查询改写技巧：避免 SELECT *、子查询改 JOIN、深分页优化
关键配置参数调优：innodb_buffer_pool_size、max_connections 等

2. 性能优化全景流程

性能优化不是一次性的工作，而是"发现→分析→优化→验证"的循环过程。

flowchart TD
    A[发现慢查询] --> B[EXPLAIN 分析执行计划]
    B --> C{问题类型？}
    C -->|缺少索引| D[索引优化]
    C -->|查询写法低效| E[查询改写]
    C -->|配置不合理| F[配置调优]
    D --> G[验证性能提升]
    E --> G
    F --> G
    G -->|仍不达标| A
    G -->|达标| H[上线监控]

3. 慢查询日志

慢查询日志是定位性能问题的第一道防线，它自动记录执行时间超过阈值的 SQL 语句。

(1) 开启与配置

SQL

SET GLOBAL slow_query_log = ON;
SET GLOBAL long_query_time = 1;
SET GLOBAL log_queries_not_using_indexes = ON;
SHOW VARIABLES LIKE 'slow_query%';
SHOW VARIABLES LIKE 'long_query_time';

(2) 日志分析工具

mysqldumpslow 是 MySQL 自带的慢日志汇总工具，按查询时间或出现次数排序。

BASH

mysqldumpslow -s t -t 10 /var/lib/mysql/slow.log

(3) Performance Schema 替代方案

MySQL 5.6+ 可用 Performance Schema 采集慢查询，无需修改配置文件即可动态开启。

SQL

UPDATE performance_schema.setup_consumers
SET ENABLED = 'YES' WHERE NAME = 'events_statements_history_long';

▶ 示例：开启慢查询日志并定位 Top 5 慢查询

SQL

SET GLOBAL slow_query_log = ON;
SET GLOBAL long_query_time = 0.5;
SET GLOBAL min_examined_row_limit = 100;
SHOW VARIABLES LIKE 'slow_query_log_file';

▶ 试一试

BASH

mysqldumpslow -s t -t 5 /var/lib/mysql/slow.log

▶ 示例：用 sys 视图快速查看慢查询

SQL

SELECT query_id, LEFT(query, 80) AS query_text,
       exec_count, avg_timer_ms, rows_examined
FROM sys.statements_with_runtimes_in_95th_percentile
ORDER BY avg_timer_ms DESC LIMIT 10;

▶ 试一试

4. EXPLAIN 执行计划深度解读

EXPLAIN 是 SQL 调优最核心的诊断工具，它能展示 MySQL 如何执行查询。

(1) EXPLAIN 输出字段概览

字段	含义	关注重点
id	查询序号	子查询的执行顺序
select_type	查询类型	避免 DERIVED、UNCACHEABLE
table	访问的表	关联表的数量
type	访问类型	从 system 到 ALL，越左越好
possible_keys	可能用到的索引	与 key 对比判断索引选择
key	实际使用的索引	NULL 表示未用索引
key_len	索引使用长度	判断联合索引用了几个字段
rows	预估扫描行数	越小越好
Extra	额外信息	Using filesort/Using temporary 需优化

(2) type 字段详解

type 是 EXPLAIN 中最关键的字段，直接反映查询效率。

type 值	含义	扫描方式	性能评级
system	表中只有一行	直接读取	★★★★★
const	主键/唯一索引等值查询	最多一行匹配	★★★★★
eq_ref	关联查询中主键/唯一索引	每行关联一行	★★★★☆
ref	非唯一索引等值查询	匹配多行	★★★☆☆
range	索引范围扫描	BETWEEN/IN/>/<	★★★☆☆
index	全索引扫描	遍历整棵索引树	★★☆☆☆
ALL	全表扫描	遍历整张表	★☆☆☆☆

(3) Extra 字段关键值

Using index：覆盖索引，无需回表，性能最佳
Using where：存储引擎返回数据后由 Server 层过滤
Using filesort：无法用索引排序，需额外排序操作
Using temporary：使用了临时表，常见于 GROUP BY 无索引
Using index condition：索引下推（ICP），减少回表次数

▶ 示例：EXPLAIN 分析单表查询

SQL

EXPLAIN SELECT order_id, user_id, total_amount
FROM orders
WHERE user_id = 42 AND status = 'PAID';

▶ 试一试

TEXT 📖 仅展示

+----+-------------+--------+------+---------------+------+---------+------+------+-----------------------+
| id | select_type | table  | type | possible_keys | key  | key_len | rows | Extra                     |
+----+-------------+--------+------+---------------+------+---------+------+------+-----------------------+
|  1 | SIMPLE      | orders | ref  | idx_user      | idx_user | 4    |  120 | Using where               |
+----+-------------+--------+------+---------------+------+---------+------+------+-----------------------+

▶ 示例：EXPLAIN 分析关联查询

SQL

EXPLAIN SELECT o.order_id, u.username
FROM orders o
JOIN users u ON o.user_id = u.id
WHERE o.create_time > '2025-01-01';

▶ 试一试

5. 索引优化策略

索引是查询加速的利器，但用错索引比没索引更危险。

(1) 覆盖索引

覆盖索引指查询的所有字段都包含在索引中，无需回表读取行数据。

SQL

ALTER TABLE orders ADD INDEX idx_cover (user_id, status, total_amount);

查询 SELECT user_id, status, total_amount FROM orders WHERE user_id = 42 可完全从索引获取数据。

(2) 联合索引与最左前缀

联合索引遵循最左前缀原则：查询条件必须从索引最左列开始匹配。

SQL

ALTER TABLE orders ADD INDEX idx_user_status_time (user_id, status, create_time);

查询条件	能否命中索引	原因
WHERE user_id = 1	✅	匹配最左列
WHERE user_id = 1 AND status = 'PAID'	✅	匹配前两列
WHERE user_id = 1 AND create_time > '2025-01-01'	⚠️	只命中 user_id，跳过 status
WHERE status = 'PAID'	❌	缺少最左列 user_id

(3) 索引失效场景速查

失效场景	错误写法	正确写法
对索引列使用函数	`WHERE YEAR(create_time) = 2025`	`WHERE create_time >= '2025-01-01' AND create_time < '2026-01-01'`
隐式类型转换	`WHERE varchar_col = 123`	`WHERE varchar_col = '123'`
左模糊查询	`WHERE name LIKE '%alice'`	`WHERE name LIKE 'alice%'`
OR 连接非索引列	`WHERE indexed_col = 1 OR unindexed = 2`	拆分为 UNION 或给 unindexed 加索引
不等于判断	`WHERE status != 'PAID'`	`WHERE status IN ('UNPAID', 'CANCELLED')`
索引列参与计算	`WHERE id + 1 = 100`	`WHERE id = 99`

▶ 示例：联合索引最左前缀验证

SQL

ALTER TABLE products ADD INDEX idx_cat_brand_price (category_id, brand_id, price);

EXPLAIN SELECT * FROM products WHERE category_id = 5 AND brand_id = 10;
EXPLAIN SELECT * FROM products WHERE brand_id = 10;

▶ 试一试

第二条查询 type 为 ALL，因为跳过了最左列 category_id。

▶ 示例：覆盖索引消除回表

SQL

ALTER TABLE orders ADD INDEX idx_user_status_amount (user_id, status, total_amount);

EXPLAIN SELECT user_id, status, total_amount
FROM orders WHERE user_id = 42;

▶ 试一试

Extra 列出现 Using index，说明无需回表。

6. 查询优化技巧

即使有了索引，写法不当的查询仍然无法利用索引优势。

(1) 避免 SELECT *

SELECT * 会读取所有列，增加 I/O 并可能使覆盖索引失效。

SQL

SELECT id, username, email FROM users WHERE id = 100;

(2) 子查询改写为 JOIN

相关子查询每行执行一次，改写为 JOIN 可大幅减少扫描次数。

SQL

SELECT o.order_id, o.total_amount
FROM orders o
WHERE o.user_id IN (SELECT id FROM users WHERE vip_level >= 3);

优化为：

SQL

SELECT o.order_id, o.total_amount
FROM orders o
JOIN users u ON o.user_id = u.id AND u.vip_level >= 3;

(3) 深分页优化

传统 LIMIT offset, n 在 offset 很大时性能极差。

SQL

SELECT * FROM orders ORDER BY id LIMIT 1000000, 10;

优化方案——游标分页：

SQL

SELECT * FROM orders WHERE id > 1000000 ORDER BY id LIMIT 10;

(4) 查询优化技巧速查

优化技巧	反模式	推荐写法	适用场景
避免 SELECT *	`SELECT *`	只查需要的列	所有查询
子查询改 JOIN	`WHERE IN (SELECT ...)`	`JOIN ... ON ...`	关联查询
游标分页	`LIMIT 100000, 10`	`WHERE id > last_id LIMIT 10`	深分页
批量插入	循环单条 INSERT	`INSERT INTO ... VALUES (...),(...),(...)`	数据导入
避免大事务	长时间持有锁	拆分为小事务	高并发写入

▶ 示例：深分页优化对比

SQL

SELECT * FROM orders ORDER BY create_time LIMIT 500000, 20;

▶ 试一试

优化为延迟关联：

SQL

SELECT o.* FROM orders o
JOIN (SELECT id FROM orders ORDER BY create_time LIMIT 500000, 20) t
ON o.id = t.id;

子查询只查主键，利用覆盖索引快速定位 id，再回表取完整数据。

▶ 示例：批量插入优化

SQL

INSERT INTO order_items (order_id, product_id, quantity)
VALUES (1001, 201, 2), (1001, 202, 1), (1001, 203, 5);

▶ 试一试

比循环执行 3 次单条 INSERT 减少了 2 次网络往返和事务提交开销。

7. 表结构优化

合理的表结构设计是性能的基础，改写 SQL 只能在既定结构上优化。

(1) 字段类型选择

数据类型	推荐选择	原因
主键	BIGINT UNSIGNED	自增整数，B+Tree 插入效率高
状态/枚举	TINYINT	1 字节存储，配合 CHECK 约束
金额	DECIMAL(10,2)	精确计算，避免浮点误差
短文本	VARCHAR(N)	按实际长度分配空间
长文本	TEXT	单独存储，避免溢出影响主记录
时间	DATETIME / TIMESTAMP	TIMESTAMP 占 4 字节但范围有限
布尔	TINYINT(1)	MySQL 无原生 BOOLEAN

(2) 反范式设计

在高查询压力的场景下，适度冗余可减少 JOIN 操作。

SQL

CREATE TABLE order_summary (
    order_id BIGINT PRIMARY KEY,
    user_id BIGINT,
    username VARCHAR(64),
    total_amount DECIMAL(10,2),
    INDEX idx_user (user_id)
);

将 username 冗余到订单表，避免每次查询都 JOIN users 表。

▶ 示例：字段类型优化对比

SQL

ALTER TABLE products MODIFY COLUMN weight DECIMAL(8,2);
ALTER TABLE products MODIFY COLUMN description TEXT;

▶ 试一试

将 weight 从 VARCHAR 改为 DECIMAL，description 从 VARCHAR(5000) 改为 TEXT，减少主记录长度。

8. 配置参数调优

MySQL 的配置参数直接影响存储引擎的行为和资源分配。

(1) 关键配置参数与推荐值

参数	说明	推荐值	调优依据
innodb_buffer_pool_size	InnoDB 缓冲池大小	物理内存的 60%-80%	缓存数据页和索引页，减少磁盘 I/O
innodb_log_file_size	redo log 单文件大小	256M-1G	过小导致频繁 checkpoint
max_connections	最大并发连接数	200-500	过大浪费内存，过小拒绝连接
innodb_flush_method	刷盘方式	O_DIRECT	绕过 OS 缓冲，避免双缓存
sync_binlog	binlog 同步频率	1（安全）/ 100（性能）	1 为每次提交同步，最安全
innodb_io_capacity	InnoDB I/O 能力	SSD: 2000 / HDD: 200	影响后台刷脏页速度
query_cache_type	查询缓存开关	OFF	MySQL 8.0 已移除，5.7 建议关闭

(2) 动态调整参数

部分参数支持在线修改，无需重启实例。

SQL

SET GLOBAL innodb_buffer_pool_size = 8589934592;
SHOW VARIABLES LIKE 'innodb_buffer_pool_size';

▶ 示例：查看当前 InnoDB 缓冲池命中率

SQL

SHOW STATUS LIKE 'Innodb_buffer_pool_read%';

▶ 试一试

TEXT 📖 仅展示

+---------------------------------------+-----------+
| Variable_name                         | Value     |
+---------------------------------------+-----------+
| Innodb_buffer_pool_read_requests      | 1024000   |
| Innodb_buffer_pool_reads              | 512       |
+---------------------------------------+-----------+

命中率 = 1 - (512 / 1024000) ≈ 99.95%，缓冲池大小合理。

9. Performance Schema 入门

Performance Schema 是 MySQL 内置的性能监控引擎，提供比慢查询日志更细粒度的诊断数据。

(1) 开启 Performance Schema

SQL

SHOW VARIABLES LIKE 'performance_schema';
UPDATE performance_schema.setup_instruments
SET ENABLED = 'YES', TIMED = 'YES'
WHERE NAME LIKE '%statement/%';

(2) 常用监控视图

sys 视图	用途
sys.statements_with_runtimes_in_95th_percentile	95 分位慢查询
sys.schema_index_statistics	索引使用统计
sys.memory_by_host_by_current_bytes	各连接内存占用
sys.io_by_thread_by_latency	I/O 延迟分布

▶ 示例：查看未使用的索引

SQL

SELECT object_schema, object_name, index_name
FROM performance_schema.table_io_waits_summary_by_index_usage
WHERE index_name IS NOT NULL
  AND count_star = 0
  AND object_schema = 'ecommerce'
ORDER BY object_name;

▶ 试一试

未使用的索引浪费存储空间，且 INSERT/UPDATE 时需额外维护，应及时清理。

10. 综合实战：完整性能诊断流程

以 Alice 的电商平台为例，演示从发现慢查询到验证提升的完整流程。

SQL

SET GLOBAL slow_query_log = ON;
SET GLOBAL long_query_time = 1;
SET GLOBAL log_queries_not_using_indexes = ON;

BASH

mysqldumpslow -s t -t 5 /var/lib/mysql/slow.log

TEXT 📖 仅展示

Count: 328  Time=4.52s  Rows=1.0  Rows_examined=890000
SELECT * FROM orders WHERE YEAR(create_time)=2025 AND status='PAID';

SQL

EXPLAIN SELECT * FROM orders
WHERE YEAR(create_time) = 2025 AND status = 'PAID';

TEXT 📖 仅展示

type: ALL | key: NULL | rows: 890000 | Extra: Using where

索引失效原因：对 create_time 使用了 YEAR() 函数。修复方案：

SQL

ALTER TABLE orders ADD INDEX idx_status_time (status, create_time);

SELECT order_id, user_id, total_amount
FROM orders
WHERE create_time >= '2025-01-01' AND create_time < '2026-01-01'
  AND status = 'PAID';

SQL

EXPLAIN SELECT order_id, user_id, total_amount
FROM orders
WHERE create_time >= '2025-01-01' AND create_time < '2026-01-01'
  AND status = 'PAID';

TEXT 📖 仅展示

type: range | key: idx_status_time | rows: 3200 | Extra: Using index condition

扫描行数从 890000 降到 3200，查询时间从 4.5s 降到 0.08s。最后调整缓冲池：

SQL

SET GLOBAL innodb_buffer_pool_size = 8589934592;

❓ 常见问题

Q 慢查询日志会影响生产性能吗？

A 影响极小。long_query_time 设为 1 秒时，只有超时查询才写日志，写入开销可忽略。若担心 I/O 压力，可将日志输出到文件而非表，或使用 Performance Schema 替代。

Q EXPLAIN 的 rows 字段准确吗？

A rows 是基于统计信息的预估值，不是精确值。对于数据分布不均匀的列，预估值可能偏差较大。可用 ANALYZE TABLE 更新统计信息提高准确度。

Q 索引越多查询越快吗？

A 不是。索引会占用磁盘空间，且每次 INSERT/UPDATE/DELETE 都需维护所有索引。建议单表索引不超过 5-6 个，优先使用联合索引减少索引总数。

Q 什么时候应该考虑分库分表？

A 当单表数据量超过 5000 万行，且 SQL 优化和索引优化已达瓶颈时再考虑。分库分表会增加系统复杂度（跨库 JOIN、分布式事务），不应作为首选方案。

Q innodb_buffer_pool_size 设多大合适？

A 专用数据库服务器建议设为物理内存的 60%-80%。如果是共享服务器，需留足 OS 和其他进程的内存。可通过 Innodb_buffer_pool_reads 命中率判断：低于 99% 说明缓冲池偏小。

Q 查询中出现 Using filesort 一定需要优化吗？

A 不一定。如果结果集很小（几十行），filesort 在内存中完成，开销可忽略。只有当排序行数很大且导致临时文件写入磁盘时才需优化，可通过增加 sort_buffer_size 缓解。

Q 如何判断索引是否被使用？

A 用 sys.schema_unused_indexes 视图查询从未使用过的索引，或在 Performance Schema 中查看 table_io_waits_summary_by_index_usage 的 count_star 为 0 的记录。

📖 小节

慢查询日志是性能优化的起点，用 mysqldumpslow 或 sys 视图快速定位瓶颈
EXPLAIN 的 type 字段从 system 到 ALL 逐级变差，目标是至少达到 ref 级别
索引优化核心：避免函数/隐式转换导致失效，善用覆盖索引和联合索引最左前缀
查询改写：SELECT * 改精准列、子查询改 JOIN、深分页用游标或延迟关联
配置调优：innodb_buffer_pool_size 是最关键参数，缓冲池命中率应保持 99% 以上
Performance Schema 提供细粒度监控，可发现慢日志遗漏的高频短查询和未使用索引

📝 作业

基础题（难度⭐）：开启慢查询日志，设置阈值为 0.5 秒，用 mysqldumpslow 找出执行次数最多的 Top 5 慢查询。
进阶题（难度⭐⭐）：对一条 type 为 ALL 的查询，用 EXPLAIN 分析后添加合适的索引，验证 type 提升到 ref 或 range 级别。
挑战题（难度⭐⭐⭐）：设计一张 100 万行订单表的索引方案，用覆盖索引优化查询 SELECT user_id, status, total_amount FROM orders WHERE user_id = ? AND status = ?，对比优化前后 EXPLAIN 的 rows 和 Extra 字段差异。
实战题（难度⭐⭐⭐）：将一条包含子查询的慢 SQL 改写为 JOIN，再配合索引优化，使查询时间从秒级降到百毫秒以内，记录完整优化过程。

Web-Tutorial 技术团队

由多位开发者共同维护的编程教程平台。每篇教程由对应领域的开发者编写和审核，确保内容准确可靠。如发现任何问题，欢迎向我们反馈。

关于我们

MySQL: MySQL 性能优化：慢查询分析与索引调优实战

1. 你将学到

2. 性能优化全景流程

3. 慢查询日志

(1) 开启与配置

(2) 日志分析工具

(3) Performance Schema 替代方案

▶ 示例：开启慢查询日志并定位 Top 5 慢查询

▶ 示例：用 sys 视图快速查看慢查询

4. EXPLAIN 执行计划深度解读

(1) EXPLAIN 输出字段概览

(2) type 字段详解

(3) Extra 字段关键值

▶ 示例：EXPLAIN 分析单表查询

▶ 示例：EXPLAIN 分析关联查询

5. 索引优化策略

(1) 覆盖索引

(2) 联合索引与最左前缀

(3) 索引失效场景速查

▶ 示例：联合索引最左前缀验证

▶ 示例：覆盖索引消除回表

6. 查询优化技巧

(1) 避免 SELECT *

(2) 子查询改写为 JOIN

(3) 深分页优化

(4) 查询优化技巧速查

▶ 示例：深分页优化对比

▶ 示例：批量插入优化

7. 表结构优化

(1) 字段类型选择

(2) 反范式设计

▶ 示例：字段类型优化对比

8. 配置参数调优

(1) 关键配置参数与推荐值

(2) 动态调整参数

▶ 示例：查看当前 InnoDB 缓冲池命中率

9. Performance Schema 入门

(1) 开启 Performance Schema

(2) 常用监控视图

▶ 示例：查看未使用的索引

10. 综合实战：完整性能诊断流程

❓ 常见问题

📖 小节

📝 作业

🙏 帮我们做得更好