MySQL初心者に贈るインデックスチューニングのポイントまとめ2014

サイバーエージェント公式ブログをご覧の皆さんこんばんは、インフラ＆コアテク本部の須藤(@strsk)です。普段はAmebaのソーシャルゲーム全般のインフラを見つつ、日本語ラップの啓蒙をしながら弊社社員を素材にコラ画像をつくったりしています。好きなAAは麻呂です。
はい、というわけで今回はMySQLインデックスチューニングの基本的な流れについてまとめてみました。ソーシャルゲームは更新も参照もめちゃくちゃ多いです。数秒のレプリケーション遅延も致命的なので適切なテーブル、クエリとインデックス設計が重要です。︵何でもそうですけど︶インデックスが多くなると更新コストなどが懸念されますが、インデックスが正しく使われていないクエリを放置している方が悪です。そんなこんなで、割と例も偏ったりしてるかもしれませんがあしからず。
前提としてはInnoDBを想定しています。MyISAMはほとんど使っていません。

なぜインデックスが必要か

インデックスが使われていないクエリは、はじめこそ影響は少ない︵見えない︶もののレコード件数が増加するに連れレスポンスが劣化します。 DBのレスポンスが劣化すればWebサービス自体のレスポンスも低下するし、レプリケーション遅延の原因にもなります。また、フルテーブルスキャンなどはCPU負荷になるため1、リリースされたあるクエリが原因で一気にCPUリソースが枯渇しサービス停止に追い込まれる、なんて可能性も否定できません…。クリティカルな負荷がかかる場所だからこそ適切にインデックスを作成する必要があります。

スロークエリログを出力する

解析するためにはスロークエリログが出力されてなければ話になりません。また、出力しているからといって安心してもいけません。 long_query_timeに設定した値より短いクエリは残らないため、1秒に設定している裏で0.8秒かかるクエリが何万コールもしている、ということもあり得ます。 1秒未満でも設定できるので、最初に0.5秒～0.1秒あたりに設定し、スロークエリを改善しながら調整していきます。
log_queries_not_using_indexesを設定すると、long_query_timeの設定に関わらずインデックスが使用されていないクエリを出力することができますが、ゲームのマスターデータでレコード件数も少なくインデックスが必要ないクエリなど、許容しているレベルのクエリも出てしまうため時と場合によって使い分けます。
/etc/my.cnf

slow_query_log = 1
slow_query_log_file = mysql-slow.log
long_query_time = 0.1
#log_queries_not_using_indexes

オンラインで変更する場合

mysql> set global slow_query_log = 1;
mysql> set global long_query_time=0.1;

スロークエリログの設定をすると、このようにクエリの実行時間︵秒︶やロック時間︵秒︶とクエリの内容が出力されます。

# Time: 140829  3:34:08
# User@Host: root[root] @ localhost []  Id: 2156371
# Query_time: 23.355119  Lock_time: 0.000034 Rows_sent: 1  Rows_examined: 28057953
SET timestamp=1409250848;
select count(*) from hoge;

スロークエリログをローテートする

スロークエリログを出力したらローテートの設定もします。統計を取るときも便利だし、開いた時に1年前のクエリから表示されていた時の絶望からも免れることができます。
/etc/logrotate.d/mysql

/var/lib/mysql/mysql-slow.log {
        create 644 mysql mysql
        notifempty
        daily
        rotate 30
        missingok
        compress
        delaycompress
        dateext
        sharedscripts
        postrotate
        # just if mysqld is really running
        if test -x /usr/bin/mysqladmin && \
            /usr/bin/mysqladmin ping &>/dev/null
        then
            /usr/bin/mysqladmin flush-logs
fi
    endscript
}

細かい設定は良きに計らってください。

スロークエリの統計を取る

ログが出力され始めたら、解析の準備をします。目grepしたいところですが、凡人にはちと厳しいため、統計を取ってインパクトの大きいものから解析します。 mysqldumpslowでも良いですが、percona-toolkitのpt-query-digestを使ったほうが見やすくて好きです。
Percona Toolkitインストール

$ sudo yum -y install perl-Time-HiRes perl-IO-Socket-SSL perl-DBD-MySQL
$ sudo rpm -ivh http://www.percona.com/redir/downloads/percona-toolkit/LATEST/RPM/percona-toolkit-2.2.10-1.noarch.rpm

解析方法

$ pt-query-digest mysql-slow.log > digest.txt

digest.txt 上部に統計情報とレスポンスタイムとコール率の高い順にランキングが表示されます。

# A software update is available:
#   * Percona Toolkit 2.2.6 has a possible security issue (CVE-2014-2029) upgrade is recommended. The current version for Percona::Toolkit is 2.2.10.


# 5.3s user time, 80ms system time, 28.53M rss, 216.25M vsz
# Current date: Thu Aug 28 21:42:16 2014
# Hostname: hoge-db03
# Files: /var/lib/mysql/mysql-slow.log
# Overall: 10.48k total, 57 unique, 0.16 QPS, 0.05x concurrency __________
# Time range: 2014-08-28 03:40:02 to 21:42:15
# Attribute          total     min     max     avg     95%  stddev  median
# ============     ======= ======= ======= ======= ======= ======= =======
# Exec time          3002s   100ms     11s   286ms   777ms   284ms   180ms
# Lock time             1s    41us   378us    96us   119us    16us    98us
# Rows sent         13.66M       1 174.31k   1.33k   6.31k   5.51k    0.99
# Rows examine     825.18M   2.00k   1.46M  80.61k 211.82k 110.61k  36.57k
# Query size         5.05M     154     791  504.95  755.64  100.54  487.09

# Profile
# Rank Query ID           Response time   Calls R/Call V/M   Item
# ==== ================== =============== ===== ====== ===== =============
#    1 0x3BEFCC5114487A23 1268.7555 42.3%  4211 0.3013  0.36 SELECT tablename
#    2 0x323595A45502EE39  315.5999 10.5%  1026 0.3076  0.19 SELECT tablename
#    3 0xD0473BC5F5324984  176.0897  5.9%   634 0.2777  0.00 SELECT tablename
#    4 0x9FA860819FCAB0B9  174.2407  5.8%   439 0.3969  0.47 SELECT tablename
#    5 0xB9B067F6AF21AD27  149.3055  5.0%   370 0.4035  0.25 SELECT tablename
#    6 0x957F7C8D78DF45F4   97.7765  3.3%   770 0.1270  0.00 SELECT tablename
#    7 0xDF95841E1D35E78C   85.7142  2.9%   483 0.1775  0.12 SELECT tablename
#    8 0xB8E3A489E461A7D7   82.2773  2.7%   244 0.3372  0.14 SELECT tablename
#    9 0x4E29A68B8903FFB3   72.5987  2.4%   323 0.2248  0.43 SELECT tablename
#   10 0x2839418CA78A9FEB   61.5735  2.1%   103 0.5978  0.00 SELECT tablename
#   11 0x91AAB488213B2825   58.6673  2.0%   126 0.4656  0.23 SELECT tablename

下部にクエリの詳細と平均レスポンスタイムのグラフが表示されます。このクエリはほぼ~~100ミリ秒~~︵※訂正︶→100ミリ秒から1秒の間　で応答していますが、ときどき10秒以上かかっていることもあるようです。これはひどいですね。

# Query 1: 0.06 QPS, 0.02x concurrency, ID 0x3BEFCC5114487A23 at byte 3388347
# This item is included in the report because it matches --limit.
# Scores: V/M = 0.36
# Time range: 2014-08-28 03:40:02 to 21:41:41
# Attribute    pct   total     min     max     avg     95%  stddev  median
# ============ === ======= ======= ======= ======= ======= ======= =======
# Count         40    4211
# Exec time     42   1269s   100ms     11s   301ms   777ms   327ms   180ms
# Lock time     42   426ms    50us   253us   101us   119us    12us    98us
# Rows sent      0   4.11k       1       1       1       1       0       1
# Rows examine  23 197.02M   3.36k 847.55k  47.91k 130.04k  51.64k  30.09k
# Query size    39   2.00M     492     498  497.70  487.09       0  487.09
# String:
# Databases    hoge
# Hosts
# Users        hoge
# Query_time distribution
#   1us
#  10us
# 100us
#   1ms
#  10ms
# 100ms  ################################################################
#    1s  ##
#  10s+  #
# Tables
#    SHOW TABLE STATUS FROM `database` LIKE 'tablename'\G
#    SHOW CREATE TABLE `database`.`tablename`\G
# EXPLAIN /*!50100 PARTITIONS*/
select hoge, fuga from tablename                        where                (                             hoge =             1                                    and                fuga = 2                 and     hogehoge = 3                     )         order by upd_datetime DESC\G

クエリを解析する

ようやくExplainの出番です。対象のクエリの先頭に"EXPLAIN"をつけて解析します。

EXPLAIN SELECT hoge, count(fuga)  from xxxxxxxxxxx_8      where id = 4             and upd_date  <  1408647600000      group by hoge;
+----+-------------+-------------------+-------+---------------+---------+---------+------+---------+-------------+
| id | select_type | table                 | type  | possible_keys | key     | key_len | ref  | rows    | Extra       |
+----+-------------+-------------------+-------+---------------+---------+---------+------+---------+-------------+
|  1 | SIMPLE        | xxxxxxxxxxx_8 | index | NULL          | PRIMARY | 12      | NULL | 4857810 | Using where |
+----+-------------+-------------------+-------+---------------+---------+---------+------+---------+-------------+
1 row in set (0.00 sec)

と、こんな風に解析結果が表示されます。各項目について見ていきます。
select_type ここにDEPENDENT SUBQUERYやUNCACHEABLE SUBQUERYが出ていたら要注意です。DEPENDENT SUBQUERYはインデックスチューニングで改善できる可能性がありますが、UNCACHEABLE SUBQUERYが出ている場合は読んで字のごとくキャッシュできないのでインデックスどうこうではなく、クエリ自体を見なおしたほうが良いです。ただ今回サブクエリの話は出てきません(☝ ՞ਊ ՞)＝☞)՞ਊ ՞)
type 対象テーブルへのアクセス方法を表示しますが、indexとALLが出たら要注意です。indexは一見良さそうですがインデックスのフルスキャンなので遅く、ALLはテーブルのフルスキャンなのでさらに遅いです。上の解析結果はindexなのでチューニングの余地がありますね。
key クエリに使用されたインデックスが表示されます。意図したインデックスが使われていないということもあるので確認します。NULLの場合はインデックスが使われていません。
key_len 使用されているキーの長さ︵バイト︶です。キー長は短いほうが良しとされますが、user_id(int),status(int)で作成した複合インデックスが使われているのに、key_lenが4だとuser_idまでしか利用されていないということがわかります。インデックスが使われているのにスロークエリが出ている場合、このパターンが多いです。データタイプごとに必要な容量は公式ドキュメントをチェケラーしましょう。
~~MySQL :: MySQL 5.1 リファレンスマニュアル :: 10.5 データタイプが必要とする記憶容量~~ ︵追記︶ご指摘いただいたんですが、MySQL5.6ではDATATIMEなどで記憶容量が変更されているようなので下記のほうがまとまってて良いです。 MySQL :: MySQL 5.6 Reference Manual :: 11.7 Data Type Storage Requirements
rows 検索に読み取る必要がある推定レコード数が表示されます。インデックスが使われていようとここが数万を超えるようだとスロークエリとして出力されることが多いです。上の解析結果では480万行と明らかに多いことがわかります。まずはここを減らせるようにインデックスを作成します。
Extra 注意すべきはUsing filesortとUsing temporaryです。Using temporaryはソート時のテンポラリテーブルが必要な場合に表示されますが、極力少ないほうが良いです。Using filesortはインデックスを利用しないクイックソートなので大抵遅くなります。レコード件数が多ければ多いほど致命的になるのでインデックス作成が必須になってきます。後述しますが、Using IndexはCovering Indexが利用されている場合に表示されます。

インデックス確認

mysql> SHOW INDEX FROM tbl_name;

インデックスを作成する前に現状のインデックス構成を把握しておきます。確認するには"SHOW INDEX 構文を利用します。

よくチェックする項目を説明します。書いてない項目はそんなに見ることはないです。
Key_name そのままインデックスにつけた名前です。もちろん主キーはPRIMARY。
Column_name これもそのまま、各インデックスのカラム名です。
Cardinality ユニークな値の見積もりです。見積もりなので正確な値ではないですが、カーディナリティが高ければインデックスを利用する可能性が高くなります。逆にカーディナリティが低いカラムにインデックスを作成しても効果が薄いとも言えます。

インデックス作成

mysql> CREATE INDEX index_name ON tbl_name(index_col_name, ...);

インデックス作成はCREATE INDEX構文を利用します。ALTER TABLEでもいいですが、CREATE INDEXのほうがシンプルで好きです。
いくつかのパターンごとにチューニング方法を説明していきます。なお実際のチューニングでは、おそらくバックアップはしているでしょうから、バックアップDBでインデックスを作成しEXPLAINの結果を比較しながら適切なものを探すのが良いと思います。説明文中ではいろいろはしょってCREATE INDEX文の"(index_col_name, ...)"内のみ表記します。

mysql> SELECT ... WHERE col1 = x AND col2 = y;

インデックスの基本はWHERE句の順番どおりに作成します。この場合であれば(col1,col2)です。ただし、既にcol1からはじまる別のインデックスを利用していてcol2のカーディナリティが低い場合、もしくはcol1のカーディナリティが高かったりユニークな値の場合は既に絞りきっているため効果は薄いです。WHERE句がそのあともcol3 = z AND col4 = …と続く場合も含めて、rowsの値がちゃんと減っているか確認しながら適切なカラムまで指定します。

mysql> SELECT ... WHERE col1 = x ORDER BY col2;

ORDER BY句があるときに適切なインデックスがないとUsing filesortが出ます。基本は順番通り(col1,col2)で作成します。

mysql> SELECT ... WHERE col1 = x ORDER BY col2 LIMIT 10;

これにLIMITがついている場合、かつcol1のカーディナリティが低い︵検索効率が悪い︶場合は(col2)で作成しソートした順にWHERE句で抽出したほうが10件呼ばれた段階で処理が完了するため高速になります。

mysql> SELECT ... WHERE col1 = x AND col2 = y ORDER BY col3;

WHERE句に複数のカラムがあるパターンでも順番どおりの(col1,col2,col3)で作成しますが、col2のカーディナリティが低い場合は(col1,col3)なども有効です。ソート時のインデックス利用はkey_lenに含まれない場合もあるので注意ですが、ExtraでUsing filesortが出てこなくなればOKです。

mysql> SELECT ... WHERE col1 = x AND col2 > y ORDER BY col3;

WHERE句にレンジスキャンの条件が入っている場合、順番通りのインデックス(col1,col2,col3)ではソートにインデックスを利用することができません。その場合はレンジスキャン(col1,col2)かソート(col1,col3)で比較して作成します。構成次第ですが(col1,col3)の方が良い結果になりやすいことが多いように思います。

mysql> SELECT ... WHERE col1 = x AND col2 > y AND col3 > z;

レンジスキャンが複数あるパターン。レンジスキャンはひとつのカラムまでしか利用できないので、これも(col1,col2)か(col1,col3)でrowsの値などを比較します。

mysql> SELECT ... WHERE col1 = x AND col2 = y ORDER BY col3 DESC col4 ASC;

ORDER BYの条件に昇順と降順が混在している場合、これはどうやってもインデックスが効かないのでクエリ自体を見直しましょう。

Covering Index

上記では検索効率の悪いカラムをなるべくインデックスに含めない方針で説明してきましたが、呼び出しに必要なカラムで主キー以外のすべてがセカンダリインデックス内にあればCovering Indexとなりさらに高速に結果を返すことができます。これは、InnoDBのインデックス構造の恩恵でランダムリードが大量にあるクエリなどで効果が大きくなります。ここまで来てなんですが積極的に使っていったほうが良いです。なおCovering Indexになる場合、ExtraにはUsing indexが表示されます。

mysql> SELECT pk_col FROM t1 WHERE col1 = x AND col2 = y;

この場合は(col1,col2)のセカンダリインデックスがあれば主キー(pk_col)がセカンダリインデックスのリーフに格納されているのでCovering Indexになります。

mysql> SELECT pk_col,col1 FROM t1 WHERE col1 = x ORDER BY col2;

この場合も(col1,col2)でおそらくCovering Indexになります。

mysql> SELECT pk_col FROM t1 WHERE col1 = x AND col2 > y ORDER BY col3;

この場合、(col1,col2,col3)でCovering Indexになりますが、同時にUsing filesortも出てしまいます。ランダムアクセスがない分こちらが高速になる可能性がありますが、リソース状況などを考慮してどちらを取るか決めたほうが良いでしょう。

注意点

●検証は本番と同じ環境︵レコード数︶で行う インデックスを作成しても、テーブルの30％以上のデータにアクセスする場合インデックスは利用されません。データ量によってオプティマイザの選ぶインデックスが変わるためなるべく同一状況で検証したほうが良いです。 ●フルテーブルスキャンでもrowsが減れば速くなる 速いと判断すればオプティマイザがフルテーブルスキャンを選択することもあります。オプティマイザに任せずインデックスを使いたい場合はFORCE INDEXなどを使います。 ●key_lenを見て期待通りにインデックスが利用されているか確認する 無駄な複合インデックスや効率的なインデックスを判断するのに有効です。 ●事前にインデックス作成時間を見積もる インデックス作成に限ったことではないですが、数分かかるALTER文を本番稼働中に実行したらその間更新はロックされるのでアウトです。さらに同じ時間だけレプリケーションも遅延してしまうので見積もることが重要です。 pt-online-schema-changeを使うか、MySQL5.6であればOnline DDLという機能が追加されているためロックされずに実行できます。ただし、これによってレプリケーション遅延が回避できるわけではないので注意しましょう。