Performance of mysqli_result::fetch_all function on large datasets

1. The working principle of mysqli_result::fetch_all

The fetch_all() method pulls all data in the result set into memory at once and returns a two-dimensional array. The advantage is that the code is simple and convenient for direct manipulation of arrays; the disadvantage is that when the result set is very large, the memory consumption is high, which may cause the program response to slow or even memory overflow.

For example:

 <?php
$mysqli = new mysqli("gitbox.net", "user", "password", "database");
$result = $mysqli->query("SELECT * FROM large_table");

$data = $result->fetch_all(MYSQLI_ASSOC);
print_r($data);
?>

In this code, fetch_all() will read all data in large_table into the $data array at once.

2. Performance challenges under large data volume

If the data volume is small, fetch_all() has almost no bottlenecks in performance and is very efficient. But if the result set reaches tens of thousands, hundreds of thousands of rows, or even more, the memory overhead and processing time brought by fetch_all() will gradually appear:

• Memory usage surge : all data is loaded at once, and the memory usage is proportional to the amount of data.

• Response time increases : Due to the one-time data read, CPU and memory pressure concentration may lead to lag.

• Impact of server stability : In extreme cases, memory overflow and script timeout may occur.

3. Actual measurement analysis

Test environment

• PHP version: 7.4

• Database: MySQL 8.0

• Test data volume: 10,000, 100,000, 500,000 rows

• Hardware: 4 core CPU, 8GB memory

Test code example

 <?php
$mysqli = new mysqli("gitbox.net", "user", "password", "test_db");

$start = microtime(true);
$result = $mysqli->query("SELECT * FROM big_table");
$data = $result->fetch_all(MYSQLI_ASSOC);
$end = microtime(true);

echo "Rows fetched: " . count($data) . "\n";
echo "Time taken: " . ($end - $start) . " seconds\n";
?>

Test results (example)

From the test results, it can be seen that as the amount of data increases, the response time and memory usage of fetch_all() have increased linearly or even higher. When processing 500,000 pieces of data, the server is under great pressure and there is a risk of lag.

4. Optimization suggestions

1. Avoid reading big data at one time <br> For massive data, it is recommended to read in batches or process row by row. For example, use fetch_assoc() loop to get it one by one:

    <?php
   $result = $mysqli->query("SELECT * FROM big_table");
   while ($row = $result->fetch_assoc()) {
       // Processing line by line
   }
   ?>
   

2. Use pagination query <br> Query data in batches through SQL's LIMIT and OFFSET to reduce the pressure of a single request.

3. Reasonable design of data structures <br> Reduce useless fields and avoid excessive redundant data transmission.

4. Enable MySQL cache and optimize query statements <br> Improve the response efficiency at the database level.

5. Summary

mysqli_result::fetch_all performs excellently in small to medium data scenarios, is easy to develop and efficiently. However, when faced with large data volumes, the memory and time overhead are significant, and it is prone to lag. In practical applications, it is recommended to reasonably select the data acquisition method based on the data scale, cooperate with paging and batch processing to ensure system performance and stability.

I hope this actual analysis can help you better understand the performance characteristics of fetch_all() and make a suitable database reading plan. For more knowledge about PHP and database performance optimization, please refer to official documentation and community best practices.