Clustered index & non-clustered Index

The purpose of creating an index is to speed up the search or sorting of records in the table. Price:

1. Added database storage space

2. It takes a lot of time to insert and modify data (because the index also needs to change ).

 

I. Index category

Indexes can be divided into clustered indexes and non-clustered indexes.

Each table can have only one clustered index, because records in one table can only be stored in one physical order. However, a table can have more than one non-clustered index. In fact, you can create a maximum

249 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 predict that a table needs to be updated frequently

Therefore, 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.

 

II. Differences

1. The leaf node of the clustered index is the data node, but the page node of the non-clustered index is still the index checkpoint, and a link is retained to point to the corresponding data block.

2. The insertion speed of the clustered index primary key is much slower than that of the non-clustered index primary key.

3. In contrast, clustering indexes are suitable for sorting, and non-clustering indexes are not suitable for sorting. Because the clustered index itself is already placed in physical order, and the sorting is fast. Non-clustered indexes are not stored in order,

Additional resources are required for sorting.

4. When you need to retrieve data within a certain range, it is better to use clustered indexes than to use non-clustered indexes.

 

3. Idea of building clustered Index

1. Most tables should have clustered indexes or use partitions to reduce competition on the last page of the table. In a highly transactional environment, blocking the last page seriously affects the system throughput.

2. In the clustered index, data is physically arranged on the data page in order, and duplicate values are also arranged together, therefore, you can use group by or

In order by query, once a row with the first key value in the range is found, the row with the subsequent index value is physically contiguous without further searching, this avoids extensive scanning.

3. When you create a clustered index on a table with frequent insert operations, do not create columns with monotonic appreciation. Otherwise, blocking conflicts may often occur.

4. Do not include frequently modified columns in the clustered index, because after the code value is modified, the data row must be moved to a new location.

5. Select the cluster index based on the where clause and connection operation type.

 

4.Clustered index Columns

1. Primary Key column, which is used in the WHERE clause and inserted randomly.

2. Columns accessed by range, such as pri_order> 100 and pri_order <200.

3. Columns used in group by or order.

4. columns that are not frequently modified.

5. Columns Used in connection operations.

 

5. MySQLClustered Index

1. InnoDBUseB-treeAnd store the index and data in the same structure.

 

2. Only leaf nodes include the values of data rows. Non-leaf nodes only store index fields.

 

3. InnoDB can only perform clustering based on the primary key.If no primary key is defined in the table, InnoDB selects an index that is not empty and has unique constraints for clustering. If no such index exists,

Invisible primary key, and cluster with this primary key.

 

4. InnoDB clusters records together only within a page. pages with adjacent key values may be distant from each other.

 

The clusters of InnoDB are page-level, that is, the rows with adjacent cluster index values are stored on the same page. When the page is full, the next adjacent lines are saved to another page, and the two pages can

It can be far away. The index value is 1 ~ 10 rows exist on the first page, 11 ~ 20 rows are stored on another page, and data is clustered on a single page, while disks on pages 1 and 2

The location may be far away.