Database performance tuning Technology

I. I will provide a detailed description of these content in several subsequent articles. Before that, we must first understand how to understand the database execution plan. This article only describes the execution plan of a single table operation. DM database, oracle database, and sqlserver database can all display the execution plan of a given statement. I have analyzed these three numbers in detail.

I. I will provide a detailed description of these content in several subsequent articles. Before that, we must first understand how to understand the database execution plan. This article only describes the execution plan of a single table operation. DM database, oracle database, and SQL server database can all display the execution plan of a given statement. I have analyzed these three numbers in detail.

I. Overview

These content will be described in detail in several subsequent articles. Before that, we must first understand how to understand the database execution plan. This article only describes the execution plan of a single table operation. DM database, oracle database, and SQL server database can all display the execution plan of a given statement. I have analyzed in detail the execution plans of these three databases, and there is no essential difference between them. Therefore, the content in this article is suitable for these three databases. Similarly, it should be suitable for the vast majority of other databases.

A deep understanding of Single-Table execution is the basis for understanding the multi-Table execution plan. The DM database displays more information when displaying the execution plan. Therefore, in this article, I chose DM database as an instance database to explain the principles of the execution plan. After reading this article, you should be able to read the single table execution plans of these three databases.

2. in-depth understanding of database execution plans

DM database has two display modes: graphical display mode and text display mode. The second method is used for explanation. Understanding the execution plan is an important step towards understanding the database performance tuning. From the execution plan, we can see how the database executes the query statement, and determine whether the execution of the query statement is efficient Based on the execution plan, and how to optimize it.

Here are some examples to understandDatabase execution plan.

1. How to perform full table scan and filtering without indexes?

Construction execution scenario:

Create table t1 (c1 int, c2 int );

Insert into t1 values (1, 1 );

Insert into t1 values (2, 2 );

Insert into t1 values (3, 3 );

Insert into t1 values (4, 4 );

Insert into t1 values (5, 5 );

Insert into t1 values (6, 6 );

The query statement is:

Select*FromT1WhereC1 = 2;

The execution process of this statement can be described as follows:

1) if this step is performed for the first time, the first record of the table is obtained; otherwise, the next record of the current record is obtained. If the record has been scanned, perform Step 4; otherwise, perform step 2.

2) Determine whether the record meets the filtering condition c1 = 2. If yes, perform Step 3. Otherwise, perform step 1.

3) Place the record in the result set and perform step 1.

4) return the result set to the client.

In fact, the process of executing a query statement in a database is similar. The execution plan of the query statement is as follows:

# RSET: [21, 1, 1];

# XFLT: [0, 0, 0]; EXPR0 = 2

# CSEK: [21, 1, 1]; INDEX33555545 (T1), FULL_SCAN

What appears in the execution plan is explained here:

1)CSEK(Search) similar to Step 1 described above, the content in square brackets is the evaluation cost for performing this operation, which is not analyzed in this article. "INDEX33555545 (T1)" indicates that the clustered index of table T1 is used. "FULL_SCAN" indicates that the clustered index INDEX33555545 (T1) is fully scanned. Note that tables in the DM database are indexed by default. If cluster primary key is specified during table creation, the data is organized by the clsuter primary key; otherwise, the data is organized by rowid.

2)XFLT(Filter) similar to step 2 described above, "EXPR0 = 2" is a filter condition.

3)RSET(Result set) similar to Step 3 described above, used to store a set of qualified records.

We can see that the database execution process is consistent with the steps described in the language.

The complete execution process of the query statement is as follows:

1) CSEK obtains the first record () and sends the control to XFLT.

2) XFLT finds that the record () does not meet the condition and transmits the control to CSEK.

3) CSEK obtains the next record (2, 2) and sends the control to XFLT.

(4) The XFLT discovers that the record () meets the condition, transmits the record to RSET, and transmits the control to RSET.

5) RSET puts records () into the result set and passes control to XFLT.

6) XFLT sends control to CSEK.

7) CSEK gets the next one (3, 3) and sends the control to XFLT.

(8) XFLT finds that the record (3, 3) does not meet the condition and transmits the control to CSEK.

9) CSEK gets the next one (4, 4) and sends the control to XFLT.

10) XFLT finds that the record (4, 4) does not meet the conditions and passes control to CSEK

11) CSEK gets the next one (5, 5) and sends the control to XFLT.

12) If XFLT finds that the record () does not meet the conditions, it passes control to CSEK.

13) CSEK gets the next one (6, 6) and sends the control to XFLT.

14) When XFLT finds that the record (6, 6) does not meet the conditions, it passes control to CSEK.

15) When CSEK finds that the description operation has ended, it notifies XFLT that the operation has ended. Pass control to XFLT.

16) XFLT indicates that the query operation is complete and RSET is ended. Pass control to RSET.

17) RSET knows that the operation is complete.

18) Send the result set (including records (2, 2) to the client.

2. If the c1 column on table t1 has a non-unique index, what should I do?

Table t1 has the same definitions and data as described in table 1.

Create an index:Create indexIt1_c1OnT1 (c1 );

Query statement"Select*FromT1WhereC1 = 2; "corresponding execution plan:

# RSET: [201, 2, 1];

# CSEK (SECOND): [201, 2, 1]; IT1C1 (T1), INDEX_EQU_SEARCH

The "SECOND" in the CSEK row indicates that the non-clustered Index "IT1C1" is used to search for the index equivalent (INDEX_EQU_SEARCH.

The execution process of this execution plan is:

1)CSEKUse c1 = 2 to search for non-clustered indexes and obtain rowid1 (a numerical value) in the first index record c1 = 2 (2, rowid1 ). Use rowid1 to search for the clustered index to obtain the corresponding data record () and pass the control to RSET.

2)RSETPut records () into the result set and pass control to CSEK. (Because the index on c1 is not unique, more than two records may meet c1 = 2, so control needs to be passed to CSEK ).

3)CSEKObtain the next record (3, rowid2) of the current non-clustered record because 3! = 2, so the scan ends. Pass control to RSET. (If the number of records that meet c1 = 2 is greater than 1, you need to pass the record to RSET, and so on until the record that does not meet c1 = 2 is ended .)

4)RSETThe operation is completed.

5) Send the result set (including records (2, 2) to the client.

3. What if column c1 on table t1 has a unique index?

First, delete the non-unique index on the c1 column, and then create a unique index on the c1 column:

Drop indexIt1 c1;

Create uniqueindexUit1 c1OnT1 (c1 );

Query statement"Select*FromT1WhereC1 = 2; "corresponding execution plan:

# RSET: [201, 2, 1];

# CSEK (SECOND): [201, 2, 1]; UIT1C1 (T1), INDEX_EQU_SEARCH

The execution process of this execution plan is:

1)CSEKUse c1 = 2 to search for non-clustered indexes and obtain rowid1 (a value) in the index record c1 = 2 (2, rowid1 ). Use rowid1 to search for the clustered index to obtain the corresponding data record () and pass the control to RSET. (Of course, some may ask, what if no record meets c1 = 2? In this case, no record is transferred to RSET, notifying the RSET query operation to end, and finally returning an empty set to the client ).

2)RSETPut record () into the result set, and the operation ends (because it is a unique index, only one record can satisfy c1 = 2 at most ).

3) Send the result set (including records (2, 2) to the client.

Here we find that example 3 uses a unique index and Example 2 uses a non-unique index. The execution speed of Example 3 is greater than that of Example 2.

4. How to understand the top n operations in the execution plan?

The execution plan of the query statement "select top 10 * from t1 wherec1> 2;" is:

# RSET: [21, 1, 1];

# XTOP: [0, 0, 0]; top_off (0), top_num (10)

# XFLT: [0, 0, 0]; EXPR1> 2

# CSEK: [21, 1, 1]; INDEX33555545 (T1), FULL_SCAN

XTOP(Obtain the first N records): Put the records passed by the XFLT operator into the RSET (result set, determine whether the number of records is equal to the given value 10 (top 10 in the statement ). If the value is equal to 10, the query is successfully executed and exits. Otherwise, pass the control permission to XFLT and continue execution. Execute the command in sequence until 10 records are obtained, or the CSEK operation of the table has been completed (that is, less than 10 records meeting the condition ).

5. How to understand the order by operation in the execution plan?

The execution plan for the query statement "select top 10 * from t1 where c2> 2 order by c1;" is:

# RSET: [21, 1, 1];

# XSORT: [0, 0, 0]; keys_num (1), is_distinct (FALSE)

# XFLT: [0, 0, 0]; EXPR1> 2

# CSEK: [21, 1, 1]; INDEX33555545 (T1), FULL_SCAN

XSORT(Sort records): insert records passed by the XFLT operator into a reasonable location in the temporary space maintained by XSORT, and sort the records in sequence by c1. Then pass the control to XFLT to obtain the next qualified record. After processing all the qualified records. The XSORT operator passes the control permission to RSET.

6. Is the XSORT operation displayed in the execution plan once the order by clause appears in the query statement?

?

No. For example, the query statement"SelectC1FromT1OrderC1; "corresponds to the execution plan:

# RSET: [0, 0, 0];

# CSEK: [0, 0, 0]; UIT1C1 (T1), FULL_SCAN

From the execution, we can see that DM directly performs a full index scan on the index UIT1C1, and does not need to perform XSORT sorting for each record, which is directly placed in the RSET (result set. Because the index UIT1C1 itself is sorted by c1.

7. According to some documents, for the statement "select max (c1) from t1", you can create an index on the c1 column to make the query faster. How is the execution plan reflected?

Query statement"Selectmax (C1) FromT1 "corresponding execution plan:

# RSET: [0, 0, 0];

# XEVL: [0, 0, 0];

# FAGR: [0, 0, 0]; function_num (1)

In this execution plan, we do not see the CSEK operator. Because the index UIT1C1 exists on c1, the rightmost end of the leaf node of the index is the maximum value of c1. FARG returns the maximum value. Statement"Select min (C1) FromT1; ", statement"Select count (*) FromT1; "The execution principle is the same. XEVL is expression calculation. This document does not explain it.

8. If an index exists in a column, how does one understand the group by operation?

Query statement"SelectC1,Count (*) FromT1WhereC1> = 2GroupC1; "corresponds to the execution plan:

# RSET: [11, 1, 1];

# XEVL: [0, 0, 0];

# SAGR: [0, 0, 0]; group_by_num (1), function_num (1)

# CSEK: [11, 1, 1]; UIT1C1 (T1), INDEX_GE_SEARCH

We can see that CSEK uses the index UIT1C1 for range search. The record group passed to SARG is continuous c1 = 2, followed by c1 = 3, followed by c1 = 4 ,......

The execution process of SARG is

1) Obtain a c1 = 2 record from CSEK and add the count to 1,

2) obtain the next record from CSEK. If the record meets c1, the count is + 1.

3) Repeat Step 2 until the first record that does not meet c1 = 2 is obtained, pass (2, corresponding calculation) to XEVL, and then to RSET (result set ). Then execute the same process for the record group c1 = 3. Wait until all matching records are processed.

Here, our grouping function is count (*). If it is another grouping function, the processing process is similar.

9. If no index exists in the column, how does one understand the group by operation?

Query statement"SelectC2,Count (*) FromT1WhereC2> = 2GroupC2; "corresponding execution plan:

# RSET: [21, 1, 1];

# XEVL: [0, 0, 0];

# HAGR: [0, 0, 0]; group_by_num (1), function_num (1)

# XFLT: [0, 0, 0]; EXPR0> = 2

# CSEK: [21, 1, 1]; INDEX33555550 (T1), FULL_SCAN

As there is no index on c2, HARG serves as a HASH group.

The execution process of HARG is:

1) Get a record from XFLT

2) c1 = m of the record. If the corresponding item already exists in the hash table, Count + 1. If no corresponding item exists, create a new hash item.

3) after processing all records that meet the filtering conditions, HARG will pass the control permission to the upper-layer operator, and each time HARG passes one (count corresponding to m and m) to the upper-layer operator ).

Here, our grouping function is count (*). If it is another grouping function, the processing process is similar.