Home >Backend Development >PHP Tutorial >Implementation of php+mysql caching technology_PHP tutorial
Implementation of php+mysql caching technology_PHP tutorial
- WBOYOriginal
- 2016-07-13 17:53:47899browse
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 databases as containers 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 to a file in the system, thereby preventing the database from being connected, constructing queries, and obtaining 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 Prepare SQL query
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 the 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 the 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 is to use 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 in the system.
The function unserialize() does the opposite of the above operation and returns the serialized string to its original structure and data content.
Taking an e-commerce store as an example. 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
category_id int (10) UNSIGNED PRI Auto_incremen
category_name varchar(255)
category_description text
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 into an array so that you can access them later
l Serialized array
l Save the serialized array to the 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);
//fwrite($fp,$OUTPUT);
fclose($fp);
/*View 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 of an array (a numeric index column containing the query results - each row is a number and a string... it's really confusing).
Put them together:
Determines 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 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 record in the cache
$records = unserialize(file_get_contents($file));
} else {
//Create cache through serialize() function
}
/*Additional other possibilities:
l Store cached results in shared memory for faster speed
l 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 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));
//$records=unserialize(fread($file,filesize($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);
fwrite($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 font
print ''.$row['category_name'].'
';
} else {
// Other directories are displayed using regular fonts
print $row['category_name'].'
';
}
} // end foreach