SRGについてこれまでの取り組み技術記事採用情報
SRGについてこれまでの取り組み技術記事採用情報
HOME/技術記事/MySQLインデックスチューニング基礎

MySQLインデックスチューニング基礎

技術本部 サービスリライアビリティグループ（SRG）の鬼海（@fat47）です。
#SRG（Service Reliability Group）は、主に弊社メディアサービスのインフラ周りを横断的にサポートしており、既存サービスの改善や新規立ち上げ、OSS貢献などを行っているグループです。
本記事は、SRG 内にある DBWG（DBワーキンググループ）が全社内向けに提供しているデータベースに関する資料を公開します。
なにかの役に立てば幸いです。
 

インデックスとは

MySQLがデータを効率的に取得するデータ構造でDBのデータサイズによって重要性が変わります。
インデックスは本に例えると「索引」です。
ある項目について本の中身を探すときに本の索引を利用することで、
その項目が本の何ページに登場するかをすぐに見つけることができます。
 

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

ページ数が少ない本では最初のページから探していけば目的の項目を探せますが、
辞書のようにページ数が多い本ではすぐには探せません。
DBでも同様に小さいサイズのデータベースの場合はインデックスがなくてもデータは探せます。
しかしDBサイズが大きくなると急激にパフォーマンスが低下する可能性があります。
そのため適切なインデックスを設定し、目的のデータをすぐに探し出せる状態にする必要があります。
 

EXPLAINの見方

インデックスを貼るにはEXPLAINコマンドを活用していくことが必要です。
EXPLAINはMySQLのクエリオプティマイザがクエリをどう実行するかを可視化してくれます。
💡
EXPLAINは本番環境と同量のデータが入ってる環境で解析するようにしましょう 開発環境やステージング環境等のレコード数が少ない環境の場合 インデックスが存在してもフルスキャンしたほうが早いと判断される場合があります
 
実際にクエリを解析してみましょう。

EXPLAINの各項目意味

  • id select文を識別しているid番号
      • サブクエリやUNIONを使っている場合は参考になることもあります。
  • select_type PRIMARY/SUBQUERY/DEPENDENT SUBQUERY/UNCACHEABLE SUBQUERY/DERIVED/UNION/UNION RESULTなどが記載されます。
      • DEPENDENT SUBQUERY(相関サブクエリ)が出ていたら注意しましょう。
  • table テーブル名
  • type
      • テーブルへのアクセス方法 ALL/index/range/ref/eq_ref/const,system/nullなどがある。 ALLはフルスキャン、indexはインデックス順のフルスキャンなのでこれが出ている場合は注意。 望ましい順番はconst-eq_ref-ref-range-index-ALL
  • possible_keys 選択される可能性のあるインデックスです。
  • key 実際にクエリで選択されたインデックスです。 NULLの場合はインデックスが使用されていません。
  • ref ref列はkey列で指定されたインデックスの値を調べるために利用されます。 定数が指定されているとconst 、JOINの場合は結合する相手側のテーブルで検索条件として 利用されているカラムが表示されます。
  • rows MySQLが目的の行を検索するために読み取る必要があると推定した行の数を示します。 基本的にはrowsがすくなければ少ないほどよいです。 ここの数字は大まかな見積もりであり実際にフェッチする行ではないことと、 WHERE句の検索条件によってフェッチした行に対してさらに行の絞り込みが行われるので、返される結果行は少なくなる可能性があることに注意です。
  • Extra オプティマイザがどのような戦略を選択したかを示しています。 下記のような情報が表示されます。
    • Using index - カバリングインデックスを利用しており最もよい。
    • Using where - ストレージエンジン側で行を取得した後にwhere句でMySQLサーバーがフィルタリングしている。
    • Using temporary - 結果ソートに一時テーブルを作成している。group byするときなどは仕方がないが、極力ないほうが良い。
    • Using filesort - インデックス順ではなく、ファイルソートを利用する。少ない件数であればファイルソートが速いこともあるが、件数が増えた場合はほとんどこの部分がボトルネックとなるので避けること。

インデックスチューニングの流れ

  1. 遅いクエリを把握するためにスロークエリ取得設定をいれてクエリを探します。
  1. 遅いクエリにEXPLAINを付けてサービス参照のないDBスレーブで実行します。
  1. rowsやExtraの出力内容を見て適切なインデックスが適用されているか確認します。
  1. 3の結果でインデックスに不足があったテーブルにインデックスを設定します。
💡
チューニングのコツひとまとめ ・ rowsが少なくなるインデックスを作成しましょう。多くても数万程度にしたいです。 ・更新性能に影響するのでやみくもにindexは貼らない。 ・インデックスによってrowsが20%程度に絞られる場合に貼ることを検討しましょう。 ・カーディナリティが低すぎる(インデックスを貼っても30%以上にしか絞られない)場合は貼っても効かないか逆に遅くなることもあります。 ・explainのkey_len列を見て、複合インデックスがどこまできいているのか確認しましょう。 ・Extra列にUsingfilesort　Using Temporaryが出ている場合は要注意。 ・ソート(order by)にインデックスを使わせるように気をつけましょう。 ・インデックスが効くようなクエリを書きましょう。 ・複合インデックスは範囲条件(upd_datetime < 'YYYYMMDD'など)が発生した場合そこで終わります。 ・つまり複合インデックス内で複数の範囲条件に対して複合インデックスがあっても両方は利用できません。ソートの場合も同様です。

解析のケーススタディ

とあるテーブル設計をサンプルとして解析してみます。
サンプルテーブル名：i_user_test
レコード件数：約100万件
 

ケース1 : インデックスが全く設定されていない場合

rowsが106万件あるので全件取得のフルスキャンになっています。
にインデックスを貼ってみます。
rowsが約3万件まで絞りこまれています。さらに件数を絞るため
と の複合インデックスを作成します。
rowsが約4000件まで絞れました。key_lenを見てみると8になっています。
これはintが1カラム4バイトなので、2カラムにつけた複合インデックスが利用されていることがわかります。
更に にも複合インデックスを貼ってみましょう。
はtinyintなのでバイト数は1です。key_lenが9になっているので複合インデックスの
利用はされているようですが、rowsの件数はかわっていません。
これはの結果がほとんど0でカーディナリティが低い（カラム内のデータの種類の数が少ない）ため絞り込まれていないです。
ですので今回はと までの複合インデックスが適切だったということがわかります。

ケース2 : 「1」のクエリにソートとLimit句を追加した場合

rowsの件数は絞り込まれていますが、ExtraにUsing filesortが出現しました。
このクエリの場合4000件を取得してソートしたあとに10件だけ取り出しているので、約4000件の取得はほぼ無駄と言えます。
ソート対象であるも複合インデックスに加えます。
ExtraからUsing filesortが消えました。key_lenを見ると8なのでが使われてないようにみえますが、実際はソートで利用されています。
今回の場合はソートされた順に10件取り出した時点で処理が完了するため、rowsの4000件も取りにいかないようになっています。

ケース3 : 「2」に加えて範囲条件がある場合

ととで複合インデックスを作成してみましょう。
ExtraにUsing filesortが出ているのでのソートでインデックスが使われていません。これはInnoDBの仕様上の範囲条件でインデックスの利用が終了してまうためです。
この場合との2つの複合インデックスを作成してのfilesortを許容するか、
との複合インデックスを作成してfilesortを消すか実際の実行時間を見て検討する必要があります。

ケース4 : 複数の範囲条件がある場合

ととで複合インデックスを貼ってみます。
key_lenは12になっています。4バイトのと8バイトのまでしかインデックスは効いておらず、は使われていません。
これはケース3同様、範囲条件が利用された時点でインデックスの使用が終了するためです。
つまり定数条件(この場合で言えば)は複合インデックスの中に複数指定できますが、範囲条件はインデックスの最後に一つしか指定できません。
ですので今回のケースの場合、[]か[]の
どちらかのインデックスしか選択できません。
まずはでインデックスを作成してみます。
rowsが約19万件になったため、最初ののほうが件数が少ないため適切だと言えるでしょう。
今回のケースではクエリを書き換えることができそうです。
を範囲条件ではなくIN句に書き換えて、それに合わせてインデックスを貼ってみましょう。
level <5で範囲条件にしていた部分をlevel in (1,2,3,4,5)に変更しています。
key_lenは16で全てのカラムのインデックスが効いており、rowsの件数は1486件まで減りました。
このように範囲条件をINである程度少ない引数で書き換えられる場合は、そちらの方がインデックスを効果的に利用することができます。
ただし実際の実行速度については本番環境で比較することが必要です。

ケース5 : カバリングインデックスの作成の場合

このクエリの場合ととの3つにインデックスを貼ると、
インデックスを見るだけで結果を返すことができます。これを「カバリングインデックス」といいます。
Extra列にUsing indexが記載いるのでカバリングインデックスになっています。
あまりインデックスを張りすぎるのもデータ肥大化や更新負荷につながるのでよくないですが、
効果がありそうなときは活用しましょう。

ケース6 : カバリングインデックスその2

条件は5とほ同じですが取得するカラムがPKのuser_idとインデックスで利用しているのみとなります。
、、3つでインデックスを貼ってみます。
Extra列にUsing indexが出ているのでカバリングインデックスとなっています。
Using filesortがでているのはのソート処理の部分です 。
ケース3のようにでインデックスを作成することでfilesortをなくす方法もありますが、filesortを許容する場合は今回のようにカバリングインデックスになっていたほうがデータを見にいかない分若干性能がよくなります。

ケース7 : 複合インデックスの順番

インデックスは左から順番に評価されていくので
複合インデックスの左端のカラムが条件になければインデックスは利用されません。
これは複合プライマリーキーでも同様です。
この複合インデックスを利用するようにするためには、下記のように順番をいれかえると利用されます。

ケース8 : サイズの大きいカラムのインデックスの場合

はvarchar(255)のカラムです。
key_lenが768=((255+1)×3)となっています。3倍になってるのはutf8Unicodeで一文字ごとに3バイト必要なためです。
今回の場合最初の一文字だけで必要なデータが取り出せそうなので、一文字だけのインデックスを貼ってみます。
key_lenが6=((1+1)×3)になっているのでインデックスサイズを減らすことができています。
rowsも先ほどと変わりませんので先頭何文字かのみで絞り込める場合は、
このようなインデックス作成も有効です。
 

終わりに

SRG では一緒に働く仲間を募集しています。 ご興味ありましたらぜひこちらからご連絡ください。
 
このエントリーをはてなブックマークに追加