时间戳

首页 > 解决方案 > 两个看似相同的数据库如何返回按不同列排序的结果？

mysql - 两个看似相同的数据库如何返回按不同列排序的结果？

问题描述

我有一个查询

SELECT r.id
     , r.account_id
     , r.name
     , r.bucket_id
     , r.description
     , r.development
     , r.created_at
     , r.priority
  FROM realms r
 WHERE r.account_id = 3;

我在两个具有相同索引的不同表上运行它，一个结果按r.idand排序r.created_at（无论哪种方式都相同），另一个结果按r.name. 怎么会这样？

通过 MySQL Workbench 中的表检查器查看它，两者的索引都是：

+---------------------------+-------+-----+-----------------+
| key                       |Type   |Uni  | Columns         |
+ --------------------------+-------+-----+-----------------+
| PRIMARY                   | BTREE | YES | id              |
| realms_account_id_name_UQ | BTREE | YES | account_id,name |
| realms_account_id_IX      | BTREE | NO  | account_id      |
| realms_bucket_id_IX       | BTREE | NO  | bucket_id       |
+---------------------------+-------+-----+-----------------+

我认为是索引决定了输入的顺序，当我在两者之间切换时屏幕甚至不会闪烁。如果他们两个的主键是id为什么一个显示按名称排序的结果？

标签： mysqlsortingindexing

解决方案

如果您的 中没有ORDER BY子句SELECT，则系统可以做任何想做的事情。时期。句号。

现在，我将解释可能发生的事情。

首先，优化器将分析索引、数据类型、统计信息等，并决定如何执行查询。您可以通过执行来了解此操作EXPLAIN SELECT ...。它会说它可能使用哪个索引。

我看到了两个合理的索引——两个以account_id. 任何一个都可以。可能优化器在两台机器上的统计数据略有不同，导致它在一台机器上选择一个索引，而在另一台机器上选择另一个。

使用分析INDEX(account_id, name)。该索引是 account_ids 和名称对的有序列表。在使用该索引的机器上，它向下钻取 BTree 索引到 的第一个条目account_id = 3，然后向前扫描。这为您提供了按 排序的结果name。

使用分析INDEX(account_id)。InnoDB，为了找到数据，将PRIMARY KEY列附加到每个二级索引上。因此，该指数有效INDEX(account_id, id)。在使用该索引的机器上，它向下钻取 BTree 索引到 的第一个条目account_id = 3，然后向前扫描。这为您提供了按 排序的结果id。

第三种可能性很常见，值得注意。如果有很多行account_id = 3，优化器将决定避开索引并简单地读取数据。由于数据是根据 存储的PRIMARY KEY，它会再次按id顺序传递行（但出于完全不同的原因）。

推荐阅读