企业官网建设流程全解析

有个订单列表慢查询，我印象很深。SQL 很短，逻辑也不复杂，就是一个WHERE user_id IN (SELECT ...)，可线上就是稳定地跑 2 秒多。看执行计划才发现，MySQL 根本没有按我脑子里“先查出子查询结果，再一次性匹配”的顺序去执行，而是对外层每一行都去内层查一遍，活生生打出了一堆重复扫描。

这种情况，就是典型的子查询物化优化（Materialization）可以解决的场景。物化的思路其实特别简单：优化器把子查询的结果先执行一次，落到一张内部临时表里，再和外层表做匹配。放在 PHP 场景下，你不光能依赖数据库优化器自动物化，还可以在 PHP 代码里做“人工物化”——把子查询结果取出来存数组、分批 IN，或者手动建临时表 JOIN。这篇文章想把这些做法讲透，适合那些写 PHP、经常处理 MySQL 慢查询、又不想一上来就堆 Redis 或改表结构的开发者。看完你至少能自己复现一次快慢对比，也知道该在什么时候信优化器，什么时候自己动手。

1. 子查询物化：它到底解决了什么问题

1.1 先看一个“看起来没毛病”的烂 SQL

假设我们有这样两张表，一张订单表order_info，一张会员等级表user_vip_level，要查所有 VIP 等级大于等于 3 的用户订单：

SELECT o.id, o.order_no, o.pay_amount FROM order_info o WHERE o.user_id IN ( SELECT v.user_id FROM user_vip_level v WHERE v.level >= 3 );

两张表的数据量大概是：order_info有上百万行，user_id上虽然有普通索引，但用户购频次高、索引区分度很一般；user_vip_level有十来万行，level字段有索引，满足条件的用户约 3 万。这条 SQL 的原始执行计划里，出现了DEPENDENT SUBQUERY，意思就是优化器把原本非相关的IN子查询，改写成了“外层每一行都去执行一次子查询”的形式。外层有 100 万行，内层就被反复驱动了 100 万次，虽然内层走索引快，但架不住一次一次重复调用，最终反馈到接口上就是稳定慢查询。

这种“把外层的每一行都拿去匹配子查询”的方式，本质上和 PHP 里最遭人恨的 N+1 查询是一回事。你写 SQL 时的意图是“先圈出 3 万个 VIP 用户，再在订单表里过滤”，但优化器在没启用物化优化、或者成本估算不准的时候，选择了逐行驱动。这里最容易踩的坑，是有些同学一看 SQL 不是JOIN就觉得没救了，或者条件反射地开始拆多个 SQL，其实 MySQL 5.6 之后本来就有专门的优化策略，只是它没有次次都按你想的那么干。

1.2 优化器为什么要物化子查询

物化的英文是 Materialization，字面意思就是“实打实地变成一份数据”。优化器会把子查询的执行结果保存到一张内部临时表，然后外层查询再去和这张临时表做关联。和逐行执行子查询相比，它的优势非常实在：

• 子查询只执行一次，重复的工作不再重复做。
• 临时表在生成时会考虑加内部索引，用于快速探测，不用每次都全表扫。
• 对IN这类集合匹配，临时表可能会顺带去重，减少后续匹配量。

用一个生活里的类比来说：你周末想照着菜谱做十道菜，正常人的做法是先网上买好所有需要的食材，一次性把菜送到家，做菜的时候直接拿；而不是每做一道菜，就重新跑一趟菜市场，买这一道菜需要的东西。物化就是“先一次性备料”，相关子查询逐行执行就是“每道菜跑一趟菜市场”。

但物化也不是没有成本。子查询结果集越大，生成临时表耗时越长，占用的内存/磁盘越多；如果临时表还要再扫描、再排序，总成本可能比逐行执行还高。所以 MySQL 的优化器不会无脑物化，它会根据表统计信息做代价估算，只有算出来物化的总开销更划算，才会选择物化。这就是为什么同一个 SQL，在不同版本、不同数据分布、不同索引条件下，执行计划可能完全不一样。

1.3 物化的触发前提与限制条件

为了在日常 SQL 里少“赌”优化器的运气，可以先记住物化生效的大前提：

• 子查询必须是非相关子查询，也就是子查询内部不能引用外层查询的字段。
• 优化器开关materialization处于开启状态，一般 MySQL 5.6 及以上默认开启。
• 成本估算认为物化比相关子查询/其他半连接优化更划算。
• 子查询的结果集不要大到让内部临时表直接落盘，否则物化带来的额外 I/O 反而更快暴露问题。

还有一个容易忽略的细节：NOT IN和<> ALL这类“取反”写法，优化器处理起来会更谨慎，特别是当子查询结果里出现NULL时，集合语义会变得很奇怪。很多人遇到过“用NOT IN查出来是空”的问题，这跟物化关系不大，但会影响你改写 SQL 的方向。实战中我一般优先用NOT EXISTS或LEFT JOIN ... WHERE IS NULL代替NOT IN，避免在线上一惊一乍。

记住一个原则：物化是优化器的“可选策略”，不是你写完 SQL 就必然触发的能力。后面 PHP 侧的做法，其实就是在“优化器不想物化”的时候，把物化的动作强行搬到应用层。

2. PHP 场景下怎么把物化优势用起来

2.1 先检查服务端 optimizer_switch

在 PHP 项目里折腾物化优化，我建议第一步先看 MySQL 的优化器开关，别一上来就改 SQL。登录到数据库执行：

SHOW VARIABLES LIKE 'optimizer_switch';

返回结果会是一长串flag=on/off的组合值，重点关注这三项：materialization、semijoin、duplicateweedout。如果materialization=off，那不管你怎么写 SQL，物化这条路基本是被堵死的，需要先打开：

SET GLOBAL optimizer_switch = 'materialization=on,semijoin=on';

注意SET GLOBAL只对后续新连接生效，已经建立的长连接（比如 PHP-FPM 里常驻的连接池）不会自动跟上。为了保险，执行完开关切换后，最好让 PHP 侧重连一次，或者干脆把相关配置写进 MySQL 配置文件里，避免重启后失效。

和物化有直接关系的内存参数是tmp_table_size与max_heap_table_size。物化生成的内部临时表，并不会乖乖在 InnoDB 表空间里待着，而是先走内存临时表，一旦大小超过两个参数中较小的那个，就会转成磁盘临时表。很多项目把这两个参数默认值留得很小，比如 16MB，结果子查询筛出 30 万行数据，临时表转磁盘后查询性能立刻崩。实践经验是让它们不小于 64MB，如果服务器内存充裕且内层临时表业务常见，可以放宽到 128MB，但也要盯着磁盘 I/O 和临时空间，不能盲目调大。

2.2 PHP 手工物化：拆两层查询再合并

当数据库优化器不给力，或者你担心统计信息不准导致物化策略不稳定，PHP 代码里完全可以自己把“物化”做一遍。核心就三步：先查子查询结果，把结果装进 PHP 数组，再把这个数组作为参数拼到外层查询的IN里。

用 PDO 写一个最简单版本：

$pdo = new PDO('mysql:host=127.0.0.1;dbname=app', 'user', 'pass', [ PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION, ]); // 第一步：先物化子查询结果到 PHP 数组 $stmt = $pdo->query("SELECT user_id FROM user_vip_level WHERE level >= 3"); $ids = $stmt->fetchAll(PDO::FETCH_COLUMN); if (empty($ids)) { return []; // 子查询没结果，直接返回空，避免 IN () 语法错误 } // 第二步：把数组安全地拼进 IN 占位符 $placeholders = implode(',', array_fill(0, count($ids), '?')); $sql = "SELECT id, order_no, pay_amount FROM order_info WHERE user_id IN ($placeholders)"; $stmt = $pdo->prepare($sql); $stmt->execute($ids); $orders = $stmt->fetchAll(PDO::FETCH_ASSOC);

这种写法真正的优势是“执行计划可控”。子查询单独走索引过滤，外层查询因为IN列表是固定值，优化器大概率走range访问，不会再搞出DEPENDENT SUBQUERY。对于 3 万级别的user_id列表，PDO 拼接占位符没有任何压力。

但有一个现实约束必须说：一次IN的列表太长时，可能出现两个问题。一是占位符数量超过 MySQL 预处理语句的上限；二是拼接出来的 SQL 文本过长，接近max_allowed_packet限制。稳妥的做法是分批，比如每 5000 个 ID 一批，循环多次查询后再合并$orders：

$orders = []; foreach (array_chunk($ids, 5000) as $chunk) { $placeholders = implode(',', array_fill(0, count($chunk), '?')); $sql = "SELECT id, order_no, pay_amount FROM order_info WHERE user_id IN ($placeholders)"; $stmt = $pdo->prepare($sql); $stmt->execute($chunk); foreach ($stmt->fetchAll(PDO::FETCH_ASSOC) as $row) { $orders[] = $row; } }

这里分批的 5000 是个经验值，既不会把单条 SQL 撑爆，又能让索引下推和回表保持在比较高效的状态。如果你用 Redis 缓存了这个 VIP 用户列表，物化结果甚至不用每次查库，直接把 PHP 数组换成从缓存读出来的 ID，速度还能再上一个台阶。

2.3 用临时表做显式物化

PHP 数组分批 IN 的方式，在小结果集里非常好用，但一旦子查询结果集变大，比如几十万行，拼一个巨长的 SQL 文本就变得很笨拙。这时候更接近数据库原生物化意图的，是直接在 MySQL 里建一张临时表，把子查询结果插进去，然后让外层查询 JOIN 这张临时表。

$pdo->exec("DROP TEMPORARY TABLE IF EXISTS tmp_vip_users"); $pdo->exec(" CREATE TEMPORARY TABLE tmp_vip_users ( user_id INT PRIMARY KEY ) ENGINE=InnoDB "); $insert = $pdo->prepare("INSERT INTO tmp_vip_users (user_id) VALUES (?)"); $pdo->beginTransaction(); foreach ($ids as $id) { $insert->execute([$id]); } $pdo->commit(); $sql = " SELECT o.id, o.order_no, o.pay_amount FROM order_info o INNER JOIN tmp_vip_users t ON o.user_id = t.user_id "; $orders = $pdo->query($sql)->fetchAll(PDO::FETCH_ASSOC);

给user_id建主键很重要，否则 JOIN 的时候临时表没索引，MySQL 会对临时表做全表扫描，比优化器自己搞的物化还慢。临时表的生命周期只属于当前数据库连接，PHP-FPM 脚本结束连接就断开，临时表自动消失，所以不用手动清理。

这个方案适合什么场景？同一段 PHP 脚本里你不止查一次，而是要把这 3 万个 VIP 用户和订单表、退款表、售后表分别 JOIN 多次。把物化结果落成临时表，相当于建了一次可复用索引，后面每张业务表都去“探测”同一张高权限用户表，省去了重复取数、重复拼 SQL 的成本。

3. 实操复盘：从 2.8 秒到 0.3 秒

3.1 复现环境与原始执行计划

为了说清楚整个优化过程，我在一个模拟项目里复现了场景。MySQL 版本 8.0，order_info表 200 万行，user_vip_level表 12 万行，其中level >= 3的用户约 3 万行。我第一次跑原始 SQL 时，耗时 2.8 秒。执行计划的关键部分如下：

type=ALL意味着外层订单表走了全表扫描，因为user_id索引区分度不高，优化器认为回表找行的成本比全表还高。全表 200 万行，每一行都要去user_vip_level里探测一遍等级是否达标，这相当于把子查询执行了上百万次。哪怕单次探测只要 0.1 毫秒，乘以 200 万也是 200 秒的理论量级，实际因为有缓存、有索引成本模型影响，最后稳定在 2.8 秒，但已经足以拖垮接口了。

看到这个执行计划的第一步，不是急着改代码，而是先检查统计信息。我执行了：

ANALYZE TABLE user_vip_level, order_info;

统计信息刷新后，同样一条 SQL 的耗时已经有了下降，因为优化器拿到了更准确的rows估算，但执行计划还是没用上物化。接着我试了给子查询结果加DISTINCT：

SELECT o.id, o.order_no, o.pay_amount FROM order_info o WHERE o.user_id IN ( SELECT DISTINCT v.user_id FROM user_vip_level v WHERE v.level >= 3 );

这个改写的出发点是让优化器意识到子查询结果需要去重，而物化临时表天生就适合做这件事。执行计划立刻出现了变化：order_info仍然走全表扫描，但内层不再显示DEPENDENT SUBQUERY，而是出现了一个物化子查询节点，外层通过临时表的内部索引去探测。

3.2 用 EXPLAIN ANALYZE 看物化节点

MySQL 8.0 提供的EXPLAIN ANALYZE非常值得在测试环境用起来。它会真实执行 SQL，并把每个节点的执行耗时和行数反馈出来：

EXPLAIN ANALYZE SELECT o.id, o.order_no FROM order_info o WHERE o.user_id IN ( SELECT DISTINCT v.user_id FROM user_vip_level v WHERE v.level >= 3 );

输出里能看到类似下面的结构：

-> Nested loop inner join (actual time=0.030..310.123 rows=230000) -> Table scan on o (actual time=0.025..260.123 rows=2000000) -> Single-row index lookup on <m2> using <auto_key> (actual time=0.001..0.003 rows=1) -> Materialize (actual time=15.230..18.300 rows=30000)

这个执行计划里，Materialize节点只花了几十毫秒生成 3 万行临时表，随后外层 200 万行订单通过<auto_key>索引逐行探测，单行探测时间非常短。整条 SQL 从 2.8 秒压到了 0.3 秒左右，效果非常明显。

物化临时表的大小也可以估算一下：3 万行的user_id是 INT 类型，4 字节；加上行开销和内部索引，撑死几百 KB。这个规模远低于tmp_table_size，所以临时表全程留在内存里，没有任何磁盘 I/O，这 0.3 秒基本都花在扫 200 万行订单表上，属于不可避免的成本。如果子查询里还带了VARCHAR(255)描述字段、TEXT备注字段，临时表体积会成倍增长，一个不注意就会落盘，物化的优势就会衰减。

3.3 人工物化方案的实测对比

我还在同一份数据上跑了 PHP 数组分批 IN 的方案，结果也很有参考价值。先单独查 3 万个 VIP 用户 ID，耗时约 20ms；把它们分成每批 5000 个，共 6 批去查订单表，每批耗时大约 50ms；合计约 320ms。整体性能和数据库自动物化几乎平手，优点是不再依赖优化器，代码逻辑对后来的维护者非常直白。

临时表方案的性能更稳，因为索引结构更接近原生物化。3 万行单条插入在没开事务时会比较慢，我的做法是开事务、每 1000 行提交一次，插入耗时约 200ms；再和订单表 JOIN 查询耗时约 200ms；总耗时约 400ms。虽然看起来比自动物化慢一点，但它的优势在于同一份临时表可以复用，如果后续还要和退款表、售后表继续 JOIN，总体成本会快速摊薄。

三套方案的取舍，关键看你的业务是“一次性查询”还是“同一批条件反复查询”。一次性查询让数据库自动物化最省事；接缓存选 PHP 数组最灵活；查多张业务表选临时表 JOIN 最划算。

4. 常见坑与排查技巧实录

4.1 物化没生效的排查顺序

项目里最常见的反馈就是“我也用了子查询，为什么没变快？” 我的建议是按下面这个顺序排查，不要盲改 SQL：

1. 确认 MySQL 版本：物化优化 MySQL 5.6 才开始引入，5.5 及更早版本根本没有这个开关，老项目升级要谨慎。
2. 确认 optimizer_switch 没被关：执行SHOW VARIABLES LIKE 'optimizer_switch'，重点看materialization和semijoin。
3. 确认子查询不是相关子查询：子查询里只要出现了外层表的别名，优化器就不能直接物化。
4. 刷新统计信息：用ANALYZE TABLE让优化器拿到更准确的rows，成本估算才有可能倾向于物化。
5. 检查临时表是否落盘：如果tmp_table_size设置太小，物化临时表会转磁盘，EXPLAIN ANALYZE的Materialize节点会出现明显变慢。

排查过程中推荐把EXPLAIN FORMAT=TREE的输出贴在收藏夹里，它比传统EXPLAIN更直观，能直接看到有没有Materialize节点。但注意EXPLAIN ANALYZE会真实执行 SQL，线上高并发环境慎用，先在压测环境跑出预期结果再推广。

4.2 “IN 一定慢，EXISTS 一定快”是过时经验

在 PHP 技术社区里，常常能翻到“用EXISTS代替IN性能更好”的祖传经验。这个经验在 MySQL 5.1、5.5 时代确实有一定道理，因为当时的优化器没有物化、没有半连接优化，IN子查询容易变成逐行相关子查询。

但现在把EXISTS当成万能解药，很容易翻车。EXISTS的本质是相关子查询，MySQL 很可能对外层每一行执行一次探测，如果外层是大表，退化成 N+1 的经典问题。反过来，IN在 5.6 之后普遍能走物化或半连接自动优化，只要子查询结果不是天文数字，反而更适合“先圈定集合再匹配”的语义。

给出一个简单选择参考：

至于NOT IN和NOT EXISTS，通常我更推荐NOT EXISTS，因为它不受子查询结果里NULL值的干扰。你要是坚持用NOT IN，得先确保子查询字段非空，否则可能查出一个让你抓狂的空结果。

4.3 子查询结果实在很大的时候怎么办

物化不是银弹，当子查询结果集大到几百万行、或者字段里塞满了TEXT/BLOB，内存临时表根本装不下，物化会变成一场磁盘临时表的灾难。这时候我在实际项目里一般会往三个方向调整：

• 应用层加缓存：把子查询结果先缓到 Redis，比如高等级用户 ID 集合，PHP 直接从缓存拉到数组再分页匹配。但要注意集合过大时，Redis 里存大 key 也不划算，最好用集合类型或者定期刷新汇总表。
• 做汇总表/数据冗余：提前把“符合条件的用户和订单关系”同步到一张专门的汇总表，业务查询直接打这张表。比如消息推送、会员权益这类高频次查询，没必要每次实时算。
• 任务分批错峰：实在要大批量处理，就改成后台任务，按用户 ID 分片跑，每次只处理几万行，避免单个 SQL 扛下所有压力。

如果你确实需要在 SQL 里物化一个很大的子查询，有一个小技巧：尽量只物化必要的列。子查询里只留user_id这种用来关联的键，不要在子查询里SELECT *，这样临时表的体积能小一个数量级，落盘概率大幅下降。

最后分享一个我自己的习惯：每次调完这类 SQL，我都会把EXPLAIN FORMAT=TREE的输出摘录到代码注释里，或者放在项目文档的 SQL 优化记录中。下次有人再改这段代码，扫一眼注释就知道当初为什么改、数据库是怎么执行的，不用重新踩一遍“为什么物化没生效”的坑。物化优化表面上是数据库参数和 SQL 形态的事，但在 PHP 项目里真正落地时，它考验的还是你对执行计划的敏感度和持续观察的习惯。