第四章：数据库优化¶
4.1 索引优化¶
索引设计原则¶
索引设计原则：
┌─────────────────────────────────────────────────────────────┐
│ │
│ 1. 选择性高的列优先 │
│ ┌─────────────────────────────────────────────────────┐ │
│ │ 选择性 = 不同值数量 / 总行数 │ │
│ │ 选择性越高，索引效果越好 │ │
│ │ │ │
│ │ ✅ 好的索引：user_id（唯一值多） │ │
│ │ ❌ 差的索引：gender（只有 M/F） │ │
│ └─────────────────────────────────────────────────────┘ │
│ │
│ 2. 最左前缀原则 │
│ ┌─────────────────────────────────────────────────────┐ │
│ │ 索引：(a, b, c) │ │
│ │ │ │
│ │ ✅ 能用索引：WHERE a = 1 │ │
│ │ ✅ 能用索引：WHERE a = 1 AND b = 2 │ │
│ │ ✅ 能用索引：WHERE a = 1 AND b = 2 AND c = 3 │ │
│ │ ❌ 不能用索引：WHERE b = 2 │ │
│ │ ❌ 不能用索引：WHERE c = 3 │ │
│ │ ⚠️ 部分使用：WHERE a = 1 AND c = 3（只用 a） │ │
│ └─────────────────────────────────────────────────────┘ │
│ │
│ 3. 覆盖索引 │
│ ┌─────────────────────────────────────────────────────┐ │
│ │ 查询字段都在索引中，无需回表 │ │
│ │ │ │
│ │ 索引：(user_id, status) │ │
│ │ ✅ 覆盖索引：SELECT status FROM orders │ │
│ │ WHERE user_id = 123 │ │
│ └─────────────────────────────────────────────────────┘ │
│ │
│ 4. 避免索引失效 │
│ ┌─────────────────────────────────────────────────────┐ │
│ │ ❌ WHERE YEAR(created_at) = 2024 # 函数导致失效 │ │
│ │ ✅ WHERE created_at >= '2024-01-01' │ │
│ │ AND created_at < '2025-01-01' │ │
│ │ │ │
│ │ ❌ WHERE name LIKE '%John%' # 前缀模糊失效 │ │
│ │ ✅ WHERE name LIKE 'John%' │ │
│ │ │ │
│ │ ❌ WHERE score + 1 > 100 # 计算导致失效 │ │
│ │ ✅ WHERE score > 99 │ │
│ └─────────────────────────────────────────────────────┘ │
│ │
└─────────────────────────────────────────────────────────────┘
索引分析¶
-- 查看执行计划
EXPLAIN SELECT * FROM orders WHERE user_id = 123;
-- MySQL 8.0+ 详细分析
EXPLAIN ANALYZE SELECT * FROM orders WHERE user_id = 123;
-- 查看索引使用情况
SELECT
index_name,
table_name,
cardinality,
seq_in_index
FROM information_schema.statistics
WHERE table_schema = 'mydb';
-- 查看未使用的索引
SELECT * FROM sys.schema_unused_indexes;
-- 查看冗余索引
SELECT * FROM sys.schema_redundant_indexes;
4.2 查询优化¶
慢查询优化¶
-- ❌ 全表扫描
SELECT * FROM orders WHERE YEAR(created_at) = 2024;
-- ✅ 使用索引范围
SELECT * FROM orders
WHERE created_at >= '2024-01-01' AND created_at < '2025-01-01';
-- ❌ SELECT *
SELECT * FROM orders WHERE user_id = 123;
-- ✅ 只查需要的列
SELECT id, order_no, amount FROM orders WHERE user_id = 123;
-- ❌ 大 OFFSET
SELECT * FROM orders ORDER BY id LIMIT 10000, 20;
-- ✅ 游标分页
SELECT * FROM orders WHERE id > 10000 ORDER BY id LIMIT 20;
-- ❌ OR 导致索引失效
SELECT * FROM orders WHERE user_id = 123 OR status = 'pending';
-- ✅ UNION ALL
SELECT * FROM orders WHERE user_id = 123
UNION ALL
SELECT * FROM orders WHERE status = 'pending' AND user_id != 123;
-- ❌ NOT IN
SELECT * FROM orders WHERE user_id NOT IN (1, 2, 3);
-- ✅ LEFT JOIN + IS NULL
SELECT o.* FROM orders o
LEFT JOIN blacklist b ON o.user_id = b.user_id
WHERE b.user_id IS NULL;
JOIN 优化¶
-- 小表驱动大表
-- ✅ 小表在左
SELECT o.* FROM small_table s
JOIN large_table l ON s.id = l.foreign_id;
-- 避免 JOIN 过多表
-- ❌ 多表 JOIN
SELECT * FROM a JOIN b ON ... JOIN c ON ... JOIN d ON ... JOIN e ON ...
-- ✅ 拆分查询
-- 先查 a JOIN b，再根据结果查其他
-- JOIN 字段建立索引
CREATE INDEX idx_foreign_id ON large_table(foreign_id);
4.3 连接池优化¶
连接池配置¶
连接池参数：
┌─────────────────────────────────────────────────────────────┐
│ │
│ 核心参数： │
│ ┌─────────────────────────────────────────────────────────┐│
│ │ minPoolSize 最小连接数 ││
│ │ maxPoolSize 最大连接数 ││
│ │ connectionTimeout 获取连接超时时间 ││
│ │ idleTimeout 空闲连接超时时间 ││
│ │ maxLifetime 连接最大存活时间 ││
│ └─────────────────────────────────────────────────────────┘│
│ │
│ 计算公式： │
│ ┌─────────────────────────────────────────────────────────┐│
│ │ 连接数 = (核心数 * 2) + 有效磁盘数 ││
│ │ ││
│ │ 示例：4 核 CPU + 1 块磁盘 ││
│ │ 连接数 = (4 * 2) + 1 = 9 ││
│ │ ││
│ │ 实际需要压测确定最优值 ││
│ └─────────────────────────────────────────────────────────┘│
│ │
│ HikariCP 配置示例： │
│ ┌─────────────────────────────────────────────────────────┐│
│ │ spring.datasource.hikari: ││
│ │ minimum-idle: 5 ││
│ │ maximum-pool-size: 20 ││
│ │ connection-timeout: 30000 ││
│ │ idle-timeout: 600000 ││
│ │ max-lifetime: 1800000 ││
│ │ connection-test-query: SELECT 1 ││
│ └─────────────────────────────────────────────────────────┘│
│ │
└─────────────────────────────────────────────────────────────┘
4.4 分库分表¶
分片策略¶
分库分表策略：
┌─────────────────────────────────────────────────────────────┐
│ │
│ 1. 垂直分库 │
│ ┌─────────────────────────────────────────────────────┐ │
│ │ 按业务拆分 │ │
│ │ │ │
│ │ 用户库 → 用户相关表 │ │
│ │ 订单库 → 订单相关表 │ │
│ │ 商品库 → 商品相关表 │ │
│ └─────────────────────────────────────────────────────┘ │
│ │
│ 2. 水平分表 │
│ ┌─────────────────────────────────────────────────────┐ │
│ │ 按数据量拆分 │ │
│ │ │ │
│ │ orders_0, orders_1, orders_2, ... │ │
│ │ │ │
│ │ 分片键选择： │ │
│ │ - user_id：用户维度查询 │ │
│ │ - order_id：订单维度查询 │ │
│ │ - created_at：时间维度查询 │ │
│ └─────────────────────────────────────────────────────┘ │
│ │
│ 3. 分片算法 │
│ ┌─────────────────────────────────────────────────────┐ │
│ │ 哈希取模：user_id % 16 │ │
│ │ 范围分片：按时间或 ID 范围 │ │
│ │ 一致性哈希：减少数据迁移 │ │
│ └─────────────────────────────────────────────────────┘ │
│ │
└─────────────────────────────────────────────────────────────┘
4.5 小结¶
本章介绍了数据库优化：
- 合理设计索引，遵循最左前缀原则
- 优化慢查询，避免全表扫描
- 配置合适的连接池参数
- 大表考虑分库分表
思考题¶
- 如何判断索引是否生效？
- 连接池大小如何确定？
- 分库分表后如何处理跨库查询？
参考资料¶
- 《高性能 MySQL》
- MySQL 8.0 Reference Manual
- Use The Index, Luke