数据库性能调优是保障系统稳定运行的核心能力。本文以 MySQL 为主、兼顾通用原理，从 SQL 优化、索引优化、架构层面优化、服务器参数调优四个维度，系统讲解数据库性能调优的方法论与实战技巧。
一、为什么需要性能调优
1.1 性能问题的典型表现
- 慢查询：单条 SQL 执行时间超过阈值（默认 10 秒），拖慢整体响应
- 连接池耗尽：并发请求增多，数据库连接不够用，请求排队等待
- 锁竞争：事务长时间持有锁，其他事务被阻塞，TPS 骤降
- 磁盘 I/O 瓶颈：数据量大、查询频繁，磁盘成为系统短板
- 内存不足：缓冲池命中率低，大量读操作走磁盘
1.2 调优的核心原则
| 原则 | 说明 |
|---|---|
| 先定位，后优化 | 用监控和日志找到真正的瓶颈，不要凭感觉优化 |
| 量化评估 | 优化前后必须有量化数据对比（执行时间、QPS、慢查询数等） |
| 从大到小 | 优先优化架构 → SQL → 索引 → 参数，投入产出比递减 |
| 适度优化 | 过度优化会增加系统复杂度，追求"够用"而非"极致" |
1.3 调优的一般流程
发现问题（慢查询日志/监控告警）
|
v
定位瓶颈（EXPLAIN / SHOW PROFILE / Performance Schema）
|
v
分析根因（全表扫描？索引失效？锁等待？内存不足？）
|
v
制定方案（加索引？改SQL？分库分表？调参数？）
|
v
实施优化
|
v
验证效果（对比优化前后的量化指标）
|
v
持续监控（防止回退，发现新问题）
二、SQL 优化
SQL 优化是投入产出比最高的调优手段，一条写得不好的 SQL 可能比优化后的 SQL 慢几十倍甚至几百倍。
2.1 避免 SELECT *
-- ❌ 不推荐：查询所有列
SELECT * FROM `t_order` WHERE `user_id` = 1001;
-- ✅ 推荐：只查需要的列
SELECT `order_id`, `amount`, `create_time`
FROM `t_order`
WHERE `user_id` = 1001;
原因：
- 减少网络传输量
- 减少内存占用
- 增加索引覆盖（Covering Index）的可能性
2.2 避免在索引列上做运算或函数操作
-- ❌ 索引失效：在索引列上使用函数
SELECT * FROM `t_user` WHERE YEAR(`create_time`) = 2026;
SELECT * FROM `t_user` WHERE `age` + 1 = 30;
SELECT * FROM `t_user` WHERE LEFT(`name`, 1) = '张';
-- ✅ 索引生效：改写条件
SELECT * FROM `t_user` WHERE `create_time` >= '2026-01-01' AND `create_time` < '2027-01-01';
SELECT * FROM `t_user` WHERE `age` = 29;
SELECT * FROM `t_user` WHERE `name` LIKE '张%';
核心原则：对索引列做任何运算（算术运算、函数调用、类型转换），都会导致索引失效，变成全表扫描。
2.3 避免隐式类型转换
-- 假设 user_id 是 VARCHAR 类型
-- ❌ 隐式转换：传入数字，MySQL 会把 user_id 转为数字再比较，索引失效
SELECT * FROM `t_user` WHERE `user_id` = 1001;
-- ✅ 类型一致：传入字符串
SELECT * FROM `t_user` WHERE `user_id` = '1001';
2.4 合理使用 LIMIT
-- ❌ 先全部查出来再取第一条
SELECT * FROM `t_order` WHERE `user_id` = 1001;
-- ✅ 只需要一条，加 LIMIT
SELECT * FROM `t_order` WHERE `user_id` = 1001 LIMIT 1;
加分页场景：
-- ❌ 深分页性能差：需要扫描前 10000 条然后丢弃
SELECT * FROM `t_order` ORDER BY `id` LIMIT 10000, 10;
-- ✅ 游标分页：基于上一页最后一条的 ID
SELECT * FROM `t_order` WHERE `id` > 10000 ORDER BY `id` LIMIT 10;
-- ✅ 延迟关联：先用子查询定位 ID，再回表查完整数据
SELECT o.* FROM `t_order` o
INNER JOIN (SELECT `id` FROM `t_order` ORDER BY `id` LIMIT 10000, 10) t
ON o.`id` = t.`id`;
2.5 避免不合理的 LIKE 查询
-- ❌ 前缀通配符：无法走索引
SELECT * FROM `t_user` WHERE `name` LIKE '%张%';
SELECT * FROM `t_user` WHERE `name` LIKE '%张';
-- ✅ 后缀通配符：可以走索引
SELECT * FROM `t_user` WHERE `name` LIKE '张%';
如果确实需要全文搜索，应使用全文索引（FULLTEXT）或 Elasticsearch：
-- 创建全文索引
ALTER TABLE `t_article` ADD FULLTEXT INDEX `ft_title_content` (`title`, `content`);
-- 使用全文搜索
SELECT * FROM `t_article` WHERE MATCH(`title`, `content`) AGAINST('数据库优化' IN NATURAL LANGUAGE MODE);
2.6 避免 OR 引起索引失效
-- ❌ OR 可能导致索引失效
SELECT * FROM `t_user` WHERE `name` = '张三' OR `age` = 25;
-- ✅ 改用 UNION ALL
SELECT * FROM `t_user` WHERE `name` = '张三'
UNION ALL
SELECT * FROM `t_user` WHERE `age` = 25;
注意：MySQL 优化器在部分场景下可以自动优化 OR 为 Index Merge，但并非所有情况都有效，
UNION ALL更可靠。
2.7 合理使用 EXISTS 替代 IN
-- ❌ IN 子查询：当子查询结果集大时性能差
SELECT * FROM `t_order`
WHERE `user_id` IN (SELECT `id` FROM `t_user` WHERE `status` = 1);
-- ✅ EXISTS 关联：当外层结果集小时效率更高
SELECT * FROM `t_order` o
WHERE EXISTS (SELECT 1 FROM `t_user` u WHERE u.`id` = o.`user_id` AND u.`status` = 1);
-- ✅ 更推荐：JOIN 方式
SELECT o.* FROM `t_order` o
INNER JOIN `t_user` u ON o.`user_id` = u.`id`
WHERE u.`status` = 1;
| 场景 | 推荐写法 |
|---|---|
| 子查询结果集小、外层结果集大 | IN |
| 外层结果集小、子查询结果集大 | EXISTS |
| 通用场景 | JOIN |
2.8 避免 ORDER BY 带来的 filesort
-- ❌ 无索引的排序，产生 filesort
SELECT * FROM `t_order` WHERE `user_id` = 1001 ORDER BY `create_time`;
-- ✅ 创建联合索引，让排序走索引
ALTER TABLE `t_order` ADD INDEX `idx_user_create` (`user_id`, `create_time`);
EXPLAIN 中 Extra 列出现 Using filesort 说明需要额外排序，应优化。
2.9 批量操作代替循环单条操作
-- ❌ 循环执行单条 INSERT
INSERT INTO `t_order` (`user_id`, `amount`) VALUES (1, 100);
INSERT INTO `t_order` (`user_id`, `amount`) VALUES (2, 200);
INSERT INTO `t_order` (`user_id`, `amount`) VALUES (3, 300);
-- ✅ 批量 INSERT
INSERT INTO `t_order` (`user_id`, `amount`) VALUES
(1, 100),
(2, 200),
(3, 300);
批量操作的优势：
- 减少 SQL 解析次数
- 减少网络往返次数
- 减少 binlog 写入量（一条批量语句 vs 多条单条语句）
注意：批量 INSERT 的单次数据量建议控制在 500~1000 条以内，避免单事务过大导致锁竞争和 binlog 膨胀。
三、索引优化
索引是数据库性能调优最常用的手段。好的索引可以让查询性能提升数个数量级，糟糕的索引不仅帮不上忙，还会拖慢写入速度。
3.1 索引的基本类型
| 索引类型 | 说明 | 适用场景 |
|---|---|---|
| 主键索引 | 聚簇索引，叶子节点存储完整行数据 | 每张表必须有 |
| 唯一索引 | 索引列值唯一，允许 NULL | 唯一性约束字段 |
| 普通索引 | 最基本的索引，无唯一性要求 | 高频查询过滤字段 |
| 联合索引 | 多列组合索引，遵循最左前缀原则 | 多条件组合查询 |
| 前缀索引 | 对字符串前 N 个字符建索引 | 长字符串字段 |
| 全文索引 | 支持全文搜索 | 文本搜索场景 |
| 空间索引 | 支持地理空间数据查询 | GIS 场景 |
3.2 最左前缀原则
联合索引 (a, b, c) 的匹配规则：
| 查询条件 | 是否命中索引 | 说明 |
|---|---|---|
WHERE a = 1 | ✅ 命中 a | 使用索引最左列 |
WHERE a = 1 AND b = 2 | ✅ 命中 a, b | 使用索引前两列 |
WHERE a = 1 AND b = 2 AND c = 3 | ✅ 命中 a, b, c | 使用全部索引列 |
WHERE b = 2 | ❌ 不命中 | 跳过了最左列 a |
WHERE b = 2 AND c = 3 | ❌ 不命中 | 跳过了最左列 a |
WHERE a = 1 AND c = 3 | ✅ 命中 a | 只能用到 a，c 无法利用索引 |
WHERE a > 1 AND b = 2 | ✅ 命中 a | 范围查询后的列无法使用索引 |
关键理解：联合索引是按列顺序构建的 B+ 树。跳过左边的列，右边的列就无法在 B+ 树中有序定位。
3.3 索引下推（Index Condition Pushdown，ICP）
MySQL 5.6 引入的优化，在存储引擎层根据索引条件过滤数据，减少回表次数。
-- 联合索引 (name, age)
-- 查询条件：name LIKE '张%' AND age > 25
SELECT * FROM `t_user` WHERE `name` LIKE '张%' AND `age` > 25;
| 阶段 | 无 ICP | 有 ICP |
|---|---|---|
| 存储引擎层 | 只用 name LIKE '张%' 筛选，其余交给 Server 层 | 用 name LIKE '张%' 和 age > 25 同时筛选 |
| Server 层 | 逐行判断 age > 25，大量回表 | 只需判断少量回表数据 |
| 回表次数 | 多 | 少 |
EXPLAIN 中 Extra 列出现 Using index condition 表示使用了索引下推。
3.4 覆盖索引（Covering Index）
当查询的列全部包含在索引中时，无需回表，直接从索引中返回数据：
-- 联合索引 (user_id, create_time, amount)
-- 查询只需要索引列，无需回表
SELECT `user_id`, `create_time`, `amount`
FROM `t_order`
WHERE `user_id` = 1001;
EXPLAIN 中 Extra 列出现 Using index 表示命中覆盖索引。
覆盖索引的优势：
- 避免回表，减少 I/O
- 索引数据量远小于整行数据，缓存命中率高
- 是性能优化的"银弹"之一
3.5 索引选择性
索引的选择性越高，过滤效果越好。选择性 = COUNT(DISTINCT col) / COUNT(*)，值越接近 1 越好。
-- 计算选择性
SELECT
COUNT(DISTINCT `status`) / COUNT(*) AS status_selectivity,
COUNT(DISTINCT `user_id`) / COUNT(*) AS user_id_selectivity
FROM `t_order`;
| 字段 | 选择性 | 是否适合单独建索引 |
|---|---|---|
status（只有几个值） | 很低（如 0.01） | ❌ 区分度太差 |
user_id（大量不同值） | 高（如 0.8） | ✅ 适合建索引 |
order_no（唯一值） | 1.0 | ✅ 最佳 |
经验法则：选择性低于 0.1 的字段不适合单独建索引。但可以放在联合索引的右侧，配合高选择性字段使用。
3.6 前缀索引
对长字符串字段，只取前 N 个字符建索引，节省空间：
-- 计算合适的前缀长度
SELECT
COUNT(DISTINCT `email`) / COUNT(*) AS full_selectivity,
COUNT(DISTINCT LEFT(`email`, 5)) / COUNT(*) AS prefix_5,
COUNT(DISTINCT LEFT(`email`, 10)) / COUNT(*) AS prefix_10,
COUNT(DISTINCT LEFT(`email`, 15)) / COUNT(*) AS prefix_15
FROM `t_user`;
-- 选择接近完整选择性的最小长度
ALTER TABLE `t_user` ADD INDEX `idx_email` (`email`(10));
前缀索引的局限：
- 无法用于覆盖索引：因为索引中只存了部分值，无法直接返回完整列值
- 无法用于 ORDER BY / GROUP BY：因为前缀不保证完整列的有序性
3.7 索引优化实战案例
案例 1：多条件查询的联合索引设计
-- 查询模式
SELECT * FROM `t_order`
WHERE `user_id` = 1001 AND `status` = 2 AND `create_time` > '2026-01-01'
ORDER BY `create_time` DESC
LIMIT 10;
索引设计思路：
- 等值条件放前面：
user_id、status - 范围条件放后面：
create_time - 排序字段跟在范围条件后
-- 推荐索引
ALTER TABLE `t_order` ADD INDEX `idx_user_status_create` (`user_id`, `status`, `create_time`);
案例 2：排序分页查询的索引优化
-- 查询模式
SELECT * FROM `t_article`
WHERE `category` = 'tech' AND `is_published` = 1
ORDER BY `publish_time` DESC
LIMIT 20;
-- 推荐索引：等值条件 + 排序字段
ALTER TABLE `t_article` ADD INDEX `idx_cat_pub_status` (`category`, `is_published`, `publish_time`);
这样查询可以完全走索引，且排序也走索引，避免 filesort。
四、EXPLAIN 执行计划详解
EXPLAIN 是 SQL 调优最重要的工具，用于查看 MySQL 如何执行查询。
4.1 基本用法
EXPLAIN SELECT * FROM `t_order` WHERE `user_id` = 1001;
4.2 关键字段解读
| 字段 | 含义 | 重点关注 |
|---|---|---|
| id | 查询序号 | id 越大越先执行 |
| select_type | 查询类型 | SIMPLE / PRIMARY / SUBQUERY / DERIVED |
| table | 访问的表 | — |
| type | 访问类型 | ⭐ 性能从好到差：system > const > eq_ref > ref > range > index > ALL |
| possible_keys | 可能用到的索引 | — |
| key | 实际用到的索引 | NULL 表示未用索引 |
| key_len | 索引使用的字节数 | 判断联合索引用了几列 |
| ref | 与索引比较的列或常量 | — |
| rows | 预估扫描行数 | ⭐ 越小越好 |
| filtered | 过滤比例 | 100 表示全部通过 |
| Extra | 额外信息 | ⭐ 重点关注 |
4.3 type 字段详解
| type | 说明 | 示例 |
|---|---|---|
| system | 表中只有一行数据 | — |
| const | 通过主键或唯一索引定位一条记录 | WHERE id = 1 |
| eq_ref | JOIN 时被驱动表通过主键/唯一索引匹配 | JOIN ... ON t1.id = t2.id |
| ref | 使用非唯一索引查找 | WHERE user_id = 1001 |
| range | 索引范围扫描 | WHERE age > 20 / BETWEEN / IN |
| index | 全索引扫描 | 索引覆盖但需要扫描全部索引 |
| ALL | 全表扫描 | ⚠️ 需要优化 |
优化目标：至少达到
ref级别，避免index和ALL。
4.4 Extra 字段关键值
| Extra 值 | 含义 | 建议 |
|---|---|---|
| Using index | 覆盖索引，无需回表 | ✅ 理想状态 |
| Using index condition | 索引下推 | ✅ 好的优化 |
| Using where | Server 层过滤 | ⚠️ 存储引擎返回了过多数据 |
| Using filesort | 额外排序操作 | ⚠️ 需优化排序索引 |
| Using temporary | 使用临时表 | ⚠️ 需优化 GROUP BY / DISTINCT |
| Using join buffer | JOIN 无索引，使用缓冲区 | ⚠️ 需添加 JOIN 索引 |
| Impossible WHERE | WHERE 条件恒为假 | 检查逻辑 |
| Select tables optimized away | 优化为常量 | ✅ 无需读取表 |
4.5 EXPLAIN 实战分析
-- 创建测试表
CREATE TABLE `t_order` (
`id` BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
`order_no` VARCHAR(32) NOT NULL,
`user_id` BIGINT UNSIGNED NOT NULL,
`status` TINYINT NOT NULL DEFAULT 0,
`amount` DECIMAL(10,2) NOT NULL,
`create_time` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
PRIMARY KEY (`id`),
UNIQUE KEY `uk_order_no` (`order_no`),
KEY `idx_user_id` (`user_id`)
) ENGINE=InnoDB;
-- 查询1：走索引
EXPLAIN SELECT * FROM `t_order` WHERE `user_id` = 1001;
-- type: ref, key: idx_user_id ✅
-- 查询2：走主键
EXPLAIN SELECT * FROM `t_order` WHERE `id` = 1;
-- type: const, key: PRIMARY ✅
-- 查询3：全表扫描
EXPLAIN SELECT * FROM `t_order` WHERE `amount` > 100;
-- type: ALL, key: NULL ⚠️ 需要优化
-- 优化查询3：添加索引
ALTER TABLE `t_order` ADD INDEX `idx_amount` (`amount`);
EXPLAIN SELECT * FROM `t_order` WHERE `amount` > 100;
-- type: range, key: idx_amount ✅
五、架构层面优化
当 SQL 和索引优化已达极限，需要从架构层面寻求突破。
5.1 读写分离
将读操作和写操作分发到不同的数据库实例，主库负责写，从库负责读。
应用层
|
+-- 写请求 --> 主库（Master）
| |
| +-- 异步复制 --> 从库1（Slave）
| | |
| +-- 异步复制 --> 从库2（Slave）
|
+-- 读请求 --> 从库1 / 从库2（负载均衡）
优势：
- 读操作水平扩展，增加从库即可提升读能力
- 写操作集中在主库，避免锁竞争
注意事项：
- 主从延迟：异步复制存在延迟（通常毫秒级），对实时性要求高的读操作仍应走主库
- 数据一致性：写后立即读应走主库，避免读到旧数据
5.2 分库分表
当单表数据量超过千万级别，即使索引优化也难以避免性能下降，此时需要分库分表。
| 维度 | 说明 | 适用场景 |
|---|---|---|
| 垂直分库 | 按业务模块拆分到不同数据库 | 业务耦合度低，各模块数据独立 |
| 垂直分表 | 将大表的列拆分到多张表 | 列多、访问模式不同（冷热数据分离） |
| 水平分库 | 同一表数据按规则分散到不同数据库 | 单库数据量过大 |
| 水平分表 | 同一表数据按规则分散到同库多张表 | 单表数据量过大 |
水平分片键的选择：
-- 按用户 ID 取模分片（最常用）
-- 4 张表：t_order_0, t_order_1, t_order_2, t_order_3
-- 分片规则：table_index = user_id % 4
-- 按时间范围分片
-- t_order_2026q1, t_order_2026q2, t_order_2026q3, t_order_2026q4
-- 按地域分片
-- t_order_north, t_order_south, t_order_east, t_order_west
分库分表的代价：
- 分布式查询：跨片 JOIN 性能差，尽量在分片键上查询
- 分布式事务：跨库事务复杂度大幅提升
- 全局唯一 ID：不能依赖单库自增，需用雪花算法等方案
- 运维复杂：扩容、数据迁移成本高
5.3 数据冷热分离
将热数据（近期频繁访问）和冷数据（历史归档数据）分开存储：
-- 热数据表：存储近3个月的订单
CREATE TABLE `t_order_hot` (
-- 字段同 t_order
...
) ENGINE=InnoDB;
-- 冷数据表：存储3个月前的历史订单
CREATE TABLE `t_order_cold` (
-- 字段同 t_order
...
) ENGINE=InnoDB ROW_FORMAT=COMPRESSED KEY_BLOCK_SIZE=8;
-- 定时归档任务
INSERT INTO `t_order_cold`
SELECT * FROM `t_order_hot`
WHERE `create_time` < DATE_SUB(NOW(), INTERVAL 3 MONTH);
DELETE FROM `t_order_hot`
WHERE `create_time` < DATE_SUB(NOW(), INTERVAL 3 MONTH);
冷数据存储优化：
- 使用压缩行格式减少存储空间
- 使用更大页大小提升压缩比
- 可以迁移到更便宜的存储介质
5.4 缓存层
在数据库前面加一层缓存，减少数据库访问：
| 缓存方案 | 适用场景 | 说明 |
|---|---|---|
| Redis | 热点数据查询 | 最常用的缓存中间件 |
| 本地缓存 | 极高频访问的少量数据 | Caffeine / Guava Cache |
| CDN | 静态资源、公共数据 | 边缘节点缓存 |
六、服务器参数调优
6.1 InnoDB 缓冲池（innodb_buffer_pool_size）
最重要的参数，决定 InnoDB 缓存多少数据和索引页在内存中：
# 推荐设置为物理内存的 60%~80%（专用数据库服务器）
innodb_buffer_pool_size = 8G
# 缓冲池实例数（多实例减少锁竞争，建议每个实例 >= 1GB）
innodb_buffer_pool_instances = 8
缓冲池命中率监控：
-- 查看缓冲池命中率
SHOW STATUS LIKE 'Innodb_buffer_pool_read%';
-- Innodb_buffer_pool_read_requests：逻辑读请求数
-- Innodb_buffer_pool_reads：磁盘读次数
-- 命中率 = 1 - Innodb_buffer_pool_reads / Innodb_buffer_pool_read_requests
-- 目标：命中率 > 99%
6.2 连接数（max_connections）
# 最大连接数，根据业务并发量设置
max_connections = 500
# 空闲连接超时时间（秒），及时释放不活跃连接
wait_timeout = 600
interactive_timeout = 600
连接数监控：
-- 查看当前连接数
SHOW STATUS LIKE 'Threads_connected';
-- 查看历史最大连接数
SHOW STATUS LIKE 'Max_used_connections';
-- 如果 Max_used_connections 接近 max_connections，需要调大
6.3 查询缓存（query_cache_type）
⚠️ MySQL 8.0 已移除查询缓存功能。MySQL 5.7 及以下版本建议关闭：
# MySQL 5.7：建议关闭查询缓存
# 查询缓存在高并发写入场景下反而降低性能
query_cache_type = 0
query_cache_size = 0
6.4 日志相关参数
# redo log 大小（适当增大可减少检查点写入频率）
innodb_log_file_size = 1G
# redo log 缓冲区大小
innodb_log_buffer_size = 64M
# binlog 刷盘策略
# 0 = 依赖 OS 刷盘（性能最好，可能丢数据）
# 1 = 每次事务提交刷盘（最安全，性能最差）
# N = 每 N 秒刷盘（折中）
sync_binlog = 1
# redo log 刷盘策略
# 1 = 每次事务提交刷盘（最安全）
# 2 = 每次提交写入 OS 缓存，每秒刷盘（折中）
innodb_flush_log_at_trx_commit = 1
| 安全级别 | sync_binlog | innodb_flush_log_at_trx_commit | 性能 | 数据安全 |
|---|---|---|---|---|
| 最高安全 | 1 | 1 | 最差 | 最多丢失 1 个事务 |
| 折中 | 100 | 2 | 较好 | 最多丢失 1 秒数据 |
| 最高性能 | 0 | 0 | 最好 | 可能丢失数秒数据 |
生产环境推荐：金融级应用使用
sync_binlog=1, innodb_flush_log_at_trx_commit=1；普通业务可用折中方案。
6.5 排序和连接缓冲区
-- 排序缓冲区（每个线程分配，ORDER BY / GROUP BY 使用）
sort_buffer_size = 4M
-- 连接缓冲区（每个线程分配，JOIN 无索引时使用）
join_buffer_size = 4M
-- 临时表大小（ORDER BY / GROUP BY / DISTINCT 产生的内存临时表）
tmp_table_size = 64M
max_heap_table_size = 64M
注意：这些参数是每个连接独立分配的，设置过大会在高并发时消耗大量内存。例如
sort_buffer_size = 16M，500 个连接同时排序就需要 8GB 内存。
七、慢查询日志与监控
7.1 开启慢查询日志
# my.cnf 配置
slow_query_log = 1
slow_query_log_file = /var/log/mysql/slow.log
long_query_time = 1 # 超过1秒的查询记录
min_examined_row_limit = 100 # 扫描行少于100的不记录
7.2 分析慢查询日志
# 使用 mysqldumpslow 工具分析
mysqldumpslow -s t -t 10 /var/log/mysql/slow.log
# -s t：按查询时间排序
# -t 10：显示前10条
7.3 Performance Schema
MySQL 5.7+ 内置的性能监控工具：
-- 查看最耗时的 SQL
SELECT DIGEST_TEXT, COUNT_STAR, SUM_TIMER_WAIT / 1000000000000 AS total_sec
FROM performance_schema.events_statements_summary_by_digest
ORDER BY SUM_TIMER_WAIT DESC
LIMIT 10;
-- 查看等待事件
SELECT EVENT_NAME, COUNT_STAR, SUM_TIMER_WAIT / 1000000000 AS total_ms
FROM performance_schema.events_waits_summary_global_by_event_name
WHERE COUNT_STAR > 0
ORDER BY SUM_TIMER_WAIT DESC
LIMIT 10;
7.4 SHOW PROFILE
-- 开启 profiling
SET profiling = 1;
-- 执行查询
SELECT * FROM `t_order` WHERE `user_id` = 1001;
-- 查看执行详情
SHOW PROFILE;
-- 查看所有查询的概要
SHOW PROFILES;
-- 查看指定查询的 CPU 和 IO 消耗
SHOW PROFILE CPU, BLOCK IO FOR QUERY 1;
7.5 关键监控指标
| 指标 | 含义 | 告警阈值 |
|---|---|---|
| QPS | 每秒查询数 | 接近 max_connections × 10 |
| TPS | 每秒事务数 | 根据业务基线 |
| 慢查询数 | 超过阈值的查询数 | > 0 持续出现 |
| 连接数 | 当前活跃连接 | > max_connections × 80% |
| 缓冲池命中率 | InnoDB 缓冲池命中比例 | < 95% |
| 锁等待次数 | 行锁等待次数 | 持续增长 |
| 死锁次数 | 死锁发生次数 | > 0 |
八、常见性能问题排查清单
8.1 查询突然变慢
排查步骤：
1. 检查慢查询日志，确认是哪些 SQL 变慢
2. EXPLAIN 查看执行计划，是否索引失效
3. 检查是否有 DDL 操作（ALTER TABLE）导致锁表
4. 检查是否有大事务未提交
5. 检查缓冲池命中率是否下降（可能因数据量增长）
6. 检查是否有统计信息过期（ANALYZE TABLE）
8.2 数据库 CPU 飙高
排查步骤：
1. SHOW PROCESSLIST 查看当前正在执行的 SQL
2. 找出耗时最长的 SQL，EXPLAIN 分析
3. 检查是否有全表扫描（type = ALL）
4. 检查是否有大量排序（Using filesort）
5. 检查是否有锁等待（SHOW ENGINE INNODB STATUS）
6. 检查是否有频繁的连接创建和销毁
8.3 数据库磁盘 I/O 高
排查步骤：
1. 检查缓冲池大小是否合理
2. 检查是否有大量随机 I/O（全表扫描、索引回表过多）
3. 检查 redo log / binlog 刷盘频率
4. 检查是否有大量数据导入操作
5. 检查是否有大事务产生大量 undo log
6. 考虑使用 SSD 替代 HDD
8.4 锁等待超时
-- 查看当前锁等待情况
SELECT * FROM information_schema.INNODB_LOCK_WAITS;
SELECT * FROM information_schema.INNODB_LOCKS;
-- 查看当前正在执行的事务
SELECT * FROM information_schema.INNODB_TRX;
-- 查看死锁日志
SHOW ENGINE INNODB STATUS;
锁等待的常见原因：
- 大事务持有锁时间过长
- 没有使用索引导致行锁升级为表锁
- 不同的操作顺序导致死锁
INSERT ... SELECT在 RR 隔离级别下加间隙锁
九、总结
9.1 性能调优优先级
架构优化（读写分离、分库分表） ← 投入产出比最高
↓
SQL 优化（避免全表扫描、减少回表）
↓
索引优化（联合索引、覆盖索引、索引下推）
↓
参数调优（缓冲池、连接数、日志策略）
↓
硬件升级（SSD、更大内存） ← 最后手段
9.2 核心要点速查
- 先定位瓶颈再优化，用 EXPLAIN / 慢查询日志 / 监控数据说话
- SQL 优化的核心：避免全表扫描、减少回表、减少排序
- 索引不是越多越好：每多一个索引，写入就多一份开销
- 联合索引遵循最左前缀原则：等值条件在前，范围条件在后
- 覆盖索引是性能利器：查询列都在索引中，无需回表
- 深分页用游标分页：避免
LIMIT 100000, 10 - 大表考虑冷热分离：热数据留 InnoDB，冷数据压缩归档
- 参数调优看监控：缓冲池命中率 > 99% 是核心指标
- 安全与性能的权衡：
sync_binlog和innodb_flush_log_at_trx_commit根据业务选择 - 持续监控：性能调优不是一次性工作，需要持续关注
9.3 参考文档
- MySQL 8.0 官方文档 - Optimization
- MySQL 8.0 官方文档 - EXPLAIN Output Format
- MySQL 8.0 官方文档 - InnoDB Buffer Pool
- MySQL 8.0 官方文档 - Performance Schema
