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 biete SQL Schulungen, Optimierung und Beratung an. Auch der Kauf meines Buches „SQL Performance Explained“ (ab €9,95) unterstützt meine Arbeit an dieser Webseite.

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 |
+------+----------+---------+-------+------+-------------+
Vorherige SeiteNächste Seite

Du kannst nicht alles an einem Tag lernen. Abonniere den Newsletter via E-Mail, Twitter oder RSS um sukzessive aufzuholen. Und sieh dir auch modern-sql.com an.

Über den Autor

Foto von Markus Winand

Markus Winand gibt auf modern-sql.com Einblick in SQL und zeigt, wie es von verschiedenen Systemen unterstützt wird. Zuvor machte er use-the-index-luke.com, was er noch immer wartet. 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»

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