The default invalidation strategy of the query cache plugin is Time to Live
(TTL). The built-in storage handlers will use the default
TTL defined by the PHP configuration value
mysqlnd_qc.ttl
unless the query string contains a hint for setting a different
TTL. The TTL is specified in seconds.
By default cache entries expire after 30 seconds
The example sets mysqlnd_qc.ttl=3 to cache
statements for three seconds by default. Every second it updates
a database table record to hold the current time and executes
a SELECT statement to fetch the record from the
database. The SELECT statement is cached for
three seconds because it is prefixed with the SQL hint enabling
caching. The output verifies that the query results are taken
from the cache for the duration of three seconds before they
are refreshed.
Beispiel #1 Setting the TTL with the mysqlnd_qc.ttl ini setting
mysqlnd_qc.enable_qc=1 mysqlnd_qc.ttl=3
<?php
/* Connect, create and populate test table */
$mysqli = new mysqli("host", "user", "password", "schema", "port", "socket");
$mysqli->query("DROP TABLE IF EXISTS test");
$mysqli->query("CREATE TABLE test(id VARCHAR(255))");
for ($i = 0; $i < 7; $i++) {
/* update DB row */
if (!$mysqli->query("DELETE FROM test") ||
!$mysqli->query("INSERT INTO test(id) VALUES (NOW())"))
/* Of course, a real-life script should do better error handling */
die(sprintf("[%d] %s\n", $mysqli->errno, $mysqli->error));
/* select latest row but cache results */
$query = "/*" . MYSQLND_QC_ENABLE_SWITCH . "*/";
$query .= "SELECT id AS _time FROM test";
if (!($res = $mysqli->query($query)) ||
!($row = $res->fetch_assoc()))
{
printf("[%d] %s\n", $mysqli->errno, $mysqli->error);
}
$res->free();
printf("Wall time %s - DB row time %s\n", date("H:i:s"), $row['_time']);
/* pause one second */
sleep(1);
}
?>
Oben gezeigte Beispiele erzeugen eine ähnliche Ausgabe wie:
Wall time 14:55:59 - DB row time 2012-01-11 14:55:59 Wall time 14:56:00 - DB row time 2012-01-11 14:55:59 Wall time 14:56:01 - DB row time 2012-01-11 14:55:59 Wall time 14:56:02 - DB row time 2012-01-11 14:56:02 Wall time 14:56:03 - DB row time 2012-01-11 14:56:02 Wall time 14:56:04 - DB row time 2012-01-11 14:56:02 Wall time 14:56:05 - DB row time 2012-01-11 14:56:05
As can be seen from the example, any TTL based cache
can serve stale data. Cache entries are not automatically invalidated,
if underlying data changes. Applications using the default
TTL invalidation strategy must be able to work correctly
with stale data.
A user-defined cache storage handler can implement any invalidation strategy to work around this limitation.
The default TTL can be overruled using the SQL hint
/*qc_tt=seconds*/. The SQL hint must be appear immediately
after the SQL hint which enables caching. It is recommended to use the PHP constant
MYSQLND_QC_TTL_SWITCH
instead of using the string value.
Beispiel #2 Setting TTL with SQL hints
<?php
$start = microtime(true);
/* Connect, create and populate test table */
$mysqli = new mysqli("host", "user", "password", "schema", "port", "socket");
$mysqli->query("DROP TABLE IF EXISTS test");
$mysqli->query("CREATE TABLE test(id INT)");
$mysqli->query("INSERT INTO test(id) VALUES (1), (2)");
printf("Default TTL\t: %d seconds\n", ini_get("mysqlnd_qc.ttl"));
/* Will be cached for 2 seconds */
$sql = sprintf("/*%s*//*%s%d*/SELECT id FROM test WHERE id = 1", MYSQLND_QC_ENABLE_SWITCH, MYSQLND_QC_TTL_SWITCH, 2);
$res = $mysqli->query($sql);
var_dump($res->fetch_assoc());
$res->free();
$mysqli->query("DELETE FROM test WHERE id = 1");
sleep(1);
/* Cache hit - no automatic invalidation and still valid! */
$res = $mysqli->query($sql);
var_dump($res->fetch_assoc());
$res->free();
sleep(2);
/* Cache miss - cache entry has expired */
$res = $mysqli->query($sql);
var_dump($res->fetch_assoc());
$res->free();
printf("Script runtime\t: %d seconds\n", microtime(true) - $start);
?>
Oben gezeigte Beispiele erzeugen eine ähnliche Ausgabe wie:
Default TTL : 30 seconds
array(1) {
["id"]=>
string(1) "1"
}
array(1) {
["id"]=>
string(1) "1"
}
NULL
Script runtime : 3 seconds