Posted inMySQL

MySQLのCOUNT(*)が遅い？データが数百万件に達したときにダッシュボードをフリーズさせない方法

MySQL tutorial - IT technology blog
MySQL tutorial - IT technology blog

背景：COUNT(*)という名の落とし穴

プロジェクトに参加したばかりの頃、私は典型的な「落とし穴」に嵌まったことがあります。タスクは、Eコマースサイトのダッシュボードに注文合計数を表示することでした。私は自信満々に SELECT COUNT(*) FROM orders と入力し、そのまま本番環境にデプロイしました。結果はどうだったでしょうか？orders テーブルが1,000万行に達するまではすべて順順調でした。しかし、Eコマース向けMySQLスキーマ設計が不十分なままデータが増大すると、ある時からダッシュボードの読み込みが止まらなくなり、最終的にシステムは真っ白な画面でタイムアウトエラーを吐き出しました。

問題の核心は、InnoDBがMyISAMのようにメタデータ内に総行数を保持していない点にあります。MyISAMはあらかじめ保存されている数値を読み取るだけなので、結果を返すのに0.00秒しかかかりません。対照的に、InnoDBは一貫性（MVCC – Multi-Version Concurrency Control）を保証するために、データのスキャンを実行する必要があります。データベースは、その特定の時点において、あなたのトランザクションに対して実際に何行存在するかを数え上げる必要があるのです。

大規模なデータテーブルでページネーション（pagination）機能を実装する場合、COUNT(*) の最適化は死活問題です. ユーザーがページを更新するたびに、データベースサーバーに過度なI/O負荷を強いるような事態は避けなければなりません。

シミュレーション：500万行がデータベースを苦しめる時

違いを明確にするために、シンプルなログテーブルを作成してみましょう。パフォーマンスを検証するために、約500万件のダミーデータを投入します。

-- テストテーブルの作成
CREATE TABLE logs_test (
    id INT AUTO_INCREMENT PRIMARY KEY,
    user_id INT,
    action VARCHAR(255),
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
) ENGINE=InnoDB;

-- 500万行を挿入後、カウントクエリを実行してみる:
SELECT COUNT(*) FROM logs_test;

中スペックのサーバー（2 vCPU, 4GB RAM）では、このクエリに3〜8秒かかることがあります。EXPLAIN を使用すると、MySQLが index scan を実行していることがわかります。これは非常にリソースを消費する操作です。

EXPLAIN SELECT COUNT(*) FROM logs_test;

レコード件数を「即座に」取得するための4つの戦略

深夜2時にサーバーがフリーズするトラブルを何度も経験した結果、具体的なケースに応じた4つのアプローチにたどり着きました。

1. セカンダリインデックス（Secondary Index）の活用

MySQLは COUNT(*) を処理する際、比較的賢い動きをします。Primary Key（通常はサイズが大きい）を使用する代わりに、最小サイズのインデックスを選択してスキャンしようとします。テーブルに id 列しかない場合は、TINYINT や INT 型の列にインデックスを追加してみてください。

-- MySQLがより高速にスキャンできるように小さなインデックスを追加
ALTER TABLE logs_test ADD INDEX idx_user_id (user_id);

この方法により、速度が約2〜3倍向上します。しかし、計算量は依然として O(N) です。数億行のテーブルでは、これだけでは根本的な解決にはなりません。

2. メタデータの利用（誤差を許容する場合）

実際には、ユーザーが常に1単位まで正確な数値を必要としているわけではありません。「約120万件の結果があります」と表示するだけで十分な場合は、information_schema に問い合わせましょう。

SELECT TABLE_ROWS 
FROM information_schema.tables 
WHERE table_name = 'logs_test' 
AND table_schema = 'your_db_name';

メリット: 結果がほぼ0msで返ってきます。
デメリット: 誤差が10〜20%に達する可能性があります。この数値は、InnoDBのオプティマイザ（Optimizer）による推計値に過ぎません。

3. カウンターテーブル手法（専用のカウント用テーブル）

これは、100%の正確性と高速性の両方が求められるシステムにおける「黄金の解決策」です。重要なテーブルの総行数のみを記録する専用のテーブルを作成します。

CREATE TABLE table_counts (
    table_name VARCHAR(100) PRIMARY KEY,
    total_rows BIGINT DEFAULT 0
);

トリガー（Triggers）を使用して、この数値を自動的に更新します。INSERTのたびに1を足し、DELETEのたびに1を引きます。これにより、総行数の取得はPrimary Keyによる単純な SELECT クエリになり、ミリ秒単位の速度が実現します。

4. Redisを使用したカウント（高頻度の書き込み負荷がある場合）

システムが1秒間に数千件の書き込みを受ける場合、トリガーはボトルネック（ロック競合）を引き起こす可能性があります。その場合は、負荷を Redis に逃がしましょう。Redisの INCR や DECR といったアトミックなコマンドを使用してカウンターを管理します。その後、5分おきなどの定期的な間隔で、この数値をバックアップとしてデータベースに同期します。

おわりに

データ量が増大していく中で、COUNT(*) を盲信してはいけません。常に Slow Query Log を定期的にチェックしてください。カウントクエリがログに出現し始めたら、それはインデックス戦略を変更するか、カウンターテーブルに移行すべきだという警告信号です。深夜の緊急呼び出しを避けるために、設計段階から適切なソリューションを選択しましょう。

Share:
Tags: