Strategies for Fast Import of 1 Billion Records into MySQL

The discussion starts by clarifying interview constraints: 1 billion rows, each 1 KB, unstructured logs stored in HDFS or S3, split into 100 ordered files, requiring ordered, minimally duplicated imports into a MySQL database.

Is a single MySQL table feasible? No – MySQL single‑table best practice is under 20 million rows. Using B+‑tree index analysis, a three‑level index supports roughly 20 million rows, while a four‑level index degrades performance and can hold up to 2.5 billion rows.

层数

最大数据量

2

18,720

3

21,902,400 ≈ 20 M

4

25,625,808,000 ≈ 2.5 B

Therefore the design splits the data into ~10 M rows per table (≈1 KB × 10 M = 10 GB) and creates about 100 tables.

Efficient writing recommends batch inserts (e.g., 100 rows per batch) with InnoDB transactions to guarantee atomicity. Retries should be applied; after N retries, fall back to single‑row inserts and log failures.

Insert order matters: write by primary‑key order, avoid non‑primary indexes during bulk load, or create them after data load.

Concurrency – concurrent writes to the same table break ordering and cause index contention, so concurrent writes to a single table are prohibited.

Storage engine choice : MyISAM offers higher raw insert speed but lacks transactions; InnoDB provides safety and acceptable performance when innodb_flush_log_at_trx_commit is set to 0 or 2. The article suggests using InnoDB unless the cluster disallows changing this setting.

Sharding – a single MySQL instance caps at ~5 K TPS; SSDs perform better than HDDs, but HDDs cannot handle many concurrent writes due to a single read/write head. Hence, the design must support configurable numbers of databases and tables, allowing dynamic adjustment for HDD or SSD environments.

File‑reading methods – several Java approaches were benchmarked on a 3.4 GB file:

读取方式

耗时

Files.readAllBytes

OOM

FileReader+BufferedReader

(line‑by‑line)

11 s

File+BufferedReader

10 s

Scanner

57 s

Java NIO FileChannel

(buffered)

3 s

Although NIO is fastest, it does not preserve line boundaries, making BufferedReader the preferred method for 10 GB files (≈30 s) because the overall bottleneck is the database write phase.

Task coordination – the initial idea of decoupling read and write via Kafka was abandoned due to ordering and concurrency complexities. The final approach merges reading and writing in a single task.

Reliability is ensured by assigning a unique primary‑key composed of {taskId}{fileIndex}{lineNumber} . Redis stores each task’s offset; after a successful batch insert, the offset is incremented (e.g., INCRBY task_offset_{taskId} 100 ). On failure, retries are performed, and progress is only committed after success.

INCRBY KEY_NAME INCR_AMOUNT

Task metadata includes bizId, databaseIndex, tableIndex, parentTaskId, offset, and status. Tasks are claimed by nodes using a semaphore (Redisson) to limit concurrent writes per database.

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

Redisson semaphores lack lease renewal, so the design switches to a leader‑election model using Zookeeper+Curator. The leader schedules tasks, assigns them to workers, and uses Redisson distributed locks (with renewal) to guarantee exclusive execution.

The article concludes with nine practical takeaways: clarify constraints, split data, consider sharding, limit concurrent writes, dynamically adjust thresholds, compare InnoDB vs MyISAM, tune batch size, merge read/write tasks, and use Redis/Zookeeper for progress tracking and coordination.