What does the SQL server-nonclustered index in the programmer's eyes bring to us?



I'm not going to say a few database concepts, so I can only use some practice to understand the meaning of the concept, the following scenarios in which use cases are virtual, only as a personal study.

Programmers should have an inquisitive quirk, although this is a database problem.

Application Scenarios

There is a Product table with the following fields:

Data add script:

beginTranDeclare@Index intSet@Index=0while (@Index<1000000)beginInsert  into[dbo]. [Product] ([name],remarks,providerid,[ Time],[state])Values(' I am the test Heading 1 ',' I am a test note 1 I am a test Note 1 I am a test Note 1 I am a test Note 1 I am a test Note 1 I am a test Note 1 ',1,GETDATE(),0)Insert  into[dbo]. [Product] ([name],remarks,providerid,[ Time],[state])Values(' I am the test Heading 2 ',' I am a Test Note 2 i am a test Note 2 I am a Test Note 2 I am a Test Note 2 I am a Test Note 2 I am a test Note 2 ',1,GETDATE(),1)Insert  into[dbo]. [Product] ([name],remarks,providerid,[ Time],[state])Values(' I am the test Heading 3 ',' I am the test Note 3 ',3,GETDATE(),1)Insert  into[dbo]. [Product] ([name],remarks,providerid,[ Time],[state])Values(' I am the test Heading 4 ',' I am a test Note 4 i am a test Note 4 I am a test Note 4 I am a test Note 4 I am a test Note 4 I am a test Note 4 ',4,GETDATE(),1)Set@Index[Email protected]Index+1EndCommit

4 million of the data is inserted into the product table, and the data is inserted differently in order to approach our real production environment.

General application Environment query, sometimes we will make a where query for a field, and sometimes also query with another field, this time, about the two fields of the index how to build? Or does it need to be built? are two separate buildings? Or build a combination? In fact, it may be seen that the database of the great God will smile, but as a programmer, these I really do not know, the search information is not on these trivial instructions, no way, can only be a blind toss. We have to do is ProviderID and state query operations, there are separate queries, there are combination queries, and then we set the Product table index of these two fields to see what is the difference? There is a different way of indexing, the query will be different? Let's look at it with wide eyes. Technology to improve access to www.cgzhw.com game Programming network is a very good technology site.

Problem analysis

I will explain the above analysis, first of all, the main query for 2 kinds:

1. where providerid=?
   
2. where providerid=? and state=?

Nonclustered indexes are created in 3 main types:

1. Do not create an index
   
2. ProviderID Field Index
   
3. ProviderID and State field indexes

For this application scenario and the above analysis, will draw 3*2 six kinds of results, in fact, I want to know is the following third, that is, to create a composite field index, the query for a single field will not affect? And, conversely, does the index creation of a single field affect the query of a combined field? Of course I tried before I knew, look at the results of the implementation.

Execution results

Test script:

declare @begin_date datetimedeclare @end_date datetimeselect @begin_date = getdate()select * from [dbo].[Product] where ...select @end_date = getdate()select datediff(ms,@begin_date,@end_date) as ‘用时/毫秒‘

In order to approach the test results, each statement executed three times, and then averaged, too troublesome, this way directly paste the execution results.

Do not create an 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 state=1 (1 million data)
   Execution Result: 6556 MS, 6613 MS, 6706 MS
   Average result: 6625 ms

Create an 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

Results analysis

Although some of the tests are incomplete, but see the results, brother is a bit messy (built index, performance will be reduced?) ), is there a problem with the data I inserted? Or is there a problem creating the index? Or is there a problem with my character??? Wait for the database big God teach ...