MySQLのインデックスを学ぶ (2)

Linux-DBシステム構築運用入門をさらに読んでいる。前回は主に Chapter 8 を読んだ内容をメモったが今回は Chapter 9 の内容。

INSERT とインデックス

インデックスが多いと INSERT の性能が落ちる、という話題はなんとなく見聞きしていたが、何故性能が落ちるのかについてはわかっていなかった。詳しくは Linux-DBシステム構築運用入門 P210 の図を見てもらうとして*1、ランダム INSERT よりも昇順 INSERT のほうがインデックスのリーフブロックの仕様効率が良い == インデックスのサイズが小さくなる == バッファキャッシュに載りやすくなる == I/O が減る、というのは知らなかったしとても勉強になった。インデックスを更新するために INSERT でもディスクから読み込みが発生する、というのも知らなかった。

クラスタインデックスとランダム INSERT/昇順 INSERT

InnoDB はクラスタインデックスなので主キーと他の列のデータが同じリーフブロックに格納される。なので、ランダム INSERT と昇順 INSERT の差はより顕著に表れるようだ。これも図を見ながらだとよく理解できる。インデックス追加時にリーフブロックが分割されてディスク上をデータが移動するとき、クラスタインデックスだと移動させるべきデータ量が多いのでオーバーヘッドも大きいということか。クラスタインデックスの主キーはセカンダリインデックスにも含まれるのでインデックスサイズを小さくするためにサイズの小さい整数値などを使え、というのは前回まとめた際にも読んでいたが、 UUID のようなランダム値を使うとデータサイズ以外にも悪影響があり得るのか。

インデックスとハッシュ値

URL などにインデックスをはる場合にハッシュ値をとって url_hash のような別カラムに保存し、ハッシュ値にインデックスをはって検索する、というテクニックはありがちでおれも知っていたが、ハッシュ値 == md5 とか sha1 とかのハッシュ関数を利用してアプリケーション側で計算するもの、と思い込んでいた。Linux-DBシステム構築運用入門で紹介されていたのは CRC32() という SQL 関数を利用して、 sha1 などよりも短い == データサイズの小さい数値型のハッシュ値をとり、検索時に url と url_hash を両方 WHERE 句に指定する、という方法だった。これは現実的で良いアプローチだなと思う。

こういうクエリになる。

SELECT * FROM t WHERE url = 'http://www.google.co.jp/' AND url_hash = CRC32('http://www.google.co.jp/');

CRC32() でとったハッシュ値は衝突耐性が低いようで重複する可能性があるが、インデックスを使って候補を数件程度まで絞り込めれば url そのもので一意に決められる、というわけ。インデックスのサイズと検索の速度のバランスがとれたうまいやり方に思える。ハッシュ値の計算を MySQL に任せられるのでアプリケーション側の処理が楽になるのも良い。

カーディナリティ

これは今まで自分があまり意識できてなかったので反省の意味も込めてメモしておく。カーディナリティは高いほうが良い == インデックスでレコードを効率良く絞り込めるが、マルチカラムインデックスをはる場合にカラムの指定順によってカーディナリティに差が出てしまう。

dev.mysql.com で配布しているサンプル用の employees データベースにインデックスをはってみた例。 idx1, idx2 ともに対象カラムは同じで順番が入れ替わっているが、 Cardinality にはおおきな開きがある。 Seq_in_index が 1 のほうの Cardinality が大きい数字であるほどマルチカラムインデックスとしては効率が良いと言えるはず。

mysql> alter table employees add key idx1 (first_name, gender), add key idx2 (gender, first_name);                     Query OK, 300024 rows affected (4.33 sec)
Records: 300024  Duplicates: 0  Warnings: 0

mysql> show indexes from employees;
+-----------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+
| Table     | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment |
+-----------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+
| employees |          0 | PRIMARY  |            1 | emp_no      | A         |      300141 |     NULL | NULL   |      | BTREE      |         |
| employees |          1 | idx1     |            1 | first_name  | A         |        2543 |     NULL | NULL   |      | BTREE      |         |
| employees |          1 | idx1     |            2 | gender      | A         |        5359 |     NULL | NULL   |      | BTREE      |         |
| employees |          1 | idx2     |            1 | gender      | A         |          14 |     NULL | NULL   |      | BTREE      |         |
| employees |          1 | idx2     |            2 | first_name  | A         |        5265 |     NULL | NULL   |      | BTREE      |         |
+-----------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+
5 rows in set (0.00 sec)

Linux-DB システム構築/運用入門 (DB Magazine SELECTION)

*1:まだお持ちでない？ぜひ当エントリ内のリンクから購入を！