Database performance optimization join method description

We can see that many of my friends still haven't broken through a bottleneck in understanding and understanding the database, and this bottleneck is often just a piece of window, and you will see a new world.

During project creation in 2000, SQL Server 8 million and core tables (most frequently used key functions are required each time) reached of the data volume, I checked some related tables a long time ago, and some of them reached more than 30 million. The disk uses a fiber-optic disk with GB space. data must be backed up and transferred every week. Otherwise, the GB space will be full in a week. This system has been running for several years, it still maintains excellent performance. I have also heard that my friend's SQL Server 2000 database is working in dozens of TB environments and has a high concurrency. I am still far away from the ability to control this level.

When I think about SQL Server, I think its performance cannot be compared with that of Oracle. When I mention Big Data Processing, I think of oracle. Along the way, I recorded a lot of optimization post on the local blog, right and wrong, and there was no time series to sort it out. This articleArticleSort out the concept of the join method for your reference. By checking the information to understand the various concepts mentioned in it, we can continue to verify and summarize the concepts in practice, so that we can fully understand the database step by step.

I only know more about SQL Server 2000, but this does not prevent me from optimizing SQL statements and product architecture in Oracle and MySQL, because in terms of the theoretical principles of databases, the major databases are not very similar, a deep understanding of the database will not affect your architecture design philosophy. On the contrary, it will bring you deeper thinking.

Description of the Execution Plan

Select Show execution plan from the query menu of the SQL Server Query analyzer and run the SQL query statement. The results window contains three tabs: grid, execution plan, and messages. The execution plan in graphical form is in the order from right to left, which is also the execution order. Each icon in the execution plan represents an operation. Each operation has one or more inputs and one or more outputs. Input and Output may be a physical data table, index data structure, or intermediate result set/Data Structure during execution. Move the mouse over the icon to display the operation details, for example, the logical and physical operation name, the number and size of records, the I/O cost, the CPU cost, and the specific expression of the operation (the argument parameter ). Move the cursor over the connection arrow to display the number of records and the size of records in the Operation output result set at the start of the arrow. Generally, this output result set can be understood as the input at the end of the arrow.

In addition, some supplementary instructions on the Execution Plan: 1. the information displayed in the execution plan is an "evaluation" result, not 100% accurate information. For example, the number of records is taken from the statistical information, the I/O cost and CPU cost come from the evaluation results obtained based on statistics during execution plan generation. 2. the execution plan is not necessarily accurate. On the one hand, it is affected by the accuracy of statistics maintained by SQL Server, and on the other hand, the environment of SQL statement compilation and execution time (memory usage, CPU status, etc) may be different.

I/O cost and CPU cost evaluation, SQL statement compilation and execution process are not discussed here. In addition, although the execution plan is not necessarily accurate, it is still the most important basis for SQL statement analysis, because you can understand that in most cases, SQL Server is executed in this way.

 

 

Join method description

In the database, how do SQL statements such as tablea inner join tableb and tablea left out join tableb execute the join operation? What does SQL server use?AlgorithmHow to join two tables?

SQL Server 2000 has three methods: nested loop, merge, and hash. Oracle also uses these three methods. However, the conditions for using the nested loop in Oracle are a little different from those in SQL Server. The memory management mechanism is different from that in SQL Server. Therefore, you can view the execution plan, in Oracle, the nested loop is used a lot, while the merge and hash methods are relatively small. in SQL Server, the merge and hash methods are very common.

Take SQL Server 2000 as an example to describe these three methods, interspersed with some preliminary use of the execution plan.

1. nested loop join

1.1 example SQL

 

Select... from tablea inner join tableb on tablea. col1 = tableb. col1 where tablea. col2 =? And tableb. col2 =? No index is created in tablea, and a primary key (clustered index) is created on col1 ).

1.2 algorithm pseudoCodeDescription

 

Foreach ROWA in tablea where tablea. col2 =? {Search rowsb from tableb where tableb. col1 = Rowa. col1 and tableb. col2 =? ; If (rowsb. Count <= 0) discard ROWA; elseoutput ROWA and rowsb ;}

The Join Operation has two inputs. In the preceding example, tablea is the outer input used for the outer loop, and tableb is the inner input used for the internal loop. The following describes the specific steps for SQL Server to complete this operation for the execution plan.

1.3 view the execution plan Method

Move to the beginning of the article.

1.4 procedure

The following figure shows the execution plan of the example SQL statement. On the Right of the nested loop operation, outer input is located on the top and inner input is located on the bottom. You cannot determine the relationship between outer input and inner input based on the table that appears in the join operation, but you must determine from the execution plan, because the SQL server automatically selects the inner input.

 

A) Perform table scan on tablea. The input of this operation is the data in Table A. The data is stored on the disk and loaded to the memory during the operation. The output is the outer input of the qualified record set, which will be used as B. In this operation, tablea. col1 =? .

B) Execute the nested loop operation described in the preceding pseudocode. Perform Step C for each output record in ).

C) Perform the clustered index seek operation on tableb. This operation is performed in the nested loop, and the input is the clustered index data of Table B. It uses tableb. col1 = Rowa. col1 and tableb. col2 =? Select the matching results from the clustered index of tableb.

 

D) construct the returned result set. From the output of the nested loop, sort out the specified fields in the SELECT statement to construct the final output result set.

1.5 advanced description

 

The preceding example uses clustered indexes for inner input. The following describes non-clustered indexes to enhance the understanding and analysis capabilities of the execution plan.

 

Modify the primary key of Table B col1 to a non-clustering mode. The execution plan of the SQL statement in this example is as follows.

 

The preceding steps a), B), and C are the same as those in 1.4. Note that step C is used to perform the index seek operation, it is different from clustered index seek. The root node of the clustered index is each actual data record, and the root node of the non-clustered index is a reference to the root-node key value of the clustered index (if the table has a clustered index ), or reference the rowid of the actual data record (a table without clustered indexes, which is called a heap table ). After clustered index seek is executed, the actual physical data records have been loaded into the memory. After the index seek operation, the actual physical data records are not loaded, instead, it is only the root node data of non-clustered indexes, which only contains the index field data and the referenced clustered index key value or rowid. In this step, SQL server uses the index field value in the root node data of the non-clustered index to match the field associated with the record (ROWA) in the outer input to determine whether the result meets the criteria, if yes, the data structure of the root node of the non-clustered index is saved to the output data structure of the nested loop operation, and a bookmark is created ), indicates that you need to obtain the referenced data based on the bookmarks when necessary.

D) execute the bookmark lookup operation. The output of the nested loop operation is a memory data structure. Before you sort the output result set of the entire query statement from the memory data structure, you need to handle the bookmark reference problem, the bookmark lookup operation is to obtain detailed record data based on the clustered index key value or rowid referenced in the bookmarks.

E) filter. Review the previous operations. During the execution of the nested loop, only the index fields of Non-clustered indexes (tableb. col1) matches with the field associated with outer input, so far tableb has not been used. col2 =? This operation uses tableb. col2 =? Filter the output of bookmark lookup.

 

The person who looks at it carefully may have a few questions after coming here, 1. tablea. col2 =? Why is there no filter operation? 2. Why didn't the filter operation appear in 1.4? 1. The table scan operation is performed on tablea, which directly scans the actual data. During this scan, tablea. col2 =? This condition is filtered to avoid an additional filter operation. Move the cursor to the table scan operation. tablea. col2 =? is displayed in the prompt parameter (argument? Has been applied. 2. As mentioned earlier, the root node of the clustered index is the actual data record. When clustered index seek is executed, the actual data record is finally scanned. In this process, tableb. col2 =? This condition also avoids an additional filter operation. This is why the filter operation is not performed in MySQL 1.4.

F) construct the returned result set. Same as step d in step 2.

1.6 nested loop usage conditions

If any Join Operation meets the nested loop usage conditions, the SQL server will evaluate the cost of the nested loop (I/O cost, CPU cost, etc.) during the query optimization process, determine whether to use this join method based on the evaluation results.

The condition for using the nested loop method is: a) the number of records in the outer input is not large, preferably below 1000-2000. Generally, it is hard to say that the number of records exceeds 3000. The nested loop is basically not selected. B) as an inner input table, there are indexes that can be used for this query.

This is because the number of outer input records is small, which means that the number of Outer Loops is relatively small; the inner input has available indexes, which means that when the inner input table searches for matching records in the loop, efficiency is very high, even if the inner input table actually records millions of records. Based on these two conditions, the execution efficiency of the nested loop is very high. Among the three join modes, the execution efficiency is the least memory and CPU consumption (except when the nested loop mode is forcibly specified ).

Note: The number of records in outer input is not the actual number of records in the outer input table. For example, in the example SQL, if tablea has maintenance statistics on col2 (there are indexes of col2 or separately maintained statistics), and tablea. col2 =? If the condition value of is in the form of Sarg (searchable parameters), SQL Server can use the statistical information and condition value to evaluate the number of records that meet the condition at the time of compilation, the query execution time meets the condition tablea. col2 =? Is used for the outer loop. The inner input table has available indexes, which are the fields (one or more fields) associated with the outer input table in the inner input table) can hit an index (some or all of these fields appear before an index field ).

It does not mean that SQL Server 100% selects the nested loop. Because the SQL Server query optimizer is based on cost evaluation, if the cost evaluated by other schemes is better than this, SQL Server selects other join methods. For example, if the index that meets the conditions on inner input is a non-clustered index, SQL Server may need an additional bookmark lookup operation to obtain the actual record data. If the inner input table has a large data volume, when the index fragmentation level is very high, the bookmark lookup cost may be very high. SQL server will try to evaluate the selection of other join solutions.

 

1.7 force specify the nested loop method

If you use the loop keyword, for example, tablea inner loop join tableb, SQL Server is forced to use the nested loop method to perform this join operation. You can also use the option. For example, the tablea inner join tableb option (loop join) nested loop algorithm has its applicability. The efficiency within this range is the highest, but the efficiency beyond this range is poor, unless you are very sure, do not forcibly specify the join method.

Next we will not go into detail like above.

 

 

2. Merge join

The first step of merge join is to ensure that the two joined tables are sorted by the joined fields. If the associated fields have available indexes and the sorting is consistent, you can directly perform the merge join operation. Otherwise, SQL Server needs to sort the joined tables by the joined fields first (that is, on the two inputs before merge join, A sort operation may be executed before merge join ).

After the two tables are sorted by the joined fields, the merge join operation starts to match one record in each table. If the Join Operation meets the joined conditions, it is placed in the result set. Otherwise, discard a record with a small associated field value, and retrieve the next record from the corresponding table of this record to continue matching until the entire cycle ends.

When performing merge join on many-to-many joined tables, temporary tables are usually required. For example, when a join B uses merge join, if a group of values of the joined field exist in A and B, there are multiple records A1, a2... an, B1, B2... BN, then A1, a2... an, all must be in B for all equal records B1, B2... BN is matched once. In this way, the pointer needs to be moved from B1 to bn multiple times, and the corresponding B1. .. bn record needs to be read each time. Pre-read B1.. BN records into a temporary memory table, which is faster than reading from the original data page or disk.

The merge join operation itself is very fast, but sorting before merge join may be quite time-consuming (SQL server consumes the most memory and CPU operations, one is big data sorting, one is the hash operation of big data, which refers to the sort and hash-related operations in the query plan, such as hash join and distinct operations implemented using the hash algorithm, rather than the order by keyword in your SQL statement), especially for record sets with a very large data volume, resulting in a very high query cost for using merge join. For tables with a large data volume, if the joined fields of merge join can use clustered indexes, merge join is one of the fastest join methods. Therefore, the optimization scheme is to design the association relationship and the index structure of the table at the table structure design level. SQL statements make full use of indexes to minimize the sort operations before merge join and reduce bookmark lookup operations.

 

In general, if the nested loop condition cannot be met, the evaluation of the merge join method will be considered. The selection of merge join mainly considers the data size of two inputs and whether the associated fields can hit the index. For example, tablea join tableb, the associated fields can hit the index in both tables. If the data volume exceeds the Selection Range of the nested loop, the merge join method is considered. Of course, if the amount of data in tablea and tableb is too large, resulting in an excessively high cost, it will give up merge join and evaluate hash join.

Use Inner merge join or option (merge join) to force the merge join method.

3. Hash join

Hash join has two inputs: Build input (also called outer input) and probe input (also called inner input), not only used for inner/left/right join, operations such as Union/group by are also performed using hash join. In group by, both build input and probe input are in the same record set.

Similar to the nested loop, In the execution plan, build input is located above, and probe input is located below.

The hash join operation is completed in two phases: Build and probe.

 

Build stage

In this phase, we mainly construct a hash table. In Inner/left/right join operations, the joined fields of the table are used as the hash key. In the group by operation, the group by field is used as the hash key; in union or some other operations to remove duplicate records, the hash key includes all Select fields.

 

The build operation extracts each row of records from the build input, and uses the hash function to generate the hash value for the values of the fields associated with this row of records, the hash value corresponds to the hash buckets in the hash table ). If a hash value corresponds to multiple hash buckts, these hash buckets are connected using the linked table data structure. After the entire build input table is processed, all records in build input are referenced/associated with hash buckets in the hash table.

 

Probe stage

At this stage, SQL Server extracts each row of records from the probe input, also records the values of fields associated with this row, and generates hash values using the same hash function in the build phase, based on the hash value, search for the corresponding hash bucket from the hash table constructed in the build phase. In the hash algorithm, to solve the conflict, the hash bucket may be linked to another hash bucket. The probe action searches the hash bucket on the entire conflict chain for matching records.

 

For details about the hash algorithm, you can view some data about the data structure. The hash algorithm is mainly used for searching large amounts of data. To avoid search matching in all data like merge join every time, the appropriate hash function is used, first, the data to be searched is indexed Based on the hash key. During the search, the hash value is used to locate a small search range, search for matching results in this range to improve efficiency.

SQL Server uses a table with a small amount of data as build input. Try to make the hash table constructed based on build input fully stored in the memory, in this way, the matching operation in the probe stage is fully performed in the memory, and such a hash join is called in-memory hash join.

If the number of build input records is very large and the created hash table cannot be stored in the memory, SQL Server splits build input and probe input into multiple partitions (partition ), each partition includes an independent and paired build input and probe input. In this way, a large hash join is split into multiple independent hash joins with no mutual impact, the hash Join Operation for each partition can be completed in the memory. SQL Server saves the split partition file on the disk. Each time it loads the build input and probe input of a partition into the memory, it performs a hash join operation. This type of hash join is called Grace hash join, which uses the grace hash join algorithm.

Along with the hash Join Operation of big data, there will also be standard external merge sorts, multiple merge levels, multiple partitioning steps, multiple partitioning levels, SQL Server may also use algorithms such as recursive hash join or other optimization methods.

 

Hash join is generally used for operations on a large amount of data. For example, if the data of a table in the join operation reaches a certain level or cannot be loaded to the memory at a time, in addition, if your joined fields cannot hit indexes in both join tables, hash join is also used for processing. Therefore, the hahs join processing cost is usually very high, and it is one of the top killer of the memory and CPU of the database server, especially when it involves partitions (a large amount of data leads to insufficient memory, or the current processing thread cannot obtain enough memory due to high concurrent access, so partitions may be required if the data volume is not extremely large.) to complete all the partitioning steps as soon as possible, A large number of asynchronous I/O operations will be used, so a single thread during this period may cause multiple disk drives to be busy, which may block the execution of other threads.

Use Inner hash join or option (hash join) to force the hash join method.

> Suggestion

 

All three join methods have two inputs. Basic Principles of optimization: 1. Avoid hash join of big data and convert it into efficient merge join and nested loop join. Possible means include table structure design, index adjustment design, SQL optimization, and business design optimization. For example, when redundant fields are used, the statistical analysis results are periodically run to static tables using the service, redundant tables are appropriately used, and AOP or similar mechanisms are used for Synchronous updates. 2. Try to reduce the data volume of the join two inputs. This is a common problem: the condition does not match Sarg (a lot of superb skills can be used to this point ), the internal conditions of the subquery are insufficient (the SQL Server query optimizer is often stupid when the SQL is too complex, and the conditions written outside the subquery are not used in the subquery, it affects the internal efficiency of the subquery or the efficiency when the subquery is joined again ). In addition, the design and business end try to limit the data size of these two inputs.

For business design optimization, refer to the previous post article: System Analysis and Design of a join problem solution.

Supplement: SQL Server 2005

After reading about SQL Server 2005, the execution plan display is indeed different, but the main part or principle is similar and there will be no deviation. The preceding SQL example shows the execution plan of SQL Server 2005 when non-clustered indexes are used on table B.

One major difference is the bookmark lookup operation under SQL Server 2000, which is shown as an RID lookup operation + A nested loops operation implementation under 2005. In fact, this is well understood, it can be said that the execution plan is more reasonable. As soon as you see this operation, you will know that it uses a loop mechanism to obtain actual data from tableb.

 

Another point is that after you move the cursor over the execution plan icon, some changes are prompted. For example, the output list of each operation is displayed in 2005 ), in my previous articles, the words "Output Data Structure" are basically used for expression. By viewing the output list, you can better understand the reasons for operations like RID Lookup (bookmark lookup.

Finally, you can save the execution plan displayed in the graph in 2005. You can enable it later and view and analyze it in a graphical way. This is not possible under 2000, 2000 only execution plan text can be saved. These small functions are very useful for analyzing SQL Performance and more intuitive analysis on the graphic interface.