Finding cache candidates
A statement should be considered for caching if it is executed often and has a long run time. Cache candidates are found by creating a list of statements sorted by the product of the number of executions multiplied by the statements run time. The function mysqlnd_qc_get_query_trace_log() returns a query log which help with the task.
Collecting a query trace is a slow operation. Thus, it is disabled by default. The PHP configuration directive mysqlnd_qc.collect_query_trace is used to enable it. The functions trace contains one entry for every query issued before the function is called.
Example #1 Collecting a query trace
mysqlnd_qc.enable_qc=1 mysqlnd_qc.collect_query_trace=1
<?php
$mysqli = new mysqli ( "host" , "user" , "password" , "schema" , "port" , "socket" );
for ( $i = 0 ; $i < 2 ; $i ++) {
$res = $mysqli -> query ( "SELECT 1 AS _one FROM DUAL" );
$res -> free ();
}
var_dump ( mysqlnd_qc_get_query_trace_log ());
?> 以上例程会输出：
array(2) {
[0]=>
array(8) {
["query"]=>
string(26) "SELECT 1 AS _one FROM DUAL"
["origin"]=>
string(102) "#0 qc.php(7): mysqli->query('SELECT 1 AS _on...')
#1 {main}"
["run_time"]=>
int(0)
["store_time"]=>
int(25)
["eligible_for_caching"]=>
bool(false)
["no_table"]=>
bool(false)
["was_added"]=>
bool(false)
["was_already_in_cache"]=>
bool(false)
}
[1]=>
array(8) {
["query"]=>
string(26) "SELECT 1 AS _one FROM DUAL"
["origin"]=>
string(102) "#0 qc.php(7): mysqli->query('SELECT 1 AS _on...')
#1 {main}"
["run_time"]=>
int(0)
["store_time"]=>
int(8)
["eligible_for_caching"]=>
bool(false)
["no_table"]=>
bool(false)
["was_added"]=>
bool(false)
["was_already_in_cache"]=>
bool(false)
}
}
Assorted information is given in the trace. Among them timings and the origin of the query call. The origin property holds a code backtrace to identify the source of the query. The depth of the backtrace can be limited with the PHP configuration directive mysqlnd_qc.query_trace_bt_depth . The default depth is 3.
Example #2 Setting the backtrace depth with the mysqlnd_qc.query_trace_bt_depth ini setting
mysqlnd_qc.enable_qc=1 mysqlnd_qc.collect_query_trace=1
<?php
$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), (3)" );
for ( $i = 0 ; $i < 3 ; $i ++) {
$res = $mysqli -> query ( "SELECT id FROM test WHERE id = " . $mysqli -> real_escape_string ( $i ));
$res -> free ();
}
$trace = mysqlnd_qc_get_query_trace_log ();
$summary = array();
foreach ( $trace as $entry ) {
if (!isset( $summary [ $entry [ 'query' ]])) {
$summary [ $entry [ 'query' ]] = array(
"executions" => 1 ,
"time" => $entry [ 'run_time' ] + $entry [ 'store_time' ],
);
} else {
$summary [ $entry [ 'query' ]][ 'executions' ]++;
$summary [ $entry [ 'query' ]][ 'time' ] += $entry [ 'run_time' ] + $entry [ 'store_time' ];
}
}
foreach ( $summary as $query => $details ) {
printf ( "%45s: %5dms (%dx)\n" ,
$query , $details [ 'time' ], $details [ 'executions' ]);
}
?> 以上例程的输出类似于：
DROP TABLE IF EXISTS test: 0ms (1x)
CREATE TABLE test(id INT): 0ms (1x)
INSERT INTO test(id) VALUES (1), (2), (3): 0ms (1x)
SELECT id FROM test WHERE id = 0: 25ms (1x)
SELECT id FROM test WHERE id = 1: 10ms (1x)
SELECT id FROM test WHERE id = 2: 9ms (1x)