EXPLAINによるMySQLの実行計画の確認
担当しているソフトウェアで、運用しているとMySQL内のレコードが増えて性能が悪化するというお問い合わせをいただきました。 そこで、Indexを改善しようということになったのですが、どこにIndexを付けるべきか確認するためにMySQLの実行計画を確認しました。
自分のバックグラウンドとしては、データベースは特別詳しいわけではないので、 Indexは論理的に付けてはいるものの、それがあっているのかどうか確証はありませんでした。
で、調べたところMySQLでも実行計画が確認できるみたいなので、今回、それを使って確認を行ってみました。
MySQL :: MySQL 5.6 リファレンスマニュアル :: 8.8 クエリー実行プランの理解
対象のテーブル
こんなテーブルです。ユーザーのセッション情報を格納するためのテーブルで、ログアウトされないと、レコードが蓄積されていきます。
MySQL [apppot]> desc UserSession; +-----------------+--------------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +-----------------+--------------+------+-----+---------+-------+ | userToken | varchar(255) | NO | PRI | NULL | | | appTableId | bigint(20) | YES | | NULL | | | companyId | bigint(20) | YES | | NULL | | | deviceUDID | varchar(255) | YES | | NULL | | | loginDate | datetime | YES | | NULL | | | tokenExpireDate | datetime | YES | | NULL | | | userId | bigint(20) | YES | | NULL | | +-----------------+--------------+------+-----+---------+-------+ 7 rows in set (0.05 sec)
こんな感じのSQLが実行されます。
select * from UserSession where userId=205 and appTableId=4 and deviceUDID='apppotsdkjs' and tokenExpireDate>='2018-01-12 22:16:13';
MySQLの実行計画の確認は手軽で、EXPLAINを頭に付けるだけでOKです。
Index追加前
rowsに122381という値が入っていますが、いわゆるフルスキャンが発生して、全部のレコードをチェックしていることがわかりました。そりゃ遅いですね。
MySQL [apppot]> EXPLAIN select * from UserSession where userId=205 and appTableId=4 and deviceUDID='apppotsdkjs' and tokenExpireDate>='2018-01-12 22:16:13'; +----+-------------+-------------+------+---------------+------+---------+------+--------+-------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +----+-------------+-------------+------+---------------+------+---------+------+--------+-------------+ | 1 | SIMPLE | UserSession | ALL | NULL | NULL | NULL | NULL | 122381 | Using where | +----+-------------+-------------+------+---------------+------+---------+------+--------+-------------+ 1 row in set (0.01 sec)
userIdにIndexを追加してみる
試しにuserIdにIndexを追加してみます。 ログインするユーザーが十分バラけていれば、これでも早くなりそうな感じがします。
ALTER TABLE UserSession ADD INDEX index_userId(userId);
実行計画はこんな感じ。チェックする対象のrowsの値が1/4になってますね。
MySQL [apppot]> EXPLAIN select * from UserSession where userId=205 and appTableId=4 and deviceUDID='apppotsdkjs' and tokenExpireDate>='2018-01-12 22:16:13'; +----+-------------+-------------+------+---------------+--------------+---------+-------+-------+-------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +----+-------------+-------------+------+---------------+--------------+---------+-------+-------+-------------+ | 1 | SIMPLE | UserSession | ref | index_userId | index_userId | 9 | const | 33436 | Using where | +----+-------------+-------------+------+---------------+--------------+---------+-------+-------+-------------+ 1 row in set (0.00 sec)
複数カラムのIndex
MySQLは複数のIndexあっても、どれか１つしか使ってくれません。 そこで、複数のカラムで１つのIndexを作ることができます。遅いSQLの条件で使っているカラムの組み合わせてIndexを作ります。
ALTER TABLE UserSession ADD INDEX index_get_session(userId, appTableId, deviceUDID, tokenExpireDate);
実行計画を見てみましょう。rowsの値が3桁減ってますね。 possible_keysが使用可能なIndex、keyが実際に使われたIndexということのようです。 新しく付けたindex_get_sessionが使われてますね。
MySQL [apppot]> EXPLAIN select * from UserSession where userId=205 and appTableId=4 and deviceUDID='apppotsdkjs' and tokenExpireDate>='2018-01-12 22:16:13'; +----+-------------+-------------+-------+--------------------------------+-------------------+---------+------+------+-----------------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +----+-------------+-------------+-------+--------------------------------+-------------------+---------+------+------+-----------------------+ | 1 | SIMPLE | UserSession | range | index_userId,index_get_session | index_get_session | 792 | NULL | 258 | Using index condition | +----+-------------+-------------+-------+--------------------------------+-------------------+---------+------+------+-----------------------+ 1 row in set (0.08 sec)
以上です。
