Die MySQL-Server Optionen --log-slow-queries und --log-queries-using-no-indexes sollten jedem MySQL-Anwender geläufig sein. Wenn nicht, schnell die Links verfolgen und die Wissenslücke schließen.
Nicht bekannt waren mir die Flags SERVER_QUERY_NO_INDEX_USED und SERVER_QUERY_NO_GOOD_INDEX_USED. Ich bin das erste Mal über die beiden Flags gestolpert als ich mich fragte wie ein Test für die PHP-Funktion mysqli_report() auszusehen hat. mysqli_report() ist eine überaus nützliche Funktion, welche die ansonsten wenig redsame ext/mysqli-API dazu bringt jedes noch so kleine Problem in Form einer PHP-Warning oder eines PHP-Errors anzuzeigen. Gerade auf Test- und Entwicklungssystemen ist die nützlich, um Probleme aufzudecken, die ansonsten nicht einmal ein error_reporting(E_ALL) zu Tage fördert. Ich kann nur dazu raten, die Funktion auszuprobieren, sofern diese nicht ohnehin schon im Einsatz ist.
Wenn man einen Test für eine API-Funktion schreiben will, dann kann man sich entweder auf die Korrektheit und Vollständigkeit der Dokumentation verlassen und das dort spezifizierte im Test validieren oder aber man betrachtet die Kommentare zur Funktion. Ich habe etwas in den Kommentardateien mit der Endung *.c geblättert und da war es dann:
...
if (status & SERVER_QUERY_NO_GOOD_INDEX_USED) {
...
if (status & SERVER_QUERY_NO_INDEX_USED) {
...
Das MySQL-Referenzhandbuch erwähnt das Weihnachtsgeschenk eines Entwicklers an den anderen nur beiläufig in den Changes zur Version 4.1.2 auf http://dev.mysql.com/doc/refman/4.1/en/news-4-1-2.html:
C API enhancement: SERVER_QUERY_NO_INDEX_USED and SERVER_QUERY_NO_GOOD_INDEX_USED flags are now set in the server_status field of the MYSQL structure. It is these flags that make the query to be logged as slow if mysqld was started with –log-slow-queries –log-queries-not-using-indexes.
PHP-Anwender, die den MySQL Server mit den Optionen --log-slow-queries und --log-queries-not-using-indexes betreiben, können mittels mysqli_report(MYSQLI_REPORT_INDEX) bei Ausführung einer Anfrage, die keine bzw. nur schlechte Indizies verwendet, diese gleich auf dem Bildschirm ausgeben lassen. Wie immer bei Loginformationen: bitte darauf achten, daß nur die Leser Zugriff auf die Protokollinformationen erhalten, die keinen Unfug damit treiben werden. Ein “No index used in query/prepared statement UPDATE myuser SET password = ‘geheim’ WHERE username = ‘nixnutz'” wird von manchen Zeitgenossen zurecht als Einladung zum Spielen verstanden. Ob der MySQL Server mit den notwendigen Optionen gestartet wurde, verrät SHOW VARIABLES:
mysql> show variables like "%log%quer%"; +-------------------------------+-------+ | Variable_name | Value | +-------------------------------+-------+ | log_queries_not_using_indexes | ON | | log_slow_queries | ON | +-------------------------------+-------+ 2 rows in set (0.04 sec)
Leser, die nicht mit PHP arbeiten sondern den Server über die C-API ansprechen, finden untenstehend ein kleines Beispiel, welches die Anwendung der Flags demonstriert. Das Beispiel erhebt keinen Anspruch auf Vollständigkeit oder Korrektheit – dies ist ein Blog, nicht das MySQL Referenzhandbuch. Außerdem habe ich nie Anwendungen in C geschrieben also nicht den Stil nachahmen!
gcc -o mysqlundoc `/usr/local/mysql/bin/mysql_config --cflags` mysql_undoc.c `/usr/local/mysql/bin/mysql_config --libs`
#include <stdio.h>
#include <stdlib.h>
#include <assert.h>
#include <string.h>
#include <time.h>
#include <mysql.h>
#define NUM_ROWS 100
int bail(MYSQL *mysql, char* msg);
int setup_test(MYSQL *mysql, unsigned int num_rows);
unsigned int random_string(char* buf, unsigned int maxlen);
unsigned int random_uint(unsigned int min, unsigned int max);
int main(void)
{
MYSQL *mysql = mysql_init(NULL);
MYSQL_RES *res = NULL;
MYSQL_ROW row;
unsigned int i = 0;
unsigned int j = 0;
unsigned int num_fields = 0;
unsigned long *lengths;
if (mysql_server_init(-1, NULL, NULL))
return EXIT_FAILURE;
printf("\n\nStarting ...\n");
if (!mysql_real_connect(mysql, "localhost", "root", "root", "test", 0, NULL, 0))
return bail(mysql, "Cannot connect");
printf("... setting up test with %d rows\n", setup_test(mysql, NUM_ROWS));
if (mysql_query(mysql, "SELECT "
"t1.id, t2.label "
"FROM "
"test t1, test t2 "
"WHERE "
"t1.id != t2.id AND "
"t1.label > 'A' AND "
"t1.data2 < 1000 "
"LIMIT 10"))
return bail(mysql, "SELECT failed.");
if (mysql->server_status & SERVER_QUERY_NO_INDEX_USED)
printf("\n... WARNING: no index used by query!\n\n");
if (mysql->server_status & SERVER_QUERY_NO_GOOD_INDEX_USED)
printf("\n... WARNING: no good index used by query!\n\n");
if (!(res = mysql_use_result(mysql)))
return bail(mysql, "Cannot use result.");
printf("... fetching first 10 matching rows, if any..\n");
num_fields = mysql_num_fields(res);
while ((row = mysql_fetch_row(res))) {
lengths = mysql_fetch_lengths(res);
printf("... row %02d = ", ++j);
for (i = 0; i < num_fields; i++) {
printf("[%.*s] ", (int)lengths[i], row[i] ? row[i] : "NULL");
}
printf("\n");
}
mysql_free_result(res);
printf("... done!\n");
mysql_close(mysql);
mysql_server_end();
printf("\n\n");
return EXIT_SUCCESS;
}
int setup_test(MYSQL* mysql, unsigned int num_rows) {
MYSQL_STMT *stmt;
MYSQL_BIND bind[3];
unsigned int i;
unsigned long label_len, data1_len;
unsigned int data2;
char label[33];
char data1[256];
const char insert[] = "INSERT INTO test(label, data1, data2) VALUES (?, ?, ?)";
if (mysql_query(mysql, "DROP TABLE IF EXISTS test"))
return 0;
if (mysql_query(mysql, "CREATE TABLE test(id INT NOT NULL AUTO_INCREMENT, "
"label VARCHAR(32), data1 VARCHAR(255), data2 INT UNSIGNED, "
"PRIMARY KEY(id))"))
return 0;
if (!(stmt = mysql_stmt_init(mysql)))
return 0;
if (mysql_stmt_prepare(stmt, insert, (unsigned long)strlen(insert))) {
mysql_stmt_close(stmt);
return 0;
}
if (mysql_stmt_param_count(stmt) != 3) {
mysql_stmt_close(stmt);
return 0;
}
memset(bind, 0, sizeof(bind));
bind[0].buffer_type = MYSQL_TYPE_STRING;
bind[0].buffer = (char *)label;
bind[0].is_null = (my_bool *)0;
bind[0].length = &label_len;
bind[1].buffer_type = MYSQL_TYPE_STRING;
bind[1].buffer = (char *)data1;
bind[1].is_null = (my_bool*)0;
bind[1].length = &data1_len;
bind[2].buffer_type = MYSQL_TYPE_LONG;
bind[2].buffer = (char *)&data2;
bind[2].is_null = (my_bool*)0;
bind[2].length = 0;
bind[2].is_unsigned = (my_bool)1;
if (mysql_stmt_bind_param(stmt, bind)) {
mysql_stmt_close(stmt);
return 0;
}
srand(time(NULL));
for (i = 0; i < num_rows; i++) {
label_len = (unsigned long)random_string(label, 33);
data1_len = (unsigned long)random_string(data1, 256);
data2 = random_uint(0, 10000);
if (mysql_stmt_execute(stmt)) {
mysql_stmt_close(stmt);
return 0;
}
}
if (mysql_stmt_close(stmt))
return 0;
return num_rows;
}
unsigned int random_uint(unsigned int min, unsigned int max) {
assert(min < max);
assert(max < RAND_MAX);
return min + (int)((double)(max - min) * rand() / RAND_MAX + 1.0);
}
unsigned int random_string(char* buf, unsigned int maxlen) {
unsigned int i, len;
assert(maxlen > 0);
len = 1 + (int)((double)(maxlen - 1) * rand() / RAND_MAX + 1.0);
for (i = 0; i < len; i++)
sprintf(&buf[i], "%c", random_uint(65, 90));
buf[len] = '\0';
return len;
}
int bail(MYSQL *mysql, char* msg) {
printf("\n\n%s\n", msg);
printf("[%d] %s\n\n", mysql_errno(mysql), mysql_error(mysql));
mysql_close(mysql);
return EXIT_FAILURE;
}