# 数据库优化建议报告
## 性能问题诊断
- 主要瓶颈分析：
- 月度科目余额：对 ledger_entry 的高代价扫描与聚合，逻辑读约 3.5M pages；现有索引 (account_id, posted_at) 不能充分利用按时间范围的选择性，导致并行扫描与大范围聚合。
- 逾期发票清单：虽然存在 (status, due_date) 索引，但非覆盖导致 Key Lookup；分页与排序触发额外 IO；P95 达 480ms。
- 交易-分录明细：联接计划多为 Hash Join，原因是返回行数较大且索引不覆盖，产生 Lookup 与额外排序；偶发 PAGELATCH_UP 与热点页相关（高并发插入/读取相同叶级页）。
- 客户发票汇总：以 issue_date 过滤、按 customer_id 聚合，但当前索引以 customer_id 为前导，导致范围扫描与昂贵聚合。
- 其他：参数嗅探造成波动；部分日期参数未对齐分区边界导致跨月扫描；等待中 IO_COMPLETION 占 11% 反映高读 IO。
- 影响范围：
- 受影响查询：月度余额、逾期发票、交易-分录明细、客户发票汇总（覆盖最近一周高频业务报表与明细检索）。
- 数据量级：ledger_entry ~900M（按月分区）、invoice ~40M。
## 视图优化建议
### 建议1：v_ledger_monthly_balance
- **优化类型**：创建新视图（索引化视图/物化）
- **优化原理**：将明细级别的分录按账户+月份预聚合，显著降低月度余额查询的读取与聚合成本；索引化视图的聚合结果持久化，查询可直接扫描小规模聚合页集。注意：索引化视图对 DML 有维护开销，需在预生产评估。
- **具体方案**：
- 先确保创建索引化视图所需 SET 选项（窗口会话级）：
```
SET ANSI_NULLS ON;
SET QUOTED_IDENTIFIER ON;
SET ANSI_PADDING ON;
SET ANSI_WARNINGS ON;
SET CONCAT_NULL_YIELDS_NULL ON;
SET NUMERIC_ROUNDABORT OFF;
SET ARITHABORT ON;
```
- 创建视图与唯一聚集索引：
```sql
USE finance;
GO
CREATE VIEW dbo.v_ledger_monthly_balance
WITH SCHEMABINDING
AS
SELECT
le.account_id,
CONVERT(date, DATEFROMPARTS(YEAR(le.posted_at), MONTH(le.posted_at), 1)) AS month_start,
COUNT_BIG(*) AS row_count,
SUM(le.amount) AS balance
FROM dbo.ledger_entry AS le
GROUP BY
le.account_id,
CONVERT(date, DATEFROMPARTS(YEAR(le.posted_at), MONTH(le.posted_at), 1));
GO
-- 建立唯一聚集索引（行数量级为 账户数 × 月份数，体量远小于明细表）
CREATE UNIQUE CLUSTERED INDEX CIX_v_ledger_monthly_balance
ON dbo.v_ledger_monthly_balance (month_start, account_id);
GO
```
- 查询改写示例（建议使用 NOEXPAND 保证命中视图索引）：
```sql
SELECT account_id, balance
FROM dbo.v_ledger_monthly_balance WITH (NOEXPAND)
WHERE month_start >= @start_month -- e.g. '2024-01-01'
AND month_start < @end_month -- e.g. '2024-02-01'
ORDER BY account_id;
```
- **预期效果**：月度余额查询从扫描 3.5M pages 降至扫描数千页以内，响应时间预计 1.6s → 80~200ms（>80% 降幅）；CPU/IO 显著下降，计划稳定。
### 建议2：v_invoice_daily_cust_agg
- **优化类型**：创建新视图（索引化视图/物化）
- **优化原理**：将发票按“客户-自然日”预聚合，客户发票汇总在任意日期区间只需扫描日粒度聚合结果并再聚合，显著减少扫描与聚合量。
- **具体方案**：
```sql
USE finance;
GO
-- 同样要求与建议1相同的 SET 选项
CREATE VIEW dbo.v_invoice_daily_cust_agg
WITH SCHEMABINDING
AS
SELECT
i.customer_id,
i.issue_date,
COUNT_BIG(*) AS cnt,
SUM(i.amount) AS amt
FROM dbo.invoice AS i
GROUP BY i.customer_id, i.issue_date;
GO
CREATE UNIQUE CLUSTERED INDEX CIX_v_invoice_daily_cust_agg
ON dbo.v_invoice_daily_cust_agg(issue_date, customer_id);
GO
-- 查询改写示例：
SELECT customer_id, SUM(cnt) AS cnt, SUM(amt) AS amt
FROM dbo.v_invoice_daily_cust_agg WITH (NOEXPAND)
WHERE issue_date BETWEEN @start AND @end
GROUP BY customer_id
ORDER BY SUM(amt) DESC;
- 预期效果：对 invoice 的汇总查询预计 50~90% IO 降幅，P95 响应显著改善；计划更稳定。
索引优化建议
建议1：IX_ledger_posted_account_incl_amount
- 操作类型：新建
- 索引结构：非聚集分区对齐索引，键 (posted_at ASC, account_id)，INCLUDE(amount)
- 创建语句：
-- 请将 [PS_ledger_posted_at] 替换为现有基于 posted_at 的分区方案名称
CREATE NONCLUSTERED INDEX IX_ledger_posted_account_incl_amount
ON dbo.ledger_entry (posted_at ASC, account_id)
INCLUDE (amount)
WITH (DATA_COMPRESSION = PAGE, STATISTICS_INCREMENTAL = ON)
ON [PS_ledger_posted_at](posted_at);
- 选择性分析：
- 以 posted_at 为前导满足时间范围 Seek，并利用分区消除；在单月分区内再按 account_id 聚合，仅读取命中分区的索引页，避免对全表或多分区的大扫描。
- 覆盖 amount 消除 Key Lookup。预计月度余额查询逻辑读降低 70~90%。
建议2：IX_ledger_account_posted_desc_cover
- 操作类型：新建（或重建现有 IX_ledger_account_posted 以覆盖）
- 索引结构：非聚集分区对齐索引，键 (account_id, posted_at DESC)，INCLUDE(amount, txn_id)
- 创建语句：
CREATE NONCLUSTERED INDEX IX_ledger_account_posted_desc_cover
ON dbo.ledger_entry (account_id, posted_at DESC)
INCLUDE (amount, txn_id)
WITH (DATA_COMPRESSION = PAGE, STATISTICS_INCREMENTAL = ON)
ON [PS_ledger_posted_at](posted_at);
- 选择性分析：
- 针对“交易-分录明细”：按 account_id=@account + posted_at 范围检索，索引键顺序与 ORDER BY posted_at DESC 一致，可避免显式排序；INCLUDE 覆盖 amount/txn_id，消除 Lookup，降低 Hash Join 被选择的概率（更可能选择 Nested Loops + Seek 到 transactions）。
- 预计 P95 1.2s → 300
600ms，IO 降幅 4070%。
建议3：IX_invoice_open_due_cover（过滤索引）
建议4：IX_invoice_issue_customer_cover
建议5：NCCI_ledger_entry_analytics（可选）
- 操作类型：新建（非聚集列存索引，分析型负载）
- 索引结构：非聚集列存索引，列 (account_id, posted_at, amount, txn_id)
- 创建语句：
CREATE NONCLUSTERED COLUMNSTORE INDEX NCCI_ledger_entry_analytics
ON dbo.ledger_entry (account_id, posted_at, amount, txn_id);
- 选择性分析：
- SQL Server 2019 支持行存+列存混合负载。该索引对大范围扫描+聚合（如月度余额、跨期对账）能启用批处理模式与高压缩，显著降低 IO/CPU。
- DML 维护成本需评估（插入频繁时 delta store/压缩开销增加）。建议先在预生产验证：全月余额/跨期报表可能获得数量级加速。
实施优先级
- 高优先级：
- 新建 IX_invoice_open_due_cover：立刻消除排序与 Lookup，最小风险，收益显著（P95 480ms → 80~200ms）。
- 新建 IX_ledger_account_posted_desc_cover：覆盖明细查询并匹配排序，直降 P95，缓解 PAGELATCH_UP 相关的回表压力。
- 新建 IX_ledger_posted_account_incl_amount：使月度余额按分区+时间范围高效 Seek，明显降低 3.5M pages 读取。
- 中优先级：
- 创建 v_ledger_monthly_balance（索引化视图）：大幅改善月度余额并稳定计划；需评估 DML 维护成本与构建时间。
- 创建 v_invoice_daily_cust_agg（索引化视图）：显著改善客户汇总报表；构建与维护成本中等。
- 低优先级（可选/验证后实施）：
- 新建 NCCI_ledger_entry_analytics：面向分析/大范围聚合的通用加速器；需在预生产验证对写入的影响与收益。
- 对新建大索引执行分区级 PAGE 压缩重建（ALTER INDEX ... REBUILD PARTITION = ALL WITH (DATA_COMPRESSION = PAGE)），在保证 CPU 余量的前提下进一步降低 IO。
补充可操作要点（与上述建议配套）：
- 确保日期筛选使用 SARGable 谓词：posted_at >= @start AND posted_at < @end，参数类型与列一致（DATETIME2(3)），避免隐式转换。
- 为新建的分区对齐索引启用增量统计（已在 DDL 中指定 STATISTICS_INCREMENTAL = ON），定期更新活跃分区统计，稳定选择性估计与并行度。
- 部署顺序建议：在低峰期创建索引→验证执行计划与 IO → 再部署索引化视图；全部操作先在预生产环境回放一周典型负载，确认无写入回归后再上线。
