株式会社リゾーム 技術部 システム開発 第3グループの岡部です。今回は「スッキリわかるSQL入門」読書会の第10回のレポートです。
過去のレポートはこちらからご覧いただけます。
書籍について
使用した書籍は「スッキリわかるSQL入門 第3版 - IT入門書籍 スッキリシリーズ」です。
第10回レポート
今回の学習範囲は第11章「さまざまな支援機能」です。データベースを「より速く」「より便利に」「より安全に」使うための機能を学びました。
参加者は5名でした。
11.1 データベースをより速くする
本に索引（インデックス）があると、特定のキーワードが登場するページを素早く調べることができます。データベースにおけるインデックスもそれとよく似ており、目的のレコードを高速に検索することができます。このセクションでは、そんなインデックスの使い方や注意点について学びました。
インデックスが有効な例
インデックス による高速化の効果が得られやすい例として以下の3つが挙げられていました。
- WHERE句による絞り込み
- ORDER BYによる並び替え
- JOINによる結合の条件
参加者の意見・感想
- 「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文」なので、以下のようなデメリットがあるようです。
参加者の意見・感想
- 濫用しないようにしたいが、どこまでが適切な使用なのかを見極めるのが難しそう
- プログラミングでも、呼び出した関数が実は重い処理をしていて…、という経験があったのを思い出した
- 性能重視の場合はマテリアライズドビューも検討したい
- マテビューの話題は第6章の集計テーブルでも出ましたね
重複しない番号の管理
主キーに連番を振ることはよくありますが、値が重複しないよう自力で管理するのは結構大変そうです。そこで、DBMSに用意されている機能を使って連番を管理する方法を学びました。
代表的なものは以下の2つです。
- 連番が自動的に振られる特殊な列を定義する
- 連番を管理してくれる「シーケンス（SEQUENCE）」を用いる
参加者の意見・感想
- 自動採番列にはかなりお世話になっています🙇🏻♂️自前で管理する必要が無くて楽だし、なにより確実なのがありがたい
- シーケンスは使ったことがなかった。今のところIDENTITY列で間に合っている
- 自分の携わっているシステムでは、自動採番で管理しているものも、採番テーブルで管理しているものも両方あった。どういう使い分けをしているんだろう？
11.3 データベースをより安全に使う
トランザクション処理で必要とされる4つの特性をまとめてACID特性と呼びます。
- Atomicity（原子性）
- Consistency（一貫性）
- Isolation（独立性）
- Durability（永続性）
これまでコミットやロールバック、制約、ロック、分離レベルなどの安全機構を学んできましたが、これらはA・C・Iを実現するためのものでした。ではDはどうでしょうか？データベースに格納されたデータは物理的なストレージに記録されるため、故障によって読み書きができなくなる可能性をゼロにすることはできません。それを踏まえて、Durability（永続性）を確保するための仕組みがDBMSには用意されています。
データベースのバックアップ
データベースの全内容をファイルに出力することができます。そのファイルをデータベースから独立した別のストレージにコピーし保管する、というのが基本的なバックアップのやり方です。オフラインバックアップとオンラインバックアップの2つの方式があります。
ログファイルのバックアップ
データベースのバックアップは時間がかかる処理なので、あまり頻繁にはできません。そのため、データベースのログファイルを高頻度でバックアップするのが一般的だそうです。
データベースのログはREDOログやアーカイブログ、あるいはトランザクションログと呼ばれますが、その内容はそれまでにデータベースを更新したすべてのSQL文にほかなりません。最新のDBバックアップを復元し、そのバックアップ以降に実行されたSQLを実行する（※これをロールフォワードという）ことで、障害が発生する直前の状態までデータを復元することができます。
参加者の意見・感想
- ロールフォワードという言葉は聞いたことがあったが、具体的な仕組みは知らなかったので学べてよかった
- データベースのバックアップとトランザクションログのバックアップをうまく組み合わせることが大事なんだな
- ストレージ容量やシステム負荷の関係上、完全にスキの無いバックアップを取るのは難しい。バックアップの頻度や保存期間をうまく調整する必要がある
- 定型業務の中でバックアップや復元作業を行っているが、今でも緊張して冷や汗をかいている…
- 下手に慣れて油断しちゃうより、緊張感を持ってやるくらいがいいかもね
まとめ
今回の章で紹介された機能は実際の業務でも使っているので身近に感じますが、今まで理解が浅いまま使っていた部分がありました。あらためて体系的に勉強できてよかったです。
次回の範囲は第12章「テーブルの設計」の前半（1～4）です。
