How does SQLSERVER locate the target data through indexing and Statistics (Article 2)

How does SQLSERVER locate the target data through indexing and Statistics (article 2) before reading the content of this article, please read the content of the first article first, without the foundation of the first article, we will see the first article of o (_) o address: How does SQLSERVER locate the target data through indexing and Statistics (Article 1 )---------------

How does SQLSERVER locate the target data through indexing and Statistics (article 2) before reading the content of this article, please read the content of the first article first, without the foundation of the first article, we will see the first article of o (_) o address: How does SQLSERVER locate the target data through indexing and Statistics (Article 1 )---------------

How does SQLSERVER locate the target data through indexing and Statistics (Article 2)

Before reading the content of this article, please first read the content of the first article. If there is no basis for the first article, you will see confused o (∩ _ ∩) o

Address of Article 1: How does SQLSERVER locate the target data through indexing and Statistics (Article 1)

------------------------------ Start ----------------------------------------------------------------

Data Retrieval Method on Indexes

If a table has no clustered index, the data is stored in the Table in random order.

Take [SalesOrderDetail_test] as an example (this table has been created in the first article ). There is no clustered index on it,

There is only one non-clustered index on SalesOrderID. Therefore, records in each row of the table are not stored in any order, but randomly stored in the Hash.

At this time, if you want to find a detailed sales record with a unit price greater than 200, the statement to be run will be

1 SET STATISTICS PROFILE ON2 USE [AdventureWorks]3 GO4 SELECT 5 [SalesOrderDetailID],[UnitPrice]6 FROM [dbo].[SalesOrderDetail_test]7 WHERE [UnitPrice]>200

Since the table has no index on UnitPrice, SQLSERVER has to scan this table from start to end and record all records whose UnitPrice value is greater than 200.

One by one, the execution plan clearly shows that SQLSERVER performs a table scan. (I will not post the execution plan)

What if this table has clustered indexes? Let's take the table as an example. First, we will show him the SalesOrderDetailID field with a unique value.

Create a clustered index so that all data is stored in the order of clustered indexes.

1 USE [AdventureWorks]2 GO3 CREATE CLUSTERED INDEX SalesOrderDetail_test_CL ON [dbo].[SalesOrderDetail_test]([SalesOrderDetailID])4 GO

Unfortunately, there is no index on the query condition UnitPrice, so SQLSERVER still needs to scan all records

The difference is:The table scan in the execution plan becomes a clustered index scan, because the data on the table with clustered indexes is

Directly stored at the bottom of the index,Therefore, to scan the data in the entire table, we need to scan the entire clustered index.

Here, clustered index scanning is equivalent to a table scan.There is no difference between the time and resources used and the table scan.

It doesn't mean that "Index" is included here,This shows how much progress the execution plan has made over table scanning.

If you see the word "Table Scan,It indicates that there is no clustered index on this table.

Create a non-clustered index on UnitPrice to see how the situation changes.

1 USE [AdventureWorks]2 GO3 CREATE INDEX SalesOrderDetail_test_NCL_Price ON [dbo].[SalesOrderDetail_test]([UnitPrice])4 GO 

In a non-clustered index, a non-clustered index key value and a clustered index key value are stored for each record.

[In a table without clustered indexes, the RID value points to the data page. If there is clustered index, it points to the clustered index key (when include is not used )]

Therefore, each record has a [UnitPrice] And a [SalesOrderDetailID] record, which are stored in the order of [UnitPrice ].

Run the query. You will see that SQLSERVER does not need to scan the entire table this time. Based on the newly created index,

He directly found the execution plan that conforms to the record value:Index Search

However, indexes created on [UnitPrice] cannot tell us the values of other fields. If several more fields are added to the query,

SQLSERVER first finds all records with the value of [UnitPrice] greater than 200 on the non-clustered index, and then according to the value of [SalesOrderDetailID]

Find the detailed data stored on the clustered index. This process can be called "bookmark loolup" booksearch (it is difficult to avoid bookmarking)

Why is it difficult to avoid searching bookmarks? You will see it later.

1 SET STATISTICS PROFILE ON2 USE [AdventureWorks]3 GO4 SELECT [SalesOrderID],[SalesOrderDetailID],[UnitPrice]5 FROM [dbo].[SalesOrderDetail_test] WITH (INDEX (salesorderdetail_test_ncl_price))6 WHERE [UnitPrice]>200

After SQLSERVER2005, the action of bookmark lookup is completed in a nested loop. So in the execution plan, we can see

SQLSERVER first seek the non-clustered index salesorderdetail_test_ncl_price, and then uses clustered index seek to find the required rows.

The nested loop here is actually bookmark lookup

----------------------------------- Summary --------------------------------------------------------
To sum up, in SQLSERVER, there are several situations in which the targets and methods are different.
SQL Server Data Retrieval Method
Structure Scan Seek
Heap (Table data page without clustered indexes) Table Scan none
Clustered Index Scan Clustered Index Seek
Non-clustered Index Scan Index Seek

Note: Non-clustered indexes: (if the table does not have clustered indexes, the data is stored on the table data page of the heap structure, only in the non-clustered index,

A non-clustered index key value and a clustered index key value are stored for each record. (In a table without clustered indexes, the RID value points to the data page,

If a clustered index exists, it directs to the clustered index key )!!

Why not: NONCLUSTERED Index Scan? NONCLUSTERED Index Seek?

As mentioned above, SQLSERVER has only two types of indexes: clustered index and non-clustered index.

The following two statements are the same: create a non-clustered index:

1 CREATE NONCLUSTERED INDEX SalesOrderDetail_test_NCL_Price ON [dbo].[SalesOrderDetail_test]([UnitPrice])2 GO3 4 5 CREATE  INDEX SalesOrderDetail_test_NCL_Price ON [dbo].[SalesOrderDetail_test]([UnitPrice])6 GO

So there is no need to call NONCLUSTERED Index Scan and NONCLUSTERED Index Seek.

-------------------------------------------------- Then summarize --------------------------------------------------------------

I don't know how to typeset it because it's too long.

If you see these actions in the execution plan, you should be able to know which object is being operated by SQLSERVER.

Table scan indicates that the table being processed has no clustered index, and SQLSERVER is scanning the entire table.

Clustered index scan indicates that SQLSERVER is scanning a table with clustered index, but it is also a whole table scan.

Index Scan indicates that SQLSERVER is scanning a non-clustered Index. Since non-clustered indexes generally only have a small part of fields,

Therefore, although this is also a scan, the cost will be much less than the full table scan, because the data is not stored in the index.

Clustered Index Seek and Index Seek indicate that SQL is using Index results to retrieve target data. If the result set only accounts for a small portion of the total table data volume

In addition, the fields in the result set are included in the index. Seek is much cheaper than Scan, and the index plays a role in improving performance,

OtherwiseBookmark lookup

-------------------------------------Further Summary------------------------------------------------------

(1) After where (filtered field ):Determines the range of fields to be created when you create an index. If a field is often used as a filter field, you can create an index on it.

1 CREATE CLUSTERED INDEX SalesOrderDetail_test_CL ON [dbo].[SalesOrderDetail_test]([SalesOrderDetailID])

(2) Before where (returned field ):Determines the data search speed of your table. The values of other fields are returned Based on the clustered index. If no clustered index exists, you can find the field values on the data page.

(3) clustered index scan:The field after where is a field that does not have an index [including non-clustered indexes (table must have non-clustered indexes) and clustered indexes]

However, there is a clustered index on the table, regardless of whether there is any non-clustered index on the table.

(3) Non-clustered index scan:The fields after where are not indexed (non-clustered index), but the table has non-clustered index but no clustered index.

(4) Table scan:The field after where is a field with no index and the table does not contain clustered and non-clustered indexes.

(5) Search for bookmarks:The fields after where are indexed (whether clustered or non-clustered), but some of the fields returned before where are not indexed.

(Whether clustered index or non-clustered index)

Personal Opinion: Few people usually create indexes on many fields during indexing, whether clustered or non-clustered, because SQLSERVER maintains these indexes.

Cost required and cost required

1 create index SalesOrderDetail_test_NCL_Price ON [dbo]. [SalesOrderDetail_test] ([UnitPrice].. N table fields) 2 GO

You can place frequently returned columns in the index include to overwrite as many columns as possible without increasing the index key size.

In this way, when some queries do not use the pilot column of the composite index, but do not feel that they are not worth creating an index for them, if the fields used in this query are indexed by the composite index, the index will overwrite them.

The two articles that can be referenced are written by park friends in the blog Garden:

Listening to the wind and rain

Include charm in SQL Server indexes (include index of included columns)

Create a proper index for SQL Server query performance optimization (Part II)

Http://www.cnblogs.com/lzrabbit/archive/2012/07/03/2549558.html

Necessity of non-clustered index:

I personally think:If you can specify an inclusive column for a clustered index, non-clustered indexes are useless. I tested it.

The result cannot specify an inclusive column for The clustered index, because the clustered index stores all data on the index page.

(As mentioned above, data in tables with clustered indexes is directly stored at the bottom of the index.) Therefore, these include index Columns cannot be added at will,

This is because the addition will change the data storage order rather than the clustered index.

Therefore, the existence of non-clustered indexes is necessary.

Write the third article "o (∩ _ ∩) o" tonight

Article 3: How does SQLSERVER locate the target data through indexing and Statistics (Article 3)