ざっくり始めるクエリパフォーマンスチューニング

お断り

  • 基本的にはMySQL(InnoDB)を前提にしています
  • ものによりMySQL5.6と5.7の情報が混在しているかも
  • 一部個人的な見解が含まれています

クエリの実行順序

大きい括りでの基本戦略

  • あくまで個人的な意見
  • DBサーバにはDBサーバにしかできないことをやらせる戦略
    • アプリサーバでできるなら任せる
      • 例えば、データのソート、絞り込み
    • 転送量との相談にはなるがざっくり投げてもネットワークに負荷がかからない、アプリサーバのメモリに乗り切る場合はアプリサーバにぶん投げてアプリサーバ上でやる
    • DBサーバのリソースは貴重です
    • DBのキャッシュを有効にしているとキャッシュヒット率が改善されるかもしれません
      • MySQL8.0からキャッシュが無効になったので過度なキャッシュ利用は避けましょう
      • キャッシュは麻薬
    • DBのキャッシュ機能をオフっていてもOSキャッシュが効く可能性がある
    • DBサーバのリソースはDBサーバにしかできないことに使いましょう
      • 基本的には、更新系クエリ優先
      • 読み取りは、レプリケーションを使えばある程度はスケールアウトが可能
      • 更新系の処理で負荷に耐えれない場合は一般的にはスケールアップで対応する
      • スケールアップにも限界がある
      • マルチマスタという手もあるが一般的に運用(障害発生時など)が大変なので避けたほうが良いというスタンス
      • 物理的にDBを分ける垂直分割という手もあるがトランザクションやデータの整合性を担保するのにアプリ側の対応が必要になる場合がある
  • これが絶対正しいわけではない

実行計画のもととなるデータ

実行計画の改善の基本戦略

  • インデックスを活用する
    • インデックス特性を理解して取得する
    • カバリングインデックスが使えて効果的な場合は使う
    • カーディナリティが低い場合は、あえてインデックスを設定しない選択もする
    • 基本的にMySQL(InnoDB)は一つのテーブルにつきインデックスは一つしか使われない
      • ケースにより使われる(sort_union)
  • 不要なデータは取得しない
    • ただしアプリに処理を委ねるときはその限りではない
  • 駆動表は可能な限り小さく
  • オンメモリで完結させる
    • Using temporaryはメモリ上では対応できなくなったという意味で外部装置を使っている
    • いくらSSDが速くなったとはいえメモリに勝てるほどではない
    • 読み込み速度を意識する
    • あくまで目安。実際はSSDはもっと速いものもあるが
名前 読込速度 読込速度(MB/s)
メモリ 10GB/s 10000MB/s
有線ネットワーク 1GB/s 1000MB/s
SSD(シーケンシャルアクセス) 100MB/s 100MB/s
HDD(シーケンシャルアクセス) 100MB/s 100MB/s
SSD(ランダムアクセス) 10MB/s 10MB/s
HDD(ランダムアクセス) 1MB/s 1MB/s

カーディナリティ

  • 多様性のこと
  • 都道府県やフラグ、モードなどデータの種類が限られていること
  • データの種類が限られていることをカーディナリティが低いという
  • 一般的にはカーディナリティが低いとインデックスの効果が薄いと言われる
    • Bツリーでの絞り込み効果が低いため
  • ただし、カバリングインデックスを効かせるためにあえて複合インデックスの一部として設定することもある
    • クラスタインデックス側を参照しなくて良いのでパフォーマンスは向上する

駆動表

インデックス

出力フォーマットの変更

  • MySQL5.6から実行計画はテーブル形式の他にJSON形式でも出力できる
    • explain format=json select ~
  • テーブル形式より細かい粒度の情報が出力される
    • 特にコストが表示されるので実際に何にコストがかかっているかを分解してみることができる
  • それぞれのコストとかの説明とかあると良いよね

実行計画の改善

実行計画の出力イメージ

id select_type table partitions type possible_keys key key_len ref rows filtered Extra
SELECT 識別子。 SELECT 型 出力行のテーブル 一致するパーティション 結合型 選択可能なインデックス 実際に選択されたインデックス 選択されたキーの長さ インデックスと比較されるカラム 調査される行の見積もり テーブル条件によってフィルタ処理される行の割合 追加情報

type

  • レコードへのアクセスタイプ。上のほうが良い
type名 説明 意訳
system systemテーブルの話
const 一致するレコードが1つしかない 1件しかないので高速
eq_ref プライマリーキーもしくはユニークなキーでの結合 1対1でマッチするから高速
ref 上記以外の等価結合 NULLの可能性もあるが1対1でマッチするから高速
fulltext FULLTEXTインデックスが使われる よく知らん
ref_or_null IS NULLを使ったアクセス NULLアクセスはインデックス効かないけど最適化されているので若干マシ
index_merge 2つのキーをマージする 単体のインデックスアクセスよりは高速
unique_subquery サブクエリの最適化パターン。一意キーを選択した場合これになる サブクエリなので一般的な最適化されたアクセスタイプよりは遅い
index_subquery サブクエリの最適化パターン。一意でないキーを選択した場合これになる ユニークではない分、unique_subqueryより遅い
range 範囲指定をした場合でのアクセスタイプ 範囲指定している分アクセス対象が多いので遅め
index インデックスをフルスキャン インデックスをあまり上手く使えてないよ
ALL フルテーブルスキャン 基本的には避ける。ただしデータが小さいテーブルはこれになることもあるしそれが最適なこともある

extra

  • 自分も知らないしかいつまんで
  • 特にこの2つはメモリに乗り切らない可能性があり外部装置に吐き出された瞬間パフォーマンスが悪化する要因になる
  • 特に目くじらを立てて一掃する必要はないが、そもそも不要な処理をさせている可能性があるので使っていることを理解し問題ない判断をした上で使うことをおすすめする
    • ORマッパが吐き出すクエリを基本的には信用しない
extra 説明 意訳
Using filesort ソート処理でクイックソートが実行されている ソートは本当にDB上でする必要がありますか？
Using temporary 一時的にテーブルを作成している 結果が多すぎませんか？もっと小さくできませんか？

その他

  • 実行されたクエリ自体はGeneral Logに出力されるので本番環境とかでも確認は可能
  • ただし、実行時間までは出ない
  • 可能ならslow query logに出力させることで実行時間を知ることができる
    • slow_query_log = 1, long_query_time = 0 と設定すると実行されるクエリがすべて出力される(かしこー)
    • ただし再起動しないと反映されないので注意
  • https://nishinatoshiharu.com/mysql-slow-query-log/