为何数据库文件无法同时拥有Primary Index与Clustering Index？是否受物理排序字段限制？

Great question! Let's unpack this clearly by first defining the two index types, then breaking down the core constraint that prevents them from coexisting.

Key Definitions #

• Primary Index: This is an index built on a table's primary key—a field (or set of fields) that uniquely identifies every row and cannot be null. It enforces logical uniqueness, but its link to physical storage depends on the database system.
• Clustering Index: This index directly defines the physical storage order of rows on disk. When a table has a clustering index, all rows are stored in the exact sorted order of the index's key values. This is a fundamental property of the underlying file—data can only be arranged in one linear sequence on storage media.

The Core Reason for Mutual Exclusivity #

Your intuition is spot-on: a file (database table) can only have one physical sort order, and the clustering index is what locks in that order. Here's how this ties to the mutual exclusivity:

• In most databases (like MySQL's InnoDB), the primary index is by default the clustering index. The primary key simultaneously enforces uniqueness and dictates how data is physically sorted on disk—so there's no separate "clustering index" to exist alongside it.
• If you explicitly set a non-primary field as the clustering index (possible in systems like SQL Server), the primary index becomes a non-clustering index. It still enforces uniqueness via a separate index structure, but the clustering index already controls the physical storage order. You still can't have two clustering indexes (one for the primary key and another for a different field) because you can't sort the same physical data in two conflicting ways at once.

Think of it like arranging a list of books on a shelf: you can sort them by title or by publication date, but you can't sort them by both at the same time. The clustering index is that single shelf-sorting rule, and a primary index either is that rule or exists independently—but you can't have two competing rules for the same set of data.

内容的提问来源于stack exchange，提问作者Tenji Kiriyama