Performance reference: item by item
INSERTabout 115~231 days(The worst case of disk and network can be on the order of hundreds of days, and the best case is about 2 months); the entire solution (multi-sharding, multi-instance parallelism) has been implemented,The total throughput in an SSD environment can reach 3.5 hours.——This is written in parallel by multiple instancesTotal throughput targetapproximately 3~20 SSD instances are needed (depending on the single machine throughput and sharding uniformity),Subject to pressure test. This is not an ORM parameter adjustment technique, but to first determine “should you enter MySQL” and then change the problem form. The actual throughput of a single machine and a single shard is usually on the order of tens to hundreds of thousands of rows/second.Release Statement: All the C# code in this article is already in SqlSugarCore 5.1.4.221 + MySqlConnector 2.6.2（.NET 10.0.300） Compilation verification passed (API existence and syntax correctness); runtime behavior (especially
Fastest().BulkCopybottom layer,Storageable/BulkMergeSQL generation) still needs to be confirmed by a real data stress test on the MySQL you actually deploy.
core idea: First, look at the cost structure clearly, and then decide whether to dilute the cost or replace the system that bears the cost.
methodology: Space dimensionality reduction → IO dimensionality reduction → Engineering details.
The relationship between the three:Spatial dimensionality reduction solves the ceiling, IO dimensionality reduction squeezes each shard to the limit, and the project maintains the bottom line of data correctness.
1.1 Choose the battlefield first: Should 10 billion records enter MySQL?
This is the easiest step to skip, but the most rewarding step. If 10 billion rows are time series reporting, logging, and auditing, they will naturally have the characteristics of “more writing and less reading, scanning based on time range, and very few updates” – this is not the job of OLTP.
Let the right tool do the right thingis the answer at the architectural level; the following parameter adjustments are just the limit squeeze under the established constraint of “MySQL must be used”.
The premise of this article’s discussion is: this batch of dataMust be in MySQL, requires transactions and idempotence, and subsequent retrieval by shard key. If the answer is “no need”, change the storage first and then talk about writing.
1.2 Capacity account
Assume that a row of records reported by the device is about 500 bytes, and 10 billion rows≈ 4.5TiB/5TB Original data; add primary key, secondary index, page fill rate and import period binlog, and the disk placement is easy 6~8TB. The import period also generates an equal amount of binlog and several terabytes of temporary files. Conclusion: This is not a volume that “one machine and one table” can handle, not from the beginning.
1.3 B+ tree ledger: Why “20 million per table” is an experience line
Estimated based on InnoDB’s default 16KB page, 1KB row, and 8B primary key:
- leaf page deposit 16 lines/page；
- Non-leaf node reservation 1170 primary key pointers；
- Three-layer B+ tree：1170 × 1170 × 16 ≈ 21.9 million lines；
- Four-layer B+ tree：1170³ × 16 ≈ 25.6 billion rows。
The scientific source of the Jianghu experience that “a single table should not exceed 20 million” is here:The three-layer B+ tree just fits the first two layers of the index into the memory, and the fourth layer starts with one more disk IO for each query.. 10 billion has stepped into the fourth or even fifth level. The smaller the row width and the more rows on a single page, the threshold will move upward; but the conclusion of the order of magnitude remains unchanged:10 billion must be sharded, and a single table can be compressed to 5 million to 10 million rows.。
1.4 Time account: without optimization, it is 115~231 days
Single INSERT Automatically commit, fsync once for each transaction, about 0.5ms, equivalent to 500~1000 lines/second(0.5ms corresponds to the theoretical upper limit of fsync of about 2000 times/second. After adding network RTT, SQL parsing, and secondary index maintenance, it falls to 500~1000, which is a conservative level): According to 500 rows/second, 10 billion ÷ 500 = 2×10⁷ seconds≈ 231 days; Press 1000 lines/second≈ 116 days. This is SSD moderate scenarioOn the order of magnitude – worse disks and networks (such as mechanical disks) can last for hundreds of days, and top-tier SSD + fast fsync can last to about 2 months.
Looking at the goal in reverse:3.5 hours = 12600 seconds10 billion ÷ 12600 ≈ 794,000 rows/second. NOTE: This isTotal throughput target for multi-shard, multi-instance parallel writesit is not a problem of a single machine and a single shard – it takes about 3~20 SSD instancesParallelism (depending on single-machine throughput and sharding uniformity),If the scale is not enough, it will not reach 3.5 hours.. All subsequent optimizations are based on this base number.
Time consuming sensitivity: The same 10 billion rows, the throughput difference is 1000 times, and the time consumption ranges from 2.8 hours to 3 years. The following table compares “throughput → 10 billion time consumption” to facilitate your estimation of your own hardware.
these areMagnitude reference, not precise prediction: Any changes in disk type, fsync frequency, network RTT, row width, and number of instances will cause the results to span orders of magnitude. Please use real data samples for stress testing before going online.
Goal: Split 10 billion rows into 1500~2000 sub-tableslet the single table return to 5 million~10 million rowsthe golden interval. The number of shards is deduced according to the upper limit of the team’s operation and maintenance capabilities. More is not always better.
2.1 How to choose the shard key
Three hard standards are indispensable:high cardinality(It can be spread evenly, do not select fields with only a few values such as gender and status);Query high frequency(90% of business inquiries require it);immutable(Once the shard key is written, it should not be changed. If it is changed, it must be migrated across shards).
Routing strategy:Fixed impression(like device_id % 1500) is simple to implement, evenly distributed, and is the first choice for one-time import;consistent hashingIt is suitable for subsequent online elastic expansion, and historical import scenarios do not need to be prioritized.
2.2 Shard quantity planning
10 billion÷1500 pieces≈ 6.67 million lines per slicefalling in the middle of the golden interval of 5 million to 10 million; if you press 1000 slices, each slice has 10 million rows at the upper edge of the interval. If the row width is slightly larger or the index is slightly more, it may return to a four-layer B+ tree. It is recommended to increase the number of shards to 1500~2000,For example 20 libraries × 100 sheets (2000 pieces) or 24 libraries × 80 tables (1920 pieces)——Corresponding to each piece 5 million/5.21 million linesare all within the golden range and have a margin.
The more shards the better: the cost of monitoring, backup, DDL changes, and connection pool operation and maintenance of 100 database instances will increase significantly. It is more common for production to landDozens of instances, dozens of tables for each instancereverse the number of shards based on team capabilities. As long as the single meter is kept between 5 million and 10 million, there is no need to deliberately approach the lower limit of 1,000 pieces.
2.3 Use SqlSugar’s SplitTable to implement
using SqlSugar;
// 设备上报分表实体：按月分表，按设备维度做库级分片。
// 按月分表：自动生成 device_report_202611 等物理表
[SplitTable(SplitType.Month)]
// 表名模板：{year}{month} 为 4 位年 + 2 位月
[SugarTable("device_report_{year}{month}")]
public class DeviceReport
{
// 雪花 ID，非自增；由应用侧生成（见 3.3）
[SugarColumn(IsPrimaryKey = true)]
public long Id { get; set; }
// 分片键：哈希取模决定去哪个库
public long DeviceId { get; set; }
// 分表路由字段；时间分表时必须是 DateTime
[SplitField]
public DateTime ReportTime { get; set; }
// 上报载荷（示例字段，可按需替换）
public int Payload { get; set; }
}
During initialization, all sub-table structures are built at once:db.CodeFirst.SplitTables().InitTables
Automatic routing on writing:await db.Insertable(list).SplitTable().ExecuteCommandAsync();
A few key details:SplitField must be DateTime Type (when the time is divided into schedules);shard key (DeviceId) and sub-table fields (ReportTime) are two sets of dimensions, don’t mix them up；Automatic table creation is suitable for initialization, but not suitable for high-frequency writing——When importing in large quantities, the table structure should be built in advance by DDL. After the sharding task obtains the physical table name, it is more stable to write the fixed table name;SplitTable The automatic identification mainly covers Insert/Update/Delete,Select query requires manually specifying the table name or using AsTable mapping。
2.4 Weight loss and separation of hot and cold
Only retain the columns required for online query, and split large text/Blob into bypass tables or object storage; if the value can be fixed in length, there is no need to change the length. TINYINT Need not INT，Never use it CHAR(36) UUID primary key; The archiving strategy will be determined upon launch: hot data in the past 3 months will be retained in MySQL, historical data will be exported to ClickHouse, and MySQL will be partitioned on the side DROP PARTITION Recover space in seconds.
Goal: Load as many rows as possible in a single network round trip, cover as many transactions as possible in a single disk flush, and make disk writes as sequential as possible.
3.1 First identify “fake batches”
Java’s rewriteBatchedStatements=true There is no equivalent with the same name in .NET; there are two types of batch paths in .NET:MySqlBulkCopy(Multi-value INSERT batch) and MySqlBulkLoader(LOAD DATA LOCAL INFILE streaming)。Insertable(List) The multi-value INSERT still goes through the SQL parsing layer and is a “semi-batch”; what really reduces IO by an order of magnitude is the BulkCopy / LOAD DATA path.
// 白银写法：一次提交一批，合并网络往返 + 合并事务。
// 注意：此事务仅包裹多值 INSERT；BulkCopy 路径不要在外层再开大事务（见 3.5 红线）。
db.Ado.BeginTran();
try
{
// 每 2000 行拼一条多值 INSERT
await db.Insertable(list)
.PageSize(2000)
.ExecuteCommandAsync();
db.Ado.CommitTran();
}
catch
{
// 任何异常都回滚，避免事务悬空占用连接
db.Ado.RollbackTran();
throw;
}
3.2 Big killer: Fastest().BulkCopy()
SqlSugar Fastest The underlying implementation on MySQLDepends on current SqlSugar/driver version: Commonly used is MySqlConnector MySqlBulkCopy(Multi-value INSERT batch), only available under certain versions/configurations MySqlBulkLoader(LOAD DATA LOCAL INFILE streaming). Both paths can combine N row-by-row IOs into a few large operations;Which path to take and whether it depends on local_infileplease refer to the actual measurement of the current version.. Official document confirmation PageSize Available with BulkCopy Use together for batching.
If your current version does go LOAD DATA LOCAL INFILE path, the following two prerequisites are required (otherwise BulkCopy Possible error):
① The connection string must be added AllowLoadLocalInfile=true：
var db = new SqlSugarClient(new ConnectionConfig
{
DbType = DbType.MySql,
ConnectionString = "Server=10.0.0.10;Port=3306;Database=iot;Uid=importer;Pwd=***;" +
// 走 LOAD DATA LOCAL 时必需
"AllowLoadLocalInfile=true;" +
// 建连超时
"Connection Timeout=600;" +
// 长任务别被默认超时打断（0=不超时）
"Default Command Timeout=0;",
// 每次执行完自动归还连接
IsAutoCloseConnection = true,
// 主键/自增等特性按 Attribute 识别
InitKeyType = InitKeyType.Attribute
});
② The server must be enabled local_infile：
SHOW VARIABLES LIKE 'local_infile';
SET GLOBAL local_infile = 1;
⚠️ Safety reminder：
LOCAL INFILEAllows clients to read local files on the server, should be used in production environmentsDedicated import accountclose after the import is completelocal_infileinstead of always on.local_infileis a global variable,Docker containers may be reset to OFF after restartinga safe approach is to actively execute it once when the import program startsSET GLOBAL local_infile = 1and give a failure alarm.
Write body:
// 每 10 万行一次批量（分批语义以当前版本实测为准）
await db.Fastest()
.PageSize(100_000)
.BulkCopyAsync(batch);
PageSize The semantics of: in the official documentation PageSize(100000) and BulkCopy Used together to control the number of rows in each batch.The batch semantics of BulkCopy are subject to the actual measurement of the current SqlSugar version.; If the current version does not batch BulkCopy, BulkCopy itself will be written in a streaming manner without additional PageSize. 50,000~100,000 is a common safe range. If it is too small, the merge effect will not be obvious. If it is too large, the client memory, single transaction,max_allowed_packet Anything can go wrong.
3.3 Sequential writing: break random IO into sequential IO
InnoDB isindex organized tablethe data is stored in primary key order. The primary key is out of order (such as UUID), which may occur every time a row is inserted.Page splits + random disk writes;The primary key is in order, and writing becomessequential append。
Ordinary Insert paths can be returned using SqlSugar’s snowflake ID:
// 每个不能共享 SnowFlakeSingle 静态变量的实例必须使用不同的 WorkId（从配置/环境变量读取）
SnowFlakeSingle.WorkId = int.Parse(
Environment.GetEnvironmentVariable("SNOWFLAKE_WORKER_ID") ?? "1");
// 普通 Insertable 路径可直接返回雪花主键（同步返回 long，无需 await）
long id = db.Insertable(entity).SplitTable().ExecuteReturnSnowflakeId();
However, the BulkCopy path must have an ID pre-generated by the application side. The official statement clearly states:“If Snowflake ID is used in the big data writing method, it needs to be assigned manually:SnowFlakeSingle.Instance.NextId()”。ExecuteReturnSnowflakeId / ExecuteReturnSnowflakeIdList yes Insertable method, BulkCopy’s entity list will not be automatically populated.
// BulkCopy 批次：构造时逐条预生成雪花 ID，再按主键排序 → 顺序写
SnowFlakeSingle.WorkId = int.Parse(
Environment.GetEnvironmentVariable("SNOWFLAKE_WORKER_ID") ?? "1");
// 逐行填充雪花 ID，再按主键排序 → 顺序追加
var ordered = batch
.Select(x => { x.Id = SnowFlakeSingle.Instance.NextId(); return x; })
.OrderBy(x => x.Id)
.ToList();
await db.Fastest().PageSize(100_000).BulkCopyAsync(ordered);
WorkId Must be read from configuration/environment variables,Unique per instance, 1 is just an example. Under multi-machine parallelism + sub-database and sub-table, the database’s self-increasing ID will become a bottleneck due to the serialization of self-increasing locks, and cross-database ID collisions will occur.
3.4 Fill in the data first, then build the index
Every time a row is inserted, all secondary indexes on the table must update the B+ tree. Correct posture:
-- 导入前：删掉二级索引，让写变成纯顺序追加
ALTER TABLE device_report_202611 DROP INDEX idx_device_time;
-- 导入完成后：在线重建，不锁表、不阻塞读写
ALTER TABLE device_report_202611
ADD INDEX idx_device_time (device_id, report_time),
ALGORITHM=INPLACE, LOCK=NONE;
One-time batch index building is much faster than row-by-row maintenance because it canSort + batch load。
Unique index and foreign key constraints: Unique indexes (including primary keys) cannot be simply “built later”; if the business relies on unique keys to be idempotent, they must either deduplicate them separately after importing, or accept the import period.
unique_checks=0Delayed verification costs incurred. recoverunique_checks=1A unique index rebuild may be triggered, which is a blocking operation. If the table has foreign keys,recoverforeign_key_checks=1Orphan row cleanup must be done beforeotherwise the recovery statement will fail or leave dirty data.
3.5 Parameter “overclocking” during the introduction period: trading reliability for throughput
This is a one-time, replayable, and reconciliation-able offline import window. The value of OLTP’s “insurance” decreases and parameters can be temporarily adjusted.But it is necessary to distinguish SESSION and GLOBAL, and try/finally Restored in pairs.
-- 会话级：只影响当前导入连接
SET SESSION unique_checks = 0;
SET SESSION foreign_key_checks = 0;
SET SESSION autocommit = 0;
-- 全局级：需要 DBA 授权，仅限离线导入窗口
SET GLOBAL innodb_flush_log_at_trx_commit = 2;
SET GLOBAL sync_binlog = 0;
SET GLOBAL max_allowed_packet = 67108864;
After the import is completedMust be restored piece by piece：
SET SESSION unique_checks = 1;
SET SESSION foreign_key_checks = 1;
COMMIT;
SET SESSION autocommit = 1;
SET GLOBAL innodb_flush_log_at_trx_commit = 1;
SET GLOBAL sync_binlog = 1;
-- 说明：max_allowed_packet=67108864 恰为 MySQL 8 默认值（64MB），导入后不恢复也安全；
-- 若你原环境将其调小过，请补回 SET GLOBAL max_allowed_packet = <原值>。
A few red lines:innodb_flush_log_at_trx_commit=2 A crash may lose the last 1 second of transactions;unique_checks=0 / foreign_key_checks=0 After turning offMust be restoredotherwise subsequent business insertion may silently destroy uniqueness;Don’t be here BulkCopy Put another extra long transaction in the outer layerundo expansion slows down the process, and transaction boundaries are handed over to batch submission; these parametersIt is strictly prohibited to maintain normal online business。
A session parameter recovery package executed one by one (to avoid multiple statements relying on driver configuration, and with transaction rollback):
public static async Task WithImportSessionAsync(SqlSugarClient db, Func action)
{
// 逐条执行 SET：避免把多条语句拼成一条命令（降低对驱动“多语句”配置的依赖）
await db.Ado.ExecuteCommandAsync("SET SESSION unique_checks=0");
await db.Ado.ExecuteCommandAsync("SET SESSION foreign_key_checks=0");
try
{
await action();
}
finally
{
// 成对恢复：先回滚 action 内部可能未提交的事务，避免事务悬空占用连接
try { await db.Ado.ExecuteCommandAsync("ROLLBACK"); } catch { /* 无活动事务时忽略 */ }
await db.Ado.ExecuteCommandAsync("SET SESSION unique_checks=1");
await db.Ado.ExecuteCommandAsync("SET SESSION foreign_key_checks=1");
await db.Ado.ExecuteCommandAsync("SET SESSION autocommit=1");
}
}
It is not recommended to automatically change the GLOBAL parameters in the business code. It should be executed and restored by the DBA in the import window.
3.6 Concurrency: must fall in the “sharding dimension”
Never write out of order and concurrently in the same tablethat will only amplify lock contention, page splits, and undo pressure. Correct posture:One write stream per shardthere is naturally no lock conflict between shards; the single-machine concurrency is controlled at 16~32(This is a common experience range, which is ultimately based on the inflection point of the “throughput-concurrency” stress test curve. If it is too high, it will cause an avalanche due to thread competition); each parallel branch uses CopyNew() or independent SqlSugarClientshared connections will degenerate parallelism into serialization; use bounded Channel Do back pressure.
CopyNew()semantics: What it solves isThread safety/connection isolationnot branch routing. When multiple libraries are sharded, physical library switching should be done bySqlSugarScopeofGetConnectionScope(configId)or routing middleware is done,CopyNewIt is not responsible for switchingdb_0/db_1。
public async Task ImportShardAsync(SqlSugarClient rootDb, ShardTask shard, CancellationToken ct)
{
// 线程安全/连接隔离（不是分库路由）
var db = rootDb.CopyNew();
try
{
// 读水位：断点续传起点
var watermark = await LoadWatermarkAsync(db, shard.ShardKey);
// 上次导到的最大主键
long lastId = watermark?.LastId ?? 0;
// 标记分片“处理中”，防重复领取
await MarkRunningAsync(db, shard.ShardKey);
// 流式读取数据源，每次取 10 万行一个批次
await foreach (var batch in ReadSourceBatchAsync(shard, lastId, batchSize: 100_000, ct))
{
// 本批：逐条预生成雪花 ID → 按主键排序 → 有序批次
var ordered = batch
.Select(x => { x.Id = SnowFlakeSingle.Instance.NextId(); return x; })
.OrderBy(x => x.Id)
.ToList();
// AS(表名) 部分版本支持，以实测为准；无效则提前把数据路由到目标物理表再 BulkCopy
await db.Fastest()
// 固定物理表，避免运行时路由反射开销
.AS(shard.TableName)
.PageSize(100_000)
.BulkCopyAsync(ordered);
// 每批成功后推进水位：last_id 取本批最大主键，rows_written 累加
await UpdateWatermarkAsync(db, shard.ShardKey, ordered[^1].Id, ordered.Count);
}
// 分片全部导完，标记“成功”
await MarkDoneAsync(db, shard.ShardKey);
}
catch
{
// 任一批失败，标记“失败”供重启续传
await MarkFailedAsync(db, shard.ShardKey);
throw;
}
finally { db.Dispose(); }
}
Parallel entry:
// 单机 16~32 路，以压测拐点为准
await Parallel.ForEachAsync(
shards,
new ParallelOptions { MaxDegreeOfParallelism = 16 },
async (shard, ct) => await ImportShardAsync(rootDb, shard, ct));
Primary key is used uniformlySnowflake algorithm/number segment modeGenerated on the application side – multi-machine parallelism + sub-database and sub-table, the database’s self-increasing ID will become a bottleneck due to the serialization of self-increasing locks, and will alsoCross-database collision。
3.7 Rank comparison table (.NET + SqlSugar version)
The digital premise must be made clear: Diamond/King’s “800,000~2 million rows/second” isTotal throughput of parallel writes across hundreds of shards spread across dozens of database instancesnot the LOAD DATA throughput of a single machine and a single shard. Publicly reproducible stand-alone LOAD DATA references are mostly inTens to hundreds of thousands of rows/secondmagnitude (for example, SSD + 32GB memory is about 300,000 lines/second, ordinary SSD single channel is about 25,000~40,000 lines/second). First confirm the hardware ceiling: NVMe SSD (500,000 IOPS, latency <50μs) compared to 7200RPM HDD (200 IOPS, latency 5~10ms), the batch writing gap can reach two orders of magnitude. Please use real data samples for stress testing before going online.
The first two levels determine “how fast you can run”, and this level determines “whether you can finish the run with confidence.”
key facts：BulkCopy It’s “just plug in and don’t care”,SqlSugar native BulkCopy UPSERT semantics are not supportedan error will be reported directly if a primary key conflict occurs, and the entire batch may fail if re-run. Idempotence must be guaranteed by the outer design.
① Replace BulkMerge()(Upsert). Official principle: Oracle/SqlServer uses Merge Into + BulkCopy,The bottom layer of other libraries is paging + BulkCopy + query verification. Need to pay attention to:BulkMerge in transactions may be related to BulkCopy Built-in transaction conflicts, some libraries do not support external transactions.
// 以当前版本实测确认；存在则更新、不存在则插入
await db.Fastest()
.PageSize(100_000)
.BulkMergeAsync(page);
② Multi-value Insert Go ON DUPLICATE KEY UPDATE(for non-BulkCopy paths):
// 多值 INSERT 的 UPSERT：SqlSugar 5.x 由 Storageable 提供，底层生成 INSERT ... ON DUPLICATE KEY UPDATE ...。
// WhereColumns 指定“业务唯一键”作为冲突判定列；若唯一索引是 (device_id, report_time)，
// 就换成 .WhereColumns(it => new { it.DeviceId, it.ReportTime })。
// 冲突判定列，需与表上唯一索引一致，否则冲突检测不生效
await db.Storageable(list)
.WhereColumns(it => it.Id)
.ExecuteCommandAsync();
⚠️ Concurrency deadlock risk：
ON DUPLICATE KEY UPDATEIn the event of a concurrency conflict, an S lock will be added to the unique index. If multiple sessions hit the same unique key, they may wait for each other to form a deadlock, which is especially obvious at the RR isolation level. It is recommended to RC isolation level single statement short transactionExecute, or use the BulkCopy route of “upstream guaranteed unique + water level + reconciliation” instead.
③ Upstream guarantees uniqueness + water level meter + reconciliation(The BulkCopy scenario is the least troublesome). No matter which plan,The premise is that there is a unique index established with a unique business key on the target table.otherwise conflict detection will not take effect at all.
4.2 Resume upload from breakpoint: water level meter
CREATE TABLE import_watermark (
shard_key VARCHAR(64) NOT NULL PRIMARY KEY,
last_id BIGINT NOT NULL DEFAULT 0,
rows_written BIGINT NOT NULL DEFAULT 0,
-- status 枚举：0待处理 1处理中 2成功 3失败
status TINYINT NOT NULL DEFAULT 0,
updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP
ON UPDATE CURRENT_TIMESTAMP
) ENGINE=InnoDB;
Entity classes can be mapped by columns and will not be expanded repeatedly here. Process: Start processing shard tags
status=1→ per batchBulkCopyUpdate after successlast_idandrows_written→ The program is interrupted and restarted.status=1/3of sharding, continuing from the water level.
Recovery granularity: Water level pressevery 100,000 rowsOnce updated, the maximum number of crashes is to rerun the current batch (100,000 rows), and will not rerun 10 billion from zero. This is the lifeline of tens of billions of tasks.
4.3 Three-tier reconciliation
“Run finished” does not mean “run right”.Total reconciliation(source count vs target count);Sampling reconciliation(Extract 1‰ evenly by shards, check back by primary key and compare MD5 field by field);Fingerprint reconciliation(Both ends are calculated according to shards COUNT(*) + SUM(CRC32(关键字段))）。
-- 指纹对账：两端按分片聚合。
-- 注意：SUM(CRC32(...)) 在百亿量级会超出 BIGINT（约 2×10¹⁹ > 9.2×10¹⁸），
-- 必须 CAST 为 DECIMAL(40,0) 提升精度，否则 MySQL 会报错或失真。
SELECT shard_key,
COUNT(*) AS cnt,
CAST(SUM(CRC32(CONCAT_WS('|', id, device_id, payload))) AS DECIMAL(40,0)) AS fp
FROM device_report_202611
GROUP BY shard_key;
Switching of business traffic is only allowed if the reconciliation is passed.
4.4 Shadow table + atomic switching
If this 10 billion isReplace old tableInstead of appending, do not write directly on the old table: all data is imported into the shadow table → reconciliation and online reconstruction of secondary indexes are completed → one line is used during low business peaks RENAME Atomic switching:
RENAME TABLE device_report TO device_report_old,
device_report_shadow TO device_report;
RENAME It is a metadata operation, completed in milliseconds, and the business is almost invisible; rollback only needs to be reversed RENAME。
4.5 Back pressure current limiting and master-slave protection
When the producer and consumer speeds do not match, useBounded Channel Do backpressure:
// 有界缓冲：生产者/消费者速度不匹配时，用固定容量 Channel 做背压。
// 缓冲满了就让生产者等待（写入侧消费），形成背压
var channel = Channel.CreateBounded(new BoundedChannelOptions(64)
{
FullMode = BoundedChannelFullMode.Wait
});
Large-volume writing will instantly break the master-slave delay: introduce time-limited streaming to leave time for the slave library to catch up; make it clear during the plan reviewWhether read traffic within the import window is allowed to go from the slave library; If the delay is too large after the import, do a full rebuild directly from the new main database, which is faster than waiting for it to catch up with several TB binlog.
4.6 Observability
Must-see indicators: peak process memory, throughput of each shard (rows/s), water level delay, number of failed retries, target database Innodb_rows_inserted、Innodb_log_wait,disk iostat。Without these indicators, you would not know whether 3.5 hours is fast or slow, or where you are stuck.
5.1 Complete pipeline
数据源(HDFS/Kafka/CSV/设备上报)
↓ 流式读取 + 有界 Channel 背压
[空间降维] 按分片键分组归集，一个分片一个写入任务
↓
[IO 降维] 分片内预生成雪花 ID → 按主键排序 → 有序批次
↓
[IO 降维] Fastest().BulkCopy()
+ 导入前删二级索引
+ 临时关闭 unique_checks / 降低刷盘频率
↓
[工程兜底] 每批更新水位；失败标记分片，等待重试
↓
全部分片完成 → 在线重建二级索引
↓
[工程兜底] 三层对账（count / 抽样 / 指纹）
↓
[工程兜底] 影子表 RENAME 原子切换 → 开放业务流量
Six-step formula:The first calculation, the second split, the third batch, the fourth parameter adjustment, the fifth concurrency, and the sixth finishing.
5.2 Pitfall list (specific to .NET + SqlSugar)
- Connection string missing
AllowLoadLocalInfile=truego to LOAD DATABulkCopyReport an error directly; - Server
local_infile=OFFor Docker is reset after restarting; - The data is not grouped into shards in advance, and out-of-order routing causes batch failures;
- The primary keys in the shard are written out of order, and the pages are severely fragmented;
- The imported temporary parameters were forgotten to be restored, and the online library silently generated dirty data;
- A single shard enables massive concurrency
BulkCopylock competition, undo log surge; - Only check the success of writing to the log, without reconciliation, there will be silent loss;
SplitFieldnoDateTimeor the fragmentation field is empty, routing fails;- If the batch size is set too large, single transaction timeout, binlog explosion, rollback disaster;
- Use the database’s self-increasing ID as the primary key for sharding, cross-database collision, and self-increasing lock serialization;
BulkCopyWithout native UPSERT, direct re-run will fail the entire batch due to primary key conflict.；BulkCopypath dependencyExecuteReturnSnowflakeIdAutomatically assign ID——Requires manualSnowFlakeSingle.Instance.NextId()pre-filled;OnDuplicateKeyUpdate()No longer exists in SqlSugar 5.x——Multi-value UPSERT used insteadStorageable().WhereColumns(...)；SplitTableOnly automatic routing for Insert/Update/Delete, Select query forgets to manually specify the table name;AS(表名)rightFastest<实体>().BulkCopyFull version compatibility is not confirmedshould be measured or routed to the target physical table in advance;- Fingerprint reconciliation
SUM(CRC32)ForgotCAST AS DECIMAL(40,0)the tens of billions table may overflow and report an error.
6.1 Three reminders
1. Not all data should go into MySQL. Log/timing → ClickHouse / Doris; massive KV → HBase; full-text search → Elasticsearch; HTAP → TiDB. MySQL only servesHot data for online transactions. Putting 10 billion pieces of data in MySQL, you should first ask:Is this data really suitable for MySQL?
2. Parameter tuning during the introduction period is to “trade reliability for throughput”. turn off unique_checkschange disk brushing to every second,sync_binlog=0——The subtext of these operations is “I don’t care about the durability of this period of time.”Only allowed for rerunable offline importsrestore the double 1 configuration immediately after the import.
3. Real ability is not memorizing conclusions. FirstAsking the boundaries; can be used B+ tree settlementExplain “Why a single table is 20 million”;Layered planeach layer has a trade-off logic; there arereal numbersdare to say “3.5 hours” and dare to explain “this is the total throughput of multiple shards”.
6.2 30-second interview narration template
“With 10 billion pieces of data, let’s settle the accounts first: the capacity is 4.5TB+, the storage capacity is 6~8TB, and the B+ tree for a single table requires four layers.A single table cannot be installed on a single machine.. Nine words for processing ideas——Spatial dimensionality reduction, IO dimensionality reduction, engineering details。
① spatial dimensionality reduction: Using SqlSugarSplitTableAccording to the sharding key, it is split into 1500+ sub-tables, and the pressure of a single table is within tens of millions; hot and cold are separated.
② IO dimensionality reduction: The main force isFastest().BulkCopy();BulkCopy scene primary key is usedSnowFlakeSingle.Instance.NextId()Pre-generation, sorting within shards first; deleting secondary indexes before importing, and rebuilding online after importing; temporarily lowering parameters to disk, turning off uniqueness verification, and restoring immediately after importing.
③ concurrent: Parallel according to the shard dimension, each shard is independently connected, 16~32 channels per machine.
④ Engineering details: Water table resumable transmission, idempotence, three-tier reconciliation, shadow table atomic traffic cut, master-slave protection.
The last sentence:With a scale of 10 billion, we should first question whether it should put MySQL – cold data goes to ClickHouse, and hot data stays here.”
From 231 days to 3.5 hours, there is no “black technology” in between. All speedups come from the same thing:
Find a way to merge N operations into one; if it cannot be merged, reduce the dimensionality and split it to a granularity that can be merged.
- spatial dimensionality reductionWhat is dismantled is the “data scale” and the problem of “fitting it”;
- IO dimensionality reductionThe combination is “number of operations” and solves “fast writing”;
- Engineering detailsThe guarantee is that “nothing will happen after the merger” and the solution is “stable operation”.
These three layers are stacked up to get the complete answer from 231 days to 3.5 hours.
One final reminder: All parameter adjustments during the import period are phased choices, and the import mode cannot be regarded as a normal business model; when the business does not require the transaction and retrieval capabilities of MySQL, a specialized analytical database is preferred.
Disclaimer: The time consumption in this article is for reference only. 3.5 hours corresponds to the total throughput of multiple shards and multiple instances in parallel. Single machine and single shard are subject to stress testing. The C# code in this article is based on SqlSugarCore 5.1.4.221 + MySqlConnector 2.6.2 (.NET 10.0.300) compilation verification；
BulkCopyWhether the bottom layer uses LOAD DATA,Storageable/BulkMerge/AS(表名)SQL generation, please confirm according to your actual version and MySQL environment test.
