Clustered index and non-clustered Index

Difference of clustered Index

Clustered index: physical storage is sorted by index

Non-clustered index: physical storage is not sorted by index

Advantages and disadvantages

Clustered index: the data insertion speed is slow (the time is spent on the "sort of physical storage", that is, the location must be located first and then inserted). The data query speed is faster than that of non-clustered data.

Difference of clustered Index

Clustered index: physical storage is sorted by index

Non-clustered index: physical storage is not sorted by index

Advantages and disadvantages

Clustered index: the data insertion speed is slow (the time is spent on the "sort of physical storage", that is, the location must be located first and then inserted). The data query speed is faster than that of non-clustered data.

The index is described by the data structure of the binary tree. We can understand the clustering index as follows: the leaf node of the index is the data node. The leaf node without clustering index is still an index node, but there is a pointer pointing to the corresponding data block. For example:

Non-clustered Index

Clustered Index

I. Differences between index blocks and data blocks

As we all know, indexing can improve the retrieval efficiency, because it has a binary tree structure and occupies a small amount of space, so the access speed block. Let's calculate a mathematical problem: If a record in the table occupies 1000 bytes on disk, We will index a field of 10 bytes, the index block size of the record is only 10 bytes. We know that the minimum space allocation unit of SQL Server is "Page". If one Page occupies 8 K space on the disk, this Page can store 8 of the above records, however, 800 indexes can be stored. Now we need to retrieve records that meet a certain condition from a table with 8000 records. If there is no index, we may need to traverse 8000x1000 bytes/8 K Bytes = 1000 pages to find the result. If the above index is available for the search field, we can retrieve the index blocks that meet the Search Condition in 8000 × 10 bytes/8 K Bytes = 10 pages, then find the result data block one by one based on the pointer on the index block, so that the IO traffic is much less.

Ii. Index Optimization Technology

Is retrieval faster if there is an index? Answer. In some cases, using indexes is not as fast as using indexes. For example, if you want to retrieve all the records in the above table, if you do not need an index, you need to access 8000x1000 bytes/8 K Bytes = 1000 pages. If you want to use an index, you must first retrieve the index, access 8000 pages x 10 bytes/8 K Bytes = 10 pages to obtain the index search results. Then, retrieve the corresponding data page based on the index search results. Because all data is retrieved, therefore, you need to access 8000 more lines X 1000 bytes/8 K Bytes = 1000 pages to read all the data. A total of 1010 pages are accessed, which is obviously not as fast as that without indexes.

SQL Server has a complete set of data retrieval optimization technologies. In the above cases, SQL Server's Search Plan will automatically retrieve data using Table scanning instead of using any indexes. So how does SQL Server know when to use indexes and when to not use indexes? In addition to daily data maintenance, SQL Server also maintains data statistics, which is one of the database attribute pages:

What is the essential difference between clustered index and non-clustered index? When Will clustering indexes be used and when will non-clustering indexes be used?

This is a complicated problem and it is difficult to make it clear in a few words. Here is a brief introduction from the perspective of SQL Server INDEX OPTIMIZATION query (if you are interested in this aspect, you can read the data structure introduction of unit 2000 in Microsoft SQL Server 3rd database programming and unit 6th, 13, and 14 published by Microsoft ).
We can see that SQL Server automatically maintains statistics, including data density and data distribution information, this information helps SQL Server determine how to create a query plan, whether to use indexes, and what indexes are used for queries (here we will not explain how they helped SQL Server establish a query plan ). Let's make an experiment. Create a table: tabTest (ID, unqValue, intValue), where ID is an automatically numbered primary index, and unqValue is of the uniqueidentifier type. Create a common index and intValue is an integer, no index is created. The reason why an index-free intValue field is mounted is to prevent SQL Server from overwriting the query optimization technology using the index, so that the experiment will not function. Enter 10000 random records into the table. The Code is as follows:

Code

 CREATE TABLE [dbo].[tabTest] (
[ID] [int] IDENTITY (1, 1) NOT NULL ,
[unqValue] [uniqueidentifier] NOT NULL ,
[intValue] [int] NOT NULL
) ON [PRIMARY]
GO

ALTER TABLE [dbo].[tabTest] WITH NOCHECK ADD
CONSTRAINT [PK_tabTest] PRIMARY KEY  CLUSTERED
(
[ID]
)  ON [PRIMARY]
GO

ALTER TABLE [dbo].[tabTest] ADD
CONSTRAINT [DF_tabTest_unqValue] DEFAULT (newid()) FOR [unqValue]
GO

CREATE  INDEX [IX_tabTest_unqValue] ON [dbo].[tabTest]([unqValue]) ON [PRIMARY]
GO

declare @i int
declare @v int

set @i=0
while @i<10000
begin
set @v=rand()*1000    
insert into tabTest ([intValue]) values (@v)
set @i=@i+1
end

Then we execute two queries and view the execution plan. (The query plan can be opened in the query menu of the query analyzer. At the same time, The GUID of the first query on the graph is obtained from the database, you can set this parameter based on the values in your database ):

As you can see, in the first query, SQL Server uses the IX_tabTest_unqValue index. Based on the arrow direction, the computer first finds the index range, use Bookmark Lookup to map the index node to the data node, and finally give the SELECT result. In the second query, the system directly traverses the table to give the result, but it uses the clustered index. Why? Don't forget, the page node of the clustered index is the data node! In this way, clustering indexes will be faster (not affected by the storage holes left by data deletion and update, and directly traversing data will skip these holes ).

Next, in SQL Server, we change the clustered index of the ID field to a non-clustered index, and then execute select * from tabTest. The execution plan we see is changed:

Instead of using any indexes, SQL Server directly executes Table Scan, because only in this way can the retrieval efficiency be the highest.

Iii. essential differences between clustered indexes and non-clustered Indexes

Now we can discuss the essential differences between clustered indexes and non-clustered indexes. As shown in the preceding two figures, the leaf node of the clustered index is the data node, and the page node of the non-clustered index is still the index checkpoint, and keep a link pointing to the corresponding data block.

Let's look at their differences through a mathematical question: Suppose there is a table with 8000 records, each of which occupies 1000 bytes on the disk, if you create a primary key for a non-clustered index on a 10-byte long field, you need 16000 Binary Tree nodes (these 16000 nodes have 8000 leaf nodes, each page node points to a data record. In this way, the data occupies 8000x1000 bytes/8 K Bytes = 1000 pages; the index occupies 16000 nodes × 10 bytes/8 K Bytes = 20 pages, totaling 1020 pages.

In the same table, if we create a primary key for the clustered index on the corresponding field, because the page node of the clustered index is a data node, there are only 8000 index nodes, occupying 10 pages, data still occupies 1000 pages.

Next, let's take a look at why the primary key of the non-clustered index is faster than the primary key of the clustered index during the insert operation. The primary key constraint requires that the primary key should not be repeated. How does SQL Server know that the primary key should not be repeated? The only method is search. For non-clustered indexes, you only need to retrieve 16000 nodes on 20 pages to check whether there are duplicates, because all primary key values are included in these 16000 index nodes. However, for clustered indexes, the index node only contains 8000 intermediate nodes. As to whether there will be duplicates, You must retrieve the remaining 8000 data pages, it is equivalent to searching 10 + 1000 = 1010 pages to check whether there are duplicates. Therefore, the insertion speed of the clustered index primary key is much slower than that of the non-clustered index primary key.

Let's take a look at the efficiency of data retrieval. If you search for the above two tables, you can use the index (sometimes the SQL Server execution plan will choose not to use the index, however, let's assume that you must use indexes here. For clustered index retrieval, we may access 10 index pages and 1000 data pages to get the results (which is better in actual situations ), for non-clustered indexes, the system will find qualified nodes from 20 pages and map them to 1000 data pages (this is also the worst case, when one accesses 1010 pages and the other accesses 1020 pages, the retrieval efficiency is not very different. Therefore, no matter whether it is a non-clustered index or a clustered index, it is suitable for sorting. The clustered index is only a little faster than the non-clustered index.

Conclusion

The experiment on clustering index and non-clustering index efficiency will not be done. If you are interested, you can use the query analyzer to analyze the query plan. SQL Server is a complicated system, especially the index and query optimization technologies. Oracle is more complicated. Understanding the things behind indexes and queries is not a bad thing. It can help us better understand our system.

-------------------------------------

Non-clustering is certainly advantageous for updates
However, the retrieval performance will not suffer much loss.

So it is the best if you don't need clustering.
However, if \ order by is used
The advantages of clustering should also be obvious.

-------------------------------------

There are two types of indexes: clustered index and non-clustered index.

In a clustered index, the leaf page of the index tree contains actual data: The index order of the records is the same as that of the physical order.
In a non-clustered index, the leaf-level page points to the records in the table: the physical sequence of records is not necessarily related to the logical sequence.

The cluster index is very similar to a directory table. The order of the Directory table is the same as that of the actual page number. Non-clustered indexes are more like the standard index table of books. The order of the index table is usually different from that of the actual page number. A book may have multiple indexes. For example, it may have both the subject index and the author index. Similarly, a table can have multiple non-clustered indexes.

Generally, you use clustered indexes, but you should understand the advantages and disadvantages of both types of indexes.

Each table can have only one clustered index, because records in one table can only be stored in one physical order. Generally, you need to create a clustered index for a table based on the Identification field. However, you can also create a clustered index for other types of fields, such as numeric, numeric, and datetime fields.
Retrieving data from a table with a clustered index is faster than creating a non-clustered index. When you need to retrieve data within a certain range, it is better to use clustered indexes than to use non-clustered indexes. For example, suppose you use a table to record the activities of visitors on your site. If you want to retrieve the login information within a certain period of time, you should create a clustered index for the DATETIME type field in this table.
The main restriction on clustering indexes is that each table can only create one clustering index. However, a table can have more than one non-clustered index. In fact, you can create up to 249 non-clustered indexes for each table. You can also create clustering indexes and non-clustering indexes for a table at the same time.
Assume that you want to retrieve data not only by date, but also by user name from your site activity log. In this case, creating a clustered index and a non-clustered index at the same time is effective. You can create a clustered index for the date and time fields and a non-clustered index for the user name field. If you find that you need more indexing methods, you can add more non-clustered indexes.
Non-clustered indexes require a large amount of hard disk space and memory. In addition, although non-clustered indexes can speed up data retrieval from tables, they can also speed up data insertion and update to tables. Whenever you change the data in a table with a non-clustered index, you must update the index at the same time. Therefore, you must carefully consider creating a non-clustered index for a table. If you expect a table to frequently update data, do not create too many non-clustered indexes on it. In addition, if the hard disk and memory space are limited, you should also limit the number of non-clustered indexes.

Index attributes

Both types of indexes have two important attributes:
You can use either of the two types to index multiple fields at the same time (Composite Index );
Both types of indexes can be specified as unique indexes.
You can create a composite index or even a composite clustered index for multiple fields. Assume that a table records the surnames and names of visitors at your sites. If you want to retrieve data from the table based on the full name, you need to create an index for both the Last Name field and the name field. This is different from creating a separate index for the two fields respectively. When you want to query more than one field at the same time, you should create an index for multiple fields. If you want to query each field separately, you should create an independent index for each field.
Both types of indexes can be specified as unique indexes. If you create a unique index for a field, you cannot enter duplicate values for the field. An ID field automatically becomes a unique value field, but you can also create a unique index for other types of fields. Assume that you use a table to save the user password of your website. Of course, you do not want two users to have the same password. By forcing a field to become a unique value field, you can prevent this situation.

Http://hi.baidu.com/guobeilei/blog/item/51f55afbda311e116c22eb0e.html

 

The clustered index sorts and stores the data rows based on the key values of data rows in the table. Each table can have only one clustered index, because data rows can only be stored in one order. For more information about the clustered index architecture, see clustered index structure.

Almost every table defines clustered indexes for columns to implement the following functions:

• It can be used for frequently used queries.
• Provides high uniqueness.

  Note:
  When you create a primary key constraint, a unique index is automatically created on the column. By default, this index is a clustered index, but you can specify to create a non-clustered index when creating constraints.

   

• It can be used for range query.

If the UNIQUE attribute is not used to create a clustered index, the database engine automatically adds a 4-byteUniqueifierColumn. If necessary, the database engine automatically addsUniqueifierTo make each key unique. This column and column value are used internally and cannot be viewed or accessed by users.

Query considerations

Before creating a clustered index, you should first understand how data is accessed. Consider using clustered indexes for queries with the following features:

• Return a series of values using operators (such as BETWEEN,>, >=, <, and <=.

  When a clustered index is used to locate the row that contains the first value, the rows that contain the subsequent index value are physically adjacent. For example, if a query searches records between a series of sales order numbers,SalesOrderNumberThe clustered index of a column can quickly locate the row containing the starting sales order number, and then retrieve all consecutive rows in the table until the last sales order number is retrieved.

• Returns a large result set.
• The JOIN clause is generally used as a foreign key column.
• Use the order by or group by clause.

  The index of the column specified in the order by or group by clause does not need to be sorted BY the database engine because these rows have been sorted. This improves the query performance.

Column considerations

Generally, the fewer columns used when defining the clustered index key, the better. Consider columns with one or more of the following attributes:

• Unique or contains many unique values

  For example, an employee ID uniquely identifies an employee.EmployeeIDThe clustered index or primary key constraint of a column improves the query performance of searching employee information based on employee ID numbers. In addition, you canLastName,FirstName,MiddleNameCreate a clustered index for a column because it is often used to group and query employee records in this way, and the combination of these columns also provides a high degree of discrimination.

• Accessed in order

  For example, the product ID is uniquely identifiedAdventureWorksDatabaseProduction. ProductTable. Specify the query for sequential search (for exampleWHERE ProductID BETWEEN 980 and 999)ProductIDThe clustered index. This is because rows are stored in the sort order of the key column.

• Because the column is unique in the table, it is defined as IDENTITY.
• It is often used to sort the retrieved data in a table.

  Clustering by this column (physical sorting) is a good way to save the cost of sorting operations each time you query this column.

Clustered indexes are not applicable to columns with the following attributes:

• Columns frequently changed

  This will move the entire row because the database engine must physically keep the data values in the row. Pay special attention to this because the data in the large-capacity transaction processing system is usually variable.

• Width key

  A wide key is a combination of several or several large columns. All non-clustered indexes use the key values in the clustered index as the search key. Any non-clustered index defined for the same table will be much larger, because the non-clustered index item contains the clustering key and the key column defined for this non-clustered index.

Index options

You can specify several index options when creating a clustered index. Because clustered indexes are usually large, pay special attention to the following options:

• SORT_IN_TEMPDB
• DROP_EXISTING
• FILLFACTOR
• ONLINE