Konzept und Anwendungsbeispiele für die MySQL-Indexkardinalität

Dieser Artikel erläutert anhand von Beispielen das Konzept und die Verwendung der MySQL-Indexkardinalität. Teilen Sie uns die Einzelheiten zu Ihrer Information mit:

Kardinalität (Indexkardinalität) ist ein sehr wichtiges Konzept im MySQL-Index

Die Indexkardinalität ist die Anzahl der unterschiedlichen Werte, die eine Spalte enthält. Wenn eine Datenspalte beispielsweise die Werte 1, 2, 3, 4, 5, 1 enthält, dann beträgt ihre Kardinalität 5. Ein Index funktioniert am besten, wenn seine Kardinalität im Verhältnis zur Anzahl der Zeilen in der Tabelle hoch ist (d. h. die Spalte enthält viele unterschiedliche Werte und wenige doppelte Werte). Wenn eine Spalte viele unterschiedliche Altersangaben enthält, kann der Index die Zeilen schnell unterscheiden. Wenn eine Spalte zur Aufzeichnung des Geschlechts verwendet wird (mit nur zwei Werten, „M“ und „W“), wäre ein Index nicht sehr nützlich. Wenn die Werte mit nahezu gleicher Wahrscheinlichkeit auftreten, ergibt die Suche nach jedem Wert die Hälfte der Zeilen. In diesen Fällen ist es am besten, überhaupt keinen Index zu verwenden, denn wenn der Abfrageoptimierer feststellt, dass ein bestimmter Wert in einem hohen Prozentsatz der Datenzeilen in der Tabelle vorkommt, ignoriert er den Index im Allgemeinen und führt einen vollständigen Tabellenscan durch. Der häufig verwendete Prozentwert liegt bei „30 %“.

Ein weiteres Konzept heißt Indexselektivität

Indexselektivität = Indexkardinalität/Gesamtdaten. Die Kardinalität kann über „Index aus Tabellenname anzeigen“ angezeigt werden.
Je näher der Wert bei 1 liegt, desto wahrscheinlicher ist es, dass der Index verwendet wird. Man kann auch davon ausgehen, dass 1 100 % hoch ist. Der Vorteil der Indexselektivität besteht darin, dass MySQL bei der Suche nach Übereinstimmungen mehr Zeilen filtern kann. Die Selektivität eines eindeutigen Indexes ist mit einem Wert von 1 am besten.

Hier ist eine Tabelle von mir. Die Datenmenge ist nicht groß, nur um die Indexselektivität zu testen

Wählen Sie * aus Artikeln
id Titel Name15 Titel 0 Großer Bär16 Titel 1 Großer Bär17 Titel 2 Großer Bär18 Titel 3 Großer Bär19 Titel 4 Großer Bär20 Titel 5 Großer Bär21 Titel 6 Großer Bär22 Titel 7 Großer Bär23 Titel 8 Zwei Bären24 Titel 9 Zwei Bären

id ist der standardmäßige ganzzahlige Autoinkrement-Primärschlüssel

Fügen Sie nun die Spalte „Autor“ als Index hinzu und zeigen Sie alle Indizes der Tabelle „Artikel“ an.

ALTER TABLE `Artikel` ADD INDEX (`Autor`)
INDEX VON Artikeln ANZEIGEN
Tabelle nicht eindeutig Schlüsselname Sequenz im Index Spaltenname Sortierung Kardinalität Unterteil gepackt null Indextyp
Artikel 0 PRIMARY 1 ID A 10 NULL NULL BTREE
Artikel 1 Autor 1 Autor A 2 NULL NULL BTREE

Erklären Sie die Bedeutung der einzelnen Felder

• Tabelle Tabellenname
• non_unique ist 0, wenn der Index keine doppelten Wörter enthalten kann. 1, wenn möglich.
• Schlüsselname Indexname
• seq_in_index Die Spaltensequenznummer im Index, beginnend bei 1
• column_name Spaltenname
• Wie Sortierspalten im Index gespeichert werden. In der MySQL SHOW INDEX-Syntax gibt es den Wert „A“ (aufsteigend) oder NULL (keine Sortierung).
• Kardinalitätsindex Kardinalität
• sub_part: Die Anzahl der indizierten Zeichen, wenn die Spalte nur teilweise indiziert ist. NULL, wenn die gesamte Spalte indiziert ist.
• „packed“ gibt an, wie Schlüsselwörter gepackt werden. Wenn nicht komprimiert, ist dies NULL.
• null Enthält JA, wenn die Spalte NULL enthält. Wenn nicht, enthält die Spalte NEIN.
• Von index_type verwendete Index-Speichermethode (BTREE, FULLTEXT, HASH, RTREE)

Wie Sie sehen, verfügt die Artikeltabelle bereits über zwei Indizes.

Der ID-Index enthält keine doppelten Wörter. Der Name der Primärschlüsselspalte ist id. Die Indexkardinalität beträgt 10 in aufsteigender Reihenfolge. Es gibt keinen Teilindex. Es gibt keine Komprimierung. Es gibt kein Null. Die Speichermethode ist btree.

Der Autorenindex enthält wiederholte Wörter. Der Indexname ist Autor. Der Spaltenname ist Autor. Der Index ist aufsteigend sortiert. Die Indexkardinalität ist 2. Es gibt keinen Teilindex. Es gibt keine Komprimierung. Es gibt keine Nullen. Die Speichermethode ist B-Tree.

Gemäß dem Indexselektivitätsalgorithmus beträgt die ID-Indexselektivität 10/10 = 1 und die Autorindexselektivität 2/10 = 0,2. Lassen Sie uns das testen und „explain“ verwenden, um die Anweisungsanalyse anzuzeigen.

Erklären Sie „select * from articles where id = 15“
id select_type Tabelle Partitionen Typ mögliche Schlüssel Schlüssel Schlüssellänge ref Zeilen gefiltert extra
1 EINFACHE Artikel NULL const PRIMARY PRIMARY 4 const 1 100,00 NULL

Lassen Sie mich noch einmal über den Befehl „Explain“ sprechen.

EXPLAIN zeigt, wie MySQL Indizes verwendet, um Auswahlanweisungen zu verarbeiten und Tabellen zu verknüpfen. Es kann dabei helfen, bessere Indizes auszuwählen und optimiertere Abfrageanweisungen zu schreiben. Erklären Sie die Bedeutung der einzelnen Felder

1. id SELECT-Kennung. Dies ist die SELECT-Abfragesequenznummer. Dies ist nicht wichtig. Die Abfragesequenznummer ist die Reihenfolge, in der die SQL-Anweisungen ausgeführt werden.

2. select_type Typ auswählen

2.1. SIMPLE Wenn Sie eine einfache Auswahlabfrage ausführen, die keine Union-Operation erfordert oder keine Unterabfrage enthält, ist der select_type der Antwortabfrageanweisung einfach. Unabhängig davon, wie komplex die Abfrageanweisung ist, darf im Ausführungsplan nur eine Unitabfrage mit dem einfachen select_type vorhanden sein.
2.2. PRIMÄR In einem Auswahlabfrageausführungsplan, der eine Union-Operation erfordert oder eine Unterabfrage enthält, ist der äußerste Auswahltyp primär. Wie bei Simple gibt es nur eine Unit-Select-Abfrage mit select_type als primär.
2.3. Union: In den durch die Union-Operation gebildeten Unit-Select-Abfragen ist, mit Ausnahme der ersten, der Auswahltyp aller Unit-Select-Abfragen nach der zweiten Union. Der select_type der ersten Unit-Auswahl von Union ist nicht Union, sondern DERIVED. Es handelt sich um eine temporäre Tabelle, die zum Speichern der Abfrageergebnisse nach der Vereinigung verwendet wird.
2.4. DEPENDENT UNION dependent Wie UNION select_type erscheint dependent union in der Set-Abfrage, die durch union oder union all gebildet wird. Das Wort „abhängig“ bedeutet hier, dass die durch Vereinigung oder Vereinigung aller Einheiten gebildete Abfrage von externen Faktoren beeinflusst wird.
2.5. Union-Ergebnis Union-Ergebnis ist eine Datentabelle, die das Union-Ergebnis enthält

3. Tabelle Tabellenname

4. Typverbindungstyp , es gibt mehrere Parameter, zunächst vom besten Typ bis zum schlechtesten Typ steht auch im Mittelpunkt dieses Artikels

4.1 const , die Tabelle hat höchstens eine übereinstimmende Zeile, const wird zum Vergleichen von Primärschlüsseln oder eindeutigen Indizes verwendet. Da nur eine Datenzeile abgeglichen wird, ist dies sehr schnell, was auch als optimal optimierter Index und konstante Suche verstanden werden kann.
4.2 eq_ref Zur Erklärung von eq_ref heißt es im MySQL-Handbuch: „Lesen Sie für jede Zeilenkombination aus der vorherigen Tabelle eine Zeile aus dieser Tabelle. Mit Ausnahme von Konstantentypen ist dies möglicherweise der beste Join-Typ.“
4.3 ref Für jede Zeilenkombination aus der vorherigen Tabelle werden alle Zeilen mit übereinstimmenden Indexwerten aus dieser Tabelle gelesen. Wenn der Join nur ein ganz linkes Präfix des Schlüssels verwendet oder wenn der Schlüssel weder UNIQUE noch PRIMARY KEY ist (mit anderen Worten, wenn der Join keine einzelne Zeile basierend auf dem Schlüssel auswählen kann), wird „ref“ verwendet. Dieser Verknüpfungstyp ist gut, wenn die verwendeten Schlüssel nur mit einer kleinen Anzahl von Zeilen übereinstimmen.
4.4 ref_or_null Dieser Join-Typ ist wie ref, bietet aber zusätzlich die Möglichkeit, dass MySQL gezielt nach Zeilen suchen kann, die NULL-Werte enthalten. Diese Join-Typ-Optimierung wird häufig zum Auflösen von Unterabfragen verwendet.
4.5 index_merge Dieser Verknüpfungstyp gibt an, dass die Indexzusammenführungsoptimierungsmethode verwendet wird. In diesem Fall enthält die Spalte „key“ die Liste der verwendeten Indizes und „key_len“ das längste Schlüsselelement der verwendeten Indizes.
4.6 eindeutige Unterabfrage
4.7 Index-Unterabfrage
4.8 Bereich Suche innerhalb eines gegebenen Bereichs, mit Hilfe eines Indexes zur Überprüfung der Zeilen
4.9 Index Dieser Verknüpfungstyp ist derselbe wie ALL, außer dass nur der Indexbaum gescannt wird. Dies ist normalerweise schneller als ALL, da Indexdateien normalerweise kleiner als Datendateien sind. (Das heißt, obwohl sowohl „all“ als auch „Index“ die gesamte Tabelle lesen, liest „index“ aus dem Index, während „all“ von der Festplatte liest.)
4.10 ALLE Für jede Zeilenkombination aus den vorherigen Tabellen wird ein vollständiger Tabellenscan durchgeführt. Dies ist normalerweise schlecht, wenn die Tabelle die erste Tabelle ist, die nicht als „const“ markiert ist, und ist in anderen Fällen normalerweise sehr schlecht. Oft können Sie statt ALL weitere Indizes hinzufügen, sodass Zeilen basierend auf konstanten Werten oder Spaltenwerten in der vorherigen Tabelle abgerufen werden können.

5. Possible_keys gibt Hinweise, welcher Index zum Suchen der Zeile in der Tabelle verwendet wird, was nicht sehr wichtig ist

6. keys gibt den von der MySQL-Abfrage verwendeten Index an

7. key_len MySQL-Indexlänge

8. ref zeigt, welche Spalte oder Konstante zusammen mit dem Schlüssel verwendet wird, um Zeilen aus der Tabelle auszuwählen

9. Zeilen zeigen die Anzahl der Zeilen an, in denen MySQL die Abfrage ausführt. Je größer der Wert, desto schlechter ist es, was darauf hinweist, dass der Index nicht gut verwendet wird.

10. Extra Diese Spalte enthält detaillierte Informationen darüber, wie MySQL die Abfrage löst.

Sie können sehen, dass die ID-Abfrage den ID-Index verwendet. Der Abfragetyp ist die optimale konstante Abfrage. Versuchen wir dann eine andere Abfrage, diesmal mit dem Autorenindex.

Erklären Sie „Select * from articles where author = "Big Bear"“
1 EINFACH Artikel NULL ALLE Autor NULL NULL NULL 10 80.00 Verwendung von where

Es ist deutlich zu erkennen, dass der Autorenindex für eine einfache Abfrage verwendet wird. Der Abfragetyp ist der schlechteste vollständige Tabellenscan. Lassen Sie uns das nicht voreilig erklären. Verwenden wir stattdessen dieselbe Anweisung.

Erklären Sie „Select * from articles where author = "Autor"“
1 EINFACHE Artikel NULL ref Autor Autor 1022 const 2 100,00 NULL

Sie können sehen, dass der Abfragetyp dieses Mal ref ist.

Das heißt, da die Anzahl der vom Autor Daxiong geschriebenen Datenzeilen 30 % der Gesamtdaten übersteigt, glaubt MySQL, dass das vollständige Scannen der Tabelle schneller ist als die Verwendung von Indizes. Dies ist die Bedeutung der Konzepte Indexkardinalität und Indexselektivität. Daher sollten Sie beim Erstellen eines Index darauf achten, den Index für eine Spalte mit einer hohen Indexkardinalität zu erstellen.

Leser, die an weiteren MySQL-bezogenen Inhalten interessiert sind, können sich die folgenden Themen auf dieser Site ansehen: „Zusammenfassung der Kenntnisse im Bereich MySQL-Indexoperationen“, „Zusammenfassung der Kenntnisse im Bereich MySQL-Allgemeinfunktionen“, „Zusammenfassung der Kenntnisse im Bereich MySQL-Protokolloperationen“, „Zusammenfassung der Kenntnisse im Bereich MySQL-Transaktionsoperationen“, „Zusammenfassung der Kenntnisse im Bereich MySQL-gespeicherte Prozeduren“ und „Zusammenfassung der Kenntnisse im Zusammenhang mit MySQL-Datenbanksperren“.

Ich hoffe, dass dieser Artikel für jedermann beim Entwurf einer MySQL-Datenbank hilfreich ist.