インデックス

August 05, 2018

インデックスとは

インデックスとは、検索で特定の列の行をすばやく見つけるために使われる仕組み。
インデックスを設定せずに MySQL で検索を実行した場合、先頭から順に 1 レコードずつすべての情報を読んでいく必要がある。
レコード数や列数が少ない場合はそれでもどうにかなるが、多くなればなるほど処理速度が遅くなる。

このような検索遅延を避けるためにインデックスを設定する。インデックスは検索しそうな列にインデックス用の数値を紐づけることで生成される。
主キーや外部キーとして列には自動的にインデックスが作成されるが、社名や都道府県など、別の任意の列にインデックスを作成することもでき、また 1 つだけでなく、 2 つの列をセットでインデックスを作ることもできるため、検索でよく使いそうなものはインデックスを設定しておくと良い。

ただし、インデックスを設定すると、それだけディスク容量を消費する。また、データを更新する際はインデックスも更新する必要があるため、更新の速度は若干低下する。
そのため、比較対象としてよく使用する列にはインデックスを設定した方が良いが、あまり比較に使わない列にはインデックスを設定するメリットはない。

インデックスについては以下のブログが参考になる。

インデックスの種類

インデックスには以下の 4 種類がある。

インデックスの種類	働き
INDEX	通常のインデックス
UNIQUE	重複を許さないインデックス
SPATIAL	空間を示す例に設定するインデックス
FULLTEXT	全文検索に設定するインデックス

多くの場合 INDEX を用いるが、重複を許さない列の場合は UNIQUE を設定すると良い。また、テキスト型でそれを全文検索するには FULLTEXT を設定する。
SPATIAL は使う機会がほとんどない。

インデックスを作成する

今回は以下のテーブル City を用いる。

mysql> SELECT * FROM City LIMIT 10;  
+----+----------------+-------------+---------------+------------+  
| ID | Name           | CountryCode | District      | Population |  
+----+----------------+-------------+---------------+------------+  
|  1 | Kabul          | AFG         | Kabol         |    1780000 |  
|  2 | Qandahar       | AFG         | Qandahar      |     237500 |  
|  3 | Herat          | AFG         | Herat         |     186800 |  
|  4 | Mazar-e-Sharif | AFG         | Balkh         |     127800 |  
|  5 | Amsterdam      | NLD         | Noord-Holland |     731200 |  
|  6 | Rotterdam      | NLD         | Zuid-Holland  |     593321 |  
|  7 | Haag           | NLD         | Zuid-Holland  |     440900 |  
|  8 | Utrecht        | NLD         | Utrecht       |     234323 |  
|  9 | Eindhoven      | NLD         | Noord-Brabant |     201843 |  
| 10 | Tilburg        | NLD         | Noord-Brabant |     193238 |  
+----+----------------+-------------+---------------+------------+  
10 rows in set (0.00 sec)  
  
mysql> SELECT COUNT(*) FROM City;  
+----------+  
| COUNT(*) |  
+----------+  
|     4079 |  
+----------+  
1 row in set (0.00 sec)

すでに使用されているインデックスを確認する。

mysql> SHOW INDEX FROM City;  
+-------+------------+-------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+  
| Table | Non_unique | Key_name    | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment |  
+-------+------------+-------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+  
| City  |          0 | PRIMARY     |            1 | ID          | A         |        4046 |     NULL | NULL   |      | BTREE      |         |               |  
| City  |          1 | CountryCode |            1 | CountryCode | A         |         505 |     NULL | NULL   |      | BTREE      |         |               |  
+-------+------------+-------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+  
2 rows in set (0.00 sec)

ここで、すでに ID 及び CountryCode にインデックスが作成されている。
これは、以下からも確認できるように、ID は PRIMARY Key であり、CountryCode は外部キーであるためである。

mysql> SHOW CREATE TABLE City;  
+-------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+  
| Table | Create Table                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                       |  
+-------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+  
| City  | CREATE TABLE `City` ( 
  `ID` int(11) NOT NULL AUTO_INCREMENT,  
  `Name` char(35) COLLATE utf8_unicode_ci NOT NULL DEFAULT '',  
  `CountryCode` char(3) COLLATE utf8_unicode_ci NOT NULL DEFAULT '',  
  `District` char(20) COLLATE utf8_unicode_ci NOT NULL DEFAULT '',  
  `Population` int(11) NOT NULL DEFAULT '0',  
  PRIMARY KEY (`ID`),  
  KEY `CountryCode` (`CountryCode`),  
  CONSTRAINT `city_ibfk_1` FOREIGN KEY (`CountryCode`) REFERENCES `Country` (`Code`)  
) ENGINE=InnoDB AUTO_INCREMENT=4080 DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci |  
+-------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+  
1 row in set (0.00 sec)

実際に Population に対して Index を作成するには以下のようにすれば良い。

mysql> ALTER TABLE City ADD INDEX (Population);  
Query OK, 0 rows affected (0.10 sec)  
Records: 0  Duplicates: 0  Warnings: 0

インデックスが使われているかどうかを確認する

EXPLAIN つきの SQL を実行する。ここでは Population が 118245 であるレコードを参照してみる。

mysql> EXPLAIN SELECT * FROM City WHERE Population = 118245;  
+----+-------------+-------+------+---------------+------------+---------+-------+------+-------+  
| id | select_type | table | type | possible_keys | key        | key_len | ref   | rows | Extra |  
+----+-------------+-------+------+---------------+------------+---------+-------+------+-------+  
|  1 | SIMPLE      | City  | ref  | Population    | Population | 4       | const |    1 | NULL  |  
+----+-------------+-------+------+---------------+------------+---------+-------+------+-------+  
1 row in set (0.00 sec)

上記のそれぞれの意味は以下の通り。

項目	意味
select_type	クエリの種類。SIMPLE はテーブルを順に処理するクエリを指す
table	対象のテーブル名
type	レコードを参照する方法のことであり、ref は = 演算子を使って、値が等しいかどうかをマッチする処理を示す
possible_keys	この実行において利用可能なインデックス。ここでは現在 Population が利用可能なインデックスとして定義されていることを示す
key	実際に使用されたインデックス。ここでは Population が使用されたことを示す
key_len	インデックスのキーの長さ
ref	比較対象の種類を示すもの。ここでは 118245 という固定された数値と比較しているため、定数という意味の const が設定されている
rows	この実行によって得られるおおよそのレコード数
Extra	この実行計画を遂行するのにどのような処理が必要なのかを示すヒント

大きいサイズのテーブルでインデックスを貼ってみる

以下のようなテストテーブルを作成し、このテーブルの SELECT 結果より巨大なテーブルを作成してみる。

mysql> CREATE TABLE Testdata(str VARCHAR(10));  
Query OK, 0 rows affected (0.04 sec)  
  
mysql> SELECT * FROM Testdata;  
+------+  
| str  |  
+------+  
| A    |  
| B    |  
| C    |  
| D    |  
| E    |  
| F    |  
| G    |  
| H    |  
| I    |  
|      |  
+------+  
10 rows in set (0.01 sec)

具体的には以下のような SELECT 文をサブクエリとした INSERT 文を実行することで、大きなテーブルを作ってみる。

mysql> SELECT CONCAT(t1.str, t2.str, t3.str, t4.str, t5.str) AS String, FLOOR(RAND() * (100000000 - 1) + 1) AS Num1, FLOOR(RAND() * (100000000 - 1) + 1) AS Num2, FLOOR(RAND() * (100000000 - 1) + 1) AS Num3 FROM Testdata t1, Testdata t2, Testdata t3, Testdata t4, Testdata t5 LIMIT 10;  
+--------+----------+----------+----------+  
| String | Num1     | Num2     | Num3     |  
+--------+----------+----------+----------+  
| AAAAA  | 50780672 | 29634984 | 95832907 |  
| BAAAA  | 90259588 | 63799225 | 48217361 |  
| CAAAA  | 49689131 |  3793579 | 69900502 |  
| DAAAA  | 38121780 | 80907394 | 90171634 |  
| EAAAA  |  8135992 | 70165058 | 26417319 |  
| FAAAA  | 21591423 | 28705161 | 78751539 |  
| GAAAA  |  7642216 |  1956466 | 86855684 |  
| HAAAA  | 28409028 | 81478090 | 22163370 |  
| IAAAA  | 66382580 | 65422795 | 27966239 |  
| AAAA   | 43562809 | 33915334 | 38888247 |  
+--------+----------+----------+----------+  
10 rows in set (0.01 sec)  
  
mysql> CREATE TABLE HugeTable (String VARCHAR(20), Num1 INT, Num2 INT, Num3 INT);  
Query OK, 0 rows affected (0.03 sec)  
  
mysql> INSERT INTO HugeTable (String, Num1, Num2, Num3) SELECT CONCAT(t1.str, t2.str, t3.str, t4.str, t5.str) AS String, FLOOR(RAND() * (100000000 - 1) + 1) AS Num1, FLOOR(RAND() * (100000000 - 1) + 1) AS Num2, FLOOR(RAND() * (100000000 - 1) + 1) AS Num3 FROM Testdata t1, Testdata t2, Testdata t3, Testdata t4, Testdata t5;  
Query OK, 100000 rows affected (1.28 sec)  
Records: 100000  Duplicates: 0  Warnings: 0

実際にできたテーブルが以下。

mysql> SELECT COUNT(*) FROM HugeTable;  
+----------+  
| COUNT(*) |  
+----------+  
|   100000 |  
+----------+  
1 row in set (0.00 sec)  
  
mysql> SELECT * FROM HugeTable LIMIT 10;  
+--------+----------+----------+----------+  
| String | Num1     | Num2     | Num3     |  
+--------+----------+----------+----------+  
| AAAAA  | 34940789 | 54364524 | 67000258 |  
| BAAAA  | 71907722 | 58537838 | 76966026 |  
| CAAAA  |  9216621 | 15185028 | 48275280 |  
| DAAAA  | 95821321 | 34280768 | 83939877 |  
| EAAAA  | 16857090 | 32465816 | 11757815 |  
| FAAAA  | 61391627 | 71684695 | 74248599 |  
| GAAAA  | 56188912 | 58198765 | 22427092 |  
| HAAAA  | 37539168 | 20414568 | 89455336 |  
| IAAAA  | 86032984 | 61798916 | 50895630 |  
| AAAA   | 69081404 | 92720135 | 56356469 |  
+--------+----------+----------+----------+  
10 rows in set (0.03 sec)

インデックスを貼る前に実行したものが以下。

mysql> SELECT * FROM HugeTable WHERE Num2 = 91787196 AND Num3 = 58584415;  
+--------+----------+----------+----------+  
| String | Num1     | Num2     | Num3     |  
+--------+----------+----------+----------+  
| AAAAH  | 66783857 | 91787196 | 58584415 |  
+--------+----------+----------+----------+  
1 row in set (0.08 sec)

実際にインデックスを作成し、似たようなクエリを実行すると以下のようになる。

mysql> ALTER TABLE HugeTable ADD INDEX (Num2, Num3);  
Query OK, 0 rows affected (0.49 sec)  
Records: 0  Duplicates: 0  Warnings: 0  
  
mysql> SELECT * FROM HugeTable WHERE Num2 = 85682204 AND Num3 = 23385500;  
+--------+---------+----------+----------+  
| String | Num1    | Num2     | Num3     |  
+--------+---------+----------+----------+  
| CAAAH  | 1675175 | 85682204 | 23385500 |  
+--------+---------+----------+----------+  
1 row in set (0.01 sec)

速くなってる。