MySQLで論理削除と正しく付き合う方法
- 6. みたいな 5/33
- 8. MySQLの都合に沿 って 論理削除すれ ば 必要悪 っぽいけ ど 7/33
- 10. Using where; とか Using filesort; の温床 9/33
- 12. ＼こんばんは／ yoku0825＠とある企業のDBA オラクれない- ポスグれない- マイエスキューエる- 家に帰ると 妻の夫- せがれの⽗- ムスメの⽗- Twitter: @yoku0825 Blog: ⽇々の覚書 MyNA ML: ⽇本MySQLユーザ会 11/33
- 15. ( ﾟдﾟ) ﾊｯ 14/33
- 19. んー︖ UNIQUE制約さん息してない でも残念ながらナチュラルキーがしっかり定義してあるテーブルの⽅ が少ない - カラムぶんデータのサイズが⼤きくなる TINYINTなら(1バイト * レコード数) * (1 + セカンダリーインデッ クス数) 数千万レコード8インデックスくらいならせいぜい数百メガバイト これが⼗ギガバイト単位になるなら考え直した⽅がいいかも - 18/33
- 24. 削除 フラグ︖ というかそもそも論理 削除 とか 削除 フラグなんていうか ら話が掴みにくくなる スーパー非表⽰フラグとか表⽰ステータスとかにすればいいんじゃ ね︖ - そうすれば、それはレコードの 属性 の⼀つなので、リレーショナル モデル的に納得がいく 運営のみ閲覧可能フラグとかユーザー非表⽰フラグとかでもいい ステータスならENUM(ʻNOT̲DELETEDʼ, ʻDELETED̲BY̲USERʼ, ʻDELETED̲BECAUSE̲DMCAʼ, ʻDELETED̲BECAUSE̲PORNʼ, ..)とか︖ ただし”display̲status <> ʻNOT̲DELETEDʼ“とかやると死ねる。 とはいえそんなクエリー流すの管理画⾯だけであるべきなので、テーブルスキャンく らいは覚悟しろという気でいる。 - 23/33
- 26. 削除フラグのインデックス そして全ての(ユーザートラフィックによる)クエリーは WHERE句の先頭で削除フラグのカラムを等価⽐較しなけれ ばならない たとえば”WHERE super̲hidden̲flag= 0 AND thread̲id= ? ORDER BY comment̲date DESC” なら “KEY (super̲hidden̲flag, thread̲id, comment̲date)” - AND演算⼦は等価交換できるから先頭じゃなくてもいいにしても、 super̲hidden̲flagが”=”演算⼦で評価されてないとこのインデック スは使えない だからboolがいい - というかユーザートラフィックから参照されるならそれ削除 じゃないよね 25/33
- 28. MySQLが5.5 <del>以降</del> より前 チェンジバッファがDELETE, UPDATEに対応するのは MySQL 5.5以降 チェンジバッファが効くとセカンダリーインデックス（プライマリ ー, ユニークはダメ）への反映が非同期になる - “DELETE確かに遅いけど、そんなに⾔うほどでもなくね︖” と思った らこれが効いているケースが多い - 27/33
- 29. MySQLが5.5 <del>以降</del> より前 それ以前は インサートバッファ （INSERTにしかセカンダ リーインデックスの非同期反映が有効でない） DELETEはクラスターインデックスと全てのセカンダリーインデック スを同期で反映させるのに対し - super̲hidden̲flagのUPDATEはクラスターインデックスはまず触ら ないし、super̲hidden̲flagを含むセカンダリーインデックスだけ反 映させればいい - UPDATEにせよDELETEにせよ、MySQL 5.5とそれ以降にすることで チェンジバッファの恩恵を受けられる - ということは別に前提ではないかも 28/33
- 34. Questions and/or Suggestions? Q. インデックスの先頭にカーディナリティーの⼩さい super̲hidden̲flagを置くのは悪⼿じゃね︖ A. ちゃんと正しい位置に置いてくれるならどこでもいいんだけど、「先頭」 って⾔っておかないとだいたいひどいことになる Q. DELETEトリガーでアーカイブテーブルに追い出すののデメ リットはなんかある︖ A. トリガーはトラブルシュート⾯倒。ADD COLUMNしたらトリガーが腐っ て全部のDELETEが転けるとか。 33/33