レガシーで硬直したテーブル設計から変更容易で柔軟なテーブル設計にする

2025/11/15に開催されたJJUG CCC 2025 Fall( https://jjug.doorkeeper.jp/events/190868 )の登壇資料です。

Transcript

1. レガシーで硬直したテーブル設計から 変更容易で柔軟なテーブル設計にする 2025/11/15 JJUG CCC 2025 Fall #jjug_ccc_a
2. 自己紹介 佐々木 興平(ささきこうへい ) - 株式会社 Red Frasco(2025/08〜) - シニアソフトウェアエンジニア ◆ 経歴 株式会社Red Frasco (exエキサイト,exセレス,exCA,exぐるなび,exQUICK) ◆ その他コメント - キャリアはバックエンド畑を中心 - 組織マネジメント・プロセス改善も随時実施 - 教科書通りにやるのが苦手 2
3. 所属企業 3
4. 免責事項 4 本発表の内容は、スピーカー個人の見解であり、所属する組織・企業 の公式見解を示すものではありません。 本発表で紹介する内容は、スピーカーの個人的な経験に基づいて実 践してきたものであり、同様の効果や成果を保証するものではありま せん。 本発表の内容を参考にした結果、いかなる不利益や損害が生じた場 合においても、スピーカーおよび関係者は一切の責任を負いかねます ので、予めご了承ください。
5. はじめに 5 スライドは作成していますが、皆さんの意見を聞きながら進めたいで す。なので、色々反応してもらえると嬉しいです。
6. 話すこと、話さないこと 6 話すこと - レガシーなテーブル設計まわり - DBの歴史 - 変更容易で柔軟なテーブル設計まわり - 実践手順の一部 話さないこと - Javaのテクニック的なもの - DBの基礎知識
7. 目次 データを管理するのに何を使っていますか？ データベースの歴史 レガシーなテーブル設計のイメージ レガシーなテーブル設計はなぜ発生するのか？ テーブル設計の方法とは？ テーブル設計の難しさ 変更容易で柔軟なテーブル設計にするには？ 導入に際して実際にあったQ&A まとめ まとめ
8. 8 データを管理するのに何を使っていますか？
9. データを管理するのに何を使っていますか？ 9 1. ファイル 2. NoSQL(Firebase, MongoDB, Supabase) 3. RDB 4. 他
10. データを管理するのに何を使っていますか？ 10 https://db-engines.com/en/ranking 上位はRDB
11. 11 データベースの歴史
12. データベースの歴史 12 RDB以前 ~1970年 階層型データベース ネットワーク型 データベース リレーショナルデータベース RDB 1970年〜 NoSQL 2000年〜 関係モデル(集合論と述 語論理)に基づいてSQLで データ操作が可能、ACID 特性を担保 GraphDB Key-Value NewSQL 2010年〜 大量リクエストの処 理が可能、ACID特性 はほとんどない (MongoDBは例外) 基本的なRDBの仕様 をある程度保持しつ つ、大量リクエスト処 理が可能、ACID特性 を担保
13. データベースの歴史 13 RDB以前 ~1970年 階層型データベース ネットワーク型 データベース リレーショナルデータベース RDB 1970年〜 NoSQL 2000年〜 関係モデル(集合論と述 語論理)に基づいてSQLで データ操作が可能、ACID 特性を担保 GraphDB Key-Value NewSQL 2010年〜 大量リクエストの処 理が可能、ACID特性 はほとんどない (MongoDBは例外) 基本的なRDBの仕様 をある程度保持しつ つ、大量リクエスト処 理が可能、ACID特性 を担保
14. データベースの歴史 14 RDB以前 ~1970年 階層型データベース ネットワーク型 データベース リレーショナルデータベース RDB 1970年〜 NoSQL 2000年〜 関係モデル(集合論と述 語論理)に基づいてSQLで データ操作が可能、ACID 特性を担保 GraphDB Key-Value NewSQL 2010年〜 大量リクエストの処 理が可能、ACID特性 はほとんどない (MongoDBは例外) 基本的なRDBの仕様 をある程度保持しつ つ、大量リクエスト処 理が可能、ACID特性 を担保 データベースは進化している
15. 15 レガシーなテーブル設計のイメージ
16. レガシーなテーブル設計のイメージ 16 正規化の不足 - データ重複が多い - 更新異常、削除異常、挿入異常が発生しやすい 命名規則 - テーブル名、カラム名が不明瞭(etc. column_1,column_a) - 一貫性のない命名ルール データ型が不適切 - すべてを文字列型で保存
17. レガシーなテーブル設計のイメージ 17 設計上の問題 - 更新回数のカラムだけど特定の数字(例: 99)だけ特別な意味 - インデックスの欠乏もしくは過剰 - カラム数が多すぎる神テーブル - データ量が増えてくるとパフォーマンスが急落する 保守性の低さ - 不要なカラムがそのまま残っている(削除できない) - 予備カラム(space_1,space_2, memo_1,memo_2 etc..)
18. (スピーカーが思う )レガシーなテーブル設計のイメージ 18 性能上の問題 - カーディナリティの低いカラムにインデックスが張られている - (効果が薄い)外部キーの濫用 - insert、update、deleteが遅い - 過剰な正規化によるパフォーマンスの劣化 - 論理削除の濫用によるデータ量増加でのパフォーマンス劣化 設計上の問題 - updated_atが何を更新した時刻か分からない - フラグカラムの乱立 - 動的カラム名(campaign_2024, campaign_2025) - データ項目の追加時に影響範囲が読めない = 調査工数が増える
19. レガシーなテーブル設計はなぜ発生するのか？ 19
20. レガシーなテーブル設計はなぜ発生するのか？ 20 そもそも.... - データが増えなければ、初期設計は良い設計 になっている(はず) - 項目が増えなければ、初期設計は良い設計 になっている(はず) - 項目追加 = カラム追加の思考 になっている - カラム追加の方が工数が節約できる (と思っている) - テーブルを追加するのは謎の承認フローが必要(だったりする？) - フレームワークが勝手に テーブル設計をしてくれるから関心がない - テーブル設計の方法は特に教えてもらっていない - GoogleやAIに聞いても、あんまり明確な答えが返ってこない
21. テーブル設計の方法とは？ 21
22. テーブル設計の方法とは？ 22 アプリケーションコードは設計方法が豊富 - MVC - Clean Architecture - MVVM - ドメイン駆動開発(DDD) - Vertical Slice Architecture - Onion Architecture - Layered Architecture - Functional Programming - Object Oriented Programming
23. テーブル設計の方法とは？ 23 アプリケーションコードは設 計方法が豊富 - MVC - Clean Architecture - MVVM - ドメイン駆動開発(DDD) - Vertical Slice Architecture - Onion Architecture - Layered Architecture - Functional Programming - Object Oriented Programming 種類も豊富で情報もたくさん 進化している感じがする！
24. テーブル設計の方法とは？ 24 テーブル設計は？ - 要件の洗い出し - 概念設計 - 物理設計 - 論理設計 - 整理 - 正規化 - テーブル定義書 - SQLアンチパターン？
25. テーブル設計の方法とは？ 25 - テーブル設計は？ - 要件の洗い出し - 概念設計 - 物理設計 - 論理設計 - 整理 - 正規化 - テーブル定義書 - SQLアンチパターン？ どの説明も抽象度が高すぎて、具体が イメージできない。さらに、時間軸ベー スの説明や対処法がないので、データ が肥大化すると詰む。 進化している感じがしない。
26. テーブル設計の方法とは？ 26 - 結局のところ... - どうテーブル設計をした方が良いかがわかってない - わかった気になって、なんとなくやってしまっている - Excelやスプレッドシートをイメージしてしまっているとレガシーになりがち(経験則)
27. 27 テーブルが格納するデータの特性や重要性
28. テーブルが格納するデータの特性や重要性 28 データとコードとライフタイム - データ: 数年〜数十年 - コード: 数カ月〜数年(短くなる傾向にある) データ コード 20年 10年 5年 0年 データの方が圧倒的に長い
29. テーブルが格納するデータの特性や重要性 29 データとコードの経過年数と物量 - データ: 経過年数 * 利用頻度で積み上がっていく - コード: 経過年数でコードは増加するが、初期に書かれたコードは減少する サービスが成長するとコードも増加するが、データ も肥大化する。しかし、データは過去のものを開発 者の都合で容易には変更できない。そして変更し ないほうがよい。
30. 30 テーブル設計の難しさ
31. テーブル設計の難しさ - 正解がわからない(それなりに動作してくれる) - 未来はわからない のに、拡張が難しい - 過去の変更はできない (できてもかなりしづらい) 31 未来 過去 現在 初期構築では良い設計 変更すると不具合がでる 全くわからない わかる範囲
32. 32 変更容易で柔軟なテーブル設計にするには？
33. 変更容易で柔軟なテーブル設計にするには？ 今までのスライドの課題を折り込んだものにする - 世の中に落ちてるテーブル設計論に寄りかからない - 過去に対する変更を行わない - 未来への変更を折り込む - データ量の増加 - 格納する項目の追加 - 利用しなくなった項目の削除 - 利用しなくなったデータの削除 - サービス仕様変更によるデータの扱われ方の変更 - 一般的ではないテーブル設計方法を採用する覚悟(大事) 33
34. 変更容易で柔軟なテーブル設計にするには？ - 抽象度が高く、理解しきれない概念に依存しない - フレームワークの自動生成等に依存し(すぎ)ない - 時間軸による変化を意識するようにする 34
35. 変更容易で柔軟なテーブル設計にするには？ 過去に対する変更を行わない ALTER文やUPDATE文を極力使用しない。 - ALTER文を実行するデメリット - カラム追加により、過去の設計に改変を加えている - 過去のデータのデフォルト値どうするんだっけ？など余計なことを考える - ALTER文の実行中にロックがかかったりする(Online DDLでもある) - アプリケーションのロールバック時に手順が発生する - UPDATE文を実行するデメリット - いつ更新されたかわからない。(updated_atを更新し忘れ問題) - updated_atが変更されても何を更新したのかわからない - 通常のビジネスロジックを通ってきてない可能性もある 35
36. 変更容易で柔軟なテーブル設計にするには？ 過去に対する変更を行わない - ALTER文 -> CREATE TABLE文 + INNER JOIN文で対応 - 既存テーブルのデータの過去改変が発生しない - 新テーブルに必要なデータだけが入り, null大量のカラムとかできない - UPDATE文 -> DELETE & INSERT文で対応 - 最新のデータのみを保持するテーブルにする - 一度にINSERTできるくらいのテーブルに分割する - updated_atなどは不要 - 変更履歴が必要な場合は、${テーブル名}_logテーブルを作成し格納 36
37. 変更容易で柔軟なテーブル設計にするには？ 過去に対する変更を行わない よくある質問 - ALTER文 -> CREATE TABLE文 + INNER JOIN文で対応 - Q. JOINが多くて性能劣化になりそう - A. なってからいいましょう - A. 1対1のデータなら気にすることない - A. 必ず一緒になるデータは集約作業を行いJOIN数を削減する(後述) - A. OUTER JOIN も使用は可 37
38. 変更容易で柔軟なテーブル設計にするには？ 過去に対する変更を行わない - 集約作業について - 複数のテーブルを1テーブルにする作業のこと - 手順(右のER図を使用して) - news_contentテーブルを作成 - news_tilte, news_article, news_contentに更新処理をダブルライト - 参照をnews_contentに変更 - news_title, news_articleの更新を停止 - news_title, news_articleのテーブル名をリネーム - news_title, news_articleを削除 - 約2〜4週間かけて段階的に行うのがオススメ - サービス無停止で集約作業は可能。日中にやっても問題ない。 38
39. 変更容易で柔軟なテーブル設計にするには？ 過去に対する変更を行わない - UPDATE文 -> DELETE & INSERT文で対応 - Q. 常に最新のデータしかみられなくなる - A. ${テーブル名}_logテーブルを使用して過去データを参照可能 - Q. 全部created_atにしていいのか？ - A. 問題なし。変更がされないことが担保されていた方がいい - Q. 外部キーが使えないのでは？ - A. 使えません。更新処理が局所化されるので、バリデーション等が毎回すべて通る思想 で使用する。どうしても外部キーを使用する場合は、UPSERTなどを使用すること。 39
40. 変更容易で柔軟なテーブル設計にするには？ 未来に対する変更を折り込む - テーブル追加時に実行計画とセットでレビューする - 使用しなくなったテーブルはすぐに削除を行う - 手順 - 参照が外れているかを確認(SQLファイルでテーブル名をGrepで可能) - テーブルをリネームする - テーブルをドロップする - データ量の増加に対応する - パーティションで対応(アプリケーションの変更が不要) - シャーディングで対応(アプリケーションの変更が必要) - New SQLで対応(アプリケーションの変更が必要 & 金の力で叩く) 40
41. 変更容易で柔軟なテーブル設計にするには？ 未来に対する変更を折り込む - サービス仕様変更によるデータの扱われ方の変更 - 例) フラグや状態をサービス仕様の都合で変更したい - フラグ専用テーブル(原則1フラグ1テーブル)を用意する - 状態テーブル(場合により1状態1テーブル, 多状態1テーブル)を切り替える - コードで状態管理が正常化どうかを検証する(EnumやSealedを使用することが多い) 41 ユーザーの状態は別テーブルで管理する 重複しないようにビジネスロジックをDbUnit等で 徹底的にテストする
42. 変更容易で柔軟なテーブル設計にするには？ 一般的ではないテーブル設計方法を採用する覚悟 (大事) - 一般的なやり方ではないと思うので、メンバーへの理解が一番の障壁 - テーブル設計レビューは、かなり細かく行う(実行計画等を徹底レビュー) - パフォーマンスモニタリングも常に行う(インフラ連携が必要) - スロークエリ がでたらすぐ修正 - 異常を検知したらすぐ修正 - テーブル量が多くなるのでテーブルコメント、カラムコメントは書くべき - 命名規則が重要になってくる 42
43. 43 導入に際して実際にあった Q&A
44. 導入に際して実際にあった Q&A - Q. テーブル数が増えてしまう - A. 問題ない。MySQLもPostgreSQLも1スキーマ40億テーブル設置可能. - A. ネーミングルールを厳密にしないと探すのが大変になる - Q. パフォーマンスは出るのか？ - A. 1つ1つのテーブルのインデックスサイズは小さくなり、メモリに載りやすくなる ので、パフォーマンスは向上する ことが多い - A. 追加、削除、更新も局所的に実装可能なのでインデックスの更新のパフォー マンスは向上 することが期待できる - Q. 管理が大変にならないか？ - A. 数は多くなるので大変にはなる。DTOやMapper自動生成(MyBatis Generator やJOOQなど)で緩和可能。 44
45. 45 まとめ
46. まとめ • レガシーで硬直したテーブル設計から変更容易で柔軟なテーブル設 計の手順等の一部をご紹介しました • この運用で、DBのバージョンアップ以外でサービス・メンテナンスに 入れることはなくなりました。 •実際の運用では約10〜15のルールを徹底的にまもり、開発を行って おります。 •アンケートが好評であれば、次回は実践編でCfPを出そうと思いま す。 46
47. END OF PRESENTATION ご清聴ありがとうございました 
 全体アンケート セッションアンケート • 質問や不明点があればお問い合 わせいただければと思います。 • X: @earu • アンケートに連絡先等