Design und Verwendung des MySQL-Index

Indexdesign und -verwendung
5.1 MySQL-Indexübersicht
Alle MySQL-Spaltentypen können indiziert werden. Die Verwendung von Indizes für verwandte Spalten ist die beste Möglichkeit, die Leistung von SELECT-Vorgängen zu verbessern. Definieren Sie die maximale Anzahl von Indizes und die maximale Indexlänge für jede Tabelle entsprechend der Speicher-Engine. Alle Speicher-Engines unterstützen mindestens 16 Indizes pro Tabelle mit einer Gesamtindexlänge von mindestens 256 Byte. Die meisten Speicher-Engines haben höhere Grenzwerte.
In MySQL 5.1 können Präfixe für MyISAM- und InnoDB-Tabellen bis zu 1000 Byte lang sein. Beachten Sie, dass die Grenze eines Präfixes in Bytes gemessen werden sollte, wohingegen die Präfixlänge in der CREATE TABLE-Anweisung als Anzahl von Zeichen interpretiert wird. Berücksichtigen Sie dies unbedingt, wenn Sie eine Präfixlänge für Spalten angeben, die Multibyte-Zeichensätze verwenden.
Es können auch VOLLTEXT-Indizes erstellt werden. Der Index kann für die Volltextsuche verwendet werden. Nur die MyISAM-Speicher-Engine unterstützt FULLTEXT-Indizes und nur für CHAR-, VARCHAR- und TEXT-Spalten. Indizes werden immer für die gesamte Spalte durchgeführt. Teilindizes (Präfixe) werden nicht unterstützt. Für räumliche Spaltentypen können auch Indizes erstellt werden. Nur die MyISAM-Speicher-Engine unterstützt räumliche Typen. Räumliche Indizes verwenden R-Bäume. Standardmäßig verwendet die Speicher-Engine MEMORY (HEAP) Hash-Indizes, aber auch B-Tree-Indizes werden unterstützt.
5.2 Prinzipien des Indexdesigns
1. Die zu durchsuchende Indexspalte ist nicht unbedingt die auszuwählende Spalte. Mit anderen Worten: Die am besten geeigneten Spalten für die Indizierung sind die Spalten, die in der WHERE-Klausel oder die in der Join-Klausel angegebenen Spalten erscheinen, und nicht die Spalten, die in der Auswahlliste nach dem Schlüsselwort SELECT erscheinen.
2. Verwenden Sie einen eindeutigen Index. Betrachten Sie die Verteilung der Werte in einer Spalte. Indizes funktionieren am besten für Spalten mit eindeutigen Werten und am schlechtesten für Spalten mit mehreren doppelten Werten. Beispielsweise weist die Spalte, die das Alter enthält, unterschiedliche Werte auf, was die Unterscheidung zwischen Zeilen erleichtert.
Die zum Aufzeichnen des Geschlechts verwendete Spalte enthält nur „M“ und „F“, daher ist die Indizierung dieser Spalte von geringem Nutzen (unabhängig davon, welcher Wert gesucht wird, wird etwa die Hälfte der Zeilen erhalten)
3 Kurzer Index. Bei der Indizierung in eine Zeichenfolge sollten Sie nach Möglichkeit eine Präfixlänge angeben.
Wenn Sie beispielsweise eine CHAR(200)-Spalte haben, indizieren Sie nicht die gesamte Spalte, wenn die meisten Werte innerhalb der ersten 10 oder 20 Zeichen eindeutig sind. Die Indizierung der ersten 10 oder 20 Zeichen kann viel Indexplatz sparen und Abfragen möglicherweise beschleunigen. Kleinere Indizes erfordern weniger Festplatten-E/A und kürzere Werte sind im Vergleich schneller.
Noch wichtiger ist, dass bei kürzeren Schlüsselwerten die Blöcke im Index-Cache mehr Schlüsselwerte enthalten können, sodass MySQL auch mehr Werte im Speicher halten kann. Dies erhöht die Wahrscheinlichkeit, die Zeile zu finden, ohne größere Blöcke im Index zu lesen.
(Natürlich sollte etwas gesunder Menschenverstand verwendet werden. Die Indizierung nur nach dem ersten Zeichen eines Spaltenwerts wird wahrscheinlich keinen großen Nutzen bringen, da dieser Index nicht viele verschiedene Werte enthält.)
4. Präfix ganz links ausnutzen. Wenn Sie einen Index für n Spalten erstellen, erstellen Sie tatsächlich n Indizes, die MySQL verwenden kann.
Ein mehrspaltiger Index kann als mehrere Indizes funktionieren, da der Spaltensatz ganz links im Index zum Abgleichen von Zeilen verwendet werden kann. Ein solcher Satz von Spalten wird als Präfix ganz links bezeichnet. (Dies unterscheidet sich von der Indizierung des Präfixes einer Spalte, bei der die ersten n Zeichen der Spalte als Indexwert verwendet werden.)
5. Denken Sie nicht, dass es falsch ist, für alles Indizes zu verwenden. Jeder zusätzliche Index beansprucht zusätzlichen Speicherplatz und verringert die Leistung von Schreibvorgängen, die wir bereits eingeführt haben. Wenn der Inhalt der Tabelle geändert wird, muss der Index aktualisiert und manchmal neu erstellt werden. Daher gilt: Je mehr Indizes, desto länger dauert es
. Wenn Sie einen Index haben, der selten oder nie verwendet wird, werden Änderungen an der Tabelle unnötig verlangsamt.
Außerdem muss MySQL bei der Erstellung eines Ausführungsplans jeden Index berücksichtigen, was ebenfalls Zeit kostet. Durch die Erstellung redundanter Indizes entsteht mehr Aufwand für die Abfrageoptimierung. Zu viele Indizes können MySQL auch daran hindern, den besten zu verwendenden Index auszuwählen. Das Beibehalten nur der erforderlichen Indizes erleichtert die Abfrageoptimierung. Wenn Sie einen Index zu einer bereits indizierten Tabelle hinzufügen möchten, sollten Sie überlegen, ob der hinzuzufügende Index der Index ganz links eines vorhandenen mehrspaltigen Index ist. Wenn ja, machen Sie sich nicht die Mühe, diesen Index hinzuzufügen, da er bereits vorhanden ist.
6. Berücksichtigen Sie die Arten von Vergleichen, die auf Spalten durchgeführt werden. Indizes können mit den Operationen „ <“, „ < =“, „ > =“, „ >“ verwendet werden. Indizes werden auch in LIKE-Operationen verwendet, wenn das Muster ein Literalpräfix hat. Wenn eine Spalte nur für andere Arten von Operationen verwendet wird (z. B. STRCMP()), hat die Indizierung keinen Wert.
5.3 Btree-Index und Hash-Index
Bei BTREE- und HASH-Indizes entspricht die Vergleichsbeziehung zwischen Schlüsselelementen und konstanten Werten bei Verwendung der Operatoren =, <=>, IN, IS NULL oder IS NOT NULL zu einem Geltungsbereichsbedingungen. Hash-Indizes haben einige zusätzliche Funktionen: Sie werden nur für Gleichheitsvergleiche mit den Operatoren = oder <=> verwendet (aber schnell). Der Optimierer kann keine Hash-Indizes verwenden, um ORDER BY-Vorgänge zu beschleunigen.
(Dieser Indextyp kann nicht für die Suche nach dem nächsten Eintrag in Folge verwendet werden.) MySQL kann nicht ungefähr bestimmen, wie viele Zeilen zwischen zwei Werten liegen (dies wird vom Bereichsoptimierer verwendet, um zu bestimmen, welcher Index verwendet werden soll). Wenn Sie eine MyISAM-Tabelle in eine Hash-indizierte MEMORY-Tabelle ändern, sind einige Abfragen betroffen. Zum Durchsuchen einer Zeile kann nur das gesamte Schlüsselwort verwendet werden. (Bei einem B-Tree-Index kann das Präfix ganz links eines beliebigen Schlüssels verwendet werden, um die Zeile zu finden.)
Für den BTREE-Index bei Verwendung von >, <, >=, <=, BETWEEN, != oder <> oder LIKE 'pattern' (wobei 'pattern' nicht mit einem Platzhalter beginnt) Operator Wenn die Vergleichsbeziehung zwischen Schlüsselelementen und konstanten Werten einer Bereichsbedingung entspricht.
„Konstanter Wert“ bezieht sich auf: eine Konstante in einer Abfragezeichenfolge, eine Konstante im selben Join oder eine Spalte in der Systemtabelle, das Ergebnis einer nicht korrelierten Unterabfrage, einen Ausdruck, der vollständig aus Unterausdrücken des vorherigen Typs besteht
Im Folgenden finden Sie einige Beispiele für Abfragen mit Bereichsbedingungen in der WHERE-Klausel:
Die folgenden Bereichsabfragen eignen sich für Btree-Indizes und Hash-Indizes
SELECT * FROM t1WHEREkey_col = 1ORkey_col IN (15,18,20);
Die folgende Bereichsabfrage gilt für den Btree-Index
SELECT * FROM t1WHERE key_col > 1AND key_col <
SELECT * FROM t1WHERE key_col LIKE 'ab%'OR key_col BETWEEN 'bar' AND
' foo';
5.4 Wie MySQL Indizes verwendet
Indizes werden verwendet, um schnell Zeilen mit einem bestimmten Wert in einer Spalte zu finden. Ohne die Verwendung eines Index muss MySQL bei Datensatz 1 beginnen und die gesamte Tabelle durchlesen, bis es die relevante Zeile findet. Je größer der Tisch, desto länger dauert es. Wenn die abgefragte Spalte in der Tabelle einen Index hat, kann MySQL schnell an einen Punkt gelangen, an dem es die Mitte der Datendatei durchsucht, ohne sich alle Daten ansehen zu müssen. Zum Beispiel
Wenn eine Tabelle 1000 Zeilen hat, ist dies mindestens 100-mal schneller als das sequentielle Lesen. Beachten Sie, dass das sequentielle Lesen viel schneller ist, wenn Sie auf einen großen Teil der Zeilen zugreifen müssen, da wir an diesem Punkt eine Festplattensuche vermeiden.
Die meisten MySQL-Indizes (PRIMARY KEY, UNIQUE, INDEX und FULLTEXT) werden in B-Bäumen gespeichert. Nur räumliche Spaltentypindizes verwenden R-Tree, und MEMORY-Tabellen unterstützen auch Hash-Indizes.
Eine detaillierte Erläuterung der Umstände, unter denen die Datenbank Indizes verwendet, und der Umstände, unter denen die Datenbank keine Indizes verwendet, finden Sie in den entsprechenden Kapiteln des Optimierungskapitels, die hier nicht wiederholt werden.

Weitere verwandte Artikel finden Sie auf der chinesischen PHP-Website (www.php.cn)!