After reading this chapter, you will learn the following:
1. What are the three associations?
2. When is that suitable for which? (summary)
There are three kinds of connection mechanisms
Nest loop nested loop (size table)
Sort merge sort merge (add index + two large tables)
Hash join hash (inline)
A simple explanation is given to the above figure. When nested loops are carried out, it is usually in the form of (large table + small table), and the small table will be used as the inner table. Because the small table can reside in memory, only the large table will generate overhead when disk IO is performed. On the contrary, if the large table is too large, the large table cannot reside in memory, and the data of both internal and external tables are stored in the disk In this way, the disk IO overhead is very “considerable”
After explaining who does the internal and external tables, let’s take a look at the operation process of nested Association
1. Get the table space to be operated on
Sort the spatial records of 2
3. Compete through nested loops (if it is a double table, it may be two for nested)
4. Obtain records of requirements
It has a good time complexity (if the elements in two tables have been sorted, if they are not sorted, the time cost may be xlogx + ylogy + X + y).
Then, how to associate the sorted elements
1. First of all, there will be one for each tablePointer to the head element
2. Compare two pointers, and if they are the same, the element is proposed
3. Compare the follow-up elements, the smaller one moves down first
4. So, until the end of each table
Hash Association (hash Association)
Hash association means that after hashing the table (not exceeding the memory size), another table is matched; generally speaking, the hashed table is still a small table, and the matching process needs to match all the elements.
According to the above three kinds of association methods, we can roughly understand the principles of certain SQL statements and the tuning mechanism, that is, why indexes are added, or not added, or the relationship between internal and external tables is changed sometimes.
Copyright notice: This article is CSDN blogger “angel”_ The original article of “coder” is in accordance with the copyright agreement of CC 4.0 by-sa. Please attach the link to the original source and this statement for reprint.
Link to the original text: study hard database connection and internal principles_ Anger_ Coder’s column – CSDN blog