【MySQL】単一インデックスを設定してみる

概要

インデックスの設定によってどれくらい実行速度が違うのか調査してみました。(初心者向けです)
MySQLでダミーデータを10万件ほど流し込んで、速度調査を行いました。

調査

調査は下記の手順で行なっていきます。
① 事前準備
② インデックスの設定
③ 実行計画・結果

①事前準備

sampleテーブルを準備して予め10万件程度のレコードを流し込んでいます。

CREATE TABLE `sample` (
  `id` int NOT NULL AUTO_INCREMENT,
  `name` varchar(64) NOT NULL,
  `point` int NOT NULL,
  `type` smallint NOT NULL,
  `flag` tinyint(1) NOT NULL,
  `created_at` datetime DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8mb3;

sampleテーブルにはランダムでnameカラムやpointカラムにデータを投入しています。

インデックス設定前の速度を確認

WHERE句で絞り込みを行い、sampleテーブルからポイントが99点のデータのみを抽出します。

select * from sample WHERE point = 99;

こちらを実行して、10回の平均を取ったところ19.2msでした。これがインデックスを設定する前の速度で基準となります。

②インデックスを設定

インデックスを設定するコマンドは下記になります。

CREATE INDEX インデックス名 ON テーブル名(カラム名1, カラム名2, ...);

これを使ってindex_pointというキー名でpointカラムのインデックスを設定します。

CREATE INDEX index_point ON sample(point);

を実行しました。

DBのクライアントツールからindex_pointが設定されていること確認できました。

③ 実行計画・結果

次にEXPLAINを使用して、実行計画を確認します。

mysql> EXPLAIN select * from sample WHERE point = 99;
+----+-------------+--------+------------+------+---------------+-------------+---------+-------+------+----------+-------+
| id | select_type | table  | partitions | type | possible_keys | key         | key_len | ref   | rows | filtered | Extra |
+----+-------------+--------+------------+------+---------------+-------------+---------+-------+------+----------+-------+
|  1 | SIMPLE      | sample | NULL       | ref  | index_point   | index_point | 4       | const | 1001 |   100.00 | NULL  |
+----+-------------+--------+------------+------+---------------+-------------+---------+-------+------+----------+-------+
1 row in set, 1 warning (0.01 sec)

• keyカラムは、使用されているインデックス
• rowsカラムは、読み込んだレコードの数

EXPLAINを用いた実行計画から、
1,001件のレコードを読み込んで、index_pointのインデックスをキーとして検索を実行しようとしていることが分かります。

ちなみに、インデックスを設定する前は下記のようになっています。

mysql> EXPLAIN select * from sample WHERE point = 99;
+----+-------------+--------+------------+------+---------------+------+---------+------+--------+----------+-------------+
| id | select_type | table  | partitions | type | possible_keys | key  | key_len | ref  | rows   | filtered | Extra       |
+----+-------------+--------+------------+------+---------------+------+---------+------+--------+----------+-------------+
|  1 | SIMPLE      | sample | NULL       | ALL  | NULL          | NULL | NULL    | NULL | 100010 |    10.00 | Using where |
+----+-------------+--------+------------+------+---------------+------+---------+------+--------+----------+-------------+
1 row in set, 1 warning (0.00 sec)

インデックスを設定をしていないと、100,010件のレコードを全て検索してしまっています。(事前に投入したデータは100,000件ピッタリの予定でしたが越えてました..)

結果

インデックスを設定して、10回クエリを実行して平均を取ったところ0.24msになりました。インデックスを設定しない時は19.2msだったことから、80倍の速度で実行できたことが分かります。

◆インデックスを設定する際に考えた方が良いこと

• 主キーやユニーク制約のあるカラムはインデックスを作る必要がない。主キーやユニークインデックスによる検索の方が早い。
• インデックスは絞り込みだけに使用されるのではなく、データの並び替えにも使用される。絞り込みの条件で使わなくてもソートするカラムにはインデックスを設定した方が良い。

インデックスってすごい！

↓ちなみに設定しているインデックスを削除する方法。

ALTER TABLE テーブル名 DROP INDEX インデックス名;