von Markus Winand.

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.

Hinweis in eigener Sache

Ich lebe von SQL-Schulungen, SQL-Tuning und Beratung sowie dem Verkauf meines Buches „SQL Performance Explained“. Mehr auf winand.at.

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 |
+------+----------+---------+-------+------+-------------+

Über den Autor

Foto von Markus Winand

Markus Winand ist der SQL Renaissance Botschafter auf der Mission, Entwickler auf die Evolution von SQL im 21. Jahrhundert aufmerksam zu machen. Markus kann als Trainer, Sprecher und Berater auf winand.at engagiert werden.

Sein Buch kaufen

Titelbild von „SQL Performance Explained“: Eichhörnchen läuft durchs Grass

Die Essenz: SQL-Tuning auf 200 Seiten

Jetzt Kaufen
(Taschenbuch und/oder PDF)

Sein Training

Markus verwandelt veraltetes SQL-92-Wissen in solides und zeitgemäßes SQL-Know-how

Erfahren Sie mehr»

Nicht mit OFFSET blättern

Mehr info

Besuche meine Schwester-Seite!Seit SQL-92 hat sich einiges getan!

Die Use The Index, Luke! Tasse

Aufkleber, Bierdeckel, Bücher und Kaffeetassen. Alles was man beim Lernen braucht!

Zum Shop

Mit Markus Winand verbinden

Markus Winand auf LinkedInMarkus Winand auf XINGMarkus Winand auf Twitter
„Use The Index, Luke!“ von Markus Winand ist unter einer Creative Commons Attribution-Noncommercial-No Derivative Works 3.0 Unported License lizenziert.
Impressum | Kontakt | KEINE GEWÄHR | Handelsmarken | Datenschutz und DSGVO | CC-BY-NC-ND 3.0 Lizenz