はじめに
MySQLのキー(インデックス)についてまとめる。
インデックス
キーとか、インデックスとか呼ばれる。インデックスはデータベースで検索速度を高めるためには必要不可欠なもの。書籍の索引のようなものとして説明されることが多い。まずは、サンプルのデータベースを作成してインデックスを作成する方法はみていく。
mysql> create database hatena; Query OK, 1 row affected (0.01 sec) mysql> use hatena Database changed
インデックスの作成方法
インデックスの作成方法は2つある。create tableの段階で設定するか、alter tableでインデックスを付与するかの2通り。create tableの段階で設定する場合は下記の通り。index [index name] (hoge, fuga)またはkey [index name] (hoge, fuga)というように記述する。インデックス名は付けても、付けなくてもどちらでも良い。
create table tbl01( id int, name varchar(255), memo text, index (name) );
設定されているかどうかは、show createで確認できる。←#Here#の部分。
mysql> show create table tbl01 \G;
*************************** 1. row ***************************
Table: tbl01
Create Table: CREATE TABLE `tbl01` (
`id` int(11) DEFAULT NULL,
`name` varchar(255) COLLATE utf8mb4_general_ci DEFAULT NULL,
`memo` text COLLATE utf8mb4_general_ci,
KEY `name` (`name`) ←#Here#
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci
1 row in set (0.01 sec)
一旦、テーブルを削除して、alter tableで付与してみる。もちろんテーブルを削除する必要はなく、alter table <table name> drop index <index name>;でインデックスは削除できる。
mysql> drop table tbl01; Query OK, 0 rows affected (0.01 sec) mysql> create table tbl01( id int, name varchar(255), memo text ); Query OK, 0 rows affected (0.00 sec) mysql> alter table tbl01 add index (id); Query OK, 0 rows affected (0.02 sec) Records: 0 Duplicates: 0 Warnings: 0
ここではdescで確認してみる。問題なく付与されている。
mysql> desc tbl01; +-------+--------------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +-------+--------------+------+-----+---------+-------+ | id | int(11) | YES | MUL | NULL | | | name | varchar(255) | YES | | NULL | | | memo | text | YES | | NULL | | +-------+--------------+------+-----+---------+-------+ 3 rows in set (0.00 sec)
MySQLのインデックスは部分指定することも可能である。インデックスに利用するカラムの先頭2文字のみをインデックスとして利用したい場合、下記のよう記述する。MySQLでは、インデックスを先頭文字列から検索していくので、先頭文字列をワイルドカードで指定したwhere hoge like *fugaのようなwhere句を利用した場合、インデックスは使用されない。
index (name(2))
複合インデックス
インデックスは1つだけではなく、複数インデックスを付与できる。複数のインデックスのことを複合インデックスと呼ぶ。無論、たくさんインデックスをつければよいというわけではない。
create table tbl02(
id int,
a varchar(255),
b varchar(255),
c varchar(255),
index ind_name(a, b)
);
mysql> show create table tbl02 \G;
*************************** 1. row ***************************
Table: tbl02
Create Table: CREATE TABLE `tbl02` (
`id` int(11) DEFAULT NULL,
`a` varchar(255) COLLATE utf8mb4_general_ci DEFAULT NULL,
`b` varchar(255) COLLATE utf8mb4_general_ci DEFAULT NULL,
`c` varchar(255) COLLATE utf8mb4_general_ci DEFAULT NULL,
KEY `ind_name` (`a`,`b`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci
1 row in set (0.00 sec)
わざわざ複合インデックスにしなくても、1つずつインデックスを付与することもできるが、下記のような場合にインデックスが利用されない。さきほどのcreate tableの内容とは異なるが、ここでは、インデックスaとbが独立して付与されているとする。単独でインデックスが付与されていると、このような条件式では上手く働かない。
WHERE a >= n ORDER BY b;
この場合、複合インデックスを利用する。注意としてaがwhere句で使用されないと、order byでインデックスが利用されない。つまり、インデックスには使用される順番がある。
alter table <tbl> add index index_name(a, b);
また、前回の記事で作成したordersテーブルには3000万レコードあるが、約900万レコード以上が対象となるSQLではINDEXを使わない方が速く処理できるたりする。つまりMySQLがテーブルの30%を超えるレコードにアクセスする必要が生じる場合、インデックスが使用されない。
ユニークキー
ユニークキーは重複を許さないカラムに付与する。ユニークキーはインデックスの1種で一意な値を保証するもの。
mysql> drop table tbl01; Query OK, 0 rows affected (0.00 sec) mysql> create table tbl01( id int, name varchar(255), memo text, unique (name) ); Query OK, 0 rows affected (0.01 sec)
descで確認する。
mysql> desc tbl01; +-------+--------------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +-------+--------------+------+-----+---------+-------+ | id | int(11) | YES | | NULL | | | name | varchar(255) | YES | UNI | NULL | | | memo | text | YES | | NULL | | +-------+--------------+------+-----+---------+-------+ 3 rows in set (0.01 sec)
プライマリーキーとユニークキーは似ているようものであるが、プライマリーキーはテーブルに1つだけ作成でき、nullを許容しないキーなので、ユニークキーを厳密にしたようなキー。
B+Treeインデックス
MySQLのInnoDBエンジンでは、B+Treeインデックスが利用できる。B+TreeインデックスはB-Treeインデックスと少し異なり、ポインタを持っていたりするそうだが、大きくは変わらない。B+Treeインデックスの詳細はMySQL with InnoDB のインデックスの基礎知識とありがちな間違いがわかりやすい。また、explainの見方については漢(オトコ)のコンピュータ道: MySQLのEXPLAINを徹底解説!!が詳しい。ここでは試しに速度比較をしてみることにする。サンプルデータはRで作成した。
library(tidyverse)
library(data.table)
d <- tibble(letters, LETTERS) %>%
expand(letters, LETTERS) %>%
mutate(chr = paste(letters, LETTERS, sep = ""))
set.seed(1234)
d <- tibble(
id = 1:1e6,
c1 = sample(d$chr, 1e6, replace = TRUE),
c2 = sample(d$chr, 1e6, replace = TRUE)
)
data.table::fwrite(d,"index_test.csv")
head(d)
# A tibble: 6 x 3
id c1 c2
<int> <chr> <chr>
1 1 kX pW
2 2 dW gY
3 3 xY aL
4 4 yU sI
5 5 pJ bA
6 6 dT eM
tail(d)
# A tibble: 6 x 3
id c1 c2
<int> <chr> <chr>
1 999995 hL cB
2 999996 eO rV
3 999997 uL dS
4 999998 bR zV
5 999999 iA iT
6 1000000 rB gB
テーブル定義を作成し、何もインデックスを付けずに実行してみる。
mysql> CREATE TABLE ind (
id INT NOT NULL,
c1 VARCHAR(2) NOT NULL,
c2 VARCHAR(2) NOT NULL
);
Query OK, 0 rows affected (0.01 sec)
mysql> LOAD DATA INFILE '~/Desktop/index_test.csv'
INTO TABLE hatena.ind
FIELDS TERMINATED BY ','
OPTIONALLY ENCLOSED BY '"'
IGNORE 1 LINES;
Query OK, 1000000 rows affected (8.02 sec)
Records: 1000000 Deleted: 0 Skipped: 0 Warnings: 0
まずは、where c1 = "aa"の条件でどの程度かみてみる。
mysql> select * from ind where c1 = "aa"; +--------+----+----+ | id | c1 | c2 | +--------+----+----+ | 400 | aA | uX | | 1731 | aA | cE | 【略】 | 999692 | aA | vB | | 999738 | aA | uZ | +--------+----+----+ 1477 rows in set (0.40 sec)
インデックスをc1に付与してみると、0.4秒くらいかかっていたのが0.01秒になっていることがわかる。
mysql> alter table ind add index ind_c1(c1); Query OK, 0 rows affected (1.74 sec) Records: 0 Duplicates: 0 Warnings: 0 mysql> select * from ind where c1 = "aa"; +--------+----+----+ | id | c1 | c2 | +--------+----+----+ | 400 | aA | uX | | 1731 | aA | cE | 【略】 | 999692 | aA | vB | | 999738 | aA | uZ | +--------+----+----+ 1477 rows in set (0.01 sec)
さらに知りたい場合は、インデックスとかクエリチューニングの話がやまほど記載されている、実践ハイパフォーマンスMySQL 第3版を参照。
B-Treeインデックスの補足
基本的はB-Treeは下記の図のように木構造である。最下層のデータベースに直結する「リーフノード」、リーフノードにつながる「ノード」、□の「データへのポインタ」で表される。
Source | 達人に学ぶDB設計 徹底指南書
B-Treeインデックスは平均的に優秀なインデックスと言われる。それは「均一性」「持続性」「非等値性」「親ソート性」から説明される。
- 均一性：B-Treeは平衡木なので、リーフとノードがどこでも同じなので、距離が一定になり、計算量も同じになる。
- 持続性：更新によって初期の木構造は崩れていくものの、B-Treeの高さは平均3~4なので、更新にかかる計算量はデータ量の対数で済む。
- 非等値性：B-Treeを構築する際に、キーの値をソートするため、等号だけでなく、不等号、
betweenなど条件も使える。 - 親ソート性：集約関数、OrderBy、集合演算、OLAP関数を使うと、暗黙的にソートが実行されるが、キーを使っているのであれば、B-Treeを構築する際に、キーの値をソートするため、そこでのソートが不要になる。
