補習ほぼ確

学びや好きなことをただ自由に書く

好きなこととMySQLの実行計画の話

考察 やってみる

この記事は 🎅GMOペパボエンジニア Advent Calendar 2022 の 13 日目の記事です。

はじめに

この1年くらい、新しく入ったチームメンバにOJTで MySQL の実行計画の話をすることが多いなぁというのを思い出し、数人に説明してたらある程度まとめてみようという気持ちになり今回は実行計画を元に改善した話を書きます。

なぜ「新しく入ったチームメンバに MySQL の実行計画の話をすることが多い」かというと、Webアプリケーションを作る上で例えば Rails なら MySQL, PostgreSQL, SQLite あたりを RDBMS として採用することが多いと思われますが、パフォーマンス向上のためにアプリケーションで実行している SQL のチューニングは不可欠でチューニングには各 RDB の実行計画を読み解くことが必要になってくるけれど、それを知る機会って（いざ自分がやる立場にならないと）無いよなぁと思い、明日から使える tips の一つになればいいな〜ということで話すネタにしています。

実際に自分自身 minne という大規模なユーザー数・出品数を抱えるサービスに入ったことでそのように考えるようになったし、現在処理上のバグ・オペミスで発生した不整合なデータを復旧させるだとかそういった対応も多いのでよりパフォーマンスを意識して対応するようになりました。

では以下 MySQL での改善事例をベースに話を進めます。※ MySQL 5.6 で確認しています。

実行計画について

MySQL は実行したい SQL を解釈し最適にテーブル操作する手順を作り出すオプティマイザという機能を搭載しています。オプティマイザがどう解釈するかによって同じ SQL でも実行時間は変わることがあります（オプティマイザの中身は統計情報やその時のテーブルの状態によって変化している）。

このオプティマイザがどのようにクエリを実行していくか、を示したものが実行計画です。EXPLAIN をSELECT, DELETE, INSERT, REPLACE , UPDATE ステートメントの前に追加して実行することで、ユーザーも実行計画を参照することができます。

MySQL :: MySQL 5.6 リファレンスマニュアル :: B.5.6 オプティマイザ関連の問題

オプティマイザは上であげた MySQL, PostgreSQL, SQLite いずれにも搭載されている機能で、実行計画参照は MySQL なら EXPLAIN 、PostgreSQL も EXPLAIN で, SQLite なら EXPLAIN QUERY PLAN をつける模様。

また MySQL ではオプティマイザトレースというものが 5.6 から導入されていて、オプティマイザが計算しているコストやインデックス使用の判断理由などを EXPLAIN 句より詳細に出力することができます。

（それは別で事例作って書いてみたい...統計情報も...）

実行計画を読んでみる

運営メンバーが使用する管理画面で、ある注文情報を集計するページが重すぎて場合によってはタイムアウトする、という問題が発生。 以下のようなクエリを実行していました。

その①

SELECT
  `tbl2`.`col1`
FROM `tbl2`
WHERE `tbl2`.`state` = 'deposited'
AND `tbl2`.`created_at` BETWEEN '2022-08-01 00:00:00' AND '2022-08-31 23:59:59'

その②

SELECT
  DATE(tbl2.created_at) AS date,
  COUNT(tbl1.id) AS count
FROM `tbl1`
INNER JOIN `tbl2` ON `tbl2`.`col1` = `tbl1`.`id`
WHERE tbl1.type = X
AND (tbl1.id IN (XXXXXX))
AND (tbl2.state = 'deposited')
AND (tbl2.created_at BETWEEN '2022-08-01 00:00:00' and '2022-08-31 23:59:59')
GROUP BY DATE(tbl2.created_at)

それぞれのテーブルは以下の情報を持っているとします。

  • tbl1: 注文情報のテーブル
  • tbl2: 注文に関する入金済み/キャンセル済みなどの状態の履歴を保存しているテーブル。tbl1 とは1対多の関係。

1つ目のクエリの実行計画

MySQL > EXPLAIN SELECT `tbl2`.`col1` FROM `tbl2` WHERE `tbl2`.`state` = 'deposited' AND `tbl2`.`created_at` BETWEEN '2022-08-01 00:00:00' AND '2022-08-31 23:59:59'\G

*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: tbl2
   partitions: NULL
         type: ALL
possible_keys: NULL
          key: NULL
      key_len: NULL
          ref: NULL
         rows: 553831
     filtered: 1.11
        Extra: Using where
1 row in set, 1 warning (0.006 sec)

検証環境にて実行計画をとってみた結果こうなりました。MySQLのリファレンスに詳細に記載されていますが、各出力結果について抜粋して見てみます。

MySQL :: MySQL 5.6 リファレンスマニュアル :: 8.8.2 EXPLAIN 出力フォーマット

type

結合方法を示しており、ざっくり以下のようなものがある。

  • const
    • PRIMARY KEY または UNIQUE キーを参照して検索、一番速い
  • range
    • インデックスを使用し範囲検索
  • index
    • フルインデックススキャン
    • インデックスのみのスキャンのためALL よりは速い
  • ALL
    • フルテーブルスキャン
    • テーブルからの行の取得を可能にするインデックスを追加することで、ALL を回避できます。 とある通りインデックスが貼られていないので一番遅い

possible_keys

行検索に使用するために選択できるインデックスを示す。カラムが NULL の場合は参照するインデックスがない。

key

MySQL （オプティマイザ）が実際に使用することを決定したキー (インデックス) を示す。key が NULL の場合、MySQL はクエリをより効率的に実行するために使用するインデックスを見つけられていない。

また、possible_keys は候補を挙げている（NOT NULL）だが key は NULL になる場合もあり、それは表全体のレコード数が少ないなどインデックスを使用せずフルスキャンした方が効率的であるとオプティマイザが判断したためと思われる。

ref

テーブルから行を選択するために、key に指定されたインデックスに対して比較されるカラムまたは定数を示す。

rows

MySQL がクエリ実行のために調査する必要があると考える行数（見積もった行数）を示す。

filtered

rows は調査される推定の行数を示すのに対し、こちらはテーブル条件によってフィルタ処理されるテーブル行の推定の割合を示す。

Extra

MySQL がクエリを解決する方法に関する追加情報。（一部のみ）

  • Using where
    • 頻繁に出力される。WHERE句に検索条件が指定されており、なおかつインデックスを見ただけではWHERE句の条件を全て適用することが出来ない場合に出力。
  • Using index
    • クエリがインデックスだけを用いて解決できることを示す。Covering Index （複合インデックスに対し単一インデックス/一つのインデックスだけで検索が完結するインデックス）を使用している場合などに表示される
    • Using where とも示されている場合、キー値のルックアップを実行するためにインデックスが使用されていることを意味する
  • Using filesort
    • filesort（クイックソート）でソートを行っていることを示す
    • 検索条件とソートの条件の両方に利用できるインデックスが見つからない
    • 検索とソートに適した複合インデックスを作成する必要があるが、条件に最適なインデックスとは別のインデックスが検索またはソートに使われてしまっている、という可能性も考えられる
  • Using temporary
    • JOIN の結果をソートしたり、DISTINCT による重複の排除を行う場合など、クエリの実行にテンポラリテーブル（CREATE TEMPORARY TABLEを使って一時テーブルを作成）が必要なことを示す

これらの出力結果と、1つ目のクエリの実行計画内容と照らし合わせてみると以下の問題点があります。

  • フルテーブルスキャンされており、インデックスの利用は無いので遅い🙅
  • Using where なのですでにあるインデックス + 更に WHERE 句の条件を参照しなければいけない状態 🙅
  • 条件によってフィルタ処理されるテーブル行の推定の割合がたったの 1% である🙅

改善してみる

上記の結果からこれらをやってみます。

  1. 既存の別の検索条件&インデックスを使うでは得たい結果が得られないので、tbl2 テーブルに state, created_at で複合インデックスを貼ってみる
  2. 更に1のインデックス（index_on_state_created_atとする）を USE INDEX 構文により強制使用させる ※ MySQL で possible_keys カラムに示されたインデックスを強制的に使用させる or 無視させるには、クエリで FORCE INDEX, USE INDEX, または IGNORE INDEX を使用する

実行計画は以下のようになりました。

MySQL > EXPLAIN SELECT `tbl2`.`col1` FROM `tbl2` USE INDEX (index_on_state_created_at) WHERE `tbl2`.`state` = 'deposited' AND `tbl2`.`created_at` BETWEEN '2022-08-01 00:00:00' AND '2022-08-31 23:59:59'\G
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: tbl2
   partitions: NULL
         type: range
possible_keys: index_on_state_created_at
          key: index_on_state_created_at
      key_len: 1027
          ref: NULL
         rows: 19
     filtered: 100.00
        Extra: Using index condition
1 row in set, 1 warning (0.003 sec)
  • type ALL -> range に変わり、key も index_on_state_created_at になったことからちゃんとインデックス利用して検索されてる🙆‍♀️
  • rows 553831 -> 19 で大きく減っており、行見積もりが減った = 実際に検査するレコード数もそれだけ少なくなったので高速化が期待できそう🙆‍♀️
  • filered 1.11 -> 100 で 100% がテーブル条件によってフィルタ処理される🙆‍♀️

本番環境でのパフォーマンスも大幅改善され、注文情報を集計するページが重すぎて場合によってはタイムアウトする という事態も発生しなくなりました。

2つ目のクエリも改善してみる

その②のクエリも改善してみます。

改善前

MySQL > EXPLAIN SELECT DATE(tbl2.created_at) AS date, COUNT(tbl1.id) AS count FROM `tbl1` INNER JOIN `tbl2` ON `tbl2`.`col1` = `tbl1`.`id` WHERE (tbl1.type = X) AND (tbl1.id IN (XXXXXX)) AND (tbl2.state = 'deposited') AND (tbl2.created_at BETWEEN '2022-08-01 00:00:00' and '2022-08-31 23:59:59') GROUP BY DATE(tbl2.created_at)\G
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: tbl1
   partitions: NULL
         type: range
possible_keys: PRIMARY,index_tbl1_on_type
          key: PRIMARY
      key_len: 4
          ref: NULL
         rows: 3
     filtered: 50.00
        Extra: Using where; Using temporary; Using filesort
*************************** 2. row ***************************
           id: 1
  select_type: SIMPLE
        table: tbl2
   partitions: NULL
         type: ref
possible_keys: index_tbl2_hoge,index_tbl2_fuga
          key: index_tbl2_hoge
      key_len: 4
          ref: tbl1.id
         rows: 1
     filtered: 5.00
        Extra: Using index condition; Using where
2 rows in set, 1 warning (0.004 sec)

tbl1, tbl2 テーブルそれぞれの結果が出力されているが、ともに id: 1 で select_typeは SIMPLE になっており、これは複数テーブルであっても1回の処理でデータを取得する実行計画であることを意味します。 （select_type は JOIN 含む単純な SELECT では SIMPLE だが、UNION やサブクエリ使用時はそれに応じて変わる）

実行計画からこれらのことがわかります。

  • 両方、利用可能なインデックス（possible_keys）の候補が複数ある
  • Using filesort があり、検索条件とソートの条件の両方に利用できるインデックスが見つからない
  • 2個目は特に filered （条件によってフィルタされる行の推定割合）が低い

改善後

こちらは以下をやってみました。

  1. WHERE句には以下の条件を指定している。オプティマイザが最適と解釈した順番で実行されるのでWHERE句の順番によって速度には影響しない。と解釈しているが、tbl1.type = X での絞り込みによりある程度取得するデータ量を少なくできると見込んでいるので tbl1 テーブルに type, idc で複合インデックスを貼ってみる

    WHERE tbl1.type = X AND (tbl1.id IN (XXXXXX)) AND (tbl2.state = 'deposited') AND (tbl2.created_at BETWEEN '2022-08-01 00:00:00' and '2022-08-31 23:59:59')

  2. これも更に1のインデックス（index_on_type_id とする）を USE INDEX 構文により強制使用させる

結果はこちら

MySQL > EXPLAIN SELECT DATE(tbl2.created_at) AS date, COUNT(tbl1.id) AS count FROM `tbl1` USE INDEX (index_on_type_id) INNER JOIN `tbl2` ON `tbl2`.`col1` = `tbl1`.`id` WHERE (tbl1.type = X) AND (tbl1.id IN (XXXXXX)) AND (tbl2.state = 'deposited') AND (tbl2.created_at BETWEEN '2022-08-01 00:00:00' and '2022-08-31 23:59:59.999999') GROUP BY DATE(tbl2.created_at)\G
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: tbl1
   partitions: NULL
         type: range
possible_keys: index_on_type_id
          key: index_on_type_id
      key_len: 8
          ref: NULL
         rows: 3
     filtered: 100.00
        Extra: Using index condition; Using temporary; Using filesort
*************************** 2. row ***************************
           id: 1
  select_type: SIMPLE
        table: tbl2
   partitions: NULL
         type: ref
possible_keys: index_tbl2_hoge,index_tbl2_fuga,index_on_state_created_at
          key: index_tbl2_hoge
      key_len: 4
          ref: tbl1.id
         rows: 1
     filtered: 5.00
        Extra: Using index condition; Using where
2 rows in set, 1 warning (0.003 sec)
  • 1個目の方の key が PRIMARY -> index_on_type_id になったが、filtered は 50 -> 100 になった🙆‍♀️
  • 1個目の Using filesort が変わってないが、これは JOIN した結果に対してソートするときは常にfilesortである、というのが調べていて判明。そのため filesort は消せなかったのだが、Using index condition も含まれているので作成したインデックスはちゃんと使えている🙆‍♀️

2個目に関しては正直ほぼ差がないけれど、それでも改善前よりは高速化が見込めると検証環境で判断したので本番環境にも適用し、ある程度は速くなったかな〜という所感でした。

好きなことの話

改善できた事例を挙げてみたものの、数万〜数十万単位になってくるレコード数（データ量）で実行計画からクエリチューニングを行いﾊﾞｰｰﾝと爆速になったぜ！という成果を出すのはなかなかに難しいです。ちまちま手を入れてちまちま改善しています。

私は現在 minne というサービスで Customer Reliability Engineering(CRE) という活動をやっています。社のテックブログ「minne CREを紹介します」でも触れていますが、「カスタマーサポートの力の向上・拡大」も重要なミッションのひとつであり、サポートチームに成果を最大限発揮してもらうために顧客管理・注文情報管理システムの性能向上は不可欠なので、意欲的に上記のような改善をやるようになりました。

この記事タイトルに立ち返りますが、自分がエンジニアをしてて好きなことってなんだろうとふと考えてみて、私は「RDBMSのデータに触れる」が好きなことの一つです。こういう改善やるのも単純にデータに触れて改善するのが好きだからというのもあります。

どんな大規模なサービスでも根っこのデータは表形式でデータを管理しているって、こんなにシンプルな思想で実現できているなんて RDB って本当にすごい代物だよなぁと年々愛着が高まって(?)います。（NoSQL も使っていますが）

どうデータを持たせるかテーブル設計するのも好きにクエリ書いて取りたいデータを取れるのもマジで楽しいし、大規模なサービスに携わっているからこそ感じられるようになったことですがデータの傾向一つで今は何が起きているかを察知できるのも楽しいです。人の行動で生まれるデータって正直で尊い。宝。精通度はともかく、RDBを触るのが楽しいからこそサーバサイドのエンジニアをやっているのかもしれません。

ということで、アドベントカレンダーだし実行計画の他にもパーソナリティがわかる何かがあると良いかな？と思いつつ、冒頭で離脱されないように好きなこと語りは最後に入れました。来年ももっと開発して楽しい瞬間を増やしたい。読んでいただきありがとうございました！

（リンク集にペパボの過去数年分のアドベントカレンダーがありますが、地味に 2018 年の入社から皆勤賞でした。5回...5年.....め...だと....？？？？）

🎄GMOペパボエンジニア Advent Calendar 2022 もありますのでそちらもぜひ！