How to Import 1 Billion Records into MySQL in Record Time

Interview Question Background

The author recounts an interview question that asks how to insert 1 billion rows as quickly as possible, not to give an exact time but to design a system that can achieve the fastest throughput.

Problem Constraints

1 billion rows, each about 1 KB.

Data are unstructured access logs that need parsing before insertion.

Source files reside in HDFS or S3.

The 1 billion rows are split into roughly 100 files, each with an order‑preserving suffix.

Insertion must be ordered and avoid duplicates.

Target database is MySQL.

Can a Single MySQL Table Hold 1 Billion Rows?

MySQL uses a B+‑tree for the primary (clustered) index. Practical experience shows a single table performs well up to about 20 million rows (≈3‑level index). Beyond that the index depth grows to four levels, degrading performance.

Assuming a leaf‑page size of 16 KB and each row of 1 KB, a leaf page holds 16 rows. A non‑leaf page (also 16 KB) stores a 8‑byte BIGINT primary key plus a 6‑byte pointer, allowing roughly 1 170 child pointers per page. This yields the index‑depth table shown in the original article (4‑level index for >20 M rows).

Therefore the design splits the data into 100 tables (≈10 M rows each) to stay within the safe range.

Efficient Writing to MySQL

Single‑row inserts are slow; batch inserts dramatically improve throughput. A batch size of 100 rows (≈100 KB) is a reasonable starting point.

Transactional Guarantees : InnoDB guarantees that a batch insert is atomic – either all rows succeed or all fail.

Write logic should include retry mechanisms. After N retries, fall back to inserting rows individually and logging failures.

Insert order matters: writing rows in primary‑key order yields the best performance. Non‑primary indexes cause random page splits; it is advisable to create them after the bulk load or avoid them altogether.

Sharding and Concurrency Limits

MySQL single‑instance write throughput caps around 5 K TPS. To scale, the design proposes:

Configurable number of databases.

Configurable number of tables per database, with the ability to limit concurrent writes per table (especially on HDD where the disk head is a bottleneck).

For SSDs, write capacity varies by vendor; the system should allow dynamic tuning of these parameters.

File Reading Strategies

Each 10 GB file cannot be loaded entirely into memory. Five common Java approaches were benchmarked on macOS:

Files.readAllBytes (full load)

FileReader + BufferedReader (line‑by‑line)

File + BufferedReader

Scanner (line‑by‑line)

Java NIO FileChannel with fixed‑size buffers

The NIO FileChannel showed the best raw throughput, but it reads fixed‑size buffers and does not naturally align with line boundaries, requiring extra logic.

BufferedReader, while slightly slower, handles line breaks automatically and reads a 10 GB file in about 30 seconds, which is acceptable because the overall bottleneck is database insertion.

Sample NIO code (kept verbatim):

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));

Task Coordination: Read vs Write

Initially, a Kafka pipeline was considered: 100 read tasks push messages to Kafka, and consumer tasks write to MySQL. However, Kafka partitions would break the required ordering, and managing partitions versus tasks proved complex.

The final design merges reading and writing into a single task: each task reads a batch from its assigned file segment and immediately writes that batch to the designated table.

Reliability and Idempotency

To survive crashes or restarts, each record receives a deterministic primary key composed of {taskId}{fileIndex}{lineNumber}. This key guarantees idempotent inserts and eliminates the need for auto‑increment IDs.

Progress is tracked in Redis using INCRBY on a per‑task key: INCRBY task_offset_{taskId} 100 If a batch fails, the system retries; after repeated failures it falls back to single‑row inserts and updates Redis accordingly. On restart, the task reads the stored offset and resumes from that point.

Distributed Concurrency Control

Redisson semaphores were explored to limit the number of concurrent writes per database. Example code:

RedissonClient redissonClient = Redisson.create(config);
RSemaphore rSemaphore = redissonClient.getSemaphore("semaphore");
// Set concurrency to 1
rSemaphore.trySetPermits(1);
boolean acquired = rSemaphore.tryAcquire(); // non‑blocking acquire

Issues discovered:

If a task crashes without releasing the semaphore, subsequent tasks may be blocked.

Redisson does not support lease renewal for semaphores, making timeout handling difficult.

Therefore the design switches to a master‑node election (Zookeeper + Curator) that assigns tasks to workers. Workers acquire a distributed lock (Redisson) with lease renewal, execute the task, then release the lock.

Three possible states for the master node:

If running tasks < concurrency limit: pick the smallest‑id pending task, mark it as processing, and notify workers.

If running tasks = limit: check whether any lock has expired; if so, re‑publish the task.

If running tasks > limit: raise an alarm for manual intervention.

Task tables store fields such as bizId, databaseIndex, tableIndex, parentTaskId, offset, and status to enable precise scheduling and progress monitoring.

Final Takeaways

Clarify constraints before designing a solution.

For 1 billion rows, split into multiple databases/tables (e.g., 100 tables of ~10 M rows each).

Batch inserts and primary‑key ordered writes are essential for speed.

Choose InnoDB for transactional safety; MyISAM may be considered only after performance verification.

Test and tune the optimal batch size repeatedly.

Combine read and write tasks to avoid complex Kafka ordering issues.

Use Redis to record task offsets for crash‑recovery and idempotent inserts.

Master‑node task assignment with Zookeeper and Redisson locks provides reliable concurrency control.