English
English
Optimizing database query performance is a common challenge when developing high-performance PHP applications. Especially when processing large amounts of data, repeatedly executing the same query can greatly slow down the application response time. At this time, cached query results become an effective optimization method. This article will explore how to optimize PHP application performance by caching mysqli_result query results to Redis or file system.
Whenever an application performs a database query, the database engine will read data from the hard disk and generate query results. If these queries are duplicate and the data changes infrequently, it is unnecessary to read the same data from the database each time. Caching technology can store query results in memory (such as Redis) or store them in a local file system, thereby reducing the frequency of database access and improving application performance.
Redis is an open source in-memory data storage system, often used to cache data, especially suitable for storing frequently accessed data. Below are the basic steps to cache the mysqli_result query results to Redis.
First, you need to install the Redis service on the server. The installation method depends on your operating system. You can refer to m66.net/redis-installation for detailed installation steps.
In order to use Redis in PHP, you need to install the Redis extension for PHP. You can install it through the following command:
sudo pecl install redis
Then, enable the Redis extension in the php.ini file:
extension=redis.so
Here is a simple example code showing how to cache MySQL query results into Redis:
<?php
// connect MySQL database
$mysqli = new mysqli('localhost', 'username', 'password', 'database');
// 检查connect是否成功
if ($mysqli->connect_error) {
die('connect失败: ' . $mysqli->connect_error);
}
// Query cached keys
$queryKey = 'my_query_result';
// connect Redis
$redis = new Redis();
$redis->connect('127.0.0.1', 6379);
// Try from Redis Get cached data
$cachedResult = $redis->get($queryKey);
if ($cachedResult) {
// if Redis There are cached results in，Return directly
$result = unserialize($cachedResult);
echo 'from Redis Get data:';
} else {
// if Redis No cached results，Execute query and cache results
$result = $mysqli->query('SELECT * FROM my_table');
// Serialize and save query results Redis
$redis->set($queryKey, serialize($result), 3600); // Set the cache validity period to 1 Hour
echo 'from MySQL Get data:';
}
// Print query results
while ($row = $result->fetch_assoc()) {
echo $row['column_name'] . "\n";
}
// 关闭connect
$mysqli->close();
?>
In this example, we first try to get the cached query results from Redis. If there is cached data in Redis, use it directly; if there is no cached data, execute a query and store the results in Redis.
The cache should not exist forever, especially when the data may change. To ensure data timeliness, you can set the cache expiration time (in the example above, it is 3600 seconds, which is 1 hour). You can adjust this time according to the application's needs.
If you do not want to use Redis, you can also choose to use the file system to cache the query results. File system cache is suitable for scenarios that do not require extremely high access speeds and is easier to deploy than Redis.
Here is a simple example showing how to cache query results to the file system:
<?php
// connect MySQL database
$mysqli = new mysqli('localhost', 'username', 'password', 'database');
// 检查connect是否成功
if ($mysqli->connect_error) {
die('connect失败: ' . $mysqli->connect_error);
}
// Query the cached file path
$cacheFile = 'cache/my_query_result.cache';
// Check if the cache file exists and has not expired
if (file_exists($cacheFile) && filemtime($cacheFile) > (time() - 3600)) {
// if缓存文件存在且未过期，Read file content
$result = unserialize(file_get_contents($cacheFile));
echo 'from文件缓存Get data:';
} else {
// if缓存文件不存在或已过期，Execute query and cache results到文件
$result = $mysqli->query('SELECT * FROM my_table');
// Serialize and store the query results to a file
file_put_contents($cacheFile, serialize($result));
echo 'from MySQL Get data:';
}
// Print query results
while ($row = $result->fetch_assoc()) {
echo $row['column_name'] . "\n";
}
// 关闭connect
$mysqli->close();
?>
In this example, we serialize the query results and store them into a file. If the file exists and has not expired, we read the cached results directly from the file; otherwise, execute the query and store the results to the file.
Similar to Redis, when using file system cache, we also need to set the cache expiration time. In the example code, the filemtime function determines the last modification time of the cached file to determine whether the database needs to be requeried.
By caching the mysqli_result query results to Redis or file systems, the performance of PHP applications can be significantly improved and the burden on the database can be reduced. Redis is more suitable for scenarios where data is frequently accessed, while file system cache is suitable for applications with less access frequency. Which caching method to choose depends on your application's needs and deployment environment.
Whether you choose Redis or file system cache, you need to set a reasonable cache failure time according to the actual situation to ensure the timeliness and accuracy of the data.
