行には順番がない — 並びは、頼んだときだけ保証される — データの入口

リクエストがアプリケーションに届くまで

ブラウザURL入力
DNS名前→IP
ルータ経路選択
ロードバランサ振り分け
Nginx受け口
アプリ処理
DB保存今ここ

このコースは『データベースの仕組み』の前に読む入口です。初回は表・行・列と基本の SQL 文を導入し、行の並びを題材に「宣言した分だけ守られる」を見ます。

まず結論

ORDER BY を付けない SELECT は、行を決まった順では返しません。PostgreSQL 18 で3行の表の1行を更新しただけで、同じ問い合わせの並びが 1,2,3 から 2,3,1 に変わりました。

並びが保証されるのは ORDER BY で宣言したときだけで、それも宣言した列の分だけです。データベースが守るのは宣言されたことだけで、宣言されなかったことは自由に決められる。これがこのコースを通す芯です。

? 読む前に予測

PostgreSQL 18 で id が 1,2,3 の3行を入れ、ORDER BY なしの SELECT で 1,2,3 と返ることを確かめました。次に id=1 の行の name だけを UPDATE し、同じ SELECT をもう一度流します。並びはどうなったでしょうか。

当てることが目的ではありません。外した予測ほど記憶に残るので、直感で選んでください。

抽象原理

表・行・列と、「宣言」という見方

このコース『データの入口』は、次に読むコース『データベースの仕組み』の前に置く入口です。あちらが前提にする語をここで順に導入します。

リレーショナルデータベースはデータを表(テーブル)に入れます。表は列(id や name のような名前付きの項目)を決めて作り、データは1件ずつ行として入ります。表を作るのは CREATE TABLE、行を入れるのは INSERT、読むのは SELECT、値を書き換えるのは UPDATE。SELECT に ORDER BY 列名 を付けると、その列の値で並べた結果を頼めます。

このコースの芯は1つです。データベースが守るのは宣言されたことだけで、宣言されなかったことは自由に決められる。 初回は「行の並び」で確かめます。

リレーショナルモデルを提案した Codd の1970年の論文は、関係(relation)を n 個の値の組の集合(a set of n-tuples)として定義し、それを配列として描いたときの性質をこう並べます。

(2) The ordering of rows is immaterial. (3) All rows are distinct. (4) The ordering of columns is significant

行の順序は重要でない。列の順序には意味がある。行と列で扱いが違います。

今の製品の文書も同じ区別をしています。PostgreSQL 18 の文書は The number and order of the columns is fixed と書いた同じ段落で、こう続けます。

SQL does not make any guarantees about the order of the rows in a table. When a table is read, the rows will appear in an unspecified order, unless sorting is explicitly requested.

主語は PostgreSQL ではなく SQL です。行の並びは言語の約束の外にあり、並べ替えを明示的に頼んだときだけ約束の内に入ります。

同じ表から読んでも、宣言したかどうかで約束が変わる

表 fruits列 id, name の数と順序は固定

SELECT で読む

ORDER BY なし並びは未指定。その時の読み方で決まる

ORDER BY idid の順が約束される

・どちらも同じ行の集まりを返す。違うのは並びを約束したかどうかだけ
・ORDER BY でも、id の値が同じ行どうしの並びは約束されない

具体実物

1行更新しただけで、並びが変わる

PostgreSQL 18.6(Docker 上、設定は既定のまま)で、3行の表を作って確かめました。流した SQL は次の7文です。

e1-pg.sql設定ファイル

DROP TABLE IF EXISTS fruits;
CREATE TABLE fruits (id integer, name text);
INSERT INTO fruits VALUES (1, 'apple'), (2, 'banana'), (3, 'cherry');
SELECT id, name FROM fruits;
UPDATE fruits SET name = 'APPLE' WHERE id = 1;
SELECT id, name FROM fruits;
SELECT id, name FROM fruits ORDER BY id;

psql -e の出力(1回目。生出力のまま)コマンド出力

### PG run 1
DROP TABLE IF EXISTS fruits;
NOTICE:  table "fruits" does not exist, skipping
DROP TABLE
CREATE TABLE fruits (id integer, name text);
CREATE TABLE
INSERT INTO fruits VALUES (1, 'apple'), (2, 'banana'), (3, 'cherry');
INSERT 0 3
SELECT id, name FROM fruits;
id |  name  
----+--------
1 | apple
2 | banana
3 | cherry
(3 rows)
 
UPDATE fruits SET name = 'APPLE' WHERE id = 1;
UPDATE 1
SELECT id, name FROM fruits;
id |  name  
----+--------
2 | banana
3 | cherry
1 | APPLE
(3 rows)
 
SELECT id, name FROM fruits ORDER BY id;
id |  name  
----+--------
1 | APPLE
2 | banana
3 | cherry
(3 rows)

最初の SELECT は入れた順の 1,2,3。id=1 の name を書き換えただけの2回目は 2,3,1 で、更新した行が末尾に来ました。2回流して2回とも同じです。ORDER BY id を付けた3回目は 1,2,3 に戻ります。

2,3,1 は、新しい表・3行・既定設定での結果です。「更新すると必ず末尾に行く」とは読まないでください。見るべきは並びが変わりうることと、最初の 1,2,3 も約束ではなかったことです。なぜ末尾に来たのかは説明しません。UPDATE のとき表の中で何が起きるかは、『データベースの仕組み』で扱います。

✓ ここまでの確認

ORDER BY なしの SELECT で、入れた順どおり 1,2,3 と返ってきました。この結果から言えることはどれですか。

抽象原理

並びは、宣言した列の分だけ決まる

PostgreSQL 18 の ORDER BY の節は、並べ替えを頼まなかった場合をこう書きます。

If sorting is not chosen, the rows will be returned in an unspecified order. The actual order in that case will depend on the scan and join plan types and the order on disk, but it must not be relied on. A particular output ordering can only be guaranteed if the sort step is explicitly chosen.

並びを決めているもの(表の読み方、ディスク上の並び)は確かにあります。ただ、それは約束の外です。表の読み方が変わると何が変わるかは次回の主題です。

では ORDER BY を書けば並びは全部決まるのか。MySQL 8.4 の文書がその答えを書いています。

If multiple rows have identical values in the ORDER BY columns, the server is free to return those rows in any order, and may do so differently depending on the overall execution plan.

ORDER BY に書いた列で差がつかない行どうしの並びは、サーバが自由に決めてよい。PostgreSQL 18 の SELECT 文の文書も同じことを書いています。

If they are equal according to all specified expressions, they are returned in an implementation-dependent order.

2つの製品の文書がそろって、約束されるのは宣言した列の分だけだと書いています。同じ時刻の行が2つありうる列だけで並べたなら、その2行の順は宣言されていません。

これがコースの芯の最初の形です。並びも、一意であることも、「全部か無しか」も、宣言した分だけが守られます。残りの回で1つずつ確かめます。

具体実物

MySQL では変わらなかった

MySQL 8.4.11(InnoDB、設定は既定のまま)で同じ手順を2回流すと、UPDATE の後も 1,2,3 のままでした。さらに主キー付きの表 fruits2 に 3,1,2 の順で入れると、最初の SELECT から 1,2,3 で返りました。入れた順ですらありません。

mysql -t -v の出力(1回目の29〜62行目だけを抜粋。前後は省略)コマンド出力

--------------
SELECT id, name FROM fruits
--------------
 
+------+--------+
| id   | name   |
+------+--------+
|    1 | APPLE  |
|    2 | banana |
|    3 | cherry |
+------+--------+
--------------
DROP TABLE IF EXISTS fruits2
--------------
 
--------------
CREATE TABLE fruits2 (id integer PRIMARY KEY, name text)
--------------
 
--------------
INSERT INTO fruits2 VALUES (3, 'cherry'), (1, 'apple'), (2, 'banana')
--------------
 
--------------
SELECT id, name FROM fruits2
--------------
 
+----+--------+
| id | name   |
+----+--------+
|  1 | apple  |
|  2 | banana |
|  3 | cherry |
+----+--------+

変わらなかったことは、保証されていることを意味しません。MySQL の文書で「ORDER BY なしの並びは保証しない」と直接書く文は、確認した範囲では見つけていません。それでも、この SELECT は並びを宣言していません。2つの製品で観測が違ったことが、並びが約束の外にあることの実例です。

✓ ここまでの確認

MySQL 8.4 で同じ手順を試すと、UPDATE の後も 1,2,3 のままでした。「MySQL なら ORDER BY を省いてよい」という判断は妥当ですか。

⇄ 2つを見比べる

具体例 A

表の行(SQL)

PostgreSQL 18 の文書は、SQL は表の行の順序を保証しないと書きます。実際に1行の UPDATE で 1,2,3 が 2,3,1 に変わりました。並びが約束されるのは ORDER BY を書いたときだけです。

具体例 B

ある名前のDNSレコード

DNS では1つの名前に複数のレコード(例えば複数のアドレス)を登録できます。RFC 1034 の3.6節は、その集まりの順序は重要でなく、ネームサーバやリゾルバなど DNS のどの部分も順序を保たなくてよいと書きます。別コース『DNSを運用する側から見る』では、dig で見る並びが問い合わせごとに揺れることを実測しています。

データベースと名前解決。まったく別の仕組みですが、並びについて共通していることは何でしょうか。

「並びを約束しているのは誰か」を両方で探してみてください。

実務の場面エージェントが書いたテストが、SELECT の結果を順番どおりに比べている

生成されたテストが、SELECT id, name FROM fruits の結果を [1, 2, 3] の順と一致するか比べていたとします。手元では通ります。

このテストは、SQL が約束していないことを検査しています。今通るのは、今の並びがたまたまそうだからです。上の実験では、1行の UPDATE だけで 2,3,1 に変わりました。テストの前に誰かが行を更新すれば、コードに誤りが無くても落ちます。

直し方は、何を確かめたいかで2通りです。並びが仕様なら、クエリに ORDER BY を書く。 並びが仕様でないなら、並べ替えてから比べるか、集合として比べる。「最新の10件」を返すはずのクエリに LIMIT 10 だけがあって ORDER BY が無い場合も同じ型です。どの10件が返るかは宣言されていません。

この章のまとめ

表は列を決めて作り、データは行として入る。列の数と順序は固定、行の順序は SQL が保証しない(PostgreSQL 18 の文書)。
Codd 1970 は配列表現の性質として「行の順序は重要でない」「列の順序には意味がある」と並べた。列にも順序が無いと言ったのではない。
PostgreSQL 18 で1行を UPDATE すると、ORDER BY なしの並びが 1,2,3 から 2,3,1 に変わった(2回とも)。MySQL 8.4 では変わらなかったが、観測は保証ではない。
ORDER BY を書いても、宣言した列の値が同じ行どうしの並びは決まらない(PostgreSQL 18 と MySQL 8.4 の文書)。
DNS でも、ある名前のレコードの集まりに順序は無い(RFC 1034 §3.6)。
データベースが守るのは宣言されたことだけ。次回は、索引を足しても答えの集合が変わらず、並びだけが変わりうることを見ます。

自分の現場で確かめる

読んだだけでは身につきません。手元の環境で実際に見てみてください。

自分が関わるコードで ORDER BY の無い SELECT を探し、結果の先頭だけを使う・テストで並びを比べるなど、並びに頼っている箇所が無いか確かめる
ORDER BY がある箇所では、指定した列の値が同じ行が複数ありうるかを考える。ありうるなら、その行どうしの並びは宣言されていない

理解度チェック

読んだ直後は「分かった」と感じやすいものです。実際に解いて確かめてください。

問題 1 / 3正解: 0

Codd の1970年の論文は、関係を配列として描いたときの性質として、行と列の順序について何と書いていますか。

用語集(4語)

表(テーブル): リレーショナルデータベースがデータを入れる入れ物。作るときに列を決め、データは行として入る。CREATE TABLE で作る。
行と列: 列は名前付きの項目(id, name など)で、数と順序は表を作るときに固定される。行は1件分のデータ。SQL は行の順序を保証しない。
SELECT / INSERT / UPDATE: SQL の基本の文。SELECT は行を読む、INSERT は行を入れる、UPDATE は既存の行の値を書き換える。
ORDER BY: SELECT の結果を指定した列の値で並べ替えるよう頼む句。並びが保証されるのは、これを書いたとき、指定した列の分だけ。