達人に学ぶDB設計 を読んだので、個人的に気になったことなどをまとめてます。
学んだこと
バックアップ
バックアップの種類は以下3つ
- フルバックアップ
-
全てのデータを毎回バックアップする方法
メリット
・運用がシンプル
デメリット
・バックアップの時間が長い
・ハードウェアリソースへの負荷が高い(ディスクI/O、CPU・メモリ使用率が増える)
・サービス停止が必要
- 差分バックアップ
-
バックアップ周期の初日のみフルバックアップし、以降は変更分のみバックアップする方法
メリット
・フルバックアップと比べて、バックアップデータ量の減少
デメリット
・フルバックアップと比べて、リカバリ手順・時間の増加
- 増分バックアップ
-
バックアップ周期の初日のみフルバックアップし、以降は当日分のみバックアップする方法
メリット
・差分バックアップと比べて、バックアップデータ量の減少
デメリット
・差分バックアップと比べて、リカバリ手順・時間の増加
- バックアップのコストとリカバリのコストはトレードオフな関係
- 一般的には、差分バックアップまたは増分バックアップが用いられることが多い
RAID
RAIDの種類は主に以下がある
RAID0
複数のディスクに分散してデータを保持する。
別名、ストライピングともいう。
| ディスク1 | ディスク2 |
|---|---|
| データ1 | データ1 |
| データ2 | データ2 |
| データ3 | データ3 |
RAID1
2本のディスクにまったく同じデータを保持する。
別名、ミラーリングともいう。
データは分散されないため、性能は1本の場合と変わらず、ディスクの使用効率もよくない。
| ディスク1 | ディスク2 |
|---|---|
| データ1 | データ2 |
| データ3 | データ4 |
| データ5 | データ6 |
RAID5
最低3本のディスクで構成し、データのほかにパリティと呼ばれる誤り符号訂正符号を分散して格納する。
１本までならディスクが壊れてもでデータを保全できる。
また、データを分散できるため、I/O性能の向上も期待できる。
パリティ計算が発生するため、書き込み性能は高くないが、
ディスク本数が増えるので読み出し性能が向上する。
| ディスク1 | ディスク2 | ディスク3 |
|---|---|---|
| データA1 | データB2 | パリティA |
| データB1 | パリティB | データB2 |
| パリティC | データC1 | データC2 |
RAID10
RAID1とRAID0を組み合わせたもの。
RAID1のグループを二つ作り、それぞれのグループを使ってRAID0を作る。
高信頼性・高性能を実現できるが、必要になるディスクが多くなり、コストが高くなる。(最低でもディスプレイ4本必要になる。)
| ディスク1 | ディスク2 | ディスク3 | ディスク4 |
|---|---|---|---|
| データ1 | データ2 | データ1 | データ2 |
| データ3 | データ4 | データ3 | データ4 |
| データ5 | データ6 | データ5 | データ6 |
最低でもRAID5を推奨。
余裕があればRAID10がおすすめ。
インデックスはどの列に作るべきか
- 大規模なテーブル(レコード数が多い)に対して作成
- カーディナリティの高い列に作成
(カーディナリティ：列に含まれる異なる値の数（バリエーション）のこと) - SQL文でWHERE句の選択条件、または結合条件に使用されている列に作成
インデックスが適用されないパターン
インデックス列に演算を行っている
SELECT * FROM table WHERE index_column * 1.1 > 100;
/* 式変換で対応可能 */
SELECT * FROM table WHERE index_column > 100 / 1.1;
索引列に対してSQL関数を適用している
SELECT * FROM table WHERE SUBSTR(index_column, 1, 1) = 'a';
IS NULLを使用している
SELECT * FROM table WHERE index_column IS NULL;
否定形を用いている
SELECT * FROM table WHERE index_column <> 100;
ORを用いている
SELECT * FROM table WHERE index_column = 99 or index_column = 100;
/* INを用いることで対応可能 */
SELECT * FROM table WHERE index_column IN (99, 100);
後方一致・中間一致を用いている
/* 後方一致 */
SELECT * FROM table WHERE index_column LIKE '%a';
/* 中間一致 */
SELECT * FROM table WHERE index_column LIKE '%a%';
/* 前方一致はインデックスが適用される */
SELECT * FROM table WHERE index_column LIKE 'a%';
文字列型で定義されている場合、暗黙の型変換が行われている場合は適用されない
SELECT * FROM table WHERE index_column = 10;
/* 文字列型同士で比較しているので、適用される */
SELECT * FROM table WHERE index_column = '10';
SELECT * FROM table WHERE index_column = CAST(10, AS CHAR(2));
インデックスの注意点
- 主キー、一意制約の列には作成不要
- 更新性能を劣化さえるため、無駄なインデックスは作成しないようにする
良かったところ
インデックスが適用されないSQLを学べたのは良かった。
気づかずにインデックスが利用できていないときがあるので、意識的にSQLを書く必要があると思う。
難しかったこと
9章 「SQLで木構造を扱う」の箇所がイメージしづらかった。
RDBで木構造を扱うのは不得意で、その対応策が記述されているが、
どのパターンを利用すべきかがあまり理解できなかった。
この辺りは木構造を設計しなければいけないときに、再度読み直して実例と紐づけ考えるのが良いかと思う。
参考文献