MySQL の Index の勉強をする
概要
MySQL の INDEX って正直何って感じのレベルの人間が ISUCON で活躍したいがために頑張って勉強する話です。
当方インフラエンジニアとか言いながらアプリケーション周りとかばかりで RDBMS は MySQL がなんとなく使えるレベルになります。
学習
実際にやってみる
下記を参考に ruby でやってみる
データの insert 30万件
code:rb
# Insert Data
300000.times do
category = rand(9999)
c.query("INSERT into fuga(category, name) VALUES (#{category}, '#{name}')")
end
select 300 回
code:rb
require 'benchmark'
require 'mysql2'
c = Mysql2::Client.new(host: '127.0.0.1', username: 'root', password: '', database: 'isucon')
# SELECT 300 times
s = c.prepare("SELECT name FROM fuga WHERE category = ?")
r = Benchmark.realtime { 300.times { s.execute(rand(10000)) }}
p r
結果
13.856520000030287
explain
code:sh
mysql> explain select name from fuga where category = 0;
+----+-------------+-------+------------+------+---------------+------+---------+------+--------+----------+-------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+-------+------------+------+---------------+------+---------+------+--------+----------+-------------+
| 1 | SIMPLE | fuga | NULL | ALL | NULL | NULL | NULL | NULL | 300095 | 10.00 | Using where |
+----+-------------+-------+------------+------+---------------+------+---------+------+--------+----------+-------------+
1 row in set, 1 warning (0.00 sec)
index 貼る
code:sh
mysql> alter table fuga add index category_idx(category);
Query OK, 0 rows affected (0.37 sec)
Records: 0 Duplicates: 0 Warnings: 0
もう一回測る
0.15550900006201118
すご!
id と category を取得する時は Extra が Using Index になっているのがわかる
code:sh
mysql> explain select id from fuga where category = 1000;
+----+-------------+-------+------------+------+---------------+--------------+---------+-------+------+----------+-------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+-------+------------+------+---------------+--------------+---------+-------+------+----------+-------------+
| 1 | SIMPLE | fuga | NULL | ref | category_idx | category_idx | 4 | const | 37 | 100.00 | Using index |
+----+-------------+-------+------------+------+---------------+--------------+---------+-------+------+----------+-------------+
1 row in set, 1 warning (0.00 sec)
mysql> explain select category from fuga where category = 1000;
+----+-------------+-------+------------+------+---------------+--------------+---------+-------+------+----------+-------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+-------+------------+------+---------------+--------------+---------+-------+------+----------+-------------+
| 1 | SIMPLE | fuga | NULL | ref | category_idx | category_idx | 4 | const | 37 | 100.00 | Using index |
+----+-------------+-------+------------+------+---------------+--------------+---------+-------+------+----------+-------------+
1 row in set, 1 warning (0.00 sec)
mysql> explain select name from fuga where category = 1000;
+----+-------------+-------+------------+------+---------------+--------------+---------+-------+------+----------+-------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+-------+------------+------+---------------+--------------+---------+-------+------+----------+-------+
| 1 | SIMPLE | fuga | NULL | ref | category_idx | category_idx | 4 | const | 37 | 100.00 | NULL |
+----+-------------+-------+------------+------+---------------+--------------+---------+-------+------+----------+-------+
1 row in set, 1 warning (0.00 sec)
INDEX で使う操作
code:sql
-- 参照
SHOW INDEX FROM table_name;
-- 追加
ALTER TABLE table_name ADD INDEX index_name(column_name);
-- 追加 複合インデックス
ALTER TABLE table_name ADD INDEX index_name(column1_name, column2_name);
-- 削除
ALTER TABLE table_name DROP INDEX index_name;
-- テーブルのインデックス情報を取得
SHOW INDEXES FROM table_name;
EXPLAIN SELECT文 で index の効果を確認する。「possible_keys」「key」「Extra」でチェックできる。
mysql の index について
インデックスは速度を改善することもあるが、挿入の邪魔になって遅くなることもある
データが変化するたびにインデックスを更新する必要が出てくる
データを保存するために必要な容量が増えてしまう
検索などの性能は向上するが、容量が犠牲になる
部分的インデックス
ALTER TABLE table_name ADD INDEX(column_name(4))
上記は最初の4バイトだけにインデックスを付与できる
複数のレコードに同じインデックスの値がついてしまう可能性がある
マルチカラムインデックス(複合インデックス)
MySQL では1つのクエリを実行する時、1つのテーブルにつき1つのインデックスしか使用できない
WHERE と ORDERBY が使われている場合は複合インデックスを使う
ユニークインデックス
レコードを挿入または更新しようとするとき、全ての値を調べて、同じ値が既に存在しないことを確認する
制約条件として働く
インデックスの構造
B-TreeINDEX
最もよく使われるインデックスの型
Tree 構造でデータを保持する
ヘッダブロックにブランチブロックがぶら下がる
ブランチブロッグのほうがヘッダブロッグより細かい範囲を保持する
結果 INDEX 順に表示するのでクイックソートの必要がなくなり並び替えが早くなる
ハッシュインデックス
Rツリーインデックス
tips
テーブルの 30% を超えるアクセスには index が使われない場合がある
MySQL がテーブルスキャンの方が高速だと判断したら使われない
DELETE のコストはかなり高い
論理削除用のカラムを作り UPDATE で処理したほうがいい
index が使われないパターン
LIKEがワイルドカードで始まる
DB 全体を読んだ方が早い時
ORDER BY には使われない
WIP