COUNT 関数を使ってMySQL のインデックスの基本を理解する
Linux-DB システム構築/運用入門の8章「インデックスのチューニング(前編)」を読んだので、インデックスの基本について実際に手を動かしながら勉強してみようと思います。
内容としては、クエリを実行する際に、「インデックスだけにアクセスした場合」と、「データにもアクセスする場合」のI/O 回数の違いが、パフォーマンスにどれだけ影響を与えるか調べてみるというものです。
環境
今回は、インデックスだけにアクセスした場合と、データにもアクセスする場合のパフォーマンスの違いについて調べたいので、インデックスの構造が「キーの値, データの位置」となっているMyISAM の方が調査環境に向いていると判断しました。
- テスト用データ
- 600万行弱
- インデックスなしカラムが1つ
- インデックスありカラムが2つ
- 1つはNOT NULL のインデックスつきカラム
- 1つはNULL ありのインデックスつきカラム
mysql> SHOW fields FROM test_data; +--------------+-----------------------------------+------+-----+---------------------+----------------+ | Field | Type | Null | Key | Default | Extra | +--------------+-----------------------------------+------+-----+---------------------+----------------+ | id | int(10) unsigned | | PRI | NULL | auto_increment | | name | varchar(255) | YES | | NULL | | | key | datetime | | MUL | 0000-00-00 00:00:00 | | | nullable_key | datetime | YES | MUL | NULL | | +--------------+-----------------------------------+------+-----+---------------------+----------------+ mysql> SELECT COUNT(*) FROM test_data; +----------+ | COUNT(*) | +----------+ | 5967003 | +----------+
検証したいこと
本を読んで学んだ知識を検証対象として、実際にその通りになるのか確かめてみます。検証したいことは以下の通りです。
1. インデックスだけを読む場合と、データも読む場合では実行時間にどのくらい差があるか
- MyISAM のインデックスは「キーの値, データの位置」という構成になっているので、インデックスを使ってデータにアクセスする場合、2回のI/0 が必要になる
- 1. インデックスから条件に一致するノードを取得
- 2. ノードに記録されている「データの位置」を基に実データにアクセスする
この性質を利用して、インデックスだけを読む場合と、データも読む場合では実行時間にどのくらいの差があるか検証します。
2. インデックスを使った際、インデックスのキーになっているカラムだけを取得する場合は、実データへのアクセスが必要なくパフォーマンスが向上する
MySQL のインデックスは、キーの値自体をインデックスに含むので、その値だけを取得する場合は実データへのアクセスが必要ないはずです。この理解が正しいか検証します。
3. 実データにアクセスする必要がある場合、インデックスを使うよりも、フルテーブルスキャンの方が速い場合がある。また、このことをMySQL のオプティマイザが判断できないことがある
- 1 で説明した通り、実データにアクセスする際には2回のI/O が必要になる
- この時、1回目のI/O はインデックスがソート済みで格納されているためシーケンシャルアクセスだが、実データへのアクセスはランダムアクセスになる
- ヒットするレコードがN 件だとすると、ほぼN 回のランダムアクセスが発生する
- 一方、フルテーブルスキャンは、全データを読み出す必要があるが、シーケンシャルアクセスである
- 通常MySQL はI/O をブロック単位で行なうが、フルテーブルスキャンの場合は、一回のI/O で複数のブロックを一気に読むことができ、これによってI/O 回数を減らす工夫をしている
この性質から、ヒットするレコードの件数が多い場合は、インデックスを使ってランダムアクセスが多数発生するより、フルテーブルスキャンを使った方がI/O 回数が少なくなり、パフォーマンスが向上します。この動作を検証します。
検証方法
今回の検証にはCOUNT 関数を用います。
COUNT()、MIN()、SUM() などの集約（要約）関数では、NULL 値は無視されます。例外は COUNT(*) です。この関数は、個々のカラム値ではなくレコードをカウントします。 たとえば、以下のステートメントでは 2つのカウントが行われます。 最初は、テーブルにあるレコード数のカウントです。2 番目は age カラムにある非 NULL 値のカウントです。
mysql> SELECT COUNT(*), COUNT(age) FROM person;
MySQL :: MySQL 4.1 リファレンスマニュアル :: A.5.3 NULL 値の問題
ここにある通り、COUNT 関数は以下のように動作します。
- COUNT(*) はカラムの値は見ずに単純にレコード数をカウントするので、インデックスだけを読めば結果を返すことができる
- COUNT 関数の引数として、インデックス以外のカラムを指定すると、インデックスを辿って実データにアクセスし、そのカラムの値がNULL かどうかチェックしつつか数え上げを行う
COUNT 関数のこの性質を利用すれば、インデックスだけにアクセスする動作と、実データにもアクセスする動作を簡単にスイッチでき、その差を確認することができます。
検証
1. インデックスだけを読む場合と、データも読む場合では実行時間にどのくらい差があるか
まず、インデックスだけを読む場合。
mysql> SELECT SQL_NO_CACHE COUNT(*) FROM test_data WHERE key BETWEEN '2009-04-01 00:00:00' AND '2009-04-30 23:59:59'; +----------+ | COUNT(*) | +----------+ | 298070 | +----------+ 1 row in set (0.19 sec) mysql> EXPLAIN SELECT SQL_NO_CACHE COUNT(*) FROM test_data WHERE key BETWEEN '2009-04-01 00:00:00' AND '2009-04-30 23:59:59'; +----+-------------+-----------+-------+---------------+-----------+---------+------+--------+--------------------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +----+-------------+-----------+-------+---------------+-----------+---------+------+--------+--------------------------+ | 1 | SIMPLE | test_data | range | key_index | key_index | 8 | NULL | 292889 | Using where; Using index | +----+-------------+-----------+-------+---------------+-----------+---------+------+--------+--------------------------+ 1 row in set (0.00 sec)
Extra に「Using index」が表示されているので、インデックスだけが読まれていることが分かります。
次に、インデックスとデータの両方を読む場合。
id というインデックスに関係ない値を使ってCOUNT を実行することで、実データへのアクセスが発生するようにします。
mysql> SELECT SQL_NO_CACHE COUNT(id) FROM test_data WHERE key BETWEEN '2009-04-01 00:00:00' AND '2009-04-30 23:59:59'; +-----------+ | COUNT(id) | +-----------+ | 298070 | +-----------+ 1 row in set (2.07 sec) mysql> EXPLAIN SELECT SQL_NO_CACHE COUNT(id) FROM test_data WHERE key BETWEEN '2009-04-01 00:00:00' AND '2009-04-31 23:59:59'; +----+-------------+-----------+-------+---------------+-----------+---------+------+--------+-------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +----+-------------+-----------+-------+---------------+-----------+---------+------+--------+-------------+ | 1 | SIMPLE | test_data | range | key_index | key_index | 8 | NULL | 292889 | Using where | +----+-------------+-----------+-------+---------------+-----------+---------+------+--------+-------------+ 1 row in set (0.00 sec)
Extra に「Using index」が表示されなくなったことから、実データへのアクセスが発生したことが分かります。
結果として、パフォーマンスに10倍以上の差がでました。id は主キーなので、COUNT(id) の結果はCOUNT(*) と同じになるのですが、MySQL が内部的にCOUNT(*) に書き換えてくれることはないみたいです。
2. インデックスを使った際、そのインデックスのキーになっているカラムだけを取得する場合は、実データへのアクセスが必要なく、パフォーマンスが向上する
インデックスのキーになっているkey を使ってCOUNT 関数を実行します。
mysql> SELECT SQL_NO_CACHE COUNT(key) FROM test_data WHERE key BETWEEN '2009-04-01 00:00:00' AND '2009-04-30 23:59:59'; +------------+ | COUNT(key) | +------------+ | 298070 | +------------+ 1 row in set (0.20 sec) mysql> EXPLAIN SELECT SQL_NO_CACHE COUNT(key) FROM test_data WHERE key BETWEEN '2009-04-01 00:00:00' AND '2009-04-30 23:59:59'; +----+-------------+-----------+-------+---------------+-----------+---------+------+--------+--------------------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +----+-------------+-----------+-------+---------------+-----------+---------+------+--------+--------------------------+ | 1 | SIMPLE | test_data | range | key_index | key_index | 8 | NULL | 292889 | Using where; Using index | +----+-------------+-----------+-------+---------------+-----------+---------+------+--------+--------------------------+ 1 row in set (0.00 sec)
EXPLAIN に「Using index」が表示されていることから、このクエリの結果を取得する際にはインデックスにだけしかアクセスしていません。実データへのアクセスは発生してないことから、仮説が正しいことが分かりました。インデックスにだけしかアクセスしていないため、クエリの実行も高速でした。
また、COUNT(key) としたのでCOUNT(*) の時と比べて、NULL 値チェックが入りますが、そのオーバーヘッドはほとんどないことも分かりました。若干あったとしても、I/O が発生する場合の処理に比べれば、実行時間への影響は小さいものです。
この結果より、SELECT 時に取得する全てのカラムが、そのクエリで使用されるインデックスの構成要素になっている場合、実行時には、実データへのアクセスが発生しないことが分かります。
僕は前まで、SELECT 時に取得するカラム数を少なくすることで、SELECT を高速化することができると思っていました。でも実際には、実データへのアクセスを発生させないようにカラムを選択することが重要で、それによってI/O 回数を減り、パフォーマンスが向上するということを理解することができました。
3. 実データにアクセスする必要がある場合、インデックスを使うよりも、フルテーブルスキャンの方が速い場合がある。また、このことをMySQL のオプティマイザが判断できないことがある
COUNT(id) を使って実データへのアクセスを発生させると共に、結果がテーブルのレコード数の70% を占めるようなクエリを発行します。
まず、インデックスを使用する場合。
mysql> SELECT SQL_NO_CACHE COUNT(id) FROM test_data WHERE nullable_key IS NULL; +-----------+ | count(id) | +-----------+ | 4247195 | +-----------+ 1 row in set (25.38 sec) mysql> EXPLAIN SELECT SQL_NO_CACHE COUNT(id) FROM test_data WHERE nullable_key IS NULL; +----+-------------+-----------+------+--------------------+--------------------+---------+-------+--------+-------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +----+-------------+-----------+------+--------------------+--------------------+---------+-------+--------+-------------+ | 1 | SIMPLE | test_data | ref | nullable_key_index | nullable_key_index | 9 | const | 878918 | Using where | +----+-------------+-----------+------+--------------------+--------------------+---------+-------+--------+-------------+ 1 row in set (0.00 sec)
EXPLAIN のkey の部分を見ると、インデックスが使われていることが分かります。しかし、クエリの実行には25秒もかかってしまいました。
次に、IGNORE INDEX ヒントを使って、フルテーブルスキャンで同じクエリを実行してみます。
mysql> SELECT SQL_NO_CACHE COUNT(id) FROM test_data IGNORE INDEX(nullable_key_index) WHERE nullable_key IS NULL; +-----------+ | count(id) | +-----------+ | 4247195 | +-----------+ 1 row in set (13.21 sec) mysql> EXPLAIN SELECT SQL_NO_CACHE COUNT(id) FROM test_data IGNORE INDEX(nullable_key_index) WHERE nullable_key IS NULL; +----+-------------+-----------+------+---------------+------+---------+------+---------+-------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +----+-------------+-----------+------+---------------+------+---------+------+---------+-------------+ | 1 | SIMPLE | test_data | ALL | NULL | NULL | NULL | NULL | 5967003 | Using where | +----+-------------+-----------+------+---------------+------+---------+------+---------+-------------+ 1 row in set (0.00 sec)
なんと、フルテーブルスキャンで実行した方が、半分の時間でクエリを実行することができました。やはり、ヒットするレコードの件数が多い場合、インデックスを使ってしまうとランダムアクセスが多数発生し、フルテーブルスキャンよりパフォーマンスが落ちてしまいました。
また、IGNORE INDEX ヒントを使わないと、オプティマイザが効率の悪い実行計画を立ててしまうことも分かりました。
まとめ
今回、「Linux-DB システム構築/運用入門」の8章を読んで、MySQL のクエリチューニングをする際には、EXPLAIN を見ながらインデックスが使われるようにクエリを組み立てること以上に、I/O 回数をいかに減らすかという点が重要だということを理解することができました。
特に、今回の最後の検証で扱った例では、ランダムアクセスの効率がいかに悪いのかを実感することができました。今までフルテーブルスキャンなんて最悪と思っていた僕は、その奥にあるI/O、ディスクのシーク時間を意識することができていませんでした。その本質を理解することができ、とても大きな学びを得たと実感しています。松信本スバラシ。
最後にp.182 の本文からちょっと引用(一部改変)して、エントリーを終わります。
RDBMS のパフォーマンスの支配要因となるディスク性能を考える上で、1回のI/O で転送する量よりも、I/O 回数のほうがずっとインパクトが大きい。例えば、1KB ずつ10,000回読むのと、10KB ずつ1,000回読むのとでは、同じデータ量を読み込むにもかかわらず、後者の方がずっと高速になります。これは、一回のI/O のたびに(キャッシュされていなければ)ディスクのシーク、回転待ちが発生するためです。
