InnoDB表二级索引值相同时是否会按主键排序？

Yes, when secondary index values in an InnoDB table are identical, the corresponding index entries will be sorted by the table's primary key.

Why this happens: #

InnoDB secondary indexes are structured such that each index entry includes not only the secondary key columns but also the table's primary key columns. Since the primary key is unique, this ensures every secondary index entry is uniquely identifiable—even when the secondary key values are the same. As a result, when multiple entries share the same secondary key value, they are ordered by their primary key values to maintain a consistent, sorted structure within the index.

Reference from MySQL Documentation: #

InnoDB sorts secondary index records by the primary key if the secondary key columns are equal.

Example Illustration: #

Suppose you have a table products defined as:

CREATE TABLE products (
    id INT PRIMARY KEY AUTO_INCREMENT,
    category VARCHAR(50),
    name VARCHAR(100)
);
CREATE INDEX idx_category ON products(category);

If you insert multiple rows with the same category value (e.g., 'electronics'), the idx_category index will store these entries sorted first by category, then by the primary key id. When querying with WHERE category = 'electronics', the database can retrieve the rows in primary key order directly from the secondary index without additional sorting.

内容的提问来源于stack exchange，提问作者chi tian