Exporting Hundreds of Millions of MySQL Rows to Excel

Our company's SaaS system needed to let users export list data to Excel. But some customers' data — financial transaction records, for instance — had already grown into the tens of millions and beyond. Exports at that scale need a dedicated solution.

Otherwise, come month-end, hundreds of companies exporting Excel at the same time would send MySQL QPS through the roof, push server CPU and RAM past alert thresholds in an instant, and large-scale exports could render servers unavailable or even bring them down. (Note: for sensitive data like financials, add file encryption or temporary authorized-download links.)

The design

The starting principle: bulk exports must not affect normal business operations.

Deploy a dedicated database read replica to absorb the query load, and deploy separate groups of servers running a Spring Boot project as an MQ consumer cluster. All export requests are published to an MQ queue; Spring Boot uses pull mode to actively fetch export tasks from the queue and execute them, making full use of MQ's load-leveling and async capabilities, with thread pool resources strictly allocated inside the Spring Boot project.

Each task consumer queries MySQL list data in batches using the large-scale pagination scheme, uses Alibaba's EasyExcel to write the data to local disk, uploads the finished file to OSS (this can also go over the OSS internal network, streaming straight into the OSS file system), and then deletes the local file.

This design decouples exports completely from the main business — export becomes an independent module deployed on its own server nodes. Even if the export service goes down under a sudden flood of export requests (unlikely in practice, since MQ absorbs exactly this kind of spike), the main business keeps running untouched.

Resource limits and paginated queries

Combined with thread pool limits and a cap on how many files a single customer can export concurrently, we strictly control how many tasks a JVM pulls at once, keeping the service robust.

The database queries use the large-scale pagination scheme: first select the target ids by condition, drop the large-offset limit pattern in favor of an id cursor, keeping every SQL query in the millisecond range; and choose the selected columns carefully (an index is mandatory) so each batch flowing out of MySQL stays within a bounded size (100–500 KB).

-- Use an id cursor instead of a large-offset limit; start each batch from the previous batch's max id
select id from table where conditions and id > previous_batch_max_id order by id limit 5000;

On top of this we built an abstract factory and interfaces for developers to implement exports for all kinds of list features. Developers don't need to care how the data is paginated and batched, how the Excel file is generated, or how it gets uploaded — they just write the export SQL query and focus on the data.

Real-world results

In testing, exporting 75.5 million rows took a bit over 20 minutes (it could be faster with well-placed indexes and multi-threaded task decomposition for the queries and export), which comfortably covers our current business needs.

Estimating each SQL round-trip plus execution at 100 ms, the rough formula: (75,500,000 / 5,000 × 100 ms) / 1,000 / 60 ≈ 25 min

Memory growth for a single customer's export stays within about a 20 MB swing, because dataList only holds 5,000 rows at a time — once the data is flushed to disk, the list objects are garbage collected, so memory barely grows, with disk IO at 3–4 MB per batch. With multiple customers, thread resource limits and a cap on total export tasks are needed to keep the JVM from OOMing.

A single Excel worksheet holds only around 1 million rows; beyond that you need to split across workbooks, and how you split depends on available memory. For a full export of 100 million records, a single file would be far too large — the Excel output then needs to be split, stored, and compressed, otherwise the file is too big to open on the client side without exhausting memory.

Each database round-trip also must not burn too much performance, so the 75.5 million rows are exported over many batches. Once the Excel files on disk are complete, they're zip-compressed and uploaded straight to OSS, and finally the OSS resource URL is handed to the frontend app or web browser for the client to download.

Takeaways

Even for something as mundane as a data export feature, pushing performance to the limit touches a surprising number of concerns: MQ-based async load leveling, read replicas absorbing queries, cursor pagination, memory control and file splitting, deployment isolation, and planning horizontal scaling for future business growth. Every piece has to be designed properly.