count(*)からinnodbにおけるindex構成を確認する

* 概要

今回はinnodbにおけるcountの高速化について検証する。

きっかけは下記のブログですが。いつもお世話になっております。

http://nippondanji.blogspot.jp/2010/03/innodbcount.html

要約すると下記のようなスキーム雨がある時

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;

下記のようなsqlを想定する

SELECT count(*) FROM t1;

このとき例えばtinyintなどにindexを貼ることで、count(*)の高速化が見込める。

innodbのcount(*)において全レコードへのアクセスが必要になることは変わりないが
これは主キー(bigint)を全走査することよりも、小さいindexを全走査するほうが効率が良いということである。

まあ頭のなかでは理解できて、予想はできているんだけどちゃんと自分の手でピコピコやりたいなというところで下記を確認する。
1. 検索速度がa,b,cで変わることを確認（参照テーブルも）
2. e,f,g,h,iがあるときとないときで検索速度がそこまで変わらないことの確認（クラスタインデックスのノードが影響を与えないこと）

* 検証

検証2のリーフノードの大きさの検証のため下記データベースを用意する。

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;

CREATE TABLE t2 (
  a bigint(20) unsigned NOT NULL AUTO_INCREMENT,  
  b int(11) DEFAULT NULL,  
  c tinyint(4) DEFAULT NULL,  
  PRIMARY KEY (a)  
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

下記みたいな感じでデータを作成する

<?php

$pdo = new PDO('mysql:host=localhost;dbname=test;charset=utf8','root','');
$stmt = $pdo->prepare("INSERT INTO t1 (b,c,d,e,f,g,h,i) VALUES (:b,:c,:d,:e,:f,:g,:h,:i)");

$bind = array();
$bind['b'] = $i;
$bind['c'] = 1;
$bind['d'] = '2014-12-25';
$bind['e'] = '1234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890';
$bind['f'] = '1234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890';
$bind['g'] = '1234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890';
$bind['h'] = '1234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890';
$bind['i'] = '1234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890';

foreach($bind as $key => $value){
 $stmt->bindValue($key, $value);
}

for($i=1; $i<=10000000; $i++) {
 $stmt->execute();
}

// 値同じだけ今回はcountなので影響なし。

* sqlのクエリキャッシュを無効にする

mysql> show variables like 'query_%';
+------------------------------+---------+
| Variable_name                | Value   |
+------------------------------+---------+
| query_alloc_block_size       | 8192    |
| query_cache_limit            | 1048576 |
| query_cache_min_res_unit     | 4096    |
| query_cache_size             | 0       |
| query_cache_type             | OFF     |
| query_cache_wlock_invalidate | OFF     |
| query_prealloc_size          | 8192    |
+------------------------------+---------+
7 rows in set (0.00 sec)

* 検索する

まずは2.について。リーフノードのデカさは検索性能に影響するのか。
予想ではしないと思う。B木のキーノードだけ走査するわけだから。

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 | 9140175 | Using index |
+----+-------------+-------+-------+---------------+---------+---------+------+---------+-------------+
1 row in set (0.00 sec)

mysql> desc t1;
+-------+---------------------+------+-----+---------+----------------+
| Field | Type                | Null | Key | Default | Extra          |
+-------+---------------------+------+-----+---------+----------------+
| a     | bigint(20) unsigned | NO   | PRI | NULL    | auto_increment |
| b     | int(11)             | YES  |     | NULL    |                |
| c     | tinyint(4)          | YES  |     | NULL    |                |
| d     | date                | YES  |     | NULL    |                |
| e     | varchar(200)        | YES  |     | NULL    |                |
| f     | varchar(200)        | YES  |     | NULL    |                |
| g     | varchar(200)        | YES  |     | NULL    |                |
| h     | varchar(200)        | YES  |     | NULL    |                |
| i     | varchar(200)        | YES  |     | NULL    |                |
+-------+---------------------+------+-----+---------+----------------+
9 rows in set (0.00 sec)

mysql> select count(*) from t1;
+----------+
| count(*) |
+----------+
| 10000000 |
+----------+
1 row in set (15.87 sec)

mysql> explain select count(*) from t2;
+----+-------------+-------+-------+---------------+---------+---------+------+---------+-------------+
| id | select_type | table | type  | possible_keys | key     | key_len | ref  | rows    | Extra       |
+----+-------------+-------+-------+---------------+---------+---------+------+---------+-------------+
|  1 | SIMPLE      | t2    | index | NULL          | PRIMARY | 8       | NULL | 9223787 | Using index |
+----+-------------+-------+-------+---------------+---------+---------+------+---------+-------------+
1 row in set (0.00 sec)

mysql> desc t2;
+-------+---------------------+------+-----+---------+----------------+
| Field | Type                | Null | Key | Default | Extra          |
+-------+---------------------+------+-----+---------+----------------+
| a     | bigint(20) unsigned | NO   | PRI | NULL    | auto_increment |
| b     | int(11)             | YES  |     | NULL    |                |
| c     | tinyint(4)          | YES  |     | NULL    |                |
+-------+---------------------+------+-----+---------+----------------+
3 rows in set (0.00 sec)

mysql> select count(*) from t2;
+----------+
| count(*) |
+----------+
| 10000000 |
+----------+
1 row in set (1.98 sec)

！驚愕である。リーフノードの大きさが検索性能に大きく依存しているではないか。
これについてはもっと深追いして理解する必要がありそうだ。

* b (int)にindexを追加

mysql> ALTER TABLE t1 ADD INDEX idx_int(b);
Query OK, 0 rows affected (33.17 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> explain select count(*) from t1;
+----+-------------+-------+-------+---------------+---------+---------+------+---------+-------------+
| id | select_type | table | type  | possible_keys | key     | key_len | ref  | rows    | Extra       |
+----+-------------+-------+-------+---------------+---------+---------+------+---------+-------------+
|  1 | SIMPLE      | t1    | index | NULL          | idx_int | 5       | NULL | 9140175 | Using index |
+----+-------------+-------+-------+---------------+---------+---------+------+---------+-------------+
1 row in set (0.00 sec)

mysql> select count(*) from t1;
+----------+
| count(*) |
+----------+
| 10000000 |
+----------+
1 row in set (1.92 sec)

疾いっ！

* つづいてc (tinyint)にindexを追加

mysql> ALTER TABLE t1 ADD INDEX idx_tinyint(c);

Query OK, 0 rows affected (34.54 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> explain select count(*) from t1;
+----+-------------+-------+-------+---------------+-------------+---------+------+---------+-------------+
| id | select_type | table | type  | possible_keys | key         | key_len | ref  | rows    | Extra       |
+----+-------------+-------+-------+---------------+-------------+---------+------+---------+-------------+
|  1 | SIMPLE      | t1    | index | NULL          | idx_tinyint | 2       | NULL | 9140175 | Using index |
+----+-------------+-------+-------+---------------+-------------+---------+------+---------+-------------+
1 row in set (0.00 sec)

mysql> select count(*) from t1;
+----------+
| count(*) |
+----------+
| 10000000 |
+----------+
1 row in set (1.78 sec)

あんまりintと変わらない。これも参考元にある通り。
だが内部的には読み込みページとかの量が半減しているはずである。

さて予想外の挙動を見せたクラスタインデックスの違いはなんだろうか。

ここでt1のvarcharで表されるカラムに対してもゴミを投入していたことに着目する具体的には下記のような感じで全レコードに対して同じようなデータを投入している
検証のために下記のようなテーブルt6を作成した

CREATE TABLE `t6` (
  `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,
  PRIMARY KEY (`a`)
) ENGINE=InnoDB AUTO_INCREMENT=20000001 DEFAULT CHARSET=utf8;

これに対してデータを1000万件投入する。その時のデータと速度は下記のようになっている

mysql> select * from t6 limit 1;
+----------+-----------+------+------------+------------------------------------------------------------------------------------------------------+------------------------------------------------------------------------------------------------------+------+
| a        | b         | c    | d          | e                                                                                                    | f                                                                                                    | g    |
+----------+-----------+------+------------+------------------------------------------------------------------------------------------------------+------------------------------------------------------------------------------------------------------+------+
| 10000001 | 100000000 |    1 | 2014-12-25 | 1234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890 | 1234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890 |      |
+----------+-----------+------+------------+------------------------------------------------------------------------------------------------------+------------------------------------------------------------------------------------------------------+------+
1 row in set (0.00 sec)

mysql> select count(*) from t6;
+----------+
| count(*) |
+----------+
| 10000000 |
+----------+
1 row in set (10.42 sec)

リーフノードのデータ量が検索速度に影響を及ぼす可能性を検証するためにカラムgを空文字にupdateして検証する。

mysql> update t6 set g = '';
Query OK, 10000000 rows affected (1 min 55.63 sec)
Rows matched: 10000000  Changed: 10000000  Warnings: 0

mysql> alter table t6 engine innodb;
Query OK, 10000000 rows affected (59.86 sec)
Records: 10000000  Duplicates: 0  Warnings: 0

mysql> select count(*) from t6;
+----------+
| count(*) |
+----------+
| 10000000 |
+----------+
1 row in set (6.58 sec)

なんと！高速化されたではないか。
正直今までcount(*)するときにindexのキーだけなめて検索しているのかと思っていた。（これはcount(id)でも計測時間が変わらなかったことからの推測でもある）
しかしこの結果から導かれるのは、count(*)したときにリーフノードのデータを読み取っているということにほかならないのではないかと。

B木インデックスってのはB木のキーと値を全部読み取るような振る舞いをしている。と仮定できる。

count(*)からinnodbにおけるindex構成を確認する

シェアする

コメントを残すコメントをキャンセル

サイト内を検索

最近の投稿

最近のコメント

アーカイブ

カテゴリー

シェアする

コメントを残す コメントをキャンセル

RECOMMENDこちらの記事も人気です。

PHPのメモリ節約と参照渡しについて

xhprofを利用する

単調配列array_diffの最適化について

GASでスプレッドシートのセルの書式を文字列に設定する

パーマリンク設計をやり直した

str_replace, preg_replaceのパフォーマンス検証と呼び…