一、优化方向与核心原则

1. 减少数据访问量
2. 避免 SELECT *，仅查询需要的字段。
3. 使用 LIMIT 分页，结合覆盖索引优化大表分页（如延迟关联）。
4. 减少计算开销
5. 避免在 WHERE 子句中对字段进行函数操作（如 WHERE YEAR(create_time) = 2023）。
6. 简化复杂子查询，改用 JOIN 或临时表。
7. 利用索引高效检索
8. 遵循 最左前缀原则 设计联合索引。
9. 区分度高（基数大）的列优先建索引（如用户ID vs 性别）。

二、具体优化手段

1. 索引优化

• 场景示例：

-- 低效写法（索引失效）
SELECT * FROM orders WHERE amount * 2 > 1000;

-- 优化后（保持字段独立）
SELECT * FROM orders WHERE amount > 1000 / 2;

• 索引失效场景：
• 隐式类型转换（如字符串字段用数字查询）。
• LIKE 以通配符开头（LIKE '%abc'）。
• 对索引列使用 NOT、!= 或 OR 条件。

2. 查询语句优化

• 避免全表扫描：

-- 低效写法
SELECT * FROM user WHERE status IS NULL;

-- 优化方案：为 status 设置默认值（如 0），并建索引。

• 优化 JOIN 操作：
• 小表驱动大表（如小表在 LEFT JOIN 左侧）。
• 避免 JOIN 过多表（通常不超过 3 个）。
• 分页优化：

-- 低效写法（偏移量大时）
SELECT * FROM logs LIMIT 1000000, 10;

-- 优化写法（延迟关联）
SELECT * FROM logs 
WHERE id >= (SELECT id FROM logs ORDER BY id LIMIT 1000000, 1)
LIMIT 10;

3. 执行计划分析

• 使用 EXPLAIN 或 EXPLAIN ANALYZE：
• 关注字段：
• type：ALL（全表扫描）需优化为 ref、range 等。
• key：实际使用的索引。
• rows：扫描行数（越小越好）。
• Extra：Using filesort（需优化排序）、Using temporary（避免临时表）。

三、表设计与架构优化

1. 合理分库分表
2. 垂直拆分：按业务拆分（如用户表与订单表分离）。
3. 水平拆分：按时间或哈希分片（如按用户ID取模）。
4. 选择合适的数据类型
5. 用 INT 而非 VARCHAR 存储数值型主键。
6. 避免 TEXT 大字段频繁查询，可分离到扩展表。
7. 冷热数据分离
8. 历史数据归档（如订单表按年月分区）。

四、高级技巧与工具

1. 批量操作代替循环

-- 低效写法（逐条插入）
INSERT INTO table (col) VALUES (1);
INSERT INTO table (col) VALUES (2);

-- 高效写法
INSERT INTO table (col) VALUES (1), (2);

2. 使用预编译语句
3. 防止 SQL 注入，同时减少解析开销（如 MyBatis 的 #{}）。
4. 监控与调优工具
5. 慢查询日志（MySQL 的 slow_query_log）。
6. 性能分析工具：pt-query-digest、Percona Toolkit。

五、实战案例

场景：某电商订单查询接口超时

SELECT * FROM orders WHERE user_id = 1001 ORDER BY create_time DESC LIMIT 10;

• 问题分析：
• user_id 无索引，导致全表扫描。
• ORDER BY 未利用索引排序，产生 Using filesort。
• 优化步骤：
• 为 user_id 和 create_time 创建联合索引：

ALTER TABLE orders ADD INDEX idx_user_time (user_id, create_time);

• 仅查询必要字段：

SELECT order_id, amount, status FROM orders WHERE user_id = 1001 ORDER BY create_time DESC LIMIT 10;

六、总结