Quickstart and Examples
在线手册:中文 英文
PHP手册

Caching queries

There are four ways to trigger caching of a query.

Use of SQL hints and mysqlnd_qc.cache_by_default = 1 are explained below. Please, refer to the function reference on mysqlnd_qc_is_select() for a description of using a callback and, mysqlnd_qc_set_cache_condition() on how to set rules for automatic caching.

A SQL hint is a SQL standards compliant comment. As a SQL comment it is ignored by the database. A statement is considered eligible for caching if it either begins with the SQL hint enabling caching or it is a SELECT statement.

An individual query which shall be cached must begin with the SQL hint /*qc=on*/. It is recommended to use the PHP constant MYSQLND_QC_ENABLE_SWITCH instead of using the string value.

The examples SELECT statement string is prefixed with the MYSQLND_QC_ENABLE_SWITCH SQL hint to enable caching of the statement. The SQL hint must be given at the very beginning of the statement string to enable caching.

mysqlnd_qc.enable_qc=1
<?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 INT)");
$mysqli->query("INSERT INTO test(id) VALUES (1), (2)");

/* Will be cached because of the SQL hint */
$start microtime(true);
$res   $mysqli->query("/*" MYSQLND_QC_ENABLE_SWITCH "*/" "SELECT id FROM test WHERE id = 1");

var_dump($res->fetch_assoc());
$res->free();

printf("Total time uncached query: %.6fs\n"microtime(true) - $start);

/* Cache hit */
$start microtime(true);
$res   $mysqli->query("/*" MYSQLND_QC_ENABLE_SWITCH "*/" "SELECT id FROM test WHERE id = 1");

var_dump($res->fetch_assoc());
$res->free();

printf("Total time cached query: %.6fs\n"microtime(true) - $start);
?>

以上例程的输出类似于:

array(1) {
  ["id"]=>
  string(1) "1"
}
Total time uncached query: 0.000740s
array(1) {
  ["id"]=>
  string(1) "1"
}
Total time cached query: 0.000098s

If nothing else is configured, as it is the case in the quickstart example, the plugin will use the built-in default storage handler. The default storage handler uses process memory to hold a cache entry. Depending on the PHP deployment model, a PHP process may serve one or more web requests. Please, consult the web server manual for details. Details make no difference for the examples given in the quickstart.

The query cache plugin will cache all queries regardless if the query string begins with the SQL hint which enables caching or not, if the PHP configuration directive mysqlnd_qc.cache_by_default is set to 1. The setting mysqlnd_qc.cache_by_default is evaluated by the core of the query cache plugins. Neither the built-in nor user-defined storage handler can overrule the setting.

The SQL hint /*qc=off*/ can be used to disable caching of individual queries if mysqlnd_qc.cache_by_default = 1 It is recommended to use the PHP constant MYSQLND_QC_DISABLE_SWITCH instead of using the string value.

mysqlnd_qc.enable_qc=1
mysqlnd_qc.cache_by_default=1
<?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 INT)");
$mysqli->query("INSERT INTO test(id) VALUES (1), (2)");

/* Will be cached although no SQL hint is present because of mysqlnd_qc.cache_by_default = 1*/
$res $mysqli->query("SELECT id FROM test WHERE id = 1");
var_dump($res->fetch_assoc());
$res->free();

$mysqli->query("DELETE FROM test WHERE id = 1");

/* Cache hit - no automatic invalidation and still valid! */
$res $mysqli->query("SELECT id FROM test WHERE id = 1");
var_dump($res->fetch_assoc());
$res->free();

/* Cache miss - query must not be cached because of the SQL hint */
$res $mysqli->query("/*" MYSQLND_QC_DISABLE_SWITCH "*/SELECT id FROM test WHERE id = 1");
var_dump($res->fetch_assoc());
$res->free();
?>

以上例程会输出:

array(1) {
  ["id"]=>
  string(1) "1"
}
array(1) {
  ["id"]=>
  string(1) "1"
}
NULL

PECL/mysqlnd_qc forbids caching of statements for which at least one column from the statements result set shows no table name in its meta data by default. This is usually the case for columns originating from SQL functions such as NOW() or LAST_INSERT_ID(). The policy aims to prevent pitfalls if caching by default is used.

mysqlnd_qc.enable_qc=1
mysqlnd_qc.cache_by_default=1
<?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 INT)");
$mysqli->query("INSERT INTO test(id) VALUES (1)");

for (
$i 0$i 3$i++) {

    
$start microtime(true);

    
/* Note: statement will not be cached because of NOW() use */
    
$res $mysqli->query("SELECT id, NOW() AS _time FROM test");
    
$row $res->fetch_assoc();

    
/* dump results */
    
var_dump($row);

    
printf("Total time: %.6fs\n"microtime(true) - $start);

    
/* pause one second */
    
sleep(1);
}
?>

以上例程的输出类似于:

array(2) {
  ["id"]=>
  string(1) "1"
  ["_time"]=>
  string(19) "2012-01-11 15:43:10"
}
Total time: 0.000540s
array(2) {
  ["id"]=>
  string(1) "1"
  ["_time"]=>
  string(19) "2012-01-11 15:43:11"
}
Total time: 0.000555s
array(2) {
  ["id"]=>
  string(1) "1"
  ["_time"]=>
  string(19) "2012-01-11 15:43:12"
}
Total time: 0.000549s

It is possible to enable caching for all statements including those which has columns in their result set for which MySQL reports no table, such as the statement from the example. Set mysqlnd_qc.cache_no_table = 1 to enable caching of such statements. Please, note the difference in the measured times for the above and below examples.

mysqlnd_qc.enable_qc=1
mysqlnd_qc.cache_by_default=1
mysqlnd_qc.cache_no_table=1
<?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 INT)");
$mysqli->query("INSERT INTO test(id) VALUES (1)");

for (
$i 0$i 3$i++) {

    
$start microtime(true);

    
/* Note: statement will not be cached because of NOW() use */
    
$res $mysqli->query("SELECT id, NOW() AS _time FROM test");
    
$row $res->fetch_assoc();

    
/* dump results */
    
var_dump($row);

    
printf("Total time: %.6fs\n"microtime(true) - $start);

    
/* pause one second */
    
sleep(1);
}
?>

以上例程的输出类似于:

array(2) {
  ["id"]=>
  string(1) "1"
  ["_time"]=>
  string(19) "2012-01-11 15:47:45"
}
Total time: 0.000546s
array(2) {
  ["id"]=>
  string(1) "1"
  ["_time"]=>
  string(19) "2012-01-11 15:47:45"
}
Total time: 0.000187s
array(2) {
  ["id"]=>
  string(1) "1"
  ["_time"]=>
  string(19) "2012-01-11 15:47:45"
}
Total time: 0.000167s

Note:

Although mysqlnd_qc.cache_no_table = 1 has been created for use with mysqlnd_qc.cache_by_default = 1 it is bound it. The plugin will evaluate the mysqlnd_qc.cache_no_table whenever a query is to be cached, no matter whether caching has been enabled using a SQL hint or any other measure.


Quickstart and Examples
在线手册:中文 英文
PHP手册