Fortgeschrittenes MySQL-Lernen: Vertiefendes Verständnis der drei Join-Algorithmen

Dieser Artikel ist eine fortgeschrittene Studie über MySQL. Er wird das Prinzip der Join-Verbindung und die drei Algorithmen des Joins im Detail vorstellen.

Bei der Abfrage mehrerer Tabellen verwenden wir häufig Joins. Tatsächlich ist die Effizienz von Joins nicht gut, daher sollten wir versuchen, die Verwendung von Joins zu vermeiden Es gibt einen Join-Algorithmus, Nested-Loop Join, der viele Varianten des Algorithmus aufweist, was die Ausführungseffizienz des Joins tatsächlich verbessert. [Verwandte Empfehlungen: MySQL-Video-Tutorial]

1. Simple Nested-Loop Join (Simple Nested-Loop Join)

Simple Nested-Loop Join (NLJ) aus der ersten Tabelle in der Schleife Lesen Sie eine Zeile Nacheinander wird jede Zeile an eine verschachtelte Schleife übergeben, die aus Konsistenzgründen mit den Daten übereinstimmt. Beispielsweise ist die SQL-Anweisung für die Benutzertabelle und die gesteuerte Tabelle UserInfo select * from User u left join User_info info on u.id = info.user_id Tatsächlich wird dies bei uns häufig verwendet Die Logik des Pseudocodes sollte sein: Es handelt sich um einen einfachen und groben Algorithmus. Jedes Mal, wenn ein Datenelement aus der Benutzertabelle entnommen wird, werden alle Datensätze in User_info auf Übereinstimmung überprüft und schließlich werden die Daten zusammengeführt und kehrte zurück. select * from User u left join User_info info on u.id = info.user_id，其实就是我们常用的for循环，伪代码的逻辑应该是

for(User u:Users){
    for(UserInfo info:UserInfos){
        if(u.id == info.userId){
            // 得到匹配数据
        }
    }
}

简单粗暴的算法，每次从User表中取出一条数据，然后扫描User_info中的所有记录匹配，最后合并数据返回。

假如驱动表User有10条数据，被驱动表UserInfo也有10条数据，那么实际上驱动表User会被扫描10次，而被驱动表会被扫描10*10=100次(每扫描一次驱动表，就会扫描全部的被驱动表)，这种效率是很低的，对数据库的开销比较大，尤其是被驱动表。每一次扫描其实就是从硬盘中读取数据加载到内存中,也就是一次IO，目前IO是最大的瓶颈

2. Index Nested-Loop Join(索引嵌套循环连接)

索引嵌套循环是使用索引减少扫描的次数来提高效率的，所以要求非驱动表上必须有索引才行。

在查询的时候，驱动表(User) 会根据关联字段的索引进行查询，当索引上找到符合的值，才会进行回表查询。如果非驱动表(User_info)的关联字段(user_id)是主键的话，查询效率会非常高(主键索引结构的叶子结点包含了完整的行数据(InnoDB))，如果不是主键，每次匹配到索引后都需要进行一次回表查询(根据二级索引(非主键索引)的主键ID进行回表查询)，性能肯定弱于主键的查询。

上图中的索引查询之后不一定会回表，什么情况下会回表，这个要看索引查询到的字段能不能满足查询需要的字段，具体可以参考之前的文章：你需要知道的一些索引基础知识 和 B+树的索引知识

3. Block Nested-Loop Join(缓存块嵌套循环连接)

如果存在索引，那么会使用index的方式进行join，如果join的列没有索引，被驱动表要扫描的次数太多了，每次访问被驱动表，其表中的记录都会被加载到内存中，然后再从驱动表中取一条与其匹配，匹配结束后清除内存，然后再从驱动表中加载一条记录 然后把被驱动表的记录在加载到内存匹配，这样周而复始，大大增加了IO的次数。为了减少被驱动表的IO次数，就出现了Block Nested-Loop Join的方式。

不再是逐条获取驱动表的数据，而是一块一块的获取，引入了join buffer缓冲区，将驱动表join相关的部分数据列(大小是join buffer的限制)缓存到join buffer中，然后全表扫描被驱动表，被驱动表的每一条记录一次性和join buffer中的所有驱动表记录进行匹配（内存中操作），将简单嵌套循环中的多次比较合并成一次，降低了非驱动表的访问频率。

驱动表能不能一次加载完，要看join buffer能不能存储所有的数据，默认情况下join_buffer_size=256k,查询的时候Join Buffer 会缓存所有参与查询的列而不是只有join的列，在一个有N个join关联的sql中会分配N-1个join buffer。所以查询的时候尽量减少不必要的字段，可以让join buffer中可以存放更多的列。

可以调整join_buffer_size的缓存大小show variables like '%join_buffer%'

Wenn der Treibertabellenbenutzer 10 Daten enthält und die gesteuerte Tabelle UserInfo ebenfalls 10 Daten enthält, wird der Treibertabellenbenutzer tatsächlich 10 Mal gescannt, und die gesteuerte Tabelle wird 10 * 10 = 100 Mal gescannt (Jedes Mal, wenn die Treibertabelle gescannt wird, werden alle gesteuerten Tabellen gescannt.) Diese Effizienz ist sehr gering und der Overhead für die Datenbank ist relativ groß, insbesondere für gesteuerte Tabellen.

Jeder Scan liest eigentlich Daten von der Festplatte und lädt sie in den Speicher, was ein IO ist. Derzeit ist IO der größte Engpass

🎜🎜🎜🎜🎜2. Index Nested-Loop Join (Index Nested-Loop Join)🎜🎜🎜🎜Index Der verschachtelte Satz von Schleifen verwendet Indizes, um die Anzahl der Scans zu reduzieren und die Effizienz zu verbessern. Daher muss die nicht gesteuerte Tabelle über einen Index verfügen. 🎜🎜Bei der Abfrage führt die Treibertabelle (Benutzer) eine Abfrage basierend auf dem Index des zugehörigen Felds durch. Wenn ein passender Wert im Index gefunden wird, wird die Tabellenabfrage durchgeführt. Wenn das zugehörige Feld (user_id) der nicht gesteuerten Tabelle (User_info) der Primärschlüssel ist, ist die Abfrageeffizienz sehr hoch (die Blattknoten der Primärschlüssel-Indexstruktur enthalten vollständige Zeilendaten (InnoDB)). Nicht der Primärschlüssel, der Index wird jedes Mal abgeglichen. Schließlich ist eine Tabellenrückgabeabfrage erforderlich (eine Tabellenrückgabeabfrage wird basierend auf der Primärschlüssel-ID des Sekundärindex (Nicht-Primärschlüsselindex) durchgeführt) und die Leistung ist definitiv schwächer als die Primärschlüsselabfrage. 🎜🎜🎜🎜Up Die Indexabfrage in der Abbildung gibt möglicherweise nicht unbedingt die Tabelle zurück. Dies hängt davon ab, ob die vom Index abgefragten Felder die von der Abfrage geforderten Felder erfüllen können : 🎜Einige Index-Grundlagen, die Sie kennen müssen. Kenntnisse und Indexkenntnisse des B+-Baums Wenn die Join-Spalte keinen Index hat, muss die gesteuerte Tabelle zu oft gescannt werden. Bei jedem Zugriff auf die gesteuerte Tabelle werden die Datensätze in der Tabelle in den Speicher geladen und dann ein Datensatz Nachdem der Abgleich abgeschlossen ist, wird der Speicher gelöscht und dann ein Datensatz aus der Treibertabelle geladen. Anschließend wird der Datensatz der gesteuerten Tabelle zum Abgleichen in den Speicher geladen Auch hier erhöht sich die Anzahl der IOs erheblich. Um die Anzahl der IOs in der gesteuerten Tabelle zu reduzieren, wurde die Block Nested-Loop Join-Methode entwickelt. 🎜🎜Die Daten der Treibertabelle werden nicht mehr einzeln, sondern Stück für Stück abgerufen. Der Join-Puffer wird eingeführt, um einige Datenspalten im Zusammenhang mit dem Join der Treibertabelle zwischenzuspeichern (die Größe ist die Grenze des Joins). Puffer) in den Join-Puffer und dann vollständiger Tabellenscan der gesteuerten Tabelle, jeder Datensatz der gesteuerten Tabelle wird mit allen Treibertabellendatensätzen im Join-Puffer auf einmal abgeglichen (In-Memory-Vorgang), mehrere Vergleiche in einer einfachen verschachtelten Schleife werden zu einem zusammengeführt, wodurch die Kosten für Nichtzugriffe reduziert werden. Wie oft wird auf die Treibertabelle zugegriffen? 🎜🎜🎜🎜Treiber Ob die Tabelle auf einmal geladen werden kann, hängt davon ab, ob der Join-Puffer alle Daten speichern kann. Standardmäßig join_buffer_size=256k speichert der Join-Puffer bei der Abfrage stattdessen alle an der Abfrage beteiligten Spalten Von nur den Join-Spalten werden in einer SQL mit N Join-Zuordnungen N-1 Join-Puffer zugewiesen. Versuchen Sie daher bei der Abfrage, unnötige Felder zu reduzieren, damit mehr Spalten im Join-Puffer gespeichert werden können. 🎜🎜Sie können die Cache-Größe von join_buffer_size anpassen Variablen wie „%join_buffer%“ anzeigen Dieser Wert kann entsprechend der tatsächlichen Situation geändert werden. 🎜🎜🎜🎜

Um den Block Nested-Loop Join-Algorithmus zu verwenden, müssen Sie die Einstellung „optimierer_schalter“ der Optimierungsverwaltungskonfiguration „block_nested_loop“ auf „Ein“ setzen, was standardmäßig aktiviert ist. Verfügbar über show variables like '%optimizer_switch%' 查看block_nested_loop Status.

Sie können die oben genannten drei Algorithmen verstehen. Solange wir Indizes gut nutzen können, müssen wir darauf achten, ob die zugehörigen Felder vorhanden sind sind indiziert, oder wir müssen gut darin sein, Indizes zu verwenden, um Abfragen effizienter zu gestalten.

Ursprüngliche Adresse: https://juejin.cn/post/7014105037517357093

Autor: Herr Ji

Weitere Programmierkenntnisse finden Sie unter: Einführung in die Programmierung! !

Das obige ist der detaillierte Inhalt vonFortgeschrittenes MySQL-Lernen: Vertiefendes Verständnis der drei Join-Algorithmen. Für weitere Informationen folgen Sie bitte anderen verwandten Artikeln auf der PHP chinesischen Website!