テーブル設計

情報を分類

世の中の情報を分類して各テーブルにする

使い方の想定

欲しいであろうデータの取得方法を考えて、実行する

DB定義の管理

マイグレーション等でリポジトリで管理し、各環境のDBを変更するのはシステムユーザーだけにする
DB定義の調査はローカルのdev環境で行う
※各環境でのDBの差異を作らない

キー

主キー

何で一意なレコードになるかを考える
基本的にIDでNULLは許容せず、auto incrementさせる
idは何のidなのかを明確にするため、idにはしない(xxx_id)

※インデックスは自動的に作成される
※複合主キーの場合はそれぞれの列のインデックスを作成する

外部キー

他のテーブルのID等を格納する場合はそのIDのレコードが削除されたときを想定する
削除されたIDが格納されている他のレコードも削除するかどうか
削除されたIDを新しく格納しようとする際にエラーにするか
※参照先のテーブルで主キーではない場合はインデックスを作成する

ON UPDATE|DELETE RESTRICT|CASCADE|SET NULL
RESTRICT: 参照されているレコードを削除・更新するときにエラーにする
CASCADE:  参照されているレコードを削除・更新するときに他のテーブルのレコードも削除・更新する
SET NULL: 参照されているレコードを削除・更新するときに他のテーブルのレコードをNULLにする

構文

CREATE TABLE テーブル名 (
  主キー名 型 UNSIGNED NOT NULL AUTOINCREMENT,
  数値カラム名 BIGINT UNSIGNED NOT NULL,
  文字列カラム名 VARCHAR(桁数) NOT NULL,
  Booleanカラム名 SMALLINT NOT NULL DEFAULT TRUE,
  日時カラム名 DATETIME NOT NULL,
  タイムスタンプカラム名 TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY(主キー名),
  KEY インデックス名 (カラム名),
  CONSTRAINT 外部キー名 FOREIGN KEY (カラム名)
    REFERENCES 参照先テーブル名 (参照先カラム名) ON DELETE RESTRICT ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

テーブル移行

CREATE TABLE
  テーブル名
AS
  SELECT * FROM テーブル名;

---

パーティションでデータを分割

レコードが増えてきたときにデータを分割する
特定の期間経過したレコードをまとめてパーティションごと削除する

※SELECT,DELET時はどのパーティションのレコードかの判定に余分な時間がかかる
※INSERTやUPDATE時はどのパーティションのレコードかの判定に余分な時間がかかる

パーティションを確認

SELECT
  *
FROM
  information_schema.PARTITIONS
WHERE
  PARTITION_NAME IS NOT NULL
ORDER BY
  PARTITION_ORDINAL_POSITION;
※テーブルを削除するとinformation_schema.PARTITIONSの情報も自動で削除される
※TABLE_ROWSカラムは後からALTER TABLEすると0になるがレコードはSELECT PARTITION(p0)等で取得できる

RANGE

CREATE TABLE テーブル名 (
...
)
PARTITION BY RANGE
  (YEAR(日付カラム)) (
    PARTITION 日付カラム_2019 VALUES LESS THAN (2020),
    PARTITION 日付カラム_2020 VALUES LESS THAN (2021),
    PARTITION 日付カラム_2021 VALUES LESS THAN (2022),
    PARTITION 日付カラム_OTHER VALUES LESS THAN (MAXVALUE),
  );

ALTER TABLE
  テーブル名
REORGANIZE PARTITION
  日付カラム_OTHER INTO (
    PARTITION 日付カラム_2022 VALUES LESS THAN (2023),
    PARTITION 日付カラム_2023 VALUES LESS THAN (2024),
    PARTITION 日付カラム_OTHER VALUES LESS THAN (MAXVALUE),
  );

LIST

パーティションのVALUES INのいずれにもない場合にはエラーになるので、使わない

HASH

最初からパーティションを作成

CREATE TABLE テーブル名 (
...
)
PARTITION BY
  HASH (xxx_id) PARTITIONS 10;
※複数のカラムを含むハッシュ式を使用することはあまり推奨されていない
※ALTER TABLE DROP PARTITIONはできない

パーティションを後から作成

ALTER TABLE
  テーブル名
PARTITION BY
  HASH(xxx_id) PARTITIONS 10;

RANGE, HASH

CREATE TABLE テーブル名 (
...
)
PARTITION BY
  RANGE (YEAR(日付カラム))
  SUBPARTITION BY HASH(xxx_id)
  SUBPARTITIONS 10 (
    PARTITION 日付カラム_2019 VALUES LESS THAN (2020),
    PARTITION 日付カラム_2020 VALUES LESS THAN (2021),
    PARTITION 日付カラム_OTHER VALUES LESS THAN (MAXVALUE),
  );

SELECT

SELECT * FROM テーブル名;
SELECT * FROM テーブル名 PARTITION (p0);