「MySQLのinnodbはcountすると遅い」…という知識だけが先行してしまい、イケてない設計をしてしまった。。。
それは、ソーシャルアプリでよくある「未確認通知数」を算出するために発生した。
↓こーいうの
パッと思いついた設計は以下の3つ。
- 通知テーブルに確認フラグを持っておき、未確認の件数をcountする。
☆メリット ：シンプル。生産性＆保守性UP。
★デメリット：速度が遅い（と思っていた）。 - KVSに未確認件数を保持しておく。
☆メリット ：速度が速い。
★デメリット：KVSはトランザクションが無いので整合性の保障がない。 - 専用のテーブル＆列を作ってカウントアップ・ダウンする。
☆メリット ：取得対象が1レコードなので速度が速い。
★デメリット：同一ユーザへのアクションが多発した場合ロックする可能性あり。
速度重視＆整合性重要の案件だったので、1.は速度的にNG、2.は整合性が保たれないのでNG・・・っていうことで3.を選択してしまった。
これでも仕様は満たせていたわけだけど、運用時に大きな問題が発生する。
時間がかかるバッチ処理で、この専用テーブルを更新する必要が発生したので、通常処理でロック待ちが発生するようになってしまったのです。
そこだけトランザクションから外せばいいといえばよかったけれど、今後も発生しそうな事例ではあるので、いい機会なので「そもそもどれ位遅いのか？」速度を調べてみた。
■テーブルイメージ
テーブルの定義はこんな感じ。
CREATE TABLE `notification` ( `target_user_id` int(10) NOT NULL, `type` varchar(60) COLLATE utf8_bin NOT NULL, `check_flag` tinyint(3) NOT NULL DEFAULT '0', PRIMARY KEY (`target_user_id`,`user_id`,`type`), KEY `test` (`type`,`check_flag`) );
■データ数
テスト投入したデータは200万件。
■全件COUNT
まず、全件COUNTしてみた。
SELECT COUNT(*) AS cnt FROM notification;
+---------+ | cnt | +---------+ | 2000000 | +---------+ 0.494901 sec
0.5秒・・・遅いが、まぁALLなので仕方がない。
■100件COUNT
次に、リアルどころでindexが効いたwhereで100件COUNT。
SELECT COUNT(*) AS cnt FROM notification WHERE target_user_id = 1 AND check_flag = 0;
+-----+ | cnt | +-----+ | 100 | +-----+ 0.000479 sec
0.0005秒・・・
ﾊﾔｲ！？Σ(ﾟдﾟlll)
充分すぎる。。。
ちなみにExplainはこんな感じ。
indexは有効になっている。
+----+-------------+--------------+------+---------------+---------+---------+-------+------+-------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +----+-------------+--------------+------+---------------+---------+---------+-------+------+-------------+ | 1 | SIMPLE | notification | ref | PRIMARY | PRIMARY | 4 | const | 99 | Using where | +----+-------------+--------------+------+---------------+---------+---------+-------+------+-------------+
■20万件COUNT
じゃあ多めに取得してみる。
SELECT COUNT(*) AS cnt FROM notification WHERE type = 'test_1' AND check_flag = 0;
+--------+ | cnt | +--------+ | 244290 | +--------+ 0.086372 sec
+----+-------------+--------------+------+---------------+------+---------+-------------+--------+--------------------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +----+-------------+--------------+------+---------------+------+---------+-------------+--------+--------------------------+ | 1 | SIMPLE | notification | ref | test | test | 183 | const,const | 620774 | Using where; Using index | +----+-------------+--------------+------+---------------+------+---------+-------------+--------+--------------------------+
■つまり・・・
どうやら、innodbが遅いというのは、「MyISAMと比較して遅い」という記憶だった模様。
MyISAMはシステムテーブルにレコード件数を保持しているので速度が速いけど、それはMyISAMが特殊なだけ。
innodbも、決して遅いわけではない。
なので、今回の場合は、仕様で「未読通知100件以上は残らない。削除しちゃう。」とか制限をかければ、countがベストだと分かりました。。。
