Designing an Efficient Import of 1 Billion Records into MySQL: Architecture, Batch Loading, Sharding, and Concurrency Control

Can a Single MySQL Table Hold 1 Billion Rows?

The answer is no; MySQL’s recommended maximum per table is roughly 20 million rows. The limit comes from the B+‑tree index structure: leaf pages are 16 KB, each holding 16 rows of 1 KB, while non‑leaf pages can hold about 1 170 pointers, causing a fourth index level when the row count exceeds 20 M and significantly hurting performance.

How to Write Data Efficiently

Single‑row inserts are slow, so batch inserts should be used (e.g., 100 rows per batch). MySQL InnoDB guarantees atomicity of batch transactions. Retries should be implemented; after N failed attempts, fall back to single‑row inserts and log the failures.

Writing in primary‑key order yields the best throughput. Avoid non‑primary indexes during bulk load; create them after the data is loaded, or omit them entirely.

Should Multiple Workers Write to the Same Table Concurrently?

No. Concurrent writes to the same table cannot preserve order and increase index‑rebalancing overhead. Instead, increase the batch size to raise effective concurrency without parallel writes.

Choosing the MySQL Storage Engine

MyISAM offers higher raw insert speed but lacks transactional guarantees, making it unsuitable for reliable batch loads. InnoDB provides ACID compliance; its performance is comparable to MyISAM when innodb_flush_log_at_trx_commit is set to 0 or 2 (flushing every second), accepting a possible 1‑second data loss on crash.

Therefore, the default choice is InnoDB unless the cluster policy forbids changing the flush setting.

Should We Shard the Database?

Single‑instance MySQL caps at about 5 K TPS on SSD; on HDD the limit is lower due to head‑seeking. Sharding (multiple databases and tables) allows scaling write concurrency and adapting to different storage media.

Configuration should allow dynamic adjustment of the number of databases and the number of tables written concurrently.

File‑Reading Strategies

The 1 B rows (≈931 GB) are split into 100 files (~10 GB each). Reading the whole file into memory is impossible; viable methods include:

Files.readAllBytes (not suitable for large files)

FileReader + BufferedReader (line‑by‑line, good performance)

File + BufferedReader

Scanner (line‑by‑line)

Java NIO FileChannel with a fixed‑size ByteBuffer

Benchmarks on macOS show Java NIO FileChannel is fastest, but it does not preserve line boundaries. For simplicity and acceptable speed (≈30 s for a 10 GB file), BufferedReader is recommended.

File file = new File("/xxx.zip");
FileInputStream fileInputStream = null;
long now = System.currentTimeMillis();
try {
    fileInputStream = new FileInputStream(file);
    FileChannel fileChannel = fileInputStream.getChannel();
    int capacity = 1 * 1024 * 1024; // 1M
    ByteBuffer byteBuffer = ByteBuffer.allocate(capacity);
    StringBuffer buffer = new StringBuffer();
    int size = 0;
    while (fileChannel.read(byteBuffer) != -1) {
        byteBuffer.clear();
        byte[] bytes = byteBuffer.array();
        size += bytes.length;
    }
    System.out.println("file size:" + size);
} catch (FileNotFoundException e) {
    e.printStackTrace();
} catch (IOException e) {
    e.printStackTrace();
} finally {
    // TODO close resources.
}
System.out.println("Time:" + (System.currentTimeMillis() - now));

Coordinating Read and Write Tasks

Running 100 read tasks that each write to the same database concurrently would exceed the write‑concurrency limit. The final design merges reading and writing into a single task: each task reads a chunk of a file and immediately writes the batch to its assigned table.

Ensuring Task Reliability

Use a composite primary key (file suffix + line number) to guarantee idempotent inserts. Store task progress in Redis (e.g., INCRBY task_offset_{taskId} 100 ) and update it after each successful batch. On failure, retry the batch; after repeated failures, fall back to single‑row inserts and continue updating Redis.

Controlling Task Concurrency

Each node acquires a Redisson semaphore (permit = 1) before processing a task. If a node crashes without releasing the permit, a timeout can be set to auto‑release it. Because Redisson lacks permit‑renewal, an alternative is a leader‑node that polls the task table, assigns the smallest pending task, and uses a Zookeeper‑based distributed lock (via Curator) to guarantee exclusive execution.

Summary

Clarify constraints (data size, storage, ordering) before designing the solution.

Split the billion rows into multiple databases/tables; a single table cannot hold that many rows efficiently.

Limit concurrent writes per database/table and allow dynamic tuning.

Compare InnoDB and MyISAM; InnoDB is preferred with appropriate flush settings.

Determine the optimal batch size through testing.

Merge read and write tasks to avoid complex Kafka pipelines.

Record progress in Redis to achieve reliable, resumable imports.

Use a leader node with Zookeeper+Curator for task assignment and Redisson locks to avoid semaphore timeout issues.

Backend Technical Community

Join our high‑quality tech exchange group for developers and recruiting HRs to share job opportunities and grow together!