.NET WebAPI分库分表实战：解决大数据量分页与跨分片查询

先给结论当你的 .NET WebAPI 接口已经走到“索引优化结束、SQL 改写无效、硬件加不动”这一步单表单库基本到头了。分页翻到第 50 页开始变卡、联表查询频繁出现在慢查询日志里、数据库连接数被打满这时候再看分库分表不是炫技是保命。这篇教程不是 PPT 式概念科普。我会把 .NET WebAPI 场景下的分库分表方案拆成可落地的东西大数据量分页怎么改、联表查询怎么写不会跨分片、数据拆分合并怎么迁移才能少踩坑以及怎么用 AI 辅助把路由代码和迁移脚本这类重复劳动压到最低。文中会明确哪些结论是通用实践、哪些需要根据你公司的数据量和业务重新评估。阅读前提默认你已经能独立创建 .NET WebAPI 项目用过 Entity Framework Core知道连接字符串是什么。如果你连项目模板都还没建过建议先把下面的目录结构和基础代码跑通再回来处理分片问题。1. 核心能力速览项目说明适用项目.NET 8 / .NET 6 WebAPI、EFCore、MySQL/SQL Server核心问题大数据量分页慢、联表查询跨分片、数据拆分与合并迁移核心技术分片键路由、键集分页、广播表、双写迁移、停机迁移AI 辅助方式用 AI 生成路由类、迁移脚本、对账 SQL、分页改写代码推荐环境开发机建议 16G 内存库建议 MySQL 8.0 或兼容版本前置依赖.NET SDK、VS2022 / Rider、Docker 或本地数据库硬件门槛无特殊 GPU 要求纯 CPU 开发调试是否需要 API 接口教程涉及 WebAPI 接口分层最终仍以 HTTP API 暴露是否支持批量任务数据迁移和分页改造均按批量处理场景设计适合读者后端开发、架构师、正在做系统性能优化的技术负责人不适合场景单表低于百万行、无性能瓶颈、业务允许简单加索引解决这里要说明一点文章里的代码和命令是通用落地模板不是从某个仓库直接拉出来的业务代码。你的表结构、分片键、数据库版本不同路径、表名、连接字符串都要替换。2. 适用场景与使用边界2.1 什么情况必须分库分表分库分表不是拿来装点简历的。真正需要它的情况一般可以归纳成三类单表数据量过大。MySQL InnoDB 的 B 树在数据量超过千万级、尤其是超过 2000 万行后索引层级变深随机 IO 成本上升即使有索引深分页也会越来越慢。这是业界比较公认的经验阈值。写入吞吐遇到单库瓶颈。单实例的写入并发有上限日志、订单、流水类数据高频写入时单库的 IO、锁、binlog 同步压力会先于 CPU 出问题。单库容量与备份恢复时间不可接受。当你的数据文件达到几百 GB 甚至 TB 级一次全量备份时间变长恢复时间更是无法保证时数据就需要拆开。如果你的接口问题只是某几条 SQL 没走索引那先加索引如果是缓存设计不合理先改缓存如果这些手段都做完了还是撑不住再来考虑分库分表。2.2 什么情况不要分库分表分库分表会永久改变系统的复杂度。表 join 受限、事务范围变大、数据迁移需要专门的流程、分片扩容非常痛苦。所以下面几种情况建议先别做单表只有几十万行加几个普通索引就能解决问题业务本身强依赖跨实体的复杂事务拆开后没有清晰的补偿方案团队没有专职 DBA数据库运维能力薄弱分片键还没想清楚直接动手建分表后面改路由代价会非常大。更稳妥的判断是分库分表的收益必须大于查询和事务重构的成本才值得动手。2.3 数据安全与合规边界做数据拆分和迁移时涉及生产数据导出的内容必须先确认数据脱敏需求。如果是订单、用户等包含隐私信息的数据迁移过程中建议用测试数据演练生产环境操作要有审批与备份。代码入库时不要把生产连接字符串、账号密码提交到 Git 仓库统一走环境变量或密钥管理。3. 环境准备与前置条件3.1 本机开发环境检查清单先按清单确认一遍环境免得后面启动项目时浪费时间检查项要求验证方式.NET SDK建议 .NET 8兼容 .NET 6dotnet --version开发工具VS2022 或 Rider无需验证VS2022 工作负载已安装 ASP.NET 和 Web 开发创建项目时能搜到 WebAPI 模板MySQL8.0 及以上mysql --version数据库客户端Navicat、DBeaver 或命令行能连上本地库Git建议已配置git --version如果你在 Windows 上安装 .NET Framework 3.5 时遇到 0x80070005 这类权限错误执行“以管理员身份打开命令提示符”再运行系统功能添加即可但这与本文的 .NET 8 项目无关只是顺带排查。3.2 数据库准备本机如果没有 MySQL最快的方式是用 Docker 起两个分片库。这里用两个库模拟分库效果docker run --name order_db_0 -e MYSQL_ROOT_PASSWORD123456 -p 3306:3306 -d mysql:8.0 docker run --name order_db_1 -e MYSQL_ROOT_PASSWORD123456 -p 3307:3306 -d mysql:8.0注意这里两个容器映射到宿主机的 3306 和 3307 端口目的是模拟物理上不同的库地址。实际生产环境更可能是两台独立实例但对本地验证来说两个库就够了。3.3 基础设施确认分库分表之后WebAPI 要维护多套连接字符串。建议在配置文件中设计一个数组结构统一管理{ ConnectionStrings: { Default: Server127.0.0.1;Port3306;Databaseorder;Uidroot;Pwd123456; }, Sharding: { DbCount: 2, TableCountPerDb: 4, DbPrefix: order_db_, Databases: [ { Index: 0, ConnectionString: Server127.0.0.1;Port3306;Databaseorder_db_0;Uidroot;Pwd123456; }, { Index: 1, ConnectionString: Server127.0.0.1;Port3307;Databaseorder_db_1;Uidroot;Pwd123456; } ] } }这个结构的重点是路由层先通过分片算法算出库编号和表编号再根据编号查连接字符串这样 WebAPI 本身不需要在代码里写死数据库地址。4. 项目搭建与核心依赖4.1 创建 WebAPI 项目打开终端执行命令dotnet new webapi -n Order.Api cd Order.Api dotnet add package Microsoft.EntityFrameworkCore dotnet add package Pomelo.EntityFrameworkCore.MySql如果要用 VS2022新建项目时选择“ASP.NET Core Web API”模板框架选择 .NET 8取消勾选 HTTPS 可减少本地调试的证书冲突。模板找不到时一般是因为没有安装“ASP.NET 和 Web 开发”工作负载去 VS Installer 里补装即可。4.2 分库分表中间件选型.NET 生态里比较成熟的分库分表方案有两类第一类是使用 ShardingCore 这类开源分片中间件它能与 EFCore 结合根据实体上的分片字段自动路由到不同库和表。优点是省去手写路由代码缺点是框架接管了查询翻译遇到复杂查询时需要理解框架本身的排错方式。第二类是自己在仓储层封装路由规则通过一个静态路由类计算表名和库名查询时手动拼 SQL 或根据分片路由后的连接串执行 Dapper 查询。这种方式代码透明排错直接但它要求团队对分片逻辑有清晰认知不能把所有查询都简单交给 EFCore 处理。更务实的路线是如果团队 EFCore 用得很熟、分片字段比较固定优先评估 ShardingCore如果业务里有大量复杂 SQL建议手写路由配合 Dapper 或 SqlSugar。本文以手写路由为教学示例因为理解手写路由之后再去使用中间件会容易很多。4.3 目录结构设计建议把分片相关代码独立成目录不要在 Controller 里写分片逻辑Order.Api ├── Controllers │ └── OrderController.cs ├── Models │ └── Order.cs ├── Sharding │ ├── ShardRouter.cs │ └── ShardConnectionFactory.cs ├── Repositories │ └── OrderRepository.cs ├── Migrations │ └── 迁移脚本.sql ├── appsettings.json └── Program.cs核心思路Controller 只接收 HTTP 请求Repository 负责查询订单Sharding 目录负责算出库和表迁移脚本独立保存。5. 分库分表核心实现路由规则与读写封装5.1 分片键选择分片键是整个分库分表方案里最重要、也最不能拍脑袋决定的一步。订单系统的通用建议是如果查询基本围绕“某个用户的订单”选 user_id 作为分片键这样同一用户的所有订单必然落在同一个分片天然规避了跨分片查询。如果写入量需要非常均衡地打散到各分片选 order_id 取模写入均衡但查询用户订单时必须带 order_id 或走汇总索引。折中方案是主键用雪花 ID业务上把 user_id 冗余进订单表并作为路由字段保存。个人更推荐按 user_id 分片。原因是大多数订单系统的核心接口就是“查我的订单列表”“查我的订单详情”按用户路由后这些高频查询都能落到单个分片。5.2 路由类实现新建 Sharding/ShardRouter.cspublic static class ShardRouter { public static string GetOrderDb(long userId, int dbCount) { return $order_db_{userId % dbCount}; } public static string GetOrderTable(long userId, int tableCount) { var index userId % tableCount; return $orders_{index:00}; } }这里用取模实现最简单的整数哈希。实际业务中如果 userId 是字符串可以改算哈希再取模public static int GetShardIndex(string key, int totalCount) { var hash System.Text.Encoding.UTF8.GetBytes(key); int sum 0; foreach (var b in hash) { sum b; } return sum % totalCount; }注意取模路由实现简单但后续扩容时会涉及大量数据的重分布后面第 8 节会讲扩容问题。5.3 连接工厂与仓储层新建 Sharding/ShardConnectionFactory.cs职责是根据路由结果创建数据库连接public class ShardConnectionFactory { private readonly IConfiguration _configuration; public ShardConnectionFactory(IConfiguration configuration) { _configuration configuration; } public MySqlConnection CreateConnection(string dbKey) { var section _configuration.GetSection(Sharding:Databases) .GetChildren() .FirstOrDefault(x x[Index] dbKey); var connectionString section[ConnectionString]; return new MySqlConnection(connectionString); } }Repository 中按 userId 路由到对应库表后执行查询。这一步是核心因为所有查询必须经过路由不能出现不带路由条件的全表扫描。public class OrderRepository { private readonly ShardConnectionFactory _factory; public async TaskListOrder GetUserOrders(long userId, int pageSize) { var table ShardRouter.GetOrderTable(userId, 4); var dbKey ShardRouter.GetOrderDb(userId, 2).Split(_).Last(); var sql $ SELECT order_id, order_no, user_id, create_time FROM {table} WHERE user_id UserId ORDER BY create_time DESC LIMIT PageSize; ; using var conn _factory.CreateConnection(dbKey); return (await conn.QueryAsyncOrder(sql, new { UserId userId, PageSize pageSize })).ToList(); } }这段代码里有两个关键点表名由路由得到不能使用用户直接传入的字符串查询条件中必须包含 user_id否则路由规则形同虚设。6. 大数据量分页优化实战6.1 为什么深分页会慢在单表里执行SELECT * FROM orders ORDER BY create_time DESC LIMIT 100000, 20;数据库需要先扫描并丢弃前 100000 行再返回 20 行。数据量越大偏移量越大耗时越长。分库分表后问题更严重要先把所有分片的数据各自取出来归并再做全局排序和截断代价成倍上涨。如果分页查询不带路由键还会有更麻烦的情况应用不知道去哪几个分片查只能全分片广播。一旦某次请求量增大数据库连接数和 CPU 会同时被打高。6.2 推荐方案键集分页键集分页又叫游标分页、seek method。思路是不用页码而是用“上一页最后一条记录的排序列”作为下一页的查询起点。SELECT order_id, order_no, user_id, create_time FROM orders_03 WHERE user_id UserId AND (create_time, order_id) (LastCreateTime, LastOrderId) ORDER BY create_time DESC, order_id DESC LIMIT 20;在 WebAPI 中接口参数不再接收 pageIndex 和 pageSize而是接收 lastCreateTime 和 lastId[HttpGet(list)] public async TaskIActionResult GetList(long userId, DateTime? lastCreateTime, long? lastOrderId) { var pageSize 20; var list await _orderRepository.GetUserOrdersByCursor( userId, lastCreateTime, lastOrderId, pageSize); return Ok(new { list, hasMore list.Count pageSize }); }键集分页的优点是数据量大时性能稳定下一页查询永远只扫描目标区间不会随着翻页变慢。缺点是用户无法直接跳到第 100 页产品上如果必须提供跳页功能只能走汇总表或搜索引擎。6.3 跨分片归并分页的处理如果业务确实需要不带路由键的全局分页比如运营后台查询所有订单建议按成本从低到高依次评估限制查询范围。比如必须选择时间范围时间范围本质上充当了路由维度建立汇总索引表。单独维护一张轻量级订单索引表分页只用这张表不查全分片明细引入 Elasticsearch。订单写入时同步一份数据到 ES运营后台的分页、筛选全部走 ES分片库只负责事务性写入和详情查询。这里要强调不要想着自己做“全分片并行 limit 内存归并排序”这个方案在分片数量少、数据量小时能跑但分片数一多、并发一高内存和响应时间都会爆炸不适合生产长时间运行。6.4 总条数统计的取舍分库分表后COUNT(*) 要遍历所有分片再相加成本很高。实际业务里的折中做法有不显示总条数只显示“是否还有更多”用缓存维护每日订单数、总订单数允许一定延迟用汇总表定时刷新统计结果。订单列表场景中“首页显示总记录数”通常不是强需求取消后换“加载更多”是合理的产品变更。7. 联表查询实战分库后的四种解法分库分表后join 不能像单库那样随意了。联表查询的优化原则是能不 join 就不 join必须 join 就先让数据落在同一分片。7.1 同分片绑定 Join订单表和订单明细表是最典型的绑定关系。设计分片时让 order_items 表也包含 user_id 或 order_id并且用相同规则路由。这样同一个订单的头信息和明细必然在同一个库的同一个表编号下join 可以直接执行SELECT o.order_no, oi.product_name, oi.price FROM orders_03 o JOIN order_items_03 oi ON oi.order_id o.order_id WHERE o.user_id UserId AND o.order_id OrderId;前提是写入时必须保证父表和子表都带上相同的分片键。EFCore 里配置实体时要把 user_id 冗余到订单明细实体上。7.2 全局表 / 广播表用户表、商品表这类被高频 join 且数据量不大的表适合做成全局表。所谓全局表就是每个分片库里都保存一份完整副本。订单查询需要展示用户名时直接 join 当前分片库里的 user 表不会跨网络访问。全局表的问题是写放大每次用户信息更新所有分片库都要更新。解决办法是把全局表的更新放到一个独立服务通过消息队列广播更新事件分片库消费后更新本地副本。实时性要求极高的场景还要加本地缓存。7.3 冗余字段与反范式化订单列表中用户其实只需要展示昵称和头像不需要实时查用户表。最简单的方法是把这些字段冗余到订单表里下单时一次性写入。这样查询订单列表时不需要 join 用户表性能提升非常明显。同样的思路适用于商品信息。下单时把商品名称、SKU、单价冗余到订单明细表后续即使商品改价订单里的历史价格也保持不变这还顺带解决了业务上的“价格快照”问题。7.4 应用层聚合 缓存如果业务中必须查询“某卖家卖出的所有订单”但订单表是按 user_id 分片的就会发生跨分片查询。建议不要硬 join而是在卖家维度维护一张卖家订单镜像表或汇总表。数据写入订单表时通过消息队列异步写一份到卖家视角的汇总表。查询时走镜像表返回结果后再回订单分片库取详情。这种做法的代价是多维护一份数据但换来的好处是任何复杂维度的查询都不影响核心分片库的查询性能。8. 数据拆分合并实战8.1 数据迁移前评估数据迁移是分库分表里最容易出问题的一环必须提前明确几个信息老表总行数、数据文件大小分片键取值分布是否有严重的倾斜业务允许的停机窗口迁移后如何校验数据一致性失败回滚方案。如果数据量在千万级以内停机迁移的成本是可接受的如果数据量大且业务不允许长时间停写就要走双写迁移。8.2 停机迁移流程停机迁移的步骤比较简单停写接口进入维护模式在分片库中创建所有分表按路由条件从老表取数分批写入新分表执行 count、抽样、业务对账切换读流量到新表观察一段时间后放开写接口。迁移脚本模板如下-- 在每个分片库执行创建分表骨架 CREATE TABLE IF NOT EXISTS orders_00 LIKE orders; CREATE TABLE IF NOT EXISTS orders_01 LIKE orders; CREATE TABLE IF NOT EXISTS orders_02 LIKE orders; CREATE TABLE IF NOT EXISTS orders_03 LIKE orders; -- 示例按 order_id 取模迁移到 orders_00 INSERT INTO orders_00 SELECT * FROM orders WHERE MOD(order_id, 4) 0; -- 校验分表行数总和是否等于老表行数 SELECT COUNT(*) FROM orders; SELECT SUM(cnt) FROM ( SELECT COUNT(*) AS cnt FROM orders_00 UNION ALL SELECT COUNT(*) FROM orders_01 UNION ALL SELECT COUNT(*) FROM orders_02 UNION ALL SELECT COUNT(*) FROM orders_03 ) t;实际操作时不建议一条 INSERT 全量跑容易造成锁和回滚段过大的问题。建议按主键范围分批迁移每批 5000 到 10000 条并打印日志。8.3 双写迁移流程不能停机时可以采用双写迁移创建新分片表业务代码中开启双写老表写入一份新分表也写入一份从新分表侧开启增量补偿任务把历史数据逐步搬过去通过消息队列或定时任务消费历史数据边迁边比对达到一致后把读流量切到新分片观察确认稳定再关闭老表写入。双写要处理的一个核心问题是顺序。推荐先写新分表再写老表。如果新分表写入失败主流程返回失败不产生不一致如果新分表成功而老表失败就记录补偿日志由对账任务修复。如果先写老表成功、新表失败老表里会多出数据更难处理。8.4 扩容从 2 分片到 4 分片取模路由的扩容代价很大。假设原来按user_id % 2分到 2 个库扩容到 4 个库后大部分数据的路由结果都会变化需要全量重新迁移。缓解方法有几个方向设计分片键时预留充足分片数。比如一开始就按 32 或 64 个分表设计只把表分布在少数库上后续加库只是移动表文件不需要重新分表。使用一致性哈希但要注意一致性哈希只能减少需要迁移的数据量不能完全避免迁移。如果业务带有明显的时间属性按时间维度月份、年份分片扩容时新增新时间分片即可不需要迁移旧数据。订单系统里“按时间维度分片”会引出一个问题查询深度历史订单时可能需要扫多个时间分片。通常的解法是组合路由默认按用户路由历史归档数据单独放归档库冷热分离后热库压力大大降低。9. AI 辅助落地分库分表方案9.1 AI 能做什么分库分表涉及的重复劳动很多AI 编码助手在这里的价值不是直接架构设计而是快速生成以下内容分片路由类及其单元测试幂等的数据迁移 SQL 脚本分表后的 count 校验、抽样比对 SQL把旧分页代码改写成键集分页根据执行计划解释慢查询根因。在动手前先给 AI 一个清晰的上下文描述越具体越有效。我建议准备一份业务描述模板包含数据库类型、表结构、数据量、分片键、分片数量把这份模板作为每次提问的前置说明。9.2 工作流示例假设我要生成订单表的分表迁移脚本可以先整理出如下 Prompt我是 .NET 后端开发。正在做订单表分库分表。 数据库MySQL 8.0 老表orders字段为 order_id bigint、user_id bigint、order_no varchar(64)、create_time datetime。 数据量约 2000 万行。 规划按 user_id 取模分成 4 个分表分表名称为 orders_00、orders_01、orders_02、orders_03。 请生成 1. 分表迁移 SQL要求分批按主键范围迁移不要一条 Insert 全量跑 2. 每个分表迁移后的数量校验 SQL 3. C# 路由示例代码 4. 迁移过程的注意点清单。AI 大概率会给你一个可以运行的初版。然后你需要重点审查三件事路由公式是否正确区分了库和表、迁移 SQL 是否包含幂等条件、校验 SQL 是否能处理重复数据。9.3 必须人工把关的地方AI 生成代码的准确率依赖上下文描述下面这些地方不能全信分片键选择必须由熟悉业务的人决策事务边界分库后事务变成了分布式事务AI 不会替你判断是否值得引入可靠消息最终一致性方案生产连接字符串和备份策略不能写在代码里由 AI 生成表结构变更顺序AI 可能忽略外键、索引和归档表的依赖关系。正确姿势是把 AI 当“高级代码生成器”所有输出都要经过 review 和本地验证。10. 常见问题与排查方法问题现象可能原因排查方式解决方案WebAPI 启动后接口 404Controller 没被路由发现或模板创建时缺引用检查日志、访问 /swagger 与 /api/order确认 Controller 继承 ControllerBase并标注 ApiController调用接口超时页面提示 net::ERR_CONNECTION_TIMED_OUT服务未启动、端口未监听、防火墙拦截检查进程、netstat 端口、浏览器访问启动服务更换端口检查入站规则分页查询不带路由键扫描所有分片分片键没作为查询参数传递查看 SQL 是否包含 user_id 条件查询强制校验路由键缺失时拒绝请求联表查询跨分片报错或超时父表与子表分片键不一致查看执行计划与表结构冗余分片键到子表按同一维度路由迁移后分表数据总数不等于老表总数有数据重复或漏迁用 count 比对、抽样比对使用幂等迁移脚本按主键范围分批执行VS2022 创建 WebAPI 项目找不到模板未安装 ASP.NET 和 Web 开发工作负载VS Installer 查看已装负载补装工作负载后重启 VS.NET Framework 3.5 安装报 0x80070005权限不足或系统更新冲突以管理员运行命令管理员权限执行 DISM 或系统功能添加后台管理分页深翻页越来越慢使用 offset/limit 深分页查看慢查询日志改成键集分页或引入 ES/汇总索引表数据写入双写后新库缺数据补偿任务没跑或消息丢失查补偿日志、对账任务增加失败重试和告警定期全量对账10.1 补充说明跨分片查询的兜底所有分库分表项目都会遇到“查询条件里没有路由键”的请求。我的建议是不要试图在数据库层解决所有查询而是给系统设计一条兜底链路线上实时查询尽量要求带路由键需要全局查询的后台功能走汇总索引表、ES 或只读分析库不可避免的全分片广播查询要加并发控制、超时控制和结果集大小限制。11. 最佳实践与使用建议分库分表不是一次性改造是一个持续演进的过程。下面这些建议是从大量项目里沉淀下来的通用实践第一次使用低并发、小数据量场景验证路由代码数据量上来后再做压力测试。保留一套最小可运行配置包括两个分片库、四张分表、一个分页接口和一个联表查询接口作为后续所有人学习和联调的基准环境。模型文件、迁移脚本、SQL 对账脚本分目录管理。迁移脚本建议带上日期前缀例如20250601_init_sharding.sql。批量任务必须加日志、任务 ID 和失败重试机制。数据迁移任务失败时要能根据任务 ID 精确重跑。接口服务启动后要限制访问范围。开发环境只监听 127.0.0.1生产环境通过 Nginx 网关暴露不对公网直接开放数据库端口。涉及用户隐私的数据迁移时测试环境必须脱敏生产数据导出要有审批记录。发布上线前必须核对三个点路由公式、连接字符串、迁移脚本版本。这三个点任何一个出错都会导致生产事故。每次分片扩容要先在预发环境完整演练一遍包括回滚演练。12. 总结与下一步这个方案里最值得先尝试的是先把单个订单列表接口改成“按 user_id 路由 键集分页”它不需要立刻迁移全部数据却能让你直观感受到分页性能的变化也是分库分表所有改造里成本最低的一个。最先应该验证的功能是路由类。你可以写一个最基础的单元测试把不同 userId 输进去确认它们能被均匀分到 2 个库、4 张表。路由正确后再做迁移才有意义。最容易踩的坑有两个一是分片键选错上线后才发现高频查询全都跨分片二是迁移脚本没有幂等性重跑产生重复数据。这两个坑一旦踩进去修复成本远高于一开始多花时间设计。后续可以扩展的方向包括引入 ShardingCore 这类中间件进一步减少手写路由成本、增加分布式事务的可靠消息最终一致性方案、把订单索引同步到 Elasticsearch 支持运营后台复杂筛选。如果这篇对你有帮助建议收藏备用等到真正需要拆分数据时再照着流程走一遍。