DB(MySQL)でDDLを作成するときの小ネタまとめ
最近久々にSQL、特にDDLを書くことがあったのでまとめます。万人ウケするかは分かりません。ほぼ自分の備忘録用途です。
マスタテーブルのIDはAUTO_INCREMENT
`id` int(11) NOT NULL AUTO_INCREMENT,
基本ですね。サロゲートキー、というかnumber列の場合の話ですが。自分で採番しなくていいので楽です。
マスタテーブルには操作日時列を付ける
`created_at` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP,
`updated_at` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
DEFAULTの部分が少しだけトリッキーです。created_atにはレコードを作成した日時が自動で入ります。updated_atにはレコード更新日時が自動で入ります。
マスタデータがいつ変わったか、などが見れるので障害調査とかに役に立ちますね。
桁とO/Rマップ時の型に注意
`id` int(11) NOT NULL AUTO_INCREMENT,
みたいなときにunsignedを付けるかどうか、みたいな話です。付けても良いのですが、個人的には付けない派です。
O/Rマッパーでテーブルに対応するJavaやScalaのクラスを生成した時にunsignedだとLongになったりします。
FOREIGN KEYはCASCADE
FOREIGN KEY (`genre_id`) REFERENCES `genres` (`id`) ON DELETE CASCADE ON UPDATE CASCADE,
MySQLの外部キー制約RESTRICT,CASCADE,SET NULL,NO ACTIONの違いは？
意味は上記の記事が詳しいです。助かります。何でもCASCADEにすればいいというものではないですが、自分の経験上、親テーブルが変更されたら子テーブルはそれに追従させたいというケースが多いので。
日付型は一旦考えてから選ぶ (date, datetime, timestamp, etc)
手前味噌ですが。さらにかなり前の記事ですが・・・。よく違いを忘れるので。
VIEWを作るときは性能を考える
VIEWは便利で非常によく使うのですが、理解していないとパフォーマンスに影響が出ます。
https://dev.mysql.com/doc/refman/5.6/ja/view-restrictions.html
マージアルゴリズムを使用して処理されたビューに、インデックスを使用することは可能です。ただし、TEMPTABLE アルゴリズムで処理されたビューは、そのベースとなるテーブルのインデックスを利用できません (ただし、一時テーブルの作成中にはインデックスを使用できます)。
https://dev.mysql.com/doc/refman/5.6/ja/view-algorithms.html
MERGEの場合、ビューを参照するステートメントのテキストとビュー定義がマージされ、ビュー定義の部分が対応するステートメントの部分と置き換えられます。
TEMPTABLEの場合、ビューの結果が一時テーブル内に取得され、その後、ステートメントを実行するために使用されます。
UNDEFINEDの場合、MySQL は使用するアルゴリズムを選択します。できるかぎりTEMPTABLEよりMERGEが優先されます。これは通常、MERGEのほうが効率性が高く、一時テーブルを使用するとビューを更新できなくなるためです。
自分の理解が間違っているかもしれませんが、MERGEが選ばれれば、VIEWの参照部分は普通のSQLに置き換えられる感じで動くので、INDEXが使える。TEMPLATEが選ばれれば一時テーブル作成にINDEXが使われるが、その後はINDEXが使われないので遅い。という感じです。
細かいTEMPLATEになってしまう条件などはリンク先を参照しましょう。
CREATE VIEWではDEFINERに注意
特にオプションを指定しない場合、CREATE VIEWはこのようになります。
CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`%` SQL SECURITY DEFINER VIEW `my_view` AS ...
SQL SECURITY DEFINERになっているので、このVIEWは定義したユーザであるrootしか見れません。他のユーザにも見れるようにする場合は
CREATE SQL SECURITY INVOKER VIEW my_view AS ...
こうしてINVOKERにしておきます。
以上です。できれば随時追加していこうと思います。