SQL优化的20条军规
前言
作为一个写SQL的程序员,代码写得好不好是一回事,但SQL写得烂,性能拉胯,全公司都得为你的慢查询买单,尤其在大数据量表上,SQL写不好就是“内鬼”级别的错误。
今天不整那些虚的,直接来点硬货:20条SQL优化小技巧,每一条都能救你一命,关键是,我还给你配上代码例子,拿去直接用,希望对你会有所帮助。
我最近开源了一个基于 SpringBoot+Vue+uniapp 的商城项目,里面的技术亮点挺多的,欢迎访问和star。[https://gitee.com/dvsusan/susan_mall]
1. 避免SELECT *
别一上来就 SELECT *
,你觉得这样写省事,但数据库得把整张表的每一列都给你搬过来,代价大得吓人。需要啥列写清楚,别偷懒。
-- 错误写法
SELECT * FROM users;
-- 正确写法
SELECT id, name, age FROM users;
优化原因:减少网络传输数据量,避免无用字段占内存。
最后说一句(求关注,别白嫖我)
如果这篇文章对您有所帮助,或者有所启发的话,帮忙关注一下我的同名公众号:苏三说技术,您的支持是我坚持写作最大的动力。
求一键三连:点赞、转发、在看。
关注公众号:【苏三说技术】,在公众号中回复:进大厂,可以免费获取我最近整理的10万字的面试宝典,好多小伙伴靠这个宝典拿到了多家大厂的offer。
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 = 25
UNION
SELECT * 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,性能就拉了。
-- 复杂的多表JOIN
SELECT * FROM orders
JOIN users ON orders.user_id = users.id
JOIN 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. 定期优化表
表用了很久会有碎片,定期优化表能提高性能。
OPTIMIZE TABLE users;
总结
SQL 优化是个手艺活,写好 SQL 就能少掉无数锅,尤其是团队开发时,一个烂 SQL 能拖死整个项目。
这 20 条技巧别只看一遍,拿着自己的查询多实践,别等线上掉链子才后悔!
【推荐】编程新体验,更懂你的AI,立即体验豆包MarsCode编程助手
【推荐】凌霞软件回馈社区,博客园 & 1Panel & Halo 联合会员上线
【推荐】抖音旗下AI助手豆包,你的智能百科全书,全免费不限次数
【推荐】博客园社区专享云产品让利特惠,阿里云新客6.5折上折
【推荐】轻量又高性能的 SSH 工具 IShell:AI 加持,快人一步
· DeepSeek “源神”启动!「GitHub 热点速览」
· 微软正式发布.NET 10 Preview 1:开启下一代开发框架新篇章
· C# 集成 DeepSeek 模型实现 AI 私有化(本地部署与 API 调用教程)
· DeepSeek R1 简明指南:架构、训练、本地部署及硬件要求
· 2 本地部署DeepSeek模型构建本地知识库+联网搜索详细步骤