InnoDBを使うとき、MyISAMと比較して度々やり玉に挙げられるポイントとして﹁COUNT()が遅い﹂というものがある。確かにInnoDBにおいて行数を弾き出すのにはテーブルスキャンが必要なのだが、そもそもMyISAMのCOUNT()が速い︵テーブルの行数を保持してる︶のが特殊なのであって、InnoDBが遅いわけではないのである。とはいえ、高速なCOUNT()については需要が多く、この問題には多くの人取り組んでおられるようだ。しかしながら、COUNT()のチューニングについては未だ語られていない点があるように見受けられるので、今日はCOUNT()のチューニングについて解説しようと思う。
COUNT(*)、COUNT(col)、COUNT(1)の違い
基本的なことではあるが、COUNT(*)とCOUNT(col)では意味が異なるため、異なる結果が返される場合がある。COUNT(*)はフェッチした全ての行をカウントするが、COUNT(col)ではcolがNULLでない値の場合だけカウントされるという違いがある。colというように単一のカラムではなく、COUNT()の中身はもう少し複雑な式であっても良い。以下に、この違いがはっきり分かる簡単な例を示す。mysql> CREATE TABLE num_tbl (a INT) ENGINE InnoDB; Query OK, 0 rows affected (0.44 sec) mysql> INSERT INTO num_tbl VALUES(0),(1),(2),(3),(NULL); Query OK, 5 rows affected (0.00 sec) Records: 5 Duplicates: 0 Warnings: 0 mysql> SELECT COUNT(*) FROM num_tbl; +----------+ | COUNT(*) | +----------+ | 5 | +----------+ 1 row in set (0.00 sec) mysql> SELECT COUNT(a) FROM num_tbl; +----------+ | COUNT(a) | +----------+ | 4 | <--- 値がNULLのカラムがカウントされない。 +----------+ 1 row in set (0.00 sec) mysql> SELECT COUNT(100/a) FROM num_tbl; +--------------+ | COUNT(100/a) | +--------------+ | 3 | <--- ゼロ除算の結果はNULLなのでカウントされない。 +--------------+ 1 row in set (0.00 sec)このことはチューニングする上で意味があることなので覚えていて貰いたい。 ちなみに、COUNT(*)という表記は慣習的なものであり、実はアスタリスクを指定する意味はあまりない。COUNT(1)を指定しても同じ結果が得られるのである。
よく用いられるCOUNT()高速化対策法
COUNT()を高速化する方法として最もよく利用されるのが、トリガを使う方法であろう。行数を保持するテーブルを作成して、COUNT()したいテーブルにトリガを設定し、INSERTされるごとに行数を+1、DELETEされるごとに-1することで、別テーブルで行数をメンテしようというものである。これにより、COUNT(*)をする代わりに行数を保持しているテーブルから1行のレコードをフェッチするだけで済むため、行数を数える処理が格段に高速化するというわけだ。ただし、この手法では、既にKazuhooku氏がブログで紹介しているように、INSERT時にオーバーヘッドが生じるという問題がある。また、わざわざ別テーブルとトリガをいちいち作成するのは面倒であり、運用の手間が増えてしまう。このようなデメリットがあるとはいえ、それでもCOUNT()を高速化したいんだよ！という場合には、非常に有効な対策である。 また、テーブルがキューとして使われている場合のように、主キーが整数型で途中に欠番がないような場合には、MIN()/MAX()を活用することでCOUNT()と同様の結果を得られるという方法がある。こちらについてはKamipo氏がブログで紹介しているのでそちらを参照されたい。MyISAMにおけるCOUNT()の限界
MyISAMを利用しているといつでもCOUNT()が高速であるか？というと、そういうわけではない。実はMyISAMを利用している場合であっても、高速なのはSELECT COUNT(*) FROM tblというように、テーブル全体の行数を取得するようなクエリでないと速くないのである。例えば、COUNT(col)でNULL値を含む可能性のあるカラムを指定すると、テーブルスキャンが必要になるため、COUNT()は別に速くもなんともない。 GROUP BYを利用した場合も同様で、MyISAMテーブルが保持している「テーブル全体の行数」は役に立たないため、スキャンが必要になる。以下は、MySQL公式サンプルであるWorldデータベースを用いた例である。テーブルスキャンが発生していることが分かる。mysql> EXPLAIN SELECT COUNT(*) FROM Country GROUP BY Continent; +----+-------------+---------+------+---------------+------+---------+------+------+---------------------------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +----+-------------+---------+------+---------------+------+---------+------+------+---------------------------------+ | 1 | SIMPLE | country | ALL | NULL | NULL | NULL | NULL | 239 | Using temporary; Using filesort | +----+-------------+---------+------+---------------+------+---------+------+------+---------------------------------+ 1 row in set (0.01 sec)WHERE句で範囲を絞って行数を数えたいような場合も、﹁テーブル全体の行数﹂は役に立たないため同様にスキャンが生じることになる。以上をまとめると、次のような場合には、MyISAMにおいても高速に行数を取得することは出来ないのである。 ●NULL値が含まれる可能性のあるカラムをCOUNT(col)する場合 ●GROUP BY句を利用する場合 ●WHERE句で行数をカウントする範囲を限定する場合
セカンダリインデックスを使って少しだけ高速化
さて、ここからが今回のエントリの本題である。前置きが長いのはいつものことなので気にしないで頂きたい。 MyISAMと同様の制約は、InnoDBにおいて前述の﹁よく用いられるCOUNT()高速化対策法﹂で紹介したような方法を使っている場合にも当てはまる。特に、WHERE句やGROUP BY句などと絡めて行数を取得したい場合には、テーブルをスキャンする以外に道はない。ここで思い出して頂きたいのが、InnoDBテーブルの構造である。そう、InnoDBはクラスタインデックスを採用しているのである。クラスタインデックスとは、レコードが主キーの一部になっている形式のインデックスで、InnoDBの場合には﹁テーブルスキャン＝主キーのスキャン﹂という図式が成り立つのだ！ ではどうすればCOUNT(*)を高速化できるか？それにはセカンダリインデックスを活用するしかない。例えば次のようなテーブルがあるとしよう。CREATE TABLE t1 ( a bigint(20) unsigned NOT NULL AUTO_INCREMENT, b int(11) DEFAULT NULL, c tinyint(4) DEFAULT NULL, d date DEFAULT NULL, e varchar(200) DEFAULT NULL, f varchar(200) DEFAULT NULL, g varchar(200) DEFAULT NULL, h varchar(200) DEFAULT NULL, i varchar(200) DEFAULT NULL, PRIMARY KEY (a) ) ENGINE=InnoDB DEFAULT CHARSET=utf8;このテーブルには主キーがひとつ設定されているだけであり、現時点ではセカンダリインデックスはない。このテーブルからCOUNT(*)すると、もれなく主キーのスキャンが発生する。以下は、1000万件のデータをつっこんでCOUNT(*)を実行したときの結果である。
mysql> explain select count(*) from t1; +----+-------------+-------+-------+---------------+---------+---------+------+----------+-------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +----+-------------+-------+-------+---------------+---------+---------+------+----------+-------------+ | 1 | SIMPLE | t1 | index | NULL | PRIMARY | 8 | NULL | 10044347 | Using index | +----+-------------+-------+-------+---------------+---------+---------+------+----------+-------------+ 1 row in set (0.07 sec) mysql> select count(*) from t1; +----------+ | count(*) | +----------+ | 10000000 | +----------+ 1 row in set (1 min 3.30 sec)このように、手元のマシンではおよそ1分半かかっている。ちなみに、innodb_buffer_pool_size=1GBに設定してある。 このスキャンが何故遅いか？という理由は、このテーブルの全体的なデータサイズが大きいからだ。今回の例ではバッファプールに収まる行数が限られていることによる要因が大きい。このテーブル全体をスキャンするのに必要なI/O回数が多く、処理に時間を要してしまうのである。このような場合、スキャンを高速化するテクニックとして有効なことのひとつが、セカンダリインデックスをつけることである。 そこで、b︵INT型︶、c︵TINYINT型︶、d︵DATE型︶、e︵VARCHAR(200) CHARACTER SET utf8︶のカラムにそれぞれインデックスをつけて同じクエリを実行してみよう。
mysql> select count(*) from t1; mysql> explain select count(*) from t1; +----+-------------+-------+-------+---------------+------+---------+------+----------+-------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +----+-------------+-------+-------+---------------+------+---------+------+----------+-------------+ | 1 | SIMPLE | t1 | index | NULL | c | 2 | NULL | 10042706 | Using index | +----+-------------+-------+-------+---------------+------+---------+------+----------+-------------+ 1 row in set (0.01 sec) +----------+ | count(*) | +----------+ | 10000000 | +----------+ 1 row in set (2.82 sec)なんとMySQLは自動的に一番小さいサイズのカラムを選択したではないか！もちろん結果も劇的に変化している。1分半→3秒弱である。まさに劇的ビフォーアフター！ このように、COUNT(*)の場合は適切なインデックスがあればそれを勝手に選んでくれるのでユーザーがあまり気にすることはない。ここでひとつポイントとして覚えて︵思い出して︶貰いたいことは、どのインデックスをスキャンしてもCOUNT()の結果自体は変わらないということである。NULL値に対してもエントリが作成されるため、セカンダリインデックスには全ての行レコードに対するエントリが存在するのである。従って、NULL値を区別しなければいけないCOUNT(c)のような場合でも、区別しないCOUNT(*)のような場合でも、セカンダリインデックスをスキャンするだけで正しい結果が得られることになり、オプティマイザは最も効率よくスキャンが出来るカラム＝サイズの小さいカラムを選択するわけだ。ちなみに、COUNT(a)でもカラムcのインデックスが利用される。カラムaはNOT NULLが指定されているため、COUNT(a)とCOUNT(*)は同じ結果になり、どのインデックスを選んでも結果は変わらないからである。 各インデックスを使った場合の結果を比較してみよう。このテーブルのセカンダリインデックスのカラムは、NOT NULLが指定されていない、つまりNULL値を含む可能性があるため、COUNT(b)というようにそのカラムを指定すれば該当するセカンダリインデックスが利用される。以下はb︵INT型︶、d︵DATE型︶、e︵VARCHAR(200) CHARACTER SET utf8︶の比較である。
mysql> select count(b) from t1; +----------+ | count(b) | +----------+ | 10000000 | +----------+ 1 row in set (3.03 sec) mysql> select count(d) from t1; +----------+ | count(d) | +----------+ | 10000000 | +----------+ 1 row in set (3.02 sec) mysql> select count(e) from t1; +----------+ | count(e) | +----------+ | 10000000 | +----------+ 1 row in set (14.08 sec)b、dはcとほとんど差はないが、セカンダリインデックスが全てバッファプールに収まっているからであろう。ちなみに、各インデックスがどの程度ページを消費しているかということは、InnoDBテーブルモニターで確認できる。以下はテーブルモニターの出力例である。
TABLE: name test/t1, id 0 263, columns 12, indexes 5, appr.rows 10179472 COLUMNS: a: DATA_INT DATA_UNSIGNED DATA_BINARY_TYPE DATA_NOT_NULL len 8; b: DATA_INT DATA_BINARY_TYPE len 4; c: DATA_INT DATA_BINARY_TYPE len 1; d: DATA_INT DATA_BINARY_TYPE len 3; e: type 12 len 600; f: type 12 len 600; g: type 12 len 600; h: type 12 len 600; i: type 12 len 600; DB_ROW_ID: DATA_SYS prtype 256 len 6; DB_TRX_ID: DATA_SYS prtype 257 len 6; DB_ROLL_PTR: DATA_SYS prtype 258 len 7; INDEX: name PRIMARY, id 0 456, fields 1/11, uniq 1, type 3 root page 131076, appr.key vals 10179472, leaf pages 360335, size pages 360768 FIELDS: a DB_TRX_ID DB_ROLL_PTR b c d e f g h i INDEX: name b, id 0 457, fields 1/2, uniq 2, type 0 root page 131077, appr.key vals 8533926, leaf pages 16443, size pages 18944 FIELDS: b a INDEX: name c, id 0 458, fields 1/2, uniq 2, type 0 root page 131078, appr.key vals 9, leaf pages 9847, size pages 11313 FIELDS: c a INDEX: name d, id 0 459, fields 1/2, uniq 2, type 0 root page 131079, appr.key vals 2541345, leaf pages 16343, size pages 18816 FIELDS: d a INDEX: name e, id 0 460, fields 1/2, uniq 2, type 0 root page 131080, appr.key vals 8703960, leaf pages 103311, size pages 119360 FIELDS: e acは9800ページ程度、bとdはそれぞれ16000ページ程度、eは100000ページ程度であり、クエリの実行結果をよく反映していることが分かる。このように、COUNT()のチューニングにはインデックスの最適化が重要なのである。︵InnoDBテーブルモニターについては、執筆中の書籍でも触れているので乞うご期待！︶ セカンダリインデックスがついている状態でも、セカンダリインデックスがなく、なおかつNULL値が格納される可能性があるカラムをわざわざ指定すると、次のように主キーのスキャンが行われる。︵EXPLAINを見るとtype=ALLになるが、InnoDBの場合は主キーのスキャンと同じことである。︶
mysql> select count(i) from t1; +----------+ | count(i) | +----------+ | 10000000 | +----------+ 1 row in set (1 min 34.41 sec)
Covering Indexの活用
前節はテーブル全体の件数を数えるクエリについてのチューニングの話であるが、GROUP BY句やWHERE句を用いた場合はどのようなクエリを書けばいいのだろう？もちろんこの場合も先ほどの場合と同様、出来るだけサイズが小さいセカンダリインデックスのスキャンになるのが望ましい。インデックスを用いてスキャンされるためには、Covering Indexがなければいけない。 例えば次のクエリはカラムdに貼らているインデックスがCovering Indexになる。mysql> SELECT FLOOR(YEAR(d)/100)*100 AS drange, COUNT(*) FROM t1 GROUP BY drange HAVING drange IS NOT NULL;;
+--------+----------+
| drange | COUNT(*) |
+--------+----------+
| 1000 | 1283 |
・・・出力省略・・・
| 9900 | 1272 |
+--------+----------+
90 rows in set (10.95 sec)
mysql> SELECT COUNT(*) FROM t1 WHERE d BETWEEN '1990-01-01' AND '2000-01-01';
+----------+
| COUNT(*) |
+----------+
| 1034 |
+----------+
1 row in set (0.00 sec)
次は少々複雑なクエリの例である。この例では、(c,d)というインデックスがCovering Indexとなる。
mysql> SELECT FLOOR(YEAR(d)/100)*100 AS drange, COUNT(*) FROM t1 WHERE c = 100 GROUP BY drange HAVING drange IS NOT NULL;
・・・出力省略・・・
さきほど作成したテスト用テーブルには(c,d)というインデックスはないため(c)が使われるが、この場合c=100で行を絞り込んでから主キーを検索して行をフェッチし、GROUP BYが行われることになり、上記のクエリは割と時間が掛かってしまう。Covering Indexがあれば主キーをフェッチする必要はなくなり、格段に効率が向上するのである。
なお、上記のテーブルをALTERするにはかなり時間がかかるため︵投稿が遅れるので︶、結果は割愛させて頂く！！が、Covering Indexの効果は絶大なので、ぜひ自分で試して見て欲しい。
セカンダリインデックス追加時の注意事項
これまで、セカンダリインデックスを追加することで如何にCOUNT()クエリが高速化されるかということについて説明したが、何でもかんでもインデックスをつければいいというわけではないということに触れてエントリを締めくくることにする。セカンダリインデックスをつければ、その分確実にページが増えてディスク上に占めるテーブルスペースの容量も増え、さらにはバッファプールを逼迫することになるだろう。それにより、インデックスを追加することでCOUNT()クエリが高速化する代わりに、全体的なパフォーマンスが低下するかも知れないのである。従って、インデックスを追加した場合には、アプリケーション全体の性能を確認するべきなのである。そのアプリケーションにとって、COUNT()が頻繁に実行されるクエリであれば、セカンダリインデックスの追加を検討すると良いだろう。そうでなければ、たまにCOUNT()を実行するときだけ遅くなるのをガマンしたほうが、全体的なパフォーマンスは良好に保たれるのである。おまけ‥トリガの活用リターンズ
先ほど、GROUP BY句を利用する場合にはトリガ＋別テーブルでテーブル全体の件数を保持する方法は使えないという旨のことを述べたが、次のようなテーブルを使って解決することも可能だ。CREATE TABLE t1_count_by_d (
drange date NOT NULL,
nrows int(10) unsigned NOT NULL,
PRIMARY KEY (drange, nrows)
) ENGINE=InnoDB;
トリガの記述は割愛するが、drangeごとに行数を増減させればいい。この方法ならCovering Indexよりも遙かに少ないディスク容量で、高速な件数の取得が可能になる。ただし、トリガは更新時のオーバーヘッドが大きいので、どちらの方が良いかは、ぜひ皆さんの手で検証して頂きたい。
2 コメント:
コメントありがとうございます。
> B-Treeの各ノードに自分の子ノードの数を記憶しておくようなIndexの作り方は存在しないのでしょうか？
これがないんです。
InnoDBはMVCCなので、ある瞬間、ひとつのノードには複数のバージョンが存在するので、その手の実装が難しいんですよ。
コメントを投稿