mysql添加联合索引之后排序发生变化？

Question

mysql添加联合索引之后排序发生变化。

create table sys_follow(
    id bigint UNSIGNED primary key,
    biz_id BIGINT UNSIGNED not null,
    status TINYINT(4) DEFAULT 0,
    creation_time TIMESTAMP DEFAULT CURRENT_TIMESTAMP
)

insert into sys_follow(id,biz_id,status) VALUES(1,21,1)

insert into sys_follow(id,biz_id,status, creation_time) VALUES(2,21,0, '2024-10-21 11:11:11')

select * from sys_follow where biz_id = 21;

在未创建索引的情况下，是按照id升序排列。

create index idx_biz_id_status on sys_follow(biz_id,status);

select * from sys_follow where biz_id = 21;

在创建完这条索引之后，将会按照 id 降序排列。除非手动指定 order by id asc .

请问这是什么原因导致的呢？

找不到具体原因。

Answer 1

创建了联合索引 idx_biz_id_status 后，MySQL 优化器会选择使用这个索引来加速查询。由于索引是按照 biz_id 和 status 排序的，查询结果会受到索引顺序的影响。

Answer 2

回答

在 MySQL 中，添加索引后查询结果的排序发生变化，这并不是因为索引本身改变了数据的物理存储顺序，而是因为查询优化器选择了不同的执行计划。

解释

1. 未添加索引时的查询：

   • 当没有索引时，MySQL 通常会使用全表扫描来执行查询。
   • 默认情况下，如果没有 ORDER BY 子句，MySQL 不保证查询结果的排序。尽管在某些情况下（如主键顺序插入的数据），结果可能看起来是按某种顺序排列的，但这只是巧合，不应作为依赖。
   • 在你的例子中，由于没有 ORDER BY 子句，结果按 id 升序排列可能只是数据插入顺序的反映。

2. 添加索引后的查询：

   • 添加 idx_biz_id_status 索引后，MySQL 优化器可能会选择使用这个索引来加速查询。
   • 索引 idx_biz_id_status 是按 (biz_id, status) 排序的。因此，使用此索引检索到的数据将按 biz_id 和 status 的顺序排列。
   • 由于索引不包含 id 列，MySQL 在使用索引检索到数据后，可能会以任何它认为高效的方式返回结果集。这通常意味着结果集不会按 id 排序，除非明确指定了 ORDER BY 子句。
   • 在你的例子中，结果集看起来按 id 降序排列，这很可能是 MySQL 在处理索引和数据检索时的内部机制导致的。然而，这种排序是不稳定的，可能会因数据变化或查询优化器的决策而改变。

结论

• 不要依赖未指定 ORDER BY 的查询结果的排序。如果希望结果按特定顺序排列，应始终使用 ORDER BY 子句。
• 索引用于加速查询，而不是保证排序。索引的创建可能会改变查询优化器的决策，从而影响结果集的返回顺序。

在你的例子中，如果希望结果按 id 升序排列，应添加 ORDER BY id ASC 子句到查询中。