index creation for SQL Server

As the system data increases, some queries become slower, and we can look at the SQL overhead based on SQL Server's execution plan, and then create an index based on the cost.

Indexes have clustered indexes and nonclustered indexes.

Clustered indexes: Clustered indexes are stored sequentially in the store, like Chinese characters in a dictionary.

Nonclustered indexes: Physical storage is discontinuous, but logically contiguous, because it maintains the relationship of the storage location of the data to the data separately.

Write 100000 data first

DECLARE @i INT,    @num int    SET @i=0   SET @num=100000     while @i<=@num    BEGIN     IF  not EXISTS(SELECT *  fromDbo.meter_manageWHEREmeter_id=@i)     INSERT  intodbo.meter_manage (meter_id, Meter_no, Meter_name)VALUES             ( @i,--Meter_id-int           'ASDASD'+CONVERT(VARCHAR( -),@i),--Meter_no-varchar ($)           'ASDSF'++CONVERT(VARCHAR( -),@i)--Meter_name-varchar ($)         ); SET @i=@i+1; END      Go

Creation of nonclustered indexes:

Create nonclustered INDEX  on Meter_manage (meter_no)  

Effect:

Select *  from where meter_no='asdasd2'

About 23 milliseconds before creating a nonclustered index

Instant completion After creating a nonclustered index

When you frequently use a multi-conditional statement query, we can create composite indexes.

Select *  from where meter_no='asdasd2' and Meter_name='  asdsf2'

Nonclustered indexes are not created and take 30 milliseconds:

Creating a single index in the Meter_no field takes 3 milliseconds:

Create nonclustered INDEX  on Meter_manage (meter_no)  

Conditional Query Location Replacement:

Select *  from where meter_name='asdsf2'and  meter_no= ' ASDASD2 '

The query speed has not changed, same as 3 milliseconds.

We also set up a nonclustered index on another field meter_name:

Create nonclustered INDEX  on Meter_manage (meter_name)  

The time to discover two nonclustered indexes did not change much with the time of a clustered index, viewing the execution plan and only hitting the INDEX1 index:

Analysis:

Let's just imagine that when the database has n indexes and the query has to use their situation separately:
The query optimizer (the thing that uses plain English to generate execution plans) requires N-Times primary binary tree lookups [here the main binary tree means the outermost index node], where the lookup process is probably as follows:
Find out the value of the first column1 the main binary tree equals 1, then go to the second Column2 main binary tree to isolate the value of Foo and the current line coumn1 must be equal to 1, Finally go to the column main binary tree to find the value of bar and column1 must be equal to 1 and column2 must be equal to Foo.
If such a process is executed once by the query optimizer, even if it is not dead and half a life, the query optimizer can not wait to execute the above plan, the greedy algorithm (nearest neighbor algorithm) does not allow this to happen, so when encountering the following statement, the database only uses the first filtered column index (column1) , it will go straight to the table scan.

Select COUNT (1) from table1 where column1 = 1 and column2 = ' foo ' and column3 = ' Bar '

So instead of a database that only supports one query statement using only one index, it's better to say that n stand-alone indexes consume more slowly than one index at a time.
So in the case of the previous article, the best recommendation is to use the index (COLUMN1,COLUMN2,COLUMN3), a federated index that can bring the benefits of the B+TREE structure to its fullest:
A main binary tree (column=1), the query to the Column=1 node based on the current node for the two-level two-tree Column2=foo query, two-level two cross-tree query to Column2=foo, go to three-level two tree Column3=bar find.

Conclusion: Two separate indexes usually the database can only use one of the

To create a composite index:

Create Index  on

Instant completion, found to be suitable for creating composite indexes under multiple conditions.

Change the condition position.

Select *  from where meter_name='asdsf2'and  meter_no=  'asdasd2'

Same instantaneous completion. View execution plan hit idx1

We remove two conditions:

Select *  from where meter_no='asdasd2'

The same instantaneous completion, also hit the index idx1

We get rid of the first condition:

Select *  from where meter_name='asdsf2'

Takes 27 milliseconds, no difference with no index, view execution plan, found that although hit idx1

But the type is index Scan, which is different from the previous index seek

Difference:

[Table Scan] table scan (slowest), check table records line by row

[Clustered Index Scan] clustered index Scan (slower), record-by-row checks on a clustered index

[Index Scan] Index Scan (normal), filter out part of the data according to the index in the progressive check

[Index seek] index lookup (faster), retrieve records based on index location

[Clustered Index seek] clustered index lookup (fastest) to fetch records directly from a clustered index

Therefore, both clustered and nonclustered indexes exist on the field, in which case only the clustered index is hit because the clustered index is the fastest, for example: Creating a nonclustered index on a primary key

Create nonclustered INDEX  on Meter_manage (meter_id)  

Instant completion, Execution plan:

index creation for SQL Server