Using indexes for SQL optimization (a small example)

According to the operation and experience in this article, we will have a basic and simplest understanding of SQL optimization. Other in-depth learning needs more information and practices:
1. Create a table:
Copy codeThe Code is as follows:
Create table site_user
(
Id int IDENTITY (1, 1) primary key,
[Name] varchar (20 ),
Code varchar (20 ),
Date datetime
)

2. Insert 80 thousand data records
Copy codeThe Code is as follows:
Declare @ m int
Set @ m = 1
While @ m <80000
Begin
Insert into [demo]. [dbo]. [site_user]
(
[Name]
, [Code], date)
VALUES
('Name' + CAST (@ m as varchar (20 ))
, 'Code' + CAST (@ m as varchar (20), GETUTCDATE ())
Select @ m = @ m + 1
END
-- Tips: We recommend that you use tools similar to sqlassist to speed up SQL statement writing.

3. Set to enable some parameter settings
Copy codeThe Code is as follows:
Set statistics io on -- view disk IO
Set statistics time on -- View SQL statement analysis compilation and execution time
SELECT * FROM site_user -- view results

4. View table indexes:
Sp_helpindex site_user

5. Execute SQL statements
Copy codeThe Code is as follows:
SELECT * FROM site_user su WHERE su. name = 'name1' table 'site _ user '.
Scan count 1, logical reads 446, physical reads 0, pre-reads 0, lob logic reads 0, lob physical reads 0, lob pre-reads 0

Ctrl + L shortcut key to view the execution plan:

6. Optimization Step 1: clustering index scanning overhead accounts for 100%. You can consider optimizing the index search to create a non-clustered index on the query condition name.
Copy codeThe Code is as follows:
Create index name_index on site_user (name)
Sp_helpindex site_user -- add the newly created index

Then run the preceding query statement:
Copy codeThe Code is as follows:
SELECT * FROM site_user su WHERE su. name = 'name1'
Table 'site _ user '. 1 scan count, 4 logical reads, 0 physical reads, 0 pre-reads, 0 lob logical reads, 0 physical reads, and 0 lob pre-reads.

The number of logical reads to the disk decreases significantly, and then you can view the execution plan:

The new index has already played a role, but it still scans the clustered index of the primary key. If it can complete the query on an index, the performance will be higher, because this query

Further optimization is considered:

7.Step 2: Create a composite index
Copy codeThe Code is as follows:
Create index name_index4 on site_user (name, code, [date])
Table 'site _ user '. 1 scan count, 3 logical reads, 0 physical reads, 0 pre-reads, 0 lob logical reads, 0 physical reads, and 0 lob pre-reads.


-- The number of logical reads on the disk has dropped.

View the execution plan:

In this way, the index search is much faster.Index4

8.Step 3 of optimization: We can also consider using the overwriting index to write all the conditions used in the index brackets, and put other queried fieldsIncludeMedium,
Copy codeThe Code is as follows:
Create index name_index5 on site_user (name) include (id, code, [date]) Table 'site _ user '.
1 scan count, 3 logical reads, 0 physical reads, 0 pre-reads, 0 lob logical reads, 0 physical reads, and 0 lob pre-reads.

-- The number of logical reads to the disk does not change significantly. Then, view the execution plan:

The index search is also used.Index5
At this point:Index4AndIndex5How to choose?
ExploitationDbccPerform data analysis:
Copy codeThe Code is as follows:
DBCC SHOW_STATISTICS ('site _ user', 'name _ index4 ')
DBCC SHOW_STATISTICS ('site _ user', 'name _ index5 ')

We can see that the same data volume, average key length: covers the index index5 and occupies less space. Therefore, we should prioritize overwriting indexes for optimization.
In view of this so easy article, you can mention more

Author: gaobanana
Source: http://www.cnblogs.com/gaobanana