Shulou(Shulou.com)06/01 Report--

Recently, some netizens in the exchange group talked about the leftmost matching principle of SQL Server index, which is understood as the writing order of Where conditions in T-SQL, which is a misunderstanding.

Let's take a look at the experimental results.

1. Prepare data.

CREATE TABLE [dbo]. [T6] (

[id] [int] IDENTITY (1) NOT NULL

[hour] [int] NULL

[ordernumber] [int] NULL

CONSTRAINT [PK_t6] PRIMARY KEY CLUSTERED ([id] ASC) ON [PRIMARY]

) ON [PRIMARY]

GO

Insert into T6 values (default,default)

-- repeat the following statement to generate a 10m record

Insert into t6 select id, hour from t6

Update t6 set

Hour=id% convert (int,300000*RAND () + 2), ordernumber=id% convert (int,3000*RAND () + 2)

2. Create index 1. Http://u48582907.b2bname.com/

Create index fhsy1 on T6 (hour, ordernumber)

3. Check that both fields are the execution plan of the equivalent query.

Select hour,ordernumber from t6 where hour=1 and ordernumber=1

Select hour,ordernumber from t6 where ordernumber=1 and hour=1

4. Create index 2.

Create index fhsy1 on T6 (ordernumber, hour)

5. Review the execution plan again.

6. take a look at the next field, which is equivalent, and the other is the execution plan of the scope query.

Select hour,ordernumber from t6 where hour=1 and ordernumber between 1 and 2

Select hour,ordernumber from t6 where ordernumber between 1 and 2 and hour=1

Select hour,ordernumber from t6 where ordernumber=1 and hour between 1 and 2

Select hour,ordernumber from t6 where hour between 1 and 2 and ordernumber=1

Conclusion

1. The leftmost matching of the index refers to the relationship between the retrieval condition and the index field, which has nothing to do with the writing order of the Where condition in the T-SQL statement.

Indexes are related to the order in which search conditions are written, which may have been possible in the last century; the intelligent programs of today's database engines should be able to achieve order-independent through intelligent optimization or sentence rewriting. This can not be done, this database is not far from elimination. Zhengzhou Infertility Hospital: http://yyk.39.net/zz3/zonghe/1d427.html

2. From the perspective of Cost, the index always matches the compound index in front of the equivalent retrieval field, which is why it is called the leftmost matching principle.

3. The execution plan of the leftmost matching index is Index Seek/Scan, that is, the location is first located by the equivalent condition, and then the range is scanned by the unequal condition. In general, this execution plan is superior to Index Scan, which is a scan of the entire index.

Doubt

In the equivalent query, CBO automatically selects an Cost minimum execution plan, where index 1 is equivalent to index 2, and the final execution plan chooses index 2 instead of index 1 for unknown reasons. It should be related to the height and statistics of the index tree. Waiting to be checked.

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.