使用 GROUP BY 与 DISTINCT 时的巨大性能差异

Question

我正在 HSQLDB 服务器上执行一些测试，其中的表包含 500 000 个条目。该表没有索引。有 5000 个不同的业务密钥。我需要他们的名单。

自然地，我从 DISTINCT 查询开始：

SELECT DISTINCT business_key
FROM memory
WHERE concept <> 'case'   OR 
      attrib  <> 'status' OR 
      value   <> 'closed';

大约需要 90 秒！

然后我尝试使用GROUP BY：

SELECT business_key
FROM memory
WHERE concept <> 'case'   OR 
      attrib  <> 'status' OR
      value   <> 'closed';
GROUP BY business_key

并且需要1秒！

为了找出差异，我运行了 EXLAIN PLAN FOR 但它似乎为两个查询提供了相同的信息。

EXLAIN PLAN FOR DISTINCT ...

isAggregated=[false]
columns=[
  COLUMN: PUBLIC.MEMORY.BUSINESS_KEY
]
[range variable 1
  join type=INNER
  table=MEMORY
  alias=M
  access=FULL SCAN
  condition = [    index=SYS_IDX_SYS_PK_10057_10058
    other condition=[
    OR arg_left=[
     OR arg_left=[
      NOT_EQUAL arg_left=[
       COLUMN: PUBLIC.MEMORY.CONCEPT] arg_right=[
       VALUE = case, TYPE = CHARACTER]] arg_right=[
      NOT_EQUAL arg_left=[
       COLUMN: PUBLIC.MEMORY.ATTRIB] arg_right=[
       VALUE = status, TYPE = CHARACTER]]] arg_right=[
     NOT_EQUAL arg_left=[
      COLUMN: PUBLIC.MEMORY.VALUE] arg_right=[
      VALUE = closed, TYPE = CHARACTER]]]
  ]
]]
PARAMETERS=[]
SUBQUERIES[]
Object References
PUBLIC.MEMORY
PUBLIC.MEMORY.CONCEPT
PUBLIC.MEMORY.ATTRIB
PUBLIC.MEMORY.VALUE
PUBLIC.MEMORY.BUSINESS_KEY
Read Locks
PUBLIC.MEMORY
WriteLocks

EXLAIN PLAN FOR SELECT ... GROUP BY ...

isDistinctSelect=[false]
isGrouped=[true]
isAggregated=[false]
columns=[
  COLUMN: PUBLIC.MEMORY.BUSINESS_KEY
]
[range variable 1
  join type=INNER
  table=MEMORY
  alias=M
  access=FULL SCAN
  condition = [    index=SYS_IDX_SYS_PK_10057_10058
    other condition=[
    OR arg_left=[
     OR arg_left=[
      NOT_EQUAL arg_left=[
       COLUMN: PUBLIC.MEMORY.CONCEPT] arg_right=[
       VALUE = case, TYPE = CHARACTER]] arg_right=[
      NOT_EQUAL arg_left=[
       COLUMN: PUBLIC.MEMORY.ATTRIB] arg_right=[
       VALUE = status, TYPE = CHARACTER]]] arg_right=[
     NOT_EQUAL arg_left=[
      COLUMN: PUBLIC.MEMORY.VALUE] arg_right=[
      VALUE = closed, TYPE = CHARACTER]]]
  ]
]]
groupColumns=[
COLUMN: PUBLIC.MEMORY.BUSINESS_KEY]
PARAMETERS=[]
SUBQUERIES[]
Object References
PUBLIC.MEMORY
PUBLIC.MEMORY.CONCEPT
PUBLIC.MEMORY.ATTRIB
PUBLIC.MEMORY.VALUE
PUBLIC.MEMORY.BUSINESS_KEY
Read Locks
PUBLIC.MEMORY
WriteLocks

---

编辑

我做了额外的测试。由于 HSQLDB 中有 500 000 条记录且具有所有不同的业务键，DISTINCT 的性能现在更好 - 3 秒，而 GROUP BY 则需要大约9秒。

在 MySQL 中，两个查询执行相同的操作：

MySQL：500 000 行 - 5 000 个不同的业务键： 两个查询：0.5 秒 MySQL：500 000 行 - 所有不同的业务键： 选择不同... - 11 秒 SELECT ... GROUP BY Business_key - 13 秒

因此问题仅与 HSQLDB 有关。

如果有人能解释为什么会有如此巨大的差异，我将非常感激。

Answer 1

这两个查询表达了相同的问题。显然查询优化器选择了两个不同的执行计划。我的猜测是，distinct 方法的执行方式如下：

• 将所有 business_key 值复制到临时表中
• 对临时表进行排序
• 扫描临时表，返回与以下内容不同的每个项目 扫描

group by 的执行方式如下：

• 全表，将 business key 的每个值存储在哈希表中
• 返回哈希表的键

第一种方法进行优化对于内存使用：当一部分临时表必须被换出。第二种方法优化了速度，但如果有很多不同的键，则可能需要大量内存。

由于您有足够的内存或很少有不同的密钥，因此第二种方法的性能优于第一种方法。两个执行计划之间出现 10 倍甚至 100 倍的性能差异并不罕见。