What is the difference between mysql clustered index and non-clustered index

Difference: 1. The clustered index stores the data in the table in the leaf nodes, while the non-clustered index stores the primary key and index column in the leaf nodes; 2. The order and index of the table records in the clustered index The sorting order is consistent, but the sorting order of non-clustered indexes is inconsistent; 3. Each table can only have one clustered index, but there can be multiple non-clustered indexes.

The operating environment of this tutorial: windows7 system, mysql8 version, Dell G3 computer.

The indexes of MySQL's Innodb storage engine are divided into two categories: clustered indexes and non-clustered indexes. You can understand clustered indexes and non-clustered indexes by comparing the indexes of the Chinese dictionary. The Chinese dictionary provides two ways of retrieving Chinese characters. The first is pinyin retrieval (provided that the pronunciation of the Chinese character is known). For example, the Chinese character with the pinyin of cheng is ranked after the Chinese character with the pinyin of chang. The page number of the corresponding Chinese character is found based on the pinyin (because pressing Pinyin sorting, binary search can quickly locate), this is what we usually call dictionary order; the second type is radical stroke retrieval, find the corresponding Chinese characters according to the strokes, and find the page number corresponding to the Chinese character. Pinyin retrieval is a clustered index, because the stored records (row data in the database, detailed records of Chinese characters in the dictionary) are sorted according to this index; stroke index, although words with the same strokes are adjacent in the stroke index, the actual storage The page numbers are not adjacent, this is a non-clustered index.

Clustered Index

The logical order of the key values ​​in the index determines the physical order of the corresponding rows in the table.

Clustered index determines the physical order of data in the table. A clustered index is similar to a phone book in that the data is arranged by last name. Clustered indexes are particularly effective for columns where range values ​​are often searched. Once you use a clustered index to find the row containing the first value, you can ensure that rows containing subsequent index values ​​are physically adjacent. For example, if your application performs a query that frequently retrieves records within a certain date range, you can use a clustered index to quickly find the row containing the start date and then retrieve all adjacent rows in the table until the end date is reached. This helps improve the performance of such queries. Likewise, if a column is frequently used when sorting data retrieved from a table, the table can be clustered (physically sorted) on that column to save costs by not having to sort it each time that column is queried.

The above is the b tree index structure of innodb

We know that b tree evolved from b-tree, an m-order B-Tree It has the following characteristics:

1. Each node can have up to m child nodes.
2. Except for the root node and leaf nodes, each node has at least m/2 (rounded up) child nodes.
3. If the root node is not a leaf node, the root node contains at least two child nodes.
4. All leaf nodes are located on the same layer.
5. Each node contains k elements (keywords), where m/2≤k6. The elements (keywords) in each node are arranged from small to large.
7. The value of the left node of each element (keyword) is less than or equal to the element (keyword). The values ​​of the right node are greater than or equal to the element (keyword).

b The characteristics of tree are:

1. All non-leaf nodes only store keyword information.
2. All satellite data (specific data) are stored in leaf nodes.
3. All leaf nodes contain information about all elements.
4. There is a link pointer between all leaf nodes.

We found that b trre has the following characteristics:

• is particularly effective and fast for queries within a range (through the leaf chain pointer);
• is particularly effective for specific queries Key value query is only slightly less efficient than b-tree (because it has to go to the leaf level), but it can also be ignored;

Non-clustered index

Index The logical order of the indexes in the disk is different from the physical storage order of the rows on disk.

In fact, according to the definition, indexes other than clustered indexes are non-clustered indexes, but people want to subdivide non-clustered indexes into ordinary indexes, unique indexes, and full-text indexes. If we have to compare the non-clustered index to something in real life, then the non-clustered index is like the radical dictionary of Xinhua Dictionary, and its structural order is not necessarily consistent with the actual storage order.

The storage structure of the non-clustered index is the same as before. The difference is that the data part of the leaf node no longer stores specific data, but the aggregation of data The key of the index. Therefore, the process of searching through a non-clustered index is to first find the key of the clustered index corresponding to the index key, and then use the key of the clustered index to find the corresponding data on the primary key index tree. This process is called table return !

Give an example:

create table student (

`id` INT UNSIGNED AUTO_INCREMENT,

`username` VARCHAR(255),
`score` INT,
PRIMARY KEY(`id`), KEY(`username`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8;

Clustered index clustered index(id), non-clustered index index(username).

使用以下语句进行查询，不需要进行二次查询，直接就可以从非聚集索引的节点里面就可以获取到查询列的数据。

select id, username from t1 where username = '小明'
select username from t1 where username = '小明'

但是使用以下语句进行查询，就需要二次的查询去获取原数据行的score：

select username, score from t1 where username = '小明'

聚集索引和非聚集索引区别

区别一：

聚集索引：就是以主键创建的索引，在叶子节点存储的是表中的数据

非聚集索引：就是以非主键创建的索引(也叫做二级索引)，在叶子节点存储的是主键和索引列。

区别二：

聚集索引中表记录的排列顺序和索引的排列顺序一致；所以查询效率快，因为只要找到第一个索引值记录，其余的连续性的记录在物理表中也会连续存放，一起就可以查询到。缺点：新增比较慢，因为为了保证表中记录的物理顺序和索引顺序一致，在记录插入的时候，会对数据页重新排序。

非聚集索引中表记录的排列顺序和索引的排列顺序不一致。

区别三：

聚集索引是物理上连续存在，而非聚集索引是逻辑上的连续，物理存储不连续。

区别四：

聚集索引每张表只能有一个，非聚集索引可以有多个。

【相关推荐：mysql视频教程】

The above is the detailed content of What is the difference between mysql clustered index and non-clustered index. For more information, please follow other related articles on the PHP Chinese website!