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

Question

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
となります。

Answer 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以降だとそんなことも無い気がします...

Comments

[cnx]

ご指摘頂いたとおり、UPDATEに時間が掛かっているようです。
それでも、皆さまに頂いたSQLの組換えと、今回頂いたJOINからUPDATEする組換え、そしてインデックスによって大分速くなったという認識ですので全体の最適化について道筋がついたと思います。
また、別のSQLでも悩んでおりますので別途質問をさせて頂きたいと思いますので、また皆さまにお知恵をお借りできればと思います（そちらもUPDATE/INSERTが遅いという可能性もありますが……）。
因みに、MySQL 5.6.23 です。RDSはlargeなのでインスタンスタイプ自体を上げるなどして調整をしていってみます。ありがとうございました。

[hirohiro]

ううん。。update自体が遅いのですか？ちょっと想定外です。
何十万何百万単位で更新レコードが存在するのでしょうか？
SELECT id FROM master where Exists (
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
)
updateの替わりにselectしたものですが、これは早いですか？
これが早いなら本当にupdateが遅いのかも知れません。「更新対象件数が膨大なうえidのindex更新まであるから」みたいな事はあり得なくないですが...
逆にこれも遅いならmasterのidにindexが無いからかも知れません。

[cnx]

説明不足で済みません。インデックス（idへの主キー指定）がないUPDATEが遅かったようで、当初主キー無しの状態でUPDATEを試行していた際には、ご指摘の通りINの中のSELECT（サブクエリー）は速いのにUPDATEが遅い状態でした。
ここで、主キー指定、インデックス指定により高速化されることが分かったのですが、その後にSQL文の組換えを行っても速度差があまり出なかったという状況です。
ただ、この速度差が出なかった理由は主キー指定、インデックス指定によって高速化されてしまったために差が体感するほど出なかったという事になるようです。
実験にはバッチにより以下のプロセスを踏んでいるため全体的にまだ高速化できる気でいたのですが、「データ準備」の中に他の（元々それなりに時間を食っていた）要因があるように思い現在特定を試みています。
・開始時刻の打刻
・関連テーブルドロップ
・元データの生成SQL（説明にある「元は同じテーブル」からweb_list, tel_listを生成したりする処理）
・上記を使った集計SQL（今回の検討箇所）
・結果テーブルのダウンロード
・終了時刻の打刻

[Panzer_vor]

横から失礼致します。

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

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

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

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

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

[cnx]

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

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

３点目：こちらは大丈夫かと思います（型は完全一致）。

[Panzer_vor]

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

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

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

Answer 2

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

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

Comments

[hatena19]

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

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

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

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

[Panzer_vor]

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

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

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

Answer 3

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も試してみてください。重複排除処理が省略されますので高速化されるかも？

Comments

[cnx]

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

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

Answer 4

もし 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 より若いという前提です。

Comments

[cnx]

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日以内の条件）という感じになります。

[hatena19]

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

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

[cnx]

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