嵌套查询本身不直接导致索引碎片，真正引发碎片的是其触发的高频、无序、小批量临时表创建与销毁行为，尤其在sql server中频繁物化子查询到tempdb时，会因页分裂、低填充率和空间未及时回收而累积内部与外部碎片。
嵌套查询本身不会直接导致索引碎片增加，真正引发碎片的是它背后高频、无序、小批量的临时表创建与销毁行为——尤其是当嵌套逻辑被反复执行或物化为临时表时。
嵌套查询触发临时表频繁写入的典型场景
很多开发者用嵌套查询做“条件过滤中转”，比如 WHERE id IN (SELECT id FROM ...)，但数据库优化器在无法重用执行计划或内存不足时，会自动物化子查询结果到磁盘临时表。一旦该查询被高频调用（如每秒数十次），就会持续触发以下操作：
- 每次创建临时表都分配新页，且默认不指定
FILLFACTOR，页内填充率低 → 加剧内部碎片 - 临时表无主键/无聚集索引，插入顺序随机 → 页分裂频发 → 产生外部碎片
- 临时表生命周期短，刚建好就
DROP，但页空间未及时归还给系统缓存池 → 碎片残留累积 - SQL Server 中
tempdb文件增长模式若设为自动扩展（尤其小增量如 64MB），会加剧文件内碎片，间接影响临时表页布局
为什么 CREATE TEMPORARY TABLE 比 CTE 更容易放大碎片风险
CTE（如 WITH cn_users AS (...)）多数情况下只是逻辑视图，不强制落盘；而显式 CREATE TEMPORARY TABLE 是物理写入动作，哪怕只存几千行，也会走完整页分配流程。更关键的是：
- 临时表默认无索引，后续
JOIN或WHERE条件若没手动加索引，查询引擎可能回退为扫描 → 触发更多页读取和潜在分裂 - 不同会话创建同名临时表（如
#tmp）时，SQL Server 实际生成唯一后缀（如#tmp__000000000001），但tempdb的 IAM 和 PFS 页面争用会升高，进一步干扰页分配连续性 - 如果在循环或游标中反复
CREATE + INSERT + DROP，等价于对tempdb执行高频随机写，碎片增长速度远超业务表
如何验证临时表操作是否已造成碎片
别只看业务表，重点查 tempdb 中最近活跃的用户临时表（名字以 # 开头）：
SELECT
t.name AS table_name,
i.name AS index_name,
ips.avg_fragmentation_in_percent,
ips.page_count
FROM tempdb.sys.dm_db_index_physical_stats(DB_ID('tempdb'), NULL, NULL, NULL, 'LIMITED') ips
JOIN tempdb.sys.tables t ON ips.object_id = t.object_id
JOIN tempdb.sys.indexes i ON ips.index_id = i.index_id AND ips.object_id = i.object_id
WHERE t.name LIKE '#%___%' -- 匹配系统生成的临时表名
AND ips.avg_fragmentation_in_percent > 30;
注意：sys.dm_db_index_physical_stats 在 tempdb 上运行开销不小，建议仅在怀疑时段执行一次快照；若返回大量高碎片临时表，说明嵌套改写策略已反向拖累系统底层。
真正要盯住的不是语法嵌套，而是页分配模式
索引碎片是存储层现象，根源永远在“数据怎么落盘”。嵌套查询只是表层诱因，背后是否触发临时页分配、是否绕过预分配机制、是否让 tempdb 处于持续扩张状态，这些才是决定碎片程度的关键。一个 SELECT 嵌套再深，只要全程走内存哈希匹配，就不会动磁盘一页；但一次 INSERT INTO #tmp SELECT ... 却可能立刻在 tempdb 里留下不可逆的碎片痕迹。处理时优先看执行计划里的 Warning: Spill to tempdb 和 Actual Rows Read 是否远大于 Estimated —— 那才是碎片正在发生的实时信号。
