So ermitteln Sie die Höhe des MySQL InnoDB B+-Baums

Vorwort

Der Grund, warum die InnoDB-Engine von MySQL B+Tree zum Speichern von Indizes verwendet, besteht darin, die Anzahl der Festplatten-E/A-Vorgänge bei Datenabfragen zu minimieren. Die Höhe des Baums wirkt sich direkt auf die Leistung der Abfrage aus. Im Allgemeinen ist eine Baumhöhe von 3 bis 4 Stockwerken besser geeignet. Der Zweck der Datenbankpartitionierung besteht auch darin, die Höhe des Baums zu steuern. Wie ermitteln Sie also die Höhe des Baums? Das folgende Beispiel zeigt, wie man die Höhe eines Baums ermittelt.

Probendatenaufbereitung

Die Anweisung zur Tabellenerstellung lautet wie folgt:

CREATE TABLE `Benutzer` (
  `id` int(11) NICHT NULL AUTO_INCREMENT,
  `name` varchar(100) ZEICHENSATZ latin1 STANDARD NULL,
  `Alter` int(11) DEFAULT NULL,
  Primärschlüssel (`id`),
  SCHLÜSSEL `Name` (`Name`),
  SCHLÜSSEL `Alter` (`Alter`)
) ENGINE=InnoDB STANDARD-CHARSET=utf8

Fügen Sie 1 Million Datensätze in die Tabelle ein. Die Daten lauten wie folgt:

mysql> wähle * ab Benutzerlimit 2\G
*************************** 1. Reihe ***************************
  ID: 110000
Name: ab
 Alter: 100
*************************** 2. Reihe ***************************
  ID: 110001
Name: ab
 Alter: 100
2 Zeilen im Satz (0,00 Sek.)

Ermitteln Sie die Höhe des Baums durch Abfragen der entsprechenden Datentabelle

Nehmen Sie MySQL 5.6 als Beispiel, um zu erklären, wie Sie die Höhe des Baums ermitteln.

Holen Sie sich zuerst die Seitennummer

mysql> SELECT b.name, a.name, index_id, Typ, a.space, a.PAGE_NO FROM information_schema.INNODB_SYS_INDEXES a, information_schema.INNODB_SYS_TABLES b WHERE a.table_id = b.table_id AND a.space <> 0 und b.name='test/user';
+-----------+---------+----------+-----------+-----------+----------+
| Name | Name | Index-ID | Typ | Leerzeichen | SEITEN-NR. |
+-----------+---------+----------+-----------+-----------+----------+
| Test/Benutzer | PRIMARY | 22 | 3 | 6 | 3 |
| Test/Benutzer | Name | 23 | 0 | 6 | 4 |
| Test/Benutzer | Alter | 24 | 0 | 6 | 5 |
+-----------+---------+----------+-----------+-----------+----------+
3 Zeilen im Satz (0,00 Sek.)

page_no ist die Seriennummer der Stammseite im Indexbaum. Die Bedeutung der weiteren Einträge finden Sie unter:
https://dev.mysql.com/doc/refman/5.6/en/innodb-sys-indexes-table.html

Seitengröße erneut lesen

mysql> zeige globale Variablen wie „innodb_page_size“;
+------------------+--------+
| Variablenname | Wert |
+------------------+--------+
| innodb_seitengröße | 16384 |
+------------------+--------+
1 Zeile im Satz (0,00 Sek.)

Lesen Sie abschließend die Höhe des Indexbaums

$ hexdump -s 49216 -n 10 ./user.ibd
000c040 0200 0000 0000 0000 1600
000c04a

Es kann festgestellt werden, dass PAGE_LEVEL 0200 ist, was bedeutet, dass die Höhe dieses sekundären Indexbaums 3 ist. Die folgenden 1600 sind der Index-ID-Wert des Indexes. Die Hexadezimalzahl 16 lässt sich in die Dezimalzahl 22 umwandeln. Diese 22 ist genau die Index-ID des obigen Primärschlüssels.
Wie wird 49216 im obigen Hexdump-Befehl berechnet? Die Formel lautet Seitennummer * Innodb-Seitengröße + 64.
3*16384+64=49216

Mit dieser Methode überprüfen wir die Höhen der anderen beiden Indizes.

$ hexdump -s 65600 -n 10 ./user.ibd
0010040 0100 0000 0000 0000 1700
001004a
$ hexdump -s 81984 -n 10 ./user.ibd
0014040 0200 0000 0000 0000 1800
001404a

Es ist ersichtlich, dass die Höhe des Namensindex 2 und die Höhe des Altersindex 3 beträgt.

Schätzung auf Basis der Indexstruktur

Wenn Sie keine Berechtigung für den Datenbankserver haben. Sie können die Höhe des Baums auch anhand der Datenbankindexstruktur schätzen.
Gemäß der B+Baumstruktur speichern Nicht-Blattknoten Indexdaten und Blattknoten speichern alle Daten jeder Zeile.
Die Größe jedes Indexelements eines Nicht-Blattknotens entspricht der Datengröße + Zeigergröße. Nehmen Sie an, dass die Zeigergröße 8 Byte beträgt. Die einzelnen Seiten werden nicht vollständig ausgefüllt, es bleibt also 1/5 des Platzes übrig. Als nächstes schätzen wir die Höhe der Namens- und Altersindizes.

Name Index Höhe Schätzung

Die Anzahl der pro Seite gespeicherten Indexeinträge für Nicht-Blattknoten. Die Seitengröße beträgt jeweils 16 KB. Der Wert des Namens ist ab. Belegt 2 Bytes. Die Größe jedes Datenelements beträgt 2+8=10 Bytes. Die Anzahl der Indexelemente, die auf jeder Seite gespeichert werden können, beträgt 16384 * 0,8 / 10 = 1310.
Die Anzahl der in jeder Blattknotenseite gespeicherten Indizes. Die Seitengröße beträgt jeweils 16 KB. Die Größe jedes Datenelements beträgt 4+2+8=14 Bytes. Die Anzahl der Indizes, die auf jeder Seite gespeichert werden können, beträgt 16384 * 0,8 / 14 = 936.
Zwei Ebenen können 1310*936=1226160 Datensätze speichern. Es ist ersichtlich, dass die Höhe des Baums unter 1,2 Millionen Datensätzen 2 beträgt.

Altersindex-Größenschätzung

Die Anzahl der pro Seite gespeicherten Indexeinträge für Nicht-Blattknoten. Die Seitengröße beträgt jeweils 16 KB. Alter ist vom Typ int. Belegt 4 Bytes. Die Größe jedes Datenelements beträgt 4+8=12 Bytes. Die Anzahl der Indexelemente, die auf jeder Seite gespeichert werden können, beträgt 16384 * 0,8 / 12 = 1092.
Die Anzahl der in jeder Blattknotenseite gespeicherten Indizes. Die Seitengröße beträgt jeweils 16 KB. Die Größe jedes Datenelements beträgt 4+4+8=16 Bytes. Die Anzahl der Indizes, die auf jeder Seite gespeichert werden können, beträgt 16384 * 0,8 / 16 = 819.
Zwei Schichten können 1092*819=894348 Datensätze speichern. Es ist ersichtlich, dass unter 900.000 Datensätzen die Höhe des Baums 2 beträgt. 1 Million Datensätze sind 3 Ebenen.

Andere Tools

Es gibt auch ein kleines Tool zum Ausprobieren. InnoDB-Tablespace-Visualisierungstool innodb_ruby

Oben sind die Einzelheiten des Beispiels zum Abrufen der Höhe des B+-Baums von MySQL InnoDB aufgeführt. Weitere Informationen zum B+-Baum von MySQL InnoDB finden Sie in den anderen verwandten Artikeln auf 123WORDPRESS.COM!