MySQL Clustered vs Secondary Index: Structure, Principle and Performance Deep Dive

Clustered Index

Storage Structure

B+Tree leaf nodes not only contain primary key index values but also directly store complete row record data

Features

• Data rows are actually stored in the leaf nodes of the index structure
• A table can only have one clustered index
• Physical storage order is consistent with index order

Construction Rules

1. Primary key priority principle
2. Unique index backup
3. Implicit row-id mechanism

Advantages

• High range query performance
• Excellent sort retrieval performance
• Primary key lookup requires no table lookup

Disadvantages

• Insert performance impact
• Large index key affects storage space

Primary Key Index vs Secondary Index Comparison

Primary Key Index

• Leaf nodes store complete row data
• Must be a clustered index
• Query requires only one index lookup

Secondary Index

• Leaf nodes store index field values and primary key values
• Requires two lookups: first secondary index, then clustered index
• Can create multiple

Table Lookup Query

Mechanism

1. Find matching index entries through secondary index tree
2. Obtain record’s primary key value
3. Use primary key value to find complete record in clustered index

Performance Analysis

• Requires two index lookups
• May require two disk I/O operations

Optimization Strategies

• Use covering index
• Reduce query columns
• Use primary key queries

Clustered Index Selection Suggestions

Type	Rating
Auto-increment integer	High, sequential insertion, reduces page splitting
UUID/random number	Low, random insertion, page splitting
String/long key value	Low, large space occupation

Comparison

Feature	Clustered Index	Non-clustered Index
Storage Structure	Index and data physically integrated	Index independent
Quantity Limit	Only one per table	Multiple per table
Suitable Scenarios	Range query, sorting	Exact condition query