Erstellen Sie einen Index für Myql

Um die Leistung von MySQL zu verbessern, können wir Indizes erstellen, um die Suchgeschwindigkeit von MySQL zu verbessern und den Druck auf die MySQL-Datenbank zu verringern. Lassen Sie uns über MySQL-Indizes und einige erweiterte Verwendungsmöglichkeiten sprechen.

Alle MySQL-Spaltentypen können indiziert werden. 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.

Derzeit gibt es nur zwei Speichertypen von Indizes (btree und hash), die sich speziell auf den Speicher-Engine-Modus beziehen:
MyISAM btree
InnoDB btree
MEMORY/Heap-Hash, btree

Standardmäßig verwendet die MEMORY/Heap-Speicher-Engine einen Hash-Index

Der Unterschied zwischen dem Btree-Index und dem Hash-Index von MySQL
Die Besonderheit der Hash-Indexstruktur, ihre Abrufeffizienz ist sehr hoch, der Indexabruf kann im Gegensatz dazu auf einmal lokalisiert werden Der Btree-Index (B-Tree) erfordert mehrere E/A-Zugriffe vom Wurzelknoten zum Zweigknoten und schließlich zum Seitenknoten, sodass die Abfrageeffizienz des Hash-Index viel höher ist als die des Btree-Index (B-Tree).

Obwohl der Hash-Index sehr effizient ist, bringt der Hash-Index selbst aufgrund seiner Besonderheiten auch viele Einschränkungen und Nachteile mit sich, hauptsächlich wie folgt.
(1) Der Hash-Index kann nur =-, <=>-, IN-, IS NULL- oder IS NOT NULL-Abfragen erfüllen, und Bereichsabfragen können nicht verwendet werden.
Da der Hash-Index den Hash-Wert nach der Hash-Operation vergleicht, kann er nur für die Gleichwertfilterung und nicht für die bereichsbasierte Filterung verwendet werden, da der Hash-Wert nach der Verarbeitung durch den entsprechenden Hash-Algorithmus groß ist Es ist nicht garantiert, dass die Beziehung genau dieselbe ist wie vor der Hash-Operation.

(2) Der Hash-Index kann nicht verwendet werden, um Datensortierungsvorgänge zu vermeiden.
Da der Hash-Index den Hash-Wert nach der Hash-Berechnung speichert und die Größenbeziehung des Hash-Werts nicht unbedingt genau mit dem Schlüsselwert vor der Hash-Operation übereinstimmt, kann die Datenbank die Indexdaten nicht dazu verwenden Vermeiden Sie Sortiervorgänge.

(3) Der Hash-Index kann nicht mit Teilindexschlüsseln abgefragt werden.
Für den kombinierten Index werden bei der Berechnung des Hash-Werts des Hash-Index die kombinierten Indexschlüssel zusammengeführt und dann der Hash-Wert zusammen berechnet, anstatt den Hash-Wert separat zu berechnen, sodass er durch berechnet wird Der erste oder mehrere Indexschlüssel des kombinierten Indexes kann bei der Abfrage nicht verwendet werden.

(4) Der Hash-Index kann den Tabellenscan zu keinem Zeitpunkt vermeiden.
Wie wir bereits wissen, dient der Hash-Index dazu, den Hash-Wert des Hash-Operationsergebnisses und die entsprechenden Zeilenzeigerinformationen in einer Hash-Tabelle zu speichern, nachdem der Indexschlüssel gehasht wurde. Da verschiedene Indexschlüssel den gleichen Hash-Wert haben. Selbst wenn Sie also die Anzahl der Datensätze erhalten, die einen bestimmten Hash-Schlüsselwert erfüllen, können Sie die Abfrage nicht direkt über den Hash-Index abschließen. Sie müssen dennoch entsprechende Vergleiche durchführen, indem Sie auf die tatsächlichen Daten in der Tabelle zugreifen und die entsprechenden Ergebnisse erhalten.

(5) Wenn ein Hash-Index auf eine große Anzahl gleicher Hash-Werte trifft, ist seine Leistung nicht unbedingt höher als die des B-Tree-Index.
Wenn bei Indexschlüsseln mit geringer Selektivität ein Hash-Index erstellt wird, ist eine große Anzahl von Datensatzzeigerinformationen mit demselben Hash-Wert verknüpft. Auf diese Weise ist es sehr mühsam, einen bestimmten Datensatz zu finden, und es werden mehrere Zugriffe auf die Tabellendaten verschwendet, was zu einer geringen Gesamtleistung führt.


Der B-Tree-Index ist der am häufigsten verwendete Index Geben Sie die MySQL-Datenbank ein. Alle Speicher-Engines außer der Archiv-Speicher-Engine unterstützen B-Tree-Indizes. Dies ist nicht nur in MySQL der Fall, sondern in vielen anderen Datenbankverwaltungssystemen ist der B-Tree-Index auch der Hauptindextyp. Dies liegt hauptsächlich daran, dass die Speicherstruktur des B-Tree-Index bestimmte Funktionen beim Abrufen von Datenbankdaten hat . Sehr gute Leistung.
Im Allgemeinen werden die meisten physischen Dateien des B-Tree-Index in MySQL in der Balance Tree-Struktur gespeichert, d. h. alle tatsächlich erforderlichen Daten werden im Blattknoten des Baums gespeichert und können von dort aus abgerufen werden Jeder Blattknoten hat genau die gleiche Länge, daher nennen wir ihn alle einen B-Tree-Index. Natürlich können verschiedene Datenbanken (oder verschiedene Speicher-Engines von MySQL) ihre eigenen B-Tree-Indizes speichern leicht modifiziert.
Zum Beispiel ist die tatsächliche Speicherstruktur, die vom B-Tree-Index der Innodb-Speicher-Engine verwendet wird, tatsächlich B+Tree, was bedeutet, dass eine kleine Änderung auf der Grundlage der B-Tree-Datenstruktur vorgenommen und hinzugefügt wird zu jedem Blattknoten. Zusätzlich zum Speichern der relevanten Informationen des Indexschlüssels werden auch die Zeigerinformationen gespeichert, die auf den nächsten Blattknoten neben dem Blattknoten zeigen. Dies dient hauptsächlich dazu, die Effizienz beim Abrufen mehrerer benachbarter Blattknoten zu beschleunigen.
In der Innodb-Speicher-Engine gibt es zwei verschiedene Formen von Indizes: Eine ist der Primärschlüsselindex (Primärschlüssel) in Form eines Clusters und die andere ist grundsätzlich dieselbe Speicherform wie bei anderen Speicher-Engines (z. B MyISAM-Speicher-Engine) Gewöhnlicher B-Tree-Index. Dieser Index wird in der Innodb-Speicher-Engine als Sekundärindex bezeichnet.
Wenn Sie in Innodb über den Primärschlüssel auf Daten zugreifen, ist es sehr effizient. Wenn Sie über den Sekundärindex auf die Daten zugreifen, ruft Innodb zunächst den Blattknoten über die relevanten Informationen des Sekundärindex und den entsprechenden Indexschlüssel ab. Rufen Sie dann die entsprechende Datenzeile über den im Blattknoten gespeicherten Primärschlüsselwert und dann über den Primärschlüsselindex ab.
Der Unterschied zwischen dem Primärschlüsselindex und dem Nicht-Primärschlüsselindex der MyISAM-Speicher-Engine ist sehr gering, außer dass der Indexschlüssel des Primärschlüsselindex ein eindeutiger und nicht leerer Schlüssel ist. Darüber hinaus ist die Speicherstruktur des Index der MyISAM-Speicher-Engine im Wesentlichen dieselbe wie die des Sekundärindex von Innodb. Der Hauptunterschied besteht darin, dass die MyISAM-Speicher-Engine die Indexschlüsselinformationen auf den Blattknoten speichert Die Informationen der entsprechenden Datenzeile (z. B. Zeilennummer) können direkt nach der Speicherung gespeichert werden, die Schlüsselwertinformationen des Primärschlüssels werden jedoch nicht gespeichert.

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:
(1) Gewöhnlicher Index, der der grundlegendste Index ist und keinen Einschränkungen unterliegt. Es gibt die folgenden Erstellungsmethoden:

-- Index erstellen

CREATE INDEX indexName ON mytable(username(10)); Einzelspaltenindex

-- CREATE INDEX indexName ON mytable(username(10),city(10)); -- Kombinierter Index

-- indexName ist der Indexname, der Mytable-Tabellenname, der Benutzername und die Stadt ​​ist der Spaltenname, 10 ist die Präfixlänge, dh die Länge der im Index gespeicherten Informationen, beginnend mit dem Zeichen ganz links in der Spalte, Einheitsbyte

– wenn es CHAR ist, VARCHAR Beim Typ BLOB und TEXT kann die Präfixlänge kleiner sein als die tatsächliche Länge des Felds. Die Präfixlänge muss wie unten angegeben angegeben werden.

-- Ändern Sie die Tabellenstruktur, um einen Index zu erstellen

ALTER TABLE mytable ADD INDEX indexName (username(10));

-- ALTER TABLE mytable ADD INDEX indexName (username(10),city(10));

-- Der Indexname muss hier nicht geschrieben werden, das System weist automatisch die Namen username, username_2, username_3 usw. zu. .

--Direkt angeben

CREATE TABLE mytable(

id INT,

username VARCHAR(16),

city VARCHAR(16),

age INT,

INDEX indexName (username(10))-- INDEX indexName (username(10),city(10))

);

-- Hier kann der Indexname indexName auch weggelassen werden

(2) Eindeutiger Index, der dem vorherigen normalen Index ähnelt, der Unterschied ist : Der Wert der Indexspalte muss eindeutig sein, aber Nullwerte sind zulässig. Bei einem zusammengesetzten Index muss die Kombination der Spaltenwerte eindeutig sein. Es verfügt über die folgenden Erstellungsmethoden (fügen Sie UNIQUE nur vor dem Schlüsselwort INDEX hinzu, wenn Sie einen normalen Index erstellen):

-- Index erstellen

CREATE UNIQUE INDEX indexName ON mytable(username(10));

-- Ändern Sie die Tabellenstruktur, um einen Index zu erstellen

ALTER TABLE mytable ADD UNIQUE INDEX indexName (username( 10) ));-- Es kann auch abgekürzt werden als ALTER TABLE mytable ADD UNIQUE indexName (username(10));

-- Direkt angeben

CREATE TABLE beim Erstellen der Tabelle mytable(

id INT,

Benutzername VARCHAR(16),

Stadt VARCHAR(16),

Alter INT,

UNIQUE INDEX indexName (username(10)) – kann auch als UNIQUE indexName (username(10)) abgekürzt werden

);

(3) Primärschlüssel Index, ein spezieller eindeutiger Index, der keine Nullwerte zulässt. Der beim Erstellen der Tabelle gleichzeitig erstellte Primärschlüssel ist der Primärschlüsselindex
Der Primärschlüsselindex muss nicht benannt werden und eine Tabelle kann nur einen Primärschlüssel haben. Der Primärschlüsselindex kann gleichzeitig ein eindeutiger Index oder ein Volltextindex sein, aber der eindeutige Index oder der Volltextindex können nicht im selben Index koexistieren:

- - Ändern Sie die Tabellenstruktur, um einen Index zu erstellen ALTER TABLE mytable ADD PRIMARY KEY (id );

-- Geben Sie CREATE TABLE mytable direkt an, wenn Sie eine Tabelle erstellen(
id INT,
Benutzername VARCHAR(16 ),
Stadt VARCHAR(16),
Alter INT, PRIMARY KEY(id)
);

(4) Volltextindex, InnoDB-Speicher-Engine unterstützt keinen Volltextindex:

-- Erstellen Sie einen Index. CREATE FULLTEXT INDEX indexName ON mytable(username(10));

-- Ändern Sie die Tabellenstruktur um einen Index zu erstellen ALTER TABLE mytable ADD FULLTEXT INDEX indexName (username(10));

- - Es kann auch als ALTER TABLE mytable ADD FULLTEXT indexName (username(10));

-- Geben Sie beim Erstellen der Tabelle direkt CREATE TABLE mytable(

id INT,
username VARCHAR(16),
city VARCHAR(16),
age INT,
FULLTEXT INDEX indexName ( username(10))

– kann auch als FULLTEXT indexName (username(10)))ENGINE =MYISAM;

-- abgekürzt werden. Erstellen Sie beim Erstellen einer Tabelle einen Volltextindex. Stellen Sie die Speicher-Engine der Tabelle auf MYISAM ein. Die Standard-InnoDB-Speicher-Engine der neuen Version von MySQL unterstützt keine Volltextindizes.

-- Löschen Sie den Index DROP INDEX indexName ON mytable;

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.

Das Obige habe ich zum Erstellen eines Index in MySQL zusammengefasst. Ich hoffe, dass es in Zukunft für alle hilfreich sein wird.

Verwandte Artikel:

Das Einfügen von Daten in MySQL in PHP zeigt verstümmelte Zeichen an

Myql5.7.7 optimierte Konfiguration Parameter _MySQL

Das obige ist der detaillierte Inhalt vonErstellen Sie einen Index für Myql. Für weitere Informationen folgen Sie bitte anderen verwandten Artikeln auf der PHP chinesischen Website!