Verwandte Vorgänge zum Hinzufügen und Löschen von Indizes in MySQL

1. Die Rolle des Index

In allgemeinen Anwendungssystemen beträgt das Lese-/Schreibverhältnis etwa 10:1, und bei Einfügevorgängen und allgemeinen Aktualisierungsvorgängen treten selten Leistungsprobleme auf. Die häufigsten und problematischsten Vorgänge sind einige komplexe Abfragevorgänge, sodass die Optimierung von Abfrageanweisungen offensichtlich höchste Priorität hat.

Wenn die Datenmenge und die Anzahl der Zugriffe nicht groß sind, ist der MySQL-Zugriff sehr schnell und die Frage, ob ein Index hinzugefügt wird oder nicht, hat kaum Auswirkungen auf den Zugriff. Wenn jedoch die Datenmenge und die Anzahl der Besuche drastisch ansteigen, werden Sie feststellen, dass MySQL langsamer wird oder sogar abstürzt. In diesem Fall müssen Sie eine SQL-Optimierung in Betracht ziehen. Das Einrichten korrekter und sinnvoller Indizes für die Datenbank ist ein wichtiges Mittel zur Optimierung von MySQL.

Der Zweck des Index besteht darin, die Abfrageeffizienz zu verbessern. Er kann mit einem Wörterbuch verglichen werden. Wenn wir das Wort „mysql“ nachschlagen möchten, müssen wir den Buchstaben m finden, dann von unten nach unten den Buchstaben y und dann den Rest von SQL. Wenn kein Index vorhanden ist, müssen Sie möglicherweise alle Wörter durchsehen, um das Gesuchte zu finden. Beispiele für Indizes gibt es nicht nur in Wörterbüchern, sondern auch überall im Leben, z. B. in Zugfahrplänen an Bahnhöfen, in Buchkatalogen usw. Ihre Prinzipien sind dieselben: Sie filtern die endgültigen gewünschten Ergebnisse heraus, indem sie den Umfang der Daten, die sie erhalten möchten, kontinuierlich einschränken und gleichzeitig zufällige Ereignisse in sequenzielle Ereignisse umwandeln. Das heißt, wir sperren die Daten immer mit derselben Suchmethode.

Beim Erstellen eines Indexes müssen Sie berücksichtigen, welche Spalten in SQL-Abfragen verwendet werden, und dann einen oder mehrere Indizes für diese Spalten erstellen. Tatsächlich ist ein Index auch eine Tabelle, die den Primärschlüssel oder das Indexfeld und einen Zeiger speichert, der jeden Datensatz auf die eigentliche Tabelle verweist. Indizes sind für Datenbankbenutzer unsichtbar. Sie dienen nur zur Beschleunigung von Abfragen. Datenbanksuchmaschinen verwenden Indizes, um Datensätze schnell zu finden.

Die Ausführung von INSERT- und UPDATE-Anweisungen für eine Tabelle mit einem Index dauert länger, während SELECT-Anweisungen schneller ausgeführt werden. Dies liegt daran, dass beim Ausführen eines Einfügens oder Aktualisierens die Datenbank auch den Indexwert einfügen oder aktualisieren muss.

2. Erstellen und Löschen von Indizes

Arten von Indizes:

• UNIQUE (eindeutiger Index): Der gleiche Wert kann nicht vorkommen und NULL-Werte sind zulässig
• INDEX (normaler Index): ermöglicht die Anzeige des gleichen Indexinhalts
• PROMARY KEY (Primärschlüsselindex): Doppelte Werte sind nicht zulässig
• Volltextindex: kann verwendet werden, um ein Wort in einem Wert anzuvisieren, aber die Effizienz ist nicht zufriedenstellend.
• Zusammengesetzter Index: Im Wesentlichen werden mehrere Felder in einen Index integriert, und die Kombination der Spaltenwerte muss eindeutig sein

(1) Mit der Anweisung ALTER TABLE erstellen Sie eine

Wird zum Hinzufügen nach der Erstellung der Tabelle verwendet.

ALTER TABLE Tabellenname ADD Indextyp (eindeutig, Primärschlüssel, Volltext, Index) [Indexname] (Feldname)
//Gewöhnlicher Index alter table table_name add index index_name (column_list);
//Eindeutiger Index, alter table, table_name, add unique (column_list);
//Primärschlüsselindex, Tabelle ändern, Tabellenname, Primärschlüssel hinzufügen (Spaltenliste);

Mit ALTER TABLE können drei Indexformate erstellt werden: Normaler Index, UNIQUE-Index und PRIMARY KEY-Index. table_name ist der Name der Tabelle, zu der der Index hinzugefügt werden soll, column_list gibt an, welche Spalten indiziert werden sollen und wenn mehrere Spalten vorhanden sind, werden die Spalten durch Kommas getrennt. Der Indexname index_name ist optional. Standardmäßig weist MySQL einen Namen basierend auf der ersten Indexspalte zu. Darüber hinaus ermöglicht ALTER TABLE das Ändern mehrerer Tabellen in einer einzigen Anweisung, sodass mehrere Indizes gleichzeitig erstellt werden können.

(2) Verwenden Sie die Anweisung CREATE INDEX, um der Tabelle einen Index hinzuzufügen

Mit CREATE INDEX können Sie einer Tabelle einen allgemeinen oder einen eindeutigen Index hinzufügen und beim Erstellen einer Tabelle einen Index erstellen.

CREATE INDEX index_name ON table_name(Benutzername(Länge));

Beim Typ CHAR oder VARCHAR kann die Länge kleiner sein als die tatsächliche Länge des Felds; beim Typ BLOB oder TEXT muss die Länge angegeben werden.

//create kann nur diese beiden Indizes hinzufügen;
CREATE INDEX Indexname ON Tabellenname (Spaltenliste)
CREATE UNIQUE INDEX index_name ON tabellenname (spaltenliste)

table_name, index_name und column_list haben die gleiche Bedeutung wie in der Anweisung ALTER TABLE. Der Indexname ist nicht optional. Darüber hinaus können Sie mit der Anweisung CREATE INDEX keinen PRIMARY KEY-Index erstellen.

(3) Index löschen

Das Löschen eines Indexes kann mit den Anweisungen ALTER TABLE oder DROP INDEX erfolgen. DROP INDEX kann als einzelne Anweisung innerhalb von ALTER TABLE im folgenden Format verarbeitet werden:

Lösche den Index Indexname auf Tabellenname.
Tabelle Tabellenname ändern, Index Indexname löschen;
Tabelle Tabellenname ändern, Primärschlüssel löschen;

In den beiden vorherigen Anweisungen wird der Index index_name in table_name gelöscht. In der letzten Anweisung wird es nur zum Löschen des PRIMARY KEY-Index verwendet. Da eine Tabelle nur einen PRIMARY KEY-Index haben kann, muss der Indexname nicht angegeben werden. Wenn kein PRIMARY KEY-Index erstellt wird, die Tabelle aber über einen oder mehrere UNIQUE-Indizes verfügt, löscht MySQL den ersten UNIQUE-Index.

Wenn Sie eine Spalte aus einer Tabelle entfernen, wirkt sich dies auf den Index aus. Wenn Sie bei einem Index mit mehreren Spalten eine der Spalten löschen, wird die Spalte auch aus dem Index gelöscht. Wenn Sie alle Spalten löschen, aus denen ein Index besteht, wird der gesamte Index gelöscht.

(4) Zusammengesetzter Index und Präfixindex

Es sei hier darauf hingewiesen, dass es sich bei zusammengesetzten Indizes und Präfixindizes um Namen für Indizierungstechniken handelt und nicht um Indextypen. Zur besseren Erklärung wird im Folgenden eine Demotabelle erstellt.

Tabelle USER_DEMO erstellen
(
 ID int nicht null auto_increment Kommentar 'Primärschlüssel',
 LOGIN_NAME varchar(100) nicht null Kommentar 'Anmeldename',
 PASSWORT varchar(100) nicht null Kommentar 'Passwort',
 CITY varchar(30) nicht null Kommentar 'Stadt',
 AGE int nicht null Kommentar 'Alter',
 SEX int not null Kommentar 'Geschlecht (0: weiblich 1: männlich)',
 Primärschlüssel (ID)
);

Um die Effizienz von MySQL weiter zu steigern, können Sie die Erstellung eines zusammengesetzten Indexes in Erwägung ziehen, d. h. die Integration von LOGIN_NAME, CITY und AGE in einen Index:

ALTER TABLE USER_DEMO ADD INDEX name_city_age (LOGIN_NAME(16),STADT,ALTER);

Beim Erstellen einer Tabelle beträgt die Länge von LOGIN_NAME 100. Hier wird 16 verwendet, da die Länge eines Namens im Allgemeinen 16 nicht überschreitet. Dadurch werden Indexabfragen beschleunigt, die Größe der Indexdatei verringert und die Aktualisierungsgeschwindigkeit von INSERT und UPDATE erhöht.

Wenn wir jeweils einspaltige Indizes für LOGIN_NAME, CITY und AGE erstellen, sodass die Tabelle drei einspaltige Indizes hat, unterscheidet sich die Abfrageeffizienz stark von der des kombinierten Index und kann sogar weitaus geringer sein als bei unserem kombinierten Index. Obwohl derzeit drei Indizes vorhanden sind, kann MySQL nur den Einzelspaltenindex verwenden, der seiner Meinung nach am effizientesten ist. Die anderen beiden werden nicht verwendet, was bedeutet, dass es sich immer noch um einen vollständigen Tabellenscanvorgang handelt.

Die Erstellung eines solchen kombinierten Indexes ist gleichbedeutend mit der Erstellung der folgenden drei kombinierten Indizes:

LOGIN_NAME,STADT,ALTER
LOGIN_NAME,STADT
LOGIN_NAME

Warum gibt es keinen kombinierten Index wie STADT, ALTER usw.? Dies liegt daran, dass der MySQL-Verbundindex das Ergebnis „ganz linkes Präfix“ ergibt. Das einfache Verständnis ist, dass die Kombination in der Spalte ganz links beginnt und nicht alle Abfragen, die diese drei Spalten enthalten, diesen kombinierten Index verwenden. Das heißt, name_city_age(LOGIN_NAME(16),CITY,AGE) wird von links nach rechts indiziert. Wenn links vorne kein Index vorhanden ist, führt MySQL keine Indexabfrage aus.

Wenn die Indexspalte zu lang ist, wird beim Indizieren dieser Spalte eine große Indexdatei generiert, was unpraktisch ist. Sie können zum Indizieren die Präfixindizierung verwenden. Der Präfixindex sollte an einem geeigneten Punkt gesteuert werden, der innerhalb des goldenen Werts von 0,31 gesteuert werden kann (er kann erstellt werden, wenn er größer als dieser Wert ist).

SELECT COUNT(DISTINCT(LEFT(`title`,10)))/COUNT(*) FROM Arctic; -- Wenn dieser Wert größer als 0,31 ist, können Sie einen Präfixindex erstellen und Distinct verwenden, um Duplikate zu entfernen. ALTER TABLE `user` ADD INDEX `uname`(title(10)); -- Fügen Sie einen Präfixindex SQL hinzu und erstellen Sie einen Index mit dem Namen 10, wodurch die Größe der Indexdatei reduziert und Indexabfragen beschleunigt werden können.

3. Verwendung und Vorsichtsmaßnahmen des Index

EXPLAIN kann Entwicklern bei der Analyse von SQL-Problemen helfen. EXPLAIN zeigt, wie MySQL Indizes verwendet, um Select-Anweisungen zu verarbeiten und Tabellen zu verknüpfen. Dies kann dabei helfen, bessere Indizes auszuwählen und optimiertere Abfrageanweisungen zu schreiben.

Um es zu verwenden, fügen Sie einfach „Explain“ vor der Select-Anweisung hinzu:

Erklären Sie „select * from user where id=1“;

Versuchen Sie, diese SQL-Anweisungen zu vermeiden, die keine Indizes verwenden:

SELECT `sname` FROM `stu` WHERE `age`+10=30;-- Der Index wird nicht verwendet, da alle Indexspalten an der Berechnung beteiligt sind.SELECT `sname` FROM `stu` WHERE LEFT(`date`,4) <1990; -- Der Index wird nicht verwendet, da eine Funktionsoperation verwendet wird und das Prinzip das gleiche wie oben ist.SELECT * FROM `houdunwang` WHERE `uname` LIKE'后盾%' -- Gehe mit dem Index.SELECT * FROM `houdunwang` WHERE `uname` LIKE "%后盾%" -- Gehe nicht mit dem Index.-- Reguläre Ausdrücke verwenden keine Indizes, was leicht zu verstehen sein sollte. Warum ist es also schwierig, das Schlüsselwort regexp in SQL zu erkennen?-- Beim Zeichenfolgenvergleich mit Zahlen werden keine Indizes verwendet;
Tabelle `a` erstellen (`a` char(10));
EXPLAIN SELECT * FROM `a` WHERE `a`="1" -- Index verwenden EXPLAIN SELECT * FROM `a` WHERE `a`=1 -- Index nicht verwenden select * from dept where dname='xxx' or loc='xx' or deptno=45 -- Wenn die Bedingung ein „oder“ enthält, wird es nicht verwendet, auch wenn die Bedingung einen Index enthält. Mit anderen Worten müssen alle verwendeten Felder indexiert werden. Wir empfehlen, das Schlüsselwort oder möglichst zu vermeiden. Wenn MySQL schätzt, dass ein vollständiger Tabellenscan schneller ist als ein Index, wird der Index nicht verwendet.

Obwohl Indizes viele Vorteile haben, kann ihre übermäßige Verwendung das gegenteilige Problem mit sich bringen. Indizes haben auch Nachteile:

• Obwohl Indizes die Abfragegeschwindigkeit erheblich erhöhen, verringern sie auch die Geschwindigkeit von Tabellenaktualisierungen wie INSERT, UPDATE und DELETE. Denn beim Aktualisieren einer Tabelle speichert MySQL nicht nur die Daten, sondern auch die Indexdatei.
• Das Erstellen eines Indexes beansprucht Speicherplatz für Indexdateien. Normalerweise ist dieses Problem nicht schwerwiegend, wenn Sie jedoch mehrere kombinierte Indizes für eine große Tabelle erstellen, wird die Indexdatei sehr umfangreich.

Die Indizierung ist nur eine Möglichkeit, die Effizienz zu verbessern. Wenn MySQL eine Tabelle mit großen Datenmengen hat, müssen Sie Zeit damit verbringen, herauszufinden, wie Sie den besten Index erstellen oder Abfrageanweisungen optimieren können.

Für die Verwendung von Indizes gibt es einige Tipps:

1. Der Index enthält keine Spalten mit NULL

Solange die Spalte NULL-Werte enthält, wird sie nicht in den Index aufgenommen. Solange eine Spalte im zusammengesetzten Index NULL-Werte enthält, ist diese Spalte für diesen zusammengesetzten Index ungültig.

2. Verwenden Sie kurze Indizes

Bei der Indizierung einer Listenspalte sollte nach Möglichkeit eine Präfixlänge angegeben werden. Wenn Sie beispielsweise eine Spalte vom Typ char(255) haben und die meisten Werte innerhalb der ersten 10 oder 20 Zeichen eindeutig sind, indizieren Sie nicht die gesamte Spalte. Kurze Indizes können nicht nur die Abfragegeschwindigkeit verbessern, sondern auch Speicherplatz und E/A-Vorgänge sparen.

3. Indexspalten sortieren

MySQL-Abfragen verwenden nur einen Index. Wenn der Index also bereits in der Where-Klausel verwendet wird, verwenden die Spalten in der Order By-Klausel den Index nicht. Wenn die Standardsortierung der Datenbank die Anforderungen erfüllt, verwenden Sie den Sortiervorgang daher nicht. Versuchen Sie, die Sortierung mehrerer Spalten zu vermeiden. Wenn nötig, erstellen Sie am besten einen zusammengesetzten Index für diese Spalten.

4.Like-Anweisungsoperation

Generell wird von der Verwendung der Like-Operation abgeraten. Wenn Sie sie verwenden müssen, achten Sie auf die korrekte Verwendung. wie „%aaa%“ verwendet den Index nicht, aber wie „aaa%“ kann den Index verwenden.

5. Führen Sie keine Operationen an Spalten durch

6. Verwenden Sie nicht NOT IN, <>, ! =-Operation, aber <, <=, =, >, >=, BETWEEN und IN können Indizes verwenden.

7. Für häufig ausgewählte Felder sollten Indizes erstellt werden.

Dies liegt daran, dass sich die Abfragegeschwindigkeit nicht wesentlich ändert, ob diese Spalten selten verwendet werden oder nicht, ob ein Index vorhanden ist oder nicht. Im Gegenteil, das Hinzufügen von Indizes reduziert die Systemwartungsgeschwindigkeit und erhöht den Platzbedarf.

8. Der Index sollte für Felder mit relativ eindeutigen Werten erstellt werden.

9. Zu Spalten, die als Text-, Bild- und Bit-Datentypen definiert sind, sollten keine Indizes hinzugefügt werden. Dies liegt daran, dass das Datenvolumen dieser Spalten entweder sehr groß ist oder nur wenige Werte aufweist.

10. Spalten, die in „where“ und „join“ erscheinen, müssen indiziert werden.

11. Wenn die Where-Abfragebedingung ein Ungleichheitszeichen enthält (Where-Spalte != ...), kann MySQL den Index nicht verwenden.

12. Wenn in der Abfragebedingung der Where-Klausel eine Funktion verwendet wird (z. B. where DAY(column)=…), kann MySQL den Index nicht verwenden.

13. Beim Verknüpfungsvorgang (wenn Daten aus mehreren Tabellen extrahiert werden müssen) kann MySQL den Index nur verwenden, wenn der Datentyp des Primärschlüssels und des Fremdschlüssels identisch ist. Andernfalls wird der Index nicht verwendet, selbst wenn er erstellt wurde.

Nachfolgend finden Sie Ergänzungen von anderen Internetnutzern

Wie unten dargestellt:

ALTER TABLE xxxxx ADD INDEX `tid` (`tid`) USING BTREE;
DROP INDEX uid ON xxxx;
Index von xxxx anzeigen

Die oben genannten Vorgänge zum Hinzufügen und Löschen von Indizes in MySQL sind alles, was ich mit Ihnen teilen möchte. Ich hoffe, es kann Ihnen als Referenz dienen. Ich hoffe auch, dass Sie 123WORDPRESS.COM unterstützen werden.