MySQL(MariaDB) - InnoDB レコード件数取得高速化！

MySQL や MariaDB で件数を取得する際に COUNT(*) をよく使用すると思いますが、件数が多くなると取得にも時間がかかるようになります。

以下、高速化する方法についての備忘録です。（乱筆・乱文ご容赦ください）

0. 前提条件

• MySQL 5.6.16 での作業を想定。（最近のバージョンなら同様。MariaDB も同様）
• 使用するテーブル用のデータベースが作成済みであることを想定。
• COUNT(*), COUNT(1) は全行取得、 COUNT(col_name) は NULL 以外取得であることを理解しておく。

1. 未対策状態

まず、高速化未対策の状態で検証してみる。

1-1. テーブル準備

例として、元のテーブルを以下のように作成する。（id カラムにだけプライマリキーを設定）

File: 元のテーブル

CREATE TABLE table_a (
  id    int(11)    NOT NULL AUTO_INCREMENT,
  val_1 int(11)    DEFAULT '0',
  val_2 int(11)    DEFAULT '0',
  val_3 varchar(4) DEFAULT '',
  val_4 varchar(2) DEFAULT '',
  PRIMARY KEY (id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8

テーブルを作成したら、データを適当に作成する。（当方は別の株価データを 1,500 万件超 INSERT した）

1-2. 状態確認

そして、COUNT(*) で件数を取得、 EXPLAIN してみる。

mysql> SELECT COUNT(*) FROM table_a;
+----------+
| COUNT(*) |
+----------+
| 15625865 |
+----------+
1 row in set (3.39 sec)

mysql> EXPLAIN SELECT COUNT(*) FROM table_a;
+----+-------------+---------+-------+---------------+---------+---------+------+----------+-------------+
| id | select_type | table   | type  | possible_keys | key     | key_len | ref  | rows     | Extra       |
+----+-------------+---------+-------+---------------+---------+---------+------+----------+-------------+
|  1 | SIMPLE      | table_a | index | NULL          | PRIMARY | 4       | NULL | 14408357 | Using index |
+----+-------------+---------+-------+---------------+---------+---------+------+----------+-------------+
1 row in set (0.03 sec)

1-3. 結果

1,500 万件超の件数を取得するのに 3.39 秒かかり、プライマリキーで行を参照しているのが分かる。

MySQL(MariaDB) の InnoDB ストレージエンジンでは、プライマリキーはクラスタインデックスとなっている。プライマリキーを取得した時点でデータも同時に取得しているということであり、データ取得時は高速であっても、件数のみを取得したい場合には不要な情報まで取得してしまうということである。

2. 高速化対策（その１・結果的には高速化されていないケース）

クラスタインデックスのプライマリキー以外に、セカンダリインデックスを用意して、そのインデックスで COUNT を取得する方法を試してみる。

2-1. インデックス追加

既存の int 型のカラム１つにインデックスを設定してみる。（件数が多いので処理に若干時間はかかる）

mysql> ALTER TABLE table_a
    -> ADD INDEX idx_1 (val_1);
Query OK, 0 rows affected (2 min 15.49 sec)
Records: 0  Duplicates: 0  Warnings: 0

2-2. 状態確認

そして、COUNT(*) で件数を取得、 EXPLAIN してみる。

mysql> SELECT COUNT(*) FROM table_a;
+----------+
| COUNT(*) |
+----------+
| 15625865 |
+----------+
1 row in set (3.39 sec)

mysql> EXPLAIN SELECT COUNT(*) FROM table_a;
+----+-------------+---------+-------+---------------+-------+---------+------+----------+-------------+
| id | select_type | table   | type  | possible_keys | key   | key_len | ref  | rows     | Extra       |
+----+-------------+---------+-------+---------------+-------+---------+------+----------+-------------+
|  1 | SIMPLE      | table_a | index | NULL          | idx_1 | 5       | NULL | 14408357 | Using index |
+----+-------------+---------+-------+---------------+-------+---------+------+----------+-------------+
1 row in set (0.02 sec)

ちなみに、今回の場合 COUNT(*) を COUNT(val_1) に置き換えるとやや遅くなるような結果となった。（それは、全行取得か NULL 以外取得かの違いからであろう）

2-3. 結果

オプティマイザ（最適なプランを選択してくれる機能）によって追加したセカンダリインデックスが選択されるが、件数取得にかかった時間はそれほど変わらなかった。

プライマリキーと同じ int 型のためだろう。（同じ int 型でもクラスタインデックスと非クラスタインデックスで結果がそれほど変わらないのも若干不思議であるが）

3. 高速化対策（その２・高速化できたケース）

既存のカラムにセカンダリインデックスを設定するのでなく、別途 tinyint 型のカラムを追加してセカンダリインデックスを追加してみる。
既存の int 型のプライマリキーやセカンダリインデックスよりも小さいサイズのカラムが必要なだけなので、tinyint 型のカラムが存在するのならそのカラムにインデックスを設定してみてもよい。（オプティマイザにカラムを選択させるための処置）

3-1. カラム追加

インデックス設定用のカラムを追加する。件数取得目的でしか使用しないので、初期値は 0 にしている。（件数が多いので処理に若干時間はかかる）

mysql> ALTER TABLE table_a
    -> ADD COLUMN idx TINYINT NULL DEFAULT 0
    -> AFTER id;
Query OK, 0 rows affected (9 min 53.93 sec)
Records: 0  Duplicates: 0  Warnings: 0

3-2. インデックス追加

追加した tynyint 型のカラムにインデックスを設定してみる。（件数が多いので処理に若干時間はかかる）

mysql> ALTER TABLE table_a
    -> ADD INDEX idx_2 (idx);
Query OK, 0 rows affected (1 min 23.83 sec)
Records: 0  Duplicates: 0  Warnings: 0

3-3. 状態確認

そして、COUNT(*) で件数を取得、 EXPLAIN してみる。

mysql> SELECT COUNT(*) FROM table_a;
+----------+
| COUNT(*) |
+----------+
| 15625865 |
+----------+
1 row in set (3.06 sec)

mysql> EXPLAIN SELECT COUNT(*) FROM table_a;
+----+-------------+---------+-------+---------------+-------+---------+------+----------+-------------+
| id | select_type | table   | type  | possible_keys | key   | key_len | ref  | rows     | Extra       |
+----+-------------+---------+-------+---------------+-------+---------+------+----------+-------------+
|  1 | SIMPLE      | table_a | index | NULL          | idx_2 | 2       | NULL | 15193970 | Using index |
+----+-------------+---------+-------+---------------+-------+---------+------+----------+-------------+
1 row in set (0.00 sec)

3-4. 結果

今回の環境に関して言えば、若干ではあるが高速化されたと言えるであろう。
おそらく、カラム数や情報量がより多ければ効果はあるかも知れない。。。

4. その他の方法

4-1. 関数 SQL_CALC_FOUND_ROWS の使用

行取得と同時に全件数も取得可能な関数 SQL_CALC_FOUND_ROWS, FOUND_ROWS() を使用してみる方法。

LIMIT 句を使用しても全件数を取得可能なので、ページング処理を行いたい場合等には便利ではあるが、行数が増えると当然のように遅くなる。

SELECT SQL_CALC_FOUND_ROWS * FROM table_a;

SELECT FOUND_ROWS();

4-2. トリガで件数用別テーブルの作成

件数カウント用に別テーブルを作成しておき、対象テーブルにデータが INSERT されればトリガで件数をインクリメント(UPDATE)、対象テーブルからデータが DELETE されればトリガでデクリメント(UPDATE)する方法。

処理的にも簡素な処理でもあり、かなり高速化することは可能。
ただし、条件指定して件数を取得することが容易ではないため、当方の条件に見合わない。

5. まとめ

他にも考えられる方法もあるかも知れないが、今回の中では 3 のセカンダリインデックスをうまく利用して件数を取得する方法が最適ではないだろうか。

---

時と場合にもよるでしょうが、高速化のために色々と考えられる方法は存在する（考え出せる）ということです。

以上。