質問をすることでしか得られない、回答やアドバイスがある。

15分調べてもわからないことは、質問しよう!

ただいまの
回答率

87.37%

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

解決済

回答 2

投稿 編集

  • 評価
  • クリップ 0
  • VIEW 746

score 16

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
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
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 |
  • 気になる質問をクリップする

    クリップした質問は、後からいつでもマイページで確認できます。

    またクリップした質問に回答があった際、通知やメールを受け取ることができます。

    クリップを取り消します

  • 良い質問の評価を上げる

    以下のような質問は評価を上げましょう

    • 質問内容が明確
    • 自分も答えを知りたい
    • 質問者以外のユーザにも役立つ

    評価が高い質問は、TOPページの「注目」タブのフィードに表示されやすくなります。

    質問の評価を上げたことを取り消します

  • 評価を下げられる数の上限に達しました

    評価を下げることができません

    • 1日5回まで評価を下げられます
    • 1日に1ユーザに対して2回まで評価を下げられます

    質問の評価を下げる

    teratailでは下記のような質問を「具体的に困っていることがない質問」、「サイトポリシーに違反する質問」と定義し、推奨していません。

    • プログラミングに関係のない質問
    • やってほしいことだけを記載した丸投げの質問
    • 問題・課題が含まれていない質問
    • 意図的に内容が抹消された質問
    • 過去に投稿した質問と同じ内容の質問
    • 広告と受け取られるような投稿

    評価が下がると、TOPページの「アクティブ」「注目」タブのフィードに表示されにくくなります。

    質問の評価を下げたことを取り消します

    この機能は開放されていません

    評価を下げる条件を満たしてません

    評価を下げる理由を選択してください

    詳細な説明はこちら

    上記に当てはまらず、質問内容が明確になっていない質問には「情報の追加・修正依頼」機能からコメントをしてください。

    質問の評価を下げる機能の利用条件

    この機能を利用するためには、以下の事項を行う必要があります。

質問への追記・修正、ベストアンサー選択の依頼

  • Orlofsky

    2019/10/15 14:20

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

    キャンセル

  • tog

    2019/10/15 14:49

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

    キャンセル

  • Orlofsky

    2019/10/15 15:57

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

    キャンセル

  • Orlofsky

    2019/10/15 16:16

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

    キャンセル

回答 2

checkベストアンサー

+1

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

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

投稿

編集

  • 回答の評価を上げる

    以下のような回答は評価を上げましょう

    • 正しい回答
    • わかりやすい回答
    • ためになる回答

    評価が高い回答ほどページの上位に表示されます。

  • 回答の評価を下げる

    下記のような回答は推奨されていません。

    • 間違っている回答
    • 質問の回答になっていない投稿
    • スパムや攻撃的な表現を用いた投稿

    評価を下げる際はその理由を明確に伝え、適切な回答に修正してもらいましょう。

  • 2019/10/15 15:53

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

    キャンセル

  • 2019/10/15 15:59

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

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

    キャンセル

  • 2019/10/16 09:13

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

    キャンセル

+1

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

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

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

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

投稿

編集

  • 回答の評価を上げる

    以下のような回答は評価を上げましょう

    • 正しい回答
    • わかりやすい回答
    • ためになる回答

    評価が高い回答ほどページの上位に表示されます。

  • 回答の評価を下げる

    下記のような回答は推奨されていません。

    • 間違っている回答
    • 質問の回答になっていない投稿
    • スパムや攻撃的な表現を用いた投稿

    評価を下げる際はその理由を明確に伝え、適切な回答に修正してもらいましょう。

  • 2019/10/16 09:12

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

    キャンセル

15分調べてもわからないことは、teratailで質問しよう!

  • ただいまの回答率 87.37%
  • 質問をまとめることで、思考を整理して素早く解決
  • テンプレート機能で、簡単に質問をまとめられる

関連した質問

同じタグがついた質問を見る