优先用 JOIN 替代三层以上子查询，避免 IN+子查询，改用 INNER JOIN 或 EXISTS；JOIN 字段须加索引，组合索引按驱动表顺序设计；慎用函数、隐式转换和跨表 GROUP BY/ORDER BY；务必用 EXPLAIN ANALYZE 验证真实执行性能。
用 JOIN 替代多层 SELECT 嵌套，尤其是三层以上
说到多层子查询，这几乎是性能问题的“重灾区”。那种一层套一层的写法，比如经典的 SELECT * FROM t1 WHERE id IN (SELECT id FROM (SELECT id FROM t2 WHERE ...))，会让数据库优化器非常头疼，很难规划出高效的执行路径。无论是 MySQL 5.7+ 还是 PostgreSQL 12+，都很容易退化成创建临时表、再进行文件排序的笨重操作。实际测试中，面对十万级别的数据，一个三层嵌套的子查询，其执行时间可能比逻辑等价的 JOIN 写法要慢上5到8倍，这个差距不容忽视。
- 首要原则是，尽量把
WHERE ... IN (SELECT ...)这类结构改写为INNER JOIN或EXISTS。后者在子查询结果集很小的时候，表现往往更稳定。 - 务必避免在
JOIN的ON条件里使用函数，像ON UPPER(t1.name) = UPPER(t2.name)这种写法，会直接导致索引失效，让查询退回全表扫描。 - 如果某些场景下确实无法避免使用子查询，那么请确保内层查询有明确的
WHERE条件进行过滤，并且只返回必需的字段，切忌使用SELECT *。
给 JOIN 字段加索引，但注意组合索引顺序
没有索引的 JOIN 字段，比如 t1.user_id = t2.id，其后果就是全表扫描。尤其是当被驱动表（比如这里的 t2）数据量很大时，驱动表（t1）的每一行记录，都会触发一次对 t2 的全表扫描——这就是所谓的“嵌套循环爆炸”，性能会呈指数级下降。不过，光是给字段加上单列索引可能还不够，面对组合查询条件时，索引的设计顺序必须考虑查询的实际驱动顺序。
- 如果执行计划显示
t1是驱动表，t2是被驱动表，那么在t2上建立的索引就应该是(id, status, created_at)这样的覆盖索引，而不仅仅是(id)。覆盖索引能避免回表，效率更高。 - 查看
EXPLAIN输出时，要特别关注type列。如果出现ALL（全表扫描）或index（全索引扫描），就需要警惕了；理想的状态应该是ref或eq_ref。 - 对于 PostgreSQL 用户，还需要留意
JOIN字段的数据类型是否严格一致。例如，int4和int8之间的隐式转换，同样会导致索引无法使用。
GROUP BY 和 ORDER BY 涉及多表时，避免跨表字段混用
来看一个典型的“坑”：SELECT t1.name, COUNT(*) FROM t1 JOIN t2 ON t1.id = t2.t1_id GROUP BY t2.category ORDER BY t1.created_at DESC。这里的 ORDER BY 使用了非 GROUP BY 的字段（t1.created_at）。在 MySQL 5.7 的严格模式下，这会直接导致语法错误。即便在不严格的模式下能够执行，数据库也不得不使用临时表和文件排序来完成操作，性能损耗巨大。
- 解决思路有两种：要么把
t1.created_at也加入到GROUP BY子句中（但这可能会改变查询的语义和分组结果），要么就改用窗口函数，例如ROW_NUMBER() OVER (PARTITION BY t2.category ORDER BY t1.created_at DESC)。 - PostgreSQL 对
SELECT列表是否严格属于GROUP BY的检查相对宽松，但性能隐患依然存在——它可能会因此选择错误的分组算法。 - 如果业务需求只是要取出每个分组中的最新一条记录，不要使用先查
MAX(created_at)再关联回原表的方法。更高效的做法是使用DISTINCT ON（PostgreSQL 特有）或通用的ROW_NUMBER()窗口函数。
用 EXPLAIN ANALYZE 看真实执行路径，别信 EXPLAIN 的预估
这一点至关重要：EXPLAIN 命令给出的只是基于统计信息的成本估算，而 EXPLAIN ANALYZE（PostgreSQL）或者 EXPLAIN FORMAT=JSON 配合查询性能剖析（MySQL）才能揭示查询的真实执行细节。我们经常看到“预估扫描100行，实际却扫了20万行”的情况，问题根源往往在于统计信息过时，或者发生了隐式的数据类型转换。
- 在 MySQL 中，执行查询后可以立刻使用
SHOW PROFILE FOR QUERY N命令，重点观察Copying to tmp table（复制到临时表）和Sorting result（排序结果）这两个阶段的时间占比。 - 在 PostgreSQL 的
EXPLAIN ANALYZE输出中，如果Actual Rows（实际返回行数）远大于Rows Removed by Filter（被过滤掉的行数），那就说明WHERE条件没有有效利用索引，或者索引的选择性太差。 - 定期更新统计信息是保持优化器“聪明”的关键。在 MySQL 中使用
ANALYZE TABLE，在 PostgreSQL 中使用VACUUM ANALYZE，不要完全依赖数据库的自动更新机制。
真正的复杂性在于，不同的数据库对同一条 SQL 语句的优化策略可能大相径庭。MySQL 更依赖于驱动表的顺序；PostgreSQL 则更“吃”统计信息的准确度；而 SQLite 则基本不会重排 JOIN 的顺序。最容易被忽略的一个事实是：你以为的“小表驱动大表”这个黄金法则，在真实、复杂的数据分布面前，有时可能完全失效。这才是关键所在。
