MySQL クエリのパフォーマンス改善テクニック -その１- 単純な index を付与してみる
clown の note にようこそ。
はじめに
今回やること
MySQL のクエリパフォーマンス改善のひとつとして、単純な index を生成して、クエリを実行してみます。
手順
explain を実行して現状確認
index を生成する
explain を実行して現状確認
■参考コマンド
CREATE INDEX xxxx ON xxxx.xxxx (`xxxx`);対象者
RSDB を使うシステムに関わってて、ユーザから重いって言われてる人
DB 担当になったけど、何から手を付けていいかわかんない人
MySQL を使っている人（他 DB でも基本はそんなに違わないです）
ざっくり説明
今日も DBMS と格闘してますか？
いまどきのシステムで DB を使わないものはなかなか見当たらないでしょう。
クエリなんて究極は動けばいいんですけど、あるページに表示するためのデータを取得するのに数時間、なんなら数日かかるようでは、システムとしては遅すぎてだいぶ終わってる感があります。
さすがにクエリの実行結果を得るまでに数日かかるまま本番リリースされることは"ほぼ"無いですけど、ユーザは少しでも速いシステムを使いたいものです。
っていうか、少しでも遅いと感じるとクレームがきます。
じゃあ、クエリパフォーマンスを改善すればいいじゃない。
というのが今回の話。
前段となる「MySQL で explain してますか？」の記事を読んでいただくと、より味わいが深くなります。
この記事で explain を実行して、クエリがテーブルをフルスキャンをしていることがわかりました。
わかったからもう勝ったようなもんです。
でも、テーブルをフルスキャンしているなら、フルスキャンにならないようにすればいいじゃない。という感じで、せっかくなのでもう一歩進んでパフォーマンスを改善してみましょう。
そういや例のアレ。マリーアントワネットは言ってないらしいです。（真偽不明）
まず準備
テーブル
こんなテーブルを用意します。
「explain してますか？」で作ったテーブルです。
CREATE TABLE `sumple_users` (
`id` int(11) NOT NULL AUTO_INCREMENT
,`username` varchar(64) NOT NULL DEFAULT ''
,`email` varchar(255) NOT NULL
,`password` varchar(255) NOT NULL
,`login_hash` varchar(255) NOT NULL DEFAULT ''
,`created_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
,`updated_at` timestamp NOT NULL DEFAULT '0000-00-00 00:00:00'
,PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=0 DEFAULT CHARSET=utf8;このテーブルには、こんなデータを入れました。
mysql> select * from `sumple_users`;
+-------+------------+----------------------+----------+------------+---------------------+---------------------+
| id | username | email | password | login_hash | created_at | updated_at |
+-------+------------+----------------------+----------+------------+---------------------+---------------------+
| 1 | clown1 | clown1@email.com | abc | def | 2023-08-13 00:00:00 | 0000-00-00 00:00:00 |
| 2 | clown2 | clown2@email.com | abc | def | 2023-08-13 00:00:00 | 0000-00-00 00:00:00 |
| 3 | clown3 | clown3@email.com | abc | def | 2023-08-13 00:00:00 | 0000-00-00 00:00:00 |
| 4 | clown4 | clown4@email.com | abc | def | 2023-08-13 00:00:00 | 0000-00-00 00:00:00 |
| 5 | clown5 | clown5@email.com | abc | def | 2023-08-13 00:00:00 | 0000-00-00 00:00:00 |
～
中略
～
| 9995 | clown9995 | clown9995@email.com | abc | def | 2023-08-13 00:00:00 | 0000-00-00 00:00:00 |
| 9996 | clown9996 | clown9996@email.com | abc | def | 2023-08-13 00:00:00 | 0000-00-00 00:00:00 |
| 9997 | clown9997 | clown9997@email.com | abc | def | 2023-08-13 00:00:00 | 0000-00-00 00:00:00 |
| 9998 | clown9998 | clown9998@email.com | abc | def | 2023-08-13 00:00:00 | 0000-00-00 00:00:00 |
| 9999 | clown9999 | clown9999@email.com | abc | def | 2023-08-13 00:00:00 | 0000-00-00 00:00:00 |
| 10000 | clown10000 | clown10000@email.com | abc | def | 2023-08-13 00:00:00 | 0000-00-00 00:00:00 |
+-------+------------+----------------------+----------+------------+---------------------+---------------------+
10000 rows in set (0.01 sec)explain
EXPLAIN SELECT * FROM `sumple_users` WHERE `username` = 'clown1';こんな感じ（↓）になります。
+----+-------------+--------------+------------+------+---------------+------+---------+------+------+----------+-------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+--------------+------------+------+---------------+------+---------+------+------+----------+-------------+
| 1 | SIMPLE | sumple_users | NULL | ALL | NULL | NULL | NULL | NULL | 1 | 100.00 | Using where |
+----+-------------+--------------+------------+------+---------------+------+---------+------+------+----------+-------------+
1 row in set, 1 warning (0.01 sec)type = ALL
となっていて、フルスキャンしていることがわかります。
これをフルスキャンしないでも目当てのレコードが探せるようにしましょう。
index を貼る
フルスキャンしなくても目当てのレコードを取得できるようにするために、index を作ります。
index とは何か？
簡単に言ってしまうと、対象のレコードを効率良く手に入れるための索引。この index を作ることを、index を貼るとも言います。
index についてのアレコレは複数の記事を書けてしまうので、細かい説明はここでは割愛します。いずれ記事にします。
いまは、この状況を改善するには index を作る必要があるんだ。という事実を認識できれば OK です。
どうやって貼るの？
最初に index を作る…と言いました。
そう。こちらから明示的に作ってあげないと MySQL は何を手掛かりに検索すると効率がよくなるのかわからないんです。
わからないって言ってるなら、教えてあげればいいんです。
作成なので create 系を投入すれば良さそう…というのは察しがついたでしょう。
早速投入してみましょう。
と、はやる気持ちを抑えて、何かするときは、必ず前後確認をするクセをつけましょう。
やったのかやってないのかわからない = エビデンスが無い
エビデンスなんてただの飾りです。エラい人にはそれがわからんのです。
と言いたいところですけど、それだとエラい人に報告できません。責任の所在もわかりません。
誰が何の作業をしたのかもわからないのは、何かあったときに本当に困ります。
ということで、まずは確認コマンドを投入です。
今回はテーブルのカラムに index を生成するので、index を確認するコマンドです。
-- コマンド
SHOW INDEX FROM `sumple_users`;
-- 結果
+--------------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment |
+--------------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| sumple_users | 0 | PRIMARY | 1 | id | A | 0 | NULL | NULL | | BTREE | | |
+--------------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
1 row in set (0.00 sec)ふむ。プライマリキーが設定されていることはわかりましたが、WHERE 句での条件に使用しているカラム（`username`）には何の設定もされていないことがわかりました。
では、今回の索引にしたい `username` に index を貼りましょう。
-- 書式
CREATE INDEX `index名称` on `対象テーブル名` (`対象カラム名`);
-- この記事の場合
CREATE INDEX `index_01` on `sumple_users` (`username`);これで該当テーブルの該当カラムに index が作成されたハズです。
早速確認してみましょう。
mysql> SHOW INDEX FROM `sumple_users`;
+--------------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment |
+--------------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| sumple_users | 0 | PRIMARY | 1 | id | A | 0 | NULL | NULL | | BTREE | | |
| sumple_users | 1 | index_01 | 1 | username | A | 0 | NULL | NULL | | BTREE | | |
+--------------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
2 rows in set (0.00 sec)左からみっつめのカラム "Key_name" に先ほど作成した index （index_01）が表示されています。
これで index が作成されたことがわかりました。
それでは、index を使ってくれるクエリで explain を実行してみましょう。
EXPLAIN SELECT * FROM `sumple_users` WHERE `username` = 'clown1';結果
mysql> EXPLAIN SELECT * FROM `sumple_users` WHERE `username` = 'clown1';
+----+-------------+--------------+------------+------+---------------+----------+---------+-------+------+----------+-------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+--------------+------------+------+---------------+----------+---------+-------+------+----------+-------+
| 1 | SIMPLE | sumple_users | NULL | ref | index_01 | index_01 | 194 | const | 1 | 100.00 | NULL |
+----+-------------+--------------+------------+------+---------------+----------+---------+-------+------+----------+-------+
1 row in set, 1 warning (0.00 sec)type = ALL から type = ref と変化しました。
そして、key = index_01 と先ほど生成した index が使われていることがわかりました。
つまり
フルテーブルスキャン から インデックスを使って等価検索 をするようになりました。
これでパフォーマンスが改善します。
とにかく要対応な type = ALL
type = ALL はテーブルをフルスキャンしています。
レコード数が少なければ大した問題ではありませんが、レコード数が多いと即パフォーマンスが悪化します。
DB チューニングやパフォーマンス改善の初手にこれを確認して、適切にインデックスを貼るだけでも大幅なパフォーマンス改善になるケースが多いです。
だから、type = ALL の結果が無いかだけでも確認して index を生成してみてください。