Clustered index and non-clustered index

2026/08/282144 words5 min read

"TLDR: This article introduces the physical storage methods of clustered indexes and non-clustered indexes in MySQL. Clustered indexes store the table's index and data together, while non-clustered indexes store them separately. The article further analyzes the performance differences between the two index structures in query and modification operations: the clustered index is faster during query, but may cause B+ tree splitting and merging during modification, while the non-clustered index is relatively less time-consuming."

Clustered index/non-clustered index is not a logical index structure similar to primary key index, but a physical storage method. Taking MySQL as an example, the Innodb engine uses a clustered index, while myisam uses a non-clustered index.

We compare the physical structures of clustered indexes and non-clustered indexes from the file names:

The storage files of Innodb are: .frm and .idb. The former stores the structure of the table, and the latter stores the index and data of the internal data of the table.
The storage files of MyISAM are: .frm, .myd, myyi, which store the table structure, table data and table index respectively.

It is obvious that the so-called clustering refers to whether the index and data of the table are stored together.

Going deeper into the internal details of idb, as shown below, leaf nodes store data and indexes

The internal details of .myd and .myYI are as shown below. The leaf nodes store the addresses of the index and data. If you want to get the real address, you need to take the address and search it twice in the .myd file.

We continue to analyze the advantages and disadvantages of the two physical storages from two perspectives: query and modification:

Query:
- Clustered index: only one search is needed to find the data
- Non-clustered index: One search can only get the address of the data, and you need to use the address for a second search, which is slightly slower.
Modification: If we modify the key of the index, it may cause the split and merge of the B+ tree
- Clustered index: Splitting and merging leaf nodes requires moving the data itself and the index, which is more time-consuming
- Non-clustered index: The splitting and merging of leaf nodes only require moving the address and index of the data, which is relatively fast and the modification cost is small.

AI Slop: When the Cost of Content Approaches Zero, There Is No Turning Back

After 2023, the narrative around AI online has taken on a peculiar binary structure. On one side, there's the AGI frenzy. Every new model release triggers a wave of posts counting down to the "extinction of human professions." OpenAI's launch events now carry the ritualistic weight of Apple's keynote back in the day, with onlookers holding their breath for those few benchmark numbers—as if the moment the curve bends toward the upper right, the AGI singularity arrives tomorrow. On the other side, there are reflections worth paying attention to. Andrej Karpathy recently mentioned on a podcast that he now uses AI-generated articles for initial screening—not to gather information, but to find the things that "haven't been written by AI yet." Gary Marcus has repeatedly discussed in public how "the average quality of AI-generated content is converging toward the mean."

2026/08/28·16 min read

The SEO Dilemma: The Gap Between Ideals and Reality

1. The world has already entered an era of oversupply, where distribution and marketing may matter far more than production. Driving traffic is what truly determines survival. 2. SEO is something where experience outweighs technique. The SEO tips written in books, spanning dozens of pages—internal links, DR, authority, long-

Related

AI Slop: When the Cost of Content Approaches Zero, There Is No Turning Back

The SEO Dilemma: The Gap Between Ideals and Reality