Home >Backend Development >PHP Tutorial >Implementation of PHP cache_PHP tutorial
Implementation of PHP cache_PHP tutorial
- WBOYOriginal
- 2016-07-13 17:45:31687browse
Php code
SQL query cache
Suitable for readers
This tutorial is suitable for PHP programmers who are interested in caching SQL queries to reduce the load on database connections and execution and improve script performance.
Overview
Many sites use a database as a container for site data storage. The database contains product information, directory structures, articles or guestbooks. Some of the data is likely to be completely static, and these will benefit greatly from a caching system.
Such a system improves response time by caching the results of SQL queries in a file on the system, thereby preventing the database from being connected, constructing the query, and obtaining the returned results.
Some system databases are not placed on the WEB server, which requires a remote connection (TCP or other similar), or to obtain a large amount of data from the database, so you have to endure more time, which depends on the system response time and resources. use.
Prerequisite
This tutorial uses MySQL as the database. You need to install MySQL (www.mysql.com download is available) and activate the PHP MYSQL extension (activated by default).
Since you want to query the database, you need to know some basic knowledge of SQL (Structured Query Language).
Caching SQL query results
Why cache query results?
Caching query results can greatly improve script execution time and resource requirements.
Caching SQL query results also allows you to post-process the data. If you use a file cache to store the entire script's output (HTML output), this may not work.
When you execute a SQL query, the processing process of Diandian is:
l Connect to database
l Preparing SQL queries
l Send query to database
l Get the return result
l Close database connection
The above method is very resource intensive and adversely affects the performance of the script. This can only be coordinated through the two factors of obtaining a large amount of returned data and the location of the database server. Although continuous connections can improve the load when connecting to the database, they are very memory resource intensive. If a large amount of data is obtained, the entire storage time will be very short.
Create a SQL query:
SQL (Structured Query Language) queries are used as an interface for manipulating the database and its contents. SQL can be used to define and edit the structure of a table, insert data into the table, and update or delete information in the table.
SQL is a language used to communicate with data. In most PHP database extensions (MySQL, ODBC, Oracle, etc.), the entire process is managed by passing SQL queries to the database.
In this tutorial, only the select language is used to obtain data in the database. This data will be cached and later used as a data source.
Decide when to update cache:
Caching can take many forms depending on the needs of the program. The 3 most common ways are:
l Time triggered cache (expired timestamp)
l Content changes trigger cache (after discovering data changes, the cache is updated accordingly)
l Manually trigger cache (manually inform the system that the information has expired and force a new cache)
Your caching needs may be a combination of one or more of the above principles. This tutorial will discuss time triggering methods. However, in a comprehensive caching mechanism, a combination of the 3 methods will be used.
Cached results:
The basic cache uses PHP's two functions serialize() and unserialize() (Annotation: These two functions represent serialization and deserialization respectively).
The function serialize() is used to store PHP values, which ensures that the type and structure of these values are not lost.
In fact, PHP's session extension uses serialized variables and stores the session variable ($_SESSION) in a file on the system.
The function unserialize() does the opposite of the above operation and returns the serialized string to its original structure and data content.
In this case, take an e-commerce store. The store has two basic tables, categories and products (here is the original database table name). The product table may change every day, but categories remain static.
To display products, you can use an output caching script to store the output HTML results in a file. However, the categories table may require post-processing. For example, if all categories are displayed via the variable category_id (obtained via $_REQUEST['category_id']), you may wish to highlight the currently selected category.
Table categories structure
Field
Type
Key
Extra
category_id
category_name
category_description
int(10) unsigned
varchar(255)
text
PRI
auto_incremen
In this example, time-triggered caching technology is used to set the cached SQL output to expire after a period of time. In this particular example, the period of time is 24 hours.
Serialization example:
l Connect to database
l Execute query
l Get all the results to form an array so that you can access it later
l Serialized array
l Save the serialized array to a file
$file = 'sql_cache.txt';
$link = mysql_connect('localhost','username','password')
or die (mysql_error());
mysql_select_db('shop')
or die (mysql_error());
/* Construct SQL query */
$query = "SELECT * FROM categories";
$result = mysql_query($query)
or die (mysql_error());
while ($record = mysql_fetch_array($result) )
{
$records[] = $record;
}
$OUTPUT = serialize($records);
$fp = fopen($file,"w"); // Open the file with write permission
fputs($fp, $OUTPUT);
fclose($fp);
Check the sql_cache.txt file. The content may be similar to this:
a:1:{i:0;a:6:{i:0;s:1:"1";s:11:"category_id";s:1:"1";i:1;s:9: "Computers";s:13:"category_name";s:9:
"Computers" ;i:2;s:25:"Description for computers";s:20:"category_description"
;s:25:"Description for computers";}}
This output is the internal representation of its variables and types. Suppose you use the mysql_fetch_array() function to return a numeric indexed array and an associative array (that's why the data looks like it happens twice), one with the numeric index and the other with the string index.
Use cache:
To use caching, you need to use the function unserialize() to restore the data to its original format and type.
You can use the file_get_contents() function to read the contents of the sql_cache.txt file and assign it to a variable.
Please note: This function is valid in PHP4.3.0 and above. If you are using an older version of PHP, a simple method is to use the file() function (read the entire file into an array, each line becomes an array). The implode() function is used to concatenate the elements of the array into a string and then use unserialize() to deserialize it.
// file_get_contents() suitable for PHP < 4.3.0
$file = 'sql_cache.txt';
$records = unserialize(implode('',file($file)));
Now you can pass the $records array and get the original query data:
foreach ($records as $id=>$row) {
Print $row['category_name']."
";
}
Note that $records is a row in an array (a numeric indexed column containing the results of the query - each row is a number and a string...what a mess).
Put them together:
Decide whether to cache based on the time in this example. If the file modification timestamp is greater than the current timestamp minus the expiration timestamp, then the cache is used, otherwise the cache is updated.
l Check whether the file exists and the timestamp is less than the set expiration time
l Get the records stored in the cache file or update the cache file
$file = 'sql_cache.txt';
$expire = 86400; // 24 hours (unit: seconds)
if (file_exists($file) &&
filemtime($file) > (time() - $expire))
{
// Get the records in the cache
$records = unserialize(file_get_contents($file));
} else {
//Create cache through serialize() function
}
Additional possibilities:
l Store cached results in shared memory for faster speed
l ‐‐ - – Added a function to randomly run the SQL query and check whether the output is consistent with the cached output. If it is inconsistent, update the cache (the probability of running this function can be set as 1/100). Hash algorithms (such as MD5()) can help determine whether a string or file has changed.
l Add an administrator function to manually delete this cache file to force the cache to be updated (such as when the file_exists() function returns false). You can delete files using the function unlink().
Script:
$file = 'sql_cache.txt';
$expire = 86400; // 24 hours
if (file_exists($file) &&
filemtime($file) > (time() - $expire)) {
$records = unserialize(file_get_contents($file));
} else {
$link = mysql_connect('localhost','username','password')
or die (mysql_error());
Mysql_select_db('shop')
or die (mysql_error());
/* Construct SQL query */
$query = "SELECT * FROM categories";
$result = mysql_query($query)
or die (mysql_error());
While ($record = mysql_fetch_array($result) ) {
$records[] = $record;
}
$OUTPUT = serialize($records);
$fp = fopen($file,"w");
fputs($fp, $OUTPUT);
fclose($fp);
} // end else
//The query results are in the array $records
foreach ($records as $id=>$row) {
If ($row['category_id'] == $_REQUEST['category_id']) {
//The selected directory is displayed in bold
print ''.$row['category_name'].'
';
} else {
// Other directories are displayed in regular fonts
print $row['category_name'].'
';
}
} // end foreach
Author "tw5566"