はじめに
SQLの勉強会の発表が今回で最後になります。
今回は書籍の「さまざまな支援機能」についてまとめます。
この章ではデータベースをより早く、便利に、安全に使うためにそのような機能があるということを学びました。
インデックスとは
テーブルに多くのデータが格納されていた場合特定のデータを検索しようとすると非常に効率が悪く、多くの時間を使う可能性があります。こういったときに指定したカラムに対してインデックスを作るとDBMSが自動的にインデックスを使用しようとするので検索が高速になることが多くなります。
ここでいうインデックスとは、テーブルの情報を速く検索するための仕組みのことです。
一般的には索引や見出しと言われています。
例えば家計簿テーブルの「費目ID」に対してインデックスを作ると、検索条件に費目IDを使用した検索が高速になります。インデックスを作成するにはCREATE INDEXを使います。
CREATE INDEX インデックス名 ON テーブル名(カラム名)
インデックス名は重複しない任意な名前をつけることができます。
また、インデックス名はDROP INDEX文で削除できます。
DROP INDEX インデックス名
インデックスの効果が得られやすいケース
①: WHERE句を使って絞り込む場合
WHERE句は完全一致検索ができるのでインデックスが使用されると高速に検索されます。
SELECT * FROM 家計簿
WHERE 費目 = '食費'
ただし、部分一致検索や後方一致件検索ではインデックスを利用することはできません。
これはインデックスが作成されたカラムがソート状態(小さい順、大きい順など特定のルールに従ってデータを並べること)で管理されているからです。
SELECT * FROM 家計簿
WHERE メモ LIKE '2月の%'
②: ORDER BY による並び替えで使う場合
SELECT * FROM 家計簿
ORDER BY メモID
インデックスには並び替えを高速にする効果もあるのでORDER BYの処理が速くなります。
③: JOINによる結合
SELECT * FROM 家計簿
JOIN メモ
ON 家計簿.メモID = メモ.ID
結合処理は内部で並び替えを行なっているのでインデックスのある列を使うと速くなります。
以上のことから次のような列にインデックスを作成すると良いと考えられます。
- WHERE句に高頻度で登場する列
- ORDER BY句に高頻度で登場する列
- JOINの結合条件に高頻度で登場する列(外部キーの列)
インデックスを作成するデメリット
- 索引情報を保存するとディスク容量を消費する
- テーブルのデータが更新されるとインデックスも更新されるのでINSERT文,UPDATE文,DELETE文の付加的な処理が増える
インデックスは便利だが濫用しないように注意が必要！！
ビューとは
データベースを使っていると同じようなSQL文を実行することがあります。そこで同じSQL文を書くのを防ぐためにビューという機能を使います。
SELECT * FROM 家計簿
WHERE 日付 >= '2021-07-01'
AND 日付 <= '2021-07-31';
SELECT DISTINCT 費目 ID FROM 家計簿
WHERE 日付 >= '2021-07-01'
AND 日付 <= '2021-07-31';
この2つのSQL文において、日付の指定の部分が同じになっています。
そこで7月分のデータのみのビューを作ることでこれをテーブルのように使用してあげます。
CREATE VIEW 家計簿7月 AS
SELECT * FROM 家計簿
WHERE 日付 >= '2021-07-01'
AND 日付 <= '2021-07-31'
作成したビューを使って、先ほどの「7月の家計簿に関するSQL文」をシンプルにしてみます。
SELECT * FROM 家計簿7月;
SELECT DISTINCT 費目ID FROM 家計簿7月;
このように重複を避けて記述することができました！(ビューを使うことでSQL文がシンプルになりましたね)
ビューはテーブルとよく似ていますが、ビューはいくつかの条件が揃わないとSELECT文しか使うことができません。
これはビューがSELECT文に名前をつけたものだからです。
ビューはシンプルな形ですが、実際に実行されるSQL文は複雑なものになる可能性があります。
採番について
テーブルに行を追加するときに、適切な番号を取得することを採番と言います。
主キーのIDなどに振る番号は重複してはいけないので独自の番号を使います。
その際、次にどんな番号を使えば良いのか、最後に使った番号は何かなどを記録する必要があります。
そこで専用のテーブル(採番テーブル)を使って記録します。
連番を振るときに一部のDBMSでは連番を管理する機能があります。
- DBMS製品における連番修飾
| SQL Server | MySQL | MariaDB | PostgreSQL | SQLite | |
|---|---|---|---|---|---|
| 宣言に修飾 | IDENTITY | AUTO_INCREMENT | AUTO_INCREMENT | なし | IDENTITY/AUTO_INCREMENT |
| 独自型 | 無し | SERIAL型 | SERIAL型 | SERIAL型 | SERIAL型 |
また、Oracle DB,Db2,SQL Server, PostgreSQLではシーケンスを使用することができます。
シーケンスとは自動的に一意な番号を取得できるデータオブジェクトのことです。
これは常に採番した最新の値を記憶し、現在の値や次の値を取り出すことができます。
CREATE SEQUENCE シーケンス名
DROP SEQUENCE シーケンス名
シーケンスはCREATE SEQUENCE文で作成、DROP SEQUENCE文で削除します。
バックアップについて
DBMSは、データの消失した場合に備えてバックアップという機能を持っています。
これは、データベースの全内容をファイルに出力できるというものです。
○ オフラインバックアップ
DBMSを停止して行うバックアップで、このバックアップを行なっている場合はデータの処理が行えないという欠点があります。
○ オンラインバックアップ
DBMSを稼働しながら行うバックアップで、便利であるが制約を伴うことがある。
2つのファイルのバックアップ
重要なシステムではデータベースの内容は1日だったり、週ごとだったりの低頻度でバックアップを行うのに対して、ログファイルの内容は数分~数時間の高頻度で行うようにします。
ログファイルは、プログラムの稼働状況やエラーメッセージなどが書いてあります。
データベースのログはREDOログ、トランザクションログとも言い、その内容は「それまでに実行した全てのSQL文」です。
バックアップからの復元方法
① 最後に取得したデータベースのバックアップを復元する
② 最後のデーターベースバックアップ以降に実行されたものを再実行する
ロールフォワード: まずバックアップを適用し、そのバックアップ以降に行った処理を再現すること
ロールバック: 主にトランザクションの処理の途中で失敗したときに実行する。途中のトランザクションの処理を取り消す。
まとめ
- インデックス
テーブルの列に対して、索引情報を生成する
インデックスが存在する列の検索は多くの場合高速になる
インデックスは濫用しないよにすること
- ビュー
SELECT文の結果表を仮想的なテーブルとして扱う
実体はSELECT文のため内部にデータを持っているわけではなく、DBMSへの負荷も変わらない
- 採番とシーケンス
テーブルに行を追加するときに、独自の番号を振るために、適切な番号を取得することを採番という
連番を管理する専用の道具としてシーケンスがあり、これに指示することで最後に採番した値や次に採番する値を取り出すことができる
- バックアップ
データの消失に備えてバックアップの仕組みを備えている
データベースの内容とログファイルの内容をバックアップし、ロールフォワードすることで障害発生前の状態までデータの復元ができる
参考文献
書籍『スッキリわかるSQL入門第2版ドリル222問付き！』