PHP Cache Implementation. Php code SQL query cache is suitable for PHP programmers who are interested in caching SQL queries to reduce database connection and execution load and improve script performance. Overview Php code
SQL query cache
Suitable for readers
This tutorial is applicable to PHP programmers who are interested in caching SQL queries to reduce the load on database connection and execution and improve script performance.
Overview
Many sites use databases as containers for storing site data. The database contains the producer information, directory structure, article, or message book. some data may be completely static, which will be of great benefit from a cache system.
Such a system caches the SQL query results to a file of the system for storage, thus blocking connection to the database, constructing the query and obtaining the returned results, and improving the response time.
Some system databases are not stored on WEB servers. Therefore, a remote connection (TCP or similar) is required, or a large amount of data is obtained from the database, in this way, you have to endure more time, which is determined by the system response time and resource utilization.
Prerequisites
This tutorial uses MySQL as the database. You need to install MySQL (www.mysql.com is valid for download) and activate php mysql extension (activated by default ).
To query databases, you need to have basic knowledge about SQL (Structured Query Language.
Cache SQL query results
Why cache query results?
The cache query results greatly improve the script execution time and resource requirements.
Caching SQL query results also allows you to process data later. If you use the file cache to store the output results of all scripts (HTML output), this may not work.
When you execute an SQL query, the typical processing process is:
L connect to the database
L prepare SQL query
L send query to database
L get the returned results
L close database connection
The above method occupies resources and, on the contrary, affects the script performance. Coordination can only be achieved by obtaining a large amount of returned data and the location of the database server. Although continuous connection can improve the load when connecting to the database, it consumes a lot of memory resources. if a large amount of data is obtained, the storage duration will be very short.
Create an SQL query:
SQL (Structured Query Language) queries are used as interfaces to operate databases and their content. SQL can be used to define and edit the table structure, insert data to the table, and update or delete information in the table.
SQL is a language used for data communication. In most PHP databases, extensions (MySQL, ODBC, Oracle, and so on) pass SQL queries to the database to manage the entire process.
In this tutorial, only the select language is used to obtain data in the database. The data will be cached and then used as the data source.
Decide when to update the cache:
Depending on the program's needs, the cache can take multiple forms. The most common three methods are:
L time-triggered cache (expired timestamp)
L content change triggers cache (when data changes are detected, the cache is updated accordingly)
L manually trigger the cache (manually inform the system that the information is out of date and generate a new cache)
Your cache requirements may be one or more of the above principles. This tutorial will discuss how to trigger the time. However, in a comprehensive Cache mechanism, the combination of the three methods will be used.
Cache results:
Basically, the cache uses two PHP functions: serialize () and unserialize () (Note: These two functions represent serialization and deserialization respectively ).
The serialize () function is used to store PHP values. it ensures that the types and structures of these values are not lost.
In fact, PHP's session extension uses serialized variables to store session variables ($ _ SESSION) in a system file.
The unserialize () function is opposite to the preceding operation and returns the serialized string to its original structure and data content.
In this example, an e-commerce store is used as an example. The store has two basic tables, categories and products (the original database table name here). The product table may change every day and the categories remains unchanged.
To display the product, you can use an output cache script to store the output HTML results to a file. However, the categories table may need to be processed later. For example, all directories are displayed through the variable category_id (obtained through $ _ REQUEST ['Category _ id']). you may want to highlight the selected directory.
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, the time-triggered cache technology is used to set the cache SQL output to expire after a period of time. In this special example, the time is set to 24 hours.
Serialization example:
L connect to the database
L query execution
L obtain all the results to form an array so that you can access
L serialized array
L save serialized arrays to files
$ File = 'SQL _cache.txt ';
$ Link = mysql_connect ('localhost', 'username', 'password ')
Or die (mysql_error ());
Mysql_select_db ('shop ')
Or die (mysql_error ());
/* Construct an 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 );
The SQL _cache.txt file may contain the following content:
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. If you use the mysql_fetch_array () function to return the array of the numeric index and an associated array (that is why the data seems to have happened twice), one is the numeric index and the other is the string index.
Use cache:
To use the cache, you need to use the unserialize () function to restore the data to the original format and type.
You can use the file_get_contents() to read the content of the SQL _cache.txt file and assign it to a variable.
Note: This function is valid in PHP4.3.0 and later versions. If you are using an old version of PHP, a simple method is to use the file () function (read the entire file to an array, and each row becomes an array ). The implode () function is used to connect each element of the array into a string and then use unserialize () for deserialization.
// File_get_contents () is suitable for PHP <4.3.0
$ File = 'SQL _cache.txt ';
$ Records = unserialize (implode ('', file ($ file )));
Now you can use the $ records array to obtain the original query data:
Foreach ($ records as $ id => $ row ){
Print $ row ['Category _ name']."
";
}
Note that $ records is an array (a numeric index column containing the query results-each row is a number and a string... it is really messy.
Put them in one piece:
The cache is determined based on the time in this example. If the modified timestamp of a file is greater than the current timestamp minus the expiration timestamp, the file will be cached; otherwise, the cache will be updated.
L check whether the file exists and the timestamp is earlier than the set expiration time
L obtain records stored in cached files or update cached files
$ File = 'SQL _cache.txt ';
$ Expire = 86400; // 24 hours (unit: Seconds)
If (file_exists ($ file )&&
Filemtime ($ file)> (time ()-$ expire ))
{
// Obtain cache records
$ Records = unserialize (file_get_contents ($ file ));
} Else {
// Use the serialize () function to create a cache
}
Additional possibilities:
L store the cache results in the shared memory for faster retrieval
L added a function to run SQL queries randomly and check whether the output is consistent with the cache output. If they are inconsistent, update the cache (the probability of this function running count can be set to 1/100 ). The hash algorithm (such as MD5 () can be used to determine whether the string or file has changed.
L adds an administrator function to manually delete the cache file to force update the cache (for example, when the file_exists () function returns false ). You can use the unlink () function to delete files.
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 an 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 result is in the array $ records
Foreach ($ records as $ id => $ row ){
If ($ row ['Category _ id'] = $ _ REQUEST ['Category _ id']) {
// The selected directory displays the bold characters
Print'
'. $ Row ['Category _ name'].'
';
} Else {
// Use the regular font to display other directories
Print $ row ['Category _ name'].'
';
}
} // End foreach
Author "tw5566"
SQL query cache is suitable for PHP programmers who are interested in caching SQL queries to reduce database connection and execution load and improve script performance. Overview Xu...
