通知機能の設計｜既読・未読のテーブル設計と通知手段の使い分け

通知機能の設計とは、「だれに・何を・どの手段で届け、読んだかどうかをどう覚えるか」をデータ構造として決めることです。 いちばん差が出るのは既読・未読の持ち方で、個人あての通知と全員あてのお知らせを同じ形で持つと、利用者が増えたときに行数が掛け算で増えていきます。

この記事はデータ構造に絞り、既読の持ち方3方式の選び方、PostgreSQL と MySQL で動かした DDL と SQL、未読バッジ用インデックスの EXPLAIN、通知手段（プッシュ・メール・アプリ内）の使い分け、受信設定テーブル、重複送信を防ぐ一意キーを、実行結果つきで並べます。題材は架空の備品予約システムです。

確認環境（確認日 2026-10-06）：Ubuntu 24.04、PostgreSQL 18.4、MySQL 8.0.46。SQL はすべてこの2つで実行し、出力をそのまま載せています。プッシュ通知の性質は MDN の Push API のページで確認しました。

この記事の結論

• 個人あての通知は、受信者ごとに1行を作り、read_at（既読にした時刻）を持つ。 NULL が未読です。
• 全員あてのお知らせは、本体を1行だけ作り、読んだ人の分だけ既読行を足す。 全員分の行を先に作らない。
• 未読バッジの件数は、未読の行だけを対象にしたインデックスで数える（PostgreSQL は部分インデックス、MySQL は複合インデックス）。
• 通知手段は緊急度と、相手がいまアプリを開いているかで選ぶ。アプリ内を基本にし、プッシュとメールは種別ごとに足す。
• 二重登録・二重送信は、一意キー（受信者＋出来事のキー、通知＋手段）でデータベースに止めてもらう。

既読の持ち方3方式の早見表

件数と表示要件から選ぶ

全員分の行を先に作ると、どれだけ増えるか

全員あてのお知らせを方式 A で持つ（お知らせ1件につき全員分の未読行を作る）と、行数は「利用者数 × お知らせの件数」になります。会員10万人にお知らせを200件出すと、10万 × 200 = 2,000万行です。新しく登録した会員の分をどう作るか、という課題も付いてきます。

方式 B なら、読まれた回数だけ行が増えます。この記事の検証データ（会員2,000人、お知らせ50件）では次のとおりでした。

SELECT (SELECT count(*) FROM announcement_reads) AS read_rows,
       2000 * (SELECT count(*) FROM announcements) AS rows_if_precreated;

 read_rows | rows_if_precreated
-----------+--------------------
      9453 |             100000

テーブル設計（PostgreSQL 18）

-- 通知種別（マスタ）と、種別ごとの既定の通知手段
CREATE TABLE notification_types (
  type            text PRIMARY KEY,
  label           text NOT NULL,
  default_in_app  boolean NOT NULL DEFAULT true,
  default_email   boolean NOT NULL DEFAULT false,
  default_push    boolean NOT NULL DEFAULT false,
  mandatory       boolean NOT NULL DEFAULT false   -- true の種別は利用者が止められない
);

-- 個人あて通知：受信者ごとに1行。既読は read_at に時刻を入れる
CREATE TABLE notifications (
  id          bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  user_id     bigint NOT NULL,
  type        text   NOT NULL REFERENCES notification_types (type),
  title       text   NOT NULL,
  body        text   NOT NULL,
  link_path   text,
  dedupe_key  text   NOT NULL,                     -- 同じ出来事を二重に登録しないためのキー
  read_at     timestamptz,                         -- NULL なら未読
  created_at  timestamptz NOT NULL DEFAULT now(),
  CONSTRAINT notifications_dedupe UNIQUE (user_id, dedupe_key)
);
CREATE INDEX notifications_list_idx   ON notifications (user_id, created_at DESC, id DESC);
CREATE INDEX notifications_unread_idx ON notifications (user_id) WHERE read_at IS NULL;

-- 全員あてのお知らせ：本体は1行だけ
CREATE TABLE announcements (
  id            bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  title         text NOT NULL,
  body          text NOT NULL,
  published_at  timestamptz NOT NULL,
  expires_at    timestamptz
);
CREATE INDEX announcements_published_idx ON announcements (published_at DESC);

-- 読んだ人だけ行を足す
CREATE TABLE announcement_reads (
  user_id          bigint NOT NULL,
  announcement_id  bigint NOT NULL REFERENCES announcements (id) ON DELETE CASCADE,
  read_at          timestamptz NOT NULL DEFAULT now(),
  PRIMARY KEY (user_id, announcement_id)
);

-- 通知種別×通知手段ごとの受信設定：既定から変えた分だけ行を持つ
CREATE TABLE notification_preferences (
  user_id   bigint  NOT NULL,
  type      text    NOT NULL REFERENCES notification_types (type),
  channel   text    NOT NULL CHECK (channel IN ('in_app', 'email', 'push')),
  enabled   boolean NOT NULL,
  PRIMARY KEY (user_id, type, channel)
);

-- 通知手段ごとの送信記録：同じ通知を同じ手段で二重に送らない
CREATE TABLE notification_deliveries (
  id               bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  notification_id  bigint NOT NULL REFERENCES notifications (id) ON DELETE CASCADE,
  channel          text   NOT NULL CHECK (channel IN ('email', 'push')),
  status           text   NOT NULL DEFAULT 'pending' CHECK (status IN ('pending', 'sent', 'error')),
  sent_at          timestamptz,
  CONSTRAINT notification_deliveries_once UNIQUE (notification_id, channel)
);

利用者テーブルへの外部キーは、利用者テーブルの形がシステムごとに違うため省いています。実際には user_id に外部キーを付けます。

検証データ

INSERT INTO notification_types (type, label, default_in_app, default_email, default_push, mandatory) VALUES
  ('reservation_approved', '予約の承認',       true, true,  true,  false),
  ('reservation_remind',   '利用前日のお知らせ', true, false, true,  false),
  ('comment',              'コメント',         true, false, false, false),
  ('security',             'ログイン・パスワード変更', true, true, false, true);

-- 会員 2,000人 × 100件 = 20万件。新しい5件だけ未読（未読は全体の5%）
INSERT INTO notifications (user_id, type, title, body, link_path, dedupe_key, read_at, created_at)
SELECT u, 'comment', '予約 #' || n || ' にコメントが付きました', '本文', '/reservations/' || n,
       'comment:' || n,
       CASE WHEN n <= 95 THEN timestamptz '2026-10-01 09:00+09' + n * interval '1 hour' END,
       timestamptz '2026-09-01 09:00+09' + n * interval '6 hours'
FROM generate_series(1, 2000) u, generate_series(1, 100) n;

-- お知らせ 50件。会員1〜200（全体の1割）が、最新の3件を除いて読んだ
INSERT INTO announcements (title, body, published_at)
SELECT 'お知らせ ' || n, '本文', timestamptz '2026-08-01 10:00+09' + n * interval '1 day'
FROM generate_series(1, 50) n;
INSERT INTO announcement_reads (user_id, announcement_id)
SELECT u, a FROM generate_series(1, 200) u, generate_series(1, 47) a;
ANALYZE;

   n    | unread | reads
--------+--------+-------
 200000 |  10000 |  9400

未読件数を数える（未読バッジ）

SELECT count(*) AS unread FROM notifications WHERE user_id = 42 AND read_at IS NULL;

 unread
--------
      5

部分インデックスの効果を EXPLAIN で見る

notifications_unread_idx は、read_at が NULL の行だけを入れた部分インデックスです。

EXPLAIN (ANALYZE, BUFFERS, COSTS OFF, TIMING OFF, SUMMARY OFF)
SELECT count(*) FROM notifications WHERE user_id = 42 AND read_at IS NULL;

 Aggregate (actual rows=1.00 loops=1)
   Buffers: shared hit=7
   ->  Bitmap Heap Scan on notifications (actual rows=5.00 loops=1)
         Recheck Cond: ((user_id = 42) AND (read_at IS NULL))
         Heap Blocks: exact=5
         Buffers: shared hit=7
         ->  Bitmap Index Scan on notifications_unread_idx (actual rows=5.00 loops=1)
               Index Cond: (user_id = 42)
               Index Searches: 1
               Buffers: shared hit=2

部分インデックスを外すと、一覧用のインデックスでその人の100件をすべて読み、95件を捨てる形になります。

 Aggregate (actual rows=1.00 loops=1)
   Buffers: shared hit=103
   ->  Bitmap Heap Scan on notifications (actual rows=5.00 loops=1)
         Recheck Cond: (user_id = 42)
         Filter: (read_at IS NULL)
         Rows Removed by Filter: 95
         Heap Blocks: exact=100
         Buffers: shared hit=103
         ->  Bitmap Index Scan on notifications_list_idx (actual rows=100.00 loops=1)
               Index Cond: (user_id = 42)
               Index Searches: 1
               Buffers: shared hit=3
 Planning:
   Buffers: shared hit=4

読んだバッファは 103 から 7 に減りました。 この差は、1人あたりの通知が増えるほど広がります（未読は少数のまま、既読だけが積み上がるため）。

比較として、全行を対象にした複合インデックス (user_id, read_at) も作って試しました。

読み取りの速さは部分インデックスと複合インデックスでほぼ同じで、違いは大きさ（192 kB と 6000 kB、約31分の1）でした。 部分インデックスは、既読にした行がインデックスから外れるので、通知が増えても大きさが未読の件数に比例したままになります。

バッジの上限を決めて、数える量を抑える

未読が数千件たまった人でも、バッジの表示は「99+」のように上限があります。上限＋1件で数えるのをやめる書き方です。

SELECT count(*) AS unread_capped
FROM (SELECT 1 FROM notifications WHERE user_id = 42 AND read_at IS NULL LIMIT 100) t;

 unread_capped
---------------
             5

通知一覧を取得する

SELECT id, title, read_at IS NULL AS unread, created_at
FROM notifications
WHERE user_id = 42
ORDER BY created_at DESC, id DESC
LIMIT 5;

   id   |              title               | unread |       created_at
--------+----------------------------------+--------+------------------------
 198042 | 予約 #100 にコメントが付きました | t      | 2026-09-26 09:00:00+09
 196042 | 予約 #99 にコメントが付きました  | t      | 2026-09-26 03:00:00+09
 194042 | 予約 #98 にコメントが付きました  | t      | 2026-09-25 21:00:00+09
 192042 | 予約 #97 にコメントが付きました  | t      | 2026-09-25 15:00:00+09
 190042 | 予約 #96 にコメントが付きました  | t      | 2026-09-25 09:00:00+09

 Limit (actual rows=5.00 loops=1)
   Buffers: shared hit=8
   ->  Index Scan using notifications_list_idx on notifications (actual rows=5.00 loops=1)
         Index Cond: (user_id = 42)
         Index Searches: 1
         Buffers: shared hit=8

並べ替えなしで、インデックスの順に5件だけ読んでいます。続きは、前のページの最後の行（created_at と id）を条件にします。

SELECT id, title, read_at IS NULL AS unread, created_at
FROM notifications
WHERE user_id = 42
  AND (created_at, id) < (timestamptz '2026-09-25 09:00+09', 190042)
ORDER BY created_at DESC, id DESC
LIMIT 3;

   id   |              title              | unread |       created_at
--------+---------------------------------+--------+------------------------
 188042 | 予約 #95 にコメントが付きました | f      | 2026-09-25 03:00:00+09
 186042 | 予約 #94 にコメントが付きました | f      | 2026-09-24 21:00:00+09
 184042 | 予約 #93 にコメントが付きました | f      | 2026-09-24 15:00:00+09

既読にする

1件を既読にする

UPDATE notifications SET read_at = now()
WHERE id = 198042 AND user_id = 42 AND read_at IS NULL;

同じ文を2回、さらに別の利用者（user_id = 43）で1回実行した結果です。

UPDATE 1
UPDATE 0
UPDATE 0

条件に read_at IS NULL を入れておくと、2回目は 0 件になり、最初に読んだ時刻が上書きされません。 user_id も条件に入れているので、他人の通知の ID を指定しても更新されません。

すべて既読にする

UPDATE notifications SET read_at = now() WHERE user_id = 42 AND read_at IS NULL;

UPDATE 4

更新の対象も未読の行だけなので、部分インデックスがそのまま使えます。

全員あてのお知らせ（本体1行＋既読行）

未読件数

SELECT count(*) AS unread
FROM announcements a
WHERE a.published_at <= now()
  AND (a.expires_at IS NULL OR a.expires_at > now())
  AND NOT EXISTS (
    SELECT 1 FROM announcement_reads r
    WHERE r.announcement_id = a.id AND r.user_id = 42
  );

会員42（47件を読んだ人）と、会員1500（1件も読んでいない人）の結果です。

 unread
--------
      3

 unread
--------
     50

1件も読んでいない人には既読行が1行もありませんが、未読は正しく 50 と数えられます。 行を先に作らなくても未読を表せるのが、この方式の要点です。

一覧（既読・未読つき）

SELECT a.id, a.title, r.read_at IS NULL AS unread
FROM announcements a
LEFT JOIN announcement_reads r ON r.announcement_id = a.id AND r.user_id = 42
WHERE a.published_at <= now()
  AND (a.expires_at IS NULL OR a.expires_at > now())
ORDER BY a.published_at DESC
LIMIT 5;

 id |    title    | unread
----+-------------+--------
 50 | お知らせ 50 | t
 49 | お知らせ 49 | t
 48 | お知らせ 48 | t
 47 | お知らせ 47 | f
 46 | お知らせ 46 | f

既読にする（1件・すべて）

-- 1件（2回実行）
INSERT INTO announcement_reads (user_id, announcement_id) VALUES (42, 50)
ON CONFLICT DO NOTHING;

INSERT 0 1
INSERT 0 0

-- すべて既読：公開中のお知らせのうち、まだ既読行が無いものだけが足される
INSERT INTO announcement_reads (user_id, announcement_id)
SELECT 42, a.id
FROM announcements a
WHERE a.published_at <= now()
  AND (a.expires_at IS NULL OR a.expires_at > now())
ON CONFLICT DO NOTHING;

INSERT 0 2

会員42は残り2件、同じ文を会員1500で実行すると INSERT 0 50 でした。

未読バッジは2つを足す

SELECT
  (SELECT count(*) FROM notifications WHERE user_id = 7 AND read_at IS NULL)
  +
  (SELECT count(*) FROM announcements a
    WHERE a.published_at <= now()
      AND (a.expires_at IS NULL OR a.expires_at > now())
      AND NOT EXISTS (SELECT 1 FROM announcement_reads r
                      WHERE r.announcement_id = a.id AND r.user_id = 7)) AS badge;

 badge
-------
     8

個人あて5件と、お知らせ3件の合計です。お知らせは件数が少ないので、公開中の一覧をアプリ側でキャッシュし、既読行だけを利用者ごとに読む形にもできます。

設計で決めておくこと

最終閲覧時刻で持つ方式（方式 C）

CREATE TABLE notification_last_seen (
  user_id       bigint PRIMARY KEY,
  last_seen_at  timestamptz NOT NULL
);
INSERT INTO notification_last_seen VALUES (7, timestamptz '2026-09-25 00:00+09');

-- 未読件数：最終閲覧より新しい通知
SELECT count(*) AS unread
FROM notifications n
WHERE n.user_id = 7
  AND n.created_at > COALESCE(
        (SELECT last_seen_at FROM notification_last_seen WHERE user_id = 7), '-infinity');

 unread
--------
      6

-- 一覧を開いたとき：時刻を1行更新するだけで、すべて既読になる
INSERT INTO notification_last_seen VALUES (7, now())
ON CONFLICT (user_id) DO UPDATE SET last_seen_at = EXCLUDED.last_seen_at;

実行後、同じ未読件数の問い合わせは 0 になりました。既読にする処理が「1行の更新」で済むのが利点で、代わりに一覧を開いた時点で、中身を読んでいない通知も既読扱いになります。方式 A の read_at と組み合わせ、「バッジは最終閲覧時刻、行の太字は read_at」と役割を分ける設計もあります。

重複を防ぐ一意キー

同じ出来事の通知を二重に登録しない

承認ボタンの二度押しや、処理の再実行で、同じ通知が2件できることがあります。「受信者＋出来事を表すキー」に一意制約を付け、2回目を登録しないようにします。dedupe_key は「種別:対象のID」のように、出来事が同じなら同じ文字列になるよう組み立てます。

INSERT INTO notifications (user_id, type, title, body, link_path, dedupe_key)
VALUES (42, 'reservation_approved', '予約 #310 が承認されました', '本文',
        '/reservations/310', 'reservation_approved:310')
ON CONFLICT (user_id, dedupe_key) DO NOTHING
RETURNING id;

1回目と2回目の結果です。

   id
--------
 200001
(1 row)

INSERT 0 1

 id
----
(0 rows)

INSERT 0 0

RETURNING が行を返したときだけ、後続の送信処理へ進めます。 ON CONFLICT を付けない INSERT は、次のエラーで止まります。

ERROR:  duplicate key value violates unique constraint "notifications_dedupe"
DETAIL:  Key (user_id, dedupe_key)=(42, reservation_approved:310) already exists.

同じ通知を同じ手段で二重に送らない

INSERT INTO notification_deliveries (notification_id, channel)
SELECT n.id, 'email' FROM notifications n
WHERE n.user_id = 42 AND n.dedupe_key = 'reservation_approved:310'
ON CONFLICT (notification_id, channel) DO NOTHING;

INSERT 0 1
INSERT 0 0

送信する側は、pending の行を取り出して送り、結果を書き戻します。複数のワーカーが同じ行を取らないよう、FOR UPDATE SKIP LOCKED を使います。

WITH picked AS (
  SELECT id FROM notification_deliveries
  WHERE status = 'pending' ORDER BY id LIMIT 10
  FOR UPDATE SKIP LOCKED
)
UPDATE notification_deliveries d
SET status = 'sent', sent_at = now()
FROM picked WHERE d.id = picked.id
RETURNING d.id, d.notification_id, d.channel, d.status;

 id | notification_id | channel | status
----+-----------------+---------+--------
  1 |          200001 | email   | sent

プッシュ・メール・アプリ内の使い分け

MDN の Push API のページでは、プッシュは Web アプリが前面に無いときや読み込まれていないときでも受け取れること、利用者が受け取りに同意（オプトイン）していること、有効な Service Worker が必要なことが説明されています。プッシュは「許可した人にだけ届く」手段なので、プッシュだけに載せた通知は、許可していない人には届きません。

緊急度と到達性で選ぶ

アプリ内の通知行（notifications）を先に作り、プッシュとメールは「その通知をどの手段でも送るか」という追加の行（notification_deliveries）で表すと、手段を増やしてもテーブルの形が変わりません。

通知種別ごとの受信設定テーブル

受信設定は、種別のマスタに既定値を持ち、利用者が変えた分だけ行を持つ形にします。全利用者×全種別×全手段の行を先に作らずに済み、種別を増やしたときも既定値が自動で効きます。

-- 会員42の設定：承認メールを止める、コメントのプッシュを受け取る、（セキュリティのメールを止めようとする）
INSERT INTO notification_preferences (user_id, type, channel, enabled) VALUES
  (42, 'reservation_approved', 'email', false),
  (42, 'comment', 'push', true),
  (42, 'security', 'email', false);

-- 種別×手段ごとに、送るかどうかを決める
SELECT t.type, c.channel,
       CASE WHEN t.mandatory THEN
              CASE c.channel WHEN 'in_app' THEN t.default_in_app
                             WHEN 'email'  THEN t.default_email
                             ELSE t.default_push END
            ELSE COALESCE(p.enabled,
              CASE c.channel WHEN 'in_app' THEN t.default_in_app
                             WHEN 'email'  THEN t.default_email
                             ELSE t.default_push END)
       END AS send,
       CASE WHEN t.mandatory THEN '必須（変更不可）'
            WHEN p.enabled IS NULL THEN '既定'
            ELSE '本人の設定' END AS source
FROM notification_types t
CROSS JOIN (VALUES ('in_app'), ('email'), ('push')) AS c(channel)
LEFT JOIN notification_preferences p
       ON p.user_id = 42 AND p.type = t.type AND p.channel = c.channel
ORDER BY t.type, c.channel;

         type         | channel | send |      source
----------------------+---------+------+------------------
 comment              | email   | f    | 既定
 comment              | in_app  | t    | 既定
 comment              | push    | t    | 本人の設定
 reservation_approved | email   | f    | 本人の設定
 reservation_approved | in_app  | t    | 既定
 reservation_approved | push    | t    | 既定
 reservation_remind   | email   | f    | 既定
 reservation_remind   | in_app  | t    | 既定
 reservation_remind   | push    | t    | 既定
 security             | email   | t    | 必須（変更不可）
 security             | in_app  | t    | 必須（変更不可）
 security             | push    | f    | 必須（変更不可）

security は mandatory が true なので、本人がメールを止める行を入れても、送る（t）のままです。画面で止められないようにするだけでなく、送るかどうかを決める SQL の側でも守っています。

MySQL 8.0 での書き方

CREATE TABLE notification_types (
  type            varchar(50)  PRIMARY KEY,
  label           varchar(100) NOT NULL,
  default_in_app  boolean NOT NULL DEFAULT true,
  default_email   boolean NOT NULL DEFAULT false,
  default_push    boolean NOT NULL DEFAULT false,
  mandatory       boolean NOT NULL DEFAULT false
);

CREATE TABLE notifications (
  id          bigint AUTO_INCREMENT PRIMARY KEY,
  user_id     bigint       NOT NULL,
  type        varchar(50)  NOT NULL,
  title       varchar(200) NOT NULL,
  body        text         NOT NULL,
  link_path   varchar(500),
  dedupe_key  varchar(100) NOT NULL,
  read_at     datetime(6),                          -- NULL なら未読
  created_at  datetime(6)  NOT NULL DEFAULT CURRENT_TIMESTAMP(6),
  UNIQUE KEY notifications_dedupe (user_id, dedupe_key),
  KEY notifications_list_idx (user_id, created_at DESC, id DESC),
  KEY notifications_unread_idx (user_id, read_at),  -- 部分インデックスが無いので複合インデックスで代える
  CONSTRAINT notifications_type_fk FOREIGN KEY (type) REFERENCES notification_types (type)
);

CREATE TABLE announcements (
  id            bigint AUTO_INCREMENT PRIMARY KEY,
  title         varchar(200) NOT NULL,
  body          text         NOT NULL,
  published_at  datetime(6)  NOT NULL,
  expires_at    datetime(6),
  KEY announcements_published_idx (published_at DESC)
);

CREATE TABLE announcement_reads (
  user_id          bigint NOT NULL,
  announcement_id  bigint NOT NULL,
  read_at          datetime(6) NOT NULL DEFAULT CURRENT_TIMESTAMP(6),
  PRIMARY KEY (user_id, announcement_id),
  CONSTRAINT announcement_reads_fk FOREIGN KEY (announcement_id) REFERENCES announcements (id) ON DELETE CASCADE
);

CREATE TABLE notification_preferences (
  user_id   bigint      NOT NULL,
  type      varchar(50) NOT NULL,
  channel   varchar(10) NOT NULL,
  enabled   boolean     NOT NULL,
  PRIMARY KEY (user_id, type, channel),
  CONSTRAINT notification_preferences_channel CHECK (channel IN ('in_app', 'email', 'push')),
  CONSTRAINT notification_preferences_type_fk FOREIGN KEY (type) REFERENCES notification_types (type)
);

CREATE TABLE notification_deliveries (
  id               bigint AUTO_INCREMENT PRIMARY KEY,
  notification_id  bigint      NOT NULL,
  channel          varchar(10) NOT NULL,
  status           varchar(10) NOT NULL DEFAULT 'pending',
  sent_at          datetime(6),
  UNIQUE KEY notification_deliveries_once (notification_id, channel),
  CONSTRAINT notification_deliveries_channel CHECK (channel IN ('email', 'push')),
  CONSTRAINT notification_deliveries_status CHECK (status IN ('pending', 'sent', 'error')),
  CONSTRAINT notification_deliveries_fk FOREIGN KEY (notification_id) REFERENCES notifications (id) ON DELETE CASCADE
);

PostgreSQL との違い

未読件数と EXPLAIN

EXPLAIN ANALYZE
SELECT COUNT(*) FROM notifications WHERE user_id = 42 AND read_at IS NULL;

-> Aggregate: count(0)  (cost=1.25 rows=1) (actual time=0.0126..0.0127 rows=1 loops=1)
    -> Filter: (notifications.read_at is null)  (cost=0.753 rows=5) (actual time=0.00737..0.0106 rows=5 loops=1)
        -> Covering index lookup on notifications using notifications_unread_idx (user_id=42, read_at=NULL)  (cost=0.753 rows=5) (actual time=0.00682..0.00967 rows=5 loops=1)

複合インデックスの中だけで5行を読んで数えています（Covering index lookup）。このインデックスを使わせない指定（IGNORE INDEX）で実行すると、その人の100行を読んでから絞り込む形になりました。

-> Aggregate: count(0)  (cost=27 rows=1) (actual time=0.196..0.196 rows=1 loops=1)
    -> Filter: (notifications.read_at is null)  (cost=26 rows=10) (actual time=0.056..0.194 rows=5 loops=1)
        -> Index lookup on notifications using notifications_dedupe (user_id=42)  (cost=26 rows=100) (actual time=0.0541..0.189 rows=100 loops=1)

一覧の続き・既読・重複防止

-- 一覧の続き
SELECT id, title, read_at IS NULL AS unread, created_at
FROM notifications
WHERE user_id = 42
  AND (created_at < '2026-09-25 09:00:00'
       OR (created_at = '2026-09-25 09:00:00' AND id < 4196))
ORDER BY created_at DESC, id DESC
LIMIT 3;

+------+----------------------------------------------+--------+----------------------------+
| id   | title                                        | unread | created_at                 |
+------+----------------------------------------------+--------+----------------------------+
| 4195 | 予約 #95 にコメントが付きました              |      0 | 2026-09-25 03:00:00.000000 |
| 4194 | 予約 #94 にコメントが付きました              |      0 | 2026-09-24 21:00:00.000000 |
| 4193 | 予約 #93 にコメントが付きました              |      0 | 2026-09-24 15:00:00.000000 |
+------+----------------------------------------------+--------+----------------------------+

-- 1件を既読（2回実行）→ ROW_COUNT() は 1、0
UPDATE notifications SET read_at = NOW(6) WHERE id = 4200 AND user_id = 42 AND read_at IS NULL;
SELECT ROW_COUNT();

-- すべて既読 → ROW_COUNT() は 4
UPDATE notifications SET read_at = NOW(6) WHERE user_id = 42 AND read_at IS NULL;

-- お知らせを既読（2回実行）→ ROW_COUNT() は 1、0
INSERT IGNORE INTO announcement_reads (user_id, announcement_id) VALUES (42, 50);

-- お知らせをすべて既読 → ROW_COUNT() は 2
INSERT IGNORE INTO announcement_reads (user_id, announcement_id)
SELECT 42, a.id FROM announcements a
WHERE a.published_at <= NOW(6) AND (a.expires_at IS NULL OR a.expires_at > NOW(6));

-- 同じ出来事の通知（2回実行）→ ROW_COUNT() は 1、0
INSERT IGNORE INTO notifications (user_id, type, title, body, link_path, dedupe_key)
VALUES (42, 'reservation_approved', '予約 #310 が承認されました', '本文',
        '/reservations/310', 'reservation_approved:310');

件数は PostgreSQL と同じ結果でした。INSERT IGNORE を付けない場合と、CHECK 制約に合わない通知手段を入れた場合のエラーです。

ERROR 1062 (23000) at line 60: Duplicate entry '42-reservation_approved:310' for key 'notifications.notifications_dedupe'
ERROR 3819 (HY000) at line 56: Check constraint 'notification_preferences_channel' is violated.

INSERT IGNORE は、重複以外のエラーも警告に変えます。title（NOT NULL）に NULL を指定して INSERT IGNORE を実行すると、エラーにならず、title が空文字の行が登録されました。

INSERT IGNORE INTO notifications (user_id, type, title, body, dedupe_key)
VALUES (42, 'comment', NULL, '本文', 'comment:null-title');
SHOW WARNINGS;

+---------+------+-------------------------------+
| Level   | Code | Message                       |
+---------+------+-------------------------------+
| Warning | 1048 | Column 'title' cannot be null |
+---------+------+-------------------------------+

重複だけを無視したいときは ON DUPLICATE KEY UPDATE id = id を使います（同じ重複データで実行し、ROW_COUNT() が 0 になりました）。

たまった通知の扱い

期間の数値は例です。保存期間を決めておくと、既読の行が増え続けることを前提にしなくて済みます。

確かめて分かったこと

• 未読件数の問い合わせで読むバッファは、未読用のインデックスの有無で 103 と 7 の差になりました。 1人100件の時点でこの差で、既読の行が増えるほど広がります。
• 部分インデックスと、全行を対象にした複合インデックスは、読み取りの量がほぼ同じ（7 と 8）でした。 違いは大きさで、192 kB と 6000 kB です。未読の割合が小さいほど、部分インデックスの利点が大きくなります。
• 既読行が1行も無い利用者でも、NOT EXISTS で未読を正しく数えられました（50件）。全員分の未読行を先に作る必要はありません。
• 全員分の行を先に作る形だと 100,000 行、読んだ人だけ足す形だと 9,453 行でした（会員2,000人、お知らせ50件、読んだのは1割の会員）。
• 既読の UPDATE に read_at IS NULL を入れると、2回目は 0 件になり、最初の既読時刻が保たれました。
• mandatory の種別は、本人が止める設定行を入れても送る判定のままでした。設定画面だけでなく、判定の SQL でも守れます。
• MySQL 8.0.46 では、CHECK 制約に合わない通知手段はエラー 3819 で登録されませんでした。 通知手段の値の揺れを、テーブル定義で防げます。
• MySQL の INSERT IGNORE は、NOT NULL の列に NULL を入れた行を空文字で登録しました。 重複の無視には ON DUPLICATE KEY UPDATE のほうが意図に合います。

よくある質問

Q. 既読・未読はフラグ（true／false）と時刻（read_at）のどちらで持つのがよいですか？ A. 時刻で持つのがおすすめです。NULL かどうかで未読を判定でき、「いつ読んだか」も同じ列で分かります。

Q. 全員あてのお知らせは、利用者ごとに行を作る必要がありますか？ A. 必要ありません。本体を1行だけ作り、読んだ人の分だけ既読行を足せば、行が無い人を未読として数えられます。

Q. 未読件数の取得が遅いときはどうすればよいですか？ A. 未読の行だけを対象にしたインデックスを作ります。PostgreSQL は WHERE read_at IS NULL の部分インデックス、MySQL は (user_id, read_at) の複合インデックスです。あわせて、バッジの上限で数えるのを打ち切ります。

Q. プッシュ通知とアプリ内通知の違いは何ですか？ A. プッシュはアプリを開いていない人に気づいてもらう手段、アプリ内は開いた人に一覧として見せる手段です。プッシュは利用者の許可が要るので、アプリ内の通知を基本にしてプッシュを足します。

Q. 同じ通知が2回届くときは、どこを直せばよいですか？ A. 通知の登録と送信記録に一意キーを付けます。登録は「受信者＋出来事のキー」、送信は「通知＋手段」を一意にし、重複した INSERT が何もしないようにします。

まとめ

• 個人あては受信者ごとの行に read_at、全員あては本体1行＋読んだ人だけ既読行の2本立てにする。
• 既読の持ち方は A：行ごとの read_at／B：既読テーブル／C：最終閲覧時刻 の3つ。件数と「1件ずつ既読を見せるか」で選ぶ。
• 未読バッジは未読の行だけのインデックスで数える。PostgreSQL の部分インデックスは、全行の複合インデックスの約31分の1の大きさだった。
• 既読の UPDATE には read_at IS NULL、既読行の INSERT には重複を無視する指定を入れ、何度実行しても結果が同じになるようにする。
• 通知手段はアプリ内を基本に、緊急度が高いものへプッシュ、記録として残すものへメールを足す。
• 受信設定は種別マスタの既定値＋変えた分だけの行。必ず伝える種別は判定の SQL でも守る。
• 二重登録・二重送信は一意キーで止める。

参考・出典

• MDN Web Docs「Push API」: https://developer.mozilla.org/en-US/docs/Web/API/Push_API

株式会社RJC ― SI事業・SES事業・AI駆動開発。RJCは一緒に成長を楽しめる会社です。