Optimierung der Abfragegeschwindigkeit von MySQL mit mehreren zehn Millionen Daten mithilfe von Indizes

1. Die Rolle des Index

Im Allgemeinen entspricht ein Index dem Inhaltsverzeichnis eines Buches. Wenn wir eine Abfrage basierend auf Bedingungen durchführen und kein Index vorhanden ist, müssen wir die gesamte Tabelle scannen. Das ist in Ordnung, wenn die Datenmenge klein ist, aber sobald die Datenmenge eine Million oder sogar mehrere zehn Millionen übersteigt, dauert die Ausführung einer SQL-Abfrage oft mehrere zehn Sekunden oder sogar länger. Mehr als 5 Sekunden sind bereits unerträglich.

Wenn das Problem durch Software gelöst werden kann, wird es durch Hardware nicht gelöst. Schließlich ist die Verbesserung des Hardwarecodes teuer und die Kosteneffizienz zu gering. Eine kostengünstige und effektive Lösung besteht darin, entsprechend Indizes hinzuzufügen. Die ordnungsgemäße Verwendung von Indizes kann die Abfragegeschwindigkeit um ein Tausendfaches erhöhen, was ein erstaunlicher Effekt ist.

(ii) MySQL-Indextypen:

Es gibt fünf Arten von MySQL-Indizes: Primärschlüsselindex, normaler Index, eindeutiger Index, Volltextindex und Clusterindex (mehrspaltiger Index).

Eindeutige Indizes und Volltextindizes werden selten verwendet. Wir konzentrieren uns hauptsächlich auf Primärschlüsselindizes, gemeinsame Indizes und Clusterindizes.

1) Primärschlüsselindex: Der Primärschlüsselindex ist ein Index, der dem Primärschlüssel hinzugefügt wird. Beim Festlegen des Primärschlüssels erstellt MySQL automatisch den Primärschlüsselindex.

2) Normaler Index: ein Index, der auf Grundlage von Spalten erstellt wird, die keine Primärschlüssel sind;

3) Clusterindex: Ein Index, der auf mehreren Spalten basiert.

(III) Indexsyntax:

Zeigen Sie den Index einer Tabelle an: Zeigen Sie den Index vom Tabellennamen an.

Erstellen Sie einen normalen Index: Tabelle ändern Tabellenname Index hinzufügen Indexname (Spalte zum Hinzufügen eines Index)

Erstellen Sie einen gruppierten Index: alter table table name add index index name (indizierte Spalte 1, indizierte Spalte 2)

Löschen Sie den Index einer Tabelle: Legen Sie den Index „Indexname“ auf dem Tabellennamen ab.

(IV) Leistungstests

Testumgebung: Arbeitsdesktop des Bloggers

Prozessor: Intel Core i5-4460 3,2 GHz;

Speicher 8G;

64-Bit-Windows.

1: Erstellen Sie eine Testtabelle

Tabelle löschen, wenn `Testbenutzer` vorhanden ist;
CREATE TABLE `test_user` (
 `id` bigint(20) PRIMARY-Schlüssel nicht null AUTO_INCREMENT,
 `Benutzername` varchar(11) DEFAULT NULL,
 `Geschlecht` varchar(2) DEFAULT NULL,
 `Passwort` varchar(100) DEFAULT NULL
)ENGINE=MyISAM STANDARD-CHARSET=utf8;

Die Speicher-Engine verwendet MyISAM, da diese Engine keine Transaktionen hat und eine sehr schnelle Einfügegeschwindigkeit aufweist, wodurch wir schnell zig Millionen Testdaten einfügen können. Nachdem wir die Daten eingefügt haben, ändern wir den Speichertyp in InnoDB.

2: Verwenden Sie gespeicherte Prozeduren, um 10 Millionen Datensätze einzufügen

Prozedur myproc() erstellen 
beginnen 
Deklariere num int; 
setze num=1; 
während num <= 10000000 
einfügen in test_user(Benutzername, Geschlecht, Passwort) Werte(Anzahl, „vertraulich“, PASSWORT(Anzahl)); 
setze num=num+1;
Ende während;
 Ende

rufe myproc() auf;

Aufgrund der verwendeten MyISAM-Engine dauerte das Einfügen von 10 Millionen Datensätzen nur 246 Sekunden. Bei der InnoDB-Engine würde es mehrere Stunden dauern.

Ändern Sie dann die Speicher-Engine zurück zu InnDB. Verwenden Sie den folgenden Befehl: alter table test_user engine=InnoDB; Die Ausführung dieses Befehls dauert etwa 5 Minuten. Bitte haben Sie Geduld.

Tipps: Dies ist ein Test. Ändern Sie die Speicher-Engine in der Produktionsumgebung nicht nach Belieben. Der Vorgang „Tabelle ändern“ sperrt die gesamte Tabelle. Verwenden Sie ihn mit Vorsicht. Zweitens: Die MyISAM-Engine hat keine Transaktionen und schreibt nur Daten in den Speicher und schreibt die Daten dann regelmäßig auf die Festplatte, sodass ein plötzlicher Stromausfall zu Datenverlust führt. Die InnDB-Engine schreibt Daten in Protokolle und überträgt sie dann regelmäßig auf die Festplatte, sodass Sie sich keine Sorgen über plötzliche Stromausfälle machen müssen. Wenn InnDB in der tatsächlichen Produktion verwendet werden kann, sollte es daher verwendet werden.

3: SQL-Test

Wählen Sie ID, Benutzername, Geschlecht und Passwort aus Testbenutzer, wobei ID = 999999

Dauer: 0,114 s.

Da wir beim Erstellen der Tabelle die ID als Primärschlüssel festgelegt haben, wurde beim Ausführen dieses SQL der Primärschlüsselindex verwendet, was die Abfragegeschwindigkeit so hoch machte.

Führen wir „select id,username,gender,password from test_user where username='9000000'“ aus.
Benötigte Zeit: 4,613 s.

Wir fügen der Spalte „Benutzername“ einen normalen Index hinzu.

ALTER TABLE `test_user` ADD INDEX index_name(Benutzername);

Dieser Vorgang dauert etwa 54,028 s. Beim Indexerstellungsprozess wird die gesamte Tabelle gescannt und die Indizes nacheinander erstellt, was natürlich langsam ist.

Führen Sie dann Folgendes aus: selectid,username,gender,password from test_user where username='9000000'
Dauer: 0,043 s.

Geben Sie anschließend Benutzername und Passwort ein, um an der Abfrage teilzunehmen.

Wählen Sie ID, Benutzername, Geschlecht und Passwort aus Testbenutzer, wobei Benutzername = „9000000“ und „Passwort“ = „*3A70E147E88D99888804E4D472410EFD9CD890AE“ ist.

Obwohl wir der Spalte „Benutzername“ einen Index hinzugefügt haben, ist die Spalte „Passwort“ nicht indiziert. Wenn der Index zum Filtern des Passworts verwendet wird, wird trotzdem die gesamte Tabelle gescannt.

Die Abfragegeschwindigkeit sank sofort.

Benötigte Zeit: 4,492 s.

Wenn unser SQL mehrere Spalten mit Filterbedingungen hat, müssen wir mehreren Spalten der Abfrage Indizes hinzufügen, um einen Gesamtindex zu bilden:

Fügen Sie einen gruppierten Index hinzu: ALTER TABLE `test_user` ADD INDEX index_union_name_password(username,password)
Führen Sie dann Folgendes aus:

Dauer: 0,001 s.

Wie eingangs erwähnt, besteht die Optimierung auf Softwareebene darin, Indizes sinnvoll hinzuzufügen und langsam ausgeführtes SQL zu optimieren. Die beiden ergänzen sich und sind unverzichtbar. Wenn die Abfrage nach dem Hinzufügen eines Index immer noch langsam ist, sollten Sie überlegen, ob es sich um ein SQL-Problem handelt, und SQL optimieren.

Tipps:

1: Auch wenn der Index hinzugefügt wurde, können die folgenden Situationen dennoch einen vollständigen Tabellenscan erfordern:

Die Indexspalte ist eine Zeichenfolge ohne Anführungszeichen;

Die Indexspalte erscheint nicht nach der Where-Bedingung;

Die Indexspalte erscheint nicht im Vordergrund.

2: Mögliche Situationen, in denen die zugehörige Abfrage den Index nicht verwendet, sind:

Die Zeichensätze der zugehörigen Tabellen sind unterschiedlich;

Die Zeichensätze der zugehörigen Felder sind unterschiedlich;

Die Speicher-Engine ist anders;

Die Längen der Felder sind unterschiedlich.

Dies ist das Ende dieses Artikels über die Optimierung der Abfragegeschwindigkeit von MySQL-Zehnmillionen von Daten basierend auf Indizes. Weitere relevante Inhalte zur MySQL-Indexoptimierungsabfrage finden Sie in früheren Artikeln auf 123WORDPRESS.COM oder in den folgenden verwandten Artikeln. Ich hoffe, Sie werden 123WORDPRESS.COM auch in Zukunft unterstützen!