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

目次
データを管理するに何を使っていますか？
データベース歴史
レガシーなテーブル設計イメージ
レガシーなテーブル設計なぜ発生するか？
テーブル設計方法と？
テーブル設計難しさ
変更容易で柔軟なテーブル設計にするに？
導入に際して実際にあったQ&A
まとめ

Transcript

1. レガシーで硬直したテーブル設計から 変更容易で柔軟なテーブル設計にする 2025/11/15 JJUG CCC 2025 Fall #jjug_ccc_ab
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 レガシーなテーブル設計のイメージ
15. レガシーなテーブル設計のイメージ 15 正規化の不足 - データ重複が多い - 更新異常、削除異常、挿入異常が発生しやすい 命名規則 - テーブル名、カラム名が不明瞭(etc. column_1,column_a) - 一貫性のない命名ルール データ型が不適切 - すべてを文字列型で保存
16. レガシーなテーブル設計のイメージ 16 設計上の問題 - 更新回数のカラムだけど特定の数字(例: 99)だけ特別な意味 - インデックスの欠乏もしくは過剰 - カラム数が多すぎる神テーブル - データ量が増えてくるとパフォーマンスが落ちる 保守性の低さ - 不要なカラムがそのまま - 予備カラム(space_1,space_2, memo_1,memo_2 etc..)
17. (スピーカーが思う )レガシーなテーブル設計のイメージ 17 性能上の問題 - カーディナリティの低いカラムにインデックスが張られている - 必要のない外部キーの濫用 - insert、update、deleteが遅い - 過剰な正規化によるパフォーマンスの劣化 - 論理削除の濫用によるデータ量増加でのパフォーマンス劣化 設計上の問題 - updated_atが何を更新した時刻か分からない - フラグカラムの乱立 - 動的カラム名(campaign_2024, campaign_2025) - データ項目の追加時に影響範囲が読めない = 調査工数が増える
18. レガシーなテーブル設計はなぜ発生するのか？ 18
19. レガシーなテーブル設計はなぜ発生するのか？ 19 - データが増えなければ、初期設計は良い設計になっている(はず) - 項目が増えなければ、初期設計は良い設計になっている(はず) - 項目追加 = カラム追加の思考 になっている - カラム追加の方が工数が節約できる (と思っている) - テーブルを追加するのは謎の承認フローが必要(想像) - フレームワークが勝手に テーブル設計をしてくれるから関心がない - テーブル設計の方法は特に教えてもらっていない - GoogleやAIに聞いても、あんまり明確な答えが返ってこない
20. テーブル設計の方法とは？ 20
21. テーブル設計の方法とは？ 21 - アプリケーションコードは設計方法が豊富 - MVC - Clean Architecture - MVVM - ドメイン駆動開発(DDD) - Vertical Slice Architecture - Onion Architecture - Layerd Architecture - Functional Programming - Object Oriented Programming
22. テーブル設計の方法とは？ 22 - アプリケーションコードは設計方法が豊富 - MVC - Clean Architecture - MVVM - ドメイン駆動開発(DDD) - Vertical Slice Architecture - Onion Architecture - Layerd Architecture - Functional Programming - Object Oriented Programming 種類も豊富で情報もたくさん
23. テーブル設計の方法とは？ 23 - テーブル設計は？ - 要件の洗い出し - 概念設計 - 物理設計 - 論理設計 - 整理 - 正規化 - テーブル定義書 - SQLアンチパターン？
24. テーブル設計の方法とは？ 24 - テーブル設計は？ - 要件の洗い出し - 概念設計 - 物理設計 - 論理設計 - 整理 - 正規化 - テーブル定義書 - SQLアンチパターン？ どの説明も抽象度が高すぎて、具 体がイメージできない。 さらに、時間軸ベースの説明がな いので、データが肥大化すると詰 む。
25. テーブル設計の方法とは？ 25 - 結局のところ... - どうテーブル設計をした方がいいかがわかってない - わかった気になって、なんとなくやってしまっている - それっぽくやってしまっている - Excelやスプレッドシートをイメージしてしまっている
26. 26 テーブルが格納するデータの特性や重要性
27. テーブルが格納するデータの特性や重要性 27 データとコードとライフタイム - データ: 数年〜数十年 - コード: 数カ月〜数年(短くなる傾向にある) データ コード 20年 10年 5年 0年 データの方が圧倒的に長い
28. テーブルが格納するデータの特性や重要性 28 データとコードの経過年数と物量 - データ: 経過年数 x 利用頻度で積み上がっていく - コード: 経過年数でコードは増加するが、初期に書かれたコードは減少する サービスが成長するとコードも増加するが、データ も肥大化する。しかし、データは過去のものを開発 者の都合で容易には変更できない。そして変更し ないほうがよい。
29. 29 テーブル設計の難しさ
30. テーブル設計の難しさ • 正解がわからない(それなりに動作してくれる) • 未来はわからない のに、拡張が難しい • 過去の変更はできない (できてもかなりしづらい) 30 未来 過去 現在 初期構築では良い設計 変更すると不具合がでる 全くわからない わかる範囲
31. 31 変更容易で柔軟なテーブル設計にするには？
32. 変更容易で柔軟なテーブル設計にするには？ 今までのスライドの課題を折り込んだものにする - 世の中に落ちてるテーブル設計論に寄りかからない - 過去に対する変更を行わない - 未来への変更を折り込む - データ量の増加 - 格納する項目の追加 - 利用しなくなった項目の削除 - 利用しなくなったデータの削除 - サービス仕様変更によるデータの扱われ方の変更 - 一般的ではないテーブル設計方法を採用する覚悟(大事) 32
33. 変更容易で柔軟なテーブル設計にするには？ - 抽象度が高く、理解しきれない概念に依存しない - フレームワークの自動生成等に依存し(すぎ)ない - 時間軸による変化を意識するようにする 33
34. 変更容易で柔軟なテーブル設計にするには？ 過去に対する変更を行わない - ALTER文を実行するデメリット - カラム追加により、過去の設計に改変を加えている - 過去のデータのデフォルト値どうするんだっけ？など余計なことを考える - ALTER文の実行中にロックがかかったりする(Online DDLでもある) - アプリケーションのロールバック時に手順が発生する - UPDATE文を実行するデメリット - いつ更新されたかわからない。(updated_atを更新し忘れ問題) - updated_atが変更されても何を更新したのかわからない - 通常のビジネスロジックを通ってきてない可能性もある 34
35. 変更容易で柔軟なテーブル設計にするには？ 過去に対する変更を行わない - ALTER文 -> CREATE TABLE文 + INNER JOIN文で対応 - 既存テーブルのデータの過去改変が発生しない - 新テーブルに必要なデータだけが入り, null大量のカラムとかできない - UPDATE文 -> DELETE & INSERT文で対応 - 最新のデータのみを保持するテーブルにする - 一度にINSERTできるくらいのテーブルに分割する - updated_atなどは不要 - 変更履歴が必要な場合は、${テーブル名}_logテーブルを作成し格納 35
36. 変更容易で柔軟なテーブル設計にするには？ 過去に対する変更を行わない - ALTER文 -> CREATE TABLE文 + INNER JOIN文で対応 - Q. JOINが多くて性能劣化になりそう - A. なってからいいましょう - A. 1対1のデータなら気にすることない - A. 必ず一緒になるデータは集約作業を行いJOIN数を削減する(後述) - A. OUTER JOIN も使用は可 36
37. 変更容易で柔軟なテーブル設計にするには？ 過去に対する変更を行わない - 集約作業 - 複数のテーブルを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週間かけて段階的に行うのがオススメ - サービス無停止で集約作業は可能。日中にやっても問題ない。 37
38. 変更容易で柔軟なテーブル設計にするには？ 過去に対する変更を行わない - UPDATE文 -> DELETE & INSERT文で対応 - Q. 常に最新のデータしかみられなくなる - A. ${テーブル名}_logテーブルを使用して過去データを参照可能 - Q. 全部created_atにしていいのか？ - A. 問題なし。変更がされないことが担保されていた方がいい - Q. 外部キーが使えないのでは？ - A. 使えません。更新処理が局所化されるので、バリデーション等が毎回すべて通る思想 で使用する。どうしても外部キーを使用する場合は、UPSERTなどを使用すること。 38
39. 変更容易で柔軟なテーブル設計にするには？ 未来に対する変更を折り込む - テーブル追加時に実行計画とセットでレビューする - 使用しなくなったテーブルはすぐに削除を行う - 手順 - 参照が外れているかを確認 - テーブルをリネームする - テーブルをドロップする - データ量の増加に対応する - パーティションで対応(アプリケーションの変更が不要) - シャーディングで対応(アプリケーションの変更が必要) - New SQLで対応(金の力で叩く) 39
40. 変更容易で柔軟なテーブル設計にするには？ 未来に対する変更を折り込む - サービス仕様変更によるデータの扱われ方の変更 - 例) フラグや状態をサービス仕様の都合で変更したい - フラグ専用テーブル(原則1フラグ1テーブル)を用意する - 状態テーブル(場合により1状態1テーブル, 多状態1テーブル)を切り替える - コードで状態管理が正常化どうかを検証する(EnumやSealedを使用することが多い) 40 ユーザーの状態は別テーブルで管理する 重複しないようにビジネスロジックを徹底的に DbUnit等でテストする
41. 変更容易で柔軟なテーブル設計にするには？ 一般的ではないテーブル設計方法を採用する覚悟 (大事) - 一般的なやり方ではないと思うので、メンバーへの理解が一番の障 壁 - テーブル設計レビューは、かなり細かく行う - パフォーマンスモニタリングも常に行う(インフラ連携が必要) - スロークエリがでたらすぐ修正 - 異常を検知したらすぐ修正 - テーブル量が多くなるのでテーブルコメント、カラムコメントは書くべ き - 命名規則が重要になってくる 41
42. 42 導入に際して実際にあった Q&A
43. 導入に際して実際にあった Q&A - Q. テーブル数が増えてしまう - A. 問題ない。MySQLもPostgreSQLも1スキーマ40億テーブル設置可能. - Q. パフォーマンスは出るのか？ - A. 1つ1つのテーブルのインデックスサイズは小さくなり、すべてメモリに載り やすくなる - A. 追加、削除、更新も局所的に実装可能なのでインデックスの更新ラグを抑 止できる - Q. 管理が大変にならないか？ - A. 数は多くなるので大変にはなる。DTOの自動生成(MyBatis Generatorや JOOQなど)で緩和可能 43
44. 44 まとめ
45. まとめ • 以上のレガシーで硬直したテーブル設計から変更容易で柔軟なテー ブル設計の一部をご紹介しました。 • この運用で、DBのバージョンアップ以外でサービス・メンテナンスに 入れることはなくなりました。 •実際の運用では約10〜15のルールを徹底的にまもり、開発を行って おります。 •アンケートが好評であれば、次回は実践編でCfPを出そうと思います • 不明点があればお問い合わせいただければと思います。 • X: @earu 45
46. END OF PRESENTATION ご清聴ありがとうございました 
 全体アンケート セッションアンケート