mysql(5.7)におけるfrom-toテーブルの更新におけるパフォーマンス最適化について

Question

MySQL 5.7を利用しております。
下記のようなfrom-to管理テーブルをある時点のデータテーブルで更新(Update)処理をおこないたいのですが、現行のフローだとパフォーマンスが悪いです。(というよりも更新クエリが返ってこないです。)
どのような更新クエリを書けば良いでしょうか？

更新前のテーブルA(275万件程度)

from_date	to_date(primary key)	id(primary key)	value
2018-01-01	2018-04-01	1	1A
2018-04-02	2019-04-01	1	1B
2019-04-02	9999-12-31	1	1C
2018-05-02	2019-09-01	2	2A
2019-09-02	9999-12-31	2	2B
```MySQL5.7
CREATE TABLE テーブルA (

`from_date` DATE NOT NULL,
`to_date` DATE NOT NULL,
`id` INT(11) NOT NULL,
`value` VARCHAR(50) NOT NULL,
PRIMARY KEY (`to_date`, `id`)

)
COLLATE='utf8mb4_general_ci'
ENGINE=InnoDB
;

更新情報を持ったある時点のテーブルB(170万件程度)
|base_date(primary key)|id(primary key)|value|
|:--|:--:|--:|
|2019-10-10|1|1D|
|2019-10-10|2|2C|

```MySQL5.7
CREATE TABLE `テーブルB` (
	`base_date` DATE NOT NULL,	
	`id` INT(11) NOT NULL,
	`value` VARCHAR(50) NOT NULL,
	PRIMARY KEY (`base_date`, `id`)
)
COLLATE='utf8mb4_general_ci'
ENGINE=InnoDB
;

更新後のテーブルA

from_date	to_date(primary key)	id(primary key)	value
2018-01-01	2018-04-01	1	1A
2018-04-02	2019-04-01	1	1B
2019-04-02	9999-12-31	1	1D
2018-05-02	2019-09-01	2	2A
2019-09-02	9999-12-31	2	2C

現行の更新クエリ：

update [テーブルA] A
　　inner join [テーブルB] B on A.id=B.id and B.base_date between A.from_date and A.to_date
set A.value=B.value;

explain結果

| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | 
| ---: | --- | --- | --- | --- | --- | --- | --- | --- | ---: | ---: | --- | 
| 1 | SIMPLE | B | \N | ALL | PRIMARY,テーブルB | \N | \N | \N | 1658424 | 100.00 | \N | 
| 1 | UPDATE | A | \N | ref | PRIMARY,テーブルA | PRIMARY | 202 | B.id | 916054 | 1.11 | Using where |

Comments

[Orlofsky]

質問に 2つのテーブルのCREATE TABLE, CREATE INDEX を https://teratail.com/help/question-tips#questionTips3-7 の [コード] で追記してください。

[tog]

ありがとうございます。
追記致しました。

[Orlofsky]

update, explain も https://teratail.com/help/question-tips#questionTips3-7 の [コード] に修正してください。

[Orlofsky]

explain も。
これ以上は有料にします。笑

Answer 1

解決済みになっていますが、解決はしていませんよね？

primaryの構成が、突合時の効率が悪いものになっています。
id同士なら仮に履歴状になっていても先ずは数件同士を突合する事で済みますが、日付同士の比較だと限定されるものは殆どない状態だと思われます。

** PRIMARY KEY ( id, to_date)**
のように両テーブルともidが前になるようにすること。

ただprimaryだと結合項目が不足するので、結合条件になっているものをすべて含んだインデックスの方が良いと思います。

Comments

[tog]

ありがとうございます。
無事解決しました。
基本的な所を理解できていなかったようです。
よくわかりました。

Answer 2

テーブルA は id, from_date, to_date の3カラムでインデックスが欲しいです。
テーブルB は id, B.base_date の2カラムでインデックスが欲しいです。

インデックスを追加する前後の MySQL SQL実行計画の疑問解決には「とりあえずEXPLAIN」しよう の結果を質問に追記してください。

Comments

[tog]

ありがとうございます。
やってみましたがやはり返ってこないようです。
実はこれまでクエリが返ってくるまで実行した事がありません。
5分以上かかった段階でプロセスKillをしておりました。
ですのでまずはクエリが返ってくるまで待機してみようと思います。
もしかするとパフォーマンスを最適化したところで1時間程度かかる処理なのかもしれません。

[Orlofsky]

> テーブルA は id, from_date, to_date の3カラムでインデックスが欲しいです。
> テーブルB は id, B.base_date の2カラムでインデックスが欲しいです。

って書いたのを理解出来なかったら、お金を払って助っ人を呼んでください。

[tog]

ありがとうございます。
選択性の高いものをもってこないといけないという事だったのですね。
解決しました。