MyISAMのCOUNT()関数
こんにちは。今回はMyISAMでのCOUNT()関数について書いてみたいと思います。
COUNT()は皆さんご存知のように、テーブル中の行数を数える関数です。
MyISAMではテーブル全件を数えるCOUNT(*)関数の実行が非常に速いというのが巷の噂ですが、実際のところはどうなのでしょうか？ちょっと見てみたいと思います。
まずは比較のため、innodbのテーブルでカウントしてみたいと思います。
少しは件数の入ったテーブルが良いので、MySQLのサンプルとして提供されている
WorldデータベースのCityテーブルを使ってみました。
[InnoDB結果]
全件
mysql> alter table city engine=innodb;
Query OK, 4079 rows affected (0.27 sec)
Records: 4079 Duplicates: 0 Warnings: 0
mysql> flush tables;
Query OK, 0 rows affected (0.00 sec)
mysql> select count(*) from city;
+----------+
| count(*) |
+----------+
| 4079 |
+----------+
1 row in set (0.02 sec)
mysql> show profile;
+--------------------+----------+
| Status | Duration |
+--------------------+----------+
| starting | 0.000106 |
| Opening tables | 0.001007 |
| System lock | 0.000009 |
| Table lock | 0.000016 |
| init | 0.000024 |
| optimizing | 0.000010 |
| statistics | 0.000018 |
| preparing | 0.000016 |
| executing | 0.000012 |
| Sending data | 0.005298 |
| end | 0.000022 |
| query end | 0.000009 |
| freeing items | 0.000336 |
| logging slow query | 0.000009 |
| cleaning up | 0.000009 |
+--------------------+----------+
WHERE句付き
mysql> flush tables;
Query OK, 0 rows affected (0.00 sec)
mysql> select count(*) from city where name = 'tokyo'
+----------+
| count(*) |
+----------+
| 1 |
+----------+
1 row in set (0.00 sec)
mysql> show profile;
+--------------------+----------+
| Status | Duration |
+--------------------+----------+
| starting | 0.000127 |
| Opening tables | 0.001020 |
| System lock | 0.000009 |
| Table lock | 0.000017 |
| init | 0.000052 |
| optimizing | 0.000022 |
| statistics | 0.000021 |
| preparing | 0.000022 |
| executing | 0.000011 |
| Sending data | 0.006663 |
| end | 0.000009 |
| query end | 0.000007 |
| freeing items | 0.000061 |
| logging slow query | 0.000007 |
| cleaning up | 0.000008 |
+--------------------+----------+
続けて、MyISAMテーブルでの結果です。
[MyISAM結果]
全件
mysql> flush tables;
Query OK, 0 rows affected (0.00 sec)
mysql> select count(*) from city;
+----------+
| count(*) |
+----------+
| 4079 |
+----------+
1 row in set (0.00 sec)
mysql> show profile;
+--------------------+----------+
| Status | Duration |
+--------------------+----------+
| starting | 0.000106 |
| Opening tables | 0.002326 |
| System lock | 0.000011 |
| Table lock | 0.000016 |
| init | 0.000023 |
| optimizing | 0.000012 |
| executing | 0.000021 |
| end | 0.000010 |
| query end | 0.000008 |
| freeing items | 0.000046 |
| logging slow query | 0.000007 |
| cleaning up | 0.000007 |
+--------------------+----------+
WHERE句付き
mysql> flush tables;
Query OK, 0 rows affected (0.05 sec)
mysql> select count(*) from city where name = 'tokyo';
+----------+
| count(*) |
+----------+
| 1 |
+----------+
1 row in set (0.02 sec)
mysql> show profile;
+--------------------+----------+
| Status | Duration |
+--------------------+----------+
| starting | 0.000131 |
| Opening tables | 0.002439 |
| System lock | 0.000010 |
| Table lock | 0.000017 |
| init | 0.000051 |
| optimizing | 0.000021 |
| statistics | 0.000022 |
| preparing | 0.000024 |
| executing | 0.000011 |
| Sending data | 0.003237 |
| end | 0.000011 |
| query end | 0.000009 |
| freeing items | 0.000094 |
| logging slow query | 0.000009 |
| cleaning up | 0.000012 |
+--------------------+----------+
まだまだ行数が少なかったようで、体感レスポンスタイムはあまり変わりませんでした。
しかし、プロファイリングしてみると中での動きは全然違うことが分かります。
・結果を出すためのステップがMyISAM-全件ではかなり省略されている
・MyISAM-全件では、特にSending dataのステップがない
以上のことから皆さんも「MyISAMの全件カウントは何か特別なことをしているな～」と感じられるのではないでしょうか。
実はMyISAMでは、テーブル中に現在何件の行があるかの情報をインデックスファイル(MYIファイル）に持っています。そして、MyISAMのテーブルで全件のCOUNT()が来たらMySQLサーバはデータファイルを一切読まず、計算などすることもなくインデックスファイルにある行数を返す仕様になっています。MyISAMテーブルに対するINSERTやDELETEが行われるたびに、この行数カウントが増減するようになっています。なお、InnoDBは構造上このようなフィールドを持つことができないため、件数カウントに時間がかかる仕様となっています(MVCC等のためです。詳しい理由は別の機会に・・・）
もうちょっと細かく見てみましょう。MYIファイルの中身はおおよそ以下のようになっています。
HEADER
ステート情報
ベース情報（主にデータ長定義）
キー定義情報
..
レコード情報
..
KEY
キーセグメント情報(キー長さ、ポインタ情報）
..
さらに、[ステート情報]の部分を細かく見てみると以下のようになっています。
ヘッダ：24バイト(1-24)
オープンカウント : 2バイト(25-26)
ステート変更フラグ：1バイト(27)
ソートキー(不使用）：1バイト(28)
レコード数：8バイト(29-36)
削除レコード数：8バイト(37-43)
<以下略>
MyISAMでは全件のCOUNT()関数が来るとMYIファイルの[現在の行数]の部分からデータを読みこんで結果を返します。ちょっと実験してみましょう。
MYIファイルの件数をみてCOUNT()関数が結果を返すならば、そこを変えるとテーブルデータはそのままでも結果件数は変わるはずです（不整合となりますが・・・）
何もしないと4079行という結果が返ってきます。
mysql> select count(*) from city;
+----------+
| count(*) |
+----------+
| 4079 |
+----------+
1 row in set (0.01 sec)
ここで、MYIファイルをバイナリエディタでちょっといじってみます。編集するのは、city.MYIという名前のファイルです。下のスクリーンショットのちょうどカーソルがあたっている部分が該当する部分となります。現在値は0FEF(4079)なので、これを適当な数字に変更してみます。
[変更後]
150FEFに変更しました。
さて、COUNT(*)の結果はどうなっているでしょうか。
mysql> select count(*) from city;
+----------+
| count(*) |
+----------+
| 1380335 |
+----------+
1 row in set (0.00 sec)
1380335(150FEF)に変更されています。
今回は実験目的で実施しましたが、通常バイナリを直接編集する機会はあまりないと思います。
もしも、今回のようにMYI（インデックス）の内容とMYD(データ）に差異が出てしまった場合は以下のようにして調査、修復することが可能です。
mysql> check table city;
+------------+-------+----------+----------------------------+
| Table | Op | Msg_type | Msg_text |
+------------+-------+----------+----------------------------+
| world.city | check | error | Found 4079 keys of 1380335 |
| world.city | check | error | Corrupt |
+------------+-------+----------+----------------------------+
2 rows in set (0.00 sec)
上記のようにCHECK TABLE文（オンライン用）または、myisamchkユーティリティ(オフライン用）を使ってファイルの状態を検査します。今回はMYIの件数が間違っているので、きちんと1380335ではなく実数は4079であると警告されています。
このように問題を発見した場合、REPAIR TABLE文（オンライン用）もしくはmyisamchkユーティリティ(オフライン用）で修復しましょう。
mysql> repair table city;
+------------+--------+----------+---------------------------------------------+
| Table | Op | Msg_type | Msg_text |
+------------+--------+----------+---------------------------------------------+
| world.city | repair | warning | Number of rows changed from 1380335 to 4079 |
| world.city | repair | status | OK |
+------------+--------+----------+---------------------------------------------+
2 rows in set (0.03 sec)
mysql> select count(*) from city;
+----------+
| count(*) |
+----------+
| 4079 |
+----------+
1 row in set (0.00 sec)
これで、もとの行数に戻りました。MYIファイルが消失してしまった場合でもMYDからREPAIR TABLEコマンドでMYIが復活できますので便利です（消失したときはUSE_FRMオプションをつけてください）。
注：DISTINCTやWHEREを使うとMYIから件数を持って来なくなります（それはそうですね）。