前言
作为一个写 SQL 的程序员，代码写得好不好是一回事，但 SQL 写得烂，性能拉胯，全公司都得为你的慢查询买单，尤其在大数据量表上，SQL 写不好就是“内鬼”级别的错误。
今天不整那些虚的，直接来点硬货：20 条 SQL 优化小技巧，每一条都能救你一命，关键是，我还给你配上代码例子，拿去直接用，希望对你会有所帮助。
1. 避免 SELECT *
别一上来就 SELECT *，你觉得这样写省事，但数据库得把整张表的每一列都给你搬过来，代价大得吓人。需要啥列写清楚，别偷懒。
-- 错误写法SELECT * FROM users;
-- 正确写法SELECT id, name, age FROM users;
复制代码
优化原因：减少网络传输数据量，避免无用字段占内存。
2. WHERE 字段要有索引
你写 WHERE 条件时，涉及到的字段要有索引。没索引，MySQL 就得一行行地扫描表，全表扫描慢得你怀疑人生。
-- 创建索引CREATE INDEX idx_users_age ON users(age);
-- 使用索引查询SELECT * FROM users WHERE age > 25;
复制代码
优化原因：索引就像目录，能直接跳到你要的那页，而不是一本书从头翻到尾。
3. 用 EXPLAIN 分析 SQL 性能
写 SQL 前，别直接跑，先加个 EXPLAIN 看看执行计划。看啥？type 是不是 ALL，是不是走了全表扫描。如果有，赶紧改。
EXPLAIN SELECT * FROM users WHERE age > 25;
复制代码
优化原因：清楚执行计划，知道 SQL 跑得是不是高效。
4. 避免 WHERE 里的函数操作
别在 WHERE 条件里对字段用函数，索引直接失效。
-- 错误写法SELECT * FROM users WHERE YEAR(create_time) = 2023;
-- 正确写法SELECT * FROM users WHERE create_time >= '2023-01-01' AND create_time < '2024-01-01';
复制代码
优化原因：函数操作让索引失效，回表扫描浪费时间。
5. 避免 OR 条件，改用 UNION
WHERE 里写了个 OR，可能导致两个字段的索引全失效。别用 OR，用 UNION 替代。
-- 错误写法SELECT * FROM users WHERE age = 25 OR city = 'shenzhen';
-- 正确写法SELECT * FROM users WHERE age = 25UNIONSELECT * FROM users WHERE city = 'shenzhen';
复制代码
优化原因：OR 会让索引失效，而 UNION 可以分别利用索引。
6. 优化 LIMIT 分页
LIMIT 用得好不好直接影响性能，特别是分页到 10000 页的时候，直接卡死你。改进方法是用 ID 或时间范围过滤。
-- 错误写法SELECT * FROM users ORDER BY create_time LIMIT 10000, 10;
-- 正确写法SELECT * FROM users WHERE id > 10000 ORDER BY id LIMIT 10;
复制代码
优化原因：用范围过滤减少不必要的扫描。
7. 用覆盖索引
覆盖索引的意思是，查询的字段全在索引里，MySQL 不需要回表。
-- 创建覆盖索引CREATE INDEX idx_users_age_name ON users(age, name);
-- 查询SELECT name FROM users WHERE age > 25;
复制代码
优化原因：只从索引获取数据，不用回表，提高查询速度。
8. 减少 JOIN 表数量
JOIN 太多表会让执行计划复杂化，数据量大的表用 JOIN，性能就拉了。
-- 复杂的多表JOINSELECT * FROM ordersJOIN users ON orders.user_id = users.idJOIN products ON orders.product_id = products.id;
-- 优化：拆分查询SELECT * FROM orders WHERE user_id IN ( SELECT id FROM users WHERE age > 25);
复制代码
优化原因：减少中间表的数据处理量，降低 JOIN 复杂度。
9. 使用批量插入
单条插入写多了，性能会被写锁拖垮，换成批量插入。
-- 错误写法INSERT INTO users (id, name) VALUES (1, 'zhangsan');INSERT INTO users (id, name) VALUES (2, 'lisi');
-- 正确写法INSERT INTO users (id, name) VALUES (1, 'zhangsan'), (2, 'lisi');
复制代码
优化原因：减少数据库连接和提交的次数。
10. GROUP BY 前过滤数据
GROUP BY 本质上是对结果排序分组，数据量大时效率感人。先用 WHERE 把数据量缩小。
-- 错误写法SELECT age, COUNT(*) FROM users GROUP BY age;
-- 正确写法SELECT age, COUNT(*) FROM users WHERE age > 25 GROUP BY age;
复制代码
优化原因：减少 GROUP BY 处理的数据量。
11. LIKE 查询优化
模糊查询用 %xxx% 的时候，索引没了，直接全表扫描。改成前缀匹配，或者用全文索引。
-- 前缀匹配SELECT * FROM users WHERE name LIKE 'zhang%';
-- 全文索引CREATE FULLTEXT INDEX idx_name ON users(name);SELECT * FROM users WHERE MATCH(name) AGAINST('zhang');
复制代码
优化原因：用索引提高查询效率。
12. 避免大字段频繁查询
TEXT 和 BLOB 这种大字段会拖累查询性能，把它们单独拆到一张表。
-- 原表CREATE TABLE users ( id INT PRIMARY KEY, name VARCHAR(50), profile TEXT);
-- 拆表CREATE TABLE user_profiles ( user_id INT PRIMARY KEY, profile TEXT);
复制代码
优化原因：减少主表查询时的大字段负担。
13. 定期清理无用数据
表里垃圾数据太多，索引也跟着膨胀，查询性能直线下降。
-- 定期清理历史数据DELETE FROM logs WHERE create_time < '2022-01-01';
复制代码
优化原因：保持表轻量化，避免数据膨胀。
14. 用分区表优化大数据表
分区表是大数据量优化的利器，把数据按范围分成多个分区。
CREATE TABLE orders ( id INT NOT NULL, order_date DATE NOT NULL) PARTITION BY RANGE (YEAR(order_date)) ( PARTITION p2022 VALUES LESS THAN (2023), PARTITION p2023 VALUES LESS THAN (2024));
复制代码
优化原因：查询只扫描一个分区，而不是整张表。
15. 索引字段选用低基数的字段
基数低的字段（比如性别）用索引没意义，能用组合索引就用。
-- 错误：性别字段加索引CREATE INDEX idx_gender ON users(gender);
-- 正确：性别+年龄组合索引CREATE INDEX idx_gender_age ON users(gender, age);
复制代码
优化原因：避免低基数索引浪费存储。
16. 控制表字段数量
表字段太多会让数据表臃肿，查询性能也会变差。合理拆分表。
17. 用事务优化批量更新
大批量更新用事务，可以减少锁竞争。
START TRANSACTION;UPDATE users SET age = age + 1 WHERE age > 25;COMMIT;
复制代码
18. 用查询缓存
MySQL 有查询缓存机制，适合静态数据高频查询场景。
-- 开启查询缓存SET GLOBAL query_cache_size = 1048576;SELECT SQL_CACHE * FROM users WHERE age > 25;
复制代码
19. 用 PreparedStatement
预编译可以提高性能，还能防止 SQL 注入。
20. 定期优化表
表用了很久会有碎片，定期优化表能提高性能。
总结
SQL 优化是个手艺活，写好 SQL 就能少掉无数锅，尤其是团队开发时，一个烂 SQL 能拖死整个项目。
这 20 条技巧别只看一遍，拿着自己的查询多实践，别等线上掉链子才后悔！
评论