MySQLクエリパフォーマンス改善簡易まとめ

数年やってないと記憶の彼方に飛んでいきそうだったので、MySQLのクエリ改善方法のテンプレを自分用に明記。

スロークエリを除去する事。

• 初めはとにかく観察。スロークエリを出力させて、観察する。
• indexが効かないクエリを排除する。
• indexが予期できない条件分岐によるクエリを廃止する。
• 場合によってはソートをさせない。コード側でソートさせる。
• JOINをわざとさせないのも一つの手。後にDB分離レベルのシャーディング等が発生する可能性のあるようなシステムでは、JOIN禁止にする事は決して間違ってはいない。

indexを必ず効かせる

• レコード数に応じて、割当たるindexが異なることがあるので、必ず同じデータ数か実際の運用環境で検証すること。
• 但し、indexを増やし過ぎると、挿入時に更新対象が増えるため、必要最低限にすること。

explain してindexを確認する

• 特に注目しなければいけないのはtype。
• typeがallや indexだと問題。全スキャン相当が走る。 refやrangeなどに最低でも抑えこむこと。もっともよいのはconst。
• 他、Extraに、「Using filesort」など出てるかを確認。複合indexなどで対処する。

LIMITの罠に注意

• LIMITがあろうと、全スキャンになったら意味は無い。絞り込んだサブテーブルを使うなどする。
• 高速化するために1000ごとに取得を分けるようなアホな事をするなら、一括で取得したほうがマシ。

大量の更新処理に注意

• Masterで非常に時間のかかる更新処理を行うと、場合によってはクエリを詰まらせることになる。

大量のdelete処理に注意

• deleteはupdate以上に重い。
• 該当レコードを全て洗い出してから、indexも含めて削除する処理を行うので、適当なupdateクエリよりも重い。
• 百万以上のレコードを削除するときは、余裕で超スロークエリ化するので注意。

Select For Update に注意

• SelectしたデータをいじってUpdateする時、他と衝突させないためにSelectした段階でロックをするために Select For Updateをする　というテクがあるが、MySQL的には共有ロックがかかる
• 共有ロックを解除しないとクエリのロック待ちが発生するので当然詰まる。
  • 共有ロックの後に、Where文が無い全更新クエリが走ったりすると最悪
• 量が多いなら何らかのキューイングで順番に処理をさせるなど、MySQL以外の機構でロックを回避してもいいと思われ。

トランザクションを貼ってから解除するまでの間に余計な処理をコードに書かない

• クエリの生成処理はトランザクションを貼る前にやりましょう。
• Redisへのアクセス、memcachedへのアクセス、コード上（PHP等）での処理は、トランザクションの外でやりましょう。
• よくある例
  1. トランザクション貼る
  2. 配列や変数から文字列クエリを作る（余計な処理）
  3. insert
  4. トランザクション解除
  • 文字列クエリを作ってる間もトランザクションは貼られているため、ロック待ちが増えるとクエリが詰まりやすくなる。
  • 文字列クエリを作る時間が顕著にDBに影響する。コードの書き方でクエリ処理の時間が変わる
  • どっちも一瞬だろと思うけど、PHPの文字列結合の遅さをあまりなめないほうがいい。クエリが多いと塵も積もれば山となる。

データがメモリ上にのるなら全部乗せる

• 当たり前なので略

パーティショニングする

• 100万以上のレコードを入れる可能性があるテーブルは、パーティショニング。
• 目安として100万といってますが、レコード数が増えれば増えるほど、クエリは重くなる。
• 1テーブルでなんとかなるならパーティショニングで。

シャーディング（水平分割）する

• 1テーブルどころか1DBでもなんとかならなければシャーディング。
• 複数マシンのパワーを使えばきっと怖くない。
• あと、MySQL5.0とか使ってる場合はパーティショニング出来ないので同様にシャーディングをorz

垂直分割する

• いわゆる正規化の話。
• データ量を削減できるが、HDD容量の問題というよりかは、ネットワークのデータ転送量を削減できる方がメリットとしては大きい。
• ただし、やりすぎると大量のJOINが必須となる。レコード数が100万を超えるシステムでは逆にデメリットの方が大きくなる可能性が高い。

InnoDBだけじゃなくMyIsamも検討する

• 場合によってはInnoDBよりもMyIsamの方が高速なパターンもある。
• 同じクエリを同じ頻度で流して要検証すること。
  • 大量のUpdate処理でテーブルロックがかかるパターンでもレコード数が少なければMyIsamの方が高速というのはあった。
• 但し、行ロック・テーブル・ロックの違い、その他の違いが多数あるので、当然注意は必要。

クラステーブル継承をやめる。（正規化とJOINを極力使わなくする）

• 代わりに、具象テーブル継承を使う。
• スループットを下げずにJOINが可能なのは、せいぜい1万レコードまで。
• 10万レコード超える事が想定されるテーブルは、具象テーブル継承で設計すべき。
• そもそも100万を超えるレコードの時は、
  • パーティショニングが必須だが、パーティション間をまたぐ検索はスループットを悪化させるので防ぐべき。
  • JOINは全パーティション間をまたいだ検索が必要になるため、不向き