分类:数据库 / SQL 优化 | 阅读时间:约 8 分钟同样一条业务需求,SQL 写得好和写得糟,查询耗时可能相差几十倍。本文用「反例 vs 正例」的方式,拆解 4 个最常见、也最容易改进的写法,并配示意图讲清背后的原理。全部为通用 SQL 思路,可迁移到 MySQL、PostgreSQL、SQL Server 等主流数据库。目录前言:为什么同样的结果,性能差这么多一、提前过滤:JOIN 之前先缩小数据集二、别让子查询重复劳动:用 CTE 只计算一次三、让 WHERE 走索引:避免全表扫描四、告别 SELECT *:只取你需要的列一图总结:4 个优化点对照最后几点提醒前言:为什么同样的结果,性能差这么多SQL 是声明式语言——你只描述「想要什么」,由数据库的查询优化器决定「怎么执行」。但优化器并非万能:当你的写法给了它更小的数据集、更清晰的意图、更容易命中的索引时,它就能生成更高效的执行计划。下面这 4 个点,是日常开发中最高频的「性能坏味道」。每一个都给出反例、正例、原理示意图和注意事项。建议边看边用EXPLAIN在自己的库上验证。一、提前过滤:JOIN 之前先缩小数据集JOIN 的开销与参与连接的行数强相关。如果先把两张大表连接起来,再用WHERE丢弃大部分行,等于让数据库做了大量「白工」。更好的做法是:先过滤、再连接。❌ 反例 · 先 JOIN 再过滤SELECTt1.column_1,t2.column_2FROMtable1 t1JOINtable2 t2ONt1.foreign_idt2.primary_idWHEREt2.filter_columnsome_value;✅ 正例 · 用 CTE 先过滤,再 JOINWITHcteAS(SELECT*FROMtable2WHEREfilter_columnsome_value)SELECTt1.column_1,cte.column_2FROMtable1 t1JOINcteONt1.foreign_idcte.primary_id;原理:先在 CTE 里把table2过滤成一小撮行,再拿去和table1连接,中间结果集大幅变小,连接、排序、内存占用都随之下降。这在数据库术语里叫「谓词下推(predicate pushdown)」。注意:现代优化器很多时候会自动帮你下推过滤条件。但当涉及视图、子查询、在列上使用函数、或较复杂的连接时,自动下推可能失效。显式地先过滤,既是一种保险,也让 SQL 的意图更清晰。二、别让子查询重复劳动:用 CTE 只计算一次需要同一份计算的多个结果(比如同一组连接的MAX和MIN)时,如果写成两个独立的相关子查询,数据库就会把同样的 JOIN 聚合执行两遍。用一个 CTE 计算一次、多处引用,能避免这种重复劳动。❌ 反例 · 两个子查询,各扫一遍SELECTcolumn_1,(SELECTMAX(column2)FROMtable1 t1JOINtable2 t2ONt1.column3t2.column3)ASmax_value,(SELECTMIN(column2)FROMtable1 t1JOINtable2 t2ONt1.column3t2.column3)ASmin_valueFROMtable3;✅ 正例 · 一个 CTE,算一次复用WITHcteAS(SELECTMAX(column2)ASmax_value,MIN(column2)ASmin_valueFROMtable1 t1JOINtable2 t2ONt1.column3t2.column3)SELECTcolumn_1,cte.max_value,cte.min_valueFROMtable3,cte;原理:两个子查询里的 JOIN 聚合完全相同,却被独立执行了两次,开销近乎翻倍。把它抽到 CTE 中只算一次,后续两处直接引用结果列。注意:CTE 是否「物化(算一次存起来)」因数据库而异。PostgreSQL 12 在无副作用且被多次引用时可能内联优化;SQL Server、MySQL 8 的处理方式也不同。若担心被反复计算,可考虑临时表或MATERIALIZED提示,并用执行计划确认。三、让 WHERE 走索引:避免全表扫描过滤条件落在有索引的列上,数据库可以像查字典一样快速定位;落在无索引的列上,则只能逐行扫描整张表。数据量越大,差距越夸张。❌ 反例 · 过滤无索引列SELECTcolumn_1FROMtable_nameWHEREunindexed_columnsome_value;✅ 正例 · 过滤有索引列-- 先为常用过滤列建索引CREATEINDEXidx_colONtable_name(indexed_column);SELECTcolumn_1FROMtable_nameWHEREindexed_columnsome_value;原理:B-Tree 索引让查找的复杂度从全表扫描的O(n)降到约O(log n)。100 万行里精确查一个值,全表扫描要逐行比较,走索引往往只需读取十几个节点。索引不是越多越好:每个索引都会增加写入(INSERT/UPDATE/DELETE)成本和存储开销。应针对高频过滤、连接、排序的列建索引,而非盲目加。当心让索引「失效」的写法:在索引列上套函数,如WHERE YEAR(created_at) 2025—— 改为范围条件created_at 2025-01-01 AND created_at 2026-01-01。隐式类型转换,如用字符串索引列去比较数字。前导通配的模糊匹配LIKE %abc—— 无法利用普通 B-Tree 索引。四、告别 SELECT *:只取你需要的列SELECT *图一时省事,却让数据库读取并传输大量你根本用不到的列。明确列出所需字段,几乎在所有维度上都更优。❌ 反例 · SELECT *SELECT*FROMtable_name;✅ 正例 · 只选需要的列SELECTcolumn1,column2,column3FROMtable_name;为什么更好:更少的磁盘 I/O 与网络传输;更小的内存占用;更容易命中「覆盖索引」(所需列都在索引里,无需回表);还能避免表结构变更后SELECT *悄悄带出新列、撑爆下游或破坏INSERT ... SELECT的列对应。一图总结:4 个优化点对照优化点坏味道(反例)优化写法(正例)提前过滤先 JOIN 大表,再 WHERE 丢弃用 CTE 先过滤,再 JOIN 小结果避免重复计算多个相关子查询,重复扫描/聚合抽成一个 CTE,算一次多处引用走索引过滤WHERE 落在无索引列 → 全表扫描过滤列建索引,并避免索引失效写法按需取列SELECT *读取全部列只 SELECT 需要的列,利于覆盖索引最后几点提醒先测量,再优化。用EXPLAIN/EXPLAIN ANALYZE看真实执行计划,别凭感觉。优化器很聪明但不万能。上面的「正例」在简单场景下优化器可能已经帮你做了;但写清楚意图,能在复杂场景下更稳定地拿到好计划。行为因数据库而异。CTE 是否物化、索引选择策略等在 MySQL、PostgreSQL、SQL Server 上各有差异,以你实际使用的数据库为准。可读性同样是收益。这些写法不仅更快,通常也更清晰、更易维护。结语:把这 4 个习惯刻进肌肉记忆,大多数日常查询的性能就已经赢在起跑线上了。真正的调优,始于一次EXPLAIN。