はじめに
以下はスッキリわかるSQL入門第12章(さまざまな支援機能)の学習備忘録です。
引用されている個所は書籍の内容をそのまま書き、引用されていない箇所は私が調べて補足した内容を書いてあります。
SQLのタグが付いているコードブロックに関しては、書籍の引用と私が調べて補足したものを織り交ぜた内容になっています。
RDMSについてはMySQLを想定しています。
第11章 さまざまな支援機能
11.1 データベースをより早くする
インデックスの作成と削除
- インデックスは、指定した列に作られる
- インデックスが存在する列に対して検索が行われた場合、DBMSは自動的にインデックスの使用を試みるため、高速に処理されることが多い(検索の内容によってはインデックスの利用はできず性能も向上しない事がある)
- インデックスには名前を付けなければならない
インデックスの作成
CREATE INDEX インデックス名 ON テーブル名(列名)
インデックスの削除
DROP INDEX インデックス名 ON テーブル名
複数の列を1つのインデックスとする複合インデックス
CREATE INDEX インデックス名 ON テーブル名(列名1, 列名2)
インデックスの効果が得られやすい典型的な3つのケース(WHERE句、ORDER BY句、JOIN結合)
1. WHERE句による絞り込み
完全一致検索ではインデックスが使用される。DBMSの種類や内部構造によるが、文字列比較の場合完全一致ではなく前方一致検索(最初の部分が一致することを条件とした検索)の場合でもインデックスが使用されることがある。
しかし、部分一致検索(位置に関係なく任意の壱が一致する事)や後方一致検索(末尾の部分が一致することを条件とした検索)ではインデックスが利用できない。
2. ORDER BY句による並び替え
インデックスには並び替えを高速に行えるようにする効果もあるため、OEDER BYの処理が早くなる。
3. JOINによる結合の条件
結合処理は内部で並び替えを行っているため、インデックスのある列を使うと高速になる。
インデックスを作成する事によるデメリット
- 索引情報を保存するために、ディスク容量が増える
- テーブルのデータが変更されると、インデックスを書き換える必要が出てくるため、INSERT文、UPDATE文、DELETE文のオーバーヘッドが増加する。
※インデックスを濫用すると検索性能が向上するが、書き換え性能時のオーバーヘッドが増える事に注意。
11.2 データベースをより便利にする
ビュー(view)
ビューの実態は単なる『名前を付けたSELECT文』から取得した結果表。メリットとしてはSQL文がシンプルになること。デメリットしては複雑なSQL文になっている可能性があり、一見するよりも負荷の高い処理になっている可能性がある。
ビューの作成と削除
CREATE VIEW ビュー名 AS SELECT文
DROP VIEW ビュー名
/* 4月に関する家計簿データのみを持つビューを定義 */
CREATE VIEW 家計簿4月 AS
SELECT * FROM 家計簿
WHERE 日付 >= '2018-04-01'
AND 日付 >= '2018-04-30'
/* 家計簿4月ビューを使ったSQL文の実行 */
SELECT * FROM 家計簿4月
SELECT DISTINCT 費目ID FROM 家計簿4月;
採番の方法
追加する行に独自の番号を振るために、適切な番号を取得する方法を採番という。
実際の開発現場では、すでに採番した番号や最後に採番した番号を記録用のテーブルに保存することがある。この記録用のテーブルは採番テーブルと呼ばれる。
管理するのは大変だが、全てのDBMSにおいて共通に利用できる、最も汎用的な方法。
もし、単純な連番で良い場合、一部のDBMSでは連番を管理する機能が提供されている。
1. 連番が自動的に振られる特殊な列を定義できる(AUTO_INCREMENT)
/* MySQL Maria DBの場合 */
CREATE TABLE テーブル名 (
ID INTEGER PRIMARY KEY AUTO_INCREMENT
)
AUTO_INCREMETNTを使う際の制約について
- AUTO_INCREMENTを指定した列に対してインデックスが割り当てられているかどうか
- AUTO_INCREMENTは1つのテーブルにつき、1つの列にしか指定できない
- DEFAULTと併用できない。そもそもAUTO_INCREMENTは自動で採番するのでデフォルト値を使えない。
また、以下3つの方法によりインデックスを列に割り当てることができる。
-
PRIMARY KEY制約・・・primary keyを記述したら自動的にインデックスが割り当てられる。
-
UNIQUE制約・・・primary key同様、unique制約も自動的にインデックスが割り当てられる
-
index(key)・・・下記のように記述することで指定したカラムに対してインデックスを割り当てることができる。
create table test_db.test_table (
key1 int auto_increment,
index(key1)
);
2. 連番を管理してくれる専用の道具が提供されている
シーケンスと呼ばれるカウンターのような機能を用いれば、次に採番すべき値を取り出せるがMySQLで実装されていないようなので、ここでは割愛。
最大値を用いた採番
テーブルに格納された値を調べて、その最大値から連番を取得する方法も考えられるが、以下の副作用が考えられるのでやらないほうが賢明。
- ロックを用いない限り、複数の人に同じ番号が採番される。
- 最後に採番した行の削除後に採番すると、同じ番号を再利用してしまい、主キーとして利用する場合の不変性の崩壊が懸念される。
11.3 データベースをより安全に扱う
ACID特性とは
ITの世界では、『データを正確に安全に取り扱うためにシステムが備えるべき4つの特性』としてACID特性が知られている。
- 原子性(Atomicity)・・・処理が中断しても中途半端な状態にならない
- 一貫性(Consistency)・・・データの内容が矛盾したものにならない
- 分離性(Isolation)・・・複数の処理を同時実行しても副作用が無い
- 永続性(Durability)・・・記録した情報は消滅せず保持されし続ける
バックアップの仕組み
データベースの全内容(テーブル格納や格納されたデータなど)をファイルに出力することができるもの。バックアップは毎日や毎週などの定期的な間隔で自動的に行われるように設定される。
出力されたバックアップファイルは、データベースから独立した別の記憶媒体(磁気記憶装置やテープ装置などに)コピーし、大切に保管する必要がある。
2つのバックアップ方式
- オフラインバックアップ・・・DBMSを停止して行うバックアップ。整合性を保ちつつバックアップを行うもっとも簡単な方法
- オンラインバックアップ・・・DBMSを稼働させながら行うバックアップ。稼働しながら整合性のあるバックアップデータを取得できるが、便利な反面、制約を伴うこともある。使用に際にはDBMSのマニュアルを確認する必要がある。
2つのファイルをバックアップ
- データベースの内容・・・低頻度(日時、週次、月次など)で。
- ログファイルの内容・・・高頻度(数分ごと～数時間ごとなどで)。ここでのログファイルとはデータベースのログファイルでありREDOログ(アーカイブログ)またはトランザクションログと呼ばれ、その内容は『それまで実行したすべてのSQL文』。重要な情報システムではデータベースの内容だけではなくログファイルもバックアップを行う。
ロールバックとロールフォワード
- ロールバック・・・実行した処理を取り消すこと。データベースの利用中に、SQLの失敗やデッドロックで度々発生する。
- ロールフォワード・・・まだ実行されていない処理を実行すること。障害復旧時に行われる処理であるため、滅多に発生しない。
高速化の効果を測定する
DBMSは環境に応じて、どの表に、どの順番で、どのような方法でアクセスすれば最も高速な方法であるかを分析し、プラン(plan)と呼ばれる作戦を立ててから実行に移る。 プランではインデックスを使って検索を行うか、1行ずつ地道に調べていくかの決定も含まれている。
EXPLAIN PLAN文、またはEXPLAIN文を使って指定したSQL文を実行するプランを調べることが出来る。
EXPLAIN SELECT * FROM テーブル名 WHERE 検索カラム = '検索文字列'