「コツ」と題しましたが、パフォーマンスの良いSQLって本当に難しい事で、ちょっとしたコツでなんとかなる問題ではないことは重々承知しています。
しかし最近、SQLは知っているもののパフォーマンスなんて何も意識した事がない方が結構いるのだなぁと実感することがあり、そういう方にパフォーマンス設計のさわりだけでも知っていて頂ければと思い、記事にしました。
この記事の対象者
SQLはよく書くものの、パフォーマンスを意識してSQLを書いたことがない方
環境
MySQL5.5〜5.7
エンジンはinnoDB
まずは自分が作ったSQLが速いか見てみる
SQLを実行して素早く返ってくるか？だけで判断せず、**実行計画（explain）**も見るようにしましょう。
実行計画とは、MySQLがどのような手順でSQLを解析、実行するかの計画を示してくれるものです。
SQLの頭にexplainとつけて実行するだけで見ることができます。
explain
select
*
from
t1 inner join t2 on t1.t2_id = t2.id
where
t2.name = 'aaa'
以下のような結果が取れます。
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
|---|---|---|---|---|---|---|---|---|---|---|---|
| 1 | SIMPLE | t1 | NULL | ALL | index_t1_on_t2_id | NULL | NULL | NULL | 341 | 10.00 | Using where |
| 1 | SIMPLE | t2 | NULL | eq_ref | PRIMARY | PRIMARY | 4 | db.t1.t2_id | 1 | 100.00 | NULL |
実行計画の見方ですが、とりあえずtable、type、rows、Extraを見ておきましょう。
| カラム | 説明 |
|---|---|
| table | この順でテーブルを解析するよ、ということ |
| type | テーブルの検索方法。上から順に総なめしていくとか、インデックスを使用して高速に検索するとかいうことがわかる |
| rows | 検索対象とする行数で、ここが多いとそれだけパフォーマンスが悪くなる |
| Extra | もう少し詳細な解析内容が書かれる |
以下のような結果は要注意！
- table：絞り込みが効きにくい順序になっていないか
- type：ALLが出ている
- rows：何十万件にもなっている
- Extra：Using filesortやUsing temporaryが出ている
逆にすばらしい実行計画は以下のような感じです
- 「PRIMARY」と出ている（主キー検索）
- 「eq_ref」と出ている（インデックス検索）
遅いSQLがアプリケーションの中にどれくらいあるのだろう？
slow logを出力するようにMySQLを設定しましょう。応答に○秒以上かかったSQLだけを収集してくれるログです。１秒とか１分とか３０分とか設定できます。
インデックス
インデックスとは辞書の索引のようなものです。テーブルごとにalter tableで追加していくことができます。主キーや外部キーには最初からインデックスが作成されます。
MySQLはインデックスのないカラムで検索すると、一番上から順に総なめして検索します。これがtype:ALLの状態です。件数が多いと非常に遅くなります。
インデックスはうまく使えば劇的に速くなります。
インデックスの作成
インデックスを検討するときは以下の観点で行いましょう。
- 対象テーブル：データ件数が多く、検索されることも多いテーブル
- 対象カラム：where・join・sortに頻繁に使用されるカラムで、かつデータのばらつきが多いカラム
ひとつのテーブルに複数作成することができますが、１回のSQLで１テーブル１つしか使用されません。たくさん貼っても意味がない場合もあるので注意して下さい。
データのばらつきですが、例えばフラグのような２値しか持たないカラムに対してインデックスを作成してもあまり効果が得られないということです。IDや作成日時など、ばらばらのデータには非常に効果的です。
また、インデックスを作成することで以下のようなデメリットもあります。
- insertやupdateは遅くなる（同時にインデックスも作成するため）
- インデックス自体もディスク容量を消費する
インデックスが効いてるか確認する方法
インデックスを貼ったあと、実行計画のpossible_keysとkeysを見てみましょう。
| カラム | 説明 |
|---|---|
| possible_keys | 使用できるインデックスの候補 |
| keys | 実際に使用するインデックス |
インデックス貼ったのに効かない！インデックスの効きを阻害する要因
関数
DATEDIFFとかSUBSTRINGとか、私はよく使ってしまうのですが、関数を使用したカラムはインデックスが全く効かなくなるので要注意です。
LIKE %text%
LIKE検索は前方一致のみインデックスが効きます。
text%→効く
%text%→効かない
他のインデックスが採用されている
複数のインデックスを貼っている時、どのインデックスを使用するかはMySQLが判断しますが、時に思いもよらぬインデックスを使用してしまうことがあります。
そういう場合は以下のような対策があります。
- analyze tableコマンド実行（インデックスの再編成）
- USINGという構文を使用して使用するインデックスをMySQLに指示する
SQLがあまりにも複雑すぎる
副問合せしてCASE構文使ってJOINも多すぎてUNIONもしちゃって更にGROUP BY HAVING……こんなことになると、MySQLも限界です。
個人的には、SQLはシンプルイズベストにして、プログラムの方でできることはプログラムにやらせる、としています。