sql优化

SHENKEM

于 2025-04-01 08:22:47 发布

阅读量422

点赞数 3

分类专栏： mysql 文章标签： mysql

本文链接：https://blog.youkuaiyun.com/C18298182575/article/details/146896794

版权

mysql 专栏收录该内容

61 篇文章

订阅专栏

SQL优化是提升数据库性能的关键步骤，以下是一份系统化的优化方案，结合了常见问题和实际场景的分析：

---

### **1. 索引优化**
- **核心原则**：避免全表扫描，提升查询效率。
- **常用列加索引**：对高频查询的 `WHERE`、`JOIN`、`ORDER BY` 列建立索引。
- **组合索引策略**：遵循最左匹配原则（如索引 `(a,b,c)` 支持 `a=1`、`a=1 AND b=2`，但不支持单独 `b=2`）。
- **避免冗余索引**：已有组合索引 `(a,b)` 时，无需单独建 `a` 的索引。
- **低区分度列慎用索引**：如性别、状态等重复值多的列，索引可能无效。
- **索引覆盖**：将查询字段包含在索引中（如 `SELECT a,b FROM table`，索引设为 `(c,a,b)`）。
- **维护成本权衡**：索引会降低写操作速度，需平衡读写比例。

---

### **2. SQL语句优化**
- **减少数据量**：
- 避免 `SELECT *`，只查询必要字段。
- 分页时用 `WHERE id > 末页最大ID` 替代 `LIMIT offset, size`（避免大偏移量）。
- **优化查询逻辑**：
- 用 `JOIN` 替代低效子查询（但 `EXISTS` 可能更适合部分场景）。
- 避免 `WHERE` 中对字段进行运算（如 `YEAR(date) = 2023` 改为 `BETWEEN '2023-01-01' AND '2023-12-31'`）。
- **集合操作优化**：
- 用 `UNION ALL` 替代 `UNION`（除非明确需要去重）。
- 避免过多 `OR` 条件，可改用 `IN` 或分拆查询。

---

### **3. 数据库设计优化**
- **数据类型选择**：
- 优先使用紧凑类型（如 `INT` 而非 `VARCHAR` 存储数字）。
- 避免 `NULL` 字段，用默认值替代（`NULL` 增加索引复杂度）。
- **范式与反范式平衡**：
- 适当冗余高频查询字段（如订单表冗余用户名，减少 `JOIN`）。
- **分区与分表**：
- 按时间或范围分区（如历史数据归档）。
- 分库分表应对海量数据（如用户ID取模分片）。

---

### **4. 执行计划分析**
- **使用 `EXPLAIN`**：
- 关注 `type` 列：避免 `ALL`（全表扫描），目标至少为 `range` 或 `ref`。
- 检查 `key` 列：确认实际使用的索引。
- `rows` 列：估算扫描行数，值越小越好。
- `Extra` 列：警惕 `Using filesort`（需优化排序）或 `Using temporary`（临时表）。
- **强制索引**：极端情况下用 `FORCE INDEX` 纠正优化器选择。

---

### **5. 高级技巧**
- **批量操作**：
- 批量插入：`INSERT INTO ... VALUES (...), (...)`。
- 分批更新：避免单次操作锁定大量数据。
- **事务优化**：
- 缩短事务时间，及时提交/回滚。
- 避免长事务导致锁竞争。
- **缓存与读写分离**：
- 用 Redis 缓存热点数据（如商品信息）。
- 读写分离（主库写，从库读）。
- **统计信息维护**：
- 定期执行 `ANALYZE TABLE` 更新统计信息。
- 重建碎片化索引（`OPTIMIZE TABLE`）。

---

### **6. 分布式优化**
- **分库分表策略**：
- 垂直分库（按业务拆分）、水平分表（按数据分布）。
- 使用中间件（如 ShardingSphere）管理路由。
- **异步处理**：
- 非实时任务用消息队列（如 Kafka、RabbitMQ）解耦。
- 最终一致性替代实时强一致性。

---

### **实战示例**
**场景**：订单查询慢（联查用户表，按时间排序分页）。
```sql
SELECT o.order_id, u.username, o.amount
FROM orders o
JOIN users u ON o.user_id = u.user_id
WHERE o.status = 1
ORDER BY o.create_time DESC
LIMIT 100000, 10;
```
**优化步骤**：
1. **索引检查**：
- `orders` 表：为 `(user_id, status, create_time)` 建组合索引。
- `users` 表：主键 `user_id` 已索引。
2. **分页优化**：
- 记录上一页最大 `create_time`，改为：
```sql
WHERE create_time < '上一页最大时间'
ORDER BY create_time DESC
LIMIT 10;
```
3. **执行计划验证**：确保使用索引且无临时表排序。