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)

MySQLの日付型の扱い方や機能をまとめてみました

手前味噌ですが。さらにかなり前の記事ですが・・・。よく違いを忘れるので。

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にしておきます。

 

以上です。できれば随時追加していこうと思います。