2013年4月2日火曜日

REPLACE INTOが実はINSERT + DELETEだった(INSERTが先で後から消す) 間違ってました。。

「INSERT .. ON DUPLICATE KEY UPDATE ..でクエリを投げているけど、99%くらいはUPDATEになっている」
「UPDATEを直接投げた方が速かったりする?」
「極端にパフォーマンス違うんだったら、UPDATEが転けてからINSERTする方式も考える」

とか聞かれたのでINSERT .. ON DUPLICATE KEY UPDATEについて調べてたんですが、
ソース読んでたら豆知識に出会ったのでメモ。

5.6.10のsql/sql_insert.ccの中でゴニョゴニョしている気配があるけど、write_record関数の中で

table->file->ha_write_row(table->record[0]してみる。
⇒何もないINSERTだとエラーになったらエラー。
⇒REPLACE INTO, INSERT .. ON DUPLICATE KEY UPDATEだと
それぞれHA_ERR_FOUND_DUPP_KEYを引っ掛けてハンドルしている。
最終的にはtable->file->ha_update_row(table->record[1], table->record[0])、
これはsql/sql_update.ccのmysql_update関数で呼んでいるのと同じ感じ。
…あれ。

REPLACE INTOの場合、この処理の`後に'DELETEのトリガー呼んでるように見えるぞ。。


mysql56> CREATE TRIGGER tr1 BEFORE INSERT ON t2 FOR EACH ROW BEGIN INSERT INTO t3(trg, val) VALUES ('before insert', new.num); END;//
Query OK, 0 rows affected (0.00 sec)

mysql56> CREATE TRIGGER tr2 BEFORE DELETE ON t2 FOR EACH ROW BEGIN INSERT INTO t3(trg, val) VALUES ('before delete', old.num); END;//
Query OK, 0 rows affected (0.01 sec)


BEFORE INSERTとBEFORE DELETEのトリガーを作ってみて

mysql56> INSERT INTO t2 VALUES (4, now());
Query OK, 1 row affected (0.01 sec)

mysql56> SELECT * FROM t3;
+---------------------+---------------+------+
| dateval             | trg           | val  |
+---------------------+---------------+------+
| 2013-04-02 15:49:25 | before insert | 4    |
+---------------------+---------------+------+
1 row in set (0.00 sec)

mysql56> REPLACE INTO t2 VALUES (4, now());
Query OK, 2 rows affected (0.01 sec)

mysql56> SELECT * FROM t3;
+---------------------+---------------+------+
| dateval             | trg           | val  |
+---------------------+---------------+------+
| 2013-04-02 15:49:25 | before insert | 4    |
| 2013-04-02 15:49:39 | before insert | 4    |
| 2013-04-02 15:49:39 | before delete | 4    |
+---------------------+---------------+------+
3 rows in set (0.00 sec)


大当たりー。
ソースコードにも、「DELETEトリガーがあったら、ユーザーは俺たちのチートに気付くだろうしNE☆」って書いてあった。

えーと、何の話でしたっけ。。


【2013/04/08 12:12】
@sakaik さんにツッコミをいただいたので丁寧に調べてみたら、

REPLACE INTOは行を消してから新しい行を書く。
けど、トリガーはINSERTトリガーが先に引かれてDELETEトリガーが後から引かれる。

でした。。申し訳ない。。orz

http://yoku0825.blogspot.jp/2013/04/replace-intoinsert-deletedelete-insert.html

@meijik さん、@sakaik さん、ツッコミありがとうございましたm(_"_)m

7 件のコメント :

  1. 初めてのSQL: replaceコマンドの置換(replace)
    にあり、結構有名な話です。

    http://blog.kimuradb.com/?eid=639798

    返信削除
    返信
    1. いろいろ曖昧な部分があるコメントですみません。上記話はreplaceがdelete+insertであり、delete処理の副作用がある(参照した本や私ブログではInnoDB FKの副作用、yoku ts.さんの本エントリではdeleteトリガ)ということについて「有名な話」といっているだけで、それぞれの処理の順番や、トリガ順番について「有名な話」といっているわけではありません。あしからず。

      削除
    2. ご丁寧にありがとうございますm(_"_)m

      ああ、良かったです。
      今まで「REPLACEはDELETE + INSERTみたいなもん」だと思っていて、
      「実はINSERT(が先) + DELETE」が常識だったとしたら間抜けだー、とヒヤヒヤしました。。

      たびたびありがとうございます!

      削除
  2. Σ(゚д゚lll) 有名だったんですね! 不勉強さが現れてしまいました。

    イメージ的にDELETEが先でINSERT、って感じだったので、ちょっとびっくりした次第だったのです。

    情報ありがとうございますm(_"_)m

    返信削除
  3. あ、なるほどREPLACE=DELETE+INSERTは有名ですが、その順番はおっしゃるとおりあまり知られてないです。(私も知らなかった)
    上記の本でも「その行を削除してから新しい行を挿入する」となってますので、文字通り解釈するとDELETE->INSERTに読みとれますから。

    返信削除
  4. その結果だけだと、delete trigger が後、ということはわからないと思います。同時刻なので。
    特に変なことをしていなければ登録順に表示される「ことが多い」ので、おそらく想像どおりなのかとは思いますが、念のため、http://blog.kimuradb.com/?eid=877275 あたりを見て、マイクロ秒まで確認してみてはいかがでしょう。

    あと、今回の話題の中で「INSERT処理自体が先に実行される」ことと「INSERTトリガが先に走る」ことが混じって語られているように感じました(ちょっと読み取れなかった読解力をお許しください・・・)。

    返信削除
  5. 確かにわたし、「INSERTトリガーで記録された行がDELETEトリガーで記録された行より先「に表示される」」と「INSERTトリガーがDELETEトリガーより先」と「行の書き込みが行の消しこみより先」を混同していますね。。

    トリガーの中身をSYSDATE(6)に変えて取ってみたところ、ちゃんとINSERTトリガーの方が300usくらい先になりました。
    明日にでも修正しておきます!

    でもDELETEトリガーを引いているところはなんとなく判るんですが、
    INSERTトリガーをどこで引いているのかイマイチ釈然としない(if文で分岐しているような気がする)ので、もうちょっと調べてみます。

    色々ありがとうございます!

    返信削除