Best Practices für den MYSQL-Index

Sie haben eine kluge Wahl getroffen

Das Verständnis von Indizes ist sowohl für Entwickler als auch für Datenbankadministratoren äußerst wichtig.

Schlechte Indizes sind für einen Großteil der Produktprobleme verantwortlich.

Die Indizierung ist nicht der Fall ein fortgeschrittenes Problem

MySQL-Indexübersicht

Indizes verstehen

Erstellen Sie den besten Index für Ihre Anwendung

Erkennen Sie die Einschränkungen von MySQL

Eine kurze Einführung in Indizes

Wozu dienen Indizes?

Beschleunigen Sie das Lesen von Daten aus der Datenbank

Erzwingen Sie Einschränkungen (eindeutiger Index UNIQUE, Fremdschlüssel FOREIGN KEY)

Die Abfrageseite kann ohne Index normal ausgeführt werden

Aber das kann lange dauern

Indextypen, von denen Sie vielleicht schon gehört haben

BTREE-Index – der Hauptindex Indextyp in MySQL

RTREE-Index – nur von MyISAM unterstützt, verwendet in GIS

HASH-Index – MEMORY, NDB unterstützt

BITMAP-Index – MySQL unterstützt nicht

VOLLTEXT-Index – MyISAM, Innodb (unterstützt von MySQL 5.6 und höher)

BTREE-ähnliche Indexfamilie

Es gibt viele verschiedene Implementierungen

in Accelable, die dieselben Attribute teilen im Betrieb

Speicher macht das Leben besser als eine Festplatte

B-Tree wird normalerweise für die Festplattenspeicherung verwendet

Daten werden in Blattknoten gespeichert

B Baumbeispiel

MyISAM, Innodb-Indexvergleich

MyISAM

Der Datenzeiger zeigt auf den physischen Speicherort in der Datendatei

Alle Indizes sind gleich (zeigen auf den physischen Standort))

Innodb

Primärschlüsselindex (explizit oder implizit) – Speichern Sie Daten direkt im Blattknoten des Index, anstatt ein Zeiger

Sekundärer Index – speichert den Wert des Primärschlüsselindex als Datenzeiger

Für welche Operationen kann der BTREE-Index verwendet werden?

Fragen Sie alle Datensätze mit KEY ab =5 (Punktabfrage)

Alle Datensätze von KEY>5 (offen und geschlossen) abfragen

Alle Datensätze von 5

Nicht anwendbar um: Den letzten SCHLÜSSEL abzufragen. Alle Datensätze mit einer Nummer gleich 0

, da dies nicht als Bereichsabfrageoperation definiert werden kann

Zeichenindex

Dies unterscheidet sich nicht (von numerisch). Wert)... wirklich

Sortierung ist eine Sortierregel, die für Zeichenfolgen definiert ist

wie zum Beispiel: „AAAA“

Präfix LIKE-Abfrage ist ein spezieller Bereich Abfrage

LIKE "ABC %" bedeutet:

"ABC[Minimalwert]"

LIKE "�C" Nicht möglich Indexabfrage verwenden

Der gemeinsame Index

wird wie folgt sortiert, indem die erste Spalte, dann die zweite Spalte, die dritte Spalte usw. verglichen werden, z. B.:

KEY(col1,col2,col3)

(1,2,3)

Verwenden Sie einen BTREE-Index anstelle eines separaten BTREE-Index für jede Ebene

Overhead des Index

Indizes sind teuer, fügen Sie keine redundanten Indizes hinzu

In den meisten Fällen ist die Erweiterung eines Indexes besser als das Hinzufügen eines neuen

Schreiben – Das Aktualisieren eines Index ist oft ein Datenbank-Schreibvorgang. Hauptaufwand

Lesen – erfordert zusätzlichen Speicherplatz auf der Festplatte und im Arbeitsspeicher bei der Abfrageoptimierung

Auswirkungen der Indexkosten

Langer Primärschlüsselindex (Innodb) – macht alle entsprechenden Sekundärindizes länger und langsamer

„Zufälliger“ Primärschlüsselindex (Innodb) – Einfügungen führen zu großen Seitenaufteilungen

Längere Indizes sind im Allgemeinen langsamer

Index mit Einfügung in zufälliger Reihenfolge – SHA1('Passwort')

Indizes mit geringer Diskriminierung sind minderwertig – Indizes, die auf Geschlechtsfeldern basieren

Verwandte Indizes sind kostengünstiger – insert_time bezieht sich auf die Auto-Inkrement-ID

Innodb-Tabellenindex

Daten werden nach Primärschlüssel aggregiert

Wählen Sie das beste Feld als Primärschlüssel aus

Zum Beispiel ist die Kommentartabelle – (POST_ID,COMMENT_ID) eine gute Wahl als Primärschlüssel, sodass Kommentare für einen einzelnen Beitrag gruppiert werden können

oder mit einem einzigen BIGINT (Feld) „gepackt“ werden

Der Primärschlüssel ist implizit an alle Indizes angehängt

KEY (A) ist im Wesentlichen KEY (A,ID)

der den Index abdeckt, was für die Sortierung von Vorteil ist

Wie MySQL Indizes verwendet

Abfragen

Sortieren

Vermeiden Sie das Lesen von Daten (nur Index lesen)

Andere spezielle Optimierungen

Verwenden Index für Abfrage

SELECT * FROM EMPLOYEES WHERELAST_NAME="Smith"

Dies ist ein typischer Index KEY(LAST_NAME)

Sie können einen zusammengesetzten Index verwenden

SELECT * FROM EMPLOYEES WHERELAST_NAME="Smith" AND DEPT="Accounting"

Der Index KEY(DEPT,LAST_NAME) wird verwendet

Der zusammengesetzte Index ist komplizierter

Index (A,B,C) - Feldreihenfolgeproblem

Die folgende Situation wird für die Abfrage verwendet (vollständiger Zustand):

A>5

A=5 AND B>6

A=5 AND B=6 AND C=7

A=5 UND B IN (2,3) UND C>5

Die folgenden Bedingungen verwenden den Index nicht

B>5 – Bedingung ohne A vor B-Feld

B=6 UND C=7 – Die Bedingung hat kein A vor den B- und C-Feldern

Verwenden Sie einen Teil des Index in den folgenden Situationen

A>5 AND B=2 – Erste Bereichsabfrage von Feld A, was dazu führt, dass nur der Teil von Feld A im Index verwendet wird

A=5 AND B>6 AND C=2 – Bereichsabfrage von Feld B, was dazu führt, dass nur der Teil von Feld A im Index verwendet wird und B-Felder

Die erste Regel des MySQL-Optimierers

In einem zusammengesetzten Index stoppt MySQL, wenn es auf a trifft Rückgabeabfrage (,BETWEEN) Brechen Sie die Verwendung des verbleibenden Teils (Index) ab, verwenden Sie jedoch die „Bereichsabfrage“ von IN(...), um den Index (mehr Teile) weiter rechts zu verwenden

Sortieren nach dem verwendeten Index

SELECT * FROM PLAYERS ORDER BY SCOREDESC LIMIT 10

wird den Index KEY(SCORE) verwenden

Wenn der Index nicht verwendet wird, wird ausgeführt eine sehr teure „Filesort“-Operation (externalsort)

oft verwendet. Index für Abfrage kombinieren

SELECT * FROM PLAYERS WHERE COUNTRY=“US“ ORDER BY SCORE DESC LIMIT 10

Die beste Wahl ist KEY(COUNTRY,SCORE)

Effiziente Sortierung Der gemeinsame Index

wird eingeschränkter

KEY(A,B)

Die In den folgenden Situationen wird der Index zum Sortieren verwendet

ORDER BY A – Sortieren Sie das erste Feld des Index

A=5 ORDER BY B – Führen Sie eine Punktabfrage für das erste Feld durch und sortieren Sie das zweite Feld

ORDER BY A DESC, B DESC – Sortieren Sie die beiden Felder in der gleichen Reihenfolge

A>5 ORDER BY A – Führen Sie eine Bereichsabfrage für das erste Feld durch und sortieren Sie das erste Feld

In den folgenden Situationen wird der Index nicht zum Sortieren verwendet

ORDER BY B – Sortieren Sie das zweite Feld (das erste Feld wird nicht verwendet)

A>5 ORDER BY B – Ausführen eine Bereichsabfrage für das erste Feld, sortieren Sie das zweite Feld

A IN(1,2) ORDER BY B – führen Sie eine IN-Abfrage für das erste Feld durch und sortieren Sie das zweite Feld

ORDER BY A ASC, B DESC – Führen Sie eine IN-Abfrage für die beiden Felder aus. Sortierung in unterschiedlicher Reihenfolge

MySQL verwendet Indexsortierregeln

Sie können zwei Felder nicht in unterschiedlicher Reihenfolge sortieren

Nur Punktabfragen können für Felder verwendet werden, die nicht Teil des ORDER BY-Teils (=) sind – In diesem Fall funktioniert auch IN() nicht

Vermeiden Sie das Lesen der Daten (lesen Sie nur den Index)

„Abdeckender Index“ – bezieht sich hier auf den Index, der für eine bestimmte Abfrage geeignet ist, und nicht auf einen Indextyp

Liest nur den Index, nicht die Daten

SELECT STATUS AUS BESTELLUNGEN WHERECUSTOMER_ID=123

KEY(CUSTOMER_ID,STATUS)

Indizes sind normalerweise kleiner als die Daten selbst

(Index)-Lesevorgänge sind sequentieller – Lesedatenzeiger normalerweise zufällig

Min/Max-Optimierung

Indizes können dabei helfen, statistische Funktionen wie MIN()/MAX() zu optimieren – enthalten aber nur Folgendes:

SELECT MAX(ID) FROM TBL;

MAX( GEHALT) AUS EMPLOYEEGROUP NACH DEPT_ID AUSWÄHLEN

wird von KEY(DEPT_ID,SALARY) profitieren

"Index für Gruppierung verwenden"

Verwendung des Index bei gemeinsamen Tabellenabfragen

MySQL verwendet „Nested Loops“, um gemeinsame Tabellenabfragen durchzuführen

SELECT * FROM POSTS,COMMENTS WHEREAUTHOR="Peter" AND COMMENTS.POST_ID=POSTS .ID

Durchsuchen Sie die Tabelle POSTS, um alle Beiträge mit zusammengesetzten Bedingungen abzufragen.

Schleifen Sie Beiträge und finden Sie alle Kommentare zu jedem Beitrag in der Tabelle KOMMENTARE.

Es ist sehr wichtig, den oberen Index zu verwenden für jede zugehörige Tabelle (zugehöriges Feld) Es ist wichtig

Der Index ist nur für das abgefragte Feld erforderlich – der Index des Feldes POSTS.ID wird in dieser Abfrage nicht verwendet

Alle neu gestalten Indizes, die nicht gut sind Join-Abfrage

Mehrere Indizes verwenden

MySQL kann mehr als einen Index verwenden

"Indexzusammenführung"

SELECT * FROM TBL WHERE A=5 UND B=6– Sie können Index KEY(A) bzw. KEY(B) verwenden

Index KEY(A,B) ist eine bessere Wahl

SELECT * FROM TBL WHERE A=5 ODER B=6– Zwei Indizes werden gleichzeitig verwendet

Index KEY(A,B) kann in dieser Abfrage nicht verwendet werden

Präfixindex

Sie können das hinzufügen letzter linker Präfixindex

ALTER TABLE TITLE ADD KEY(TITLE(20));

Felder vom Typ BLOB/TEXT müssen indiziert werden

kann den Speicherplatzverbrauch erheblich reduzieren

Kann nicht zum Abdecken von Indizes verwendet werden

Auswahl der Präfixlänge wird zum Problem

Auswahl der Präfixlänge

Das Präfix sollte ausreichend eindeutig sein

Vergleichen Sie eindeutiges Präfix und eindeutigen Feldwert

mysql> ) S. 20 aus dem Titel;

1 Zeile im Satz (44,19 Sek.)

Auf Ausreißer prüfen

Stellen Sie sicher, dass es keine Viele gibt Datensätze verwenden das gleiche Präfix

Der am häufigsten verwendete Titlemysql> select count(*) cnt, title tl from title group by tl order by cnt desc limit 3;

3 Zeilen im Satz (27,49 Sek.)

Das am häufigsten verwendete Titelpräfix mysql> select count(*) cnt, left(title,20) tl from title group by tl order by cnt desc limit 3;

3 Zeilen im Satz (33,23 Sek.)

Wie wählt MySQL aus, welcher Index verwendet werden soll?

Dynamische Auswahl für jede Abfrage – Konstanten im Abfragetext sind wichtig

Bewerten Sie die Anzahl der abzufragenden Zeilen. Für einen bestimmten Index „tauchen“ Sie in die Tabelle ein

Wenn ( dive) ist nicht möglich. Zeilen, verwenden Sie „Kardinalität“ für Statistiken – dies wird aktualisiert, wenn Sie ANALYZE TABLE ausführen

Mehr Optionen für die Indizierung

Nicht nur Minimierung der Anzahl gescannter Zeilen

Viele andere Heuristiken (Versuche) und Hacks – Primärschlüssel sind für Innodb sehr wichtig

Abgedeckte Indexvorteile

Vollständiger Tabellenscan ist schneller, wenn alle gleich sind (dieser Satz ist nicht sehr klar)

Wir können auch Indizes zum Sortieren verwenden

Hinweise

Überprüfen Sie den tatsächlich von MYSQL verwendeten Ausführungsplan

Beachten Sie, dass er basierend auf Konstanten und dynamisch geändert werden kann data

Verwenden Sie EXPLAIN

EXPLAIN ist ein gutes Tool, um zu sehen, wie MYSQL

mysql> explore select max(season_nr) from title group by Production_year;

http://dev.mysql.com/doc/refm...

Denken Sie daran, dass die tatsächliche Abfrage vom Ausführungsplan abweichen kann

1 Zeile im Satz (0,01 Sek.)

MySQL Explain 101

"Typ" Vom Besten zum Schlechtesten sortiert: – system,const,eq_ref,ref,range,index,ALL

Beobachten Sie „rows“ – größere Werte bedeuten langsamere Abfragen

Überprüfen Sie „key_len“ – zeigt an, welche Teile des Index tatsächlich verwendet werden

Beobachten Sie „Extra“

Index verwenden – gut

Filesort verwenden, temporär verwenden – schlecht

Indizierungsstrategie

Indizieren Ihrer leistungskritischen Abfragesätze – eine ganzheitliche Sicht, anstatt sie einzeln zu betrachten Eins

Es ist am besten, Indizes für alle Abfragebedingungen und Join-Tabellenbedingungen zu verwenden – zumindest der differenzierteste Teil ist

Generell gilt: Wenn möglich, erweitern Sie den Index, anstatt einen neuen zu erstellen Index

Denken Sie daran, die Auswirkungen auf die Leistung zu überprüfen, wenn Sie Änderungen vornehmen

Beispiel einer Indexstrategie

Indizes in einer Reihenfolge erstellen, die mehr Abfragen unterstützen kann

SELECT * FROM TBL WHERE A=5 AND B=6

SELECT * FROM TBL WHERE A>5 AND B=6– KEY(B,A) ist eine bessere Wahl für beide Abfragen

Alle eingeben Felder, bei denen es sich um Punktabfragen am Anfang des Index handelt

Fügen Sie keine Indizes für nicht leistungskritische Abfragen hinzu – zu viele Indizes verlangsamen MYSQL

Trick Nr. 1: Aufzählungsbereich

KEY (A,B)

SELECT * FROM TBL WHERE A BETWEEN 2AND 4 AND B=5

verwendet nur den ersten Teil des Indexfelds

SELECT * FROM TBL WHERE A IN (2,3,4) ANDB=5

Beide Feldteile des Index verwenden

Trick Nr. 2: Fügen Sie eine gefälschte Bedingung hinzu

KEY (GENDER,CITY)

SELECT * FROM PEOPLE WHERE CITY=“NEWYORK“

Es wird überhaupt kein Index verwendet

SELECT * FROM PEOPLE WHERE GENDER IN(" M","F") AND CITY="NEW YORK"

Wird den Index verwenden

Dieser Trick kann bei Feldern mit geringer Unterscheidung gut sein. Verwendung von

Geschlecht, Status , Boolesche Typen usw.

Trick Nr. 3: Virtuelle und reale Dateisortierung

KEY(A,B)

SELECT * FROM TBL WHERE A IN (1,2) ORDER BY B LIMIT 5;

Kann nicht nach Index sortiert werden

(SELECT FROM TBL WHERE A=1 ORDER BY B LIMIT 5) UNION ALL (SELECT FROM TBL WHERE A=2 ORDER BY B LIMIT 5) ORDER BY B LIMIT 5;

verwendet den Index und „filesort“ wird nur für Datensätze mit nicht mehr als 10 Zeilen verwendet