はじめに
本記事は、サムザップ Advent Calendar 2020 #2 の12/19の記事です。
この記事では、MySQLのindexの基本的な事を書きます。
さらっと流して読んで頂ければ。
環境
mysql> select version();
+-----------+
| version() |
+-----------+
| 5.7.31 |
+-----------+
1 row in set (0.48 sec)
５．７
テーブル設計
サンプルテーブル
CREATE TABLE `hoge_table` (
`id` int(10) unsigned NOT NULL AUTO_INCREMENT,
`user_id` int(10) unsigned NOT NULL COMMENT 'ユーザID',
`name` varchar(255) NOT NULL COMMENT '名前',
`type` tinyint(2) unsigned NOT NULL DEFAULT '0' COMMENT 'タイプ',
`level` smallint(4) unsigned NOT NULL DEFAULT '0' COMMENT 'レベル',
`sort` mediumint(8) unsigned NOT NULL DEFAULT '0' COMMENT '順番',
`created_at` timestamp NULL DEFAULT NULL,
`updated_at` timestamp NULL DEFAULT NULL,
`deleted_at` timestamp NULL DEFAULT NULL,
PRIMARY KEY (`id`),
KEY `unq_1` (`user_id`),
KEY `idx_1` (`sort`,`level`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci COMMENT='ユーザ情報';
INDEXの基本的な動き
これついては、セカンダリインデックスを含めて書きます。
まず、知っていると思いますが、indexを貼ればselect時にKEYから参照して検索をする為、早くデータが返ってきます。
しかし、逆にinsert時にもKEYを貼ってから登録を行うので、若干insertも重くなります。
なので、indexの貼り過ぎには注意しましょう。
まずexplainの見方
ざっくり言うと、
type
possible_keys
key
Extra
だけ見ましょう！
（上記以外は、どんなクエリだよって教えてくれているだけです）
（rowsもって思う人もいるかもですが、ざっくとした見積もりの数値なので参考程度に見るくらいで良いです）
| type | 「ALL」「index」になっていないか |
| possible_keys | ちゃんと表示されているか |
| key | index名がちゃんと使用されているか |
| Extra | 「Using filesort」が表示されていないか |
順番
indexは貼った順番どおりに機能します。
例えば
KEY `idx_1` (`type`,`level`),
このindexにlevelだけ投げても
mysql> explain select * from hoge_table where level=20;
+----+-------------+------------+------------+------+---------------+------+---------+------+------+----------+-------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+------------+------------+------+---------------+------+---------+------+------+----------+-------------+
| 1 | SIMPLE | hoge_table | NULL | ALL | NULL | NULL | NULL | NULL | 7973 | 10.00 | Using where |
+----+-------------+------------+------------+------+---------------+------+---------+------+------+----------+-------------+
1 row in set, 1 warning (0.07 sec)
もちろん機能しません。
なので、ちゃんと「type」カラムを指定してあげれば機能します。
mysql> explain select * from hoge_table where type=1 and level=20 ;
+----+-------------+------------+------------+------+---------------+-------+---------+-------------+------+----------+-------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+------------+------------+------+---------------+-------+---------+-------------+------+----------+-------+
| 1 | SIMPLE | hoge_table | NULL | ref | idx_1 | idx_1 | 3 | const,const | 13 | 100.00 | NULL |
+----+-------------+------------+------------+------+---------------+-------+---------+-------------+------+----------+-------+
1 row in set, 1 warning (0.08 sec)
ちなみに、「type」カラムのみでも機能します。
mysql> explain select * from hoge_table where type=1;
+----+-------------+------------+------------+------+---------------+-------+---------+-------+------+----------+-------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+------------+------------+------+---------------+-------+---------+-------+------+----------+-------+
| 1 | SIMPLE | hoge_table | NULL | ref | idx_1 | idx_1 | 1 | const | 1764 | 100.00 | NULL |
+----+-------------+------------+------------+------+---------------+-------+---------+-------+------+----------+-------+
1 row in set, 1 warning (0.19 sec)
また、順番を逆にしても、MySQLが勝手に判断してくれます。
mysql> explain select * from hoge_table where level=20 and type=1 ;
+----+-------------+------------+------------+------+---------------+-------+---------+-------------+------+----------+-------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+------------+------------+------+---------------+-------+---------+-------------+------+----------+-------+
| 1 | SIMPLE | hoge_table | NULL | ref | idx_1 | idx_1 | 3 | const,const | 13 | 100.00 | NULL |
+----+-------------+------------+------------+------+---------------+-------+---------+-------------+------+----------+-------+
1 row in set, 1 warning (0.09 sec)
時々以下のような、無駄にindexを貼っている悪い例を見ることがありますが
KEY `idx_1` (`type`,`level`),
KEY `idx_2` (`type`,`level`,`sort`)
これなら１つにまとめてしまって良いですね。
KEY `idx_1` (`type`,`level`,`sort`)
INDEXの「ORDER BY」
indexには「order by」にも機能します。
mysql> explain select * from hoge_table where type = 10 order by level;
+----+-------------+------------+------------+------+---------------+-------+---------+-------+------+----------+-----------------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+------------+------------+------+---------------+-------+---------+-------+------+----------+-----------------------+
| 1 | SIMPLE | hoge_table | NULL | ref | idx_1 | idx_1 | 1 | const | 1 | 100.00 | Using index condition |
+----+-------------+------------+------------+------+---------------+-------+---------+-------+------+----------+-----------------------+
1 row in set, 1 warning (0.09 sec)
indexの順番で、orderはwhere句の後になるので、今回であれば
type→levelの順番ですね。
orderは複数カラムでもwehre句と同じ様に機能します。
KEY `idx_2` (`type`,`level`,`sort`)
mysql> explain select * from hoge_table where type = 10 order by level, sort;
+----+-------------+------------+------------+------+---------------+-------+---------+-------+------+----------+-----------------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+------------+------------+------+---------------+-------+---------+-------+------+----------+-----------------------+
| 1 | SIMPLE | hoge_table | NULL | ref | idx_1,idx_2 | idx_2 | 1 | const | 1 | 100.00 | Using index condition |
+----+-------------+------------+------------+------+---------------+-------+---------+-------+------+----------+-----------------------+
1 row in set, 1 warning (0.09 sec)
ただし、acsとdescを混ぜると機能しなくなるので注意。
mysql> explain select * from hoge_table where type = 10 order by level desc, sort asc;
+----+-------------+------------+------------+------+---------------+-------+---------+-------+------+----------+---------------------------------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+------------+------------+------+---------------+-------+---------+-------+------+----------+---------------------------------------+
| 1 | SIMPLE | hoge_table | NULL | ref | idx_1,idx_2 | idx_1 | 1 | const | 1 | 100.00 | Using index condition; Using filesort |
+----+-------------+------------+------------+------+---------------+-------+---------+-------+------+----------+---------------------------------------+
1 row in set, 1 warning (0.07 sec)
またwhere句と違って、orderは順番をユーザ側で指定する為、順番を変えると機能しなくなります。
mysql> explain select * from hoge_table where type = 10 order by sort, level;
+----+-------------+------------+------------+------+---------------+-------+---------+-------+------+----------+---------------------------------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+------------+------------+------+---------------+-------+---------+-------+------+----------+---------------------------------------+
| 1 | SIMPLE | hoge_table | NULL | ref | idx_1,idx_2 | idx_1 | 1 | const | 1 | 100.00 | Using index condition; Using filesort |
+----+-------------+------------+------------+------+---------------+-------+---------+-------+------+----------+---------------------------------------+
1 row in set, 1 warning (0.08 sec)
データの割合について
（わかりやすい様にidx_2は削除しました）
現在このテーブルはtypeが5種類あります。
mysql> select type, count(1) from hoge_table group by type;
+------+----------+
| type | count(1) |
+------+----------+
| 1 | 1764 |
| 2 | 1691 |
| 3 | 1690 |
| 4 | 1730 |
| 5 | 1833 |
+------+----------+
もちろん以下の場合はindexは機能します。
mysql> explain select * from hoge_table where type =1;
+----+-------------+------------+------------+------+---------------+-------+---------+-------+------+----------+-------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+------------+------------+------+---------------+-------+---------+-------+------+----------+-------+
| 1 | SIMPLE | hoge_table | NULL | ref | idx_1 | idx_1 | 1 | const | 1764 | 100.00 | NULL |
+----+-------------+------------+------------+------+---------------+-------+---------+-------+------+----------+-------+
1 row in set, 1 warning (0.01 sec)
しかし、割合を変えると以下の様に、フルスキャンしてしまい、indexが機能しなくなります。
mysql> select type, count(1) from hoge_table group by type;
+------+----------+
| type | count(1) |
+------+----------+
| 1 | 8692 |
| 5 | 16 |
+------+----------+
mysql> explain select * from hoge_table where type =1;
+----+-------------+------------+------------+------+---------------+------+---------+------+------+----------+-------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+------------+------------+------+---------------+------+---------+------+------+----------+-------------+
| 1 | SIMPLE | hoge_table | NULL | ALL | idx_1 | NULL | NULL | NULL | 8835 | 98.38 | Using where |
+----+-------------+------------+------------+------+---------------+------+---------+------+------+----------+-------------+
1 row in set, 1 warning (0.01 sec)
なので、データの割合などをしっかり確認しましょう。
よくある例としては、
論理削除フラグなど持たせてindexを貼ったのだけど
実際論理削除をほぼ全くしなくて、上記の様にindexが動いていなかった、、、
なんて事は聞いた事があります。
INDEXが効かないから気をつける事
・LIKE文を使う
mysql> explain select * from hoge_table_1 where type like 1;
+----+-------------+--------------+------------+------+---------------+------+---------+------+------+----------+-------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+--------------+------------+------+---------------+------+---------+------+------+----------+-------------+
| 1 | SIMPLE | hoge_table_1 | NULL | ALL | idx_1 | NULL | NULL | NULL | 8835 | 11.11 | Using where |
+----+-------------+--------------+------------+------+---------------+------+---------+------+------+----------+-------------+
1 row in set, 1 warning (0.19 sec)
・ORを使う
mysql> explain select * from hoge_table_1 where type = 5 or level=30;
+----+-------------+--------------+------------+------+---------------+------+---------+------+------+----------+-------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+--------------+------------+------+---------------+------+---------+------+------+----------+-------------+
| 1 | SIMPLE | hoge_table_1 | NULL | ALL | idx_1 | NULL | NULL | NULL | 8835 | 28.00 | Using where |
+----+-------------+--------------+------------+------+---------------+------+---------+------+------+----------+-------------+
1 row in set, 1 warning (0.12 sec)
後は、「CASE文」や「サブクエリ」などで、効かなくなることが多いので、気をつけてください。
余談
MySQLWorkbenchを使用すると、indexの順序などが分かりやすく表示されているのでオススメです。
URL
https://www.mysql.com/jp/products/workbench
VISUAL EXPLAINを使う
indexが機能していると
機能していないと
となります.
JOIN等も分かりやすく出力されるので、是非活用してみてください。
というわけで、忘れがちなINDEXの基本的な動きを説明させていただきました。
皆様の今後の参考になれば幸いです。
Will Smith