SQL常用优化的技巧

1. 避免使用 SELECT *

查询时只选取需要的列，避免使用 SELECT *，减少不必要的数据查询和传输。

SELECT name, age FROM user WHERE id = 1;

2. 用 UNION ALL 代替 UNION

使用 UNION ALL 获取所有数据，包括重复的，避免 UNION 的去重操作，减少CPU资源消耗。

(SELECT * FROM user WHERE id = 1)
UNION ALL
(SELECT * FROM user WHERE id = 2);

3. 小表驱动大表

使用小表的数据集来驱动大表的数据集，例如使用 IN 或 EXISTS，根据实际情况选择合适的关键字。

SELECT * FROM orders WHERE user_id IN (SELECT id FROM user WHERE status = 1);

4. 批量操作

批量插入或更新数据，减少数据库请求次数，提升性能。

INSERT INTO orders (id, code, user_id) 
VALUES (123, '001', 100), (124, '002', 100), (125, '003', 101);

5. 多用 LIMIT

使用 LIMIT 限制查询结果数量，提高查询效率，同时在删除或修改操作中使用，以避免误操作。

SELECT id, create_date FROM orders WHERE user_id = 123 ORDER BY create_date ASC LIMIT 1;

6. IN 中值太多

对 IN 子句中的值进行限制，避免查询大量数据导致接口超时。

SELECT id, name FROM category WHERE id IN (1, 2, 3, ..., 100) LIMIT 500;

7. 增量查询

通过条件限制，每次只查询部分数据，提高同步效率。

SELECT * FROM user WHERE id > #{lastId} AND create_time >= #{lastCreateTime} LIMIT 100;

8. 高效分页

使用 LIMIT 进行分页时，对于大数据量的表，使用 id 范围查询代替偏移量分页。

SELECT id, name, age FROM user WHERE id > 1000000 LIMIT 20;

9. 用连接查询代替子查询

使用连接查询代替子查询，减少临时表的创建和删除，提高查询效率。

SELECT o.* 
FROM orders o 
INNER JOIN user u ON o.user_id = u.id 
WHERE u.status = 1;

10. JOIN 的表不宜过多

控制 JOIN 表的数量，避免复杂的索引选择和性能消耗。

SELECT a.name, b.name, c.name 
FROM a 
INNER JOIN b ON a.id = b.a_id 
INNER JOIN c ON c.b_id = b.id;

11. JOIN 时要注意

使用 INNER JOIN 时，小表驱动大表；使用 LEFT JOIN 时，注意左边应为小表。

SELECT o.id, o.code, u.name 
FROM orders o 
INNER JOIN user u ON o.user_id = u.id 
WHERE u.status = 1;

12. 控制索引的数量

合理控制索引数量，避免过多的索引导致性能消耗。

13. 选择合理的字段类型

根据数据特点选择合适的字段类型，例如 CHAR 和 VARCHAR，以及数字类型和字符串类型的选择。

ALTER TABLE orders ADD COLUMN code CHAR(20) NOT NULL;

14. 提升 GROUP BY 的效率

在 GROUP BY 前使用 WHERE 条件过滤数据，减少分组操作的数据量。

SELECT user_id, user_name 
FROM orders 
WHERE user_id <= 200 
GROUP BY user_id;

15. 索引优化

使用 EXPLAIN 命令检查SQL语句是否走索引，避免索引失效，并在必要时使用 FORCE INDEX 强制使用特定索引。

EXPLAIN SELECT * FROM orders WHERE code = '002';