This query cache feature enhances a lot performance of my web site, unfortunately, installation dll are available on windows only up to version 5.5. For example, if I use it on php 5.6, there is an error message on startup saying it is not compatible
Caching queries
There are four ways to trigger caching of a query.
- Use of SQL hints on a per query basis
- User supplied callbacks to decide on a per query basis, for example, using mysqlnd_qc_is_select()
- mysqlnd_set_cache_condition() for rule based automatic per query decisions
-
mysqlnd_qc.cache_by_default = 1to cache all queries blindly
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.
-
not eligible for caching and not cached:
INSERT INTO test(id) VALUES (1) -
not eligible for caching and not cached:
SHOW ENGINES -
eligible for caching but uncached:
SELECT id FROM test -
eligible for caching and cached:
/*qc=on*/SELECT id FROM test
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.
例1 Using the MYSQLND_QC_ENABLE_SWITCH SQL hint
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.
例2 Using the MYSQLND_QC_DISABLE_SWITCH SQL hint
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.
例3 Example showing which type of statements are not cached
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.
例4 Enabling caching for all statements using the mysqlnd_qc.cache_no_table ini setting
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
注意:
Although
mysqlnd_qc.cache_no_table = 1has been created for use withmysqlnd_qc.cache_by_default = 1it is bound it. The plugin will evaluate themysqlnd_qc.cache_no_tablewhenever a query is to be cached, no matter whether caching has been enabled using a SQL hint or any other measure.
add a note
User Contributed Notes 1 note
laurent at pele dot org ¶
7 years ago
