Some minor aspects that are easily overlooked in SQL development (5)

Background:

Index classification: As we all know, indexes are classified into clustered indexes and non-clustered indexes.

Index advantage: accelerate data query.

Q: But do we really know the index application? Can the query statement you write fully apply the index, or how do you design your index to make it more efficient?

Experience: I used to only know the benefits of indexing, but whether it can really make it play a role does not have much theory. I have made some demos for myself, to briefly describe under what circumstances to make full use of the index.

Case:

Create a student table with the following fields. At this time, no indexes are created in the table.

Create Table [DBO]. [Student] (
[ID] [int] identity (1, 1) not null, -- Student ID
[Susername] [nchar] (10) Collate chinese_prc_ci_as null, -- Student name
[Saddress] [varchar] (200) Collate chinese_prc_ci_as null, -- Student address
[Classid] [int] Null, -- Student Class ID
[Create_date] [datetime] Null constraint [df_student_create_date] default (getdate () -- school entry time

) On [primary]
 

Business Requirements:

Query the names and addresses of all students whose class ID is 9.

Scenario 1:
-- No index is created for the field.
Select susername, saddress from student

Where classid = 9

The execution plan is as follows:

Conclusion: If no index exists, full table scan is selected for the query.

Scenario 2:

Create a clustered index for the ID auto-increment column. In many cases, this is the default value. The primary key is the clustered index. For the same query and different query plans, it is found that although there is no ID in the output column and condition, clustering query is selected for the query.

The execution plan is shown in Figure 1.

Case 3:Create a non-clustered index on the classid.

Conclusion: although the classid index Column exists in the condition column, no index is created in the output column, and the clustering scan method is still used for query.
 

Case 4:Create a non-clustered index on susername

Conclusion: Same as above

Case 5:Continue to create non-clustered index on saddress

Conclusion: Same as above
 

Scenario 6:Create a joint non-clustered index for susername and saddress

Conclusion: Same as above
 

Case 7:Create an index on classid and susername

Conclusion: Same as above
 

Case 8:Create a union non-clustered index on classid, susername, and saddress

The execution plan is shown in the figure below:

Conclusion: When the columns in the condition match the output column and the composite index column completely, the above index scan is used.

Case 9:Delete all indexes and retain the clustered index of IDs. Use clustered index columns as one of the conditions to query.

Select susername, saddress from student

Where id = 10021002.

Or: Select susername, saddress from student
Where id = 10021002 and classid = 9

Execution Plan diagram:

 

Summary:

1: when usingNonIo during clustered index scanning: 'student 'of the table '. 1 scan count, 70 logical reads, 0 physical reads, 0 pre-reads, 0 lob logical reads, 0 physical reads, and 0 lob pre-reads.

2: Io when clustered index scanning is used (No clustered index Column exists in the condition.): Table 'student '. Scan count 3, logical reads 8835, physical reads 0, pre-reads 0, lob logic reads 0, lob physical reads 0, and LOB pre-reads 0.

3: Io when clustered index scanning is used (Clustered index column appears in the condition): Table 'student '. 1 scan count, 3 logical reads, 0 physical reads, 0 pre-reads, 0 lob logical reads, 0 physical reads, and 0 lob pre-reads.

Conclusion:

1: If the index is not correct, the number of table scans is increased.

2: During data query, a matched index is first found.
1): If a clustered index column appears in the condition column, it will be searched by clustered index (with clustered index) regardless of whether the output column is indexed or not ).
2): If no clustered index Column exists in the condition column, search for the matched non-clustered index. If a matched index exists, query by the corresponding index, otherwise, the clustered index will be scanned (with clustered index ).

3): searches for matched non-clustered indexes (no clustered indexes ).

Summary:

I just wrote some demos about index usage. In actual development, I need to analyze indexes based on actual conditions. Sometimes I cannot use indexes completely, however, queries can generate at least I/O reads and the number of table scans.