MySQL 调优指南：从慢如蜗牛到快如闪电

螺旋星系

404人浏览 · 2026-04-03 17:31:03

螺旋星系 · 2026-04-03 17:31:03 发布

MySQL 调优指南：从慢如蜗牛到快如闪电

当你的查询慢到可以去泡杯咖啡回来还没结果时，是时候看看这篇文档了。
别担心，我们都是从这个阶段过来的——那个以为自己写出了 SQL 之王的阶段。

📋 目录

慢查询：你的数据库在摸鱼
索引：不是加了就有用
EXPLAIN：SQL 的体检报告
锁问题：当数据库变成停车场
配置调优：默认配置是个坑
实战案例集锦

1. 慢查询：你的数据库在摸鱼

😱 问题现场

-- 这个查询跑了 30 秒
SELECT * FROM orders WHERE user_id = 123;

30 秒！ 这时候用户已经：

刷新了 3 次页面
怀疑网速有问题
准备去竞争对手网站了

🔍 问题原因

1.1 全表扫描（Table Scan）

-- 没有索引的查询
SELECT * FROM users WHERE email = 'user@example.com';

数据库内心戏：“好吧，我把这 100 万行用户记录从头到尾看一遍，找找看哪个邮箱匹配…”

这就是全表扫描，相当于让你在没有目录的 1000 页书中找一个句子。

1.2 SELECT * 的陷阱

SELECT * FROM orders;  -- ❌ 别这么干！

问题：

读取不必要的列，浪费 I/O
增加网络传输开销
可能导致索引失效（覆盖索引用不上）

1.3 隐式类型转换

-- user_id 是 VARCHAR，但你用了数字
SELECT * FROM users WHERE user_id = 123;  -- ❌

MySQL：“这个 123 是什么？字符串？数字？算了，我先把所有 user_id 转成数字再比较…”

结果：索引失效，全表扫描。

🛠️ 排查过程

第一步：开启慢查询日志

-- 查看慢查询配置
SHOW VARIABLES LIKE 'slow_query%';

-- 开启慢查询日志
SET GLOBAL slow_query_log = 'ON';
SET GLOBAL long_query_time = 1;  -- 超过 1 秒的查询记录下来

日志文件位置：/var/log/mysql/slow-query.log

第二步：分析慢查询日志

使用 mysqldumpslow 工具：

# 查看最慢的 10 条查询
mysqldumpslow -s t -t 10 /var/log/mysql/slow-query.log

# 按平均执行时间排序
mysqldumpslow -s at -t 10 /var/log/mysql/slow-query.log

或者使用 pt-query-digest（更强大）：

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

输出示例：

# Profile
# Rank Query ID           Response time   Calls   R/Call   V/M   Item
# ==== ================== ============= ====== ======== ===== ===============
#    1 0x9A1B2C3D4E5F6A7B 45.2341 s     12342  0.003665  0.10 SELECT users?

第三步：定位具体查询

找到慢查询后，手动测试并分析：

-- 复制慢查询，加上 EXPLAIN
EXPLAIN SELECT * FROM orders WHERE user_id = 123;

💡 解决方法

1. 添加合适的索引

-- 为 user_id 创建索引
CREATE INDEX idx_user_id ON orders(user_id);

-- 组合索引（注意顺序！）
CREATE INDEX idx_user_status_date ON orders(user_id, status, created_at);

索引设计原则：

✅ 经常作为 WHERE 条件的列
✅ JOIN 关联的列
✅ ORDER BY / GROUP BY 的列
❌ 频繁更新的列（更新成本高）
❌ 区分度低的列（如性别、状态）

2. 避免SELECT *

-- ✅ 只查询需要的列
SELECT id, user_id, amount, created_at
FROM orders
WHERE user_id = 123;

3. 修复类型转换问题

-- ✅ 使用正确的类型
SELECT * FROM users WHERE user_id = '123';  -- 字符串用引号

2. 索引：不是加了就有用

😤 常见误区

开发者：“我给所有字段都加了索引，应该很快了吧！”

MySQL：“你以为索引是免费的午餐？每次 INSERT/UPDATE/DELETE 我都要更新这些索引，累死我了…”

🔍 索引失效的 10 种情况

情况	示例	为什么失效
1. 使用函数	`WHERE YEAR(created_at) = 2024`	函数破坏了索引树的有序性
2. LIKE 前缀通配符	`WHERE name LIKE '%张%'`	无法使用索引范围扫描
3. OR 连接无索引字段	`WHERE indexed_col = 1 OR non_indexed = 2`	优化器放弃索引
4. 联合索引不满足最左前缀	索引(a,b,c)，查询 `WHERE b = 1`	跳过了最左边的 a
5. 隐式转换	`WHERE varchar_col = 123`	类型转换导致无法用索引
6. 不等于操作	`WHERE status != 1`	范围太分散
7. IS NULL（可能）	`WHERE col IS NULL`	取决于存储引擎和版本
8. 负向查询	`WHERE NOT IN (...)`	优化器选择全表扫描
9. 数据类型不匹配	`WHERE int_col = '123'`	隐式转换
10. 小表全表扫描更快	表只有 100 行	优化器：直接扫描比走索引快

🛠️ 索引优化实战

案例：联合索引的"最左前缀"原则

-- 创建联合索引
CREATE INDEX idx_abc ON users(age, city, name);

-- ✅ 能用索引
WHERE age = 25;                           -- 用到 age
WHERE age = 25 AND city = '北京';         -- 用到 age, city
WHERE age = 25 AND city = '北京' AND name = '张三';  -- 全用上

-- ❌ 不能用索引（或部分用）
WHERE city = '北京';                      -- 跳过了 age
WHERE name = '张三';                      -- 跳过了 age, city
WHERE age = 25 AND name = '张三';         -- city 断了

记忆口诀：联合索引就像爬山，必须从第一阶梯开始，不能跳级！

案例：LIKE 优化

-- ❌ 索引失效
SELECT * FROM products WHERE name LIKE '%手机%';

-- ✅ 索引生效（前缀匹配）
SELECT * FROM products WHERE name LIKE 'iPhone%';

-- 💡 替代方案：全文索引
ALTER TABLE products ADD FULLTEXT INDEX ft_name(name);
SELECT * FROM products WHERE MATCH(name) AGAINST('手机');

案例：覆盖索引（Covering Index）

-- 创建索引
CREATE INDEX idx_user_date ON orders(user_id, created_at, amount);

-- ✅ 这个查询只需要读索引，不需要回表
SELECT user_id, created_at, amount
FROM orders
WHERE user_id = 123;

EXPLAIN 显示：Extra: Using index（完美！）

3. EXPLAIN：SQL 的体检报告

📊 读懂 EXPLAIN 输出

EXPLAIN SELECT * FROM orders o
JOIN users u ON o.user_id = u.id
WHERE o.status = 1;

输出解读：

列	说明	理想值
id	查询序列号	—
select_type	查询类型	SIMPLE（简单查询）
table	访问的表	—
type	访问类型	ref > range > index > ALL
possible_keys	可能使用的索引	—
key	实际使用的索引	不为空
key_len	使用的索引长度	越长越好（联合索引）
ref	索引比较的列	—
rows	预估扫描行数	越少越好
Extra	额外信息	Using index（覆盖索引）

🎯 type 字段详解（从好到坏）

-- 1. const：主键/唯一索引等值查询（最好）
EXPLAIN SELECT * FROM users WHERE id = 1;
-- type: const

-- 2. eq_ref：JOIN 时使用主键/唯一索引
EXPLAIN SELECT * FROM orders o JOIN users u ON o.user_id = u.id;
-- type: eq_ref（u 表）

-- 3. ref：非唯一索引等值查询
EXPLAIN SELECT * FROM orders WHERE user_id = 123;
-- type: ref

-- 4. range：范围查询
EXPLAIN SELECT * FROM orders WHERE created_at > '2024-01-01';
-- type: range

-- 5. index：索引全扫描
EXPLAIN SELECT user_id FROM orders;
-- type: index

-- 6. ALL：全表扫描（最差，需要优化！）
EXPLAIN SELECT * FROM orders WHERE amount > 100;
-- type: ALL

🔑 Extra 字段解读

Extra 值	含义	好坏
Using index	覆盖索引，不需要回表	✅ 完美
Using where	WHERE 过滤	✅ 正常
Using index condition	索引条件下推	✅ 较好
Using filesort	需要文件排序（内存或磁盘）	❌ 优化
Using temporary	使用临时表	❌ 优化
Using join buffer	JOIN 缓冲	⚠️ 可能需要优化

🛠️ 实战优化案例

问题：Using filesort

-- 查询
EXPLAIN SELECT * FROM orders WHERE user_id = 123 ORDER BY created_at DESC;

-- 输出
-- Extra: Using filesort  ❌

原因：索引不包含 ORDER BY 的列

解决：

-- 创建包含排序字段的索引
CREATE INDEX idx_user_date ON orders(user_id, created_at);

-- 再次查询
EXPLAIN SELECT * FROM orders WHERE user_id = 123 ORDER BY created_at DESC;
-- Extra: Using index condition  ✅

4. 锁问题：当数据库变成停车场

🚗 锁的比喻

共享锁（Shared Lock，S 锁）：读锁，多人可以同时读
排他锁（Exclusive Lock，X 锁）：写锁，只有一个人能写，其他人等
意向锁：表明"我打算加锁"，提前声明

😱 常见锁问题

4.1 行锁升级为表锁

-- 问题查询
UPDATE users SET name = '新名字' WHERE LOWER(name) = 'old name';

结果：行锁 → 表锁，整个表被锁定！

原因：函数破坏了索引，MySQL 不知道锁哪行

解决：

-- ✅ 改写查询
UPDATE users SET name = '新名字' WHERE name = 'old name';

4.2 死锁（Deadlock）

场景：

-- 事务 A
START TRANSACTION;
UPDATE users SET balance = balance - 100 WHERE id = 1;
-- 等待...

-- 事务 B
START TRANSACTION;
UPDATE users SET balance = balance + 100 WHERE id = 2;
UPDATE users SET balance = balance - 100 WHERE id = 1;  -- 等待 A
-- 此时 A 也尝试更新 id = 2...死锁！

MySQL 内心戏：“你们两个互相等着？没完没了了是吧？我只好杀一个…”

解决方法：

统一加锁顺序（最重要！）：

-- 所有事务都按相同顺序加锁
-- 比如总是按 id 从小到大

减少事务持有锁的时间：

-- ❌ 不好
START TRANSACTION;
UPDATE users SET balance = balance - 100 WHERE id = 1;
-- 执行一些耗时的操作...
sleep(5);
COMMIT;

-- ✅ 好
-- 快速执行，快速提交
START TRANSACTION;
UPDATE users SET balance = balance - 100 WHERE id = 1;
COMMIT;

设置死锁超时：

SET GLOBAL innodb_lock_wait_timeout = 10;  -- 10 秒后放弃

4.3 间隙锁（Gap Lock）的坑

-- 当前数据：id = 1, 5, 10
-- 事务 A
BEGIN;
SELECT * FROM users WHERE id > 1 AND id < 10 FOR UPDATE;
-- 锁定了 (1, 5) 和 (5, 10) 的间隙

-- 事务 B
BEGIN;
INSERT INTO users VALUES (3, 'new');  -- 被阻塞！

解决：

-- 使用 READ COMMITTED 隔离级别（减少间隙锁）
SET SESSION TRANSACTION ISOLATION LEVEL READ COMMITTED;

5. 配置调优：默认配置是个坑

⚙️ 关键配置参数

5.1 InnoDB 缓冲池大小

-- 查看当前配置
SHOW VARIABLES LIKE 'innodb_buffer_pool_size';
-- 默认：128M（太小了！）

-- 设置为物理内存的 50-70%
-- 例如 16GB 内存的服务器：
SET GLOBAL innodb_buffer_pool_size = 10737418240;  -- 10GB

为什么重要：InnoDB 的数据和索引都缓存在这里，太小会导致频繁磁盘 I/O。

5.2 连接数

-- 查看最大连接数
SHOW VARIABLES LIKE 'max_connections';
-- 默认：151

-- 根据业务调整
SET GLOBAL max_connections = 500;

注意：不是越大越好！每个连接占用内存，太多会 OOM。

5.3 查询缓存（Query Cache）

-- MySQL 8.0 已移除，8.0 以下版本建议关闭
SET GLOBAL query_cache_type = 0;
SET GLOBAL query_cache_size = 0;

为什么关闭：

并发高时锁竞争严重
命中率通常很低
表更新导致整个缓存失效

5.4 临时表大小

-- 内存临时表大小
SHOW VARIABLES LIKE 'tmp_table_size';  -- 默认 16M

-- 增加
SET GLOBAL tmp_table_size = 67108864;  -- 64M
SET GLOBAL max_heap_table_size = 67108864;

5.5 日志配置

-- Redo Log 大小（影响写入性能）
SHOW VARIABLES LIKE 'innodb_log_file_size';
-- 建议设置为 512M - 2G

-- 刷盘策略（1 最安全但最慢，0/2 更快但可能丢数据）
SHOW VARIABLES LIKE 'innodb_flush_log_at_trx_commit';
-- 生产环境建议 1，高并发可考虑 2

📝 配置文件模板（my.cnf）

[mysqld]
# 基础配置
port = 3306
datadir = /var/lib/mysql
socket = /var/lib/mysql/mysql.sock

# 连接配置
max_connections = 500
max_connect_errors = 1000

# InnoDB 配置（最重要！）
innodb_buffer_pool_size = 10G
innodb_log_file_size = 512M
innodb_flush_log_at_trx_commit = 1
innodb_flush_method = O_DIRECT

# 慢查询日志
slow_query_log = ON
long_query_time = 1
slow_query_log_file = /var/log/mysql/slow-query.log

# 字符集
character-set-server = utf8mb4
collation-server = utf8mb4_unicode_ci

6. 实战案例集锦

案例 1：分页查询优化

问题：深分页很慢

-- 查询第 10000 页，每页 10 条
SELECT * FROM orders ORDER BY id LIMIT 100000, 10;
-- 执行时间：8.5 秒 😱

原因：MySQL 需要扫描 100000 行然后抛弃

解决方案

方案 1：使用子查询（推荐）

SELECT * FROM orders
WHERE id >= (
  SELECT id FROM orders ORDER BY id LIMIT 100000, 1
)
ORDER BY id LIMIT 10;
-- 执行时间：0.05 秒 ✅

方案 2：记录上次 ID（游标分页）

-- 第一页
SELECT * FROM orders ORDER BY id LIMIT 10;
-- 假设最后一个 id = 100

-- 第二页
SELECT * FROM orders WHERE id > 100 ORDER BY id LIMIT 10;
-- 超级快！✅

方案 3：使用覆盖索引

-- 先查 ID（很快）
SELECT id FROM orders ORDER BY id LIMIT 100000, 10;

-- 再根据 ID 查完整数据
SELECT * FROM orders WHERE id IN (1, 2, 3, ...);

案例 2：COUNT 查询优化

问题：COUNT(*) 很慢

-- 大表统计
SELECT COUNT(*) FROM orders;
-- 500 万行，执行 3 秒

解决方案

方案 1：使用近似值

-- 使用 EXPLAIN 估算
EXPLAIN SELECT * FROM orders;
-- rows 字段就是估算值

-- 或使用信息架构
SELECT table_rows
FROM information_schema.tables
WHERE table_schema = 'your_db' AND table_name = 'orders';

方案 2：维护计数器表

-- 创建计数器表
CREATE TABLE counters (
  table_name VARCHAR(64) PRIMARY KEY,
  row_count INT
);

-- 应用层维护计数
-- 每次插入/删除时更新

方案 3：使用 COUNT(列) + WHERE

-- 如果只需要统计特定条件
SELECT COUNT(*) FROM orders WHERE status = 1
AND created_at > '2024-01-01';  -- 利用索引

案例 3：JOIN 优化

问题：多表 JOIN 很慢

SELECT *
FROM orders o
LEFT JOIN users u ON o.user_id = u.id
LEFT JOIN products p ON o.product_id = p.id
WHERE o.status = 1;
-- 执行时间：5 秒

优化步骤

1. 检查 JOIN 字段索引

-- 确保 JOIN 字段有索引
SHOW INDEX FROM orders;
SHOW INDEX FROM users;
SHOW INDEX FROM products;

CREATE INDEX idx_user_id ON orders(user_id);
CREATE INDEX idx_product_id ON orders(product_id);

2. 使用 STRAIGHT_JOIN 强制顺序

-- 如果你知道小表应该先 JOIN
SELECT *
FROM users u  -- 小表
STRAIGHT_JOIN orders o ON u.id = o.user_id
WHERE o.status = 1;

3. 分步查询替代大 JOIN

-- 先查需要的 user_id
SELECT user_id FROM orders WHERE status = 1;

-- 再批量查询用户信息
SELECT * FROM users WHERE id IN (1, 2, 3, ...);
-- 应用层组装

案例 4：子查询优化

问题：相关子查询很慢

-- 慢查询
SELECT *,
  (SELECT COUNT(*) FROM order_items WHERE order_id = orders.id) AS item_count
FROM orders;
-- 执行时间：12 秒

优化

-- 使用 JOIN
SELECT o.*, COUNT(oi.id) AS item_count
FROM orders o
LEFT JOIN order_items oi ON o.id = oi.order_id
GROUP BY o.id;
-- 执行时间：0.5 秒 ✅

🎓 总结与最佳实践

SQL 调优 Checklist

检查项	说明
✅ 索引	WHERE / JOIN / ORDER BY 字段是否有索引
✅ EXPLAIN	是否扫过执行计划，type 是否合理
✅ SELECT *	是否只查询需要的列
✅ 慢查询日志	是否开启并定期分析
✅ 分页	是否避免深度分页
✅ 事务	是否快速提交，避免长事务
✅ 配置	缓冲池、连接数是否合理

🚀 调优的黄金法则

先监控，后优化：不知道慢在哪里就别乱改
索引第一：80% 的性能问题都是索引问题
小步快跑：一次优化一个点，测试验证
数据驱动：用 benchmark 数据说话，别靠感觉
留有余地：别压榨到极限，给流量增长留空间

📚 推荐工具

慢查询分析：pt-query-digest, mysqldumpslow
性能监控：MySQL Enterprise Monitor, Percona PMM
压测工具：sysbench, mysqlslap
可视化管理：phpMyAdmin, DBeaver, Navicat

🤣 最后

希望这篇文档能帮你把 MySQL 从"蜗牛"变成"闪电"！

记住：调优是个持续的过程，没有一劳永逸的方案。定期检查慢查询日志，保持好奇心，你的数据库会越来越快！

“Premature optimization is the root of all evil” — Donald Knuth

但如果是生产环境慢查询，那就不是过早优化了，那是救火！🔥

Happy Querying! 🚀

AtomGit开源社区

AtomGit 是由开放原子开源基金会联合 CSDN 等生态伙伴共同推出的新一代开源与人工智能协作平台。平台坚持“开放、中立、公益”的理念，把代码托管、模型共享、数据集托管、智能体开发体验和算力服务整合在一起，为开发者提供从开发、训练到部署的一站式体验。

更多推荐

面向MIMO基带干扰消除的高灵活性异构多核体系结构设计开发【附程序】

为了支持非线性干扰消除中的排序操作，设计了一个专用的排序系统，包含4级流水线比较树，对16个输入值进行并行排序，延迟仅为3个时钟周期。编程模型方面，计算核采用类似软流水的方式，一个计算任务分解为多个阶段，每个阶段由一条微指令控制，一条微指令可以同时驱动多个运算单元。以8x8 MIMO的线性最小均方误差检测算法为例，C代码约500行，手工映射到该异构多核架构后，汇编代码为1800条，运行总时钟周期为

AtomGit开源社区

[智能体-118]：LangChain 核心组件、功能与 API 详解

功能：定期调用 LLM 把长对话压缩为摘要，大幅减少 Token 占用。适用：长时多轮对话、上下文窗口较小的模型。

AtomGit开源社区

【EI复现】基于主从博弈的新型城镇配电系统产消者竞价策略【IEEE33节点】(Matlab代码实现）

本文采用SFE模型对产消者竞价行为建模,确立了含多产消者的新型城镇配电系统日前现货市场交易机制,建立了含竞价博弈和优化调度的双层模型。上层模型追求产消者利润最大化,可确定多个产消者在配电网内的最优报价策略,下层模型考虑运行安全约束以及用户参与DR对系统进行最优经济调度﹐确定市场出清价格。最后﹐采用改进粒子群优化算法与(CPLEX求解器相结合的方法对该多主从博弈模型进行求解。

AtomGit开源社区

所有评论(0)

查看更多评论

螺旋星系

@qq_62425694

已为社区贡献1条内容

MySQL 调优指南：从慢如蜗牛到快如闪电

螺旋星系

MySQL 调优指南：从慢如蜗牛到快如闪电

📋 目录

1. 慢查询：你的数据库在摸鱼

😱 问题现场

🔍 问题原因

1.1 全表扫描（Table Scan）

1.2 SELECT * 的陷阱

1.3 隐式类型转换

🛠️ 排查过程

第一步：开启慢查询日志

第二步：分析慢查询日志

第三步：定位具体查询

💡 解决方法

1. 添加合适的索引

2. 避免SELECT *

3. 修复类型转换问题

2. 索引：不是加了就有用

😤 常见误区

🔍 索引失效的 10 种情况

🛠️ 索引优化实战

案例：联合索引的"最左前缀"原则

案例：LIKE 优化

案例：覆盖索引（Covering Index）

3. EXPLAIN：SQL 的体检报告

📊 读懂 EXPLAIN 输出

🎯 type 字段详解（从好到坏）

🔑 Extra 字段解读

🛠️ 实战优化案例

问题：Using filesort

4. 锁问题：当数据库变成停车场

🚗 锁的比喻

😱 常见锁问题

4.1 行锁升级为表锁

4.2 死锁（Deadlock）

4.3 间隙锁（Gap Lock）的坑

5. 配置调优：默认配置是个坑

⚙️ 关键配置参数

5.1 InnoDB 缓冲池大小

5.2 连接数

5.3 查询缓存（Query Cache）

5.4 临时表大小

5.5 日志配置

📝 配置文件模板（my.cnf）

6. 实战案例集锦

案例 1：分页查询优化

问题：深分页很慢

解决方案

案例 2：COUNT 查询优化

问题：COUNT(*) 很慢

解决方案

案例 3：JOIN 优化

问题：多表 JOIN 很慢

优化步骤

案例 4：子查询优化

问题：相关子查询很慢

优化

🎓 总结与最佳实践

SQL 调优 Checklist

🚀 调优的黄金法则

📚 推荐工具

🤣 最后

所有评论(0)

温馨提示：您尚未绑定手机号

螺旋星系