[解決済み] GROUP BYとDISTINCTを使用した場合のパフォーマンスの大きな違い

質問

私はあるテストを 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

編集

追加でテストしてみました。500 000レコードで HSQLDB にある 500,000 件のレコードと、すべての明確なビジネス キーを使用した場合 DISTINCT のパフォーマンスは 3 秒と向上しています。 GROUP BY が約9秒かかっていたのに対して、3秒です。

で MySQL では、どちらのクエリも同じように実行されます。

MySQLを使用します。500 000 行 - 5 000 個のビジネス キー。 両方のクエリ 0.5秒 MySQLの場合 500 000行 - すべての異なるビジネス・キー。 SELECT DISTINCT ... - 11秒 SELECT ... GROUP BY business_key - 13秒

つまり、この問題は HSQLDB .

なぜこのような劇的な違いがあるのか、どなたか説明していただけると大変ありがたいのですが。

どのように解決するのですか？

2つのクエリは同じ質問を表現しています。 どうやら、クエリオプティマイザは 2 つの異なる実行計画を選択するようです。 私の推測では distinct のようなアプローチが実行されます。

• すべてコピー business_key の値をテンポラリテーブルにコピーします。
• テンポラリテーブルをソートする
• テンポラリテーブルをスキャンし、前の項目と異なる各項目を返す

は group by のように実行することができる。

• テーブル全体をスキャンし、それぞれの値を business key の各値をハッシュテーブルに格納します。
• ハッシュテーブルのキーを返す

最初の方法はメモリ使用量を最適化します。一時テーブルの一部をスワップアウトしなければならない場合でも、それなりにうまく動作するでしょう。 2番目の方法は速度を最適化しますが、多くの異なるキーがある場合、大量のメモリを必要とする可能性があります。

十分なメモリがあるか、異なるキーがほとんどないため、2 番目の方法は 1 番目の方法よりも性能が高くなります。 2 つの実行プランの間に 10 倍、あるいは 100 倍の性能差が生じることは珍しくありません。