【データベース】制約の種類と速度検証

検証に使用したクエリは以下
https://www.notion.so/85c3816ea99840249be0b9104f15ab04
→ 【データベース】制約の種類と速度検証　で使用したSQL

Transcript

1. 【データベース】 制約の種類と速度検証
2. 目次 1. はじめに →　制約は本当に必要？速度に影響するの？ 2. 制約の種類と解説 →　制約にはどんな種類があるの？ 3. 制約ごとの処理速度 →　制約によって速度がどう変わるのか？ 4. 制約を使うべき /外すことを検討すべきケース →　実際はどのようなことを検討すべきか？ 5. 速度検証（外部キー制約、デフォルト制約） →　本当に早くなる？遅くなる？試してみた 6. まとめ →　制約は便利だが波フォーマンンスを考えて使いこなそう！
3. 1.　はじめに • データベースの制約（PRIMARY KEY、UNIQUE など）は、 データの整合性を守るために重要。 • しかし、クエリの実行速度にも影響を与える ことがある。 • 例えば… ◦ INSERT が遅くなる？ ◦ DELETE が思ったより時間がかかる？ ◦ SELECT は逆に速くなることも？ • 今回は、主要な制約の種類と、それが処理速度にどう影響するのかを検証！
4. 2.　制約の種類と解説 1. 主キー制約（ PRIMARY KEY） • 概要 ◦ テーブル内で一意の識別子を保証する制約。NULL 不可。 ◦ 自動的にインデックスが作成される。 • ポイント ◦ INSERT / UPDATE → やや遅くなる（インデックス更新あり） ◦ SELECT → 速くなる（インデックスにより検索最適化） • 例 ◦ CREATE TABLE users (id SERIAL PRIMARY KEY , name TEXT );
5. 2.　制約の種類と解説 2. 一意制約（ UNIQUE） • 概要 ◦ 指定したカラムの値が重複しないようにする制約（NULL は許容） ◦ PRIMARY KEY とは異なり、複数設定可能。 • ポイント ◦ INSERT / UPDATE → やや遅くなる（一意性チェックが発生） ◦ SELECT → 速くなる（インデックスによる最適化） • 例 ◦ CREATE TABLE users (id SERIAL , email TEXT UNIQUE );
6. 2.　制約の種類と解説 3. 外部キー制約（ FOREIGN KEY） • 概要 ◦ 別のテーブルのカラムとリレーションを持ち、参照整合性を保証する制約。 ◦ 削除や更新時に、参照先のデータをチェック。 • ポイント ◦ INSERT / UPDATE → 遅くなる（参照先のデータチェックが発生） ◦ DELETE → 遅くなる（DB製品により挙動が異なる） ◦ SELECT → 影響なし • 例 ◦ CREATE TABLE orders ( id SERIAL PRIMARY KEY , user_id INT REFERENCES users(id) );
7. 2.　制約の種類と解説 ⚠ 外部キー制約についての注意点 DB製品 親テーブルのチェック方法 特徴・影響 PostgreSQL / MySQL(InnoDB) 即時チェック (INSERT / UPDATE / DELETE のたびに親テーブルのデータを検索 ) 参照整合性を厳密に保証するが、 処理のオーバーヘッドが発生し、 DELETE の影響が特に大きい Oracle トランザクション完了時にまとめてチェック (DEFERRED 制約が可能) 即時チェックしないため、 INSERT / UPDATE の処理が高速に実行できる • PostgreSQL / MySQL（InnoDB）では、 DELETE 時に子テーブルのスキャンが発生するため、パフォーマンスを考慮する必要がある。 • Oracle では DEFERRED を活用することで、INSERT / UPDATE のオーバーヘッドを減らせる。 • どのDBでも、外部キーを使う場合は、 子テーブルの外部キー列にインデックスを作成すると高速化できる。
8. 2.　制約の種類と解説 4. チェック制約（ CHECK） • 概要 ◦ 特定の条件を満たさないデータの INSERT / UPDATE を禁止する制約。 • ポイント ◦ INSERT / UPDATE → ほぼ影響なし（簡単な条件） ◦ DELETE / SELECT → 影響なし • 例 ◦ CREATE TABLE products ( id SERIAL PRIMARY KEY , price INT CHECK (price > 0) );
9. 2.　制約の種類と解説 4. チェック制約（ CHECK） 🔔 注意点（外部キー制約と DELETE の影響） • 親テーブル（参照元）の DELETE は遅くなる ◦ 理由: 子テーブルの 参照整合性チェック が発生するため。 ◦ ON DELETE CASCADE の場合、 子テーブルのデータ削除も発生するためさらに遅くなる。 ◦ 対策: 子テーブルの外部キー列に インデックスを作成 すると高速化できる。 • 子テーブル（参照先）の DELETE は影響なし ◦ 理由: 外部キーのチェックは不要なので、通常の DELETE と同じ速度。
10. 2.　制約の種類と解説 5. 非NULL制約（NOT NULL） • 概要 ◦ 指定したカラムに NULL を許容しない制約。 ◦ データの一貫性を保つために使用。 • ポイント ◦ INSERT / UPDATE → ほぼ影響なし（単純なチェック） ◦ DELETE / SELECT → 影響なし • 例 ◦ CREATE TABLE employees ( id SERIAL PRIMARY KEY , name TEXT NOT NULL );
11. 2.　制約の種類と解説 6. デフォルト制約（DEFAULT） • 概要 ◦ 値が指定されなかった場合に、デフォルト値を設定する制約。 • ポイント • INSERT → 速くなる（値の補完による最適化） ◦ UPDATE / DELETE / SELECT → 影響なし • 例 ◦ CREATE TABLE customers ( id SERIAL PRIMARY KEY , status TEXT DEFAULT ‘active’ , created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP );
12. 2.　制約の種類と解説 ⚠ デフォルト制約についての注意点 • PostgreSQL / MySQL / SQL Server ◦ DEFAULT CURRENT_TIMESTAMP などの動的デフォルト(トランザクション開始時の値を維持。一貫性有) は、DBエンジンによって若干の処理差が出る可能性がある。 • Oracle ◦ SYSTIMESTAMPは関数でありトランザクション中は異なる値が出力される場合有。 ◦ DEFAULT にシーケンス（DEFAULT NEXTVAL(seq)）を設定できる。 ◦ ただし INSERT 時のパフォーマンスにはほぼ影響しない。
13. 3.　制約ごとの処理速度 制約の種類 INSERT UPDATE DELETE SELECT 主キー制約 （PRIMARY KEY） やや遅くなる (Index更新あり) 影響なし (主キー変更は非推奨) 影響なし 速くなる (Indexによる最適化) 一意制約 （UNIQUE） やや遅くなる (一意性チェックあり) 影響なし (一意制約の更新時は チェックあり) 影響なし 速くなる (Indexによる最適化) 外部キー制約 （FOREIGN KEY） 遅くなる (参照先の存在Check) 遅くなる (参照整合性Check) 参照先:影響なし 参照元:遅くなる場合有 影響なし (参照先チェックなし) チェック制約 （CHECK） ほぼ影響なし (単純な条件) ほぼ影響なし (条件が合わないと×） 影響なし 影響なし (制約は影響しない) 非NULL制約 （NOT NULL） ほぼ影響なし (NULL チェックのみ) ほぼ影響なし (NULL チェックのみ) 影響なし 影響なし (関与しない） デフォルト制約 （DEFAULT） 基本的にやや速くなる (値の補完最適化) 影響なし (適用されない) 影響なし 影響なし (関与しない）
14. 4.　制約を使うべき /外すことを検討すべきケース • ⭕制約を使うべきケース ◦ データの整合性が最優先のシステム（金融、医療など） ◦ 外部キー制約がないとデータの矛盾が起こりやすい場面 ◦ アプリ側でデータ管理が難しい場合（複数サービス連携など） ◦ 業務上、誤ったデータ登録を防ぎたい場合（IDの不整合、NULL値の防止） • ❌制約を外すことを検討すべきケース ◦ 大量データを高速に INSERT するバッチ処理 ◦ 外部キーによるパフォーマンス低下が許容できないシステム ◦ アプリ側で整合性を担保できる場合（JOIN クエリや API で管理） ◦ データの削除頻度が高く、参照制約がボトルネックになる場合
15. 5.　速度検証（外部キー制約） • 検証目的 ◦ 外部キー制約が INSERT / DELETE の速度にどの程度影響を与えるか を測定。 • 検証条件 ◦ DB：PostgrteSQL ◦ データ件数：100万件（子テーブルにINSERT） ◦ 比較パターン ▪ 子テーブルへINSERT時に外部キー制約 有り　VS　無し ◦ 初回はキャッシュの影響を避けるため除外し、 初回以降の3回の速度検証の平均を比較する。
16. 5.　速度検証（外部キー制約）INSERT • 子テーブルへINSERT時に外部キー制約有り　VS　無し • テーブル構成イメージ -- データ挿入（ 100万件） INSERT INTO parent DEFAULT VALUES; INSERT INTO child_fk (parent_id) SELECT 1 FROM generate_series(1, 1000000); – 検証 INSERT INTO child_no_fk (parent_id) SELECT 1 FROM generate_series(1, 1000000); – 検証 Parent テーブル (親テーブル) id(PK) child_fk テーブル (外部キーあり) id(PK) parent_id (FK) → parent.id child_no_fk テーブル (外部キーなし) id(PK) parent_id
17. 5.　速度検証（外部キー制約） • 外部キー制約有り • 外部キー制約無し 1回目 11,668ms 2回目 12,717ms 3回目 12,440ms 平均 12,275ms 1回目 3,734ms 2回目 4,240ms 3回目 3,404ms 平均 3,793ms 外部キーありの INSERT が遅くなる （親テーブルを参照整合性チェックするため）
18. 5.　速度検証（デフォルト制約） • 検証目的 ◦ INSERTの速度が、デフォルト制約の有無でどう変わるか検証。 • 検証条件 ◦ DB：PostgrteSQL ◦ データ件数：100万件（対象テーブルにINSERT） ◦ 比較パターン ▪ デフォルト制約 有り　VS　無し ◦ 初回はキャッシュの影響を避けるため除外し、 初回以降の3回の速度検証の平均を比較する。
19. 5.　速度検証（デフォルト制約）INSERT • デフォルト制約 有り　VS　無し • テーブル構成イメージ default_none テーブル (デフォルト制約なし) id(PK) created_at (timestamp) col1(text) col2(int) col3(boolean) col4(numeric) col5(date) default_none テーブル (デフォルト制約あり) id(PK) created_at (timestamp) col1(text) col2(int) col3(boolean) col4(numeric) col5(date) デフォルト制約あり
20. 5.　速度検証（デフォルト制約） • デフォルト制約あり • デフォルト制約なし 1回目 4,529ms 2回目 5,002ms 3回目 4,427ms 平均 4,653ms 1回目 3,599ms 2回目 3,399ms 3回目 3,820ms 平均 3,606ms 微々たる差だが、 DEFAULT ありのほうが INSERT が速くなる
21. 5.　速度検証（デフォルト制約） • 速度差があまり出なかった理由 ◦ デフォルト制約 (DEFAULT) は INSERT 時の影響が小さい ▪ デフォルト値の適用は、テーブル定義の時点で決まっているため、 評価コストがほぼゼロ ▪ デフォルトなし (NOW() など明示的指定) でも、関数の評価コストは小さい ◦ INSERT のボトルネックは主にディスク I/O やインデックス更新 ▪ デフォルトの有無よりも、ディスクへの書き込み負荷が処理時間に影響 ▪ データサイズやインデックスの有無の方が INSERT 速度に大きく関与する ◦ デフォルト制約のメリットは速度よりも運用の簡潔さ ▪ デフォルト値を設定することで、INSERT クエリをシンプルにできる ▪ アプリ側で値を設定する負担を減らし、データの一貫性を保ちやすい • 結論: デフォルト制約の有無による INSERT の速度差は小さいが、 運用面でのメリットが大きい！
22. 6.　まとめ • 制約の影響 ◦ 制約は データの整合性を保つために重要 だが、処理速度に影響を与えることがある ◦ INSERT は外部キーありのほうが遅い（親テーブルの参照チェックが発生） ◦ デフォルト制約 (DEFAULT) の有無による INSERT の速度差は小さい • 実務での活用ポイント ◦ 制約を適切に設計し、整合性とパフォーマンスのバランスを考えることが重要 ◦ 外部キーを使う場合は、子テーブルにインデックスを作成し、速度への影響を最小化 ◦ デフォルト制約の主なメリットは、 速度ではなく「クエリの簡潔化」や「運用のしやすさ」 ▪ アプリ側で値を設定する負担を減らし、データの一貫性を保ちやすい