Eine kurze Diskussion über den Unterschied zwischen MySQL-Primärschlüsselindex und Nicht-Primärschlüsselindex

Was ist ein Index

Die offizielle Definition des MySql-Index: Ein Index ist eine Datenstruktur, die MySql dabei hilft, effizient Daten abzurufen. Der Zweck des Index besteht darin, die Abfrageeffizienz zu verbessern, analog zu einem Wörterbuch. Tatsächlich ist der Index auch eine Tabelle, die den Primärschlüssel und die Indexfelder speichert und auf die Datensätze der Entitätstabelle verweist. Die Indexspalte nimmt ebenfalls Speicherplatz ein.

Der Unterschied zwischen Primärschlüsselindex und normalem Index

1. Der Primärschlüsselindex indiziert die Daten, während der normale Index den Primärschlüssel-ID-Wert indiziert (dies ist in InnoDB der Fall, aber wenn es Myisam ist, gibt es keinen Unterschied zwischen dem Primärschlüsselindex und dem normalen Index, beide indizieren die Daten direkt).
2. Wenn Sie mit where id=x abfragen, müssen Sie den Primärschlüsselindex nur einmal scannen, um die entsprechenden Daten zu erhalten. Wenn Sie jedoch mit einem normalen Index abfragen, scannen Sie zuerst den normalen Index, um den Primärschlüsselwert zu erhalten, und scannen dann den Primärschlüsselindex erneut, um die erforderlichen Daten zu erhalten. Dieser Vorgang wird als Table Back bezeichnet.

Welche Datenstruktur wird für den Index verwendet?

Es gibt zwei gängige MySQL-Strukturen: Hash-Index und B+ Tree-Index. Wir verwenden die InnoDB-Engine und der Standardwert ist B+ Tree.

InnoDB verwendet das B+ Tree-Indexmodell. Warum also B+ Tree verwenden? Welche Vor- und Nachteile hat dies gegenüber dem Hash-Index?

Der Unterschied zwischen B+-Baumindex und Hash-Index Der Hash-Index ist für Abfragen mit gleichen Werten geeignet, kann jedoch keine Bereichsabfragen durchführen Der Hash-Index kann den Index nicht zum Abschließen der Sortierung verwenden Der Hash-Index unterstützt nicht die Übereinstimmungsregel für den am weitesten links stehenden mehrspaltigen gemeinsamen Index Wenn eine große Anzahl doppelter Schlüsselwerte vorhanden ist, ist die Effizienz des Hash-Index aufgrund des Hash-Kollisionsproblems sehr gering

Was kann in den Blattknoten des B+-Baums gespeichert werden?

In InnoDB ist der Blattknoten des Index-B+-Baums, der die gesamte Datenzeile speichert, der Primärschlüsselindex, auch als gruppierter Index bezeichnet. Die Blattknoten des Index B+-Baums, die den Primärschlüsselwert speichern, sind Nicht-Primärschlüsselindizes, auch als nicht gruppierte Indizes bezeichnet.

Gibt es beim Abfragen von Daten Unterschiede zwischen gruppierten und nicht gruppierten Indizes?

Clustered-Indexabfragen sind schneller, da die Blattknoten des Primärschlüssel-Indexbaums direkt die gesamte Datenzeile sind, die wir abfragen möchten. Der Blattknoten des Nichtprimärschlüsselindex ist der Wert des Primärschlüssels. Nachdem Sie den Wert des Primärschlüssels gefunden haben, müssen Sie ihn erneut über den Wert des Primärschlüssels abfragen. Dieser Vorgang wird als Tabellenrückgabe bezeichnet.
Werden Nicht-Primärschlüsselindizes mehrfach abgefragt?
Ein überdeckender Index kann auch nur einmal abgefragt werden. Ein überdeckender Index bedeutet, dass die Ausführung einer Abfrageanweisung nur aus dem Index abgerufen werden kann, ohne dass dieser aus der Datentabelle gelesen werden muss. Man kann auch sagen, dass eine Indexabdeckung erreicht ist. Wenn eine Abfrageanweisung die Bedingung des abdeckenden Index erfüllt, muss MySQL den Index nur verwenden, um die für die Abfrage erforderlichen Daten zurückzugeben. Dadurch wird die Notwendigkeit vermieden, nach dem Auffinden des Index zur Tabelle zurückzukehren, was die E/A reduziert und die Effizienz verbessert. Beispielsweise gibt es einen gemeinsamen Index idx_key1_key2(key1, key2) in der Tabelle coverage_index_sample. Wenn wir die SQL-Anweisung verwenden: „Select key2 from coverage_index_sample where key1 = 'keytest';“, können wir den überdeckenden Index abfragen, ohne zur Tabelle zurückzukehren.

Index-Bedingungs-Pushdown

MySQL 5.6 führte die Index-Pushdown-Optimierung ein, die standardmäßig aktiviert ist und mit SET optimizer_switch = 'index_condition_pushdown=off'; deaktiviert werden kann. Die Beispiele und Erklärungen in der offiziellen Dokumentation lauten wie folgt: In der Personentabelle bilden (Postleitzahl, Nachname, Vorname) einen Index

WO PLZ='95054' UND Nachname WIE '%etrunia%' UND Adresse WIE '%Main Street%';

Wenn die Index-Pushdown-Technologie nicht verwendet wird, fragt MySQL die entsprechenden Daten von der Speicher-Engine über zipcode='95054' ab und gibt sie an den MySQL-Server zurück. Der MySQL-Server ermittelt dann, ob die Daten die Bedingungen basierend auf Nachname LIKE '%etrunia%' und Adresse LIKE '%Main Street%' erfüllen. Wenn die Index-Push-Down-Technologie verwendet wird, gibt MySQL zuerst den Index zurück, der mit zipcode='95054' übereinstimmt, filtert dann den Index heraus, der die Bedingungen basierend auf dem Nachnamen LIKE '%etrunia%' erfüllt, und gibt ihn an den MySQL-Server zurück. Der MySQL-Server ermittelt dann, ob die Daten die Bedingungen basierend auf der Adresse LIKE '%Main Street%' erfüllen, sodass die Anzahl der an den MySQL-Server zurückgegebenen Indizes reduziert wird. Mithilfe der Index-Pushdown-Optimierung lässt sich die Anzahl der Tabellenrückgaben bei Abfragen mit ähnlichen Bedingungen reduzieren.

Abfrage-Optimierer

Eine SQL-Anweisungsabfrage kann verschiedene Ausführungspläne haben. Welcher Plan letztendlich ausgewählt wird, muss vom Optimierer ausgewählt werden, um den Plan mit den niedrigsten Ausführungskosten auszuwählen. Bevor eine Einzeltabellenabfrageanweisung tatsächlich ausgeführt wird, sucht der MySQL-Abfrageoptimierer nach allen möglichen Lösungen zur Ausführung der Anweisung und findet nach dem Vergleich die Lösung mit den geringsten Kosten. Diese kostengünstigste Lösung ist der sogenannte Ausführungsplan. Der Optimierungsprozess läuft wie folgt ab: 1. Finden Sie alle möglichen Indizes basierend auf den Suchbedingungen. 2. Berechnen Sie die Kosten für den vollständigen Tabellenscan. 3. Berechnen Sie die Kosten für die Ausführung von Abfragen mit verschiedenen Indizes. 4. Vergleichen Sie die Kosten verschiedener Ausführungspläne und finden Sie den mit den niedrigsten Kosten.

Frage zum Index

TABELLE „Geek“ erstellen (
  `a` int(11) NICHT NULL,
  `b` int(11) NICHT NULL,
  `c` int(11) NICHT NULL,
  `d` int(11) NICHT NULL,
  PRIMÄRSCHLÜSSEL (`a`,`b`),
  SCHLÜSSEL `c` (`c`),
  SCHLÜSSEL `ca` (`c`,`a`),
  SCHLÜSSEL `cb` (`c`,`b`)
)ENGINE=InnoDB;

Aus historischen Gründen erfordert diese Tabelle a und b als gemeinsame Primärschlüssel.
Das heißt, wenn wir einen Index nur für Feld c erstellen, enthält dieser bereits drei Felder. Warum müssen wir zwei Indizes „ca“ und „cb“ erstellen?

Wählen Sie * aus Geek, wobei c = N, sortiert nach der Grenze 1;
Wählen Sie * aus Geek, wobei c = N, Sortierung nach b, Grenze 1;

Frage: Ist die Erklärung des Kollegen richtig? Welche Indizes sind unnötig und können gelöscht werden?

Antwort:

(1) Die Organisationsreihenfolge des gruppierten Index der Primärschlüssel a und b entspricht der Sortierung nach a, b, d. h., zuerst wird nach a, dann nach b sortiert, und c ist nicht in der richtigen Reihenfolge.

–a--|–b--|–c--|–d--
1 2 3 Tage
1 3 2 Tage
1 4 3 Tage
2 1 3 Tage
2 2 2 d
2 3 4 Tage

(2) Die Organisation des Index ca besteht darin, zuerst nach c und dann nach a zu sortieren und gleichzeitig den Primärschlüssel aufzuzeichnen.

–c--|–a--|–Primärschlüsselteil b-- (Achtung, hier steht nicht ab, sondern nur b)
2 1 3
2 2 2
3 1 2
3 1 4
3 2 1
4 2 3

Die Daten des obigen Index ca sind exakt dieselben wie die des Index c.

(3) Die Organisation des Index cb besteht darin, zuerst nach c und dann nach b zu sortieren und gleichzeitig den Primärschlüssel aufzuzeichnen

–c--|–b--|–Primärschlüsselteil a-- (wie oben)
2 2 2
2 3 1
3 1 2
3 2 1
3 4 1
4 3 2

Die Schlussfolgerung lautet also, dass ca entfernt und cb beibehalten werden kann.

Dies ist das Ende dieses Artikels über den Unterschied zwischen MySQL-Primärschlüsselindex und Nicht-Primärschlüsselindex. Weitere relevante Inhalte zu MySQL-Primärschlüsselindex und Nicht-Primärschlüsselindex finden Sie in den vorherigen Artikeln von 123WORDPRESS.COM oder in den folgenden verwandten Artikeln. Ich hoffe, dass jeder 123WORDPRESS.COM in Zukunft unterstützen wird!