巨大テーブル×超少数テーブルのJOINが異常に遅くなる件

@hoge 巨大テーブル×超少数 テーブルのJOINが 異常に遅くなる件

巨大テーブル×超少数テーブルのJOINが 異常に遅くなる件 100万行×3行のようなJOINが妙に遅くなる場合あり なんかHash join&テーブルスキャンが選ばれている =インデックスが使われない 100万行×10行にデータを増やすとNLJになって早くなったり (弊社で40s->1s位の実績あり) 一体何がどうなって？

サンプルデータ CREATE TABLE types ( id INT AUTO_INCREMENT PRIMARY KEY, name VARCHAR(255) ); CREATE TABLE mytable ( id INT AUTO_INCREMENT PRIMARY KEY, name VARCHAR(255), type_id INT NOT NULL, --これでJOIN value VARCHAR(255), FOREIGN KEY (type_id) REFERENCES types (id) ); 超単純なテーブル2つ マスタtypes (3行) データmytable (100万行)

サンプルデータ INSERT INTO types (name) VALUES ("TypeA"), ("TypeB"), ("TypeC"); INSERT INTO mytable (name, type_id, value) VALUES ('NAME-*', [1～3], 'hogehoge...'), (...), (...), ...; ↑100文字 ANALYZE TABLE mytable; ANALYZE TABLE types; マスタ3行 データ100万行

JOIN結果 JOINすると何故かHash Join＆テーブルスキャンが選ばれる mysql> explain analyze select * from mytable left join types on mytable.type_id = types.id; +----------------------------------------------------------------------------------------------------------------------------- -----------| EXPLAIN ------------------------------------------------------------------------------------------------------------------------------------------ | -> Left hash join (`types`.id = mytable.type_id) (cost=298684 rows=2.97e+6) (actual time=0.0436..247 rows=1e+6 loops=1) -> Table scan on mytable (cost=102801 rows=989158) (actual time=0.0164..177 rows=1e+6 loops=1) -> Hash -> Table scan on types (cost=0.00196 rows=3) (actual time=0.0169..0.0192 rows=3 loops=1) +----------------------------------------------------------------------------------------------------------------------------- ------------- 1 row in set (0.31 sec)

JOIN結果 ちょっと重くしてみる (group by追加) mysql> explain analyze select * from mytable left join types on mytable.type_id = types.id group by mytable.id; +----------------------------------------------------------------------------------------------------------------------------- -----------| EXPLAIN +----------------------------------------------------------------------------------------------------------------------------- -----------| -> Table scan on <temporary> (cost=601242..638338 rows=2.97e+6) (actual time=1289..1516 rows=1e+6 loops=1) -> Temporary table with deduplication (cost=601242..601242 rows=2.97e+6) (actual time=1289..1289 rows=1e+6 loops=1) -> Left hash join (`types`.id = mytable.type_id) (cost=304495 rows=2.97e+6) (actual time=0.0875..324 rows=1e+6 loops=1) -> Table scan on mytable (cost=108429 rows=989158) (actual time=0.0376..249 rows=1e+6 loops=1) -> Hash -> Table scan on types (cost=0.00783 rows=3) (actual time=0.0403..0.0424 rows=3 loops=1) +----------------------------------------------------------------------------------------------------------------------------- ------------ 1 row in set (1.58 sec)

マスタを増やすと・・・ マスタを3→10に増やしてみる INSERT INTO types (name) VALUES ("TypeD"), ("TypeE"), ("TypeF"), ("TypeG"), ("TypeH"),("TypeI"), ("TypeJ"); ANALYZE TABLE types; 追加分

マスタを増やすと・・・  NLJに変わる？？ インデックス使われるように  0.31s->0.61sとちょっと遅くなるが・・・ mysql> explain analyze select * from mytable left join types on mytable.type_id = types.id; +------------------------------------------------------------------------------------------------------------------------------| EXPLAIN +------------------------------------------------------------------------------------------------------------------------------| -> Nested loop left join (cost=452670 rows=989158) (actual time=0.073..543 rows=1e+6 loops=1) -> Table scan on mytable (cost=106465 rows=989158) (actual time=0.0642..223 rows=1e+6 loops=1) -> Single-row index lookup on types using PRIMARY (id=mytable.type_id) (cost=0.25 rows=1) (actual time=227e-6..241e-6 rows=1 loops=1e+6) +------------------------------------------------------------------------------------------------------------------------------- 1 row in set (0.61 sec)

マスタを増やすと・・・  group by追加版  こっちは1.58s->0.72sと早くなる  (極端な例だと弊社で40s->1sになった) mysql> explain analyze select * from mytable left join types on mytable.type_id = types.id group by mytable.id; +----------------------------------------------------------------------------------------------------------------------------| EXPLAIN +----------------------------------------------------------------------------------------------------------------------------| -> Group (no aggregates) (cost=550359 rows=989158) (actual time=0.0772..662 rows=1e+6 loops=1) -> Nested loop left join (cost=451443 rows=989158) (actual time=0.073..537 rows=1e+6 loops=1) -> Index scan on mytable using PRIMARY (cost=105238 rows=989158) (actual time=0.0652..216 rows=1e+6 loops=1) -> Single-row index lookup on types using PRIMARY (id=mytable.type_id) (cost=0.25 rows=1) (actual time=230e-6..243e-6 rows=1 loops=1e+6) +----------------------------------------------------------------------------------------------------------------------------- 1 row in set (0.72 sec)

マニュアルでは https://dev.mysql.com/doc/refman/8.0/ja/hash-joins.html

• https://dev.mysql.com/doc/refman/8.0/ja/hash-joins.html

マニュアルでは https://dev.mysql.com/doc/refman/8.3/en/hash-joins.html

• https://dev.mysql.com/doc/refman/8.3/en/hash-joins.html

巨大テーブル×超少数テーブルのJOINが 異常に遅くなる件 同じ経験ありますか？ ダミーマスタ追加 or オプティマイザヒント /*+ NO_BNL() */ で対応はできるけど・・・