PHP database cache implementation ideas

Caching SQL query results

Why should we 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 output of the entire script (HTML output), this may not work.

When you execute a SQL query, the Diandian processing process is:

l  Connect to database

l     Prepare SQL query

< !--[if !supportLists]-->l Send query to database

l Get the returned result

l 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 the cache:

Caching can take many forms depending on the needs of your program. The three most common methods are:

l                                                                                                          

l Content changes trigger cache (after discovering data changes, update the cache accordingly)

l                                                                                                                                                                                                 ​

Caching results:

FieldType KeyExtracategory_idcategory_namecategory_descriptionint(10) unsignedvarchar(255)textPRIauto_incremen

l                                                                                                                                                                --[if!supportLists]-->l !--[endif]-->Save the serialized array to the file

view plaincopy

$file = 'sql_cache.txt';  
$link = mysql_connect('localhost','username','password')  
    or die (mysql_error());  
mysql_select_db('shop')  
    or die (mysql_error());  
/* 构造SQL查询 */  
$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);

View the sql_cache.txt file, inside 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 associated array (that's why the data looks like it happens twice), one with the numeric index and the other with the string index.

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

view plaincopy

$file = 'sql_cache.txt';  
$records = unserialize(implode('',file($file)));  
//现在你可以通过$records数组并且取得原始查询的数据：  
foreach ($records as $id=>$row) {  
    print $row[&#39;category_name&#39;]."<br>";  
}

Note that $records is an array ( A numeric index column that contains the results of the query - each row is a row of a number and a string...what a mess).

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 records stored in the cache file or update the cache file

view plaincopy

$file = 'sql_cache.txt';  
$expire = 86400; // 24 小时 (单位：秒)
if (file_exists($file) &&   
filemtime($file) > (time() - $expire))  
{  
    // 取得缓存中的记录  
    $records = unserialize(file_get_contents($file));  
} else {  
    // 通过 serialize() 函数创建缓存  
}

Additional other possibilities:

l -[endif]-->Store cached results in shared memory for faster speedl ]-->Add a function to randomly run SQL queries 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 an update Caching (such as when the file_exists() function returns false). You can delete files using the function unlink().

[php] view plaincopy

$file = 'sql_cache.txt';  
$expire = 86400; // 24 小时
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());  
    /* 构造SQL查询 */  
    $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  
   
// 查询结果在数组 $records 中  
foreach ($records as $id=>$row) {  
    if ($row['category_id'] == $_REQUEST['category_id']) {  
        // 被选择的目录显示粗体字  
        print &#39;<B>&#39;.$row[&#39;category_name&#39;].&#39;</B><BR>&#39;;  
    } else {  
        // 其它目录显示用常规字体  
        print $row[&#39;category_name&#39;].&#39;<br>&#39;;  
    }  
} // end foreach