The SQL Server execution plan teaches me how to create indexes?

The SQL Server execution plan teaches me how to create indexes?

I am not very familiar with the index, so the test results are of no value, or even some people are misleading. I am sorry here, but where did I fall.

Application scenarios

Or use the Product table as an example. The table structure is as follows:

There is such a business scenario: to obtain a list of commodities in the ProviderID of a certain supplier, with the status of sold commodities as 1), sorted by the production date ProduceTime in descending order, it is possible that our application uses paging when displaying data. Here we query the first 100 rows. Translate into SQL code:

 
 

  
  
SELECT TOP 100  
  
  
[ID], 
  
  
[Name], 
  
  
[Remarks], 
  
  
[ProviderID], 
  
  
[ProduceTime], 
  
  
[State] 
  
  
FROM [TestDB].[dbo].[Product] 
  
  
WHERE [ProviderID]=1 AND [State]=1 
  
  
ORDER BY [ProduceTime] DESC 
 
 

The above business scenario is basically met in our general applications. Sometimes, when the data volume is not large, we generally do not optimize any database, but you have read the following practices, should you consider adding an index to your current database?

SQL Server execution plan

The SQL Server execution plan is the tool for analyzing SQL Execution. Through it, we can also view the execution of indexes in a very good way. Before practice, we need to know some necessary skills, the following knowledge is taken from-understand the SQL Server query plan.

SQL Server has two types of indexes: clustered index and non-clustered index. The difference between the two is that clustered indexes directly determine the storage location of records, or you can directly obtain records based on Clustered indexes. Non-clustered index: stores two pieces of information: 1. The value of the corresponding index field, 2. records the location of the corresponding clustered index. If the table does not have a clustered index, the record pointer is saved ). Therefore, if you can use clustered indexes to search for records, it is obviously the fastest.

SQL Server provides the following methods to find the data records you need:

1. Table Scan: traverses the entire Table and finds all matched record rows. This operation will check one row at a time. Of course, the efficiency is also the worst.

2. Index Scan: filters out some records from the table based on the Index, and then searches for all matching record rows, which is obviously smaller than the first method, therefore, it is faster than Table Scan.

3. Index Seek: locate and obtain the location of the Record Based on the Index, and then obtain the record. Therefore, it is faster than the first two methods.

4. Clustered Index Scan: Same as Table Scan. Note: Do not think that there is an Index here, so it is different. In fact, it means that each row of records is scanned row by clustered index, because records are stored in the order of clustered index. Table Scan: The Table to be scanned does not have clustered indexes. Therefore, these two operations are essentially the same.

5. Clustered Index Seek: obtains records directly based on the Clustered Index, which is the fastest!

Therefore, when you find that a query is slow, you can first check which operations are expensive, and then check whether the operations are Table Scan or Clustered Index Scan when searching records ], if it is related to the two operation types, you need to consider adding indexes to solve the problem. However, adding an index will also affect the modification of the data table, because the index of the corresponding field must be updated when the data table is modified. Therefore, too many indexes may affect the performance. Another case is that it is not suitable for adding an index: the status of a field expressed as 0 or 1. For example, if the vast majority is 1, adding an index is meaningless. In this case, only the values 0 or 1 can be considered for separate storage. Table sharding or partition is a good choice.

Application Analysis

We will not create any index except the clustered index of the primary key ID.) let's take a look at the SQL code above and the execution in the SQL Server execution plan:

As you can see, the query overhead is basically occupied by SORT. In this case, we should first consider creating a non-clustered index for ProduceTime and then sorting by DESC, but sometimes we have to worry about it. Is it better to sort IDs? Because in the Product table, the ID is an auto-increment field and the current time is obtained when ProduceTime is added. In SQL sorting, the sorting effect of IDs and ProduceTime is the same, however, the execution performance is indeed quite different. Let's take a look at the execution plan:

From the above execution plan, we can intuitively see the difference. Therefore, we must be careful when writing SQL statements. Here, we sort the statements by the ProduceTime field for ease of display, sorting by ID. Although there is no SORT performance overhead, it is found that the query record is "Clustered Index Scan", which means full table query, we should ideally choose "Index Seek" or "Clustered Index Seek", because this is the fastest query by Index. According to our programmer's understanding, we should create a non-clustered index, such as the IX_Product_Provider_State index below:

After creating the SQL statement, run the following SQL code:

The "Key Lookup (Clustered)" record is still in full table search. By default, PK_Product is Clustered index. We may have doubts that indexes are created by query and sorting, why? At this time, let's take a look at the fields after the SELECT statement. The query shows all the fields in the Product table, but the IX_Product_Provider_State is not a clustered index and is only a condition field for the query, no. The query display fields are included. In the index creation window, there is a "inclusion column" next to the "index key column" TAB. We will add other display fields, check the execution result:

"Index Seek" is what we want. In fact, there are many practical problems about Index creation. For example, what is the difference between a combined field Index and a single field Index? Like the query case in the preceding example, if the ProduceTime sorting exists in other query conditions, should I pull it out to create an index? Or do I create a composite field index together with the query condition like above? Another case is that in an application query, there is a query for a single field or a query for a combination of fields. Then we create a single field index? Or create a composite field index? To solve these problems, create an index and use "SQL Execution Plan.

Summary

For the above query cases, I personally think the best solution is to use the ID of the sorting field to extract the fields to be queried according to the actual application scenario, so as to avoid the SELECT *, this will reduce the number of fields that are added to the "include columns" and create an IX_Product_Provider_State non-clustered index with the following index fields: ProviderID and State, if the State value is not changeable, for example, the values are 1 and 0), do not create non-clustered indexes for the State field.

After doing this, you will find that your applications are like flying.