正規化とB-treeインデックスの設計方針

達人に学ぶDB設計-徹底指南書を読みました。
「正規化とB-treeインデックスの設計方針」という観点から学んだことをまとめようと思います。

正規化

【正規化を行う目的】

データの冗長性を排除し、データの登録・更新時の不都合を防ぐため。
例えば、下記のような、非正規形(正規化が行われていない)テーブルがある場合。会社コード「C0001」の会社名が変更された場合、3レコードの会社名をそれぞれ変更する必要が出てしまう。また、加藤さんの会社名を「A商社」に変更した場合、会社コード「C0001」に対して、会社名が「A商事」と「A商社」という2種類のデータが登録できてしまう。

会社コード会社名社員ID社員名
C0001A商事000A加藤
C0001A商事000B藤本
C0001A商事001F三島
C0002B化学000A斉藤

【正規化の設計方針】

1つのセルの中に1つの値を含める(第1正規化)

(非正規形)

扶養者
社員ID社員名
000A加藤達夫
信二
000B藤本
001F三島
陽子
清美

(第1正規形)

社員
社員ID社員名
000A加藤
000B藤本
001F三島
扶養者
社員ID
000A達夫
000A信二
001F
001F陽子
001F清美
部分関数従属を解消する(第2正規化)

部分関数従属:主キーの一部の列に対して従属する列がある関係

(非正規形)

社員
会社コード会社名社員ID社員名
C0001A商事000A加藤
C0001A商事000B藤本
C0001A商事001F三島
C0002B化学000A斉藤

(第2正規形)

社員
会社コード社員ID社員名
C0001000A加藤
C0001000B藤本
C0001001F三島
C0002000A斉藤
会社
会社コード会社名
C0001A商事
C0002B化学
推移的関数従属を解消する(第3正規化)

推移的関数従属:テーブル内部に存在する段階的な従属関係

(非正規形)

社員
会社コード社員ID社員名部署コード部署名
C0001000A加藤D01開発
C0001000B藤本D02人事
C0001001F三島D03営業
C0002000A斉藤D03営業

(第3正規形)

社員
会社コード社員ID社員名部署コード
C0001000A加藤D01
C0001000B藤本D02
C0001001F三島D03
C0002000A斉藤D03
部署
部署コード部署名
D01開発
D02人事
D03営業

B-treeインデックス

【インデックスを作成する目的】

SQLのパフォーマンスを改善するため。

【B-treeインデックスの設計方針】

大規模なテーブルに対して作成する

データ量が少ない場合、フルスキャンの方が高速。
「データ量が少ない場合」の閾値はシステムによって異なるため、あらかじめ簡単な実測を行うのがおすすめ。

カーディナリティの高い列に作成する

カーディナリティとは、特定の列の値が、どのくらいの種類の多さを持つか、ということを表す概念。 例えば、「性別」を表す列の場合。列が持つ値は以下のものが考えられるので、カーディナリティは「3」となる。

  • 男性
  • 女性
  • 不詳

また、値が平均的に分散しているのがベスト。

SQL文でWHERE句の選択条件、または結合条件に使用されている列に作成する

SQLで検索条件や結合条件として使用されている列にインデックスを作成する。
以下のようなSQLは、いずれもインデックスを利用できていない。

①インデックス列に演算を行っている

SELECT * FROM SomeTable WHERE col_1 * 1.1 > 100;

(インデックスが適用されるSQL

SELECT * FROM SomeTable WHERE col_1 > 100 / 1.1;

②索引列に対してSQL関数を適用している

SELECT * FROM SomeTable WHERE SUBSTR(col_1, 1, 1) = 'a';

③IS NULL述語を使っている

SELECT * FROM SomeTable WHERE col_1 IS NULL;

④否定形を用いている

SELECT * FROM SomeTable WHERE col_1 <> 100;

⑤ORを用いている

SELECT * FROM SomeTable WHERE col_1 = 99 OR col_1 = 100;

(インデックスが適用されるSQL

SELECT * FROM SomeTable WHERE col_1 IN (99, 100);

⑥後方一致、または中間一致のLIKE述語を用いている

SELECT * FROM SomeTable WHERE col_1 LIKE '%a';
SELECT * FROM SomeTable WHERE col_1 LIKE '%a%';

⑦暗黙の型変換を行っている
(下記は、col_1が文字列で定義されている場合)

SELECT * FROM SomeTable WHERE col_1 = 10;

所感

当記事では、データベース設計を行う上で、データの整合性と検索パフォーマンスを高めるために行うべきことをまとめました。
ただ、本書では、もう1つ重要なメッセージが述べられていました。それは、「データの整合性と検索パフォーマンスとのトレードオフ」という問題についてです。当記事で、データの整合性を高めるための方法としてあげた「正規化」は、厳密に行えば行うほど、検索パフォーマンスの悪化につながります。これらの観点以外の「予算」や「ユーザからの要求」なども踏まえて、あらゆる要件を同時に満たせる平衡点を探し出すのが、エンジニアの仕事だというのが著者からのメッセージでした。エンジニアとしての1種の理想像として、心に留めておきたいと思いました。

達人に学ぶDB設計 徹底指南書 初級者で終わりたくないあなたへ

達人に学ぶDB設計 徹底指南書 初級者で終わりたくないあなたへ