MySQL 正規化と整合性とINDEX

関連記事

• MySQL 基礎コマンド
• MySQL 関数と応用
• MySQL CTE

正規化

次のようなテーブルがあるとして、fooという記事に多数のユーザーが多数のcommentをすることを想定すると、同じテーブルにcommentカラムを増やしていく方法は、commentのつかないレコードのcommentカラムが無駄になります。
また、commentがある毎にcomment用のfooレコードを追加する方法では、もしfooのタイトルが変更された場合、comment用に追加された全てのfooレコードも変更するコストが発生します。
そのため、fooに紐づいた別のcommentテーブルとして切り出すという方法がよく使われ、そのことを正規化と言います。

-- +----+-------+  
-- | id | title |
-- +----+-------+
-- |  1 |  foo  |
-- |  2 |  bar  |
-- |  3 |  baz  |
-- +----+-------+

-- 正規化
-- +----+------------+-------------+
-- | id | article_id | comment     |
-- +----+------------+-------------+
-- |  1 |          1 | fooまあまあ   |
-- |  2 |          1 | fooとても良い  |
-- |  3 |          3 | baz良かった   |
-- +----+------------+-------------+

内部結合

内部結合は、結合する互いのテーブルに対して指定したカラムの同じ値を持つデータだけを結都合して取得します。
次のテーブルがあるとします。

CREATE TABLE articles (
  id INT NOT NULL AUTO_INCREMENT,
  title VARCHAR(140), 
  PRIMARY KEY (id)
);

CREATE TABLE comments (
  id INT NOT NULL AUTO_INCREMENT,
  article_id INT,
  comment VARCHAR(140), 
  PRIMARY KEY (id)
);

INSERT INTO articles (title) VALUES 
  ('foo'),
  ('bar'),
  ('baz');

INSERT INTO comments (article_id, comment) VALUES 
  (1, 'fooは参考になった'),
  (1, 'fooはおもしろい'),
  (3, 'bazは斬新な内容だった'),
  (4, 'quxは良かった');

-- +----+-------+
-- | id | title |
-- +----+-------+
-- |  1 | foo   |
-- |  2 | bar   |
-- |  3 | baz   |
-- +----+-------+
-- +----+------------+-----------------------------------+
-- | id | article_id | comment                           |
-- +----+------------+-----------------------------------+
-- |  1 |          1 | fooは参考になった　　　　　　　　　　               |
-- |  2 |          1 | fooはおもしろい　　　 　　　　　　　　               　|
-- |  3 |          3 | bazは斬新な内容だった　　　 　　　　　　　　　         　|
-- |  4 |          4 | quxは良かった　　　　　                    　|
-- +----+------------+-----------------------------------+

このような構文で結合します。ONの後に結合の対象となるカラムを指定します。
テーブル名1 INNER JOIN テーブル名2 ON テーブル名1.カラム名1 = テーブル名2.カラム名2
内部結合すると次のようになります。

-- SELECT * FROM articles INNER JOIN comments ON articles.id = comments.article_id;
-- INNERは省略でき、上下は同じ結果になります。
SELECT * FROM articles JOIN comments ON articles.id = comments.article_id;

-- 結果
-- +----+-------+----+------------+-----------------------------------+
-- | id | title | id | article_id | comment                           |
-- +----+-------+----+------------+-----------------------------------+
-- |  1 | foo   |  1 |          1 | fooのは参考になった　　　　　　　               |
-- |  1 | foo   |  2 |          1 | fooのはおもしろい　　　　　　　                 |
-- |  3 | baz   |  3 |          3 | bazのは斬新な内容だった　　　　　　　　           |
-- +----+-------+----+------------+-----------------------------------+

取得カラムを指定する場合、下記のようにテーブル名を省略することができます。

SELECT
-- articles.id, articles.title, comments.comment 
  articles.id, title, comment 
FROM 
  articles JOIN comments ON articles.id = comments.article_id;

-- 結果
-- +----+-------+-----------------------------------+
-- | id | title | comment                           |
-- +----+-------+-----------------------------------+
-- |  1 | foo   | fooのは参考になった                   |
-- |  1 | foo   | fooのはおもしろい                     |
-- |  3 | baz   | bazのは斬新な内容だった　               |
-- +----+-------+-----------------------------------+

外部結合

外部結合には左外部結合と右外部結合があります。

左外部結合

結合構文を書いたときに、先に指定したテーブルレコードを基準にした結合を左外部結合といます。
方法はJOINの前にLEFT　OUTERまたはLEFTと付け加えるだけです。

SELECT 
  *
FROM 
  articles LEFT JOIN comments ON articles.id = comments.article_id;
-- 結果   全てのarticleに対してcommentが取得されているので、commentの無いbarはNULLになります。
-- +----+-------+------+------------+-----------------------------------+
-- | id | title | id   | article_id | comment                           |
-- +----+-------+------+------------+-----------------------------------+
-- |  1 | foo   |    1 |          1 | fooのは参考になった　　　　　　　               |
-- |  1 | foo   |    2 |          1 | fooのはおもしろい　　　　　　　                 |
-- |  3 | baz   |    3 |          3 | bazのは斬新な内容だった　　　　　　　　           |
-- |  2 | bar   | NULL |       NULL | NULL                              |
-- +----+-------+------+------------+-----------------------------------+

右外部結合

結合構文を書いたときに、後に指定したテーブルレコードを基準にした結合を右外部結合といます。
JOINの前にRIGHT　OUTERまたはRIGHTと付け加えるだけです。

SELECT 
  *
FROM 
  articles RIGHT JOIN comments ON articles.id = comments.article_id;
-- 結果　　　　　　　　　commentsを基準に取得しているのでarticlesに無いid,titleはnullになります。
-- +------+-------+----+------------+-----------------------------------+
-- | id   | title | id | article_id | comment                           |
-- +------+-------+----+------------+-----------------------------------+
-- |    1 | foo   |  1 |          1 | fooのは参考になった　　　　　　　               |
-- |    1 | foo   |  2 |          1 | fooのはおもしろい　　　　　　　                 |
-- |    3 | baz   |  3 |          3 | bazのは斬新な内容だった　　　　　　　　           |
-- | NULL | NULL  |  4 |          4 | quxは良かった　　　　　                     |
-- +------+-------+----+------------+-----------------------------------+

FOREIGN KEY（外部キー制約）

FOREIGN KEYで結びついたテーブルは整合性が担保されます。
右外部結合ではNULLを返す使用でしたが、そもそも存在しない記事に対してコメントがあるというのは不自然です。
article_idと同じ値がarricles.idに無い場合下記のように指定してエラーを出力することができます。

CREATE TABLE articles (
  id INT NOT NULL AUTO_INCREMENT,
  title VARCHAR(140), 
  PRIMARY KEY (id)
);

CREATE TABLE comments (
  id INT NOT NULL AUTO_INCREMENT,
  article_id INT,
  comment VARCHAR(140), 
  PRIMARY KEY (id),
-- articlesを参照してarticle_idと同じ値があるか確認
  FOREIGN KEY (article_id) REFERENCES articles(id)
);

FOREIGN KEY制約のあるテーブルの削除

前述のようにcommentsでarticlesを参照するようにした場合、
articlesテーブルを削除しようとするとcommentsテーブルとの整合性が取れなくなるためエラーが発生し、削除することができません。
テーブルを削除する場合は次のように参照指示を出しているテーブルから削除する必要があります。

-- この順で記述する。
DROP TABLE IF EXISTS comments;
DROP TABLE IF EXISTS articles;

FOREIGN KEY制約のレコードの削除・更新

次のようにON 処理内容 CASCADEとしてFOREIGN KEY制約のかかった片方のテーブルから特定のデータに変更があると自動的にもう片方のテーブルのデータも変更されます。

CREATE TABLE comments (
  id INT NOT NULL AUTO_INCREMENT,
  article_id INT,
  comment VARCHAR(140), 
  PRIMARY KEY (id),
  FOREIGN KEY (article_id) REFERENCES articles(id)
    ON DELETE CASCADE
    ON UPDATE CASCADE
);

TRIGGER

特定のテーブルの変更をトリガーとして処理を指定することができます。
articleのtitleに変更があった場合をトリガーとしてlogテーブルに変更前と変更後のtitleカラムを作成する例です。

CREATE TABLE articles (
  id INT NOT NULL AUTO_INCREMENT,
  title VARCHAR(140), 
  PRIMARY KEY (id)
);

CREATE TABLE logs (
  id INT NOT NULL AUTO_INCREMENT,
  title VARCHAR(140),
  created DATETIME DEFAULT NOW(),
  PRIMARY KEY (id)
);

INSERT INTO articles (title) VALUES 
  ('foo'),
  ('bar'),
  ('baz');

-- トリガーの処理内容
CREATE TRIGGER
  articles_update_trigger --　トリガー名
AFTER UPDATE ON -- AFTER UPDATE　以外でもBEFOREやDELETEなどの指定ができる。
  articles -- articlesが更新された後に
FOR EACH ROW -- 更新されたそれぞれの行に
  INSERT INTO -- titleを挿入
    logs (title)
  VALUES -- トリガーの中で使える`OLD`と`NEW`を使用して前後の値を取得しています。
    (CONCAT(OLD.title, ' -> ', NEW.title));

UPDATE articles SET title = 'qux' WHERE id = 1; -- fooをquxに変更

SELECT * FROM logs;
-- 結果
-- +----+------------+---------------------+
-- | id | title      | created             |
-- +----+------------+---------------------+
-- |  1 | foo -> qux | 2021-07-08 20:34:27 |
-- +----+------------+---------------------+

外部（CSV）ファイルのデータ読み込み

LOAD DATA LOCAL INFILEで読み込みます。

CREATE TABLE articles (
  id INT NOT NULL AUTO_INCREMENT,
  title VARCHAR(200), 
  author INT,
  body VARCHAR(1000),
  PRIMARY KEY (id)
);

LOAD DATA LOCAL INFILE 'ファイル名.csv' INTO TABLE articles
  FIELDS TERMINATED BY ',' -- 項目の区切りをカンマに
  LINES TERMINATED BY '\n' -- 行の区切りを改行に
  IGNORE 1 LINES -- １行目を無視
  (title, author, body); -- カラム指定

-- SHOWコマンドでトリガー内容を確認することができます。
-- 表示が横に長く崩れてしまうので、行末にGをつけて表示方法変えています。
SHOW TRIGGERS\G

TRANSACTION

トランザクションとは何らかの処理のまとまりのことです。
トランザクションに対して次の2つの指示をすることができます。

• コミット - コミットは処理の確定し、処理途中に発生した外部からの処理要求を無視します。また、コミットした処理はロールバックすることはできません。
• ロールバック - ロールバックは処理中に問題が発生した場合、それまでの処理を取り消しエラーを返します。

START TRANSACTION;
-- 何らかの処理
COMMIT;
-- または
START TRANSACTION;
-- 何らかの処理
ROLLBACK;

INDEX

インデックスを作成することでデータを素早く呼び出すことができます。
また、テーブルにPRIMARY KEYを指定した時点でPRIMARY INDEXが作成されるので、PRIMARY KEYであるidを使用した検索は高速で処理されます。
反対に、インデックスを作りすぎてしまうと、検索は早くなりますが、データを変更した際に関連する全てのインデックスのデータも変更するため処理が遅くなります。

すでに作成されているカラムに対してインデックスを作成す場合、次のようにします。
ADD INDEX の後に好きなインデックス名をつけて引数に対象のカラム名を指定。

ALTER TABLE テーブル名 ADD INDEX インデックス名(対象のカラム名);

SHOW INDEX FROM テーブル名\Gでインデックスを確認することができます。

また、EXPLAIN クエリでクエリに対してどのインデックスを使用しているか確認でき、次のような形で取得できます。
rowsの数が大きこと処理が重たいことになります。

           id: SELECT 識別子
  select_type: SELECT 型
        table: 出力行のテーブル
　　　　　　partitions: 一致するパーティション
         type: 結合型
possible_keys: 選択可能なインデックス
          key: 実際に選択されたインデックス
      key_len: 選択されたキーの長さ
          ref: インデックスと比較されるカラム
         rows: 調査される行の見積もり
     filtered: テーブル条件によってフィルタ処理される行の割合
        Extra: 追加情報