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

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

ただいまの
回答率

90.37%

  • MySQL

    7306questions

    MySQL(マイエスキューエル)は、TCX DataKonsultAB社などが開発するRDBMS(リレーショナルデータベースの管理システム)です。世界で最も人気の高いシステムで、オープンソースで開発されています。MySQLデータベースサーバは、高速性と信頼性があり、Linux、UNIX、Windowsなどの複数のプラットフォームで動作することができます。

  • SQL

    3159questions

    SQL(Structured Query Language)は、リレーショナルデータベース管理システム (RDBMS)のデータベース言語です。大きく分けて、データ定義言語(DDL)、データ操作言語(DML)、データ制御言語(DCL)の3つで構成されており、プログラム上でSQL文を生成して、RDBMSに命令を出し、RDBに必要なデータを格納できます。また、格納したデータを引き出すことも可能です。

  • AWS(Amazon Web Services)

    2678questions

    Amazon Web Services (AWS)は、仮想空間を機軸とした、クラスター状のコンピュータ・ネットワーク・データベース・ストーレッジ・サポートツールをAWSというインフラから提供する商用サービスです。

「各レコードから見て後ろ3日以内で条件を満たすレコードが存在するもの」の結果一覧から高速にマスターをアップデートするSQL

解決済

回答 4

投稿 編集

  • 評価
  • クリップ 0
  • VIEW 1,356

cnx

score 13

id, name(text), inserted(UNIX_TIME)で出来ているテーブルが2つあります。
web_list, tell_listと名前を付けています。
どちらも同じ構造ですが意味合いが異なっています。

master, web_list, tel_listにはインデックスはありません。
idは自動採番、insertedは秒単位で全くバラバラです。nameは全体の2割ほどが複数回重複して含まれています。

ここで、web_listを上から順番に見ていって、あるレコード(例えばid=n)について【条件1】insertedが後ろ3日(259200秒)以内でかつ【条件2】nameが同一のレコードがあるもののidを抜き出して、masterに付き合わせてflagをアプデートしようとしています。

update master set flag=1 where id in (
     select id from web_list as wlst
           where
                 0 < (select count(id) from tell_list as tlst
                       where
                          wlst.name = tlst.name
                          and
                          wlst.inserted < tlst.inserted
                          and
                                   tlst.inserted < wlst.inserted + 259200)
                      )
);

 
と、書いて結果は得られるのですが非常に遅く、ネットなどで検索するとMySQLのin+サブクエリーは遅いのでjoinに書き換えた方が良いといった意見なども見受けられました。
AWSのRDS(large)などでテストしても各テーブルが数千件を超えるとまともに結果が返却されない(数十分かかってタイムアウト)といった状態です。

nameが同一で、3日以内のレコードが1件以上存在する(0<)ということを条件にしてweb_listからidを列挙してきているのですが……、もっと高速にSQLを書き換えられないものでしょうか。

質問のために一部SQLを変更していましたが、自己検証を繰り返しているうちに端折っているINSERT文+INの組み合わせが非常に時間を掛けていることが分かってきました。この点も踏まえてコメントをいただけるとありがたいです。

master, web_list, tel_listのidを主キーにして速度が上がりました。しかしながら、まだかなり遅い印象で、まだ速くなる余地はないでしょうか。(tell_list, web_list共に2万件で1分ほど掛かります)

web_list

id|name|inserted
11|bob |2016/09/01 12:00
12|mary|2016/09/01 13:00
15|mary|2016/09/05 10:00
16|bob |2016/09/06 15:00

tel_list

id|name|inserted
13|bob |2016/09/02 17:00
14|bob |2016/09/03 15:00
17|bob |2016/09/07 15:00
18|bob |2016/09/07 18:00
19|mary|2016/09/10 10:00


上記のようなイメージです。
この場合、INのサブクエリーで得られるID一覧は
11, 16
となります。

  • 気になる質問をクリップする

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

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

    クリップを取り消します

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

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

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

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

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

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

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

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

    質問の評価を下げる

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

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

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

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

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

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

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

    詳細な説明はこちら

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

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

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

回答 4

checkベストアンサー

+1

updateが遅いとしたら
masterのレコード数が膨大でidにindexが無いためか、単純に件数が膨大過ぎる場合でしょう「update master set flag=1 where ....」ですからね。基本的にwhere句以前には改善点がありません。
物理的に更新行が多すぎるなら改善は難しいです。PCスペックを上げるとか?

目的のIDを抽出するsql
多分次のように成ると思いますが、これは早いでしょうか?

SELECT w.id 
FROM web_list AS w 
LEFT JOIN tell_list AS t ON w.name = t.name
WHERE w.inserted < t.inserted
AND t.inserted <= w.inserted + INTERVAL 3 DAY
GROUP BY w.id


これが早いなら、in句が遅いか、冒頭で書いたようにmasterのレコード数が膨大な上indexが適当でないかのどちらかが原因かと思います。EXISTSに変更してみるか、masterのidにindex(プライマリキーにしているなら不要)を作成してみると良いかも知れません。

逆にこれが遅いならweb_listかtell_listのレコード数が膨大でindexが適切でないためだと思います。
nameに重複が少ないならname、またinsertedへのindexの作成を検討する必要があるかも知れません。

in句やdistinctはmysql5.1 以前だとやたら遅くなるケースがあったように思います。ただ特定のケースでindexが使えて無いことに起因してたように思うので、そもそもindexを作成していないなら関係無いと思いますし、5.4以降だとそんなことも無い気がします...

投稿

編集

  • 回答の評価を上げる

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

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

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

  • 回答の評価を下げる

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

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

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

  • 2016/09/19 20:59

    横から失礼致します。

    何点か気になったので余力があれば確認してください。
    問題なければ以下スルーで良いです。

    1点目、どのような感じでインデックスを貼ったのか結果がわからないので既に実施済みかもしれませんが、
    今回のケースに限った話では、
    ・masterはid列のみに作成(これは主キーで良いかな)
    ・その他2テーブルにnameとinsertedは複合インデックスを作成

    上記の貼り方が検索パス的には効率よさそうかなと思います。

    2点目、元々のクエリと同程度のデータ数を単純に結合なし(masterテーブル内で完結するもの)でUPDATEした場合の実行速度を確認してみて下さい。
    これで速度が早い場合は、
    テンポラリテーブルを作成し、それを経由した更新の方が早くなる可能性があります。

    3点目、これは多分問題ないと思いますが、
    データ型が不一致になっている箇所がないか確認して下さい。
    特に、インデックス列に検索条件をかける場合、
    データ型不一致があるとインデックスが利用されなくなるケースが多いです。
    (インデックス列の方が暗黙変換でキャストされるとインデックスはただの飾りになります)

    キャンセル

  • 2016/09/20 22:49

    コメントありがとうございます。色々と知識を得られて興味深いです。
    1点目:masterはid列に主キー設定しました(しています)。他2テーブルは個別にname, insertedそれぞれインデックスを作成しています。(結合インデックスの効果は別途確認してみたいと思います)

    2点目:masterテーブルの単純なアップデートSQLが速いかということですね。前項のid主キーを設定した後は既に大分速いようなので、件数が増えて遅くなった場合には念頭に置いてみます。
    テンポラリテーブル経由での更新というのは、どういった方法になるでしょうか。生成されたテンポラリテーブルとinまたはjoin updateという事でしょうか。

    3点目:こちらは大丈夫かと思います(型は完全一致)。

    キャンセル

  • 2016/09/22 13:52

    > cnxさん
    ご返答が遅くなり申し訳ありません。

    計測してみないと複合インデックスでの効果改善のほどは分かりませんが、
    nameとinsertedが同時に検索されることがほとんどならば、
    個別に張るよりは複合インデックスを採用するのがベストかなと思います。

    テンポラリテーブルを利用するという方法は、質問者様のご認識の通りです。
    旧いバージョンのMySQLではサブクエリを利用ができなかったり、
    サブクエリ自体が遅いという問題もあったそうで、
    それを回避する術として一時テーブルを作ってその結果を基に更新するという手段が用いられたそうです。
    (※ただ結局UPDATEで結合なりINを利用することになるので、改善見込みは薄い気がしてきました。一応こういう手段もあるよという参考程度にして下さい。)

    キャンセル

+1

もし web_list と tel_list が id でもって 1対1 に対応づけられる
という前提があり、同じ id 同士の条件を比較すればよいなら
web_list と tel_list を結合し、それに対して条件で絞れば
いまより早くなりそうな気がします。

まあ、そういった前提が無いならダメですけど…
--- 追加 ---

//そういう話なら…

tel_list を name で group by でまとめて
最小の inserted を inserted としたものと
web_list を inner join したものを
3日以内という条件でしぼるってのはどうでしょう。

// 同じ name なら web_list の inserted は
// 必ず tel_list の inserted より若いという前提です。

投稿

編集

  • 回答の評価を上げる

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

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

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

  • 回答の評価を下げる

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

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

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

  • 2016/09/18 06:39

    web_list と tel_list は元々は同じテーブルから抽出されているためIDは1対1で紐付きますが、例えば9/1にname=bobがweb_listにレコードを残したとして(id=100)、9/2にまたbobがtel_listにレコードを残す(id=130)ので、比較するidはばらばらになります。
    そのため、同じid同士というわけにはいかないところです……。
    同じname同士(かつ3日以内の条件)という感じになります。

    キャンセル

  • 2016/09/18 11:49

    > tel_list を name で group by でまとめて
    > 最小の inserted を inserted としたものと

    web_list の name に重複がない(web_listとtel_listが一対多関係)なら、それでいいと思いますが、
    質問文からは重複がありそうな感じですね。どうなんだろう?

    キャンセル

  • 2016/09/18 11:55 編集

    コメントありがとうございます。
    残念ながらtel_listのinsertedもばらばらに入っていますので、必ず若いという前提が成立しないため、tel_listのinsertedをgroup byして最小値を出してしまうと見逃してしまうレコードが出てしまいます。
    そう考えると、takasima20さんの案も汎用化するとhatena19さんと同じ構造のSQLに帰着するように思います。(認識が違っていたら済みません)
    こちらの方法については、INNER JOIN化しても意外に高速化されませんでした。

    キャンセル

+1

nameが同一で、3日以内のレコードが存在するか否か、が分かればいいので、count する必用はないのでは。

結合と抽出条件を設定して、グループ化するか、DISTINCT で重複を排除するだけで、該当する id を抽出できます。

DISTINCT を使ったコード例

UPDATE master SET flag = 1 WHERE ID In(
    SELECT DISTINCT wlist.ID
    FROM
      web_list AS wlist INNER JOIN tel_list AS tlist
      ON  wlist.name = tlist.name
    WHERE
      tlist.inserted > wlist.inserted
      And
      tlist.inserted < wlist.inserted + INTERVAL 3 DAY
);

さらに大幅な速度アップを図るなら name と inserted にもインデックスを設定するぐらいかな。


追記

結局UPDATE 〜 INになっているのが原因かも知れません。

UPDATEの方もINNER JOIN に変更してみました。これで改善できればいいのですが。

UPDATE master INNER JOIN
    (
      SELECT DISTINCT wlist.ID
      FROM
        web_list AS wlist INNER JOIN tel_list AS tlist
        ON  wlist.name = tlist.name
      WHERE
        tlist.inserted > wlist.inserted
        And tlist.inserted <= wlist.inserted + INTERVAL 3 DAY
    ) t
  ON  master.ID = t.ID
SET master.flag = 1
;

あと、INの方もJOINの方も DISTINCT を削除しても同じ結果になりますので、それを削除したSQLも試してみてください。重複排除処理が省略されますので高速化されるかも?

投稿

編集

  • 回答の評価を上げる

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

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

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

  • 回答の評価を下げる

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

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

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

  • 2016/09/18 11:45

    ありがとうございます。サブクエリをINNERT JOINに変える点について非常に参考になりました。
    INNERT JOINに変えたSQLで結果を確認しましたが、意外な事に変更前と速度が変わりませんでした……結局UPDATE 〜 INになっているのが原因かも知れません。

    インデックスについては既にご指摘の通りに張ってある状態です。こちらのインデックスによる高速化は効果が確認出来ております。

    キャンセル

+1

結合案は既に出てるので別案を。

試してみてはないので効果があるかも分かりませんが、
IN句と、COUNTで行ってる箇所をEXISTS句に置き換えるというのはどうでしょう?

投稿

  • 回答の評価を上げる

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

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

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

  • 回答の評価を下げる

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

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

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

  • 2016/09/18 18:02

    EXISTS句案も試してたらどうなるか結果には興味がありますね。

    IN句やEXISTS句は、相関サブクエリになりますので重い、INNER JOIN に置き換えることができるなら、高速化されるというのが通説です。

    EXISTSとSQLの高速化について
    http://kkoudev.github.io/blog/2013/09/14/sql/

    今回、IN句をJOINに変更しても高速化されないというのはちょっと意外でした。
    通説通りにならない要因はなんなんでしょうね。

    キャンセル

  • 2016/09/18 19:07 編集

    > hatena19さん
    案として出てなかったからEXISTSは上げましたが、恐らくINと同じかそれ以下になりそうな気配はしてます^^;

    結合案で速度が変わらないというのがUPDATEを絡めず単純にSELECTで見ても速度差がないのかは見た方が良いかもしれないですね。

    SELECTのみでは速度改善して、UPDATEでは変わらないとなるとMySQL側の実行プランを組み立てる際にとちってる可能性も否定できませんし。

    キャンセル

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

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

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

  • MySQL

    7306questions

    MySQL(マイエスキューエル)は、TCX DataKonsultAB社などが開発するRDBMS(リレーショナルデータベースの管理システム)です。世界で最も人気の高いシステムで、オープンソースで開発されています。MySQLデータベースサーバは、高速性と信頼性があり、Linux、UNIX、Windowsなどの複数のプラットフォームで動作することができます。

  • SQL

    3159questions

    SQL(Structured Query Language)は、リレーショナルデータベース管理システム (RDBMS)のデータベース言語です。大きく分けて、データ定義言語(DDL)、データ操作言語(DML)、データ制御言語(DCL)の3つで構成されており、プログラム上でSQL文を生成して、RDBMSに命令を出し、RDBに必要なデータを格納できます。また、格納したデータを引き出すことも可能です。

  • AWS(Amazon Web Services)

    2678questions

    Amazon Web Services (AWS)は、仮想空間を機軸とした、クラスター状のコンピュータ・ネットワーク・データベース・ストーレッジ・サポートツールをAWSというインフラから提供する商用サービスです。

  • トップ
  • MySQLに関する質問
  • 「各レコードから見て後ろ3日以内で条件を満たすレコードが存在するもの」の結果一覧から高速にマスターをアップデートするSQL