MySQL erstellt einen Index, um die Systemlaufgeschwindigkeit zu verbessern
- 黄舟Original
- 2016-12-15 11:26:361165Durchsuche
Angenommen, wir erstellen eine mytable-Tabelle:
CREATE TABLE mytable( ID INT NOT NULL, username VARCHAR(16) NOT NULL ); Wir fügen zufällig 10.000 Datensätze ein, darunter einen: 5555, admin.
Wenn bei der Suche nach dem Datensatz „username="admin" SELECT * FROM mytable WHERE username='admin'; ein Index für den Benutzernamen eingerichtet wurde, kann MySQL den Datensatz ohne Scan genau finden. Im Gegenteil, MySQL scannt alle Datensätze, dh es werden 10.000 Datensätze abgefragt.
Indizes werden in Einzelspaltenindizes und kombinierte Indizes unterteilt. Ein einspaltiger Index bedeutet, dass ein Index nur eine einzige Spalte enthält. Eine Tabelle kann mehrere einspaltige Indizes haben, es handelt sich jedoch nicht um einen kombinierten Index. Kombinierter Index, das heißt, ein Index enthält mehrere Spalten.
Zu den MySQL-Indextypen gehören:
(7) Vorsichtsmaßnahmen für die Verwendung von Indizes
Bei der Verwendung von Indizes gibt es einige Tipps und Vorsichtsmaßnahmen wie folgt:
◆Der Index enthält keine Spalten mit NULL-Werten
Solange eine Spalte einen NULL-Wert enthält, wird sie nicht in den Index aufgenommen. Solange eine Spalte im zusammengesetzten Index einen NULL-Wert enthält, ist diese Spalte für den zusammengesetzten Index ungültig. Daher sollten wir beim Entwerfen der Datenbank nicht zulassen, dass der Standardwert des Felds NULL ist.
◆Verwenden Sie den kurzen Index
, um die Zeichenfolge zu indizieren, und geben Sie nach Möglichkeit eine Präfixlänge an. Wenn Sie beispielsweise eine CHAR(255)-Spalte haben und die meisten Werte innerhalb der ersten 10 oder 20 Zeichen eindeutig sind, indizieren Sie nicht die gesamte Spalte. Kurze Indizes verbessern nicht nur die Abfragegeschwindigkeit, sondern sparen auch Speicherplatz und E/A-Vorgänge.
◆Indexspaltensortierung
MySQL-Abfrage verwendet nur einen Index. Wenn der Index also in der where-Klausel verwendet wurde, verwenden die Spalten in der Reihenfolge nach nicht den Index. Verwenden Sie daher keine Sortiervorgänge, wenn die Standardsortierung der Datenbank die Anforderungen erfüllen kann. Versuchen Sie, die Sortierung mehrerer Spalten nicht einzuschließen. Bei Bedarf ist es am besten, zusammengesetzte Indizes für diese Spalten zu erstellen.
◆Like-Anweisungsoperation
Im Allgemeinen wird die Verwendung einer Like-Operation nicht empfohlen. Wenn sie verwendet werden muss, ist auch die Verwendung problematisch. „%aaa%“ verwendet nicht den Index, aber „aaa%“ verwendet den Index.
◆Keine Bearbeitung von Spalten durchführen.
Wählen Sie * aus Benutzern aus, bei denen YEAR(adddate)<2007; auf jede Zeile angewendet wird, was dazu führt, dass der Index fehlschlägt und ein vollständiger Tabellenscan durchgeführt wird, damit wir Änderungen vornehmen können
wählen Sie * aus Benutzern mit adddate<'2007-01-01';
◆Verwenden Sie keine NOT IN- und <>-Operationen.
Oben werden die MySQL-Indextypen vorgestellt.
(6) Nachteile von Indizes
Oben wurden die Vorteile der Verwendung von Indizes erwähnt, aber eine übermäßige Verwendung von Indizes führt zu Missbrauch. Daher weist der Index auch seine Mängel auf:
◆Obwohl der Index die Abfragegeschwindigkeit erheblich verbessert, verringert er auch die Geschwindigkeit der Aktualisierung der Tabelle, z. B. INSERT, UPDATE und DELETE in der Tabelle. Denn beim Aktualisieren der Tabelle muss MySQL nicht nur die Daten, sondern auch die Indexdatei speichern.
◆Das Erstellen von Indexdateien belegt Speicherplatz. Im Allgemeinen ist dieses Problem nicht schwerwiegend. Wenn Sie jedoch mehrere kombinierte Indizes für eine große Tabelle erstellen, wird die Indexdatei schnell erweitert.
Indizes sind nur ein Faktor zur Verbesserung der Effizienz. Wenn Ihr MySQL über eine große Menge an Datentabellen verfügt, müssen Sie Zeit damit verbringen, die besten Indizes zu recherchieren und zu erstellen oder Abfrageanweisungen zu optimieren.
(5) Zeitpunkt zum Erstellen eines Index
Bisher haben wir gelernt, wie man einen Index erstellt. Unter welchen Umständen müssen wir also einen Index erstellen? Im Allgemeinen müssen Spalten, die in WHERE und JOIN erscheinen, indiziert werden, aber das ist nicht ganz richtig, da MySQL nur <, <=, =, >, >=, BETWEEN, IN indiziert und manchmal LIKE verwendet Index. Zum Beispiel:
SELECT t.Name FROM mytable t LEFT JOIN mytable m ON t.Name=m.username WHERE m.age=20 AND m.city='Zhengzhou' Zu diesem Zeitpunkt müssen Sie Stadt und Alter indizieren , weil Der Benutzername der Mytable-Tabelle erscheint auch in der JOIN-Klausel und muss indiziert werden.
Wie gerade erwähnt, muss LIKE nur zu bestimmten Zeiten indiziert werden. Weil MySQL den Index nicht verwendet, wenn Abfragen mit den Platzhalterzeichen % und _ beginnen. Beispielsweise verwendet der folgende Satz den Index:
SELECT * FROM mytable WHERE username like'admin%', während der folgende Satz den Index nicht verwendet:
SELECT * FROM mytable WHEREt Name like'%admin' Daher sollten Sie bei der Verwendung von LIKE auf die oben genannten Unterschiede achten.
(4) Zusammengesetzter Index
Um den Einzelspaltenindex und den zusammengesetzten Index anschaulich zu vergleichen, fügen Sie der Tabelle mehrere Felder hinzu:
CREATE TABLE mytable( ID INT NOT NULL, username VARCHAR(16) NOT NULL, city VARCHAR (50) NOT NULL, age INT NOT NULL ); Um die Effizienz von MySQL weiter zu steigern, muss über die Einrichtung eines kombinierten Index nachgedacht werden. Bauen Sie einfach Name, Stadt und Alter in einen Index ein:
ALTER TABLE mytable ADD INDEX name_city_age (name(10),city,age); Beim Erstellen der Tabelle beträgt die Länge des Benutzernamens 16, und hier wird 10 verwendet. Dies liegt daran, dass die Namenslänge im Allgemeinen 10 nicht überschreitet, was die Indexabfrage beschleunigt, die Größe der Indexdatei verringert und die Aktualisierungsgeschwindigkeit von INSERT verbessert.
Wenn Sie jeweils einen einspaltigen Index für Benutzername, Stadt und Alter erstellen, sodass die Tabelle über drei einspaltige Indizes verfügt, unterscheidet sich die Abfrageeffizienz stark vom oben genannten kombinierten Index, der weitaus niedriger ist als unser kombinierter Index . Obwohl es derzeit drei Indizes gibt, kann MySQL nur den einspaltigen Index verwenden, der seiner Meinung nach am effizientesten ist.
Das Erstellen eines solchen kombinierten Index entspricht tatsächlich dem Erstellen der folgenden drei Sätze kombinierter Indizes:
Benutzername, Stadt, Benutzername für Alter, Benutzername für Stadt. Warum gibt es keine kombinierten Indizes wie Stadt und Alter? Dies ist auf das „ganz linke Präfix“ des MySQL-Verbundindex zurückzuführen. Das einfache Verständnis besteht darin, die Kombination nur ganz links zu beginnen. Nicht nur Abfragen, die diese drei Spalten enthalten, verwenden diesen kombinierten Index, sondern auch die folgende SQL verwendet diesen kombinierten Index:
SELECT * FROM mytable WHREE username="admin" AND city="Zhengzhou" SELECT * FROM mytable WHREE username="admin " Die folgenden werden nicht verwendet:
SELECT * FROM mytable WHREE age=20 AND city="Zhengzhou" SELECT * FROM mytable WHREE city="Zhengzhou"
(3 ) Primärschlüsselindex
Das ist es ein spezieller eindeutiger Index, der keine Nullwerte zulässt. Im Allgemeinen wird der Primärschlüsselindex beim Erstellen der Tabelle erstellt:
CREATE TABLE mytable( ID INT NOT NULL, username VARCHAR(16) NOT NULL, PRIMARY KEY(ID) ); Natürlich können Sie auch den ALTER-Befehl verwenden . Denken Sie daran: Eine Tabelle kann nur einen Primärschlüssel haben.
(2) Eindeutiger Index
Er ähnelt dem vorherigen gewöhnlichen Index, außer dass der Wert der Indexspalte eindeutig sein muss, Nullwerte jedoch zulässig sind. Bei einem zusammengesetzten Index muss die Kombination der Spaltenwerte eindeutig sein. Es verfügt über die folgenden Erstellungsmethoden:
◆Index erstellen
CREATE UNIQUE INDEX indexName ON mytable(username(length)) ◆Ändern der Tabellenstruktur
ALTER mytable ADD UNIQUE [indexName] ON (username(length)) ◆Geben Sie beim Erstellen einer Tabelle direkt
CREATE TABLE mytable( ID INT NOT NULL, username VARCHAR(16) NOT NULL, UNIQUE [indexName] (username(length)) );
(1) Ordinary index Dies ist der einfachste Index, es gibt keine Einschränkungen. Es verfügt über die folgenden Erstellungsmethoden:
◆Create index
CREATE INDEX indexName ON mytable(username(length)); Wenn es sich um den Typ CHAR, VARCHAR handelt, kann die Länge kleiner sein als die tatsächliche Länge des Feldes ist vom Typ BLOB und TEXT. Die Länge muss wie unten angegeben angegeben werden.
◆Ändern Sie die Tabellenstruktur
ALTER mytable ADD INDEX [indexName] ON (username(length)) ◆Beim Erstellen der Tabelle direkt angeben
CREATE TABLE mytable( ID INT NOT NULL, username VARCHAR(16) NOT NULL , INDEX [indexName] (username(length)) ); Syntax zum Löschen des Index:
DROP INDEX [indexName] ON mytable;
Index ist der Schlüssel zur schnellen Suche. Die Einrichtung eines MySQL-Index ist für den effizienten Betrieb von MySQL sehr wichtig. Hier sind einige gängige MySQL-Indextypen.
In Datenbanktabellen kann die Indizierung von Feldern die Abfragegeschwindigkeit erheblich verbessern.