「スッキリわかるSQL入門」読書会レポート vol. 10

「WHEREの絞り込みが早くなるんだろうなぁ」程度の認識しかなかったが、ORDER BYやJOINでも効果を発揮することが分かって良かった
WHERE句による絞り込みについて、前方一致でもインデックスが利用されることがあるのは知らなかった
- ただし部分一致や後方一致の場合は基本的にインデックスが利用されない。気を付けよう
今までインデックスを自分で設定したり運用したりする機会は無かったが、今後は使いどころを意識していきたい

インデックスの注意点

便利なインデックスですが、以下のようにデメリットもあります。

ディスク容量を消費する
INSERT、UPDATE、DELETEのオーバーヘッドが増える

参加者の意見・感想

なんでもかんでもインデックスにすればいい、というわけではないんだなぁ
頻繁にINSERTやUPDATEが発生するテーブルにインデックスを貼った結果、処理性能が落ちることがあった
- ある環境ではデータが非常に大きいため、INSERTする際にインデックスを一旦削除し、INSERT後に再度インデックスを作成するという運用をしていると聞いた

その他の意見

ほかにも、インデックスについて以下のような意見がありました。

主キー制約を設定すると、内部的にインデックスも作成されるとは知らなかった（※DBMSにもよる）
SQL Serverでは実行プランを確認することで具体的にどの辺りが遅いか分かるため、パフォーマンスの改善を行う際には必ずチェックしている
- SQL Server Management Studioを使えば、実行プランをツリー形式でグラフィカルに表示することもできる

11.2 データベースをより便利にする

データベースをより便利にする仕組みとして、ビューや自動採番機能が紹介されていました。

ビューのメリット

書籍では以下のようなメリットが挙げられていました。

SQL文がシンプルで分かりやすくなる
データを参照できる範囲を柔軟に制限できる

参加者の意見・感想

業務でもビューはよく使うのでメリットを実感している
- 例：特定期間のデータのみを取得するビュー
個人的には、データの参照範囲を制限できる点が大きなメリットだと思う
少し特殊な使い方だが、別DBのテーブルを参照するビューを作ることで、あたかも自分のDB内のテーブルのように参照できる。プログラムを書き換えることなく、参照先のDBを変えることができて便利（SQL Serverではシノニムでも同じことができる）

ビューのデメリット

ビューはテーブルのように扱えるとはいえ、その実体は「名前付きのSELECT文」なので、以下のようなデメリットがあるようです。

ビューに対するINSERTやUPDATEは制限される（条件付きで可能ではあるが、DBMSによって異なる）
SQL文自体はシンプルなのに、実行される処理の負荷が想像以上に高い場合がある

参加者の意見・感想

濫用しないようにしたいが、どこまでが適切な使用なのかを見極めるのが難しそう
プログラミングでも、呼び出した関数が実は重い処理をしていて…、という経験があったのを思い出した
性能重視の場合はマテリアライズドビューも検討したい
- マテビューの話題は第6章の集計テーブルでも出ましたね

重複しない番号の管理

主キーに連番を振ることはよくありますが、値が重複しないよう自力で管理するのは結構大変そうです。そこで、DBMSに用意されている機能を使って連番を管理する方法を学びました。

代表的なものは以下の2つです。

連番が自動的に振られる特殊な列を定義する
連番を管理してくれる「シーケンス（SEQUENCE）」を用いる

参加者の意見・感想

自動採番列にはかなりお世話になっています🙇🏻‍♂️自前で管理する必要が無くて楽だし、なにより確実なのがありがたい
シーケンスは使ったことがなかった。今のところIDENTITY列で間に合っている
自分の携わっているシステムでは、自動採番で管理しているものも、採番テーブルで管理しているものも両方あった。どういう使い分けをしているんだろう？

11.3 データベースをより安全に使う

トランザクション処理で必要とされる4つの特性をまとめてACID特性と呼びます。

Atomicity（原子性）
Consistency（一貫性）
Isolation（独立性）
Durability（永続性）

これまでコミットやロールバック、制約、ロック、分離レベルなどの安全機構を学んできましたが、これらはA・C・Iを実現するためのものでした。ではDはどうでしょうか？データベースに格納されたデータは物理的なストレージに記録されるため、故障によって読み書きができなくなる可能性をゼロにすることはできません。それを踏まえて、Durability（永続性）を確保するための仕組みがDBMSには用意されています。

データベースのバックアップ

データベースの全内容をファイルに出力することができます。そのファイルをデータベースから独立した別のストレージにコピーし保管する、というのが基本的なバックアップのやり方です。オフラインバックアップとオンラインバックアップの2つの方式があります。

ログファイルのバックアップ

データベースのバックアップは時間がかかる処理なので、あまり頻繁にはできません。そのため、データベースのログファイルを高頻度でバックアップするのが一般的だそうです。

データベースのログはREDOログやアーカイブログ、あるいはトランザクションログと呼ばれますが、その内容はそれまでにデータベースを更新したすべてのSQL文にほかなりません。最新のDBバックアップを復元し、そのバックアップ以降に実行されたSQLを実行する（※これをロールフォワードという）ことで、障害が発生する直前の状態までデータを復元することができます。