久々に DB (MySQL) を触る時に振り返る Tips

Terms

Schema (スキーマ)

データの構造、性質やほかのデータとの関連、DB を操作するときのルールや表現法などを定義したもののことを言う。
が、基本的には広い意味で 「DB 全体の構造(どんなテーブルがあるか、テーブル間の繋がりはどうかなど)」 という文脈で使われる。

Migration (マイグレーション)

インフラエンジニアをやっていると、古いサーバーから新しいサーバーにデータを移すというような文脈で使われている気がするが DB における文脈では若干異なる。※1
DB における Migration とは、Migration ファイルを元にモデルが SQL を生成し、テーブル操作(テーブルの作成・削除、カラムの作成・削除、カラムの名前・型変更、外部キーの設定など ※2)を行うこと。

旧来は DB に直接接続して行っていた処理をコードベースで置き換えることができるので

• DB の定義変更用の SQL を作成する手間がなくなる
• DB の状態がバージョン管理されるので、複数人での開発が捗る

といったメリットがある。
ただ、DB のマイグレーションはアプリーケーションの根幹である DB の構造を変えてしまうことなので慎重に行う必要がある。

※1 刷新する対象が Hardware なのか、DB のデータなのかという点が異なるだけで、古い状態から新しい状態に更新するという点はインフラエンジニアの文脈と同じ。
※2 Migration にはテーブルの作成だけではなく、他にもテーブルの削除、カラムの作成・削除などもっと広い文脈で使われる点に注意。

起動と接続

# 起動
mysql.server start

# 接続
mysql -u username -h localhost -p

DB の構造チェック手順

1. どんな DB があるのか、DB の一覧確認

SHOW DATABASES;

1. どんなテーブルがあるのか、テーブルの一覧確認

SHOW TABLES;

1. カラムや型など、テーブルの詳細確認

DESC table_name;

DB・テーブル・カラムの作成・削除

DB

# 作成
CREATE DATABASE db_name;

# 削除
DROP DATABASE db_name;

テーブル

# 作成
CREATE TABLE table_name;

# 削除
DROP TABLE table_name;

カラム

# 作成
ALTER TABLE table_name ADD column_name type_name;

# 削除
ALTER TABLE table_name DROP column_name;

CRUD

CRUD	SQL	Function
Create	INSERT文	データの追加・作成
Read	SELECT文	データの取得・読み取り
Update	UPDATE文	(既に存在している)データの更新
Delete	DELETE文	データの削除

テーブル結合

つい忘れがちなのがテーブル結合についてまとめる。

そもそもなんでテーブル結合するの？

例えば A と B というテーブルがあるとして、A と B の検索結果をまとめたデータがほしいという場合、
「A を検索 → B を検索 → A と B の検索結果をまとめる」という手順でやると効率が悪い。
なので、通常は「A と B のテーブルをまとめる → 検索結果を取得」という検索時に先にテーブルをまとめてしまう手順を取る。

テーブル結合の種類は3種類

1. 内部結合 (INNER JOIN)

それぞれのテーブルの指定したカラムの値が一致するものだけを結合する。
→ ベースとなるテーブルから、条件にマッチするレコードがないものは削除される

SELECT カラム1, カラム2, ... 
FROM テーブル１ INNER JOIN テーブル2 ON 結合の条件

2. 左外部結合 (LEFT OUTER JOIN)

外部結合は、内部結合のようにそれぞれのテーブルの指定したカラムの値が一致するものを結合するのに加え、どちらかのテーブルにしか存在しないものに関しても取得する。
→ 条件の左側のテーブルの全件は漏れなく取得し、存在しないカラムは null となる

SELECT カラム1, カラム2, ...,
FROM テーブル1(基準) LEFT OUTER JOIN テーブル名2 ON 結合の条件

3. 右外部結合 (RIGHT OUTER JOIN)

条件の右側のテーブルの全件は漏れなく取得し、存在しないカラムは null となる

SELECT カラム1, カラム2, ...,
FROM テーブル1 RIGHT OUTER JOIN テーブル名2(基準) ON 結合の条件

INNER JOIN では ON 句でも WHERE 句でも、共通して条件が満たされていない行は削ぎ落とされる。
そのため以下の結果は変わらない。

SELECT hoge.name, fuga.name
FROM hoge INNER JOIN fuga ON hoge.fugaId = fuga.id
WHERE fuga.id = '1';

SELECT hoge.name, fuga.name
FROM hoge INNER JOIN fuga ON hoge.fugaId = fuga.id AND fuga.id = '1'

OUTER JOIN では基準のテーブルの行を必ず全て「呼出」し、基準のテーブルに存在しない行については NULL が埋められていくため、ON でデータの抽出は出来ない。
ON 句で結合条件を定義し、WHERE 句で抽出を行う。

その他

• ON の結合条件はだいたい hoge.hugaId = huga.id のようになる。
• RIGHT OUTER JOIN は、記述の順番を変えることで LEFT OUTER JOIN に書き換えることが可能。人によっては、SQL の可読性をあげるために RIGHT OUTER JOIN は使わずに、全て LEFT OUTER JOIN で記述するという人もいるくらい。

INDEX (インデックス)

インデックスってなに？

• テーブルとは別に検索用に最適化された状態のデータ
• あるコンテキストにそってソートしたりフィルターした別のデータ群（インデックス・テーブル）のようなもの
• MySQL では、インデックスが貼られたカラムは検索しやすいようツリー構造に加工される B-Tree という方式が採用されている

インデックスを貼った方が良いテーブル・カラムとは

• データ量が多く、少量のレコードを検索する対象となるテーブル (1000行以下のカラムにインデックス貼ってもあんまり効果がないとか)
• where, order by, join でよく使われるカラム
• NULL が多いデータから、NULL 以外の検索をする (インデックスには NULLが含まれないので有効)
• Cardinality (直訳では集合に含まれる要素の数という意味。データの種類がどれくらいあるかということ) が高いカラム。データの一意性が高いカラム。
• insert/update/delete の処理が頻繁に走るテーブルは避ける (インデックステーブルも対応してデータの更新を逐一行わないといけないので、インデックスを張りすぎるとその分やらないといけない処理が多くなるため)

インデックス関連のコマンド

• インデックスの確認

SHOW INDEX FROM db_name;

• インデックスの追加

ALTER TABLE table_name ADD INDEX index_name(column_name_1);

• マルチカラムインデックスの追加

ALTER TABLE table_name ADD INDEX index_name(column_name_1, column_name_2);

• インデックスの削除

ALTER TABLE table_name DROP INDEX index_name;

SQL のパフォーマンス改善

よく使われるカラムにはインデックスをつける

where, order by, join でよく使われるカラムにはインデックスをつける。

文字列の比較より数字の比較の方がはやい

カラムをINT型にできないか検討する。例えば性別などのカラムだと FEMALE と MALE より0と1の方がいい。
文字列を比較する時は各文字を比較する必要があるが、数字は（桁数に関係なく）すぐにできる。

複数条件がある時は、最初に大きく絞るような条件を持ってくる

SQL 文のアルゴリズムは複数の条件の場合、定義されている条件順でループし排除しながら次の条件に進む。
まずは、インデックスついてるカラムを一番前にした方がいい。

パフォーマンスの調査は EXPLAIN を使う

EXPLAIN をつけるだけで、SQL 文の実行計画を確認することができる。実行計画からインデックスが適切に使われているか、テーブルをフルスキャンしていないかなどがわかる。

全然最適化されてない例は以下のような感じ。

[approduce]> EXPLAIN SELECT * FROM sample WHERE hoge_id = '1234567890' limit 1;
Connection id:    2095131
Current database: sample
| id | select_type | table    | partitions | type   | possible_keys | key     | key_len | ref  | rows   | filtered | Extra       |
|  1 | SIMPLE      | events   | NULL       | ALL    | NULL          | NULL    | NULL    | NULL | 358878 |    10.00 | Using where |

type == ALL、possible_keys == NULL、key == NULL ということから、テーブルをフルスキャンしてしまっており全然最適化されてないということがわかる。
このケースでは、hoge_id は INT ではなく STRING になっていないか、Cardinality は高いかなどを考慮してインデックスを追加することを検討する。