Detaillierte Erläuterung des Unterschieds zwischen gewöhnlichen Indizes und eindeutigen Indizes in MySQL

Dieser Artikel stellt den Unterschied zwischen gewöhnlichen Indizes und eindeutigen Indizes in MySQL vor. Ich hoffe, dass er für Freunde, die MySQL lernen, hilfreich sein wird.

Detaillierte Erklärung des Unterschieds zwischen gewöhnlichem Index und eindeutigem Index in MySQL

Der Unterschied zwischen Abfrage und Update

Es gibt keinen Unterschied in den Abfragefunktionen zwischen diesen beiden Indextypen. Der Hauptaspekt ist die Auswirkung auf die Update-Leistung. Es wird empfohlen, möglichst normale Indizes zu wählen.

(Kostenloses Lernvideo-Tutorial empfohlen: MySQL-Video-Tutorial)

1.1 MySQL-Abfragevorgang

■ Danach Der gewöhnliche Index

findet den ersten Datensatz, der die Bedingung erfüllt, und läuft weiter rückwärts, bis der erste Datensatz erreicht ist, der die Bedingung nicht erfüllt.

■ Eindeutiger Index

Da der Index die Eindeutigkeit definiert, wird die Suche direkt gestoppt, nachdem der erste Datensatz gefunden wurde, der die Bedingungen erfüllt.

Gewöhnliche Indizes werden noch einmal abgerufen, fast ohne Auswirkungen. Da InnoDB-Daten in Einheiten von Datenseiten gelesen und geschrieben werden, werden die Datensätze beim Lesen nicht direkt von der Festplatte gelesen. Stattdessen werden die Datenseiten zuerst in den Speicher gelesen und dann von den Datenseiten abgerufen.

Eine Datenseite ist standardmäßig 16 KB groß. Bei ganzzahligen Feldern kann eine Datenseite fast tausend Schlüssel enthalten. Sofern es sich bei den zu lesenden Daten nicht um den letzten Datensatz der Datenseite handelt, müssen Sie eine andere Datenseite lesen Diese Situation kommt selten vor und der CPU-Verbrauch ist grundsätzlich vernachlässigbar.

Daher gibt es hinsichtlich der Datenabfrage keinen Unterschied zwischen gewöhnlichen Indizes und eindeutigen Indizes.

1.2 MySQL-Aktualisierungsvorgang

Der Aktualisierungsvorgang aktualisiert die Daten auf der Festplatte nicht direkt. Er liest zuerst die Datenseite von der Festplatte in den Speicher und aktualisiert sie dann die Datenseite.

■ Gewöhnlicher Index

Lesen Sie die Datenseite von der Festplatte in den Speicher und aktualisieren Sie die Datenseite.

■ Eindeutiger Index

Lesen Sie die Datenseite von der Festplatte in den Speicher, bestimmen Sie, ob sie eindeutig ist, und aktualisieren Sie dann die Datenseite.

Da es in MySQL einen Änderungspuffermechanismus gibt, gibt es einen gewissen Unterschied bei der Aktualisierung gewöhnlicher Indizes und eindeutiger Indizes.

Der Zweck des Änderungspuffers besteht darin, IO-Vorgänge zu reduzieren und eine übermäßige Systemlast zu vermeiden. Der Vorgang des Schreibens von Daten auf die Datenseite im Änderungspuffer wird als Zusammenführen bezeichnet.

Wenn sich die Datenseite, die aktualisiert werden muss, im Speicher befindet, wird die Datenseite direkt aktualisiert. Wenn sich die Daten nicht im Speicher befinden, wird der Aktualisierungsvorgang zuerst im Änderungspuffer aufgezeichnet Wenn die Datenseite das nächste Mal gelesen wird, wird sie zusammengeführt. Auf der Datenseite verfügt der Änderungspuffer auch über eine reguläre Zusammenführungsstrategie. Die Zusammenführung wird auch während des normalen Herunterfahrens der Datenbank ausgelöst.

Für einen eindeutigen Index müssen Sie vor der Aktualisierung feststellen, ob die Daten eindeutig sind (dies kann nicht mit den Daten in der Tabelle wiederholt werden, wenn sich die Datenseite im Speicher befindet, können Sie sie direkt ermitteln und aktualisieren). Wenn es nicht im Speicher ist, müssen Sie es auslesen und prüfen, ob es eindeutig ist. Der Änderungspuffer wird nicht verwendet. Auch wenn sich die Datenseite nicht im Speicher befindet, muss sie dennoch gelesen werden.

Der Änderungspuffer nutzt den Speicher im Pufferpool und kann daher nicht unendlich vergrößert werden. Die Größe des Änderungspuffers kann dynamisch über den Parameter innodb_change_buffer_max_size festgelegt werden. Wenn dieser Parameter auf 50 eingestellt ist, bedeutet dies, dass die Größe des Änderungspuffers nur bis zu 50 % des Pufferpools belegen kann.

Schlussfolgerung: Ein eindeutiger Index kann keinen Änderungspuffer verwenden, es kann nur ein gewöhnlicher Index verwendet werden.

2. Der Unterschied zwischen Änderungspuffer und Redo-Log

2.1 Anwendbare Szenarien für Änderungspuffer

Änderung buffer Die Funktion besteht darin, die Häufigkeit von Aktualisierungsvorgängen und Cache-Aktualisierungsvorgängen zu reduzieren. Dies hat den Nachteil, dass Aktualisierungen nicht zeitnah erfolgen. Bei Tabellen mit häufigen Lesevorgängen wird die Verwendung des Änderungspuffers nicht empfohlen.

Da der Aktualisierungsvorgang gerade im Änderungspuffer aufgezeichnet wurde, wurde die Tabelle gelesen, die Datenseite wurde in den Speicher eingelesen und die Daten wurden sofort in die Datenseite eingefügt. Dies verringert nicht nur nicht den Leistungsverbrauch, sondern erhöht auch die Kosten für die Wartung des Änderungspuffers.

Geeignet für Tabellen mit mehr Schreibvorgängen und weniger Lesevorgängen.

2.2 Der Unterschied zwischen Änderungspuffer und Redo-Log

Lassen Sie uns ein Beispiel geben, um Redo-Log und Änderungspuffer zu verstehen. Wir führen die folgende SQL-Anweisung aus:

mysql> insert into t(id,k) values(id1,k1),(id2,k2);

Angenommen, (id1,k1) befindet sich auf Datenseite Seite 1 und (id2,k2) befindet sich auf Datenseite Seite 2. Und Seite 1 bleibt im Gedächtnis, Seite 2 nicht.

Der Ausführungsprozess ist wie folgt:

Schreiben Sie (id1,k1) direkt auf Seite 1;

Schreiben Sie „Write (id2) auf Seite 2“ in den Änderungspuffer ,k2)"Diese Nachricht;

Zeichnen Sie die beiden oben genannten Aktionen im Redo-Protokoll auf.

Nach Abschluss der oben genannten Schritte ist die Transaktion abgeschlossen. Die Kosten für die Ausführung dieser Aktualisierungsanweisung sind sehr gering, d. h. das Schreiben in zwei Speicherorte und das anschließende Schreiben auf eine Festplatte (die beiden Vorgänge zusammen schreiben eine Festplatte) und werden nacheinander geschrieben.

Diese Update-Anweisung umfasst vier Teile: Speicher, Redo-Log (ib_log_fileX), Datentabellenbereich (t.ibd) und Systemtabellenbereich (ibdata1).

如果要读数据的话，过程是怎样的？

mysql> select * from t where k in (k1, k2);

假设读操作在更新后不久，此时内存中还有 Page 1，没有 Page 2，那么读操作就和 redo log 以及 ibdata1 无关了。

从内存中获取到 Page 1 上的最新数据 (id1,k1)；

将数据页 Page 2 读入内存，执行merge 操作，此时内存中的 Page 2 也有最新数据(id2,k2)；

需要注意的是：

redo log中的数据，可能还没有 flush 到磁盘，磁盘中的 Page 1 和 Page 2 中并没有最新数据，但我们依然可以拿到最新数据（内存中的 Page 1 就是最新的，Page 2 虽然不是最新的，但是从磁盘读到内存中后，执行了merge操作，内存中的 Page 2 就是最新的了。）

如果此时 MySQL 异常宕机了，比如服务器异常掉电，change buffer 中的数据会不会丢？

change buffer 中的数据分为两部分，一部分是已经merge到ibdata1中的数据，这部分数据已经持久化，不会丢失。另一部分数据，还在 change buffer 中，没有merge 到ibdata1，分 3 种情况：

（1）change buffer 写入数据到内存，redo log 也已经写入（ib-log-filex），但是未 commit，binlog中也没有fsync到磁盘，这部分数据会丢失；

（2）change buffer 写入数据到内存，redo log 也已经写入（ib-log-filex），但是未 commit，binlog 已写入到磁盘，这部分不会多丢失，异常重启后会先从 binlog 恢复 redo log，再从 redo log 恢复 change buffer；

（3）change buffer 写入数据到内存，redo log 和 binlog 都已经fsync，直接从redo log 恢复，不会丢失。

redo log 主要节省的是随机写磁盘的 IO 消耗（转成顺序写），而 change buffer 主要节省的则是随机读磁盘的 IO 消耗

更多MySQL相关教程，请关注PHP中文网！

Das obige ist der detaillierte Inhalt vonDetaillierte Erläuterung des Unterschieds zwischen gewöhnlichen Indizes und eindeutigen Indizes in MySQL. Für weitere Informationen folgen Sie bitte anderen verwandten Artikeln auf der PHP chinesischen Website!