DB都パフォーマンス
DBをマスターしたい
前回に引き続き、DBの基本知識を学習している
今回はDBとパフォーマンスについてまとめる
参考文献
目次
- 正規化の功罪
- 非正規化とパフォーマンス
- 冗長性とパフォーマンスのトレードオフ
1.正規化の功罪
正規化とSQL
正規化はデータ整合性を厳密に保つ方法論である
しかし、正規化の副作用としてSWLのパフォーマンス劣化を招くことがあります
- 内部結合を使うケース
社員の所属している会社を知りたい場合
SELECT 会社.会社名,
社員.社員名
FROM 社員 INNER JOIN 会社
ON 社員.会社コード ＝ 会社.会社コード
WHERE 社員.社員名 ＝ '田島';
会社名 社員名
---- ----
B工業 田島
このSQLのステップを挙げると以下になります。
- 「社員名」を検索条件として「社員」テーブルから検索する
- しかし、「社員」テーブルからは会社名が不明
＝「社員」テーブルに含まれている列は「会社コード」だけだから - 「会社名」列を持っているのは「会社」テーブル
＝つまり「会社」テーブルも検索対象に含める必要がある
1.「社員」テーブルと「会社」テーブルの２つのテーブルが検索対象になる
＝両者を結合する必要がある（結合キーは「会社コード」になる）
- 外部結合を使うケース
会社ごとに社員が何人いるか集計したい場合
SELECT 会社.会社名,
COUNT(社員.社員名 AS 社員数
FROM 会社 LEFT OUTER JOIN 社員
ON 社員.会社コード ＝ 会社.会社コード
GROUP BY 会社.会社コード;
会社コード 社員名
-------- -----
C0001 ３
C0002 ３
C0003 ０
これらのように正規化されたテーブル群であっても、
SQL文で結合することで得たい結果を自由に得ることができる
しかし、SQLにおける結合は非常にコストの高い操作であり、
結合するテーブル数、レコード数が増えると処理時間がかかる
正規化することでパフォーマンス劣化する原因の多くはSQLの結合操作である
非正規化による解決
上記のSQLの問題を解決する１つの方法は「非正規化」である
３つのテーブルを１つのテーブルに繋げた場合、SQLは以下のようになる。
SELECT 会社名,
部員名,
部署名
FROM 社員
WHERE 社員名 = '田島';
更新処理ではどちらが有効？
正規化と非正規化の更新処理は以下になる
UPDATE 社員
SET 会社名 = 'E物産'
WHERE 会社コード = 'C0001';
UPDATE 会社
SET 会社名 = 'E物産'
WHERE 会社コード = 'C0001';
SQL文では違いが少ないが、更新の数に違いがある
非正規化：更新する会社のレコード数
正規化 ：会社テーブル内の１レコードのみ
つまり、更新処理では正規化したテーブルの方が効率的であるといえる
正規化VS非正規化
-
正規化と検索SQLのパフォーマンスはトレードオフ
正規化の次数が低いほど検索SQLのパフォーマンスは良いが、データ整合性が低い
正規化していくほど、パフォーマンスが低下する代わりにデータ整合性が高くなる -
原則として非正規化は許さない
どこまで正規化すればいいのかとなるが、「非正規化は最後の手段」であることが大前提
2.非正規化とパフォーマンス
2-1.サマリーデータの冗長性とパフォーマンス
正規化によって引き起こさせる性能問題は、 SQL観点からだと２種類ある
１つ目はサーマリーデータの冗長性排除
２つ目は選択条件の冗長性排除
※両方とも必ず１対多の２つのテーブル間で発生する
- 具体例
| 受注ID | 受注日 | 注文者名義 |
|---|---|---|
| 1 | 2021-01-05 | 山田一郎 |
| 2 | 2021-01-05 | 田中茂 |
| 3 | 2021-01-06 | 川上直哉 |
| 4 | 2021-01-07 | 佐伯美奈子 |
| 5 | 2021-01-07 | 小林元 |
| 6 | 2021-01-11 | 坂本愛 |
| 7 | 2021-01-12 | 城島翔子 |
| 受注ID | 受注明細連番 | 商品名 |
|---|---|---|
| 1 | 1 | マカロン |
| 1 | 2 | 紅茶 |
| 1 | 3 | オリーブオイル |
| 1 | 4 | チョコ詰め合わせ |
| 2 | 1 | 紅茶 |
| 2 | 2 | 日本茶 |
| 2 | 3 | ティーポッド |
| 3 | 1 | 米 |
| 4 | 1 | アイロン |
| 4 | 2 | ネクタイ |
| 5 | 1 | チョコ詰め合わせ |
| 5 | 2 | 紅茶 |
| 5 | 3 | クッキーセット |
| 6 | 1 | 牛肉 |
| 6 | 2 | 鍋セット |
| 7 | 1 | 米 |
受注日ごとに何個の商品が注文されているか調べたい場合
SELECT 受注.受注日,
COUNT(*) AS 商品数
FROM 受注 INNER JOIN 受注明細
ON 受注.受注ID = 受注明細.受注ID
GROUP BY 受注.受注日;
受注日 商品数
---------- -----
2021-01-05 7
2021-01-06 1
2021-01-07 5
2021-01-11 2
2021-01-12 1
このSQLは、機能的には問題ないがパフォーマンスの観点で言うと問題がある
→２つの大きなテーブル同士を結合するため高コストな処理となる
結合しないSQLを作るテーブル設計
| 受注ID | 受注日 | 注文者名義 | 商品数 |
|---|---|---|---|
| 1 | 2021-01-05 | 山田一郎 | 7 |
| 2 | 2021-01-05 | 田中茂 | 7 |
| 3 | 2021-01-06 | 川上直哉 | 1 |
| 4 | 2021-01-07 | 佐伯美奈子 | 5 |
| 5 | 2021-01-07 | 小林元 | 5 |
| 6 | 2021-01-11 | 坂本愛 | 2 |
| 7 | 2021-01-12 | 城島翔子 | 1 |
こうした場合、SQLの処理は以下となる
SELECT DISTINCT 受注日,
商品数
FROM 受注;
結果、左まりデータを冗長の保持すると正規形に違反するが、検索を高速化できる
2-2.選択条件の冗長性とパフォーマンス
選択条件を冗長にすると正規形に違反するが、検索を高速化できる
3.冗長性とパフォーマンスのトレードオフ
非正規は更新不整合のリスクがあるが、その他にもリスクは存在する
具体的には3つに分類できる
- 更新時のパフォーマンスを低下させる
- データのリアルタイム性を低下させる
- 後続の工程で設計変更すると、手戻りが大きい
更新時のパフォーマンス
データが更新されたときに、上記で作成したようなマスタデータも更新する必要がある
このように更新処理の負荷が増加する
データのリアルタイム性
データの変更タイミングがいつ反映されるかによるが、
更新が多いほどシステムに負荷がかかる、少ないと最新情報でない状況が多くなる
改修コストの大きさ
データモデルの変更はコードベースの修正に比べて非常にコストが大きい（DOAの大原則より）
非常に高い要求水準だが、正規化の理論やそれによって生じるトレードオフを知り尽くした上で
あらゆる要件を同時に満たせる平衡点を探し出せる能力が必要となる