Detaillierte Erläuterung des MySQL-Kombinationsindex und des ganz links stehenden Übereinstimmungsprinzips

Vorwort

Ich habe im Internet schon viele Artikel über die Übereinstimmung des am weitesten links stehenden Präfixes des MySQL-Gemeinschaftsindex gesehen und dachte, ich hätte das Prinzip verstanden. Als ich kürzlich während des Interviews mit dem Interviewer kommunizierte, stellte ich fest, dass ich einige Dinge übersehen hatte. Hier werde ich den Inhalt diesbezüglich organisieren.

Wann sollte ein zusammengesetzter Index erstellt werden?

Wenn unsere Where-Abfrage mehrere Bedingungsabfragen enthält, müssen wir einen zusammengesetzten Index für die Abfragespalten erstellen.

Warum nicht für jede Spalte einen Index erstellen?

• Gemeinkosten senken
• Abdeckungsindex
• Hohe Effizienz

Reduzieren Sie den Mehraufwand: Wenn Sie einen zusammengesetzten Index für col1, col2 und col3 erstellen, entspricht dies der Erstellung von drei Indizes: (col1), (col1, col2) und (col1, col2, col3). Index abdecken: Wenn Sie SELECT col1, col2, col3 FROM Tabellenname abfragen, können Sie die abgefragten Felder, da sie auf der Indexseite vorhanden sind, direkt aus dem Index abrufen, ohne für die Abfrage zur Tabelle zurückkehren zu müssen.

Hohe Effizienz: Erstellen Sie Indizes für col1, col2 und col3. MySQL wählt nur die Spalte mit der höchsten Erkennung als Index aus. Angenommen, es gibt 1 Million Daten und ein Index filtert 10 % der Daten heraus, dann können 10 Millionen Daten herausgefiltert werden; bei einem kombinierten Index können 1 Million*10 %*10 %*10 %=1000 Daten herausgefiltert werden.

Prinzip der Übereinstimmung ganz links

Angenommen, wir erstellen einen zusammengesetzten Index von (col1, col2, col3), was dem Sortieren der Spalte col1 entspricht. Das heißt, wir erstellen einen zusammengesetzten Index basierend auf der Spalte ganz links. Solange die Abfragebedingung die Spalte ganz links enthält, verwendet die Abfrage den Index.

Erstellen einer Testtabelle

CREATE TABLE `student` (
 `id` int(11) NICHT NULL,
 `name` varchar(10) NICHT NULL,
 `Alter` int(11) NICHT NULL,
 Primärschlüssel (`id`),
 SCHLÜSSEL `idx_id_name_age` (`id`,`name`,`alter`)
) ENGINE=InnoDB STANDARD-CHARSET=utf8

Füllen Sie 1 Million Testdaten aus

DROP-VERFAHREN pro10;
PROZEDUR ERSTELLEN pro10()
BEGINNEN
	Erklären Sie, dass ich INT;
	DECLARE char_str varchar(100) DEFAULT 'abcdefghijklmnopqrstuvwxyzABCDEFGHIJKLMNOPQRSTUVWXYZ0123456789';
	DECLARE return_str varchar(255) DEFAULT '';
	DECLARE Alter INT;
	Setze i = 1;
	WÄHREND ich < 5000000 mache
		SET return_str = Teilzeichenfolge (char_str, FLOOR (1 + RAND () * 62), 8);
		Setze i = i+1;
		SET Alter = FLOOR(RAND() * 100);
		INSERT INTO student(id, name, age) values(i, return_str, age);
	ENDE WÄHREND;
ENDE;

CALL pro10();

Szenariotests

ERKLÄREN SIE: SELECT * FROM student WHERE id = 2;

Sie können sehen, dass die Abfrage den Index verwendet

EXPLAIN SELECT * FROM student WHERE id = 2 AND name = 'defghijk';

Sie können sehen, dass die Abfrage den Index verwendet

EXPLAIN SELECT * FROM student WHERE id = 2 AND name = 'defghijk' und Alter = 8;

Sie können sehen, dass die Abfrage den Index verwendet

EXPLAIN SELECT * FROM student WHERE id = 2 AND age = 8;

Sie können sehen, dass die Abfrage den Index verwendet

EXPLAIN SELECT * FROM student WHERE name = 'defghijk' AND age = 8;

Es ist ersichtlich, dass die Abfrage den Index nicht verwendet, der Typ Index ist, die Anzahl der Abfragezeilen 4989449 beträgt und fast die gesamte Tabelle gescannt wird. Da der kombinierte Index nur die Spalte ganz links sortiert, können nur Name und Alter vollständig gescannt werden.

EXPLAIN SELECT * FROM student WHERE name = 'defghijk' AND id = 2;

EXPLAIN SELECT * FROM student WHERE Alter = 8 AND ID = 2;

EXPLAIN SELECT * FROM student WHERE Name = „defghijk“ und Alter = 8 AND ID = 2;

Sie können sehen, dass die obige Abfrage auch den Index verwendet. Die Ergebnisse sind dieselben, egal ob die ID vorne oder hinten steht. MySQL findet die effizienteste Abfragemethode, nämlich die Abfrage zuerst basierend auf der ID.

Zusammenfassen

Wie im obigen Test gezeigt, wird der Index für die Abfrage verwendet, solange die Spalten in der Abfragebedingung die äußerste linke Spalte des zusammengesetzten Index enthalten, unabhängig von der Position der Spalte in der Abfragebedingung.

Das ist alles für diesen Artikel. Ich hoffe, dass der Inhalt dieses Artikels für Ihr Studium oder Ihre Arbeit von gewissem Referenzwert ist. Vielen Dank für Ihre Unterstützung von 123WORDPRESS.COM.