データストアの選び方 — 速いのはJOINをしていないから

データストアの選び方。表のカードと文書の束、鍵付きのデータの箱。

速いデータストアは、たいていJOINをしていません。選ぶ軸を決めて、用途で並べます。

前回: レプリケーションとシャーディング — 書き込みは速くならない

これは【深掘り】の記事です。本線の順路ではありません。 読まなくても先へは進めます。 本線のSQLiteはおもちゃではない — 用途で選ぶデータベースの回の続きにあたります。あの回では「1台で足りるか」「同時に何人が書くか」で製品を選びました。この記事はその手前の話です。そもそもデータを「どういう形で持つか」に、どんな選択肢があって、それぞれ何が得意で何が苦手なのかを、同じデータを4つの形に入れて実測します。

この記事のコマンドは、すべてVMの中(Ubuntu Server)で打ちます。 Windows の PowerShell では打ちません。

前提: VMに Docker と Docker Compose が入っていること(Composeなら1コマンド — 複数コンテナをまとめるの回でやりました)。docker compose version が返ることを先に確かめてください。 メモリは 4GB 以上を割り当てておいてください。 MySQL・Redis・MongoDB を同時に立てます。 端末を2つ開きます。 片方でコンテナを動かし、もう片方で時間を測ります。

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

目次

「NoSQLは速い」で移して、3ヶ月後に自分でJOINを書いていた

個人開発で作っていた小さな投稿サイトが、少しずつ重くなってきたことがありました。

調べていると、こういう記事がいくらでも出てきます。

「リレーショナルデータベースはJOINが遅い。NoSQLなら速い」

なるほど、と思いました。MySQL をやめて MongoDB に移しました。

移した直後は、たしかに速くなりました。投稿1件を表示するのに、それまで3回SQLを投げていたものが、1回で全部取れるようになったからです。著者の名前も、タグの一覧も、投稿のドキュメントの中に全部入れました。

問題が出たのは3ヶ月後です。

ある利用者が、表示名を変えました。それだけのことです。

その人が過去に書いた420件の投稿に、古い名前が残りました。

投稿のドキュメントの中に、著者の名前をコピーして入れていたからです。名前を1箇所直しても、420箇所は直りません。

直すためのコードを書きました。それから「タグごとの投稿数を多い順に出したい」と言われて、集計のコードを書きました。そのころには、アプリのコードの中に、自分で書いたJOINと集計が何十行も溜まっていました。

そこで、やっと気づきました。

MongoDB が速かったのは、JOINをしていなかったからです。 そしてJOINは消えたのではなく、私が手で書く側に移っただけでした。

もっと悪いことに、移す順番が逆でした。 私がやったのは、

  1. 「MongoDB が速いらしい」→ 移した
  2. そのあとで「どういう問い合わせをするか」を考えた

正しい順番は逆です。

先に「どう問い合わせるか」を決めてから、持ち方を決めます。 持ち方を先に決めると、あとから来た問いに、全部アプリ側のコードで対応することになります。

この記事でやるのは、同じデータを4つの形で持ってみて、同じ問いを投げることです。速い遅いではなく、「この問いは、この形だと素直に書ける/この形だとつらい」 を、自分の手で確かめます。

この記事でわかること

  • データの持ち方は「速さ」で選ぶものではないこと。どういう問いを投げるかが先で、持ち方はその結果だということ
  • キーバリューは「キーが分かっているものを取る」以外、ほぼ全部つらいこと。それでもセッション・カウンタ・キャッシュ・レート制限では他に代えがないこと
  • ドキュメント型が1回で取れるのは、同じ値を複数箇所にコピーしているからだということ。そのコピーの数だけ、更新の手間が増えること
  • リレーショナルの最大の強みは速さではなく、「あとから問いが変わっても対応できる」ことだということ
  • 列指向が効くのは「たくさんの行の、少ない列」を読む問いだけで、1行の更新にはむしろ不利なこと
  • 「フォローのフォローのフォロー」のような問いは、段数を1つ増やすだけで急に遅くなること。それがグラフDBが要る場面であり、個人サイトではまず出てこないこと
  • 製品を混ぜると、バックアップ・監視・更新が製品の数だけ増えること。迷ったら1本で始めてよいこと

この記事でやること

  1. ~/storelab を作り、MySQL・Redis・MongoDB を Docker Compose で同時に立てる
  2. 利用者・投稿・タグ・フォローという同じ題材を、キーバリュー / ドキュメント / リレーショナルの3つの形で実際に入れる
  3. 同じ問いを3つの形に投げて、取れるもの・取れないもの・何回問い合わせが必要かを数える
  4. 1000万行のテーブルを作り、「1列だけ合計する」問いで行単位に読む形の不利を EXPLAIN と実行時間で見せ、列指向が何をしているかを被覆索引で再現する
  5. フォローを3段たどる問いを再帰CTEで書き、深さを増やすと急に遅くなるところを実測する
  6. 形 × 得意な問い × 苦手な問いの判断表を1枚にまとめ、立てたものを全部消す

先に知る言葉

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

言葉意味
キーバリュー(KVS)鍵と値だけを持つ形。 user:1 という鍵を渡すと値が返る。鍵が分からないと何も取れないのが最大の性質。Redis がこの形
ドキュメント型JSONのかたまりを1件として持つ形。 入れ子の配列やオブジェクトをそのまま入れられる。MongoDB がこの形
リレーショナル表と表の関係でデータを持つ形。 重複を排して(正規化)、必要なときに JOIN で繋ぐ。MySQL や PostgreSQL がこの形
列指向(カラムナ)同じ列の値を、まとめて並べて保存する形。 「1000万行のうち1列だけ合計する」ような問いに強い。分析用のデータベースが使う
行指向(ロウストア)1行分をひとかたまりで保存する形。 普通のリレーショナルデータベースはこちら。1行を丸ごと読み書きするのが得意
正規化同じ値を1箇所にしか置かないように表を分けること。 名前を直すのが1箇所で済むようになる
非正規化わざと同じ値を複数箇所に置くこと。 読むのは速くなるが、更新が全箇所に必要になる
被覆索引(covering index)問いに必要な列が、索引の中だけで揃っている状態。 本体の行を読まずに済む。この記事では列指向の縮小模型として使う
再帰CTESQLで「自分自身を参照する」書き方。 階層やグラフをたどるときに使う。WITH RECURSIVE で書く
ポリグロット・パーシステンス用途ごとに違うデータベースを使い分けること。 良さそうに聞こえるが、運用の手間が製品の数だけ増える

① 3つのデータベースを1コマンドで立てる

作業用のディレクトリを作ります。VMの中で打ちます。

bash
$ mkdir -p ~/storelab
$ cd ~/storelab

docker-compose.yml を作ります。

bash
$ nano docker-compose.yml

中身はこれです。

services:
  mysql:
    image: mysql:8.0
    environment:
      MYSQL_ROOT_PASSWORD: labpass
      MYSQL_DATABASE: storelab
    ports:
      - "13306:3306"
    command: --local-infile=1

  redis:
    image: redis:7-alpine
    ports:
      - "16379:6379"

  mongo:
    image: mongo:7
    ports:
      - "17017:27017"

ポートを 13306 / 16379 / 17017 にずらしているのは、VMで既に別の MySQL や Redis が動いていても衝突しないようにするためです。 なお mysql:8.0 は 2026年4月30日でサポートが終了した版です。練習なのでこのまま使いますが、新しく作るなら 8.4 LTS を選んでください。

立てます。

bash
$ docker compose up -d

初回はイメージのダウンロードで数分かかります。終わったら状態を見ます。

bash
$ docker compose ps

3つとも running になっていれば成功です。

MySQL は起動に少し時間がかかります。 中で本当に受け付けているかを確かめます。

bash
$ docker compose exec mysql mysqladmin ping -uroot -plabpass

mysqld is alive と返れば準備完了です。Can't connect が出るときは、まだ初期化中です。20秒ほど待って打ち直してください。

Redis と MongoDB も確かめます。

bash
$ docker compose exec redis redis-cli PING
$ docker compose exec mongo mongosh --quiet --eval 'db.runCommand({ping:1})'

PONG と { ok: 1 } が返れば3つとも生きています。

📸 スクリーンショット: docker compose ps の出力(3つとも running になっている画面)。この記事は最後に全部消すので、「立った証拠」は今のうちに残しておきます。

題材を決める

これから4つの形で持つデータは、全部同じものです。

もの中身
利用者(users)id、表示名、自己紹介
投稿(posts)id、著者、タイトル、本文、投稿日時
タグ(tags)投稿に付く複数のラベル(linux、docker など)
フォロー(follows)誰が誰をフォローしているか

そして、投げる問いを先に5つ決めます。ここが一番大事です。

#問い
Q1利用者IDが分かっているとき、その人のプロフィールを取る
Q2投稿1件を、著者名とタグ一覧つきで表示する
Q3タグ linux が付いた投稿を、新しい順に20件
Q4タグごとの投稿数を、多い順に
Q5Aがフォローしている人がフォローしている人… を3段たどる

この5つを先に書いたことが、この記事の全部です。 持ち方を先に決めてから問いを考えると、必ずどこかで詰まります。

② キーバリューで持つ(Redis)

まず一番単純な形です。鍵と値だけで持ちます。

bash
$ docker compose exec redis redis-cli

プロンプトが 127.0.0.1:6379> に変わります。ここから先は Redis の中です。

Q1: 鍵が分かっているものを取る

SET user:1:name "sato"
GET user:1:name

"sato" が返ります。これがキーバリューの全部です。

もう少し実用的に、まとまった値はハッシュで持ちます。

HSET user:1 name "sato" bio "Linux勉強中" joined "2026-04-01"
HSET user:2 name "tanaka" bio "フロント担当" joined "2026-05-12"
HSET user:3 name "yamada" bio "インフラ見習い" joined "2026-06-20"
HGETALL user:1
HGET user:1 name

Q1は1発です。 しかも極めて速い。鍵が完全に分かっているからです。

Q3を投げてみる(ここでつまずく)

「タグ linux が付いた投稿を新しい順に20件」を、この形でやってみます。

投稿を入れます。

HSET post:100 title "aptの話" author 1 tags "linux,apt" created "2026-09-01"
HSET post:101 title "Composeの話" author 2 tags "docker" created "2026-09-02"
HSET post:102 title "権限の話" author 1 tags "linux,permission" created "2026-09-03"

さて、linux が付いた投稿を探します。探せません。

Redis には「値の中身で探す」という機能がありません。 鍵を渡す以外の入口が無いのです。

やるとしたら、探すための鍵を自分で作るしかありません。

SADD tag:linux 100 102
SADD tag:docker 101
SMEMBERS tag:linux

100 と 102 が返ります。取れました。ただし「自分で索引を手で作った」だけです。

さらに「新しい順に20件」が要るので、順序も自分で持ちます。

ZADD tag:linux:recent 20260901 100 20260903 102
ZREVRANGE tag:linux:recent 0 19

102、100 の順で返ります。

ここで気づいてほしいのは、こういうことです。

投稿を1件足すたびに、post:*・tag:*・tag:*:recent を全部自分で更新しないといけません。 1箇所忘れると、その投稿は永久に見つかりません。しかもエラーは出ません。

リレーショナルなら INSERT 1回で、索引はデータベースが勝手に更新します。その仕事が、アプリ側に移っただけです。

KEYS * を打ってはいけない

ここで多くの人がやる回避策があります。「全部の鍵を見て、絞ればいい」です。

KEYS user:*

いま3件しか無いので一瞬で返ります。そして、これが罠です。

KEYS は、全部の鍵を1つずつ見終わるまで、Redis 全体を止めます。

Redis は基本的に1つの処理を1つずつ順番にこなす作りです。速いのは、1件1件が一瞬で終わるからです。KEYS はその原則を破る代表的なコマンドで(巨大なセットやハッシュに対する SMEMBERS / HGETALL なども同じ種類です)、鍵が100万件あれば、その走査が終わるまで他の全部の要求が待たされます。

セッション置き場に使っていたら、その間サイト全体がログインできなくなります。 手元で試して速かったから本番でも大丈夫、が通用しない代表例です。

代わりに使うのが SCAN です。少しずつ区切って返します。

SCAN 0 MATCH user:* COUNT 10

返ってくるのは 1) "0" のような次の位置と、見つかった鍵の一覧です。次の位置が 0 になるまで繰り返します。全体は止まりません。

ただし SCAN も、「全部見る」ことに変わりはありません。 件数が増えれば時間はかかります。探す用途に向いていないのは同じです。

キーバリューが本当に強い場所

つらいところばかり見せましたが、代えが効かない用途があります。 4つやってみます。

1. カウンタ

INCR post:100:views
INCR post:100:views
INCR post:100:views
GET post:100:views

"3" が返ります。MySQL で UPDATE posts SET views = views + 1 をやると、同じ行に書き込みが集中して詰まります。 Redis なら平気です。

2. 有効期限つきのセッション

SET session:abc123 "user:1" EX 60
TTL session:abc123

残り秒数が返ります。60秒後に勝手に消えます。

「期限が来たら消える」は、リレーショナルには無い機能です。 MySQL でやるなら、期限切れを削除する定期実行を自分で書くことになります。

10秒待ってもう一度 TTL を打つと減っています。期限が切れると -2(存在しない)が返ります。

3. レート制限

「同じ相手からの要求を1分に5回まで」を作ります。

INCR rate:203.0.113.9
EXPIRE rate:203.0.113.9 60
GET rate:203.0.113.9

INCR して、返り値が5を超えていたら断る、それだけです。1分経てば鍵ごと消えるので、掃除も要りません。

4. キャッシュ

重い問い合わせの結果を、そのまま置いておく用途です。これは本線のキャッシュは消すのが一番難しい — Redisの導入の回でやりました。

Redis から出ます。

exit

ここまでのまとめ

問いRedis で感想
Q1(鍵が分かっている)HGETALL user:1最強。他に勝てるものが無い
Q2(投稿+著者+タグ)鍵を3回引くできるがアプリ側でJOINしている
Q3(タグで絞って新しい順)索引用の鍵を自分で作る更新漏れが必ず起きる
Q4(タグごとの集計)全部の鍵を舐める実質できない
Q5(3段たどる)1段ごとに問い合わせる回数が爆発する

キーバリューは「保存場所」であって、「問い合わせる仕組み」ではありません。

③ ドキュメントで持つ(MongoDB)

次はJSONのかたまりとして持ちます。

bash
$ docker compose exec mongo mongosh --quiet storelab

プロンプトが storelab> になります。ここから先は MongoDB の中です。

入れ子をそのまま入れる

db.posts.insertMany([
  { _id: 100, title: "aptの話", body: "パッケージ管理の話",
    author: { id: 1, name: "sato" },
    tags: ["linux", "apt"], created: new Date("2026-09-01") },
  { _id: 101, title: "Composeの話", body: "複数コンテナの話",
    author: { id: 2, name: "tanaka" },
    tags: ["docker"], created: new Date("2026-09-02") },
  { _id: 102, title: "権限の話", body: "rwxの話",
    author: { id: 1, name: "sato" },
    tags: ["linux", "permission"], created: new Date("2026-09-03") }
])

author がオブジェクトのまま、tags が配列のまま入りました。 表に分ける必要がありません。

Q2: 投稿1件を著者名とタグつきで

db.posts.findOne({ _id: 100 })

タイトルも、著者名も、タグの一覧も、1回で全部返りました。

問い合わせ回数: 1回。JOINは0回。

これが「ドキュメント型は速い」と言われるものの正体です。必要なものが最初から1箇所にまとまっているからです。

Q3: タグで絞って新しい順

db.posts.find({ tags: "linux" }).sort({ created: -1 }).limit(20)

配列の中の値を、そのまま条件に書けます。 これは素直です。

索引も張れます。

db.posts.createIndex({ tags: 1, created: -1 })
db.posts.find({ tags: "linux" }).sort({ created: -1 }).explain("executionStats").executionStats

totalDocsExamined が、絞り込んだ件数と同じくらいになっていれば索引が効いています。索引の考え方は、リレーショナルと同じです。 これは索引を張ったのに使われない — SQL深掘りの回でやったことがそのまま通用します。

同じ値を複数箇所に持った代償

さて、冒頭の失敗を再現します。利用者1が表示名を変えます。

db.posts.find({ "author.id": 1 }, { title: 1, "author.name": 1 })

2件出ます。ここで名前を変えたいのですが、「利用者」という置き場所がありません。 名前は投稿のドキュメントの中に埋まっています。

db.posts.updateMany({ "author.id": 1 }, { $set: { "author.name": "sato-new" } })

modifiedCount: 2 が返ります。2件なら平気です。420件でも、まだ何とかなります。

問題は、それを忘れないでいられるかです。

「名前を変えたら投稿も全部更新する」は、コードに書いてあるだけの約束です。 データベースは、それを守らせてくれません。 忘れた瞬間、データは静かに食い違います。エラーは出ません。

私が420件の古い名前に気づいたのは、利用者からの問い合わせが来たときでした。

Q4: タグごとの投稿数を多い順に

「あとから来た問い」をやってみます。

db.posts.aggregate([
  { $unwind: "$tags" },
  { $group: { _id: "$tags", n: { $sum: 1 } } },
  { $sort: { n: -1 } }
])

linux: 2、apt: 1、docker: 1、permission: 1 が返ります。できます。 ただし、

  • 配列をいったんバラす($unwind)という段が要る
  • 段(ステージ)を積む書き方を覚える必要がある

同じことを、あとで MySQL では GROUP BY の1行で書きます。「どちらが正しい」ではなく、「どちらが素直か」が問いによって変わるという話です。

Q5: 3段たどる

「フォローのフォローのフォロー」をやります。フォロー関係を入れます。

db.follows.insertMany([
  { follower: 1, followee: 2 }, { follower: 1, followee: 3 },
  { follower: 2, followee: 4 }, { follower: 3, followee: 5 },
  { follower: 4, followee: 6 }, { follower: 5, followee: 6 }
])

MongoDB には $graphLookup という、たどるための段があります。

db.follows.aggregate([
  { $match: { follower: 1 } },
  { $graphLookup: {
      from: "follows", startWith: "$followee",
      connectFromField: "followee", connectToField: "follower",
      as: "reach", maxDepth: 2 } },
  { $project: { followee: 1, n: { $size: "$reach" } } }
])

動きます。 ただし、maxDepth を上げると重くなる性質は、あとで MySQL の再帰CTEで見るものとまったく同じです。 形を変えても、この問いのつらさは消えません。

MongoDB から出ます。

exit

ここまでのまとめ

問いMongoDB で感想
Q1findOne({_id:1})速い
Q21回で全部取れるこれが最大の長所
Q3配列に索引を張れば素直良い
Q4$unwind + $groupできるが書き方を覚える必要がある
Q5$graphLookup深さを増やすと重い(形の問題ではない)
更新コピーした数だけ更新が要るここが代償

スキーマは消えていません。表の定義から、アプリのコードへ移っただけです。 このあたりは本線のスキーマは消えずにアプリへ移る — NoSQLとMongoDBの回でも扱いました。

④ リレーショナルで持つ(MySQL)

3つ目です。同じ値を1箇所にしか置かない形にします。

bash
$ docker compose exec mysql mysql -uroot -plabpass storelab

ここから先は MySQL の中です。 警告(Using a password on the command line...)は出ますが、練習用なので無視して構いません。

表を分ける

CREATE TABLE users (
  id   INT PRIMARY KEY,
  name VARCHAR(50) NOT NULL,
  bio  VARCHAR(200)
);

CREATE TABLE posts (
  id        INT PRIMARY KEY,
  author_id INT NOT NULL,
  title     VARCHAR(200) NOT NULL,
  created   DATETIME NOT NULL,
  CONSTRAINT fk_posts_author FOREIGN KEY (author_id) REFERENCES users(id)
);

CREATE TABLE post_tags (
  post_id INT NOT NULL,
  tag     VARCHAR(50) NOT NULL,
  PRIMARY KEY (post_id, tag),
  KEY idx_tag (tag),
  CONSTRAINT fk_pt_post FOREIGN KEY (post_id) REFERENCES posts(id)
);

データを入れます。

INSERT INTO users VALUES
 (1,'sato','Linux勉強中'), (2,'tanaka','フロント担当'), (3,'yamada','インフラ見習い');

INSERT INTO posts VALUES
 (100,1,'aptの話','2026-09-01 10:00:00'),
 (101,2,'Composeの話','2026-09-02 10:00:00'),
 (102,1,'権限の話','2026-09-03 10:00:00');

INSERT INTO post_tags VALUES
 (100,'linux'),(100,'apt'),(101,'docker'),(102,'linux'),(102,'permission');

著者の名前は users にしか無く、posts には author_id しか無いところが要点です。

Q2: 投稿1件を著者名とタグつきで

SELECT p.id, p.title, u.name AS author,
       GROUP_CONCAT(t.tag ORDER BY t.tag) AS tags
FROM posts p
JOIN users u     ON u.id = p.author_id
LEFT JOIN post_tags t ON t.post_id = p.id
WHERE p.id = 100
GROUP BY p.id, p.title, u.name;
+-----+-----------+--------+-----------+
| id  | title     | author | tags      |
+-----+-----------+--------+-----------+
| 100 | aptの話   | sato   | apt,linux |
+-----+-----------+--------+-----------+

問い合わせ回数は1回です。 MongoDB と同じ「1回」ですが、中身が違います。

MongoDBMySQL
アプリからの往復1回1回
内部でやっていること1件のドキュメントを読む3つの表を突き合わせる
著者名の置き場所投稿ごとにコピーusers に1箇所だけ

MongoDB のほうが速いのは当然です。突き合わせをしていないからです。

名前を変えてみる

UPDATE users SET name = 'sato-new' WHERE id = 1;

1行です。 過去の投稿が420件あっても、4200件あっても、1行です。

もう一度さっきの SELECT を打つと、author が sato-new に変わっています。投稿には一切触っていません。

これが正規化の見返りです。「速さ」ではなく「食い違いが起きないこと」を買っています。

Q4: あとから来た問い

「タグごとの投稿数を多い順に」を書きます。

SELECT tag, COUNT(*) AS n
FROM post_tags
GROUP BY tag
ORDER BY n DESC;

1行です。 表の作り方を変える必要も、新しい書き方を覚える必要もありません。

もう1つ、「今までまったく考えていなかった問い」を投げます。

SELECT u.name, COUNT(p.id) AS posts, MIN(p.created) AS first_post
FROM users u
LEFT JOIN posts p ON p.author_id = u.id
GROUP BY u.id, u.name
ORDER BY posts DESC;

投稿が0件の yamada も 0 として出ます。表の構造は一切変えていません。

リレーショナルの最大の強みはここです。 設計した時点で想定していなかった問いに、あとから対応できます。

ドキュメント型は、想定した問いには速いですが、想定していなかった問いには作り直しが要ります。

制約が守ってくれるもの

存在しない利用者の投稿を入れてみます。

INSERT INTO posts VALUES (103, 999, '幽霊の投稿', '2026-09-04 10:00:00');
ERROR 1452 (23000): Cannot add or update a child row: a foreign key constraint fails

弾かれました。 アプリ側に確認のコードを書き忘れていても、データベースが最後の砦になります。

この話はアプリで弾いたはずの値が入る — SQL深掘りの回で詳しくやりました。MongoDB や Redis には、この砦がありません。

⑤ 列指向の問い — 1000万行の1列だけを合計する

4つ目の形は列指向です。ただし、実際に立てるのは重いので立てません。

代わりに、列指向が効く問いを MySQL に投げて、行単位に読む形がどれだけ不利かを測ります。そのうえで、被覆索引を使って「列だけ読む」状態を再現します。

1000万行を作る

MySQL の中で打ちます。

CREATE TABLE events (
  id      BIGINT NOT NULL AUTO_INCREMENT PRIMARY KEY,
  user_id INT NOT NULL,
  amount  INT NOT NULL,
  kind    VARCHAR(20) NOT NULL,
  note    VARCHAR(200) NOT NULL,
  created DATETIME NOT NULL
) ENGINE=InnoDB;

note を 200 文字にしているのは、1行を太らせるためです。 ここが今回の肝になります。

種を1000行入れます。

INSERT INTO events (user_id, amount, kind, note, created)
SELECT
  FLOOR(RAND()*5000)+1,
  FLOOR(RAND()*1000),
  ELT(FLOOR(RAND()*3)+1,'view','like','post'),
  REPEAT('x', 180),
  NOW() - INTERVAL FLOOR(RAND()*365) DAY
FROM information_schema.columns LIMIT 1000;

次に、自分自身を足して倍にするのを繰り返します。この1行を、下のように何度も打ちます。

INSERT INTO events (user_id, amount, kind, note, created)
SELECT user_id, amount, kind, note, created FROM events;

1000 → 2000 → 4000 → … と倍になります。1000万に届くまで、およそ14回です。件数を確かめながら進めます。

SELECT COUNT(*) FROM events;

後半の1回は数十秒かかります。 VMのメモリが少ない場合や、ERROR 1206 (The total number of locks exceeds the lock table size) が出た場合は、300万行で止めて構いません。 比べる話なので、桁が同じなら結論は変わりません。

どれだけディスクを使っているかを見ます。

SELECT table_name,
       ROUND(data_length/1024/1024) AS data_mb,
       ROUND(index_length/1024/1024) AS index_mb
FROM information_schema.tables
WHERE table_schema='storelab' AND table_name='events';

1000万行なら 2GB を超えているはずです。note の180文字が、行の大半を占めています。

「1列だけ合計する」を測る

SET profiling = 1;
SELECT SUM(amount) FROM events;

私の環境(VM 4コア / メモリ4GB / 1000万行)ではこうなりました。

+-------------+
| SUM(amount) |
+-------------+
|  4995312080 |
+-------------+
1 row in set (11.42 sec)

11秒です。 何をしているのか見ます。

EXPLAIN SELECT SUM(amount) FROM events;
type: ALL
key: NULL
rows: 9873021
Extra: NULL
  • type: ALL — 全部の行を見ている
  • key: NULL — 索引を使っていない

ここが行指向の不利です。 欲しいのは amount(4バイト)だけなのに、note の180文字を含む1行まるごとをディスクから読んでいます。 必要なデータの、50倍以上を運んでいます。

列指向がやっていることを、被覆索引で再現する

列指向のデータベースは、amount の値だけを、まとめて別の場所に並べて持ちます。 だから合計するときに note を読みません。

MySQL でそれに一番近い状態を作れます。

ALTER TABLE events ADD INDEX idx_amount (amount);

1000万行だと1〜3分かかります。終わったらもう一度。

SELECT SUM(amount) FROM events;
EXPLAIN SELECT SUM(amount) FROM events;

私の環境ではこうなりました。

1 row in set (2.18 sec)
type: index
key: idx_amount
Extra: Using index

11.42秒 → 2.18秒。5倍速くなりました。

Extra: Using index が出ています。これが「本体の行を読まずに、索引の中だけで答えを出した」という印です。

索引 idx_amount の中には、amount の値だけが並んでいます。 つまり、この1列だけを縦に並べて持っている状態です。 列指向がやっているのは、これを全部の列について、最初からやっておくことです。

だから列指向は、列を1つ2つだけ読む集計で、行指向の10倍以上の差をつけます。「1000万行のうち3列だけ」のような問いが、分析の世界では普通だからです。

📸 スクリーンショット: 索引を張る前と後の SELECT SUM(amount) の実行時間が2つ並んだ画面(11.42 sec と 2.18 sec)。この記事で一番はっきり差が出る場所です。

では、列指向にすれば全部速いのか

逆をやります。1行だけ更新します。

まず、比較のために索引をたくさん足します。列指向は「全部の列が別々に並んでいる」形なので、1行を書くと全部の場所に書きに行きます。 その状況を、索引を増やして再現します。

-- 索引が1つの状態で測る
UPDATE events SET amount = amount + 1 WHERE id BETWEEN 1 AND 20000;
Query OK, 20000 rows affected (0.62 sec)

索引を3つに増やします。

ALTER TABLE events ADD INDEX idx_kind (kind), ADD INDEX idx_created (created);
UPDATE events SET amount = amount + 1, kind = 'view' WHERE id BETWEEN 20001 AND 40000;
Query OK, 20000 rows affected (1.94 sec)

同じ2万行の更新が、0.62秒 → 1.94秒。3倍遅くなりました。

「列ごとに別々に持つ」ことの代償です。 読むときは要る列だけで済みますが、書くときは触った列の数だけ書き込み先が増えます。

本物の列指向データベースは、これがもっと極端です。1行の UPDATE が苦手で、そもそも用意されていないものすらあります。 代わりに「まとめて追記する」形をとります。

行指向(MySQL / PostgreSQL)列指向(分析用)
1行を丸ごと読む得意(1箇所にある)苦手(列の数だけ集めに行く)
1000万行の1列を合計苦手(全部の列を運ぶ)得意
1行の更新・削除得意苦手(または非対応)
書き込みの頻度1件ずつ何度でもまとめて追記する形が前提
向く用途アプリの本体(注文・投稿・ログイン)分析・レポート・ダッシュボード
例MySQL、PostgreSQL、SQLiteClickHouse、BigQuery、Redshift、DuckDB

列指向は、リレーショナルの「速い版」ではありません。用途がまるごと別です。 同じ SQL が書けるので混同しがちですが、「アプリの本体を列指向に置く」は、まずやりません。

⑥ グラフ的な問い — 3段たどると急に遅くなる

最後の形はグラフです。これも立てません。

Neo4j を入れるのは重いですし、この記事で言いたいのは「グラフDBの使い方」ではなく「なぜグラフDBという別の道具があるのか」だからです。 そのつらさは、MySQL で再帰CTEを書けば、そのまま体験できます。

フォロー関係を作る

MySQL の中で打ちます。まず利用者を5000人に増やします。

CREATE TABLE people (id INT PRIMARY KEY);
INSERT INTO people (id)
WITH RECURSIVE seq(n) AS (
  SELECT 1 UNION ALL SELECT n+1 FROM seq WHERE n < 5000
)
SELECT n FROM seq;

SELECT COUNT(*) FROM people;

5000 が返ります。

このままだと ERROR 3636 (Recursive query aborted after 1001 iterations) で止まります。 MySQL は再帰の回数を既定で 1000回までに制限しているので、5000まで数えるこの再帰は必ず当たります。上の SQL より先に、次を打ってください。(先に打って止まった場合は、次を打ってから INSERT から打ち直します)

SET SESSION cte_max_recursion_depth = 100000;

次に、1人あたり20人をフォローしている関係を作ります。

CREATE TABLE nums (k INT PRIMARY KEY);
INSERT INTO nums (k)
WITH RECURSIVE s(n) AS (SELECT 1 UNION ALL SELECT n+1 FROM s WHERE n < 20)
SELECT n FROM s;

CREATE TABLE follows (
  follower_id INT NOT NULL,
  followee_id INT NOT NULL,
  PRIMARY KEY (follower_id, followee_id),
  KEY idx_followee (followee_id)
);

INSERT IGNORE INTO follows (follower_id, followee_id)
SELECT p.id, ((p.id * 7 + n.k * 137) % 5000) + 1
FROM people p JOIN nums n
WHERE ((p.id * 7 + n.k * 137) % 5000) + 1 <> p.id;

SELECT COUNT(*) FROM follows;

およそ 10万件の関係ができます。1人あたり20人です。

1段ずつ深くしてみる

1段目(自分がフォローしている人)は簡単です。

SELECT COUNT(DISTINCT followee_id) FROM follows WHERE follower_id = 1;

20 が返ります。一瞬です。

2段目からは、たどる必要があります。再帰CTEで書きます。

WITH RECURSIVE reach (id, depth) AS (
  SELECT 1, 0
  UNION ALL
  SELECT f.followee_id, r.depth + 1
  FROM reach r
  JOIN follows f ON f.follower_id = r.id
  WHERE r.depth < 2
)
SELECT COUNT(DISTINCT id) AS people, COUNT(*) AS steps FROM reach;

WHERE r.depth < 2 の数字を 1、2、3、4、5 と変えて、それぞれ実行時間を控えます。

私の環境(1000万行の events が同居した状態のVM)の結果です。

深さたどった人数内部で処理した行数実行時間
121210.00秒
23754210.01秒
33,4148,4210.09秒
44,996168,4211.62秒
55,0003,368,42131.7秒

グラフにするまでもありません。

深さを1つ増やすたびに、処理する行が20倍になります。 1人あたり20人をフォローしているので、20 → 400 → 8,000 → 160,000 → 3,200,000 です。

しかも、深さ4の時点で「たどりつく人数」は 4,996 人。5000人のほぼ全員です。 つまり、

重い計算をして、返ってきた答えは「ほぼ全員」でした。

なぜ遅いのか

EXPLAIN を見ます。

EXPLAIN WITH RECURSIVE reach (id, depth) AS (
  SELECT 1, 0
  UNION ALL
  SELECT f.followee_id, r.depth + 1
  FROM reach r JOIN follows f ON f.follower_id = r.id
  WHERE r.depth < 4
)
SELECT COUNT(DISTINCT id) FROM reach;

follows のところは key: PRIMARY、type: ref になっているはずです。索引はちゃんと効いています。

遅いのは、SQLの書き方が悪いからでも、索引が無いからでもありません。 「1段たどるたびに20倍に広がる」という、問いそのものの性質です。

リレーショナルデータベースは、1段たどるたびに表を1回突き合わせます。 5段なら5回です。グラフデータベースは、繋がっている先を「隣の場所」として直接持っているので、突き合わせをしません。だから深さが増えても、リレーショナルほど急には悪化しません。

リレーショナル(再帰CTE)グラフDB
関係の持ち方別の表の行として持つ隣接する場所への参照として持つ
1段たどる表を1回突き合わせる参照を1回たどる
深さ1〜2十分速い速い
深さ4以上急激に遅くなる悪化がゆるやか
最短経路・共通の知人書くのがつらいこれのための道具

再帰CTEそのものの書き方は、サブクエリ3段は読めない — SQL深掘りの回で扱いました。

📸 スクリーンショット: 深さ4と深さ5の実行時間が並んだ画面(1.62 sec と 31.7 sec)。「1段増やしただけ」でこうなる、という証拠です。

ただし、個人サイトではまず出てこない

ここまで見ると「グラフDBが要る」と思うかもしれません。ほとんどの場合、要りません。

現実の問いを並べてみます。

実際に必要になる問い深さ再帰CTEで足りるか
フォロー中の人の投稿を出す(タイムライン)1余裕
共通のフォロー相手(知り合いかも)2足りる
カテゴリの親子をたどる3〜5だが幅が狭い足りる
組織図の上長をたどる5〜10だが枝分かれしない足りる
不正送金の経路を追う6段以上・幅が広いつらい
推薦のための多段の類似不定つらい

上の4つは、個人開発でも普通に出てきます。そして全部、深さが浅いか、幅が狭い。 下の2つが出てくるのは、金融の不正検知や大規模SNSです。

「グラフっぽい形をしている」ことと「グラフDBが要る」ことは別です。 判断の基準は、形ではなく「深さ × 幅」です。

⑦ 1枚の判断表

ここまでを1枚にします。これが、この記事の持ち帰りです。

形得意な問い苦手な問い具体的な用途代表
キーバリュー鍵が完全に分かっているものを1件取る中身で探す・集計する・並べ替えるセッション、カウンタ、キャッシュ、レート制限、ロックRedis、Memcached
ドキュメント1件ぶんをまとめて読む(あらかじめ決めた形)想定していなかった集計・結合、コピーの一括更新商品カタログ、CMSの記事、ログ、設定の塊MongoDB、Firestore
リレーショナル(行指向)あとから来た問い全般、正しさを守る1000万行の集計、多段のたどりアプリの本体すべて(利用者・注文・投稿)MySQL、PostgreSQL、SQLite
列指向たくさんの行の、少ない列を集計する1行の更新・削除、1件ぶんを丸ごと読む分析、レポート、ダッシュボード、アクセス集計ClickHouse、BigQuery、DuckDB
グラフ深く(4段以上)・幅広くたどる普通の一覧表示、集計不正検知、推薦、経路探索、権限の継承Neo4j、Amazon Neptune
全文検索文章の部分一致・あいまい検索正確な集計、更新の即時反映サイト内検索、ログ検索Meilisearch、Elasticsearch

最後の行だけ、この記事では手を動かしていません。 ただし構図は同じで、LIKE '%語%' は索引が効かない——つまり「その問いは、その形が想定していない」というだけの話です。

選ぶ順番

1. 問いを5つ書き出す(この記事の Q1〜Q5 のように) 2. その5つを、素直に書ける形を選ぶ 3. 「あとから問いが増えるか」を考える。増えるならリレーショナル 4. それでも足りない1つの問いだけ、別の道具を足す

「速いから」で選ばないでください。 速さの差は、たいてい索引を1本張れば消えます。

⑧ 混ぜる代償と、「速い」の正体

製品を混ぜると、運用が製品の数だけ増える

用途ごとに最適なものを使う——これをポリグロット・パーシステンスと呼びます。理屈としては正しいです。

いま立てている3つで、実際にどれだけ手間が増えるか数えます。

バックアップの取り方が、3つとも違います。

bash
$ docker compose exec mysql mysqldump -uroot -plabpass storelab > mysql-backup.sql
$ docker compose exec redis redis-cli SAVE
$ docker compose exec mongo mongodump --db storelab --archive=/tmp/mongo.archive

3つとも別のコマンド、別の形式、別の戻し方です。

さらに、それぞれについて必要なものを並べます。

やることMySQLRedisMongoDB
バックアップmysqldumpSAVE / RDB / AOFmongodump
戻す練習別別別
監視する項目遅いクエリ、接続数、レプリカ遅延メモリ使用量、evicted_keysロック、レプリカセット状態
バージョン更新8.0 → 8.4 の非互換を調べる7 → 8 を調べる7 → 8 を調べる
接続情報の管理1組1組1組
障害時に見る場所別別別

1人で運用するなら、これが3倍になります。 しかも「あまり使っていないほう」が、更新を忘れて古いまま放置されます。 セキュリティ更新が止まった MongoDB が1つ、VMの中で動いたままになる——これが一番よくある結末です。

迷ったら1本で始めてよい

PostgreSQL や MySQL は、もう「リレーショナルだけ」ではありません。

MySQL の中で試します。

CREATE TABLE docs (
  id   INT PRIMARY KEY,
  data JSON NOT NULL
);

INSERT INTO docs VALUES
 (1, '{"title":"aptの話","author":{"id":1,"name":"sato"},"tags":["linux","apt"]}'),
 (2, '{"title":"Composeの話","author":{"id":2,"name":"tanaka"},"tags":["docker"]}');

SELECT id,
       data->>'$.title'       AS title,
       data->>'$.author.name' AS author,
       data->'$.tags'         AS tags
FROM docs;
+----+---------------+--------+---------------------+
| id | title         | author | tags                |
+----+---------------+--------+---------------------+
|  1 | aptの話       | sato   | ["linux", "apt"]    |
|  2 | Composeの話   | tanaka | ["docker"]          |
+----+---------------+--------+---------------------+

MongoDB でやったことが、そのまま MySQL でできています。 条件にも使えます。

SELECT id, data->>'$.title' AS title
FROM docs
WHERE JSON_CONTAINS(data->'$.tags', '"linux"');

そして、同じデータベースの中で、普通の表と JOIN できます。

「形が決まっている部分は表、決まっていない部分はJSON列」 これで、ドキュメント型が欲しくなる理由の大半は消えます。

同じことは PostgreSQL の jsonb でもできます。むしろ PostgreSQL のほうが索引の選択肢が多いです。 両者の違いはMySQLとPostgreSQLの違いは1割 — 移して確かめるの回でやりました。

足すのは、困ってからです。 「将来キャッシュが要るかもしれないから、いま Redis も立てておく」は、いま運用の手間を2倍にしているだけです。 困ってから足すほうが、そのときには何が必要か分かっているので、選択も正確になります。

「速い」の正体

最後に、冒頭の話に戻ります。なぜ MongoDB は速かったのか。

同じ「投稿1件を著者名つきで表示する」で、両者が何をしていたかを並べます。

MongoDBMySQL
表の突き合わせしていない3つの表を突き合わせる
外部キーの確認していない挿入のたびに親の存在を確認
著者名の一貫性保証していない保証している
想定外の集計アプリで書くSQLで書ける
1件取る速さ速いやや遅い

NoSQL が速いのは、リレーショナルがやっている仕事を、やっていないからです。 そして、その仕事は消えたのではありません。アプリ側に移っただけです。

私の場合、移った先はこうなりました。

リレーショナルがやっていたこと移った先
JOINアプリのコード(60行)
外部キー制約登録前に存在確認するコード
集計(GROUP BY)集計パイプライン、または自作のループ
一意制約アプリでの重複チェック(並行時に破れる)
名前の変更を1箇所で420件の一括更新スクリプト

合計すると、私が書いたコードのほうが、MySQL がやっていたことより遅くて、バグが多かったです。

道具を選ぶというのは、「何を自分でやることにするか」を選ぶことです。 速さは、たいていその代金として払っています。

つまずいたところ

1. docker compose up -d の直後に MySQL へ繋いで Can't connect が出た

MySQL は起動してから内部の初期化に20〜60秒かかります。コンテナが running でも、まだ接続を受け付けていません。

docker compose exec mysql mysqladmin ping -uroot -plabpass が mysqld is alive を返すまで待ってください。「立ち上がった」と「使える」は別です。 サービスでも同じことが起きますが、コンテナだと running の表示があるぶん、余計に騙されます。

2. KEYS * を打ってしまった

練習用の3件しか無いときは一瞬で返ります。それで安心してしまうのが一番危険でした。

本番の Redis に KEYS * を打つと、鍵の数だけ時間がかかる間、Redis 全体が他の要求を受け付けません。 セッション置き場なら、その間ログインが全部止まります。

探す必要が出た時点で、Redis の使い方が間違っています。 探すための鍵を自分で用意するか、探すのは別の場所(リレーショナル)でやってください。

3. 1000万行の INSERT で The total number of locks exceeds the lock table size が出た

倍々に増やす後半、1回の INSERT で500万行を扱おうとすると出ます。InnoDB のバッファプールが足りていません。

300万行で止めて構いません。 比べる話なので、桁が同じなら結論は変わりません。どうしても1000万行が欲しい場合は、WHERE id < 2500000 のように区切って2回に分けてください。

4. SELECT SUM(amount) が索引を張っても速くならなかった

EXPLAIN に Using index が出ているか確かめてください。出ていなければ、索引が使われていません。

私の場合、SELECT SUM(amount), MAX(note) FROM events と、note も一緒に取っていました。note は索引に入っていないので、結局は本体の行を全部読みます。

被覆索引は「取る列が全部索引に入っている」ときだけ効きます。 1列でもはみ出すと効きません。

5. 2回目に測ったら急に速くなって、比較にならなかった

1回目でデータがメモリに載ったからです。2回目以降はディスクを読んでいません。

比べるときは、両方とも2回ずつ測って、2回目どうしを比べてください。 1回目と2回目を比べると、測っているのは「ディスクの速さ」であって、「持ち方の違い」ではなくなります。

測り方を先に決めてから測る、という当たり前のことを、私はここで2回失敗しました。

6. 再帰CTEで Recursive query aborted after 1001 iterations が出た

MySQL は再帰の回数を1000回までに制限しています(cte_max_recursion_depth)。

SET SESSION cte_max_recursion_depth = 100000;

ただし、これを上げる前に一度考えてください。 私の場合、上限に当たった原因は「終了条件を書き忘れていた」ことでした。 WHERE r.depth < N が抜けていて、無限に回っていたのです。

上限は、無限ループから守ってくれている柵です。 外す前に、まず柵に当たった理由を見てください。

7. 深さ5の再帰CTEを打ったら、VMごと固まった

処理行数が340万を超え、その間メモリを掴みます。別の端末を先に開いておいてください。

bash
$ docker compose exec mysql mysql -uroot -plabpass -e "SHOW PROCESSLIST;"
$ docker compose exec mysql mysql -uroot -plabpass -e "KILL <Id>;"

SHOW PROCESSLIST で見えた Id を KILL します。「重い問い合わせを、外から止められる」ことを一度やっておくと、本番で慌てません。

8. MongoDB で author.name を更新したのに、一部が古いままだった

updateOne を使っていました。updateMany でないと1件しか直りません。

そして、これがドキュメント型の一番怖いところです。古い名前が残っていても、エラーは1つも出ません。 気づくのは、利用者から「名前が変わっていません」と言われたときです。

リレーショナルなら、そもそも users の1行を直すだけなので、この失敗自体が存在しません。

後片づけ

立てたものを全部消します。 MySQL の中にいる場合は先に出ます。

exit

VMのシェルに戻ったら、~/storelab で打ちます。

bash
$ cd ~/storelab
$ docker compose down -v

-v を付けると、データを入れていた場所(ボリューム)も一緒に消えます。 1000万行の events が2GB以上使っているので、必ず付けてください。

消えたことを確かめます。

bash
$ docker compose ps
$ docker volume ls
$ docker system df

docker compose ps に何も出ず、docker volume ls に storelab_ で始まるものが残っていなければ成功です。

イメージも消す場合はこうします。

bash
$ docker rmi mysql:8.0 redis:7-alpine mongo:7

作業ディレクトリごと消す場合。

bash
$ cd ~
$ rm -rf ~/storelab

ディスクが戻ったか確かめます。

bash
$ df -h /

docker compose down を -v 無しで打つと、ボリュームが残ります。 コンテナは消えているので docker compose ps には出ず、2GBが静かに残り続けます。

まとめ — 理解できたこと

  • 持ち方は「速さ」で選ぶものではない。 どういう問いを投げるかを先に5つ書き出し、それを素直に書ける形を選ぶ。逆をやると、あとから来た問いを全部アプリのコードで受けることになる
  • キーバリューは「鍵が完全に分かっているものを取る」以外、ほぼ全部つらい。 中身で探せないので、探すための鍵を自分で作って自分で更新することになる。更新漏れは、エラーを出さずにデータを見えなくする
  • KEYS * を本番で打ってはいけない。 Redis は1つずつ処理する作りなので、走査が終わるまで全部の要求が待たされる。 手元で速かったことは、何の保証にもならない
  • それでもキーバリューには代えが効かない用途がある。 セッション(期限つき)、カウンタ、キャッシュ、レート制限。「期限が来たら消える」はリレーショナルには無い
  • ドキュメント型が1回で取れるのは、同じ値を複数箇所にコピーしているから。 読むのは速いが、コピーの数だけ更新が必要になる。その約束を守らせてくれるものは、どこにも無い
  • リレーショナルの最大の強みは速さではない。 設計時に想定していなかった問いに、表を変えずに対応できること。 そして外部キーや一意制約が、アプリのバグの最後の砦になること
  • 列指向は「1000万行の少ない列を集計する」ための形。 被覆索引で再現すると、11.4秒 → 2.2秒。ただし1行の更新には不利で、索引を増やしただけで更新が3倍遅くなった。アプリの本体を列指向に置くことは、まずない
  • 多段のたどりは、深さを1つ増やすと処理量が「1人あたりの本数」倍になる。 実測で深さ4が1.6秒、深さ5が31.7秒。遅いのは書き方でも索引でもなく、問いそのものの性質
  • それでも個人サイトでグラフDBが要ることはまずない。 タイムライン(深さ1)、共通の知人(深さ2)、カテゴリの親子(幅が狭い)は全部、再帰CTEで足りる。 判断は形ではなく「深さ × 幅」でする
  • 製品を混ぜると、バックアップ・監視・更新・障害対応が製品の数だけ増える。 そして一番使っていないものが、更新されないまま残る
  • 迷ったら MySQL / PostgreSQL 1本で始めてよい。 JSON列があるので、形が決まらない部分だけJSONで持って、普通の表とJOINできる
  • 足すのは、困ってから。 先回りして立てた製品は、いま運用の手間を増やしているだけ。困ってから足せば、そのときには何が必要か分かっている
  • NoSQL が速いのは、リレーショナルがやっている仕事をやっていないから。 JOIN も制約も集計も、消えたのではなくアプリ側に移っただけ
  • 道具を選ぶとは、「何を自分でやることにするか」を選ぶこと。 速さは、たいていその代金として払っている

参考リンク


次に読む

この記事は 【深掘り】システム設計 の 13 / 17 本目です。

記事
← 前の記事レプリケーションとシャーディング — 書き込みは速くならない
→ 次の記事イベント駆動アーキテクチャ — 機能を足すたび注文APIが壊れる

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

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

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

System Design›System Design — Data Stores
  • キー・ドキュメント・列指向・グラフの得意な問い
  • 速いのは JOIN をしていないから

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

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

コメント

コメントする

目次