Shulou(Shulou.com)05/31 Report--

This article mainly introduces what is the difference between clustered index and non-clustered index in mysql. It is very detailed and has certain reference value. Friends who are interested must read it!

Differences: 1, the clustered index stores the data in the table in the leaf node, while the non-clustered index stores the primary key and index columns in the leaf node; 2, the order of the table records in the clustered index is the same as that of the index, while the order of the non-clustered index is different; 3, the clustered index can have only one table, while the non-clustered index can have more than one.

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

The indexes of MySQL's Innodb storage engine can be divided into clustered indexes and nonclustered indexes. Clustered indexes and nonclustered indexes can be understood by comparing the indexes of Chinese dictionaries. Chinese dictionaries provide two kinds of ways to retrieve Chinese characters. The first is pinyin retrieval (if you know the pronunciation of the Chinese character). For example, the Chinese character whose pinyin is cheng is arranged after the Chinese character in Pinyin chang, and the page number of the corresponding Chinese character is found according to Pinyin (because the binary search can be located quickly according to Pinyin). This is what we usually call dictionary order. The second category is the first stroke retrieval, according to the stroke to find the corresponding Chinese characters, find the corresponding page number of Chinese characters. Pinyin retrieval is a clustered index, because the stored records (row data in the database, details records of Chinese characters in the dictionary) are sorted according to this index; stroke index, although words with the same stroke are adjacent in the stroke index, but the actual stored page number is 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.

The clustered index determines the physical order of the data in the table. A clustered index is similar to a phone book, which sorts data by last name. Clustered indexes are particularly effective for columns that often search for range values. After you use the clustered index to find the row containing the first value, you can ensure that the row containing the subsequent index value is physically adjacent. For example, if an application executes a query that often retrieves records within a range of dates, you can use a clustered index to quickly find the row that contains the start date, and then retrieve all adjacent rows in the table until the end date is reached. This helps to improve the performance of such queries. Similarly, if a column is often used to sort data retrieved from a table, you can aggregate the table on that column (physical sort) to avoid sorting every time the column is queried, saving costs.

The above is the b+tree index structure of innodb

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

1. Each node has at most m sub-nodes.

2. Except for the root node and the leaf node, each node has at least 2 (rounded up) sub-nodes.

3. If the root node is not a leaf node, the root node contains at least two sub-nodes.

4. All the leaf nodes are located in the same layer.

5. Each node contains k elements (keywords), where m _ ≤ 2 k

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.