数据库性能优化实战：索引、查询与架构设计

为什么数据库会慢？

数据库性能问题通常来自：

• 缺少合适的索引
• 低效的 SQL 查询
• 不合理的表结构
• 锁竞争和事务问题

索引优化策略

1. 理解索引类型

-- B-Tree 索引（最常用）
CREATE INDEX idx_email ON users(email);

-- 复合索引（注意列顺序）
CREATE INDEX idx_status_created ON orders(status, created_at);

-- 唯一索引
CREATE UNIQUE INDEX idx_username ON users(username);

-- 覆盖索引（避免回表）
CREATE INDEX idx_email_id ON users(email, id);

2. 索引使用原则

• 最左前缀原则：复合索引 (a,b,c) 只能用于 a 或 a,b 或 a,b,c
• 选择性高的列：性别列不适合单独建索引
• 避免过度索引：每个索引都有写入开销

3. 检查索引使用情况

-- MySQL
EXPLAIN SELECT * FROM users WHERE email = "test@example.com";

-- 查看慢查询
SHOW PROCESSLIST;
SELECT * FROM mysql.slow_log;

SQL 查询优化

✅ 优化写法

-- 使用 LIMIT 限制结果数
SELECT * FROM users LIMIT 100;

-- 只查询需要的字段
SELECT id, name, email FROM users;

-- 使用 EXISTS 代替 IN（子查询）
SELECT * FROM users u 
WHERE EXISTS (SELECT 1 FROM orders o WHERE o.user_id = u.id);

-- 批量插入
INSERT INTO users (name, email) VALUES 
  ("a", "a@test.com"),
  ("b", "b@test.com"),
  ("c", "c@test.com");

❌ 避免的写法

-- 全表扫描
SELECT * FROM users WHERE YEAR(created_at) = 2024;
-- 优化为：
SELECT * FROM users WHERE created_at >= "2024-01-01";

-- 隐式类型转换
SELECT * FROM users WHERE phone = 13800138000;
-- phone 是字符串，应该：
SELECT * FROM users WHERE phone = "13800138000";

-- OR 导致索引失效
SELECT * FROM users WHERE name = "张三" OR email = "z@test.com";
-- 优化为 UNION:
SELECT * FROM users WHERE name = "张三"
UNION
SELECT * FROM users WHERE email = "z@test.com";

表结构设计

范式与反范式

• 第三范式：减少冗余，保证数据一致性
• 反范式：适当冗余提升查询性能

-- 范式化（需要 JOIN）
users(id, name)
orders(id, user_id, amount)

-- 反范式化（冗余用户名，避免 JOIN）
orders(id, user_id, user_name, amount)

分区表

-- 按时间分区
ALTER TABLE logs PARTITION BY RANGE (YEAR(created_at)) (
  PARTITION p2023 VALUES LESS THAN (2024),
  PARTITION p2024 VALUES LESS THAN (2025)
);

事务与锁

事务隔离级别

-- 读未提交（最低）
SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;

-- 读已提交（Oracle/PG 默认）
SET TRANSACTION ISOLATION LEVEL READ COMMITTED;

-- 可重复读（MySQL 默认）
SET TRANSACTION ISOLATION LEVEL REPEATABLE READ;

-- 串行化（最高）
SET TRANSACTION ISOLATION LEVEL SERIALIZABLE;

避免死锁

1. 固定访问表/行的顺序
2. 大事务拆分为小事务
3. 使用合适的隔离级别
4. 添加超时机制

连接池配置

# SQLAlchemy 配置
engine = create_engine(
    "mysql+pymysql://user:pass@localhost/db",
    pool_size=20,        # 连接池大小
    max_overflow=10,     # 最大溢出连接数
    pool_recycle=3600,   # 连接回收时间
    pool_pre_ping=True   # 连接前检查
)

监控与调优

关键指标

• QPS/TPS：每秒查询/事务数
• 慢查询数量
• 连接数使用率
• 缓存命中率
• 锁等待时间

常用工具

• MySQL: pt-query-digest, Percona Toolkit
• PostgreSQL: pg_stat_statements
• 通用：Prometheus + Grafana

结语

数据库优化是一个持续过程。从索引开始，优化查询，合理设计架构，配合监控告警，才能构建高性能的数据层。