Documentation Home
MySQL 8.0 リファレンスマニュアル
Download this Manual
PDF (US Ltr) - 36.1Mb
PDF (A4) - 36.2Mb

このページは機械翻訳したものです。

8.2.1.4 ハッシュ結合の最適化

MySQL 8.0.18 以降、MySQL では、各結合に等価結合条件があり、次のような結合条件に適用できるインデックスがないクエリーに対してハッシュ結合が使用されます: 

Press CTRL+C to copy
 
SELECT *
    FROM t1
    JOIN t2
        ON t1.c1=t2.c1;

ハッシュ結合は、単一テーブルの述語に使用できるインデックスが 1 つ以上ある場合にも使用できます。

ハッシュ結合は通常、以前のバージョンの MySQL で採用されていたブロックネストループアルゴリズム (Block Nested Loop 結合アルゴリズム を参照) のかわりに、このような場合に使用することを目的としています。 MySQL 8.0.20 以降では、ブロックネストループのサポートが削除され、以前にブロックネステッドループが使用されていた場所では、サーバーはハッシュ結合を採用します。

前述の例およびこのセクションの残りの例では、次のステートメントを使用して t1、t2 および t3 の 3 つのテーブルが作成されていることを前提としています: 

Press CTRL+C to copy
 
CREATE TABLE t1 (c1 INT, c2 INT);
CREATE TABLE t2 (c1 INT, c2 INT);
CREATE TABLE t3 (c1 INT, c2 INT);

ハッシュ結合が採用されていることは、次のように EXPLAIN を使用して確認できます: 

Press CTRL+C to copy
 
mysql> EXPLAIN
    -> SELECT * FROM t1
    ->     JOIN t2 ON t1.c1=t2.c1\G
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: t1
   partitions: NULL
         type: ALL
possible_keys: NULL
          key: NULL
      key_len: NULL
          ref: NULL
         rows: 1
     filtered: 100.00
        Extra: NULL
*************************** 2. row ***************************
           id: 1
  select_type: SIMPLE
        table: t2
   partitions: NULL
         type: ALL
possible_keys: NULL
          key: NULL
      key_len: NULL
          ref: NULL
         rows: 1
     filtered: 100.00
        Extra: Using where; Using join buffer (hash join)

(MySQL 8.0.20 より前では、ハッシュ結合が特定の結合に使用されていたかどうかを確認するには、FORMAT=TREE オプションを含める必要がありました。)

EXPLAIN ANALYZE には、使用されているハッシュ結合に関する情報も表示されます。

ハッシュ結合は、次に示すように、テーブルのペアごとに少なくとも 1 つの結合条件が等価結合であるかぎり、複数の結合を含むクエリーにも使用されます: 

Press CTRL+C to copy
 
SELECT * FROM t1
    JOIN t2 ON (t1.c1 = t2.c1 AND t1.c2 < t2.c2)
    JOIN t3 ON (t2.c1 = t3.c1);

内部結合を使用する前述のような場合、等価結合ではない追加の条件は、結合の実行後にフィルタとして適用されます。 (左結合、準結合、アンチ結合などの外部結合の場合は、結合の一部として出力されます。) これは、EXPLAIN の出力に表示されます: 

Press CTRL+C to copy
 
mysql> EXPLAIN FORMAT=TREE
    -> SELECT *
    ->     FROM t1
    ->     JOIN t2
    ->         ON (t1.c1 = t2.c1 AND t1.c2 < t2.c2)
    ->     JOIN t3
    ->         ON (t2.c1 = t3.c1)\G
*************************** 1. row ***************************
EXPLAIN: -> Inner hash join (t3.c1 = t1.c1)  (cost=1.05 rows=1)
    -> Table scan on t3  (cost=0.35 rows=1)
    -> Hash
        -> Filter: (t1.c2 < t2.c2)  (cost=0.70 rows=1)
            -> Inner hash join (t2.c1 = t1.c1)  (cost=0.70 rows=1)
                -> Table scan on t2  (cost=0.35 rows=1)
                -> Hash
                    -> Table scan on t1  (cost=0.35 rows=1)

前述の出力からもわかるように、複数の等価結合条件を持つ結合には複数のハッシュ結合を使用できます。

MySQL 8.0.20 より前は、結合テーブルのいずれかのペアに等価結合条件がなく、より遅いブロックのネステッドループアルゴリズムが使用されていた場合、ハッシュ結合は使用できませんでした。 MySQL 8.0.20 以降では、次に示すように、ハッシュ結合が使用されます: 

Press CTRL+C to copy
 
mysql> EXPLAIN FORMAT=TREE
    -> SELECT * FROM t1
    ->     JOIN t2 ON (t1.c1 = t2.c1)
    ->     JOIN t3 ON (t2.c1 < t3.c1)\G
*************************** 1. row ***************************
EXPLAIN: -> Filter: (t1.c1 < t3.c1)  (cost=1.05 rows=1)
    -> Inner hash join (no condition)  (cost=1.05 rows=1)
        -> Table scan on t3  (cost=0.35 rows=1)
        -> Hash
            -> Inner hash join (t2.c1 = t1.c1)  (cost=0.70 rows=1)
                -> Table scan on t2  (cost=0.35 rows=1)
                -> Hash
                    -> Table scan on t1  (cost=0.35 rows=1)

(追加の例は、このセクションの後半で説明します。)

ハッシュ結合はデカルト積にも適用されます。つまり、結合条件が指定されていない場合は、次のようになります: 

Press CTRL+C to copy
 
mysql> EXPLAIN FORMAT=TREE
    -> SELECT *
    ->     FROM t1
    ->     JOIN t2
    ->     WHERE t1.c2 > 50\G
*************************** 1. row ***************************
EXPLAIN: -> Inner hash join  (cost=0.70 rows=1)
    -> Table scan on t2  (cost=0.35 rows=1)
    -> Hash
        -> Filter: (t1.c2 > 50)  (cost=0.35 rows=1)
            -> Table scan on t1  (cost=0.35 rows=1)

MySQL 8.0.20 以降では、ハッシュ結合を使用するために、結合に少なくとも 1 つの等価結合条件を含める必要がなくなりました。 つまり、ハッシュ結合を使用して最適化できるクエリーのタイプには、次のリスト (および例) のクエリーが含まれます:

  • 内部非等価結合: 

    Press CTRL+C to copy
     
    mysql> EXPLAIN FORMAT=TREE SELECT * FROM t1 JOIN t2 ON t1.c1 < t2.c1\G
*************************** 1. row ***************************
EXPLAIN: -> Filter: (t1.c1 < t2.c1)  (cost=4.70 rows=12)
    -> Inner hash join (no condition)  (cost=4.70 rows=12)
        -> Table scan on t2  (cost=0.08 rows=6)
        -> Hash
            -> Table scan on t1  (cost=0.85 rows=6)

  • 準結合: 

    Press CTRL+C to copy
     
    mysql> EXPLAIN FORMAT=TREE SELECT * FROM t1 
    ->     WHERE t1.c1 IN (SELECT t2.c2 FROM t2)\G
*************************** 1. row ***************************
EXPLAIN: -> Nested loop inner join
    -> Filter: (t1.c1 is not null)  (cost=0.85 rows=6)
        -> Table scan on t1  (cost=0.85 rows=6)
    -> Single-row index lookup on <subquery2> using <auto_distinct_key> (c2=t1.c1)
        -> Materialize with deduplication
            -> Filter: (t2.c2 is not null)  (cost=0.85 rows=6)
                -> Table scan on t2  (cost=0.85 rows=6)

  • アンチ結合: 

    Press CTRL+C to copy
     
    mysql> EXPLAIN FORMAT=TREE SELECT * FROM t2 
    ->     WHERE NOT EXISTS (SELECT * FROM t1 WHERE t1.col1 = t2.col1)\G
*************************** 1. row ***************************
EXPLAIN: -> Nested loop antijoin
    -> Table scan on t2  (cost=0.85 rows=6)
    -> Single-row index lookup on <subquery2> using <auto_distinct_key> (c1=t2.c1)
        -> Materialize with deduplication
            -> Filter: (t1.c1 is not null)  (cost=0.85 rows=6)
                -> Table scan on t1  (cost=0.85 rows=6)

  • 左外部結合: 

    Press CTRL+C to copy
     
    mysql> EXPLAIN FORMAT=TREE SELECT * FROM t1 LEFT JOIN t2 ON t1.c1 = t2.c1\G
*************************** 1. row ***************************
EXPLAIN: -> Left hash join (t2.c1 = t1.c1)  (cost=3.99 rows=36)
    -> Table scan on t1  (cost=0.85 rows=6)
    -> Hash
        -> Table scan on t2  (cost=0.14 rows=6)

  • 右外部結合 (MySQL では、すべての右外部結合が左外部結合としてリライトされることを確認します): 

    Press CTRL+C to copy
     
    mysql> EXPLAIN FORMAT=TREE SELECT * FROM t1 RIGHT JOIN t2 ON t1.c1 = t2.c1\G
*************************** 1. row ***************************
EXPLAIN: -> Left hash join (t1.c1 = t2.c1)  (cost=3.99 rows=36)
    -> Table scan on t2  (cost=0.85 rows=6)
    -> Hash
        -> Table scan on t1  (cost=0.14 rows=6)

デフォルトでは、MySQL 8.0.18 以降では可能なかぎりハッシュ結合が使用されます。 ハッシュ結合を使用するかどうかは、BNL オプティマイザヒントと NO_BNL オプティマイザヒントのいずれかを使用して制御できます。

(MySQL 8.0.18 は、optimizer_switch サーバーシステム変数の設定の一部として hash_join=on または hash_join=off をサポートし、オプティマイザヒント HASH_JOIN または NO_HASH_JOIN もサポートしていました。 MySQL 8.0.19 以降では、これらは効果がなくなりました。)

ハッシュ結合によるメモリー使用量は、join_buffer_size システム変数を使用して制御できます。ハッシュ結合では、この量を超えるメモリーを使用できません。 ハッシュ結合に必要なメモリーが使用可能な量を超えると、MySQL はディスク上のファイルを使用してこれを処理します。 これが発生した場合、ハッシュ結合がメモリーに収まらず、open_files_limit に設定されているよりも多くのファイルが作成されると、結合が成功しない可能性があることに注意してください。 このような問題を回避するには、次のいずれかの変更を行います:

  • ハッシュ結合がディスクにオーバーフローしないように、join_buffer_size を増やします。

  • open_files_limit を増やします。

MySQL 8.0.18 以降、ハッシュ結合の結合バッファは増分的に割り当てられるため、非常に大量の RAM を割り当てる小さいクエリーなしに join_buffer_size をより高く設定できますが、外部結合ではバッファ全体が割り当てられます。 MySQL 8.0.20 以降では、外部結合 (アンチ結合および準結合を含む) にもハッシュ結合が使用されるため、これは問題ではなくなりました。


PREV   HOME   UP   NEXT
Download this Manual
PDF (US Ltr) - 36.1Mb
PDF (A4) - 36.2Mb