DBのテーブル構成を考えてください。…
- User
DBのテーブル構成を考えてください。
目的としてはチャット画面で「ユーザーからのメッセージ」「システムが送信したメッセージ」を同じ画面でタイムライン表示したいです。画像や動画はファイルに保存し、ファイルパスのみDB保存します。
ブロードキャストなど、多人数に同時に送った場合のメッセージを、複製して保持するか、デリバーテーブルなどを用意してノーマライズするかを迷っています。
MySQLですが、数千万行になってもパフォーマンスが極端に劣化しない方式が良いです。
【記録したいデータ】
- LINE公式アカウントでユーザーからWebhook経由で受信したイベント
最低保持したい内容としては、送信先チャネルID、送信元、日付時刻、イベントID、イベントタイプ、ソースタイプ、メッセージタイプ、メッセージ内容
種類: /**
* イベントタイプ */ const WH_EVENT_TYPE = array( 1 => 'message', 2 => 'unsend', 3 => 'follow', 4 => 'unfollow', 5 => 'join', 6 => 'leave', 7 => 'memberJoined', 8 => 'memberLeft', 9 => 'postback', 10 => 'videoPlayComplete', 11 => 'beacon', 12 => 'accountLink', 13 => 'things', 14 => 'membership', );ソース元はユーザー、グループ、ルーム
メッセージの場合はさらにタイプ分類がある。payloadはJSONで保存。
/** * メッセージタイプ */ const WH_MESSAGE_TYPE = array( 1 => 'text', 2 => 'image', 3 => 'video', 4 => 'audio', 5 => 'file', 6 => 'location', 7 => 'sticker', );-
アプリからLINE公式アカウントの友達(ユーザー)へ送信したメッセージ
種類: ブロードキャスト(すべての友達へ)/マルチキャスト(複数の友達)/プッシュ(一人ずつ)/reply(応答なので一人)
最低保持したい内容: ソース(送信経路:一斉配信なのかチャット画面からなのか、オーディエンスからなのか)、送信者(WP ユーザーIDorSystemなど)、送信先、メッセージタイプ、メッセージ内容(payloadはJSONでOK)、ステータス
- Assistant
以下では、
①「構造がシンプルで分かりやすい」
②「ブロードキャスト/マルチキャストでも行数爆発を起こさない」
③「1ユーザーのタイムラインを高速で引ける」を同時に満たすことを目標に MySQL 向けの論理設計を提案します。
※RDB のみで完結する案です(NoSQL 併用などは考慮外)。
1. 全体俯瞰
line_channels (LINEチャネル) line_contacts (友だち・グループ・ルーム) messages (論理メッセージ本体) ★=数千万行になるテーブル deliveries (配信先 × メッセージ) ★=行数爆発対策のキモ media_files (画像/動画/ファイルの実体) webhook_events (LINE→システムの生Webhookログ) message_types (マスタ) event_types (マスタ) delivery_status (マスタ) wp_users (送信者:WordPressユーザー) ※既存テーブルを利用[image: ER図] open
(簡易ER図/矢印はFK)
2. テーブル定義(DDLイメージ)
-- 1) チャネル(LINE公式アカウント) CREATE TABLE line_channels ( channel_id INT UNSIGNED NOT NULL AUTO_INCREMENT, line_channel_id VARCHAR(50) NOT NULL, -- LINE側のID name VARCHAR(100) NOT NULL, PRIMARY KEY(channel_id), UNIQUE(line_channel_id) ); -- 2) 友だち・グループ・ルーム(送受信主体) CREATE TABLE line_contacts ( contact_id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT, channel_id INT UNSIGNED NOT NULL, line_id VARCHAR(50) NOT NULL, -- userId / groupId / roomId source_type ENUM('user','group','room') NOT NULL, display_name VARCHAR(100), PRIMARY KEY(contact_id), UNIQUE(channel_id, line_id), FOREIGN KEY(channel_id) REFERENCES line_channels(channel_id) ); -- 3) メッセージ本体(入出力両方を格納) CREATE TABLE messages ( message_id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT, channel_id INT UNSIGNED NOT NULL, -- どのLINEチャネルか direction ENUM('in','out') NOT NULL, -- in=受信, out=送信 -- 送信者:in時=contact_id、out時=wp_user_id or NULL(system) sender_contact_id BIGINT UNSIGNED, sender_wp_id BIGINT UNSIGNED, event_type_id TINYINT UNSIGNED, -- inで使用 message_type_id TINYINT UNSIGNED, -- text/image/... content_json JSON NOT NULL, -- payload全文 created_at DATETIME NOT NULL, -- 索引 PRIMARY KEY(message_id), INDEX idx_channel_created (channel_id, created_at), INDEX idx_sender_contact (sender_contact_id), FOREIGN KEY(channel_id) REFERENCES line_channels(channel_id), FOREIGN KEY(sender_contact_id) REFERENCES line_contacts(contact_id), FOREIGN KEY(sender_wp_id) REFERENCES wp_users(ID), FOREIGN KEY(event_type_id) REFERENCES event_types(event_type_id), FOREIGN KEY(message_type_id) REFERENCES message_types(message_type_id) ) ENGINE=InnoDB; -- 4) 配信テーブル:メッセージ × 受信者 CREATE TABLE deliveries ( delivery_id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT, message_id BIGINT UNSIGNED NOT NULL, recipient_id BIGINT UNSIGNED NOT NULL, -- line_contacts.contact_id status_id TINYINT UNSIGNED NOT NULL, -- 送信済/失敗/既読... delivered_at DATETIME, read_at DATETIME, PRIMARY KEY(delivery_id), UNIQUE(message_id, recipient_id), -- 同一組合せは1行 INDEX idx_recipient_time (recipient_id, delivered_at), FOREIGN KEY(message_id) REFERENCES messages(message_id), FOREIGN KEY(recipient_id) REFERENCES line_contacts(contact_id), FOREIGN KEY(status_id) REFERENCES delivery_status(status_id) ) ENGINE=InnoDB PARTITION BY HASH(recipient_id) PARTITIONS 32; -- 🔑 パーティショニング -- 5) 画像/動画/ファイル CREATE TABLE media_files ( media_id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT, message_id BIGINT UNSIGNED NOT NULL, media_type ENUM('image','video','audio','file') NOT NULL, file_path VARCHAR(255) NOT NULL, thumbnail_path VARCHAR(255), metadata_json JSON, PRIMARY KEY(media_id), FOREIGN KEY(message_id) REFERENCES messages(message_id) ); -- 6) Webhook の生イベント CREATE TABLE webhook_events ( event_id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT, message_id BIGINT UNSIGNED, -- messagesへ後から紐付け可 raw_payload JSON NOT NULL, received_at DATETIME NOT NULL, PRIMARY KEY(event_id), INDEX idx_received_at (received_at) ); -- 7) マスタ類 CREATE TABLE event_types ( event_type_id TINYINT UNSIGNED PRIMARY KEY, name VARCHAR(30) NOT NULL ); /* 例: INSERT INTO event_types VALUES (1,'message'),(2,'unsend'),(3,'follow'), ... ; */ CREATE TABLE message_types ( message_type_id TINYINT UNSIGNED PRIMARY KEY, name VARCHAR(20) NOT NULL ); CREATE TABLE delivery_status ( status_id TINYINT UNSIGNED PRIMARY KEY, name VARCHAR(20) NOT NULL ); /* 1=queued,2=sent,3=fail,4=read ... */
3. なぜ「messages + deliveries」方式を採用するか?
方式 メリット デメリット ① 受信者ごとにメッセージを複製 JOIN が不要。単一テーブルで完結 ブロードキャストで行数が n倍 (最悪×数十万) ② 本体+デリバリーテーブル(採用) 行数爆発を受信者数のみに隔離。スタンプ・画像等の重複保存も防止 タイムライン取得時に JOIN が必要 ✔︎ 数千万行規模になるのは「messages」のみ。
✔︎ ブロードキャスト 1通 → deliveries 100万行増えても、
recipient_id でハッシュパーティショニングしておけば 1ユーザーのタイムライン取得は 1パーティションしか走らない のでスケールしやすい。
4. タイムライン取得クエリ例
「A さん(contact_id=?)のタイムライン」を取得。
a) Aさんからの受信メッセージ (=sender_contact_id に本人)
b) Aさん宛ての送信メッセージ (=deliveries.recipient_id に本人)
を UNION して時系列ソート:( -- in(受信) SELECT m.message_id, m.direction, m.created_at, m.message_type_id, m.content_json FROM messages m WHERE m.sender_contact_id = :contact_id ) UNION ALL ( -- out(配信) SELECT m.message_id, m.direction, m.created_at, m.message_type_id, m.content_json FROM deliveries d JOIN messages m ON m.message_id = d.message_id WHERE d.recipient_id = :contact_id ) ORDER BY created_at ASC LIMIT 50 OFFSET :offset;ポイント
・deliveries にINDEX(recipient_id, delivered_at)があるので INDEX RANGE + PK で高速。
・messages 側は PK で直接参照。
5. パフォーマンス・運用 Tips
-
ID 型
BIGINT UNSIGNED(約 9.2e18)で安心。- 自動増分はほぼ挿入順=時間順なのでクラスタリング効果も得られる。
-
パーティショニング
- deliveries を
HASH(recipient_id)(orLINEAR HASH) で 32〜256 分割。
⇒ 1ユーザー照会は単一パーティションに限定。 - messages は 日付 RANGE (月単位)で古いデータをアーカイブしやすく。
- deliveries を
-
カバーリングインデックス
- タイムラインでよく使う列(recipient_id, delivered_at, message_id)を含める。
- JSON の中身検索が必要であれば
GENERATED COLUMN+INDEXで対応。
-
画像・動画
- ファイルは S3 等に置き
media_files.file_pathに URI を保存。 - 画像複数枚でも media_files が1:Nで管理。
- ファイルは S3 等に置き
-
「既読」管理
- deliveries の
read_at IS NULL/NOT NULLで表現。 - 既読通知が来るたびに UPDATE。
- deliveries の
-
TTL / アーカイブ
- 例えば messages を 2年で分割テーブルに抜き、deliveries も同時に移すスクリプトを用意。
- 参照頻度が下がる古いデータは別サーバへ MySQL 8 の
TABLESPACE移動等でコスト節約。
6. 使い分けまとめ
・1対1メッセージ ➜
messages1行 +deliveries1行
・マルチキャスト(10人) ➜messages1行 +deliveries10行
・ブロードキャスト(全友だち100万) ➜messages1行 +deliveries100万行
→ 行数は増えるが、問い合わせは recipient_id で分散されるため実運用では問題になりにくい。
7. 今後拡張イメージ
- リアクション(いいね等)
message_reactions(message_id, contact_id, type, reacted_at) - スレッド(reply_to)
messagesにparent_message_idを持たせる - ボット or AI 応答ログ
sender_wp_id = NULL+sender_contact_id = NULL,direction='out'
8. まとめ
・「メッセージ本体は1行だけ保存」「誰に届いたかは deliveries で管理」というオーソドックスな正規化を選択。
・1ユーザーのタイムライン取得に必要な行は deliveries の 当該ユーザー分だけ なので、総行数が億に達してもスケールしやすい。
・パーティショニング & 適切なインデックスで数千万行規模の MySQL でも十分実績のある構成。以上をベースに実装すれば、要件(タイムライン表示・高パフォーマンス・ブロードキャスト対応)を満たせるはずです。👍
-
Other models, same or similar prompt
Responses were produced in different, uncontrolled contexts (system prompts, settings, prior turns). This is not a benchmark.