SQL查询优化

下面给出可直接替换的查询改写、索引与参数调优建议，并逐条说明优化原因，聚焦于执行计划、资源消耗、查询效率、索引使用。

一、SQL改写（去掉CTE屏障、让规划器可并行/内联、使用可命中GIN的JSONB谓词） -- 目标：在一次聚合内完成，尽量让优化器自由重排/并行，减少不必要的物化与I/O SELECT category_id, product_id, total_qty, total_amount FROM ( SELECT p.category_id, s.product_id, SUM(s.qty) AS total_qty, SUM(s.amount) AS total_amount, ROW_NUMBER() OVER ( PARTITION BY p.category_id ORDER BY SUM(s.amount) DESC ) AS rn FROM sales s JOIN ( SELECT id, category_id FROM products WHERE status = 'active' AND attrs @> '{"season":"winter"}' -- 利用GIN的@>包含查询 ) p ON p.id = s.product_id WHERE s.sold_at >= now() - interval '90 day' GROUP BY p.category_id, s.product_id ) x WHERE rn <= 10 ORDER BY category_id, total_amount DESC;

与原SQL相比的关键点

• 去掉了WITH CTE分段：在PG12+中CTE通常会被内联，但在存在复杂成本估计或低版本时可能物化。改成子查询/内联能让优化器做并行、谓词下推与更优连接顺序。
• JSONB过滤改为attrs @> '{"season":"winter"}'：现有的GIN_products_attrs (jsonb_ops)对@>非常友好；原来的attrs->>'season' = 'winter'通常不能直接命中该GIN（除非额外有函数索引）。
• 将产品筛选提到JOIN子查询中：帮助优化器优先使用较小的“活跃+冬季”产品集驱动连接，再借助sales(product_id, sold_at)索引做回表，显著降低sales的扫描量。

二、索引优化建议（按收益从高到低）

1. 覆盖式索引：sales(product_id, sold_at) INCLUDE (qty, amount)

• 语句： CREATE INDEX CONCURRENTLY idx_sales_prod_time_cover ON sales (product_id, sold_at) INCLUDE (qty, amount);
• 原因与效果：
  • 你已有(product_id, sold_at)索引，但聚合还需要qty/amount；INCLUDE可以显著增加“索引仅扫描”的概率（可减少大量Heap访问与随机I/O），尤其在90天窗口内数据经常被VACUUM标记为all-visible时收益明显。
  • 该索引完全匹配“按product_id定位后按时间过滤”的访问模式，并支撑聚合所需列。

2. 产品侧“活跃+冬季”部分覆盖索引（减少回表，提升Join与取category_id成本）

• 语句（PG11+，利用INCLUDE）： CREATE INDEX CONCURRENTLY idx_products_active_winter_id_cat ON products (id) INCLUDE (category_id) WHERE status = 'active' AND (attrs->>'season') = 'winter';
• 原因与效果：
  • 连接键是p.id，聚合要用p.category_id。该“部分覆盖索引”使连接获取category_id时无需回表（Index Only Scan），且仅覆盖到真正需要的子集（active+winter），I/O大幅下降。
  • 比“(category_id, id)”更对口，因为Join按id等值匹配。

3. 让JSONB过滤稳定命中索引的两种选择（二选一）

• 如果你更倾向写attrs->>'season' = 'winter'： CREATE INDEX CONCURRENTLY idx_products_season_expr ON products ((attrs->>'season')); 并配合WHERE status='active'多用： CREATE INDEX CONCURRENTLY idx_products_active_season_expr ON products ((attrs->>'season')) WHERE status='active';
• 如果更偏向@>（推荐，已在SQL改用@>）： 现有GIN（jsonb_ops）可用；若“季节”字段查询非常高频，也可做一个“活跃”部分GIN： CREATE INDEX CONCURRENTLY gin_products_attrs_active ON products USING GIN (attrs jsonb_path_ops) WHERE status='active'; 注：jsonb_path_ops对@>更紧凑，内存和匹配效率更好，但功能略窄；jsonb_ops已足够则不必新增。

三、执行计划预期与验证要点

1. 期望的计划轮廓

• 先扫描小集合的products（Bitmap/Index Scan）得到active+winter的id与category_id；
• Hash/Bitmap/Nested Loop Join到sales：
  • 若驱动为products，访问sales时走 idx_sales_prod_time_cover(product_id, sold_at) 做索引范围扫描（sold_at >= now() - 90d）；
  • 对sales侧触发并行扫描与并行聚合（Parallel HashAggregate）。
• HashAggregate按(category_id, product_id)汇总；
• Window（ROW_NUMBER）在聚合结果上做分区排序，结果集量级已被大幅收缩；
• 外层过滤rn <= 10，再排序输出（小结果，代价很低）。

2. 用EXPLAIN (ANALYZE, BUFFERS) 验证

• 关注点：
  • 没有“CTE Scan”/“Materialize”导致的大量I/O；
  • products使用索引（Bitmap/Index Scan）且行数估计接近；
  • sales侧使用 idx_sales_prod_time_cover，堆块(Hits)较少、索引块(Hits)为主；
  • HashAggregate/Sort 没有发生磁盘溢出（注意Sort Method: external/ spilled）；
  • 并行度（Workers Planned/Launched）> 0。

四、运行参数与统计建议

• work_mem：窗口排序与分组建议每个worker至少64–128MB（根据并发适当权衡）。若EXPLAIN显示溢出到磁盘，增大到不溢出为宜。
• max_parallel_workers_per_gather：2–4（视硬件与并发），确保并行聚合能生效。
• effective_cache_size：按机器内存合理设置（如内存的50–75%），帮助成本估计器更激进地使用索引。
• 针对表达式的统计（帮助选择合适计划，尤其是JSONB键与status的联合选择度） CREATE STATISTICS stats_products_season_status (mcv) ON (attrs->>'season'), status FROM products; ANALYZE products;
• 定期ANALYZE sales, products；大型表设置更高统计目标： ALTER TABLE products ALTER COLUMN attrs SET STATISTICS 1000;

五、进一步的结构性优化（若该查询高频）

• 预聚合/物化明细
  • 建每日级别的增量汇总表 sales_agg_daily(product_id, day, qty_sum, amount_sum)，查询聚合只需汇总近90天的<=90行/产品，大幅降低CPU与I/O；
  • 可用触发器/定时任务或流式ETL增量维护。
• 分区
  • sales按月份/天范围分区；谓词s.sold_at >= now()-90d会触发分区裁剪，只扫描近3个月分区。现有索引在每个分区建立小一些即可。
• 模型调整
  • 若season查询极其高频，可将season升维为显式列并建常规BTREE/部分索引，避免JSONB解析与GIN代价。

六、为什么这些优化有效（对应你的关切点）

• 执行计划：去掉CTE屏障、把产品筛选内联，给优化器更大自由度选择“先小后大”的连接顺序与并行聚合；使用@>让GIN生效。
• 资源消耗：覆盖索引减少大量Heap访问；部分索引将扫描范围缩到“活跃+冬季”；并行聚合降低单核CPU与内存压力。
• 查询效率：驱动小集合、索引范围扫描、减少排序/窗口输入规模；避免CTE物化的中间大结果集读写。
• 索引使用：将谓词改写为@>命中现有GIN；为连接与聚合补齐覆盖列，最大化索引仅扫描；必要时加表达式/部分索引以提升选择性。

七、落地执行顺序（建议）

1. 先改SQL为上面的版本并EXPLAIN (ANALYZE, BUFFERS) 验证计划形态；
2. 创建 idx_sales_prod_time_cover 索引；ANALYZE sales；
3. 如仍有明显Heap访问或CPU瓶颈，再创建 idx_products_active_winter_id_cat（或选择表达式/部分GIN方案之一）；ANALYZE products；
4. 调整work_mem与并行参数，消除聚合/排序磁盘溢出；
5. 若该查询高频，再评估日级物化/分区方案。

如你能提供EXPLAIN (ANALYZE, BUFFERS) 的实际输出，我可以进一步对节点与行数估计偏差、是否并行、是否溢出等进行针对性微调。

下面给出可落地的改造方案与原因说明，目标是让查询在“最近7天的Tasks”上高效聚合，并让用户邮箱筛选可走索引，降低排序与内存授予成本。

一、核心问题与思路

• 标量UDF dbo.NormalizeEmail(u.Email) 让谓词不可SARG（无法索引查找），且在SQL Server中常导致每行调用、CPU高、阻碍并行与优化器估算。
• LIKE '%@example.com' 是后缀匹配，前缀通配符使现有 Users.Email 索引无法被Seek，只能Scan。
• 时间范围筛选可利用“CreatedAt为前导列”的复合覆盖索引（或分区），让引擎只读最近7天数据。
• 将任务先按用户聚合，再TOP/ORDER，能显著减少后续连接和排序的数据量。

二、推荐的模式与索引改造

1. 为邮箱后缀查询做可SARG的列与索引

• 新增持久化计算列 EmailDomain，并建索引： ALTER TABLE dbo.Users ADD EmailDomain AS LOWER(SUBSTRING(Email, CHARINDEX('@', Email) + 1, 256)) PERSISTED;

  CREATE INDEX IX_Users_EmailDomain ON dbo.Users(EmailDomain) INCLUDE (UserId, Email);

• 如果主要筛选就是 example.com，叠加一个高度选择性的过滤索引（更小更快）： CREATE INDEX FI_Users_EmailDomain_example ON dbo.Users(EmailDomain) INCLUDE (UserId, Email) WHERE EmailDomain = N'example.com';

说明：

• 把“后缀匹配”改为“等值匹配”EmailDomain = 'example.com'，从根本上消除前缀通配符问题，使谓词可走索引Seek。
• 使用 LOWER 确保域名大小写一致性。该表达式是确定性且可持久化并可索引。

备选方案（若不能改造EmailDomain列）：

• 建反转邮箱列实现前缀LIKE： ALTER TABLE dbo.Users ADD EmailReversed AS REVERSE(Email) PERSISTED;

  CREATE INDEX IX_Users_EmailReversed ON dbo.Users(EmailReversed) INCLUDE (UserId, Email);

  查询改为 WHERE u.EmailReversed LIKE N'moc.elpmaxe@%'，这是“前缀LIKE”，可走索引Seek。

2. 为时间范围聚合建立覆盖索引（以时间为前导）

• 现有 IX_Tasks_Assigned_Created(AssignedTo, CreatedAt INCLUDE(DurationMinutes)) 在仅按时间筛选时不理想，因为 CreatedAt不是前导列。
• 新增覆盖索引，让“最近7天”可Seek并覆盖聚合： CREATE INDEX IX_Tasks_Created_Assigned_Incl ON dbo.Tasks (CreatedAt, AssignedTo) INCLUDE (DurationMinutes);

说明：

• 以 CreatedAt 为前导，可以直接Seek到“最近7天”的2M行左右，而不是在120M上扫描。
• INCLUDE DurationMinutes 保证聚合无需回表。
• 与 GROUP BY AssignedTo 配合良好：先按时间裁剪，再聚合到用户。

可选：如果已有分区（按CreatedAt），确保分区消除；若没有、且任务量巨大，考虑按月/周分区，以便此类时间窗口查询只触碰少量分区。

三、查询重写（去UDF、等值匹配、半开区间、先聚合再连接） 示例（建议版）： DECLARE @dt_end DATETIME2 = SYSUTCDATETIME(); DECLARE @dt_start DATETIME2 = DATEADD(DAY, -7, @dt_end);

WITH TaskAgg AS ( SELECT t.AssignedTo, SUM(t.DurationMinutes) AS total_minutes FROM dbo.Tasks AS t WITH (INDEX(IX_Tasks_Created_Assigned_Incl)) WHERE t.CreatedAt >= @dt_start AND t.CreatedAt < @dt_end -- 半开区间，边界更清晰 GROUP BY t.AssignedTo ) SELECT TOP (100) u.UserId, u.Email, ta.total_minutes FROM TaskAgg AS ta JOIN dbo.Users AS u ON u.UserId = ta.AssignedTo WHERE u.EmailDomain = N'example.com' -- 等值匹配，走索引 ORDER BY ta.total_minutes DESC OPTION (RECOMPILE); -- 让优化器以当前常量选择更优计划（尤其当选择性很高）

说明：

• 去掉标量UDF，避免阻断优化器。
• 半开时间区间避免“BETWEEN”边界包含/遗漏的潜在问题，通常对估算更友好。
• 先在Tasks上时间裁剪并聚合，大幅减少后续Join和排序数据量。
• OPTION(RECOMPILE)让优化器基于“example.com”和具体7天范围的实时基数估算选择更优的索引与并行度（在无参数化场景下通常收益明显）。如对CPU敏感，可视情况移除。

四、执行计划期望变化

• Users侧：由全表/索引扫描转为在 IX_Users_EmailDomain（或过滤索引）上Seek，返回极少量用户行。
• Tasks侧：在 IX_Tasks_Created_Assigned_Incl 上对最近7天做范围Seek，只读约2M行，并因覆盖索引避免Key Lookup；随后本地聚合（Hash/Aggregate或Stream Aggregate，取决于统计与排序）。
• 排序与TOP：对聚合后行集做Top-N Sort，内存授予显著下降；并行更容易介入，整体查询时间显著缩短。

五、维护与可维护性建议

• 统计信息：对新建索引（Users.EmailDomain、Tasks.CreatedAt,AssignedTo）启用自动统计更新，并在大量数据变更后考虑手动更新统计（必要时FULLSCAN）以保证范围估算准确。
• 写入路径：新增持久化计算列对写入影响极小（每次写入一次计算），通常远小于查询收益；如Email更新频率低，其开销可忽略。
• 如果必须继续用NormalizeEmail的逻辑（例如复杂邮箱规范化）：尽量将其转为“可内联的表值函数（inline TVF）”或在写入时完成规范化并存储到列中。SQL Server 2019的标量UDF内联需满足严格限制，且即便内联，前缀通配符问题仍在，因此更推荐存列+索引的方式。
• 并行与内存：在压力较大系统中，可根据观察添加 MAXDOP 或使用资源治理；但在多数场景下让优化器自由选择并行对该类查询更优。

六、如果不改造Schema的临时折中

• 继续使用 LIKE '%@example.com' 时，可建立过滤索引： CREATE INDEX FI_Users_Email_example ON dbo.Users(Email) INCLUDE (UserId) WHERE Email LIKE N'%@example.com'; 这会显著缩小扫描范围，但本质仍是扫描（非Seek）；性能提升有限，推荐度低于 EmailDomain 方案或 EmailReversed 方案。

总结

• 去UDF、消除前缀通配符，通过“EmailDomain 等值匹配 + CreatedAt前导覆盖索引 + 先聚合再连接”的组合，能最大化利用索引与统计，显著提升查询效率，同时保持可维护性与稳定的执行计划。