略微加速

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 = 1 to 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.

mysqlnd_qc.enable_qc=1

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

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

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

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.