MySQL Zugriffs- und Filterprädikate unterscheiden

Die MySQL Datenbank kann where-Bedingungen (Prädikate) auf drei ver­schiedene Arten anwenden:

Zugriffsprädikat (key_len)

Die Zugriffsprädikate bestimmen den Anfangs- und End-Punkt beim Verfolgen der Blattknote-Liste.

Index-Filterprädikat („Using index condition“, ab Version 5.6)

Index-Filterprädikate werden während des Durchsuchens der Blatt­kno­ten-Liste angewandt. Sie haben aber keinen Einfluss auf die Start- und Stopp-Bedingungen und grenzen den durchsuchten Indexbereich nicht ein.

Filterprädikat auf Tabellenebene („Using where“)

Prädikate, die sich auf Spalten beziehen, die nicht im Index sind, können erst auf Tabellenebene berücksichtigt werden. Dafür muss zuerst ein Tabellenzugriff durchgeführt werden.

MySQL-Ausführungspläne zeigen nur, welche Prädikatstypen vorkommen, nicht aber, für welche Bedingungen sie verwendet werden.

Im folgenden Beispiel kann die where-Klausel vollständig als Zugriffsprä­dikat genutzt werden:

CREATE TABLE demo (
   id1 NUMERIC
 , id2 NUMERIC
 , id3 NUMERIC
 , val NUMERIC)

INSERT INTO demo VALUES (1,1,1,1)

INSERT INTO demo VALUES (2,2,2,2)

CREATE INDEX demo_idx
          ON demo
             (id1, id2, id3)

EXPLAIN
 SELECT * 
   FROM demo
  WHERE id1=1
    AND id2=1

+------+----------+---------+-------------+------+-------+
| type | key      | key_len | ref         | rows | Extra |
+------+----------+---------+-------------+------+-------+
| ref  | demo_idx | 12      | const,const |    1 |       |
+------+----------+---------+-------------+------+-------+

Es wird weder „Using where“ noch „Using index condition“ in der Extra-Spalte angezeigt. Da der Index aber benutzt wird (type=ref, key=demo_idx), kann man davon ausgehen, dass die ganze where-Klausel als Zugriffsprä­dikat benutzt wird.

In der ref-Spalte wird auch angezeigt, dass zwei Werte beim Indexzugriff verwendet werden – in diesem Fall zwei konstanten. Eine weitere Möglichkeit die Zugriffsmethode zu prüfen gibt der key_len-Wert: Dazu muss man „nur“ wissen, wie viel Speicherplatz die indizierten Spalten jeweils belegen (siehe „Data Type Storage Requirements“ im MySQL-Referenzhandbuch). Ohne NOT NULL-Deklaration benötigt jede Spalte noch ein Byte zusätzlich. Im konkreten Fall belegt eine NUMERIC-Spalte 6 Byte. Eine Schlüssellänge von 12 bedeutet, dass die ersten beiden Spalten als Zugriffsprädikat benutzt werden.

Filtert man mit der ID3- statt der ID2-Spalte, verwendet MySQL (ab Version 5.6) ein Index-Filterprädikat („Using index condition“):

EXPLAIN
 SELECT * 
   FROM demo
  WHERE id1=1
    AND id3=1

+------+----------+---------+-------+------+-----------------------+
| type | key      | key_len | ref   | rows | Extra                 |
+------+----------+---------+-------+------+-----------------------+
| ref  | demo_idx | 6       | const |    1 | Using index condition |
+------+----------+---------+-------+------+-----------------------+

In diesem Fall weist die Schlüssellänge (key_len) und die ref-Spalte darauf hin, dass nur eine Spalte als Zugriffsprädikat genutzt wird.

Vor Version 5.6 wurde dafür ein Filterprädikat auf Tabellenebene ver­wendet, erkennbar am „Using where“ in der Extra-Spalte:

+------+----------+---------+-------+------+-------------+
| type | key      | key_len | ref   | rows | Extra       |
+------+----------+---------+-------+------+-------------+
| ref  | demo_idx | 6       | const |    1 | Using where |
+------+----------+---------+-------+------+-------------+