PAYATHON 2026

SQL Server 2016 并发读写的数据库设计策略

支付阿杰

结论

读写可以拆分，但不建议把 Payments 简单拆成两个由应用程序同时维护的数据库。更稳妥的做法是保留一个写入主库，通过 Always On 可读辅助副本或事务复制提供只读副本，并由应用层明确路由读请求。

调整架构前，先找出 CPU 和内存问题的来源。归档、扩容和增加只读副本或许有用，但不能代替对慢查询、缺失索引、执行计划、阻塞、内存配置及并发模式的排查。将数据同步到 NoSQL 也不是通用的性能解决方案。它更适合充当特定查询场景的派生数据源，而不是 Payments 的事务主库。

建议按以下顺序处理：

  1. 确认 CPU、内存和阻塞问题的来源。
  2. 优化查询、索引和 SQL Server 实例配置。
  3. 缩短事务，并采用合适的隔离级别。
  4. 将报表、搜索等允许一定延迟的查询迁移到只读副本。
  5. 确认数据量已经影响维护和查询后，再实施归档或分区。
  6. 只有关系型数据库无法以合理成本满足特定访问模式时，才引入 NoSQL。

先确认是不是 Payments 数据库造成了资源问题

同一 SQL Server 实例承载多个数据库时，实例级 CPU 达到 99%，并不能证明问题一定来自 Payments。需要先确认高消耗语句属于哪个数据库，再判断压力来自计算、编译、并行查询、阻塞，还是其他数据库的工作负载。

以下查询可以查看当前缓存中累计 CPU 消耗较高的语句：

SELECT TOP (20)
    DB_NAME(st.dbid) AS database_name,
    qs.execution_count,
    qs.total_worker_time / 1000 AS total_cpu_ms,
    qs.total_worker_time / NULLIF(qs.execution_count, 0) / 1000 AS avg_cpu_ms,
    qs.total_elapsed_time / 1000 AS total_elapsed_ms,
    qs.total_logical_reads,
    qs.total_logical_writes,
    qs.last_execution_time,
    SUBSTRING(
        st.text,
        (qs.statement_start_offset / 2) + 1,
        (
            (
                CASE qs.statement_end_offset
                    WHEN -1 THEN DATALENGTH(st.text)
                    ELSE qs.statement_end_offset
                END
                - qs.statement_start_offset
            ) / 2
        ) + 1
    ) AS statement_text,
    qp.query_plan
FROM sys.dm_exec_query_stats AS qs
CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) AS st
CROSS APPLY sys.dm_exec_query_plan(qs.plan_handle) AS qp
ORDER BY qs.total_worker_time DESC;

这些 DMV 数据从 SQL Server 启动、计划重新编译或缓存被清理后开始累计，不能作为永久历史记录使用。SQL Server 2016 可以启用 Query Store，持续记录查询、执行计划和运行时统计：

ALTER DATABASE Payments
SET QUERY_STORE = ON;
GO

ALTER DATABASE Payments
SET QUERY_STORE (
    OPERATION_MODE = READ_WRITE,
    QUERY_CAPTURE_MODE = AUTO,
    CLEANUP_POLICY = (
        STALE_QUERY_THRESHOLD_DAYS = 30
    )
);
GO

启用前需要评估存储空间和写入开销，再根据实际负载配置 Query Store，不要直接套用固定容量参数。

排查时还要关注以下问题：

  • 高 CPU 查询是否存在表扫描、隐式类型转换或低效排序。
  • 是否缺少覆盖索引，或者存在大量重复、价值较低的索引。
  • 查询是否频繁重新编译，或产生大量临时执行计划。
  • 是否因参数嗅探产生异常执行计划。
  • 写事务是否持续过久，形成锁等待和阻塞链。
  • tempdb、存储延迟和日志写入是否已成为瓶颈。
  • SQL Server 是否与操作系统争抢内存。
  • 其他数据库是否在同一时间执行备份、ETL、维护或报表任务。

正确配置 SQL Server 内存

SQL Server 默认可能占用大部分可用内存。如果实例所在服务器还运行其他程序，或者操作系统没有足够余量，就可能出现系统级内存压力。应结合服务器总内存、操作系统需求、实例数量和其他服务负载，设置 max server memory (MB)。

先查看当前配置：

EXEC sys.sp_configure 'max server memory (MB)';

配置示例：

EXEC sys.sp_configure 'show advanced options', 1;
RECONFIGURE;

EXEC sys.sp_configure 'max server memory (MB)', 49152;
RECONFIGURE;

49152 只是示例值，并非适用于所有服务器。实际设置必须根据物理内存和同机工作负载计算。同一实例承载多个数据库时，max server memory 限制的是整个实例，不能直接按数据库划分缓冲池内存。

如果 Payments 与其他数据库长期争抢资源，且难以控制，将 Payments 迁移到独立实例或服务器，通常比继续在同一实例内拆库更容易隔离 CPU、内存、I/O 和维护窗口。

是否可以分别使用读库和写库

可以，但必须确定唯一的权威写入源。典型结构如下：

写服务 ──> Payments 主库
                │
                ├──> 只读副本 ──> 查询服务
                └──> 归档或分析系统

应用程序不应自行将同一笔业务数据分别写入两个数据库，否则容易发生部分成功、重复写入、顺序错乱和数据不一致。

方案一：Always On 可用性组的可读辅助副本

如果已经具备相应的 Windows Server Failover Clustering 环境和 SQL Server 版本许可，可以使用 Always On Availability Groups。写操作连接主副本，允许延迟的读操作连接可读辅助副本。

只读连接通常需要在连接字符串中声明：

Server=PaymentsListener;
Database=Payments;
ApplicationIntent=ReadOnly;
MultiSubnetFailover=True;

写连接不设置 ApplicationIntent=ReadOnly，也可以明确使用：

ApplicationIntent=ReadWrite

使用监听器并配置只读路由后，SQL Server 可以把符合条件的只读连接导向辅助副本。

实施时需要注意：

  • SQL Server 2016 不同版本支持的可用性组功能并不相同。例如，Basic Availability Groups 与完整可用性组的限制不同，实施前必须核对实际版本和许可。
  • 可读辅助副本通常存在复制延迟，不适合要求写入后立即读取最新结果的业务步骤。
  • 辅助副本不能降低主库的写入成本，还会增加日志传输、重做、网络和运维开销。
  • 查询服务必须真正连接到辅助副本，才能分担主库的读取压力。
  • 故障转移、连接路由、备份策略和监控需要一并设计。

支付状态确认、余额判断和幂等检查等依赖最新数据的查询，通常仍应访问主库。

方案二：事务复制

事务复制可以把选定的表或数据发布到订阅数据库，适合将报表查询和部分读取流量转移到另一台 SQL Server。

与整库副本相比，它更加灵活，但需要处理以下事项：

  • 复制延迟。
  • 表结构变更。
  • 复制代理监控。
  • 初始化和重新初始化。
  • 订阅端索引设计。
  • 不适合复制的对象或业务操作。

订阅库通常只用于读取。除非已经设计了明确的冲突处理机制，否则不要让应用程序同时修改发布库和订阅库中的同一份业务数据。

方案三：日志传送

日志传送主要用于灾难恢复。辅助数据库需要周期性还原日志。即使使用 standby 模式开放读取，还原日志时也可能中断查询。因此，它通常不是高并发实时读写分离的首选方案。

并发读写不一定需要拆库

SQL Server 本身可以处理并发读写。许多问题出在事务和查询设计上，并不是因为读写位于同一个数据库。

事务首先要尽量短：

BEGIN TRANSACTION;

UPDATE dbo.Payment
SET Status = @Status,
    UpdatedAt = SYSUTCDATETIME()
WHERE PaymentId = @PaymentId;

COMMIT TRANSACTION;

不要在事务中执行外部 HTTP 调用、长时间计算或等待用户输入。事务持有锁的时间越长，通常造成的阻塞范围也越大。

索引要根据真实查询建立。例如，系统经常按商户、状态和创建时间读取支付记录，可以考虑：

CREATE INDEX IX_Payment_Merchant_Status_CreatedAt
ON dbo.Payment (MerchantId, Status, CreatedAt)
INCLUDE (PaymentId, Amount, Currency);

这只是索引形态示例。实际列顺序需要根据筛选条件的选择性、排序需求、返回列、更新频率和执行计划确定。索引越多，写操作和索引维护的成本也越高。

使用行版本控制降低读写阻塞

如果大量普通查询受到写锁阻塞，可以评估 READ_COMMITTED_SNAPSHOT。启用后，使用默认 READ COMMITTED 隔离级别的查询通常会通过行版本读取已提交数据，减少读写之间的阻塞。

ALTER DATABASE Payments
SET READ_COMMITTED_SNAPSHOT ON
WITH ROLLBACK IMMEDIATE;

不要在未经评估的生产环境中直接执行这项修改。WITH ROLLBACK IMMEDIATE 会断开数据库中的其他连接，并回滚尚未完成的事务，因此需要安排维护窗口。

启用前后应关注：

  • tempdb 的容量、吞吐量和延迟。
  • 长事务引起的版本存储增长。
  • 应用程序是否依赖原有的锁等待行为。
  • 读取已提交版本是否符合业务一致性要求。
  • 更新冲突和写写阻塞仍会发生，行版本控制不会消除所有锁。

也不建议普遍添加 WITH (NOLOCK) 来解决阻塞。它可能读到未提交数据，也可能出现重复行、遗漏行或结果不一致。对于支付、余额和结算等数据，这些风险通常不可接受。

归档的作用和实施方式

归档可以缓解活动表过大、索引维护时间过长、备份膨胀，以及历史查询影响在线业务等问题。但旧数据占用空间，并不必然导致 CPU 达到 99%。如果高 CPU 来自低效查询，即使归档了部分数据，问题也可能很快再次出现。

实施归档前，需要明确：

  • 哪些状态的数据可以归档。
  • 数据至少保留多久。
  • 归档后是否允许修改。
  • 历史查询从哪里读取。
  • 删除或归档失败后如何恢复。
  • 法规、审计和财务留存要求。
  • 外键及关联记录如何一起迁移。

建议采用小批量处理，避免一次删除大量数据，引起日志暴涨和长时间锁定。下面只展示一种基本模式：

DECLARE @BatchSize int = 1000;

WHILE 1 = 1
BEGIN
    BEGIN TRANSACTION;

    DELETE TOP (@BatchSize)
    FROM dbo.Payment
    OUTPUT
        DELETED.PaymentId,
        DELETED.MerchantId,
        DELETED.Amount,
        DELETED.Currency,
        DELETED.Status,
        DELETED.CreatedAt,
        DELETED.UpdatedAt
    INTO PaymentsArchive.dbo.Payment
    WHERE CreatedAt < DATEADD(year, -2, SYSUTCDATETIME())
      AND Status IN ('Completed', 'Cancelled', 'Failed');

    IF @@ROWCOUNT = 0
    BEGIN
        COMMIT TRANSACTION;
        BREAK;
    END;

    COMMIT TRANSACTION;
END;

使用该示例时，需要满足几个前提：

  • 归档表的列顺序和数据类型必须与 OUTPUT 列表一致。
  • 跨数据库写入会扩大故障处理范围。
  • 必须考虑外键、触发器、唯一约束以及重复执行。
  • 每批归档都应记录结果并验证归档数据，再清理源数据。
  • 生产环境通常还需要节流、重试、审计和失败恢复机制。

对于按日期持续增长的大表，还可以评估表分区。分区便于执行分区消除、分阶段维护和通过分区切换归档，但不会自动提高所有查询的性能。查询条件、分区键、唯一索引和维护流程必须配套设计。

是否应该实时同步到 NoSQL

技术上可以，但仅凭“读取更快”不足以支持迁移到 NoSQL。只有当数据模型与访问模式相匹配时，NoSQL 才可能提高效率。如果系统仍依赖复杂关联、事务一致性和临时查询，迁移后反而可能更难维护。

更合适的做法是保留 SQL Server 作为事实来源，再将特定用途的数据投影到其他存储：

  • 搜索和多条件全文检索使用搜索引擎。
  • 按固定键读取预计算结果时，使用键值数据库或缓存。
  • 大规模事件分析使用分析数据库或数据仓库。
  • 对于热点且允许短时间过期的数据，使用 Redis 等缓存。

数据同步可以采用 Change Data Capture、事务复制、业务事件或 Outbox 模式。SQL Server 2016 的 CDC 是否可用，取决于具体版本和部署条件，需要根据当前实例版本确认。

Outbox 模式可以避免业务数据已经提交，而消息发送失败的问题：

BEGIN TRANSACTION;

UPDATE dbo.Payment
SET Status = @Status,
    UpdatedAt = SYSUTCDATETIME()
WHERE PaymentId = @PaymentId;

INSERT INTO dbo.OutboxEvent
(
    EventId,
    AggregateId,
    EventType,
    Payload,
    CreatedAt
)
VALUES
(
    NEWID(),
    @PaymentId,
    'PaymentStatusChanged',
    @Payload,
    SYSUTCDATETIME()
);

COMMIT TRANSACTION;

后台发布程序读取 OutboxEvent，将事件发送到消息系统，再由消费者更新 NoSQL、缓存或搜索索引。这个过程通常采用最终一致性，因此必须处理：

  • 消费端幂等。
  • 重复消息。
  • 事件顺序。
  • 失败重试和死信。
  • 数据回放。
  • 同步延迟监控。
  • 模型变更和重新构建。

不要先提交 SQL Server，再由业务代码附带写入一次 NoSQL，并假定两次写入总能成功。这种双写方式遇到服务崩溃、网络超时或重试时，很容易产生数据不一致。

推荐的总体架构

支付类系统可以采用以下分层方案：

写服务
  │
  ▼
Payments 主库 ──> Outbox / CDC ──> 消息系统 ──> 缓存、搜索或分析存储
  │
  └──> SQL Server 只读副本 ──> 报表和非关键查询服务

需要明确各类请求的访问原则：

  • 创建支付、更新状态、余额校验和幂等判断访问主库。
  • 要求写后立即读取的数据访问主库。
  • 报表、列表和运营查询等允许一定延迟的请求访问只读副本。
  • 高频且允许短时间陈旧的数据可以进入缓存。
  • 搜索和分析场景使用专门的派生存储。
  • SQL Server 继续保存权威交易记录。

如果完成优化后，Payments 仍然持续与其他数据库争抢资源，可以优先考虑将它迁移到独立实例或服务器。这样更容易隔离资源和故障，也便于容量规划。读副本、缓存和 NoSQL 应根据已经确认的瓶颈逐项引入，不宜一次增加所有组件。

不存在一种适用于所有并发读写系统的最佳数据库架构。对于当前场景，应先建立基线监控并完成查询优化，再评估使用行版本控制减少阻塞，然后将允许延迟的查询迁移到 SQL Server 只读副本。归档用于管理数据生命周期，NoSQL 则只用于具有独立访问模式的派生数据。

备注：内容仅供参考。