第 240 章:性能调优 - 数据库

学习目标

SQL 优化方法
索引设计与优化
数据库配置调优
监控与故障排查

一、性能瓶颈定位

1.1 数据库性能瓶颈

瓶颈	现象
CPU	慢查询多
IO	磁盘 IO 高
锁	大量等待
连接	等待连接

1.2 慢查询捕获

yaml

slow_query_log: ON
long_query_time: 0.5           # 0.5 秒
log_queries_not_using_indexes: ON

# mysqldumpslow pt-query-digest 分析
mysqldumpslow -s t /var/lib/mysql/slow.log

pt-query-digest /var/lib/mysql/slow.log > digest.txt

二、EXPLAIN 解读

2.1 基础示例

sql

EXPLAIN SELECT * FROM orders WHERE user_id = 1 AND status = 'PAID';

字段	值	说明
`type`	`ref`	连接类型
`possible_keys`	`idx_user_status`	可能用到的索引
`key`	`idx_user_status`	实际用到的
`rows`	`100`	扫描行数
`Extra`	`Using where`	附加信息

2.2 type 性能排序

type	性能	含义
`system`	最佳	表中只有 1 行
`const`	极好	主键/唯一索引查
`eq_ref`	极好	主键关联
`ref`	好	普通索引
`range`	中	范围扫描
`index`	差	全索引扫
`ALL`	最差	全表扫

2.3 Extra 警告信号

Extra	含义	改进
`Using filesort`	外部排序	加索引
`Using temporary`	临时表	GROUP BY 加索引
`Using where`	正常	-
`Using index`	覆盖索引	✅ 最佳
`Impossible WHERE`	永远 false	SQL 错
`Range checked for each record`	可能用索引但代价高	优化索引

2.4 EXPLAIN FORMAT=JSON

sql

EXPLAIN FORMAT=JSON
SELECT * FROM orders WHERE user_id = 1;

json

{
  "query_block": {
    "select_id": 1,
    "cost_info": {
      "query_cost": "1.20"
    },
    "table": {
      "table_name": "orders",
      "access_type": "ref",
      "rows_examined_per_scan": 100,
      "filtered": "100.00",
      "using_index": true
    }
  }
}

三、索引设计

3.1 索引类型

类型	说明
B+Tree	默认,适合范围
HASH	内存表,等值
Fulltext	全文
空间	地理位置

3.2 设计原则

yaml

原则:
  - 高基数(区分度高)优先
  - 经常被查的字段
  - 联合索引最左前缀
  - 不创建冗余索引
  - 控制数量(5-8 个以内)

3.3 联合索引顺序

sql

-- 业务: WHERE status = ? AND user_id = ? ORDER BY created_at DESC
-- user_id 选择性高,放前
CREATE INDEX idx_user_status_time ON orders(user_id, status, created_at);

3.4 覆盖索引

sql

-- ❌ SELECT * 回表
SELECT * FROM orders WHERE user_id = 1;

-- ✅ 覆盖索引,不回表
-- 索引包含 user_id + status + amount
SELECT status, amount FROM orders WHERE user_id = 1;

3.5 索引下推(ICP)

MySQL 5.6+ 优化:

sql

-- 索引 (a, b)
SELECT * FROM t WHERE a > 1 AND b = 'x';
-- ICP:在索引中跳过 b 不符合的,减少回表

3.6 索引监控

sql

-- 监控未被使用的索引
SELECT * FROM performance_schema.table_io_waits_summary_by_index_usage
WHERE index_name IS NOT NULL AND count_read = 0;

-- 查冗余索引
SELECT a.table_schema, a.table_name, a.index_name
FROM information_schema.statistics a
JOIN information_schema.statistics b
ON a.table_schema = b.table_schema
   AND a.table_name = b.table_name
   AND a.column_name = b.column_name
   AND a.seq_in_index = b.seq_in_index
   AND a.index_name != b.index_name;

四、SQL 优化实战

4.1 优化 LIMIT 深分页

sql

-- ❌ 慢,OFFSET 100000 需要跳过 100000 行
SELECT * FROM orders
ORDER BY id LIMIT 100000, 10;

-- ✅ 用游标
SELECT * FROM orders
WHERE id > 100000
ORDER BY id LIMIT 10;

-- ✅ 用 between(有序 id)
SELECT * FROM orders WHERE id BETWEEN 100001 AND 100010;

-- ✅ 子查询定位起始 id
SELECT * FROM orders
WHERE id > (
    SELECT id FROM orders ORDER BY id LIMIT 100000, 1
)
LIMIT 10;

4.2 JOIN 优化

sql

-- ❌ 多表 JOIN,大表驱动小表
SELECT * FROM orders o
LEFT JOIN user u ON o.user_id = u.id
WHERE u.city = 'Beijing';

-- ✅ 小表驱动 + 索引
ALTER TABLE user ADD INDEX idx_city (city);

SELECT o.*, u.name FROM orders o
INNER JOIN user u ON o.user_id = u.id
WHERE u.city = 'Beijing';

4.3 GROUP BY 优化

sql

-- ❌ Using filesort
SELECT user_id, COUNT(*) FROM orders GROUP BY user_id;

-- ✅ 覆盖索引
ALTER TABLE orders ADD INDEX idx_user_created(user_id, created_at);

4.4 子查询改 JOIN

sql

-- ❌ IN 子查询
SELECT * FROM user
WHERE id IN (SELECT user_id FROM orders WHERE amount > 100);

-- ✅ JOIN
SELECT DISTINCT u.*
FROM user u
INNER JOIN orders o ON u.id = o.user_id
WHERE o.amount > 100;

4.5 OR 改 UNION

sql

-- ❌ OR 不走索引
SELECT * FROM orders WHERE status = 'A' OR status = 'B';

-- ✅ UNION 走索引
SELECT * FROM orders WHERE status = 'A'
UNION ALL
SELECT * FROM orders WHERE status = 'B';

4.6 避免 SELECT *

sql

-- ❌ SELECT *
SELECT * FROM orders WHERE id = 1;

-- ✅ 只查需要的列
SELECT id, status, amount FROM orders WHERE id = 1;

五、连接池调优

5.1 HikariCP

yaml

spring:
  datasource:
    hikari:
      maximum-pool-size: 20          # 最大连接
      minimum-idle: 5                # 最小空闲
      connection-timeout: 30000      # 连接超时
      idle-timeout: 600000           # 空闲超时
      max-lifetime: 1800000          # 最长生命周期
      validation-timeout: 5000
      auto-commit: false
      pool-name: HikariPool

5.2 公式计算

连接数 ≈ ((核心数 * 2) + 有效硬盘数)
例: 8 核 + 1 SSD → (16 + 1) = 17

实际值需根据业务调整。

5.3 监控

sql

-- 当前连接
SHOW PROCESSLIST;

-- 状态
SHOW STATUS LIKE 'Threads%';

-- 连接利用
SELECT variable_value FROM performance_schema.global_status
WHERE variable_name = 'Threads_connected';

六、事务隔离级别

6.1 四种级别

隔离级别	脏读	不可重复读	幻读
Read uncommitted	✅	✅	✅
Read committed	❌	✅	✅
Repeatable read(MySQL 默认)	❌	❌	✅
Serializable	❌	❌	❌

6.2 配置

sql

SET SESSION TRANSACTION ISOLATION LEVEL REPEATABLE READ;

6.3 Spring 中设置

java

@Transactional(isolation = Isolation.READ_COMMITTED)
public void transfer(...) { ... }

6.4 避免长事务

java

// ❌ 事务里有远程调用
@Transactional
public void createOrder() {
    orderService.save();
    inventoryClient.deduct();    // RPC 5 秒 → 事务 5 秒
}

// ✅ 拆开
public void createOrder() {
    orderService.save();    // 事务1
    inventoryClient.deduct();    // 不在事务
}

七、锁优化

7.1 锁类型

类型	行为
行锁	锁单行(InnoDB)
间隙锁	范围空白
表锁	MyISAM
MDL	元数据

7.2 死锁排查

sql

SHOW ENGINE INNODB STATUS;

sql

LATEST DETECTED DEADLOCK
*** (1) TRANSACTION:
TRANSACTION 3100, ACTIVE 0 sec
LOCK WAIT 3 lock struct(s)

7.3 减小死锁

yaml

策略:
  - 统一顺序加锁
  - 缩短事务时长
  - 索引合理(范围小)
  - 减少并发

7.4 乐观锁替代

sql

-- 用 version 字段
UPDATE inventory
SET stock = stock - 1, version = version + 1
WHERE id = ? AND version = ?;

八、InnoDB 调优

8.1 InnoDB Buffer Pool

yaml

innodb_buffer_pool_size = 物理内存 60%-80%
例:32GB 内存 → 20GB 缓存

yaml

# 缓冲池分代
innodb_buffer_pool_instances = 8

8.2 Log File

yaml

innodb_log_file_size = 4g
innodb_log_files_in_group = 2
innodb_log_buffer_size = 64m
innodb_flush_log_at_trx_commit = 1   # 写 OS cache,稍 flush
innodb_flush_method = O_DIRECT       # 绕过 OS 缓存

8.3 写入策略

yaml

innodb_doublewrite = OFF    # 高吞吐,丢文件需自行权衡
sync_binlog = 1             # 每次事务 sync binlog

8.4 IO 线程

yaml

innodb_read_io_threads = 8
innodb_write_io_threads = 8

九、查询性能分析

9.1 慢查询过滤

sql

-- 找长期阻塞
SELECT * FROM information_schema.INNODB_TRX
WHERE trx_started < NOW() - INTERVAL 30 SECOND;

-- 找长事务
SELECT trx_id, trx_state, TIMESTAMPDIFF(SECOND, trx_started, NOW()) AS duration
FROM information_schema.INNODB_TRX
ORDER BY duration DESC;

9.2 锁等待

sql

SELECT
    r.trx_id waiting_trx,
    r.trx_query waiting_query,
    b.trx_id blocking_trx,
    b.trx_query blocking_query
FROM information_schema.INNODB_LOCK_WAITS w
JOIN information_schema.INNODB_TRX r ON w.requesting_trx_id = r.trx_id
JOIN information_schema.INNODB_TRX b ON w.blocking_trx_id = b.trx_id;

9.3 索引效率

sql

-- 查询 I/O 高的索引
SELECT object_schema, object_name, index_name,
       count_read, count_fetch
FROM performance_schema.table_io_waits_summary_by_index_usage
ORDER BY count_read DESC;

十、数据库规范化

10.1 三范式

yaml

1NF: 字段不可分(电话、地址拆分)
2NF: 完全依赖主键(非主字段只依赖主键)
3NF: 不传递依赖(非主字段不依赖其他非主字段)
BCNF: 3NF 加强版

10.2 反规范化

sql

-- 读多写少:冗余字段加速
ALTER TABLE orders ADD COLUMN user_name VARCHAR(50);

-- 维护一致性
UPDATE orders o
INNER JOIN user u ON o.user_id = u.id
SET o.user_name = u.name;

10.3 物化视图

sql

CREATE TABLE daily_order_summary AS
SELECT
    DATE(created_at) AS dt,
    COUNT(*) AS cnt,
    SUM(amount) AS total
FROM orders
GROUP BY DATE(created_at);

或用 Percona / pg matview。

十一、实战案例

11.1 慢 SQL 全流程优化

sql

-- 原始
SELECT * FROM orders
WHERE user_id = 1 AND status IN ('PAID', 'SHIPPED')
ORDER BY created_at DESC
LIMIT 20;

-- 步骤 1:EXPLAIN
EXPLAIN SELECT * FROM orders WHERE user_id = 1 AND status IN ('PAID', 'SHIPPED')
ORDER BY created_at DESC LIMIT 20;

-- 步骤 2:加联合索引
ALTER TABLE orders ADD INDEX idx_user_status_created (user_id, status, created_at);

-- 步骤 3:改写查询(避免回表)
SELECT id, amount, status, created_at  -- 不需 SELECT *
FROM orders
WHERE user_id = 1 AND status IN ('PAID', 'SHIPPED')
ORDER BY created_at DESC LIMIT 20;

11.2 文本搜索优化

sql

-- ❌ LIKE
SELECT * FROM product WHERE name LIKE '%Tom%';   -- 全表扫

-- ✅ FULLTEXT 索引
ALTER TABLE product ADD FULLTEXT INDEX ft_name (name);
SELECT * FROM product
WHERE MATCH(name) AGAINST('+Tom' IN BOOLEAN MODE);

11.3 时间函数索引

sql

-- 计算列
ALTER TABLE orders
ADD COLUMN create_date DATE GENERATED ALWAYS AS (DATE(created_at)) VIRTUAL,
ADD INDEX idx_create_date (create_date);

-- 用日期列查
SELECT * FROM orders WHERE create_date = '2026-08-13';

十二、本章小结

主题	关键
索引	覆盖索引 + 联合
SQL	EXPLAIN + 重写
事务	短 + 隔离级别
配置	Buffer Pool + IO
监控	慢查询 + 锁等待

动手练习

用 EXPLAIN 看 3 个慢查询,尝试加索引
跑 pt-query-digest 分析慢日志
配置 Buffer Pool 为 70% 内存,观察 QPS 变化
找出数据库中 5 个未使用的索引

下一章:第 241 章:性能调优 - 缓存与并发

第 240 章:性能调优 - 数据库 ​

一、性能瓶颈定位 ​

1.1 数据库性能瓶颈 ​

1.2 慢查询捕获 ​

二、EXPLAIN 解读 ​

2.1 基础示例 ​

2.2 type 性能排序 ​

2.3 Extra 警告信号 ​

2.4 EXPLAIN FORMAT=JSON ​

三、索引设计 ​

3.1 索引类型 ​

3.2 设计原则 ​

3.3 联合索引顺序 ​

3.4 覆盖索引 ​

3.5 索引下推(ICP) ​

3.6 索引监控 ​

四、SQL 优化实战 ​

4.1 优化 LIMIT 深分页 ​

4.2 JOIN 优化 ​

4.3 GROUP BY 优化 ​

4.4 子查询改 JOIN ​

4.5 OR 改 UNION ​

4.6 避免 SELECT * ​

五、连接池调优 ​

5.1 HikariCP ​

5.2 公式计算 ​

5.3 监控 ​

六、事务隔离级别 ​

6.1 四种级别 ​

6.2 配置 ​

6.3 Spring 中设置 ​

6.4 避免长事务 ​

七、锁优化 ​

7.1 锁类型 ​

7.2 死锁排查 ​

7.3 减小死锁 ​

7.4 乐观锁替代 ​

八、InnoDB 调优 ​

8.1 InnoDB Buffer Pool ​

8.2 Log File ​

8.3 写入策略 ​

8.4 IO 线程 ​

九、查询性能分析 ​

9.1 慢查询过滤 ​

9.2 锁等待 ​

9.3 索引效率 ​

十、数据库规范化 ​

10.1 三范式 ​

10.2 反规范化 ​

10.3 物化视图 ​

十一、实战案例 ​

11.1 慢 SQL 全流程优化 ​

11.2 文本搜索优化 ​

11.3 时间函数索引 ​

十二、本章小结 ​

动手练习 ​

第 240 章:性能调优 - 数据库

一、性能瓶颈定位

1.1 数据库性能瓶颈

1.2 慢查询捕获

二、EXPLAIN 解读

2.1 基础示例

2.2 type 性能排序

2.3 Extra 警告信号

2.4 EXPLAIN FORMAT=JSON

三、索引设计

3.1 索引类型

3.2 设计原则

3.3 联合索引顺序

3.4 覆盖索引

3.5 索引下推(ICP)

3.6 索引监控

四、SQL 优化实战

4.1 优化 LIMIT 深分页

4.2 JOIN 优化

4.3 GROUP BY 优化

4.4 子查询改 JOIN

4.5 OR 改 UNION

4.6 避免 SELECT *

五、连接池调优

5.1 HikariCP

5.2 公式计算

5.3 监控

六、事务隔离级别

6.1 四种级别

6.2 配置

6.3 Spring 中设置

6.4 避免长事务

七、锁优化

7.1 锁类型

7.2 死锁排查

7.3 减小死锁

7.4 乐观锁替代

八、InnoDB 调优

8.1 InnoDB Buffer Pool

8.2 Log File

8.3 写入策略

8.4 IO 线程

九、查询性能分析

9.1 慢查询过滤

9.2 锁等待

9.3 索引效率

十、数据库规范化

10.1 三范式

10.2 反规范化

10.3 物化视图

十一、实战案例

11.1 慢 SQL 全流程优化

11.2 文本搜索优化

11.3 时间函数索引

十二、本章小结

动手练习