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

Wenn Dir dieser Artikel gefällt, könnte mein Buch SQL Performance Explained oder mein Training auch etwas für Dich sein.

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 lehrt effizientes SQL – inhouse und online. Er minimiert die Entwicklungszeit durch modernes SQL und optimiert die Laufzeit durch schlaue Indizierung – dazu hat er auch das Buch SQL Performance Explained veröffentlicht.

Sein Buch bei Amazon kaufen

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

Die Essenz: SQL-Tuning auf 200 Seiten

Bei Amazon kaufen
(Taschenbuch)

Taschenbuch und PDF auch auf Markus' Webseite erhältlich.

Holen Sie sich Markus

…für ein Training ins Büro.

Sein beliebtes Training stimmt Entwickler auf SQL Performance ein.

Erfahren Sie mehr»

„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 | CC-BY-NC-ND 3.0 Lizenz