DBの索引について実験してみた

はじめに

大量のデータを使用してサービスを稼働させる場合、データベースの処理速度は重要である。
データベースで検索を高速化する方法に、索引 がある。
この技術が 検索・並び替え にどのくらい影響があるのかを不思議に思い、実際に計測してみた。

計測における前提条件

• データベース
  MySQL 5.7.13
• テーブル
  計測用にテーブルMETを作成した。
  以下は、SHOW CREATE TABLE METを使用した結果である。
  .sql | met | CREATE TABLE `met` ( `id` int(11) NOT NULL AUTO_INCREMENT, `content` varchar(8) NOT NULL, `point` int(11) NOT NULL, `score` int(11) NOT NULL, `flag` tinyint(1) NOT NULL DEFAULT '1', PRIMARY KEY (`id`) ) ENGINE=InnoDB AUTO_INCREMENT=1000001 DEFAULT CHARSET=utf8 |
• データ件数
  データは以下のような内容で 1,000,000 件作成した。

COLUMN	中身
id	主キー(AUTO INCREAMENT)
content	{3桁のランダム値}-{4桁のランダム値}
point	[0, 100]のランダムな値
score	[1, 10]のランダムな値
flag	1(TRUE) OR 0(FALSE)

• その他注意点
  • 実行時間を載せていますが、これは実行環境により異なると思います。どのくらい処理効率が向上したかの目安として実行時間はご覧ください。
  • 実行時間は処理時間を取得したいため、表示時間に影響されないよう、表示件数は常に1件にします。

索引指定なしでやってみる

普通に表示してみる

mysql> SELECT SQL_CALC_FOUND_ROWS * FROM `MET` LIMIT 1;
+----+----------+-------+-------+------+
| id | content  | point | score | flag |
+----+----------+-------+-------+------+
|  1 | 863-2696 |    26 |    10 |    1 |
+----+----------+-------+-------+------+
1 row in set (0.32 sec)

pointで並び替えしてみる

mysql> SELECT SQL_CALC_FOUND_ROWS * FROM `MET` ORDER BY `point` LIMIT 1;
+-----+----------+-------+-------+------+
| id  | content  | point | score | flag |
+-----+----------+-------+-------+------+
| 231 | 299-3838 |     0 |     2 |    1 |
+-----+----------+-------+-------+------+
1 row in set (0.40 sec)

scoreで並び替えしてみる

mysql> SELECT SQL_CALC_FOUND_ROWS * FROM `MET` ORDER BY `score` LIMIT 1;
+----+----------+-------+-------+------+
| id | content  | point | score | flag |
+----+----------+-------+-------+------+
| 12 | 757-8394 |    43 |     1 |    1 |
+----+----------+-------+-------+------+
1 row in set (0.39 sec)

point、scoreで並び替え

mysql> SELECT SQL_CALC_FOUND_ROWS * FROM `MET` ORDER BY `point`, `score` LIMIT 1;
+-----+----------+-------+-------+------+
| id  | content  | point | score | flag |
+-----+----------+-------+-------+------+
| 486 | 286-7282 |     0 |     1 |    1 |
+-----+----------+-------+-------+------+
1 row in set (0.42 sec)

contentを指定してみる

mysql> SELECT SQL_CALC_FOUND_ROWS * FROM `MET` WHERE `content`='279-4397' LIMIT 1;
+--------+----------+-------+-------+------+
| id     | content  | point | score | flag |
+--------+----------+-------+-------+------+
| 245926 | 279-4397 |     3 |     1 |    1 |
+--------+----------+-------+-------+------+
1 row in set (0.36 sec)

contentを指定 ＆ scoreで並び替え

mysql> SELECT SQL_CALC_FOUND_ROWS * FROM `MET` WHERE `content`='279-4397' ORDER BY `score` LIMIT 1;
+--------+----------+-------+-------+------+
| id     | content  | point | score | flag |
+--------+----------+-------+-------+------+
| 245926 | 279-4397 |     3 |     1 |    1 |
+--------+----------+-------+-------+------+
1 row in set (0.41 sec)

idを指定 ＆ pointで並び替え

mysql> SELECT SQL_CALC_FOUND_ROWS * FROM `MET` WHERE `id`=50497 ORDER BY `point` LIMIT 1;
+-------+----------+-------+-------+------+
| id    | content  | point | score | flag |
+-------+----------+-------+-------+------+
| 50497 | 814-4629 |   100 |     1 |    1 |
+-------+----------+-------+-------+------+
1 row in set (0.00 sec)

pointを指定 ＆ idで並び替え

mysql> SELECT SQL_CALC_FOUND_ROWS * FROM `MET` WHERE `point`=50 ORDER BY `id` DESC LIMIT 1;
+--------+----------+-------+-------+------+
| id     | content  | point | score | flag |
+--------+----------+-------+-------+------+
| 999948 | 033-5013 |    50 |     5 |    1 |
+--------+----------+-------+-------+------+
1 row in set (0.61 sec)

pointを指定 ＆ scoreで並び替え

mysql> SELECT SQL_CALC_FOUND_ROWS * FROM `MET` WHERE `point`=50 ORDER BY `score` LIMIT 1;
+-----+----------+-------+-------+------+
| id  | content  | point | score | flag |
+-----+----------+-------+-------+------+
| 148 | 114-4135 |    50 |     1 |    1 |
+-----+----------+-------+-------+------+
1 row in set (0.38 sec)

scoreを指定 ＆ pointで並び替え

mysql> SELECT SQL_CALC_FOUND_ROWS * FROM `MET` WHERE `score`=5 ORDER BY `point` LIMIT 1;
+-----+----------+-------+-------+------+
| id  | content  | point | score | flag |
+-----+----------+-------+-------+------+
| 305 | 979-7876 |     0 |     5 |    1 |
+-----+----------+-------+-------+------+
1 row in set (0.58 sec)

処理時間が確かに増えてるけど、大きな違いがないんよなー
ってことで、次は索引を登録してみる。

索引（パターン1）

pointカラムに索引を登録する。

mysql> ALTER TABLE `MET` ADD INDEX (`point`);


pointで並び替えてみる。

mysql> SELECT SQL_CALC_FOUND_ROWS * FROM `MET` ORDER BY `point` LIMIT 1;
+-----+----------+-------+-------+------+
| id  | content  | point | score | flag |
+-----+----------+-------+-------+------+
| 231 | 299-3838 |     0 |     2 |    1 |
+-----+----------+-------+-------+------+
1 row in set (0.40 sec)

pointを指定

mysql> SELECT SQL_CALC_FOUND_ROWS * FROM `MET` WHERE `point`=50 LIMIT 1;
+----+----------+-------+-------+------+
| id | content  | point | score | flag |
+----+----------+-------+-------+------+
| 68 | 326-1535 |    50 |     6 |    1 |
+----+----------+-------+-------+------+
1 row in set (0.15 sec)

pointを指定 ＆ idで並び替え

mysql> SELECT SQL_CALC_FOUND_ROWS * FROM `MET` WHERE `point`=50 ORDER BY `id` DESC LIMIT 1;
+--------+----------+-------+-------+------+
| id     | content  | point | score | flag |
+--------+----------+-------+-------+------+
| 999948 | 033-5013 |    50 |     5 |    1 |
+--------+----------+-------+-------+------+
1 row in set (0.31 sec)

pointを指定 ＆ scoreで並び替え

mysql> SELECT SQL_CALC_FOUND_ROWS * FROM `MET` WHERE `point`=50 ORDER BY `score` LIMIT 1;
+-----+----------+-------+-------+------+
| id  | content  | point | score | flag |
+-----+----------+-------+-------+------+
| 148 | 114-4135 |    50 |     1 |    1 |
+-----+----------+-------+-------+------+
1 row in set (0.16 sec)

idを指定 ＆ pointで並び替え

mysql> SELECT SQL_CALC_FOUND_ROWS * FROM `MET` WHERE `id`=50497 ORDER BY `point` LIMIT 1;
+-------+----------+-------+-------+------+
| id    | content  | point | score | flag |
+-------+----------+-------+-------+------+
| 50497 | 814-4629 |   100 |     1 |    1 |
+-------+----------+-------+-------+------+
1 row in set (0.00 sec)

scoreを指定 ＆ pointで並び替え

mysql> SELECT SQL_CALC_FOUND_ROWS * FROM `MET` WHERE `score`=5 ORDER BY `point` LIMIT 1;
+-----+----------+-------+-------+------+
| id  | content  | point | score | flag |
+-----+----------+-------+-------+------+
| 305 | 979-7876 |     0 |     5 |    1 |
+-----+----------+-------+-------+------+
1 row in set (0.39 sec)

point、scoreを指定

mysql> SELECT SQL_CALC_FOUND_ROWS * FROM `MET` WHERE `point`=50 AND `score`=5 LIMIT 1;
+-----+----------+-------+-------+------+
| id  | content  | point | score | flag |
+-----+----------+-------+-------+------+
| 509 | 430-7482 |    50 |     5 |    1 |
+-----+----------+-------+-------+------+
1 row in set (0.15 sec)
mysql> SELECT SQL_CALC_FOUND_ROWS * FROM `MET` WHERE `score`=5 AND `point`=50 LIMIT 1;
+-----+----------+-------+-------+------+
| id  | content  | point | score | flag |
+-----+----------+-------+-------+------+
| 509 | 430-7482 |    50 |     5 |    1 |
+-----+----------+-------+-------+------+
1 row in set (0.24 sec)

結果、検索の効率が上がる 。 並び替えも速くなる 。
検索 > 並び替え の順序で効率が上がる。

索引（パターン2）

(point, score)カラムのペアに索引を登録する。

mysql> ALTER TABLE `MET` ADD INDEX ind_pare (`point`, `score`);


pointを指定 ＆ scoreで並び替え

mysql> SELECT SQL_CALC_FOUND_ROWS * FROM `MET` WHERE `point`=50 ORDER BY `score` LIMIT 1;
+-----+----------+-------+-------+------+
| id  | content  | point | score | flag |
+-----+----------+-------+-------+------+
| 148 | 114-4135 |    50 |     1 |    1 |
+-----+----------+-------+-------+------+
1 row in set (1.29 sec)

scoreを指定 ＆ pointで並び替え

mysql> SELECT SQL_CALC_FOUND_ROWS * FROM `MET` WHERE `score`=5 ORDER BY `point` LIMIT 1;
+-----+----------+-------+-------+------+
| id  | content  | point | score | flag |
+-----+----------+-------+-------+------+
| 305 | 979-7876 |     0 |     5 |    1 |
+-----+----------+-------+-------+------+
1 row in set (0.39 sec)

point、scoreで並び替え

mysql> SELECT SQL_CALC_FOUND_ROWS * FROM `MET` ORDER BY `point`, `score` LIMIT 1;
+-----+----------+-------+-------+------+
| id  | content  | point | score | flag |
+-----+----------+-------+-------+------+
| 486 | 286-7282 |     0 |     1 |    1 |
+-----+----------+-------+-------+------+
1 row in set (0.42 sec)

point(逆順)、scoreで並び替え

mysql> SELECT SQL_CALC_FOUND_ROWS * FROM `MET` ORDER BY `point` DESC, `score` LIMIT 1;
+------+----------+-------+-------+------+
| id   | content  | point | score | flag |
+------+----------+-------+-------+------+
| 1595 | 143-6543 |   100 |     1 |    1 |
+------+----------+-------+-------+------+
1 row in set (0.41 sec)

score、pointで並び替え

mysql> SELECT SQL_CALC_FOUND_ROWS * FROM `MET` ORDER BY `score`, `point` LIMIT 1;
+-----+----------+-------+-------+------+
| id  | content  | point | score | flag |
+-----+----------+-------+-------+------+
| 486 | 286-7282 |     0 |     1 |    1 |
+-----+----------+-------+-------+------+
1 row in set (0.42 sec)

point、scoreを指定

mysql> SELECT SQL_CALC_FOUND_ROWS * FROM `MET` WHERE `point`=50 AND `score`=5 LIMIT 1;
mysql> SELECT SQL_CALC_FOUND_ROWS * FROM `MET` WHERE `score`=5 AND `point`=50 LIMIT 1;
+-----+----------+-------+-------+------+
| id  | content  | point | score | flag |
+-----+----------+-------+-------+------+
| 509 | 430-7482 |    50 |     5 |    1 |
+-----+----------+-------+-------+------+
1 row in set (0.13 sec)

pointを指定 ＆ idで並び替え

mysql> SELECT SQL_CALC_FOUND_ROWS * FROM `MET` WHERE `point`=50 ORDER BY `id` DESC LIMIT 1;
+--------+----------+-------+-------+------+
| id     | content  | point | score | flag |
+--------+----------+-------+-------+------+
| 999948 | 033-5013 |    50 |     5 |    1 |
+--------+----------+-------+-------+------+
1 row in set (0.16 sec)

point、score、contentで並び替え

mysql> SELECT SQL_CALC_FOUND_ROWS * FROM `MET` ORDER BY `point`, `score`, `content` LIMIT 1;
+--------+----------+-------+-------+------+
| id     | content  | point | score | flag |
+--------+----------+-------+-------+------+
| 242711 | 000-0562 |     0 |     1 |    1 |
+--------+----------+-------+-------+------+
1 row in set (0.48 sec)

point、scoreを指定 ＆ contentで並び替え

mysql> SELECT SQL_CALC_FOUND_ROWS * FROM `MET` WHERE `point`=50 AND `score`=5 ORDER BY `content` LIMIT 1;
+--------+----------+-------+-------+------+
| id     | content  | point | score | flag |
+--------+----------+-------+-------+------+
| 348102 | 001-8174 |    50 |     5 |    1 |
+--------+----------+-------+-------+------+
1 row in set (0.16 sec)

複数索引に追加すると、並び替えに対して特に速くならない。
検索に対しては、一つを索引に追加しているときと同じぐらい速くなる。
また、指定の順序に対しては速度はほぼ変わらない。

結論

索引は 検索をよくするカラム に対して追加するのが良い！
並び替えは速くなってはいそうだが、殆ど無視しても良さそう。
どのようにデータを絞り込むかを考えながら、索引の登録や、DB設計をしていくことが必要です！