MySQL-Datenbankoptimierung (4) - MySQL-Indexoptimierung

1. Index-Grundlagen
Indextyp:
1. Gewöhnlicher Index: Erstellt in einem beliebigen Datentyp
2. Eindeutiger Index: eingeschränkter Index Der Wert muss eindeutig sein
3. Volltextindex: Er kann nur für Felder vom Typ char, varchar und text erstellt werden. Er wird hauptsächlich zur Verbesserung der Textabfragegeschwindigkeit verwendet. Unterstützung der MyISAM-Engine.
4. Einspaltiger Index: Erstellen Sie einen Index für ein einzelnes Feld in der Tabelle
5. Mehrspaltiger Index: Erstellen Sie einen Index für mehrere Felder
6. Räumlicher Index: Erstellt mithilfe räumlicher Parameter zur Bereitstellung das System mit der Effizienz des Erhaltens von Kontrolldaten
Grundlegende Operationen des Index:

CREATE TABLE t_user1(id INT ,
                     userName VARCHAR(20),
                     PASSWORD VARCHAR(20),
                     INDEX (userName)  
             );
             
CREATE TABLE t_user2(id INT ,
                     userName VARCHAR(20),
                     PASSWORD VARCHAR(20),
                     UNIQUE INDEX index_userName(userName)
             );
           
CREATE TABLE t_user3(id INT ,
                     userName VARCHAR(20),
                     PASSWORD VARCHAR(20),
                     INDEX index_userName_password(userName,PASSWORD)//多列索引
             );
             
CREATE  INDEX index_userName ON t_user4(userName);--对已经创建的表指定索引


CREATE  UNIQUE INDEX index_userName ON t_user4(userName);


CREATE  INDEX index_userName_password ON t_user4(userName,PASSWORD);


ALTER TABLE t_user5 ADD INDEX index_userName(userName);--修改索引


ALTER TABLE t_user5 ADD UNIQUE INDEX index_userName(userName);


ALTER TABLE t_user5 ADD INDEX index_userName_password(userName,PASSWORD);


DROP INDEX index_userName ON t_user5;


DROP INDEX index_userName_password ON t_user5;

Das Hinzufügen eines Indexes kann die Abfrage beschleunigen Effizienz und Vermeidung vollständiger Tabellendatenabfragen, stattdessen werden die Zieldaten durch Durchsuchen des Indexes gefunden. Wählen Sie t.name von Benutzer t aus, wobei t.id = 5 ist. Wenn ein Index für die Spalte „actor_id“ erstellt wird, verwendet MySQL den Index, um die Zeile mit id = 5 zu finden, d. h., es sucht zuerst nach dem Wert im Index und gibt dann alle Zeilen zurück, die die Datenzeile für den Wert enthalten.
Der Index kann eine oder mehrere Spalten enthalten. Wenn der Index mehrere Spalten enthält, ist auch die Reihenfolge der Spalten sehr wichtig, da MySQL nur die Präfixspalte ganz links im Index effizient verwenden kann. Darüber hinaus erfordern die Erstellung und Wartung von Indizes auch Systemressourcen. Dazu gehört auch die Erstellung effizienter Indizes zur Verbesserung der Abfrageeffizienz.
2. MySQL-Indextyp
Im Allgemeinen ist der Datenindex selbst auch sehr groß und es ist unmöglich, alles im Speicher zu speichern, daher ist der Index häufig Basierend auf der Indexdatei wird das Formular auf der Festplatte gespeichert. In diesem Fall kommt es im Vergleich zum Speicherzugriff zu einem Festplatten-E/A-Verbrauch während des Indexsuchvorgangs. Daher sollte die strukturelle Organisation des Index die Anzahl der Festplatten-E/A-Zugriffe während des Suchvorgangs minimieren. Dies erfordert eine hochwertige Datenstruktur zum Organisieren und Speichern von Datenbankindizes.
Allgemeine Indextypen nutzen Datenstrukturalgorithmen. Beispielsweise ist B-Tree sowohl ein Indextyp in MySQL als auch ein dynamischer Suchbaum: Binärer Suchbaum und Balanced Binary Search Tree, Binärbaumstruktur von B-Baum/B+-Baum/B*-Baum (B~Baum).
Da diese MySQL-Indextypen auf der Implementierung von Datenstrukturalgorithmen basieren, beginnt die allgemeine Entwicklung auf dieser Grundlage. Auch die Anwendungen von Indizes, die auf unterschiedlichen Datenstrukturen basieren, sind unterschiedlich.

3. Häufige Missverständnisse bei der Indexverwendung
1. Unabhängiger Spaltenindex
Falsche Verwendung von : Wählen Sie t.name von Benutzer t aus, wobei t.age+1=5 gilt. Für diese Bedingung age+1=5 kann MySQL nicht automatisch analysieren, selbst wenn ein Index für das Alter erstellt wird In der Spalte „MySQL“ wird die Indexabfrage während der Abfrage nicht verwendet und es wird weiterhin ein vollständiger Tabellenscan durchgeführt.
Falsche Verwendung von : select t.name from user t where TO_DAYS(`CURRENT_DATE`())-TO_DAYS(date_col)<=10; Das Gleiche wie oben
Richtiges Prinzip: Legen Sie die Indexspalte immer auf die separate Seite des Vergleichsoperators.
2. Mehrspaltiger Index
Häufige Fehler: Erstellen Sie einen unabhängigen Index ohne Spalten oder erstellen Sie mehrere Spalten falsch order Index, oder indizieren Sie einfach alle Spalten in der Where-Bedingung.
Korrekte Verwendung: Wählen Sie die entsprechende Indexreihenfolge für verschiedene Indextypen Wählen Sie beispielsweise t.name von Benutzer t aus, wobei t.staffId = 2 und custom_id=7;
Sollten wir als Antwort auf die obige Abfrage eine (staffId, custom_id) erstellen oder die Reihenfolge dieser beiden Spalten umkehren?

Am Beispiel von B-Tree bedeutet die Reihenfolge der Indexspalten, dass der Index zuerst von links nach rechts nach der Spalte ganz links sortiert wird. Im Allgemeinen ist es sinnvoll, die Felder mit der höchsten Häufigkeit von Filterbedingungen an den Anfang des Index zu setzen. Der so konzipierte Index kann die benötigten Zeilen schnellstmöglich herausfiltern.

4. Zusammenfassung

Wenn ich mir ein Java-Cloud-Plattform-Projekt ansehe, an dem ich zuvor gearbeitet habe, wurde das verwendete ORM-Framework verwendet. Damals hatte das Projekt langsame kaskadierende Abfragen, sodass es eine kombinierte JDBC+ORM-Formularprogrammierung kombinierte Designs mehrerer Datenbanken und zur Indexoptimierung müssen immer noch erneut angewendet werden. Im Allgemeinen verwenden die erstellten Indizes nur einspaltige Indizes und werden mit Primärschlüsseln erstellt. Dies stellt jedoch kein großes Problem dar. Das Problem der aufwändigen Abfrage, Tabellenstrukturoptimierung, Indexoptimierung und Abfrageoptimierung muss Hand in Hand gehen, aber das Verlassen auf die Neuauswahl oder Neuoptimierung des ORM-Frameworks kann das Problem nicht lösen.

Das Obige ist der Inhalt der MySQL-Datenbankoptimierung (4) – MySQL-Indexoptimierung. Weitere verwandte Inhalte finden Sie auf der chinesischen PHP-Website (www.php.cn).