What does SQL Server non-clustered index bring to us?

What does SQL Server non-clustered index bring to us?

SQL Server Profiler is used to track the execution of SQL statements when a page is accessed. Because the application is very simple, three SQL statements are detected during page loading, after reading the results, there is no problem: two get quantities, one get list). The number of SQL statements obtained should be executed very quickly. Therefore, the focus of the analysis is on the SQL statement that gets the list, because SQL has no problems, it should be about the index created by this SQL statement. Note: The project mentioned above contains about 1 million of data.

For the index concept in the database, I remember a long time ago I had compiled a blog post titled "T-SQL 8) Field index and Data Encryption". Now, what I wrote is really awkward, the concept is useless, but the key lies in the analysis of problems in practical applications. Before studying this issue, I found some relevant information, mainly from several SQL Server experts in the park, such as CareySon, Hua Zi, and the wind and rain, I mainly use some database terminology. I don't quite understand it. As a programmer, we know that indexes are classified into clustered indexes and non-clustered indexes, and clustered indexes are generally primary keys ), the table is automatically created when the table is created. For the query problem of my application above, the query condition is some non-primary key fields, so we will discuss the non-clustered index.

I will not talk about some database concepts, so I can only use some practices to understand the meaning of these concepts. The use cases in the following application scenarios are virtualized and used only for personal research.

Programmers should be curious, though this is a database problem.

Application scenarios

There is a Product table with the following fields:

Data adding script:

 
 

  
  
Begin tran
  
  
Declare @ index int
  
  
Set @ index = 0
  
  
While (@ index <1000000)
  
  
Begin
  
  
Insert into [dbo]. [Product] ([Name], Remarks, ProviderID, [Time], [State])
  
  
Values ('I am test title 1 ', 'I am testing remark 1 I'm testing remark 1 I'm testing remark 1 I'm testing remark 1 I'm testing remark 1 I'm testing remark 1', 1, GETDATE (), 0)
  
  
Insert into [dbo]. [Product] ([Name], Remarks, ProviderID, [Time], [State])
  
  
Values ('I am test title 2 ', 'test remarks 2 I test remarks 2 I test remarks 2 I test remarks 2 I test remarks 2 ', 1, GETDATE (), 1)
  
  
Insert into [dbo]. [Product] ([Name], Remarks, ProviderID, [Time], [State])
  
  
Values ('I am test title 3',' I am Test Remarks 3', 3, GETDATE (), 1)
  
  
Insert into [dbo]. [Product] ([Name], Remarks, ProviderID, [Time], [State])
  
  
Values ('test title 4 ', 'test remarks 4 I test remarks 4 I test remarks 4 I test remarks 4 I test remarks 4 I test remarks 4 ', 4, GETDATE (), 1)
  
  
Set @ index = @ index + 1
  
  
End
  
  
Commit
 
 

4 million of data is inserted in the Product table. To stay close to our actual production environment, data is inserted differently.

For general application environment queries, sometimes we perform a where query for a field, and sometimes query with another field. At this time, how can we create indexes for these two fields? Or do you not need to create it? Are two instances created separately? Or create a combination? To be honest, I may see that the database experts will smile, but as a programmer, I really don't know about this. There is no way to explain the search information, you can only try it on your own. We will perform the following query operations for ProviderID and State, including separate queries and combined queries. Then we will create indexes for these two fields in the Product table to see what is the difference? What is the difference between queries based on different indexing methods? Let's have a look.

Problem Analysis

I will explain the above analysis. First, there are two types of queries:

1. Where ProviderID =?

2. Where ProviderID =? And State =?

There are three types of non-clustered indexes:

1. Do not create Index

2. ProviderID field Index

3. ProviderID and State field Indexes

For this application scenario and the above analysis, we will get 3x2 6 results. In fact, what I want to know most is the third one below, that is, creating a composite field index, will the query of a single field affect? In turn, will the creation of an index for a single field affect the query of combined fields? Of course, you can see the execution result after you try it.

Execution result

Test script:

 
 

  
  
Declare @ begin_date datetime
  
  
Declare @ end_date datetime
  
  
Select @ begin_date = getdate ()
  
  
Select * from [dbo]. [Product] where...
  
  
Select @ end_date = getdate ()
  
  
Select datediff (MS, @ begin_date, @ end_date) as 'time/millisecond'
 
 

In order to get close to the test result, it is too troublesome to execute the statement three times each time and then take the average value. Here we will directly paste the execution result.

Do not create Index

1. Where ProviderID = 1 2 million data)
   Execution result: 13806 ms, 13380 ms, 12730 Ms
   Average result: 13305 Ms

2. Where ProviderID = 1 and status = 1 1 million data)
   Execution result: 6556 ms, 6613 ms, 6706 Ms
   Average result: 6625 Ms

Create index field ProviderID

1. Where ProviderID = 1
   Execution result: 13986 ms, 13810 ms, 15853 Ms
   Average result: 14549 Ms

2. Where ProviderID = 1 and State = 1
   Execution result: 7153 Ms, 7190 ms, 13950 Ms
   Average result: 7122 Ms

Create index fields ProviderID and State

1. Where ProviderID = 1
   Execution result: 13840 ms, 14163 ms, 15853 Ms
   Average result: 14618 Ms

2. Where ProviderID = 1 and State = 1
   Execution result: 7033 ms, 7220 ms, 7023 Ms
   Average result: 7152 Ms

Result Analysis

Although some tests are incomplete, but the results show that some of my brother's index creation is messy, and the performance will decrease ?), Is there a problem with the data I inserted? Or is there a problem with creating an index? Or is there a problem with my character ??? Wait for the advice from the database.