SQL 调优是提升数据库性能的核心手段，需要从 **查询语句、索引设计、数据库结构、系统配置** 等多维度入手。以下是一套系统性调优框架，结合实战案例说明：
---
### **一、分析性能瓶颈**
**1. 定位慢查询**
- **开启慢查询日志**：
```sql
-- MySQL
SET GLOBAL slow_query_log = ON;
SET GLOBAL long_query_time = 2; -- 记录超过2秒的查询
-- PostgreSQL
ALTER SYSTEM SET log_min_duration_statement = 2000;
```
- **使用性能分析工具**：
- `EXPLAIN ANALYZE`（PostgreSQL）或 `EXPLAIN FORMAT=TREE`（MySQL 8.0+）
- 可视化工具：pgAdmin、MySQL Workbench、Oracle SQL Developer
**2. 解读执行计划**
- **关键指标**：
- **全表扫描（Seq Scan）**：警惕未命中索引
- **索引扫描（Index Scan）** vs **索引唯一扫描（Index Only Scan）**
- **Sort/Merge Join**：可能暗示连接条件或索引问题
- **临时表（Temporary Table）**：内存或磁盘使用过高
```sql
-- MySQL 执行计划示例
EXPLAIN
SELECT o.order_id, c.name
FROM orders o
JOIN customers c ON o.customer_id = c.id
WHERE o.status = 'shipped';
```
---
### **二、索引优化策略**
**1. 创建高效索引**
- **覆盖索引（Covering Index）**：
```sql
-- 优化前：需要回表
SELECT name, email FROM users WHERE age > 25;
-- 优化后：创建复合索引 (age, name, email)
CREATE INDEX idx_users_age_covering ON users(age, name, email);
```
- **函数索引（Function-Based Index）**：
```sql
-- 处理大小写不敏感查询
CREATE INDEX idx_users_lower_email ON users(LOWER(email));
```
**2. 避免索引失效**
- **常见陷阱**：
- 对索引字段使用函数或计算：`WHERE YEAR(create_time) = 2023`
- 隐式类型转换：`WHERE user_id = '123'`（user_id 是整数）
- 前导通配符：`WHERE name LIKE '%john%'`
- OR 条件未全覆盖：`WHERE a=1 OR b=2`（需单独索引a和b）
---
### **三、SQL 语句优化**
**1. 减少数据访问量**
- **分页优化**：
```sql
-- 低效写法（OFFSET 越大越慢）
SELECT * FROM orders ORDER BY id LIMIT 10 OFFSET 10000;
-- 优化写法（使用游标）
SELECT * FROM orders WHERE id > 10000 ORDER BY id LIMIT 10;
```
- **避免 SELECT ***：
```sql
-- 只取必要字段
SELECT order_id, status FROM orders WHERE user_id = 123;
```
**2. 优化 JOIN 操作**
- **小表驱动原则**：
```sql
-- 让小表（users）驱动大表（orders）
SELECT *
FROM users u
JOIN orders o ON u.id = o.user_id
WHERE u.country = 'CN';
```
- **使用 EXISTS 替代 IN**：
```sql
-- 当子查询结果集大时更高效
SELECT * FROM products p
WHERE EXISTS (
SELECT 1 FROM inventory i
WHERE i.product_id = p.id AND i.quantity > 0
);
```
---
### **四、数据库设计优化**
**1. 反范式化设计**
- **增加冗余字段**：
```sql
-- 订单表增加用户名字段（避免连表查询）
ALTER TABLE orders ADD COLUMN customer_name VARCHAR(100);
```
- **汇总表**：
```sql
-- 创建每日销售统计表
CREATE TABLE daily_sales (
date DATE PRIMARY KEY,
total_amount DECIMAL(10,2),
order_count INT
);
```
**2. 分区表**
- **按时间范围分区**：
```sql
-- PostgreSQL 分区表示例
CREATE TABLE logs (
id SERIAL,
log_time TIMESTAMP,
message TEXT
) PARTITION BY RANGE (log_time);
CREATE TABLE logs_2023 PARTITION OF logs
FOR VALUES FROM ('2023-01-01') TO ('2024-01-01');
```
---
### **五、系统级调优**
**1. 内存配置**
- **MySQL InnoDB 缓冲池**：
```ini
# my.cnf 配置
innodb_buffer_pool_size = 系统内存的 70%-80%
innodb_buffer_pool_instances = 8（CPU核心数）
```
**2. 连接池管理**
- **限制最大连接数**：
```sql
-- MySQL
SET GLOBAL max_connections = 500;
```
**3. 硬件优化**
- **SSD 替代 HDD**
- **RAID 10 阵列配置**
---
### **六、高级技巧**
**1. 批量操作优化**
```sql
-- 低效逐条插入
INSERT INTO logs (msg) VALUES ('msg1');
INSERT INTO logs (msg) VALUES ('msg2');
-- 高效批量插入
INSERT INTO logs (msg) VALUES ('msg1'), ('msg2'), ...;
```
**2. 使用 CTE 优化复杂查询**
```sql
WITH regional_sales AS (
SELECT region, SUM(amount) AS total
FROM orders
GROUP BY region
)
SELECT region, total
FROM regional_sales
WHERE total > 1000000;
```
---
### **调优工具推荐**
| 工具名称 | 适用场景 |
|-------------------|----------------------------|
| `pt-query-digest` | MySQL 慢查询分析 |
| `pg_stat_statements` | PostgreSQL SQL 统计 |
| `Oracle AWR` | Oracle 性能分析报告 |
| `Redis慢查询日志` | 缓存层性能分析 |
---
**调优的本质是平衡**：在查询效率、数据一致性、开发成本之间找到最优解。建议每次修改后通过 `基准测试`（如 sysbench、JMeter）验证效果，避免过度优化。