MySQLのパフォーマンスチューニング

背景

今担当している案件で、MySQLのパフォーマンスチューニングを行う必要がありましたので、調査した内容について記録として残しておこうと思います。

結論

1. インデックスはカーディナリティの高いカラムに貼る。
2. 複合インデックスは、カーディナリティの高い順に貼る。

インデックスの貼り方

インデックスの貼り方の基本について、以下の例をご覧ください。

usersテーブル

※classは、A-1～A-999まであるとします。

id	name	gender	address	class
1	taro	M	tokyo	A-101
2	jiro	M	kanagawa	A-21
3	hanako	F	sendai	A-33
4	hiromi	F	hukuoka	A-11
...	...	...	...

はじめに、次ようなクエリを考えます。

SELECT * FROM users WHERE address = "kanagawa"

この場合は、addressカラムにインデックスを貼ることでパフォーマンス改善が見込めます。

次に、以下のクエリについて考えます。

SELECT * FROM users WHERE gender = "M"

この場合は、genderカラムの値はMかFの２択しかない（カーディナリティが低い）ため、インデックスを貼ってもパフォーマンス改善は期待できません。場合によっては、テーブルフルスキャンになってしまうこともあります。

続いて、次のクエリについて考えます。

SELECT * FROM users WHERE address = "kanagawa" AND WHERE class = "A-19" 

この場合は、以下のようになります。

• addressカラムに対する単一インデックス：有効
• classカラムに対する単一インデックス：有効
• address, classの順に貼った複合インデックス：有効
• class, addressの順に貼った複合インデックス：効果なし
  複合インデックスについては貼る順番を間違えると効果が出ないので注意しましょう。

複合インデックスの効果を最大限生かすためには？

インデックスを貼る順番が大切です。usersテーブルのaddressカラムとclassカラムに複合インデックスを貼る例を考えてみましょう。

例えば、usersテーブルに100万件のレコードがあるとします。
addressカラムにインデックスを貼って絞り込んだとすると、addressは47都道府県で47択のため、1/47に絞り込める見込みになります。

一方、classカラムにインデックスを貼って絞り込んだとすると、classはA-1～A-999で999択のため、1/999まで絞り込みを行える見込みとなり、大幅に絞り込みを行うことができます。

最初に検索候補を大きく減らしたほうがその後の処理が楽になるため、このケースでは

create index users_class_address_index on users(class, address);

のような順番でインデックスを貼ったほうが、パフォーマンスが良くなります。

まとめ

パフォーマンスチューニングは、実際の実行計画や速度を見て試行錯誤しながらチューニングを行っていくことになるため、一概に『これが正解』と言えない部分もありますが、基本的事項をしっかり押さえられていることで、良いチューニングができると思います。
機会があれば、インデックスを貼ったのにテーブルフルスキャンになる原因などについても記事を書きたいと思います。