What is the difference between a clustered and a non-clustered index?
What is the difference between a clustered and a non-clustered index?
A clustered index defines the physical order of the table rows: the leaf level is the table data. A non-clustered (secondary) index is a separate structure whose leaves hold the indexed columns plus a pointer back to the row.
| Engine | Behaviour |
|---|---|
| SQL Server | One clustered index per table (by default the primary key, but it can be another column). A table without one is a heap. Non-clustered indexes point to the clustered key (or a row id in a heap). |
| MySQL InnoDB | The primary key is the clustered index (if absent, the first unique NOT NULL index, else a hidden row id). Secondary indexes store the primary key value as the pointer. |
| PostgreSQL | Tables are heaps; all indexes are secondary and point to a physical tuple location. CLUSTER reorders the table once but does not keep it ordered. |
| Oracle | Heap tables by default; index-organized tables (IOT) are the clustered equivalent. |
Đăng nhập để tham gia thảo luận cùng mọi người.
Chưa có bình luận nào
Hãy là người đầu tiên chia sẻ suy nghĩ của bạn.