#テーブル走査の流れ（レコードのアクセス方法）
そもそもテーブルにクエリを投げた時に、走査（スキャン）がどのようになされるか。
基本的には、
データブロックという行レコードをまとめた単位のものを、メモリ上に読み込んで
そこで縦(列)が先に走って、次に横（行）が走って検索条件にヒットしたものを返す
みたいなイメージで
例えば、データブロックが8KB, 1行分レコードが1KBなら、
データブロックには、8行分のデータを詰め込まれるってこと。
| データブロック(8KB) |
|---|
| 行データ(1KB) |
| 行データ(1KB) |
| 行データ(1KB) |
| 行データ(1KB) |
| 行データ(1KB) |
| 行データ(1KB) |
| 行データ(1KB) |
| 行データ(1KB) |
※データブロックはこんなイメージ
※ちなみに、ディスクに前もって、詰め込めるデータブロックの論理的な範囲を設定したものをエクステントと呼ぶ。
#テーブルをスキャンする流れは、大きく分けて2種類
##①全表走査（フルテーブルスキャン）
その名の通り、
SQLで指定された表の全レコードにアクセスして、検索条件を満たす行を1行ずつチェックする方法
1.表の全データにアクセスする
2.検索条件を満たす行をチェックする
が基本の流れ。
全レコードを厳密にいうと、
過去に最もデータが挿入された位置のデータブロックまでのデータのことで、
例えば
今は格納してるレコードが0件でも、前に100万件のレコードを格納してたら、
その100万件のレコードが挿入されたところまでの位置のデータブロックが全レコードの範囲ってことになる。
##②索引走査(インデックススキャン)
索引走査、索引スキャン、インデックス走査、インデックススキャン、色々組み合わせ言い方ありますね〜
翻訳してるかしてないかですねw
インデックスは、その名の通り目次の役割を果たします。
本を読むと、最初に目次がありますが、あれです。
1.検索のための索引（さくいん）情報を使う
2.条件に該当するデータの場所を特定
3.表にアクセスする
という流れです。
###インデックスの有無で処理速度が大きく変わる
もちろん目次が使えるので、検索が早くできます。（チートですなチート。）
文献とか読んでても、目次見てココだけ読みたい時って他を飛ばしちゃいますもんね。
ただし、データの読込量によっては全表スキャンの方が早くなるケースもあるので、注意が必要。(後述)
最もよく利用されているインデックスは、**B-Tree索引(二分木走査)**というやつらしいが、
アルゴリズムまでは突っ込みません。(ご了承願いたい)
参考：いい感じの記事見つけた
###索引スキャンだと、メモリ上の読込データはどうなるの？
全表スキャンは「全データブロック」の読み込みですが、
索引スキャンでは、「索引ブロックの読み込み ＋ データブロック」の読み込みをして、メモリ上に展開する。
表にまとめると
| 種類 | メモリ上のデータ |
|---|---|
| 全表スキャン | 全データブロック |
| 索引スキャン | インデックスブロック ＋ データブロック |
「索引ブロックの読み込み＋データ・ブロック」の読み込みをするので、
表からある程度以上の割合を抽出する場合には、
全表スキャンの方が効率的になる可能性があり、注意が必要です。
一般的に、
検索したいレコード件数が、レコード全体の5〜15％程度までの場合は、索引スキャンの方が効率的
と言われている。
#そもそもテーブル上でのインデックスのイメージは？？
例えば、
従業員のポジション（役職）テーブルがあるとして、
| emp_no | emp_name | position |
|---|---|---|
| 1 | 鈴木 | 平社員 |
| 2 | 山田 | 平社員 |
| 3 | 小林 | 平社員 |
| 4 | 三上 | 課長 |
| 5 | スミス | 社長 |
| 6 | 村上 | 部長 |
インデックスは、下記の感じで、
列の値と行の位置情報を紐づけるテーブル情報みたいなもの。
| emp_name | 行の位置情報 |
|---|---|
| スミス | 5行目 |
| 三上 | 4行目 |
| 山田 | 2行目 |
| 小林 | 3行目 |
| 村上 | 6行目 |
| 鈴木 | 1行目 |
| ※emp_nameにインデックスを張ったイメージ |
例えば、スミス社長のデータを検索する場合、
-
インデックスにアクセス
- 頼むカンペを見させてください。
-
スミス社長がポジション（役職）テーブルのどこにあるか割り出す
- 5行目かー！！
-
その情報を基に、ポジション（役職）テーブルに実際にピンポイントアクセス
- 鬼のキラーピンポイントクロス！！ゴールしました。
こんな感じで、一部のデータに効率よくアクセスできる。
####ちなみに、非ユニークインデックスってのも張れるとさ。
ユニーク索引と非ユニーク索引があって、
よく使われるのは、ユニーク索引なイメージ。
カラムに入る値はユニークにして、そこにインデックスを貼って、ユニークインデックスを作る。
※非ユニーク索引は値の重複を許す索引で、一部の複数レコードを効率的に抽出できるみたいですわ。
#パフォーマンスを低下させるインデックスとは
###無駄なインデックスは消す！！
####パフォーマンス低下の原因に
例えば、
- 上記のポジション（役職）テーブルの全カラムにインデックスを作成。
- この状態で表にINSERT処理が行われると、表に行が追加される
- 同時に3つのインデックスにも自動的に新規のデータが追加される
つまり、インデックスがある分だけ、
表とインデックスの間で、データの同期を取るための時間や負荷が掛かるため、
結局、無駄なインデックスは、更新処理のパフォーマンス低下を招いてしまう。
####想定とは違った挙動になるリスク
検索時に自分が想定したものとは違うインデックスが使用される確率が上がってしまうリスクもある
というのも、オプティマイザーがよさそうなインデックスは判定して使用しているから
###カーディナリティが低いとインデックスの意味ない（B-TREEの弱点）
カーディナリティとは、端的にいうと、
カラム内のデータの種類がたくさんあるか、少ないかってことで、
少ない場合を「カーディナリティが低い」ってなんか横文字で洒落乙な鼻につく気取った言い方をします。
インデックスが貼られているカラムにおけるカーディナリティが極めて低いと、
インデックスの精度が低くなってしまい、
インデックスの効果を最大限に活かすことができない。
女、男、女、女…
だけのカラムだったら、インデックス張る意味ってなりますわな。
#実際にSQLで索引(インデックス)はどのように貼る？
一例、紹介して行きやす。
####インデックスを作成するよーってとき
CREATE INDEX index_name
ON table_name(
column_name,
column_name
)
;
####テーブル作成時にインデックスも作っちゃうよーってとき
CREATE TABLE db_name.table_name(
column_name data_type,
...,
INDEX [index_name](column_name, ...)
);
####既存テーブルにインデックスを作っちゃうよーってとき
ALTER TABLE table_name ADD INDEX index_name(
column_name
);
####既存テーブルにユニークインデックスを作っちゃうよーってとき
ALTER TABLE table_name ADD UNIQUE(
column_name
);
####既存テーブルのユニークインデックスを消しちゃうよーってとき
ALTER TABLE table_name DROP INDEX index_name;
#なるほど
インデックス、索引、目次、カンペ、カンニング
データベースもカンニングしたかったのか〜
以上、
ありがとうございました。