Prinzipien der Indizierung – am Beispiel von innodb

1. Vorab schreiben

Da die Entwicklungs- und Testaufgaben zu Ende gehen, erledigen alle einige vorbereitende Arbeiten vor der Veröffentlichung des Projekts. Eine der wichtigen Aufgaben ist die Erstellung einiger SQL-Anweisungen Der Index ist in einer Umgebung mit hoher Parallelität und hohem Datenverkehr sehr wichtig. Der Aufbau eines guten Index kann die Abfrageeffizienz von SQL-Anweisungen erheblich verbessern ? In diesem Artikel wird als Beispiel die MySQL-Innodb-Speicher-Engine verwendet und untersucht, wie ein guter Index basierend auf tatsächlichen Projekten erstellt wird.

2. Indexdefinition

Die offizielle Indexdefinition von MySQL lautet: Index (Index) ist eine Datenstruktur, die MySQL dabei hilft, Daten effizient zu erhalten. Durch Extrahieren des Satzstamms können Sie die Essenz des Index ermitteln: Der Index ist eine Datenstruktur.
Wir wissen, dass die Datenbankabfrage eine der wichtigsten Funktionen der Datenbank ist, wie zum Beispiel die folgende SQL-Anweisung: SELECT * FROM test_table WHERE id = 99; Der Datensatz mit der ID 99 kann aus der Tabelle test_table abgerufen werden.
Wir alle möchten Daten so schnell wie möglich abfragen, daher optimieren Designer von Datenbanksystemen die Abfragealgorithmen aus Sicht. Der grundlegendste Abfragealgorithmus ist natürlich die lineare Suche. Er durchläuft test_table und gleicht dann Zeile für Zeile ab, ob der Wert der ID 99 ist. Dieser Algorithmus mit einer Komplexität von O(n) ist offensichtlich schlecht, wenn die Datenmenge groß ist Die Entwicklung der Informatik hat viele bessere Suchalgorithmen hervorgebracht, wie z. B. die binäre Suche, die binäre Baumsuche usw. Wenn Sie eine kleine Analyse durchführen, werden Sie feststellen, dass jeder Suchalgorithmus nur auf eine bestimmte Datenstruktur angewendet werden kann. Beispielsweise erfordert die binäre Suche, dass die abgerufenen Daten geordnet sind, während die binäre Baumsuche nur auf binäre Suchbäume angewendet werden kann. Aber die Daten selbst Die Organisationsstruktur kann verschiedene Datenstrukturen nicht vollständig erfüllen (z. B. ist es theoretisch unmöglich, beide Spalten gleichzeitig in der richtigen Reihenfolge zu organisieren). Daher verwaltet das Datenbanksystem zusätzlich zu den Daten auch Datenstrukturen, die bestimmte Anforderungen erfüllen Suchalgorithmen verweisen in irgendeiner Weise auf Daten, sodass erweiterte Suchalgorithmen auf diesen Datenstrukturen implementiert werden können. Diese Datenstruktur ist ein Index.
Das obige Beispiel wird hauptsächlich verwendet, um die Rolle des Indexes kurz zu veranschaulichen. Die meisten Datenbanksysteme und Dateisysteme, einschließlich MySQL Innodb, wählen nicht die Binärbaumstruktur als Index, sondern verwenden B-Tree oder seine Variante B+ Tree wird als Indexstruktur verwendet, um die Anzahl der Festplatten-E/A-Zugriffe während des Suchvorgangs zu minimieren. Was B-Tree oder B+Tree ist und warum sie als Datenbankindexstrukturen ausgewählt werden, erfahren Sie hier und lernen. Im Folgenden stellen wir zunächst die beiden B+Tree-Indizes der MySQL-Innodb-Engine vor.

3. Mysql Innodb B+Tree-Index

Einer ist der Primärschlüsselindex, der Cluster-Index (Cluster-Index), der nicht nur den Primärschlüssel enthält, sondern auch alle Daten, zu denen der Primärschlüssel gehört. In Innodb ist der Primärschlüsselindex die Daten.

Einer ist ein Nicht-Primärschlüsselindex (Sekundärindex), in dem der Spaltenwert der Schlüssel und die Position des Primärschlüssels sind ist Wert, also (Spaltenwert, Primärschlüsselposition)

Innodb ist ein Index -organisierte Tabelle, und alle Daten werden unter dem Primärschlüssel-Blattknoten aufgehängt. Wenn die Einfügungsreihenfolge der Primärschlüssel nicht garantiert werden kann, kommt es daher zu einer großen Anzahl von Primärschlüsselknotenaufteilungen, was zu einer großen Anzahl von E/A-Vorgängen führt. Darüber hinaus schreibt Innodb vor, dass die Länge eines einzelnen Indexfelds 768 Bytes nicht überschreiten darf, andernfalls wird die Länge gekürzt und nicht in den Index aufgenommen. Die Nicht-Primärschlüsselindizes von Innodb verweisen alle auf den Primärschlüsselindex. Durch die Suche nach dem Nicht-Primärschlüsselindex kann nicht die gesamte Datenzeile ermittelt werden. Sie müssen die Position des Primärschlüsselindex über den Zeiger des Blattknotens ermitteln Daher muss der Primärschlüsselindex so klein wie möglich gestaltet werden. Andernfalls wird der Nicht-Primärschlüsselindex sehr groß.

4. Grundsätze der Indexierung

Als nächstes werfen wir einen Blick auf die Prinzipien, die befolgt werden müssen, um einen guten Index zu erstellen, und veranschaulichen dies anhand spezifischer Beispiele.
1 Das Prinzip der Übereinstimmung mit dem Präfix ganz links, ein sehr wichtiges Prinzip, mit dem MySQL immer übereinstimmt rechts, bis die Abfrage „Übereinstimmung bei Erreichen des Bereichs stoppen“ (>, von (a, b, c, d) wird D nicht für die Indizierung verwendet. Wenn Sie einen Index für (a, b, d, c) erstellen, kann die Reihenfolge von a, b, d angepasst werden willkürlich.
2. = und in können nicht in der richtigen Reihenfolge sein, z. B. a = 1 und b = 2 und c = 3. Sie können (a, b, c)-Indizes in beliebiger Reihenfolge erstellen. Der Abfrageoptimierer von MySQL hilft Ihnen bei der Optimierung den Index in eine erkennbare Form.
3. Versuchen Sie, Spalten mit hoher Unterscheidung als Indizes auszuwählen. Die Formel für die Unterscheidung lautet count(distinct col)/count(*), die den Anteil der Felder darstellt, die sich nicht wiederholen Wir scannen und die Eindeutigkeit Der Unterscheidungsgrad des Schlüssels beträgt 1, während der Unterscheidungsgrad einiger Status- und Geschlechtsfelder angesichts von Big Data 0 sein kann. Dann könnte jemand fragen, ob es einen empirischen Wert für dieses Verhältnis gibt. Verschiedene Verwendungsszenarien erschweren die Bestimmung dieses Werts. Im Allgemeinen erfordern wir, dass die Felder, die verbunden werden müssen, über 0,1 liegen, d „sauber“, wie from_unixtime (create_time) = „2015-08-14“ Der Grund dafür ist sehr einfach. Der b+-Baum speichert jedoch die Feldwerte in der Datentabelle Zum Vergleich müssen die Funktionen auf alle Elemente angewendet werden. Offensichtlich sind die Kosten zu hoch. Daher sollte die Anweisung als create_time = unix_timestamp(‘2015-08-14’) geschrieben werden.
5. Erweitern Sie den Index so weit wie möglich, erstellen Sie keinen neuen Index. Wenn beispielsweise bereits ein Index von a in der Tabelle vorhanden ist und Sie nun einen Index von (a, b) hinzufügen möchten, müssen Sie nur den ursprünglichen Index ändern.
6. Wenn Sie in der Order by- oder Group by-Klausel nach Index sortieren möchten, muss die Reihenfolge der Indexspalten mit der Reihenfolge der order by- oder Group by-Klausel und der Sortierrichtung aller Spalten übereinstimmen (umgekehrte Reihenfolge oder positive Reihenfolge) sind gleich; wenn die Abfrage mehreren Tabellen zugeordnet ist, kann der Index nur dann zum Sortieren verwendet werden, wenn die Felder, auf die in der order by-Klausel verwiesen wird, alle aus der ersten Tabelle stammen gruppieren nach-Anweisungen und Abfragetyp-Anweisungen sind das Gleiche: Das Präfixprinzip ganz links im Index muss erfüllt sein. Andernfalls führt MySQL einen Sortiervorgang durch und kann den Index nicht zum Sortieren verwenden Die by-Klausel erfüllt nicht das Präfixprinzip ganz links, d.

5. Beispiele

Aussage 1:

Aussage 2:

Für diese beiden Kontoauszüge können Sie bei getrennter Betrachtung zwei Indizes erstellen.

Erstellen Sie (Status, Netting_Batch_No, Debtor_Agent_Member_ID). Debtor_agent_member_id,transaction_currency);
Wenn Sie alles berücksichtigen, reicht tatsächlich ein Index aus, nämlich (netting_batch_no,debtor_agent_member_id). Es besteht keine Notwendigkeit, die Felder „status“ oder „transaction_currency“ in den Index aufzunehmen, da diese beiden Felder unterschiedlich sind ist zu schlecht;
Nach dem Indexierungsprinzip 2 kann Aussage 1 zu diesem Index gehen.
Nach dem Indexierungsprinzip 1 kann auch Aussage 2 zu diesem Index gehen . Wenn Sie zu viele Indizes erstellen, erhöht sich der Verbrauch an Datenbankspeicher oder Datenträger und die Leistung von Vorgängen wie dem Einfügen und Löschen wird beeinträchtigt. Sie müssen die Prinzipien der Indexerstellung befolgen und alles berücksichtigen > Das Obige sind die Prinzipien der Indizierung – am Beispiel von innodb. Weitere verwandte Inhalte finden Sie auf der chinesischen PHP-Website (www.php.cn)!