業務ではクエリパフォーマンスの最適化とかやらないのですが、知っておいたほうが絶対にいいので調べてみました。
ポイント
- インデックス
- Bツリー構造
SHOW INDEXCREATE INDEXEXPLAIN
インデックスとは
インデックスは特定のカラム値のある行を見つけるために使用されるデータ構造の事です。インデックスが設定されていないとすべての行を読み取って行かなくてはならないため、テーブルの行数が大きくなるほどパフォーマンスが低下してしまいます。
一般的に用いられている例を示すと、インデックスはまるで本の目次のようなものです。特定の章にたどり着くのに本を一枚づつめくっていくのは大変ですよね。データベースもそれは同じで特定の行を発見するのに上から順番になめていったら時間がかかってしまいます。そこで目次のように行を管理する機能が備わっているわけですね。
一般的にMySQLでは以下のような操作の時にインデックスを使用します。
WHERE句に一致する行の検索MIN()またはMAX()値の検索- 結合の実行時に他のテーブルから行を取得するため
注意しなければならないのは、すべてのカラムにインデックスを設定すればよいわけではないということです。目次のたとえ話で考えてみれば当然のことですね（すべてのページを目次に乗せても誰も喜ばないはずです）。MySQLに限った話ではありませんがドキュメントには以下のような記述があります。
クエリーで使用されている可能なすべてのカラムにインデックスを作成しようとしがちですが、不要なインデックスは領域を無駄にし、MySQL が使用するインデックスを判断するための時間を無駄にします。各インデックスを更新する必要があるため、インデックスは挿入、更新、削除のコストも追加します。最適なインデックスのセットを使用して、高速のクエリーを実現するために、適切なバランスを見つける必要があります。
InnoDBのインデックス
標準でインデックスをBツリー構造で持ちます。Bツリーとはツリーデータ構造の一種です。常にソートされ続け、正確な一致または範囲の高速な検索が得意です。ここで詳しい説明は省きますが、以下にWikipediaのリンクを示しておきます。
MySQLでは常に、主キーを表すインデックスを持ちます（主キーインデックスと言う。InnoDB用語ではクラスタ化されたインデックスとも言う）。 SHOW INDEX 文で対象テーブルのインデックス情報を調査できるので実際にやってみました。
MySQL :: MySQL 5.6 リファレンスマニュアル :: 13.7.5.23 SHOW INDEX 構文
mysql> SHOW CREATE TABLE books;
+-------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Table | Create Table |
+-------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| books | CREATE TABLE `books` (
`id` int(11) NOT NULL,
`name` varchar(50) NOT NULL,
`publisher` varchar(50) NOT NULL,
`author` varchar(50) NOT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci |
+-------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)
mysql> SHOW INDEX FROM books;
+-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+------------+
| Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment | Visible | Expression |
+-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+------------+
| books | 0 | PRIMARY | 1 | id | A | 2 | NULL | NULL | | BTREE | | | YES | NULL |
+-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+------------+
1 row in set (0.01 sec)
SHOW INDEX 文の結果で注目したいのは以下のカラムです。
Key_name... インデックスの名前。インデックスが主キーである場合、常にPRIMARYになる。Column_name... カラム名。Index_type... 使用されるインデックス方法。
books テーブルは id カラムが主キーのため、以下のような結果が取得できました。
Key_name... PRIMARYColumn_name... idIndex_type... BTREE
主キー以外のカラムへのインデックス
インデックスは主キー以外のカラムにも設定することができます。Bツリーデータ構造によって、 WHERE 句内の比較を高速化でき、素早く対象行のルックアップができるようになります。
主キー以外のカラムにインデックスを設定するにはいくつか方法がありますが、既存のテーブルに設定するには CREATE INDEX 文が使えます。例えば、以下のように books テーブルに追加でインデックスを設定できます。
mysql> SHOW INDEX FROM books;
+-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+------------+
| Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment | Visible | Expression |
+-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+------------+
| books | 0 | PRIMARY | 1 | id | A | 2 | NULL | NULL | | BTREE | | | YES | NULL |
+-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+------------+
1 row in set (0.00 sec)
mysql> CREATE INDEX books_name ON books (name);
Query OK, 0 rows affected (0.05 sec)
Records: 0 Duplicates: 0 Warnings: 0
mysql> SHOW INDEX FROM books;
+-------+------------+------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+------------+
| Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment | Visible | Expression |
+-------+------------+------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+------------+
| books | 0 | PRIMARY | 1 | id | A | 2 | NULL | NULL | | BTREE | | | YES | NULL |
| books | 1 | books_name | 1 | name | A | 2 | NULL | NULL | | BTREE | | | YES | NULL |
+-------+------------+------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+------------+
2 rows in set (0.01 sec)
SHOW INDEX の結果行が増えており、インデックスが追加で設定できたことがわかりますね。
実行計画からインデックスが使われているか確認
実行計画と言われる、MySQLがステートメントを実行する際の情報を元に、インデックスが使用されているかを確認することができます。
MySQL :: MySQL 5.6 リファレンスマニュアル :: 8.8.1 EXPLAIN によるクエリーの最適化
実行計画を取得するには EXPLAIN 文を使います。例えば、books テーブルで id を元に検索した時の実行計画は以下のようになります。
mysql> SHOW CREATE TABLE books;
+-------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Table | Create Table |
+-------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| books | CREATE TABLE `books` (
`id` int(11) NOT NULL,
`name` varchar(50) NOT NULL,
`publisher` varchar(50) NOT NULL,
`author` varchar(50) NOT NULL,
PRIMARY KEY (`id`),
KEY `books_name` (`name`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci |
+-------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)
mysql> EXPLAIN SELECT * FROM books WHERE id = 1;
+----+-------------+-------+------------+-------+---------------+---------+---------+-------+------+----------+-------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+-------+------------+-------+---------------+---------+---------+-------+------+----------+-------+
| 1 | SIMPLE | books | NULL | const | PRIMARY | PRIMARY | 4 | const | 1 | 100.00 | NULL |
+----+-------------+-------+------------+-------+---------------+---------+---------+-------+------+----------+-------+
1 row in set, 1 warning (0.00 sec)
EXPLAIN 文はその後ろに対象のステートメントを続けて書きます。各項目の詳細な説明は以下のリンク先のドキュメントに詳しく書かれています。
MySQL :: MySQL 5.6 リファレンスマニュアル :: 8.8.2 EXPLAIN 出力フォーマット
特に着目したい項目は以下です。
possible_keys... 選択可能なインデックスはPRIMARYだけになっている。key... 実際に選択されたインデックスはPRIMARYになっており、主キーインデックスが使用される事がわかる。rows... 調査される行の見積もりは１行。これはidによって行を一意に特定できるからだと思う。
上記の例では id を検索しましたが、 name を検索すると実行計画の内容が以下のように変化します。
mysql> EXPLAIN SELECT * FROM books WHERE name = 'Test Driven Development';
+----+-------------+-------+------------+------+---------------+------------+---------+-------+------+----------+-------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+-------+------------+------+---------------+------------+---------+-------+------+----------+-------+
| 1 | SIMPLE | books | NULL | ref | books_name | books_name | 202 | const | 1 | 100.00 | NULL |
+----+-------------+-------+------------+------+---------------+------------+---------+-------+------+----------+-------+
1 row in set, 1 warning (0.01 sec)
先程と同じ項目に着目してみましょう。
possible_keys... 選択可能なインデックスがbooks_nameになりました。key... 実際に選択されたインデックスはbooks_nameになりました。rows... 調査される行の見積もりは変わりませんでした。nameカラムがインデックスになっているので各行を一意に特定できるからだと思います。
