DBA管理者でなくても知っておくべきRDBMSの基礎知識とは何か!?

開発の現場にいれば、データベース(特にSQL)に触れない事はありません。

ただし参画現場や役割によって、DB設計や保守寄りの経験は詰めない事も多い印象。

TaNA
私も新卒からIT業界に居ますが、DB設計に関われたのは一度だけ。データベース専任のエンジニアではないので、RDBMSのコア部分の知識は結構欠落してます(;^_^

最低限知っておくべきRDBMS知識といえば、正規化やインデックスでしょうか。

特にインデックスのB-treeは、転職活動の技術面談でもよく問われる印象。

SQLを使いこなすのは当然として、最低限の知識を身につけるため、こちらを読んでみました。

正規化

正規化していないテーブルではデータの冗長と非一貫性による不整合が発生。

POINT「正規化」とはデータの冗長性をなくし、一貫性と効率性を保持するもの。

以下の手順で正規化を行う。

1.同一情報の繰り返し排除

2.部分関数従属の排除

3.推移関数従属の排除

以下の非正規形に対して、第1~第3正規形まで正規化していきます。

第一正規形では、繰り返しのグループを別の表に分解し、元表の残った表と繰り返し部分で、 それぞれレコードを一意に出来る主キーを決めます。

第二正規形では、単価は商品番号のみで一意に識別可能で、注文番号は不要なので部分関数従属しているので分解、完全関数従属とは、主キーを構成する全ての列に従属性があるものを指します。

POINT部分関数従属とは、主キーの一部の列に対して従属するもの。

第三正規形では、顧客情報(名前や電話)は注文番号は関係ないので、推移関数従属を分割。

データを整合的な状態で保持するため、第三正規形までの正規化が行われることが原則。

ただテーブル数が増え、SQLで結合多用、結果パフォーマンス悪化のデメリットがあります。

インデックス

パフォーマンス改善になぜインデックスが利用されるのか!?

・アプリ側に影響を与えない

・テーブルに影響を与えない

・性能改善の効果が大きい

多くのデータベースで利用されるインデックスがB-tree。

例えば「1,2,3,4,5,6,7」のデータの集まりから、目的のデータ(例えば5)を取り出すケース。

先頭から順に探す方法では、計算量がO(n)で時間がかかります。

では2分探索木アルゴリズムはどうなるか!?

検索したい値が中央値より小さいなら左に進み、大きいなら右に移動。

2分探索木で目的の値(5)を検索する場合、以下の手順で検索されます。

・5は4より大きいので右側へ

・5は6より小さいので左側へ

・5が見つかる

POINT上図のように木の高さが左右同じであれば、計算量はO(log(2)N)。

ただ実際には日々データが登録・更新・削除され、木の高さは変わります。

AVL木は木の高さが変更された場合、二分探索木を回転し木の高さを一定にする構造です。

二分探索木とAVL木を一般化したものがB-tree構造になります。

データを木構造で保持、最下層のリーフと呼ばれるノードが実データのポインタを保持。

POINTB-treeはlogN/logMとなる(Mは1つのノードが持てる子ノード)

インデックスの注意点

どのカラムにインデックスを貼るべきか!?

・大規模なテーブル

・カーディナリティの高い列

・WEHERE句、結合条件の列

1点目のテーブル規模について、1万件以下ではほぼ効果が無いようです。

2点目について、性別(男女)のようにカーディナリティが低いと逆効果。

また単にカーディナリティが高いだけでなく、値が平均的に分散化していると◎。

SQLの検索条件について、以下に該当するとインデックスが利用出来ません。

・インデックス列で演算

・索引列に対してSQL関数

・IS NULL述語を使う

・否定形を使う

・OR演算子を使う

・後方一致/中間一致のLIKE句

・暗黙の型変換

他にもER図の書き方や、データベースの保守作業(バックアップ)、設計のバットノウハウなど色々ありますが、開発者であればまず正規化とインデックス周りが大切なのかなと。

ちなみに主キー及び一意制約の列には、自動的にインデックスが作成されます。