Why MySQL Never Runs Out of Memory During Massive Full Table Scans

We often encounter tables with more than ten million rows (e.g., order tables, logistics trajectory tables). If a query lacks an index or the index is ineffective, the query performs a full table scan. Why doesn’t MySQL run out of memory (OOM) with such large data volumes? Let’s explore the reasons.

1. MySQL data transmission to the client

Assume a MySQL table size of 3 GB while the server has only 2 GB of memory:

If the client performs a full table scan, MySQL sends data to the net buffer in batches. The net buffer does not occupy the entire 2 GB server memory; it defaults to 16 KB. MySQL reads data until the net buffer is full (16 KB) and then sends it to the socket send buffer:

The socket send buffer forwards the data to the client’s socket receive buffer. As MySQL clears the net buffer, the process repeats:

MySQL then reads another 16 KB into the net buffer, repeating until all data is transmitted. This staged transmission explains why scanning a very large table is slow: MySQL reads only the net buffer size each time and sends the data in batches, preventing the server’s memory from being exhausted.

2. MySQL’s improved LRU

Full‑table scans load a large amount of data into the buffer pool, evicting existing pages and reducing the buffer pool hit rate. To mitigate this, InnoDB uses an improved LRU linked‑list mechanism that separates hot and cold data:

The young generation (about 63 %) stores hot data, while the old generation (about 37 %) holds less frequently used data. Each query initially places data into the old‑generation list:

This design ensures that even during a full table scan, data resides only in the old generation, leaving the young generation unaffected. If data in the young generation remains active beyond a configured time, it is promoted to the old generation.

Summary

(1) MySQL reads data into a 16 KB net buffer, sends it to the socket send buffer, which then transmits to the client’s socket receive buffer; this staged transmission prevents OOM during full scans.

(2) MySQL’s buffer pool uses an improved LRU to keep hot data in the pool and isolate cold data, maintaining query efficiency.