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

What is the difference between clustered index and non-clustered index in mysql? In view of this problem, this article introduces the corresponding analysis and answers in detail, hoping to help more partners who want to solve this problem to find a more simple and feasible way.

In general, indexing is an effective means to speed up the query. However, indexing is not omnipotent, and fast access to all data can not be achieved by indexing. In fact, if the indexing strategy is seriously inconsistent with the data retrieval requirements, indexing will degrade the query performance. Therefore, in practical use, the cost of indexing should be fully taken into account, including disk space overhead and processing overhead (such as resource competition and locking). For example, if the data is updated or deleted frequently, it is not appropriate to build an index.

Introduction to mysql clustered index and non-clustered index:

Clustering Index:

Table data is stored sequentially, that is, the index order is the same as the physical storage order of table records.

The clustered index leaf node stores data rows and B-Tree indexes.

There can be only one clustered index in a table because there can be only one real physical storage order.

A clustered index is a sparse index, and the index storage at the upper level of the data page is a page pointer, not a row pointer.

Non-clustered index:

The order in which the table data is stored is independent of the index order.

For non-clustered indexes, the leaf node contains row primary key values and primary key values as "pointers" to rows, which can be reduced by secondary index lookup and twice B-Tree lookup InnoDB adaptive hash indexing.

A non-clustered index is a dense index that stores a record for each data row on the index page one level above the data page.

Storage features: 1. Clustered index. Table data is stored in the order of the index, that is, the order of the index items is consistent with the physical order of the records in the table. For clustered indexes, the leaf node stores real rows of data, and there are no separate data pages. You can create at most one clustered index on a table because there can be only one physical order of real data. 2. Nonclustered index. The order in which the table data is stored is independent of the index order. For nonclustered indexes, the leaf node contains the value of the index field and a logical pointer to the rows of the data page, the number of rows is the same as the amount of data in the rows of the data table.

To sum up: a clustered index is a sparse index, and the index page one level above the data page stores page pointers, not row pointers. For nonclustered indexes, it is a dense index, which stores an index record for each data row on the index page one level above the data page.

Update table data:

1. Insert a new data row into the table

If a table does not have a clustered index, it is called a Heap. There is no specific order for the data rows in such a table, and all new rows are added to the end of the table. The data table with a clustered index is different: in the simplest case, the insert operation finds the corresponding data page according to the index, then moves the existing records to make room for the new data, and finally inserts the data. If the data page is full, you need to split the data page and adjust the index pointer (and if the table has nonclustered indexes, you need to update those indexes to point to the new data page). Similar to the self-incrementing clustered index, the database system may not split the data pages, but simply add new data pages.

2. Delete data rows from the table

For deleting a data row: deleting a row causes the data row below it to move up to fill in the gaps caused by the deletion of the record. If the deleted row is the last row in the data page, the data page will be recycled and the records in the corresponding index page will be deleted. The deletion of the data may result in only one record in the index page, which may be moved to the adjacent index page, and the original index page will be recycled, which is called "index merge".

This is the answer to the question about what is the difference between clustered index and non-clustered index in mysql. I hope the above content can be of some help to you. If you still have a lot of doubts to be solved, you can follow the industry information channel for more related knowledge.

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.