【JAVA架构师成长之路】【持久层】第2集：SQL常用优化手段

课程标题：SQL常用优化手段——15分钟快速提升数据库性能

目标：掌握10+核心SQL优化技巧，解决慢查询、高负载等生产问题

0-1分钟：优化核心原则——减少数据扫描量

本质逻辑：通过索引、分页、过滤条件等手段，最小化磁盘I/O和内存计算。
反例：SELECT * FROM orders（全表扫描） → 正例：SELECT id, status FROM orders WHERE user_id=100（精准查询）。

1-2分钟：索引优化——创建与选择

1. 高频查询字段必加索引：

   ALTER TABLE users ADD INDEX idx_email (email);  -- 为WHERE/ORDER BY字段加索引  
   

2. 联合索引最左匹配原则：

   INDEX idx_city_age (city, age)  -- 能命中WHERE city=xx AND age>20，但无法命中WHERE age>20  
   

2-3分钟：避免索引失效的六大陷阱

1. 对索引列计算或函数：

   -- 失效：  
   SELECT * FROM users WHERE YEAR(create_time) = 2023;  
   -- 优化：  
   SELECT * FROM users WHERE create_time BETWEEN '2023-01-01' AND '2023-12-31';  
   

2. 隐式类型转换：

   -- user_id是字符串类型，查询失效：  
   SELECT * FROM orders WHERE user_id = 100;  
   

3-4分钟：分页查询优化——拒绝OFFSET

问题：LIMIT 1000000, 10 扫描100万行后取10条。
优化方案：

-- 方案1：基于ID连续（假设id自增）  
SELECT * FROM orders WHERE id > 1000000 LIMIT 10;  

-- 方案2：延迟关联（适用于复杂查询）  
SELECT * FROM orders  
INNER JOIN (SELECT id FROM orders LIMIT 1000000, 10) AS tmp USING(id);  

5-6分钟：JOIN优化——小表驱动大表

原则：小表（结果集小）作为驱动表，减少循环次数。
手动控制：

-- STRAIGHT_JOIN强制驱动顺序  
SELECT * FROM small_table  
STRAIGHT_JOIN large_table ON small_table.id = large_table.s_id;  

6-7分钟：子查询优化——用JOIN替代

反例：子查询生成临时表，性能差。

SELECT * FROM users  
WHERE id IN (SELECT user_id FROM orders WHERE amount > 100);  

优化：

SELECT users.* FROM users  
JOIN orders ON users.id = orders.user_id  
WHERE orders.amount > 100;  

7-8分钟：覆盖索引——避免回表查询

场景：索引包含查询所需字段，无需查主键。

-- 未优化（需回表）：  
SELECT name, age FROM users WHERE city = '北京';  

-- 优化（创建覆盖索引）：  
ALTER TABLE users ADD INDEX idx_city_name_age (city, name, age);  

8-9分钟：排序优化——利用索引排序

问题：ORDER BY未用索引导致Using filesort。
优化：

-- 索引：INDEX idx_age (age)  
SELECT * FROM users ORDER BY age;  -- 命中索引排序  

-- 索引：INDEX idx_city_age (city, age)  
SELECT * FROM users WHERE city='北京' ORDER BY age;  -- 命中联合索引  

**9-10分钟：避免SELECT ***

问题：传输冗余数据，浪费内存/带宽。
优化：

-- 反例：  
SELECT * FROM products;  
-- 正例：  
SELECT id, name, price FROM products;  

10-11分钟：批量操作替代循环

反例：循环插入1000条数据（网络+事务开销）。
优化：

INSERT INTO users (name, age)  
VALUES ('张三', 20), ('李四', 22), ...;  -- 单次批量插入  

11-12分钟：合理使用事务——短事务原则

反例：长事务锁资源，引发阻塞。
优化：

-- 错误：  
BEGIN;  
UPDATE account SET balance = balance - 100 WHERE id = 1;  
-- 复杂业务逻辑...（耗时操作）  
COMMIT;  

-- 正确：拆分事务，快速提交。  

12-13分钟：EXPLAIN执行计划分析

关键字段解读：

• type：ALL（全表扫描）→ ref（索引查找）。
• Extra：Using temporary（需优化临时表）。
  操作：

EXPLAIN SELECT * FROM users WHERE age > 20;  

13-14分钟：慢查询日志与监控

配置：

-- my.cnf  
slow_query_log = 1  
long_query_time = 1  -- 记录超过1秒的查询  

工具：

• mysqldumpslow：分析日志。
• Percona Toolkit：高级诊断。

14-15分钟：总结与行动清单

优先级：

1. 索引优化：高频查询必加，避免失效。
2. 重写SQL：分页/JOIN/子查询优化。
3. 监控分析：定期检查慢查询日志。
   扩展：

• 读写分离：分散读压力。
• 分库分表：超大数据量终极方案。

通过15分钟系统学习，快速掌握SQL性能优化核心技巧，直接解决80%的数据库性能问题！