はじめに
こんにちは。
今回は実務を想定して、SQLを用いたデータ管理の解像度を上げていきたいと思います。
今の状態
- 「SQLのデータ管理か…例えば打刻システムでユーザーが打刻し忘れたら正確な時間を入力する作業とかがそれにあたるのかな」程度の解像度
理想の状態
- データ管理業務について5分程度は話すことができる
- 「データ管理あるある」を理解して、実務でミスを減らせそうと思えること
SQLのデータ管理は具体的にどんなケースがあるのか
実務でSQLを使う具体的な業務内容を調査していきます。
ケース1: 問い合わせを受けてデータを確認・修正する
「退勤打刻が反映されない」と言われて調べると、打刻レコードが2件入っていて、画面は1件目しか表示していない。ユーザーからは存在が見えないので、消すことも直すこともできません。
二重送信、バッチの二重実行、他システム連携の重複など、原因はいろいろです。
そして、報告があったデータを直して終わりではありません。同じ原因で他のデータが壊れていないか、影響範囲の調査までがセットになります。
原因調査については以下の記事が参考になると思いました。
ケース2: 画面に修正機能がない
締め処理済みの勤怠、承認済みの申請、退職者のデータ。編集不可になっていて、解除する管理画面が存在しないケース。データ管理者がデータ更新を行います。ただ本番環境のデータサーバを触るのでかなり緊張感をもつ必要がある作業です。
実務を想定して気を付けること
1. COUNT(*) で件数を確認してから更新する
UPDATE文を書く前に、まったく同じWHERE句で件数を数えます。
-- ① まず件数を数える
SELECT COUNT(*) FROM orders WHERE order_no = 'A12345';
-- → 1 が返ることを確認
-- ② 中身も見る
SELECT * FROM orders WHERE order_no = 'A12345';
-- ③ WHERE句をコピペしてUPDATE
UPDATE orders SET status = 3 WHERE order_no = 'A12345';
-- ① まず件数を数える(LIMIT使用)
-- ② 中身も見る
-- ③ WHERE句をコピペしてUPDATE
-- (③ データ更新されていることを確認)
その他の対策:
- DBツールやターミナルの背景色を、本番だけ赤にする。
参考: UPDATE文のWHERE忘れで全行更新する事故を、実行前の1分で防ぐ
2. 接続先の確認を儀式にする
本番と検証（STG）の取り違えは事故の元。
実行前にDB名やホスト名で間違いないことをスクショして慎重に作業しようと思います。
3. 重いSELECTは本番を止める
フルスキャンでCPUを食う、レプリカ遅延を起こす。まず COUNT(*) で件数を見積もり、EXPLAIN で実行計画を見て、LIMIT を付けて試します。
- EXPLAIN ：データベースが SQL クエリをどのように実行するか（実行計画）を確認するためのステートメント。クエリの前に EXPLAIN をつけるだけで、実際にデータを取得せずに処理手順や予測コストを表示できます
EXPLAINで分かることは、主に次の3つです。
どのインデックスを使うか（使わないか）
何行くらい読むと見積もっているか
ソートや一時テーブルなどの余計な処理が発生するか
4. トランザクションを開きっぱなしにしない
GUIツールが自動コミットOFFだと、SELECTしただけでもトランザクションが残り、他業務をブロックすることがあります。
FOR UPDATE を誤って書くのも同種の事故です。
（FOR UPDATE調べたけどそれをした方が良い場面としなくてもいい場面の切り分けが分からない）
上記コメントしてくれた方のお陰で解決しました。
参考:
5. DDLはロールバックできない（MySQL/Oracleの場合）
ALTER TABLE や DROP TABLE は BEGIN の中に書いても暗黙的にコミットされます。PostgreSQLは可能。ここはDBMSで挙動が違います。
その他
集計関数の LISTAGG を初めて知りました(OracleのSQL関数)。便利そうですが、使い道はまだ分かっていません。（MySQLでは GROUP_CONCAT が同じような役割を持ちます）
調査に使えそうなSQLのまとめ:
あとA5のカラム毎の色についてですが赤が主キー、黄色がNULL可カラムとのことです。
この記事を通して
- データ管理インシデントを5つ以上知ることができた
- 緊張感が持てた。本番サーバーをいじって役員から呼び出しをもらいたくない。
- 慎重に慎重に作業しようと思う