リレーショナルモデルと集合演算 — SQLは「表」を扱っていない

リレーショナルモデルと集合演算。二組のデータカードを分類する透明な丸いトレーと共通のカード。

SQLが扱っているのは、表ではなく集合です。リレーショナルモデルと集合演算から、書き方の理由を確かめます。

これは深掘りです。本線の順路ではありません。 読まなくても先へは進めます。 本線のプロセスを止めても消えない場所 — MySQLとSQLの基本の回の続きです。あの回で SELECT を打って表が出たところから、この深掘りシリーズが始まりました。この記事は、そのシリーズの締めくくりです。

前回: GRANTと最小権限 — rootで繋ぐと全部持っていかれる

作業場所はVMの中(Ubuntu Server)です。MySQL が動いていて appdb があれば、それ以外の準備は要りません。練習用のテーブルはこの記事の中で作ります。最後に、シリーズ全体で作った d_ 付きのテーブルを全部消す手順まで書きます。

ここまで16本、SQLを打ってきました。

SELECT の実行順を見て、JOIN が表を繋いでいないことを知って、GROUP BY で行が減る理由を確かめて、索引が「張る」と「使われる」で別物だと分かって。打ったSQLの本数だけで言えば、もう数百行あります。

でも、まだ一度も名前を出していないものがあります。

そのSQLが立っている土台です。あなたが SELECT と書くたびに使っている考え方には、ちゃんと名前が付いています。

リレーショナルモデル(関係モデル)です。

1970年に、E.F.コッド(Edgar F. Codd)という人が論文1本で提案しました。MySQL より25年、PostgreSQL より26年、あなたが触っているどのソフトよりも先に、考え方のほうが存在していました。 ソフトはあとから、その考え方を実装したものです。

だから、こういうことが起きます。

  • 「リレーショナル」の「リレーション」は、テーブル同士の関係のことではありません。 ほとんどの人が最初にこう誤解します(私もそうでした)
  • リレーショナルモデルには「重複した行」が存在しません。 でもMySQLには、同じ行を3つ入れられます
  • リレーショナルモデルの行に順序はありません。 でも SELECT * FROM d_emp; は、毎回だいたい同じ順で返ってきます

つまり、SQLは理論の通りには作られていません。 ズレています。

そしてそのズレが、あなたがこのシリーズでハマってきたところと、ほぼ全部一致します。 NULL も、DISTINCT も、ORDER BY を書かないと順序が決まらないことも、全部そうです。

この記事では、そのズレをSQLで実際に見にいきます。 理屈だけ読んで終わりにはしません。打って、数を数えて、目で確かめます。

対象レベル: 中級〜上級 / 区分: 発展

目次

この記事でわかること

  • リレーショナルモデルの「関係」は表ではなく集合だということ。行にも列にも順序がなく、重複行は存在しないのが理論上の前提であること
  • SQLはその前提を守っていないこと。だから重複行が入り、ORDER BY が要り、UNION と UNION ALL が別物になること
  • UNION が UNION ALL より遅いのは、重複を消すために、結果を一時テーブルに入れて突き合わせているからだということ。それが EXPLAIN に出ること
  • 表を分ける理由は「異常を消すため」だけではないこと。1つの関係は1つの事実だけを表す、という考え方から見ると、正規化は結果でしかないこと
  • SQLは「どう取るか」を書かないこと。手順を決めるのはオプティマイザで、それが分業として成立していること。そして「関係」を捨てると、その分業も一緒に失うこと

この記事でやること

  1. SELECT VERSION(); でMySQLのバージョンを確認する(この記事の後半は 8.0.31 以降でないと動きません)
  2. 練習用のテーブルを作り、同じ行を3つ入れて、重複行が入ることを確かめる
  3. 同じテーブルに違うSQLを打って、返る順序が変わることを確かめる(順序は意味ではなく事情だと知る)
  4. 集合演算4つ(UNION / UNION ALL / INTERSECT / EXCEPT)を打ち、古いMySQL向けの書き換えも用意する
  5. UNION と UNION ALL を EXPLAIN して、重複除去のコストを見る
  6. シリーズ全体で作った d_ 付きのテーブルを、確認してから全部消す

先に知る言葉

この記事で初めて出てくる言葉です。 基本的な言葉は用語集にもまとめてあります。

言葉意味
リレーショナルモデルデータを「関係」の集まりとして扱う考え方。1970年提案。ソフトの名前ではない
関係(リレーション)行の集合のこと。「テーブル同士の関連」ではない。1枚のテーブルが1つの関係にあたる
集合同じものが2つ入らない入れ物。数学の集合と同じ。{1, 2, 3} に 1 を足しても {1, 2, 3} のまま
多重集合(バッグ)同じものが何個でも入る入れ物。SQLのテーブルはこちら。{1, 1, 2} は {1, 2} と別物
集合演算2つの集合を足す・重なりを取る・引く、という操作。SQLでは UNION / INTERSECT / EXCEPT
宣言的「何が欲しいか」だけを書き、「どうやって取るか」は書かない書き方。SQLはこれ
オプティマイザ「どうやって取るか」をデータベースが自分で決める部分。実行計画を作る担当
SQL標準 / 方言SQLには国際規格があり、各製品がそれを一部だけ実装している。規格外の独自機能を方言と呼ぶ

「リレーショナル=表と表を関連づけられるDB」ではありません。 リレーショナル=データを「行の集合」として扱うDBです。関連づけ(JOIN)はその結果できることの1つにすぎません。 ここは言葉の見た目に引っ張られて、ほぼ全員が一度間違えます。

まずバージョンを確かめる

この記事の後半で使う INTERSECT と EXCEPT は、MySQL 8.0.31 以降でないと動きません。 それより古いと構文エラーになります。先に確認します。

VMの中(Ubuntu Server)で、MySQLに入ります。

bash
$ mysql -u appuser -p appdb

入ったら、MySQLのプロンプト(mysql>)で打ちます。

SELECT VERSION();
+-----------+
| VERSION() |
+-----------+
| 8.0.43    |
+-----------+

[スクリーンショット: 01_mysqlバージョン確認.png] SELECT VERSION(); を打って、バージョン番号が返っている画面を撮ってください。

読み方は3つの数字を左から比べるだけです。8.0.43 なら、8.0.31 より新しいので使えます。8.0.28 だったら使えません。その場合の書き換えも、あとで用意します。

Ubuntu 22.04 / 24.04 の apt で入れた MySQL は 8.0 系です。その中の3つ目の数字は、apt upgrade をかけたタイミングで上がっていきます。 動かなかったら、まずここを疑ってください。構文を間違えたのではなく、そのMySQLがまだその構文を知らないだけです。

「表」ではなく「関係」

リレーショナルモデルが言う「関係」には、3つの前提があります。

  1. 行に順序はない
  2. 列に順序はない(列は名前で識別する)
  3. 同じ行は2つ存在しない(集合だから)

この3つを、順番にSQLで壊していきます。 壊れるのが正常です。SQLは理論をそのまま実装したものではないからです。

練習用のテーブルを作る

この記事で使う練習用テーブルは、すべて d_ で始めます。 シリーズ全体でそうしてきたのと同じで、最後に「d_ で始まるものだけ消す」ためのしるしです。

DROP TABLE IF EXISTS d_rel_dup;
CREATE TABLE d_rel_dup (
  code VARCHAR(10),
  name VARCHAR(20)
);

主キーも UNIQUE も付けていません。 わざとです。

1. 同じ行を3つ入れてみる

INSERT INTO d_rel_dup (code, name) VALUES
  ('A01', 'ねじ'),
  ('A01', 'ねじ'),
  ('A01', 'ねじ');
Query OK, 3 rows affected (0.01 sec)
Records: 3  Duplicates: 0  Warnings: 0

エラーは出ません。 3行入りました。

SELECT COUNT(*) FROM d_rel_dup;
+----------+
| COUNT(*) |
+----------+
|        3 |
+----------+

リレーショナルモデルの上では、これは起こり得ません。 集合に同じ要素を3回足しても、要素は1つのままだからです。理論の上では、いまの d_rel_dup は「1行の関係」でなければいけません。

でもMySQLは3と答えました。

つまり、SQLのテーブルは集合ではなく多重集合(バッグ)です。 同じものが何個でも入る入れ物です。そして、その1点から出てくる結果が、これです。

SELECT DISTINCT code, name FROM d_rel_dup;
+------+------+
| code | name |
+------+------+
| A01  | ねじ |
+------+------+

DISTINCT という機能が存在すること自体が、「SQLのテーブルは集合ではない」という証拠です。 本当に集合なら、重複はそもそも入らないので、消す機能は要りません。

SELECT の回で「DISTINCT は行全体で見る」という話をしました。あれの理由がここです。 DISTINCT は「集合に戻す」操作で、集合の要素は行そのものだからです。

では、なぜ主キーを付けるのか。 ここまで来ると、答えが変わります。

  • 今までの答え: 「1件を特定できるようにするため」
  • ここでの答え: 「テーブルを、理論上の『関係』に近づけるため」

主キーや UNIQUE を付けた瞬間、そのテーブルは重複行を持てなくなり、集合に一歩近づきます。 制約の回でやった UNIQUE は、「間違いを防ぐ道具」であると同時に「関係であることを守る道具」でした。

2. 列に順序はない、はずだった

リレーショナルモデルでは、列は名前で識別します。順番は持ちません。 {name, code} と {code, name} は同じ関係です。

SQLはそうなっていません。

SELECT * FROM d_rel_dup LIMIT 1;
+------+------+
| code | name |
+------+------+
| A01  | ねじ |
+------+------+

CREATE TABLE で書いた順に出ました。 * が列の順番を知っているということは、テーブルが列の順番を持っているということです。

そして、それが事故になる形はこれです。

INSERT INTO d_rel_dup VALUES ('ボルト', 'B02');

列名を書かずに入れると、位置で入ります。 code に ボルト、name に B02 が入りました。エラーは出ません。 型も長さも通ってしまうからです。

SELECT * FROM d_rel_dup;
+--------+------+
| code   | name |
+--------+------+
| A01    | ねじ |
| A01    | ねじ |
| A01    | ねじ |
| ボルト | B02  |
+--------+------+

入れ替わったまま入りました。 元に戻します。

DELETE FROM d_rel_dup WHERE code = 'ボルト';

INSERT で列名を省略してはいけない理由は、ここまで「読みにくいから」で説明されがちです。 本当の理由は、列の順番はデータの意味ではないのに、SQLはそれを意味として使えてしまうからです。 ALTER TABLE で列を1本足した日に、その INSERT は静かに壊れます。(DDLの回で見た通りです)

3. 行に順序はない、を確かめる

ここが一番おもしろいところです。同じテーブルに、違うSQLを打つと、違う順で返ってきます。

DROP TABLE IF EXISTS d_rel_row;
CREATE TABLE d_rel_row (
  id   INT PRIMARY KEY,
  name VARCHAR(20),
  KEY idx_name (name)
);

INSERT INTO d_rel_row (id, name) VALUES
  (3, 'sato'),
  (1, 'tanaka'),
  (2, 'suzuki');

入れた順は 3 → 1 → 2 です。 では取り出します。

SELECT * FROM d_rel_row;
+----+--------+
| id | name   |
+----+--------+
|  1 | tanaka |
|  2 | suzuki |
|  3 | sato   |
+----+--------+

入れた順ではなく、id 順で返りました。 ORDER BY は書いていません。

次に、name だけを取ります。

SELECT name FROM d_rel_row;
+--------+
| name   |
+--------+
| sato   |
| suzuki |
| tanaka |
+--------+

同じテーブル、同じ3行なのに、順番が変わりました。 これも ORDER BY は書いていません。

[スクリーンショット: 02_順序が変わる.png] SELECT * FROM d_rel_row; と SELECT name FROM d_rel_row; を続けて打ち、返る順が違っている画面を撮ってください。

理由は、索引の回でやったことで説明できます。 EXPLAIN で見ます。

EXPLAIN SELECT name FROM d_rel_row;

key の欄に idx_name、Extra に Using index が出ます。 name だけなら索引を読むだけで足りるので、MySQLは表を読まずに索引を読みました。 そして索引は name の順に並んでいます。 だから name 順で返りました。

SELECT * のほうは、id の主キー順に並んだ本体(クラスタ化索引)をそのまま読んだので、id 順で返りました。

順序は、データの意味ではありません。読み方の事情がそのまま外に出ているだけです。 だから「たまたま合っている順序」に依存したコードは、索引を1本足した日に壊れます。

順序が欲しいなら、書くしかありません。

SELECT name FROM d_rel_row ORDER BY id;

「ORDER BY を書け」は作法ではありません。 SQLの返す行に順序という意味は最初から無いので、欲しいなら自分で足すしかない、という話です。

理論とSQLのズレを、1枚にする

リレーショナルモデルの前提SQL(MySQL)では実際に困る形
関係は行の集合。同じ行は2つ存在しないテーブルは多重集合。同じ行が何個でも入る集計が二重に数えられる。DISTINCT が必要になる
行に順序はない順序は保証されないが、何かの順では返るORDER BY 無しの順に依存 → 索引追加やバージョン更新で壊れる
列に順序はない。名前で識別するSELECT * は定義順。INSERT は列名省略で位置で入る列を1本足した日に INSERT が静かに壊れる
値は必ず1つ。「不明」は無いNULL がある。真・偽・不明の三値論理になる= NULL が真にならない。NOT IN に NULL が1つで全滅
関係の組み合わせは集合演算UNION は集合寄り(重複を消す)、UNION ALL は多重集合のまま遅いほうを既定で使ってしまう
すべての操作の結果もまた関係サブクエリの結果も表として扱える(ここは理論通り)サブクエリの回でやった入れ子が成立する

このシリーズでハマってきたところが、右の列にほぼ全部入っています。

「SQLは難しい」のではありません。「SQLは、理論をそのまま実装していない」だけです。どこがズレているかを1枚で持てば、初見の挙動でも当たりが付きます。

集合演算 — 表を「足す・重ねる・引く」

関係が集合なら、集合として足したり引いたりできるはずです。 SQLにはその演算があります。

練習用の2つの集合

DROP TABLE IF EXISTS d_set_a;
DROP TABLE IF EXISTS d_set_b;

CREATE TABLE d_set_a (id INT PRIMARY KEY, name VARCHAR(20));
CREATE TABLE d_set_b (id INT PRIMARY KEY, name VARCHAR(20));

INSERT INTO d_set_a (id, name) VALUES (1,'tanaka'), (2,'suzuki'), (3,'sato');
INSERT INTO d_set_b (id, name) VALUES (2,'suzuki'), (3,'sato'), (4,'takahashi');

suzuki と sato が両方にいます。 tanaka はAだけ、takahashi はBだけです。

UNION — 足す(重複を消す)

SELECT id, name FROM d_set_a
UNION
SELECT id, name FROM d_set_b;
+----+-----------+
| id | name      |
+----+-----------+
|  1 | tanaka    |
|  2 | suzuki    |
|  3 | sato      |
|  4 | takahashi |
+----+-----------+
4 rows in set (0.00 sec)

3行 + 3行 = 4行です。重なった2行が1つにまとめられました。 これが集合の足し算です。

UNION ALL — 足す(重複を残す)

SELECT id, name FROM d_set_a
UNION ALL
SELECT id, name FROM d_set_b;
+----+-----------+
| id | name      |
+----+-----------+
|  1 | tanaka    |
|  2 | suzuki    |
|  3 | sato      |
|  2 | suzuki    |
|  3 | sato      |
|  4 | takahashi |
+----+-----------+
6 rows in set (0.00 sec)

6行です。 id が重複していますが、エラーにはなりません。 結果は表であって、主キーを持たないからです。

UNION が集合、UNION ALL が多重集合です。SQLは両方持っている、という言い方が正確です。 そして「重複が無いと分かっているなら UNION ALL を書く」のが正しい理由が、次で見えます。

なぜ UNION のほうが遅いのか — EXPLAIN で見る

重複を消すには、全部の行を見比べる必要があります。 見比べるには、並べるか、ハッシュを作るかしかありません。どちらも仕事です。

EXPLAIN SELECT id, name FROM d_set_a
UNION
SELECT id, name FROM d_set_b;
+----+--------------+------------+------+---------+-----------------+
| id | select_type  | table      | type | rows    | Extra           |
+----+--------------+------------+------+---------+-----------------+
|  1 | PRIMARY      | d_set_a    | ALL  |       3 | NULL            |
|  2 | UNION        | d_set_b    | ALL  |       3 | NULL            |
| NULL | UNION RESULT | <union1,2> | ALL | NULL   | Using temporary |
+----+--------------+------------+------+---------+-----------------+

3行目に注目してください。 UNION RESULT という行があり、Extra が Using temporary です。

「一時表を作った」という意味です。重複を消すために、結果を一度どこかに置いて見比べています。

UNION ALL に変えます。

EXPLAIN SELECT id, name FROM d_set_a
UNION ALL
SELECT id, name FROM d_set_b;
+----+-------------+---------+------+------+-------+
| id | select_type | table   | type | rows | Extra |
+----+-------------+---------+------+------+-------+
|  1 | PRIMARY     | d_set_a | ALL  |    3 | NULL  |
|  2 | UNION       | d_set_b | ALL  |    3 | NULL  |
+----+-------------+---------+------+------+-------+

UNION RESULT の行が消えました。 一時表が要りません。上から順に流すだけだからです。

[スクリーンショット: 03_union_explain比較.png] UNION と UNION ALL の EXPLAIN を続けて打ち、Using temporary の有無が違う画面を撮ってください。

表示はMySQLのバージョンで変わります。 Using temporary が出ない環境もあります。 見るべきは「行が1本多いかどうか」です。多い=重複を消す仕事が1段増えている、と読みます。

3行同士なら差は出ません。 10万行同士なら、はっきり出ます。 索引の回で「Using filesort が消えると速くなる」のを見たのと、まったく同じ話です。

判断はこうです。

状況書くもの
重複が出ないと分かっている(別々の期間、別々の店舗など)UNION ALL
重複を消したいUNION
重複が出るか分からないまず UNION ALL で件数を見て、重複の有無を確かめてから決める

「とりあえず UNION」は、毎回「一時テーブルで重複を突き合わせる仕事」を1回買っていることになります。

INTERSECT と EXCEPT — 重なりと引き算

ここが 8.0.31 以降でないと動かないところです。

SELECT id, name FROM d_set_a
INTERSECT
SELECT id, name FROM d_set_b;
+----+--------+
| id | name   |
+----+--------+
|  2 | suzuki |
|  3 | sato   |
+----+--------+

両方にいる行だけが出ました。

SELECT id, name FROM d_set_a
EXCEPT
SELECT id, name FROM d_set_b;
+----+--------+
| id | name   |
+----+--------+
|  1 | tanaka |
+----+--------+

Aにいて、Bにいない行です。引き算です。

向きがあります。 ひっくり返すと結果が変わります。

SELECT id, name FROM d_set_b
EXCEPT
SELECT id, name FROM d_set_a;
+----+-----------+
| id | name      |
+----+-----------+
|  4 | takahashi |
+----+-----------+

UNION と INTERSECT は順番を入れ替えても同じ結果、EXCEPT は変わります。 足し算と掛け算には順番が無く、引き算にはある。 それと同じです。

古いMySQLでの書き換え

SELECT VERSION(); が 8.0.31 より小さかった場合、こうなります。

ERROR 1064 (42000): You have an error in your SQL syntax; ... near 'INTERSECT'

構文を間違えたのではありません。そのMySQLが INTERSECT を知らないだけです。書き換えます。

INTERSECT の代わり(EXISTS):

SELECT a.id, a.name
FROM d_set_a a
WHERE EXISTS (
  SELECT 1 FROM d_set_b b
  WHERE b.id = a.id AND b.name = a.name
);

EXCEPT の代わり(NOT EXISTS):

SELECT a.id, a.name
FROM d_set_a a
WHERE NOT EXISTS (
  SELECT 1 FROM d_set_b b
  WHERE b.id = a.id AND b.name = a.name
);

IN / NOT IN でも書けます。 比べる列が1本だけならこちらが短いです。

SELECT id, name FROM d_set_a WHERE id IN (SELECT id FROM d_set_b);

ただし NOT IN は使わないでください。

-- ⚠️ 副問い合わせ側に NULL が1つでもあると、結果が丸ごと0行になる
SELECT id, name FROM d_set_a WHERE id NOT IN (SELECT id FROM d_set_b);

SELECT の回でやった通りです。エラーは出ません。黙って0行になります。 NOT EXISTS は NULL があっても壊れません。

そして、書き換えた瞬間に分かることがあります。

INTERSECTEXISTS での書き換え
行数3行6行
読んだとき分かること「重なりが欲しい」「Aを1行ずつ見て、Bに同じものがあるか探している」

INTERSECT は「何が欲しいか」だけを書いています。EXISTS は「どう探すか」が少し混ざっています。

この差が、次の話です。

なぜ「表を分ける」のか — 正規化をもう一度

本線のテーブル設計の回で、正規化をやりました。あの回の説明はこうでした。

1つの表に全部入れると、更新のときに片方だけ直してしまう。だから分ける。

これは正しいです。 実務ではこの説明で足ります。でも、これは「症状の説明」です。

「関係」の側から見ると、順番が逆になります。

1つの関係は、1種類の事実だけを表す。 2種類の事実が1枚に混ざっているから、片方だけ直せてしまう。異常はその結果でしかない。

具体で見ます。 混ざっている表です。

DROP TABLE IF EXISTS d_rel_flat;
CREATE TABLE d_rel_flat (
  emp_id    INT PRIMARY KEY,
  emp_name  VARCHAR(20),
  dept_id   INT,
  dept_name VARCHAR(20)
);

INSERT INTO d_rel_flat VALUES
  (1, 'tanaka', 10, '営業'),
  (2, 'suzuki', 10, '営業'),
  (3, 'sato',   20, '開発');

この表は、2つの事実を持っています。

  • 「社員1は tanaka で、部署10に所属している」(社員という事実)
  • 「部署10の名前は営業である」(部署という事実)

部署10の名前は、2行に書かれています。 dept_name は dept_id が決まれば決まるのに、社員の行数だけ複製されています。

確かめます。

SELECT dept_id, COUNT(DISTINCT dept_name) AS 名前の種類, COUNT(*) AS 書かれている回数
FROM d_rel_flat
GROUP BY dept_id;
+---------+------------+------------------+
| dept_id | 名前の種類 | 書かれている回数 |
+---------+------------+------------------+
|      10 |          1 |                2 |
|      20 |          1 |                1 |
+---------+------------+------------------+

「名前の種類は1なのに、2回書かれている」。 この1が2に化けた瞬間が、更新異常です。

UPDATE d_rel_flat SET dept_name = '営業部' WHERE emp_id = 1;

SELECT dept_id, COUNT(DISTINCT dept_name) AS 名前の種類 FROM d_rel_flat GROUP BY dept_id;
+---------+------------+
| dept_id | 名前の種類 |
+---------+------------+
|      10 |          2 |
|      20 |          1 |
+---------+------------+

部署10に、名前が2つできました。 どちらが正しいかは、データベースには判断できません。

さらに、この表では「まだ社員が1人もいない部署」を登録できません。 emp_id が主キーなので、社員の行なしに部署だけ書けないからです。部署という事実が、社員という事実に人質に取られています。

分けます。

DROP TABLE IF EXISTS d_rel_emp;
DROP TABLE IF EXISTS d_rel_dept;

CREATE TABLE d_rel_dept (
  dept_id   INT PRIMARY KEY,
  dept_name VARCHAR(20) NOT NULL
);

CREATE TABLE d_rel_emp (
  emp_id   INT PRIMARY KEY,
  emp_name VARCHAR(20) NOT NULL,
  dept_id  INT,
  CONSTRAINT fk_rel_emp_dept FOREIGN KEY (dept_id) REFERENCES d_rel_dept(dept_id)
);

INSERT INTO d_rel_dept VALUES (10, '営業'), (20, '開発'), (30, '総務');
INSERT INTO d_rel_emp  VALUES (1,'tanaka',10), (2,'suzuki',10), (3,'sato',20);

部署30には社員が1人もいませんが、登録できました。 事実が独立したからです。

そして、部署名はもう1箇所にしかありません。 直す場所が1つなら、片方だけ直すという操作が存在できません。

UPDATE d_rel_dept SET dept_name = '営業部' WHERE dept_id = 10;

SELECT e.emp_name, d.dept_name
FROM d_rel_emp e
JOIN d_rel_dept d ON d.dept_id = e.dept_id;
+----------+-----------+
| emp_name | dept_name |
+----------+-----------+
| tanaka   | 営業部    |
| suzuki   | 営業部    |
| sato     | 開発      |
+----------+-----------+

1行直したら、2人とも直りました。

正規化は「異常を防ぐテクニック」ではありません。 1つの関係に1種類の事実だけを置いた結果、異常が起こりようがなくなる、という順番です。 だから「どこまで正規化するか」で迷ったときの問いは、「第3正規形か」ではなく「この表は何種類の事実を書いているか」です。

そして JOIN の回で見た「JOIN は表を繋いでいない」も、ここに繋がります。 JOIN は分かれた関係から新しい関係を1つ作る演算です。繋いでいるのではなく、作っています。 作れるから、安心して分けられます。

SQLは「どう取るか」を書かない

もう1つ、ここまで一度も意識せずに使ってきたものがあります。

SELECT e.emp_name, d.dept_name
FROM d_rel_emp e
JOIN d_rel_dept d ON d.dept_id = e.dept_id
WHERE d.dept_name = '営業部';

このSQLに、手順は1つも書かれていません。

  • どちらの表を先に読むか — 書いていない
  • 索引を使うか、全部読むか — 書いていない
  • どうやって突き合わせるか(1行ずつ探すのか、ハッシュを作るのか)— 書いていない

書いてあるのは「欲しいもの」だけです。これを宣言的と言います。

手順を決めているのは、オプティマイザです。 索引の回で見た EXPLAIN は、「その決定を見せてくれ」というお願いでした。

EXPLAIN SELECT e.emp_name, d.dept_name
FROM d_rel_emp e
JOIN d_rel_dept d ON d.dept_id = e.dept_id
WHERE d.dept_name = '営業部';

d_rel_dept が先、d_rel_emp が後、という順で計画が出ます。 SQLでは d_rel_emp を先に書いたのに、です。

FROM に書いた順は、読む順ではありません。 SELECT の回で見た「SQLは書いた順に実行されない」は、論理的な順序の話でした。 ここで見ているのは、そのさらに下、物理的にどう読むかの話です。両方とも、書いた順ではありません。

この分業には、はっきりした利益があります。

分業誰の仕事変わったときどうなるか
何が欲しいかあなた要件が変わったらSQLを書き直す
どう取るかオプティマイザデータが増えても、索引が増えても、SQLは書き直さない

3行の表と、300万行の表で、同じSQLが動きます。 同じ手順で動くのではありません。違う手順が選ばれて、同じ答えが返ります。

これは、APIの回で書いたJavaScriptとは根本的に違います。 JavaScriptで配列を絞るとき、あなたは filter を1回まわすという手順そのものを書いています。データが100万件になっても、その手順は変わりません。あなたが書き直すまでは。

代償もあります。

  • オプティマイザの判断が外れることがある。 統計情報が古いとき、想定より偏ったデータのとき
  • そのとき「なぜ遅いか」が自分の書いた行に無い。 だから EXPLAIN を読む必要が出る
  • FORCE INDEX のように手順を指定する道具はあるが、使った瞬間に分業が壊れる(索引の回で書いた通りです)

「SQLはブラックボックスで気持ち悪い」という感想は、ここから来ます。 正しくは、意図的にブラックボックスにして、その中身は EXPLAIN で開けられるようにしてある、です。

「関係」を捨てると、何が手に入り、何を失うか

本線のNoSQLの回で、MongoDBを触りました。あのとき「スキーマは消えずにアプリへ移る」という結論を書きました。

いま言い直せます。 MongoDBが捨てたのは、スキーマではなく「関係」のほうです。

リレーショナルドキュメント指向(関係を捨てた側)
データの単位行の集合。関係が集合演算で組み合わさるドキュメント1個。中に入れ子を持てる
重複の扱い分けて JOIN で作る。事実は1箇所埋め込んで複製する。読むのが速い
正しさを守るのはデータベース(型・NOT NULL・UNIQUE・外部キー)アプリ(入れる前に自分で検査する)
問い合わせ宣言的。手順はオプティマイザが決める手順に近い。どのコレクションをどう辿るか自分で組む
形の違うデータ入らない。ALTER TABLE が要るそのまま入る
複数の場所にまたがる更新トランザクションでまとめて成功か失敗(本線の回)標準構成では使えない。使わなくて済む形に寄せる
横に増やす難しい。集合演算が全台にまたがるためしやすい。ドキュメントが独立しているため

手に入るもの: 形の自由と、横に増やしやすさと、読む速さ。 失うもの: 「事実は1箇所」という保証と、宣言的な問い合わせ。

そして重要なのは、失ったものが消えるわけではないことです。

「事実は1箇所」を守る仕事は、データベースからアプリへ移ります。 「どう取るか」を決める仕事も、オプティマイザからあなたへ移ります。 仕事は無くなりません。誰がやるかが変わるだけです。

データベースの選び方の回で「迷ったらリレーショナル」と書いた根拠が、ここです。個人でサービスを作る段階では、その仕事を引き取れる人手が自分1人しかいないからです。

SQLの標準と方言 — 書いたSQLはどこまで動くのか

このシリーズで打ったSQLは、MySQLで打ちました。 では、PostgreSQL で動くのでしょうか。

答えは「7〜8割は動く。残りは書き換えが要る」です。

SQLには国際規格(ISO/IEC 9075)があります。 ただし規格を完全に実装した製品は存在しません。 各製品が一部を実装し、そこに独自機能を足しています。その独自部分を方言と呼びます。

このシリーズで打ったものの中で、方言だったところを並べます。

このシリーズで使ったもの標準かPostgreSQL では
SELECT / WHERE / JOIN / GROUP BY標準そのまま動く
UNION / INTERSECT / EXCEPT標準そのまま動く
ウィンドウ関数(OVER)標準そのまま動く
WITH(CTE)標準そのまま動く
LIMIT n OFFSET m標準は FETCH FIRST n ROWS ONLYLIMIT も動く(両対応)
バッククォート 列名標準は二重引用符 "列名"動かない。"..." に直す
SHOW TABLES / DESCRIBE方言動かない。\dt / \d 表名
AUTO_INCREMENT方言動かない。GENERATED ... AS IDENTITY
IFNULL()標準は COALESCE()動かない。COALESCE() は両方で動く
CONCAT() の NULL の扱い実装依存挙動が違う(関数の回参照)
ORDER BY での NULL の位置実装依存MySQLと逆。NULLS FIRST / NULLS LAST で明示できる

この表から出てくる指針は、1つです。

移植しやすいSQLを書くコツは、「標準だけ使う」ではありません。 どれが方言かを知っておくことです。知っていれば、移すときに探す場所が分かります。

実際に試す(次にやると一番効く1つ)

本線で PostgreSQL の回をやっているなら、環境はもう手元にあります。

このシリーズで一番長いSQL — たとえばウィンドウ関数の回やCTEの回で書いたもの — を、そのまま PostgreSQL に貼ってみてください。

やることは3つだけです。

  1. 貼る
  2. エラーが出た行だけ直す
  3. 直した理由が「方言だったから」か「自分が間違えたから」かを分ける

これをやると、「どこまでが SQL という言語で、どこからが MySQL という製品か」の線が、自分の中に引けます。 本を読んで覚えるより速いです。1本のSQLで十分効きます。

EXPLAIN の読み方だけは、まるごと別物です。PostgreSQL の EXPLAIN は形式が違います。そこは「同じSQLでも、中で何をしているかは製品ごとに違う」という、この記事の話そのものの実例になります。

つまずいたところ

1. 「リレーション=テーブル同士の関係」だと10年思っていた

外部キーで表と表が繋がるから「リレーショナル」だと思っていました。 完全に逆でした。リレーション(関係)は1枚のテーブルそのものです。外部キーは後から足された仕組みで、名前の由来ではありません。この誤解のせいで、「関係を捨てる」という言葉の意味が長くつかめませんでした。

2. UNION を既定にしていた

UNION ALL は「重複を許す雑なほう」だと思っていました。 逆です。UNION ALL が素直で、UNION が余計な仕事を1つしているほうです。EXPLAIN に UNION RESULT の行が出るのを見るまで、「重複を消す」がタダだと思っていました。

3. INTERSECT が構文エラーになって、書き方を疑った

最初、INTERSECT を打ったら ERROR 1064 が出ました。 括弧の位置やセミコロンを疑って15分溶かしました。原因は、そのMySQLが 8.0.28 だったことです。ERROR 1064 は「構文が間違っている」ではなく「私にはこの構文が読めない」という意味でした。新しい構文を試すときは、先に SELECT VERSION(); を打つ。 これだけで防げました。

4. 「順序が変わる」を再現できなくて焦った

SELECT * と SELECT name で順序が変わる例を作るのに、最初は失敗しました。 3行しか無いテーブルに索引を張らずに試したので、両方とも同じ順で返ったからです。KEY idx_name (name) を足して、EXPLAIN で Using index を確認して、やっと再現しました。 「順序は事情で決まる」なら、事情のほうを作らないと再現しない、という当たり前のことに気づくのに時間がかかりました。

5. 正規化を「型」として覚えようとして失敗した

第1正規形・第2正規形・第3正規形の定義を暗記しようとして、3回忘れました。 「この表は何種類の事実を書いているか」という問い1つに置き換えたら、忘れなくなりました。 定義は、その問いを厳密に言い直したものです。問いが先で、定義が後です。

ここまでの16本で、できるようになったこと

この記事の前に、16本ありました。 主なものを、「何ができるようになったか」だけで並べます。

扱ったことできるようになったこと
SELECT の実行順と NULLWHERE で別名が使えない理由を説明できる。 NOT IN に NULL が混ざる事故を避けられる
データ型金額に FLOAT を使わない。 型を「入る/入らない」ではなく「何を保証するか」で選べる
DDLALTER TABLE を打つ前に、戻せるかを確認する
DMLUPDATE / DELETE を先に SELECT で確かめる手順が身についた
制約アプリで弾く前に、データベース側で弾く。 どちらか一方にしない
JOINJOIN が新しい表を作っていると分かる。 件数が増える理由を説明できる
集約と GROUP BYGROUP BY で行が減る理由と、WHERE と HAVING の使い分け
ウィンドウ関数行を減らさずに集計を横に付けられる。 順位・累計が1本で書ける
サブクエリ相関サブクエリが行の数だけ実行されると分かる。 JOIN に書き換えられる
CTEと再帰3段のサブクエリを WITH で上から読める形に直せる。 階層データを辿れる
関数CONCAT に NULL が1つで全部消える類の罠を先に潰せる
索引と EXPLAIN「張った」と「使われた」を分けて考えられる。 EXPLAIN を読める

このほか、同じシリーズの中で、ビューとストアドプロシージャ、トランザクションの分離レベル、遅いクエリの直し方、権限とセキュリティも扱っています。 そしてこの記事で、打ってきたSQLが何の上に載っていたのかを言葉にしました。

16本を通して身についたのは、たぶん個々の構文ではありません。 これです。

打つ → 件数を数える → 説明できない数字が出たら止まる → EXPLAIN か NULL を疑う

この順番だけは、どのデータベースに移っても変わりません。

次に何をするか

「SQLを勉強する」を続けるのは、ここでいったん終わりでいいです。 次は、使う側に回ります。具体的に3つ書きます。

1. PostgreSQL で同じことをやる(半日)

前述の通りです。このシリーズで書いた一番長いSQLを PostgreSQL に貼って、直す。 書き換えが必要だった行を1枚のメモにする。 それが自分用の方言メモになります。

2. 自分の作ったアプリの、実際のSQLに EXPLAIN を打つ(1時間)

本線で作った ~/server のアプリには、もうSQLが何本か入っています。

bash
$ sudo grep -rn "SELECT" ~/server --include=*.js | head -20

出てきたSQLを、1本ずつ EXPLAIN にかけてください。 type: ALL が出るものが、いま全部読んでいるSQLです。行数が少ないうちは問題になりませんが、どれがそうなのかを知っているかどうかで、増えたときの対応が変わります。

3. 練習用ではなく、本物のデータで試す(1〜2時間)

行数の多いCSVを1つ持ってきて、LOAD DATA か INSERT で入れて、集計してみてください。 自治体の公開データでも、自分のサービスのログでも構いません。

練習用テーブルと決定的に違うのは、本物のデータには必ず「想定外」が入っていることです。空文字と NULL が混ざる。全角と半角が混ざる。日付の形式が途中で変わる。 文字コードの回と日付と時刻の回でやったことが、まとめて出てきます。そこで初めて、このシリーズが道具になります。

後片づけ

このシリーズを通して、練習用のテーブルを大量に作りました。 全部 d_ で始めてあります。ここでまとめて消します。

まず、何があるか数える

SHOW TABLES LIKE 'd\_%';

_ の前のバックスラッシュを忘れないでください。 LIKE の中では _ が「任意の1文字」を意味する記号なので、'd_%' と書くと data も demo も引っかかります。 d\_% と書いて初めて「文字としてのアンダースコア」になります。

+---------------------------+
| Tables_in_appdb (d\_%)    |
+---------------------------+
| d_at                      |
| d_cte_emp                 |
| d_dept                    |
| d_emp                     |
...

件数も見ておきます。

SELECT COUNT(*) AS 消す数
FROM information_schema.tables
WHERE table_schema = 'appdb' AND table_name LIKE 'd\_%';

消さないテーブルが混ざっていないことを、必ず目で確かめてください。

SHOW TABLES;

tasks など、本線のアプリが使っているテーブルが d_ で始まっていないことを確認します。

消す文を、自分で書かずに作らせる

30本以上あります。手で書くと必ず打ち間違えます。 MySQLに書かせます。

SELECT CONCAT('DROP TABLE IF EXISTS ', table_name, ';') AS stmt
FROM information_schema.tables
WHERE table_schema = 'appdb' AND table_name LIKE 'd\_%';

DROP TABLE IF EXISTS d_at; のような行がずらっと出ます。 これを目で読んでから、コピーして貼ります。

ただし、そのまま貼ると失敗するものがあります。

ERROR 3730 (HY000): Cannot drop table 'd_rel_dept' referenced by a foreign key constraint

制約の回で張った外部キーが残っているからです。参照されている側は、参照している側より先に消せません。

順番を考えるより、一時的に外します。

SET FOREIGN_KEY_CHECKS = 0;

そして DROP TABLE を全部流し、必ず戻します。

SET FOREIGN_KEY_CHECKS = 1;

SET FOREIGN_KEY_CHECKS = 0; は、いま繋いでいるセッションの中だけで効きます。 exit すれば自然に戻ります。 それでも、戻す1行を必ず打ってください。 同じセッションで続けて別の作業をしたとき、外部キーが効かないまま INSERT が通ります。

消えたことを確かめる

SHOW TABLES LIKE 'd\_%';
Empty set (0.00 sec)

そして、本線のテーブルが無事なことを確かめます。

SHOW TABLES;
SELECT COUNT(*) FROM tasks;

DROP DATABASE appdb; は打たないでください。 本線のアプリのデータが全部消えます。 消すのは d_ で始まる表だけです。

この記事のテーブルだけを消したい場合は、これです。

DROP TABLE IF EXISTS d_rel_emp;
DROP TABLE IF EXISTS d_rel_dept;
DROP TABLE IF EXISTS d_rel_flat;
DROP TABLE IF EXISTS d_rel_row;
DROP TABLE IF EXISTS d_rel_dup;
DROP TABLE IF EXISTS d_set_a;
DROP TABLE IF EXISTS d_set_b;

d_rel_emp を先に書いてあります。 外部キーで d_rel_dept を参照している側だからです。

まとめ

  • リレーショナルモデルはソフトではなく考え方。 1970年に提案され、MySQLもPostgreSQLも、あとからそれを実装したもの
  • 「リレーション(関係)」はテーブル同士の関連ではない。 1枚のテーブル、つまり行の集合そのものが関係
  • 理論上の関係には、行の順序も、列の順序も、重複行も無い。 SQLのテーブルは3つとも守っていない
  • 同じ行は3つ入る。 だから DISTINCT という機能が要る。DISTINCT の存在が「テーブルは集合ではない」証拠
  • 主キーと UNIQUE は、テーブルを理論上の「関係」に近づける道具でもある
  • SELECT * と SELECT 列名 で、返る順が変わる。 どの索引を読んだかがそのまま外に出るため。順序は意味ではなく事情
  • ORDER BY を書けというのは作法ではない。 順序という意味が最初から無いので、欲しいなら足すしかない
  • UNION は集合の足し算(重複を消す)、UNION ALL は多重集合のまま。 EXPLAIN に UNION RESULT の行が出るかどうかで見分ける
  • 重複が出ないと分かっているなら UNION ALL。 「とりあえず UNION」は毎回「一時テーブルで重複を突き合わせる仕事」を1回買っている
  • INTERSECT / EXCEPT は MySQL 8.0.31 以降。 古いと ERROR 1064 が出る。新しい構文の前に SELECT VERSION();
  • 古い環境では EXISTS / NOT EXISTS に書き換える。 NOT IN は使わない(NULL が1つで0行になる)
  • EXCEPT には向きがある。 UNION と INTERSECT には無い
  • 正規化は「異常を防ぐテクニック」ではなく、「1つの関係に1種類の事実だけを置いた結果」。 迷ったときの問いは「この表は何種類の事実を書いているか」
  • SQLは宣言的。 「何が欲しいか」だけを書き、「どう取るか」はオプティマイザが決める。だからデータが増えてもSQLを書き直さなくていい
  • 代償は「なぜ遅いかが自分の書いた行に無い」こと。 だから EXPLAIN を読む必要がある
  • 「関係」を捨てると、形の自由と横への広げやすさが手に入り、「事実は1箇所」と宣言的な問い合わせを失う。 仕事は消えず、アプリ側に移るだけ
  • SQLには標準があるが、完全実装した製品は無い。 SHOW TABLES / AUTO_INCREMENT / バッククォートは方言。移すときはそこを探す
  • 確かめ方の型は最後まで同じ:打つ → 件数を数える → 説明できない数字が出たら止まる

このシリーズは、本線の MySQLとSQLの基本の回で SELECT * FROM tasks; を打って、表が出たところから始まりました。 あのとき私は、その表が何なのか説明できませんでした。16本かけてやったのは、新しい構文を覚えることではなく、あの1行の下に何があるのかを掘ることでした。 いま言えるのは、あそこに出ていたのは表ではなく、「行の集合」という56年前の考え方を、MySQLというソフトが自分なりに実装したものだった、ということです。ズレも含めて、それがSQLでした。SQL深掘りシリーズは、ここまでです。 ここから先は、読む記事ではなく、自分のデータで打つSQLが続きを教えてくれます。本線に戻るときは、はじめにから順路をたどってください。

参考リンク


この段階の確認

【深掘り】SQL は、ここで終わりです。 次に進む前に、答えを見ずに試してください。

読み終えた直後は、たいてい「分かった気」になっています。 手が動くかどうかは別です。

説明できますか

  1. WHERE で別名が使えないのはなぜか。SELECTの実行順序を言えますか
  2. インデックスを張ったのに使われないのはどんなときか
  3. 同じ SELECT が2回目で変わるのはなぜか

手が動きますか

  • 遅いクエリを特定し、書き換えて速くなったことを測る
  • JOIN を3種類使い分け、行が減る・増える理由を説明しながら書く
  • トランザクションの分離レベルを変え、見え方が変わることを確かめる

1つでも詰まったら、飛ばした回があります。 そこへ戻ってください。戻るのは後退ではありません。 戻り先の目安: SELECTの実行順序の回 / インデックスとEXPLAINの回 / 分離レベルの回

全部答えられたなら、次の段階へ進んでください。


次に読む

この記事は 【深掘り】SQL の 17 / 17 本目です。

記事
← 前の記事GRANTと最小権限 — rootで繋ぐと全部持っていかれる
→ 次の記事コンテナとVMの違いを測る — 軽いのはOSを起動しないから

上から順に読めば積み上がるように並べています。 飛ばしても構いませんが、詰まったら前の回に戻ってください。

全体の順路は はじめに にあります。

roadmap.sh のどこをやっているか

Backend›SQL — Relational Model
  • 関係とは何か / 理論とSQLのズレ
  • 集合演算(UNION / INTERSECT / EXCEPT)

この記事は、上の項目を実際に手を動かして確かめる回です。

よかったらシェアしてね!
  • URLをコピーしました!
  • URLをコピーしました!

コメント

コメントする

目次