10 Billion Rows into MySQL: From 231 Days to 3.5 Hours

Summary

Bottom line first: writing 10 billion rows to MySQL, row-by-row INSERT takes about 231 days; if you thoroughly apply "batching + parallelism + fewer disk flushes," shard-parallel + LOAD DATA and friends can get it under 3.5 hours — these are reference magnitudes, varying with hardware, sharding, data format, and tuning.

This isn't just an interview answer to memorize. It's an actionable engineering manual: starting from the interviewer's question "how do you quickly insert 10 billion rows into MySQL," it first teaches you to ask clarifying questions, then does the B+ tree math — using 1KB rows, 8B primary keys, and 16KB data pages to derive where the "20 million per table" rule of thumb comes from. Then it walks the full pipeline as one, calculate; two, shard; three, batch; four, tune; five, parallelize; six, wrap up: database/table sharding, true batching, LOAD DATA INFILE, MyISAM staging (offline, re-runnable only), import-time parameter tuning and restoration, shard-parallelism, idempotent resumable checkpoints, reconciliation validation, and shadow-table zero-downtime switchovers.

What you'll take away from this post:

✅ Interview-ready standard answer: calculate, shard, batch, tune, parallelize, wrap up — a 30-second verbal template at the end, ready to memorize.

🔥 Most common pitfall: addBatch() ≠ true batching. Without rewriteBatchedStatements=true in the connection string, network round-trips stay the same.

✅ B+ tree hard math: how do 16KB pages produce "~21.9 million rows for three levels, ~25.6 billion for four" (using 1KB rows/8B primary keys; theoretical capacity ≠ what a single table can actually hold).

✅ Skill-level comparison table: row-by-row INSERT ~231 days, JDBC Batch 2.4–6 days, LOAD DATA 5.5–28 hours, shard-parallel under 3.5 hours (reference magnitudes).

🔥 Life-saving details: import-time parameter changes must be reverted; idempotency keys, watermark tables for resumable imports, shadow-table RENAME for traffic cutover; disabling binlog/reducing durability only for re-runnable offline scenarios and only with DBA approval.

✅ Architect's cold water: if these 10 billion rows are cold data, logs, or time-series, they probably shouldn't go into MySQL at all.

Who should read this? Backend developers preparing for interviews, engineers suffering from large-table imports, teams doing historical data backfills, and anyone wanting to advance from "can write SQL" to "can make architecture tradeoffs."

How to read: Bookmark it first, then follow the six-step mantra; the code, parameter tables, and reconciliation checklists can be copied directly. At the end there are interview plus/minus points and a 30-second verbal template. Also feel free to comment: what's the largest amount of data you've ever imported in one go? What's the most ridiculous pitfall you've hit?

👍 If this helped you, give it a like, a "wow", and forward it to the colleague still doing row-by-row insert in a for loop. Follow me; the next post is "How to Smoothly Migrate 100-Billion-Row Data with Sharding and Zero Downtime."

How to Write 10 Billion Rows to MySQL Quickly? From 231 Days to 3.5 Hours

A friend recently got stumped in an interview: 10 billion rows, how to insert them into MySQL quickly? He answered "multithreading plus batch insert," and the interviewer didn't follow up. Later we did the math: 10 billion rows, at 500 bytes each, is 4.5TB; row-by-row insertion would take 231 days. Today I'll walk through the entire pipeline that compresses 231 days to 3.5 hours — it's both an engineering playbook and a standard answer for scenario interview questions.

1. Don't rush to code: ask clarifying questions first, then do the math

In real development we often hit these "scary" requirements — like user behavior logs at 270 million rows per day, with a 3-year retention period, which comes out to exactly the 10-billion scale. The worst thing you can do is open with a for loop. A seasoned developer's first reaction is always two things: ask boundary questions and quantify the scale.

1.1 Six clarifying questions that determine the approach

Clarifying question	What it determines
Where does the data live now? HDFS / Kafka / CSV files / upstream DB	`LOAD DATA INFILE` vs JDBC batching
One-time historical import or continuous incremental writes	One-time → file batch load; continuous → Kafka peak-shaving + batch consumption
How big is each row? Structured or raw logs	Capacity estimation and parsing cost
Do you need to serve queries during the import? What QPS?	Index strategy, partitioning strategy; cold data probably shouldn't be in MySQL
How long of a maintenance window can you accept? How much data loss is tolerable?	Whether you can disable binlog, change flush strategy
Any dedup / idempotency requirements?	How to design the idempotency key

Interview gold: when the interviewer asks "10 billion," the first thing they're testing is whether you have the habit of "clarify first, then answer," not whether you immediately start writing insert. Different premises can yield a ten-fold difference in performance.

1.2 Do the math: how big is 10 billion rows, really?

Metric	Estimate
Row count	10 billion (10^10)
Row size	200B ~ 1KB
Raw data volume	2TB ~ 10TB ( ~4.55TB at 500B)
On disk after indexing	Multiply by 1.5–2x, up to 15TB+
Single-machine MySQL batch write ceiling	~10K–50K rows/sec

Then look at the time for each write method; which road to take becomes obvious:

Tier	Write method	Reference speed	Time for 10B rows
Bronze	Row-by-row INSERT + auto-commit	500–1,000 rows/s	115–231 days
Silver	JDBC Batch + manual transaction	20K–50K rows/s	2.4–6 days
Gold	MyBatis BatchExecutor	~80K rows/s	~1.5 days
Platinum	`LOAD DATA INFILE`	100K–500K rows/s	5.5–28 hours
Diamond	MyISAM import then convert to InnoDB	~800K rows/s	~3.5 hours
King	Sharding + multi-machine parallel + LOAD DATA	1M–2M rows/s	under 3.5 hours

(Speeds are reference magnitudes on a normal SSD; they vary with hardware and config, but the order of magnitude holds.)

1.3 Why a single table can't hold it: the B+ tree math

The rule "don't go over 20 million rows per table" is something everyone memorizes, but few can explain its origin. InnoDB's index is a B+ tree; all data hangs on the clustered index leaf nodes, and a data page defaults to 16KB:

Level	Calculation	Result
Leaf node	16KB ÷ 1KB per row	~16 rows per page
Non-leaf node	16384 ÷ (8B primary key + 6B pointer)	~1170 child nodes
Three-level B+ tree	1170 × 1170 × 16	~21.9 million rows
Four-level B+ tree	1170³ × 16	~25.6 billion rows

Conclusion is clear: a three-level B+ tree can roughly support 20 million rows — that's the source of the rule. Above that, the tree grows to four levels, each query costs one extra disk IO, and performance drops off a cliff. So 10 billion rows at least needs to be split into 500–2000 shards of tens of millions of rows, or 100 shard databases.

Technical highlight: doing this math in an interview is far more persuasive than reciting ten conclusions. "20 million" isn't magic; it comes from 16KB pages.

Now that we've quantified the scale, let's look at why row-by-row inserts are slow — the bottleneck might be different from what you expect.

2. Find the bottleneck: the hidden bill for a single INSERT

Many think slow inserts are because "the SQL is slow," but every single INSERT pays these five bills:

When inserting row-by-row, each write costs a network round-trip, a parse, a fsync disk flush, and index maintenance. Disk IOPS is the ceiling; multiply that by 10 billion and you get a disaster.

So the essence of all optimization is one sentence:

Fast insert = batching + parallelism + fewer disk flushes. Combine "N times" into "once": one network round-trip, one parse, one flush, one batch index maintenance.

With that direction, here's the overall roadmap.

3. Overall roadmap: one, calculate; two, shard; three, batch; four, tune; five, parallelize; six, wrap up

The whole write pipeline condenses into six actions; each chapter below expands on one:

Here's another set of measured references on a smaller dataset (10 million rows, normal SSD + InnoDB) so you can feel the benefit of batching:

Import method	Time for 10M rows	Bottleneck explanation
GUI tool / row-by-row INSERT	>1 hour, may hang	network round-trip + per-row commit
Row-by-row INSERT loop	2–3 hours	10M RTTs is the killer
Multi-value INSERT + manual transaction	20–30 minutes	fewer parses, but index still maintained per row
`LOAD DATA INFILE`	10–20 minutes	server-side bulk file load
Parallel `LOAD DATA` (file shards)	5–10 minutes	multi-shard concurrency + sequential writes, near disk ceiling

With the roadmap clear, let's start with the first action: "shard."

4. Shard: 10 billion must be sharded

4.1 How many shards?

Based on the B+ tree math, keeping a single table at 5–10 million rows is MySQL's optimal performance range:

10B ÷ 10M = 1000 tables; common layouts are 100 DBs × 10 tables, 16 DBs × 64 tables = 1024 shards, or 8 DBs × 64 tables = 512 tables;
Routing middleware: ShardingSphere, Vitess, MyCat — any works; use a high-cardinality modulo like user_id % 64, or cut by time + business dimension;
Sharding's value isn't just that it fits — it scales write capacity horizontally 1000x — each shard is an independent concurrency unit.

4.2 How to choose the shard key and prevent skew?

A shard key must satisfy three conditions: high cardinality, even distribution, and high query frequency. The worst fear is data skew — hot users' data all crammed into one shard, that shard gets hammered while others sit idle.

Anti-skew scheme	Characteristics	When to use
Hash modulo	Simple, but expansion requires migration	Fixed shard count
Consistent hashing	Only migrates small amounts on expansion	Elastic scaling
Pre-sharding + dynamic routing	Pre-allocate by hash, auto-migrate on threshold	Common in big companies

The routing code itself is simple — a modulo is enough:

// Shard routing: high-cardinality field like user_id modulo determines DB and table
int dbIndex    = (int) ((userId / tablesPerDb) % dbCount);
int tableIndex = (int) (userId % tablesPerDb);
// Target: db_{dbIndex}.t_order_{tableIndex}

4.3 Don't forget hot/cold separation

After sharding, ask again: do all 10 billion rows need to be online hot data? Archive historical data to ClickHouse / Doris / HBase; MySQL keeps only hot data — this step alone can cut a large portion of shard pressure.

Sharding is done; each shard's capacity ceiling is set. Next: how to write fast.

5. Batch: 90% of people step on the "fake batch" pitfall

5.1 The soul parameter: rewriteBatchedStatements=true

This is a pitfall I personally stepped in: I wrote addBatch(), felt great that I was already doing batch inserts, but speed didn't move an inch. Wireshark showed the MySQL driver by default splits the batch into individual INSERTs — it only saved some Java-side overhead, but network round-trips stayed the same.

Technical highlight: rewriteBatchedStatements=true is the soul parameter for MySQL JDBC batching. With it, the driver rewrites N inserts into INSERT INTO ... VALUES (...),(...),(...) multi-value insert, cutting network round-trips from N to 1, dropping RTT overhead by 90%. Without this parameter, addBatch is "fake batch."

Here's the full recommended connection string:

String url = "jdbc:mysql://host:3306/order_db"
        + "?rewriteBatchedStatements=true"   // rewrites batch to multi-value INSERT; soul parameter
        + "&useServerPrepStmts=false"        // server-side prepared statements conflict with batch rewrite; must disable
        + "&cachePrepStmts=true"             // cache prepared statements
        + "&allowLoadLocalInfile=true"       // prepare for LOAD DATA LOCAL
        + "&useSSL=false&serverTimezone=Asia/Shanghai";

5.2 Reference batch insert implementation

private static final int BATCH_SIZE = 5000; // sweet spot between 2000-10000

public void batchInsert(List<Order> rows, DataSource ds) throws SQLException {
    String sql = "INSERT INTO t_order(id, user_id, amount, created_at) VALUES (?,?,?,?)";
    try (Connection conn = ds.getConnection()) {
        conn.setAutoCommit(false);                 // disable auto-commit, otherwise fsync per row
        try (PreparedStatement ps = conn.prepareStatement(sql)) { // precompile + SQL injection protection
            int count = 0;
            for (Order o : rows) {
                ps.setLong(1, o.id);
                ps.setLong(2, o.userId);
                ps.setBigDecimal(3, o.amount);
                ps.setTimestamp(4, o.createdAt);
                ps.addBatch();
                if (++count % BATCH_SIZE == 0) {
                    ps.executeBatch();             // true batch, one network round-trip
                    conn.commit();                 // small commits to prevent undo bloat
                    ps.clearBatch();
                }
            }
            ps.executeBatch();                     // don't drop the tail batch
            conn.commit();
        } catch (SQLException e) {
            conn.rollback();
            throw e;                               // rollback on failure, let upper layer retry
        }
    }
}

If the data in memory is already grouped, you can use Lists.partition(dataList, BATCH_SIZE) to chop it and throw it into a thread pool, waiting with CountDownLatch or CompletableFuture.allOf(). Same idea.

5.3 Transactions: not too big, not too small

Two negative examples, both from real incidents I've seen:

Transaction too big: someone stuffed hundreds of millions of rows into one transaction; undo log bloated to OOM, rollback on failure took hours, and replica lag spiked to 8 hours;
Transaction too small: commit per row, fsync every time, disk IOPS maxed out.

Correct approach: commit every 2000–10000 rows. Also, don't blindly increase batch size; it's bounded by max_allowed_packet (default 4MB/64MB), exceeding it throws Packet for query is too large.

5.4 MyBatis users: remember to enable ExecutorType.BATCH

With MyBatis, the default SqlSession still sends row-by-row; you must explicitly enable BATCH mode, and flush periodically, don't save it all to the end:

SqlSession session = sqlSessionFactory.openSession(ExecutorType.BATCH);
try {
    LogMapper mapper = session.getMapper(LogMapper.class);
    for (int i = 0; i < list.size(); i++) {
        mapper.insert(list.get(i));
        if (i % 5000 == 0) {
            session.flushStatements();   // periodic flush to prevent memory pile-up
        }
    }
    session.commit();
} finally {
    session.close();
}

Batching spreads the cost an order of magnitude thinner, but there's a wilder move — bypass SQL parsing altogether.

6. Raw write: LOAD DATA INFILE, the turbo for imports

6.1 Why it can be an order of magnitude faster

LOAD DATA INFILE has the MySQL server directly read the file and write to the storage engine in bulk, skipping SQL parsing, the optimizer, permission checks, and per-row transactions. It's almost a "raw data dump," typically 3–5x faster than batched INSERT, and 10–50x faster than row-by-row. If the data is already on disk (TB-level offline import), it's the first choice.

6.2 Copy-ready full code

-- Enable acceleration switches for the import session (remember to restore afterward)
SET SESSION unique_checks       = 0;   -- disable uniqueness checks
SET SESSION foreign_key_checks  = 0;   -- disable foreign key checks
SET SESSION sql_log_bin         = 0;   -- disable binlog if replicas don't need sync; use with caution!

LOAD DATA LOCAL INFILE '/data/shards/part_0001.csv'
INTO TABLE t_order
CHARACTER SET utf8mb4
FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"'
LINES TERMINATED BY '\n'
IGNORE 1 LINES                                  -- skip header
(id, @user_id, @amount, @created_at)            -- read raw columns into variables
SET amount     = NULLIF(@amount, ''),           -- convert empty string to NULL to avoid NOT NULL errors
    created_at = STR_TO_DATE(@created_at, '%Y-%m-%d %H:%i:%s');
    -- can also use FROM_UNIXTIME(@ts) for timestamp fields; convert while importing, saving a cleanup pass

If the source is a program, generate the CSV streamingly — never accumulate 10 billion rows in memory:

try (BufferedWriter w = Files.newBufferedWriter(
        Paths.get("/data/act_" + shard + ".csv"), UTF_8, CREATE, APPEND)) {
    for (Row r : sortedRows) {          // write in ascending primary key order, sequential IO
        w.write(r.id + "\t" + r.uid + "\t" + r.act + "\t" + r.ts + "\n");
    }
}
// one file per shard, parallel LOAD with multiple threads

6.3 Three prerequisites, all pitfalls

File location: server-side local files must be inside the secure_file_priv directory; with the LOCAL version, the server needs SET GLOBAL local_infile = 1 and the driver needs allowLoadLocalInfile=true — both sides must be enabled or it won't work;
Format alignment: delimiters, line breaks, and encoding must exactly match. I had a colleague who hit this — an anomalous delimiter in the CSV stuck the whole batch at the first row, taking forever to debug. Standardize on UTF-8, CHARACTER SET utf8mb4, and you avoid a pile of Chinese mis-encoding and timestamps turning into 0000;
Disable checks upfront: before turning off unique_checks and foreign_key_checks, confirm the source data is clean, or dirty data gets mixed in directly.

6.4 Extreme play: MyISAM staging, then convert to InnoDB

For pure offline scenarios that can be re-run, there's an even more aggressive move: load into a MyISAM table first (no transactions, fast writes), then convert the whole thing to InnoDB:

-- 1. Create MyISAM table
CREATE TABLE t_log_myisam (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    user_id INT NOT NULL,
    action VARCHAR(64),
    create_time DATETIME
) ENGINE=MyISAM DEFAULT CHARSET=utf8mb4;

-- 2. Raw load (MyISAM can combine DISABLE KEYS / ENABLE KEYS to defer non-unique index maintenance)
LOAD DATA INFILE '/tmp/all_data.csv' INTO TABLE t_log_myisam
FIELDS TERMINATED BY ',' LINES TERMINATED BY '\n'
(user_id, action, create_time);

-- 3. Bulk convert to InnoDB, essentially one ordered rebuild
ALTER TABLE t_log_myisam ENGINE=InnoDB;

This pushes single-machine throughput up a notch, but it sacrifices transaction safety — only for offline scenarios where you can redo if it breaks. Production online tables should stay InnoDB.

We've covered "how to write fast." Next is "how not to break things" — idempotency, resumable checkpoints, zero-downtime switchover. These truly show engineering chops. If you find this helpful, like and bookmark first; we'll continue below.

7. Tuning: temporary overclocking during import (must revert afterward)

7.1 Parameter cheat sheet

# my.cnf import-time temporary config, restore after
innodb_buffer_pool_size        = 60%-80% of physical RAM  # keep index maintenance in memory
innodb_flush_log_at_trx_commit = 2      # flush once per second, not on every commit
sync_binlog                    = 0      # don't actively flush binlog
innodb_log_file_size           = 4G     # larger redo, fewer checkpoints
innodb_log_buffer_size         = 128M
innodb_autoinc_lock_mode       = 2      # interleaved mode, removes auto-increment lock waits on concurrent inserts
innodb_autoextend_increment    = 64     # larger tablespace increment, fewer expansions
bulk_insert_buffer_size        = 512M   # bulk insert buffer
max_allowed_packet             = 256M   # support larger batch packets
# innodb_doublewrite = 0                # extreme scenarios; can temporarily disable, power-loss risk, only for re-runnable

Three more session-level cuts:

SET autocommit = 0;
SET unique_checks = 0;        -- disable uniqueness checks, saves lots of random reads
SET foreign_key_checks = 0;

7.2 What do these parameters buy with?

Think it through: these parameters essentially trade "might lose 1 second of data on crash" reliability for throughput. They're a one-off offline scenario tradeoff. After the import, you MUST restore the double-1 (innodb_flush_log_at_trx_commit=1, sync_binlog=1). If you have replicas, tell the DBA ahead of time; if necessary, rebuild the replica from a backup after the import.

Pushing temporary tuning parameters as if they were production configuration is a classic interview minus point, and in real work it's an incident.

7.3 Index strategy: load data first, build indexes later

Maintaining secondary indexes during insert is lots of random IO with severe page splits; building indexes after load is sequential scan + sort, an order of magnitude faster. So the standard flow is:

-- Before import: disable checks, drop secondary indexes
SET FOREIGN_KEY_CHECKS = 0;
SET UNIQUE_CHECKS = 0;
ALTER TABLE t_user DROP INDEX idx_name, DROP INDEX idx_age;

-- After load: rebuild indexes online, restore checks
ALTER TABLE t_user
    ADD INDEX idx_name(name),
    ADD INDEX idx_age(age),
    ALGORITHM=INPLACE, LOCK=NONE;   -- online index build, no table lock
SET FOREIGN_KEY_CHECKS = 1;
SET UNIQUE_CHECKS = 1;

Technical highlight: keep only the primary key when creating the table, then ADD INDEX after loading — that's 30%+ faster than maintaining indexes while inserting. ALGORITHM=INPLACE, LOCK=NONE ensures business is unaffected during index rebuild. For online tables doing this kind of change, you can also use gh-ost / pt-online-schema-change.

7.4 Foundation: table structure and primary key design

This layer is most easily overlooked but affects everything:

Primary key must be ordered: AUTO_INCREMENT or snowflake ID, ensuring the clustered index appends sequentially. Never use UUID as primary key — random writes cause B+ tree page splits and massive random IO, cutting throughput in half;
Sort by primary key before import: turns random writes into sequential writes, B+ tree goes from "splitting everywhere" to "appending rightward";
Minimize column types: use INT instead of BIGINT when possible, DATETIME instead of VARCHAR. The smaller the row, the more fit per page, the shallower the tree.

With parameters and structure in place, the single-machine potential is nearly maxed. The final step is to get throughput to the ceiling with parallelism.

8. Parallelism: concurrency isn't just adding threads

8.1 Correct parallelism: at the shard level

Each shard one file, one connection, one transaction — isolated, naturally no lock contention, failure only re-runs that shard;
Do not concurrently write out of order within a single table — it breaks sequential writes and invites lock contention. Concurrency should sit at the "sharded database/table" layer;
Concurrency isn't the higher the better; keep single-machine at 16–32. Why? Once connections go up, innodb_thread_concurrency contention on lock waits makes throughput drop instead of rise, potentially even avalanche. On HDD, seek overhead is high, so concurrency should be reduced; on SSD you can go a bit higher.

8.2 Shard-parallel code with backpressure, retry, timeout

public void importAll(List<Path> shards) throws Exception {
    // CallerRunsPolicy: when queue is full, let the submitting thread run it — natural backpressure, prevents OOM
    ThreadPoolExecutor pool = new ThreadPoolExecutor(
            16, 16, 60, TimeUnit.SECONDS,
            new ArrayBlockingQueue<>(1024),
            new ThreadPoolExecutor.CallerRunsPolicy());

    List<Future<Long>> futures = new ArrayList<>();
    for (int i = 0; i < shards.size(); i++) {
        final Path file = shards.get(i);
        final int shardNo = i;   // shard number determines which DB/table to route to
        futures.add(pool.submit(() -> importShardWithRetry(file, shardNo)));
    }

    long total = 0;
    for (Future<Long> f : futures) {
        try {
            total += f.get(30, TimeUnit.MINUTES);   // timeout control, prevent one shard from hanging everything
        } catch (TimeoutException e) {
            f.cancel(true);
            log.warn("shard import timed out, waiting for retry");
        }
    }
    pool.shutdown();
    log.info("import done, {} rows total", total);
}

private long importShardWithRetry(Path file, int shardNo) {
    int retry = 3;
    while (true) {
        try {
            return loadDataInfile(file, shardNo);
        } catch (Exception e) {
            if (--retry <= 0) {   // retries exhausted: degrade to row-by-row and log dirty rows
                log.error("shard {} retries failed, degrading to row-by-row", shardNo, e);
                return fallbackRowByRow(file, shardNo);
            }
            log.warn("shard {} retrying", shardNo);
        }
    }
}

For finer control over in-flight batches, add semaphore backpressure:

Semaphore inFlight = new Semaphore(64);   // control in-flight batches, prevent connection explosion
pool.submit(() -> {
    inFlight.acquire();
    try (Connection c = shardDs(shard).getConnection()) {
        bulkInsert(c, loadShardRows(shard));
    } finally {
        inFlight.release();
    }
});

In the big-data ecosystem, same idea — Spark repartitions by shard key and writes in parallel, with the same shard's data contiguous, naturally sequential writes:

df.repartition(2048, col("shard_id"))
  .foreachPartition { it =>
    val conn = DriverManager.getConnection(url, user, pwd)
    conn.setAutoCommit(false)
    val ps = conn.prepareStatement("insert into t_order values (?,?,?,?)")
    var n = 0
    it.foreach { row =>
      ps.setLong(1, row.getLong(0))
      ps.setLong(2, row.getLong(1))
      ps.setBigDecimal(3, row.getDecimal(2))
      ps.setTimestamp(4, row.getTimestamp(3))
      ps.addBatch()
      n += 1
      if (n % 5000 == 0) { ps.executeBatch(); conn.commit(); ps.clearBatch() }
    }
    ps.executeBatch(); conn.commit(); conn.close()
  }

8.3 Three life-saving details

All threads write in ascending primary key order: prevents page splits and deadlocks. Out-of-order concurrent writes to the same table — deadlocks will make you batch-rollback until you doubt life;
Prevent OOM: stream reads (pagination / cursor / ResultSet.TYPE_FORWARD_ONLY) + bounded queue for peak shaving. Never SELECT * to load 10 billion into a List; that's a memory bomb;
Don't rely on database auto-increment IDs: with multi-machine parallel writes, auto-increment locks serialize concurrent writes. Generate IDs on the application side.

8.4 ID generation comparison

Scheme	Pros	Cons	Use case
DB auto-increment	simple, reliable	single-point bottleneck + auto-increment lock contention	small data
UUID	globally unique	unordered + space-hungry, page splits	don't use as primary key
Snowflake	ordered + high performance	clock rollback issue	mainstream in big companies
Number segment	high performance + reliable	non-contiguous IDs	Meituan Leaf, etc.

Snowflake core logic (with clock rollback protection):

public synchronized long nextId() {
    long timestamp = timeGen();
    if (timestamp < lastTimestamp) {
        throw new RuntimeException("Clock rollback, refuse to generate ID");
    }
    if (timestamp == lastTimestamp) {
        sequence = (sequence + 1) & SEQUENCE_MASK;   // increment within same millisecond, 4096 per ms
        if (sequence == 0) {
            timestamp = tilNextMillis(lastTimestamp); // sequence exhausted, wait for next ms
        }
    } else {
        sequence = 0L;
    }
    lastTimestamp = timestamp;
    return ((timestamp - START_TIMESTAMP) << TIMESTAMP_SHIFT)
            | (datacenterId << DATACENTER_ID_SHIFT)
            | (workerId << WORKER_ID_SHIFT)
            | sequence;
}

Fast writes are only the first half. The second half: what if the DB dies halfway? After the import, how do you prove not a single row was lost?

9. Wrap-up: idempotency, resumable checkpoints, reconciliation, and zero-downtime switchover

9.1 Idempotency: make re-runs safe

Retries inevitably bring duplicate writes, so every row needs an idempotency key (like shard_id + biz_id unique), and writes should use idempotent statements as a safety net:

INSERT INTO t_order(id, user_id, amount) VALUES (?,?,?)
ON DUPLICATE KEY UPDATE amount = VALUES(amount);  -- re-run safe
-- or INSERT IGNORE: silently skip duplicates, faster, but also depends on unique key

9.2 Resumable checkpoints: only re-run failed shards

It's normal for a 10-billion row import to take several days. If it crashes midway and you have to start over, you'll lose your mind. The standard approach is recording watermarks in a task table:

CREATE TABLE import_checkpoint (
    batch_id    VARCHAR(64) PRIMARY KEY,
    start_id    BIGINT,
    end_id      BIGINT,
    offset      BIGINT,        -- position already imported for this shard
    status      VARCHAR(20),   -- PENDING / RUNNING / DONE / FAILED
    import_time DATETIME
);

Before each shard import, check the watermark and skip completed batches; regularly save(shard, offset) during import. After crash, resume from the offset; combined with idempotent writes above, it's infinitely retryable and re-entrant.

9.3 Reconciliation: only when data matches are you done

Imported doesn't equal correct. Three things must be done:

Total count comparison: compare source count / sum against each MySQL shard's count / sum;
Sample validation: sample shards to compare field completeness;
Fingerprint validation: for critical batches, compute MD5/checksum before import and verify after.

9.4 Shadow table + RENAME: zero-downtime switchover

If you can't stop online business during import, use the shadow-table approach to make it transparent:

-- ① Import into shadow table, drop secondary indexes first
CREATE TABLE t_log_shadow LIKE t_log;
ALTER TABLE t_log_shadow DROP INDEX idx_uid, DROP INDEX idx_ctime;

-- ② Parallel LOAD DATA to load data (omitted)

-- ③ After load, rebuild indexes online
ALTER TABLE t_log_shadow
    ADD INDEX idx_uid(uid),
    ADD INDEX idx_ctime(create_time),
    ALGORITHM=INPLACE, LOCK=NONE;

-- ④ After row count/sample validation passes, atomic switchover, completes in seconds, rollback any time
RENAME TABLE t_log TO t_log_bak, t_log_shadow TO t_log;

9.5 Replica lag and online business protection

Don't read from replicas during import (you'll read dirty data). After load, rebuild the replica from backup, or use GTID + parallel replication to catch up;
Add token-bucket rate limiting to the import task, keeping TPS within what replicas can handle, reserving spare DB connections to avoid hammering the DB down;
If conditions allow, use a dedicated import cluster + nightly window to protect the primary's IOPS;
If you need "query while loading," use PARTITION BY RANGE on time, so the import only affects the latest partition, and do hot/cold separation for old partitions, with queries going through partition pruning.

10. Full picture: two complete pipelines and surrounding details

10.1 One-time import vs continuous writes

The above covers the offline one-time import pipeline. For continuous writes, the pipeline changes:

The full data-platform view: collection layer (Flume / Logstash) → message queue (Kafka) → compute layer (Spark / Flink / DataX) → MySQL shard cluster (ShardingSphere routing) → data warehouse (ClickHouse / Doris). Offline historical backfill is faster going straight through file batch loads; MQ adds a hop. Streaming data needs MQ for peak-shaving — the scenario decides the solution.

10.2 Hardware and OS layer

Easy to overlook, but real money savings:

Disk: SSD + RAID10; separate disks for data files and log files;
Memory: disable swap (vm.swappiness = 0), prevent buffer pool from being swapped out;
IO scheduler: use noop/none for SSD;
Network: writer and DB in the same datacenter, 10GbE, connection pool reuse to avoid frequent TCP setup, batch to reduce round-trips.

10.3 Measure first, then optimize

How do you prove it's actually faster? Don't rely on feeling:

SHOW ENGINE INNODB STATUS LOG section, to see flush and checkpoint status;
Innodb_rows_inserted per-second increments, directly reflecting write rate;
iostat's %util and await, to see if disk is saturated.

11. The soul question: should 10 billion really go in MySQL?

At this point, cold water is necessary: if these 10 billion rows are cold data archives, logs, or time-series, they probably shouldn't be in MySQL at all.

Scenario	More suitable choice
Online transactions, high-concurrency OLTP	MySQL sharding (this article's approach)
Logs / time-series analytics	ClickHouse, Doris
Massive KV / wide table	HBase
Full-text search	Elasticsearch
HTAP mixed workloads	TiDB
Offline data warehouse	Hive + Parquet

MySQL's comfort zone is sharded hot data and online transactions. Let the right tool do the right job; that's the core of architecture design.

Technical highlight: in an interview, daring to question the premise — "if 10 billion is just for analytics, don't force it into MySQL" — is the biggest plus point. It shows architect thinking rather than rote memorization.

12. Interview bonus: plus points, minus points, and a 30-second template

Plus points:

Start with clarifying questions — data source, scale, real-time, dedup, ask before answering;
Use B+ tree math — explain the "20 million" rule, don't just recite it;
Layered progression — from SQL writing → parameters → architecture, structured not scattered;
Have real numbers — "2 hours row-by-row vs 7 minutes parallel" is more persuasive than adjectives;
Mention fallbacks and idempotency — retries, degradation, reconciliation, showing engineering maturity;
Proactively point out tradeoffs — disabling binlog, reduced durability are one-off offline scenarios; restore for production;
Dare to question the premise — "10 billion might not fit MySQL."

Minus points:

Opening with a for loop insert, or only saying "batch insert";
Not knowing rewriteBatchedStatements, writing addBatch and feeling great;
No sharding plan, defaulting to one table for 10 billion;
Only talking speed, not consistency, idempotency, rollback;
Recommending temporary tuning parameters as production configuration.

30-second verbal template (memorize the six-step mantra: calculate, shard, batch, tune, parallelize, wrap up):

For 10 billion, first calculate capacity (several TB); a single-machine single-table is impossible, so work in layers: ① Shard — 1000 shards across DBs/tables, keep each table to tens of millions; ② Batch — prefer LOAD DATA INFILE, fall back to multi-value INSERT + manual transaction, connection string must include rewriteBatchedStatements=true; ③ Tune — disable unique_checks, set flush_log_at_trx_commit=2, increase buffer pool, restore afterward; ④ Parallelize — shard multi-threading, write in primary key order, no secondary indexes during import; ⑤ Wrap up — build indexes, reconcile, shadow-table switchover; ⑥ Finally add: cold data shouldn't be in MySQL.

Final words

The whole article condenses to one sentence: turn random writes into sequential writes, single point into parallel, row-by-row into batch.

Before starting, go through this landing checklist:

Calculate capacity, confirm the time window, all six clarifying questions answered
Sharding plan set, single table under tens of millions of rows
Connection string includes rewriteBatchedStatements=true
Import-time parameters changed, and "who reverts them" clarified
Idempotency key + watermark table designed
Reconciliation scripts (count / checksum / sample) ready
Replica lag plan aligned with DBA

If this helped you, welcome to like, "wow", and forward it to the colleague still doing row-by-row insert. Follow me, the next post is "How to Smoothly Migrate 100-Billion-Row Data with Sharding and Zero Downtime," a sequel to today's.

Finally, an interaction question: What's the largest amount of data you've imported in one go? How long did it take? What's the most ridiculous pitfall you've hit? Comment below; let's copy each other's homework.

Comments

Top 1 of 2 from juejin.cn, machine-translated. The original thread is authoritative.

liyunde 1 likes

The approach is correct, but the conclusion is somewhat outdated; a single table with 1B rows is actually fine, with minimal performance impact; 20M is just old theory, purely theoretical, not accounting for actual configurations, only of reference value; 10B typically goes to a data warehouse; offline analysis methods are more common; in large-memory scenarios, these are not issues at all, loading the entire table into memory poses no bottleneck; the solution is closely tied to use-case cost.

Rain的Java大神之路

Thanks for the valuable addition! I fully agree that 'the solution is closely tied to use-case cost.' 20M is a theoretical reference, not a hard standard. The '20 million per table' figure is a theoretical reference based on 16KB pages and 1KB rows, not a hard red line. In a large-memory environment, the IO overhead of the four-level B+ tree is largely shielded by the buffer pool, so a single table's capacity can be greatly increased. The article mostly explains the origin of this experience value for interview purposes, and I also agree that for billion-row cold data, a data warehouse should be the priority.