MySQLのSQLクエリチューニングの要所を掴む勉強会を開催しました！

こんにちは！DBREの福間（fkm_y）です。先月、弊社でデータベースの技術顧問をして頂いてる三谷（mita2）さんに開発本部向けの「MySQL SQLチューニング」勉強会を実施していただきました。

今回はMySQLの得意不得意なことの説明やSQLチューニングの流れ、具体的な事例を元にした対応例、また最近話題のHTAPな製品も紹介していただきとても参考になったのでポイントをおさえてレポートをお伝えします！

開催背景

弊社では三谷さんによるデータベース勉強会を定期的に開催しています。数年前にも同じテーマで勉強会を開催していたのですが、当時に比べてMySQLのバージョンが変わっていることや開発本部メンバーも大きく変わっていたことから増強版のMySQL SQLチューニング勉強会を開催することになりました。

本編

MySQL の得意なこと、苦手なこと

始めにワークロードにはOLTP（Online Transaction Processing）とOLAP（Online Analytical Processing）の2種類があります。OLTPは少数行/大量カラムに対して高速なレスポンスが期待できるが、OLAPは大量行/少数カラムに対してOLTPほど高速なレスポンスは期待できない特性があります。

また最近はOLTPとOLAPの両方に対応するデータベースとして Hybrid Transaction Analytical Processing（HTAP）がトレンドであり、HTAPな製品としてMySQL HeatWave、TiDBなどがあります。HTAPはエンジンを自動的に使い分けることで両対応を実現しています。

MySQLはOLTPが得意なDBです。集計処理は不得意なので、集計処理はBigQueryなどのOLAPと併用したほうが良いです。例えば、BigQueryなどでログを集計した結果をMySQLへ保存してからMySQLで集計させます。

データベースのチューニング手段と特徴

DBのチューニングにはSQLチューニングとパラメーターチューニングに分けることが出来ます。パラメーターチューニングは効果がDB全体に及ぶものの、最近はパブリッククラウドを使っていることが多くパラメーターが基盤に最適化されたものが提供されているため、追加の調整を実施しても効果が小さいことが多いです。

SQLチューニングの流れ

チューニングのゴールは「性能要件を満たすこと」を目標に掲げると良いです。よくある間違いとして、無駄をゼロにすることを目標に掲げることがありますが非常に手間が掛かりスムーズにチューニングが進まなくなってしまいます。

SQLチューニングの流れは、対象のSQLを決め、実行計画を確認し、改善する流れになります。アンドパッドの開発組織で使用しているツールを使った対象SQLを決める手法として Datadog APM や AWS Performance Insight を使った手法を紹介いただきました。

SQLチューニングの余地を事前に予測するための手法を紹介いただきました。 SQLチューニングの多くは、不要な行の読み取りを削減することによって達成しているためどれくらい削減する余地があるかを知ることが出来れば速くなるかを知ることができます。

Rows_examined と Rows_sent の値を比較して読み取り行数と結果セットの行数の差分が大き過ぎないかを確認してチューニング余地があるか判断できます。

MySQL8.0からはEXPLAIN FORMAT=TREE と EXPLAIN ANALYZEがサポートされるようになりました。 EXPLAIN FORMAT=TREEはツリー形式で表現されるので親子関係がわかりやすくなりました。 EXPLAIN ANALYZEは実際にクエリを実行するため、実際に時間が掛っている処理の特定が可能になります。

インデックス

インデックスのツリー構造や高速にアクセス可能になる理由の説明を始め、インデックスにカーディナリティの高いカラムを選ぶ理由、複合インデックスのツリー構造などを説明いただきました。

また書籍 SQLアンチパターンで紹介されているアンチパターン「インデックスショットガン」のように闇雲にインデックスを貼った場合のデメリットも紹介いただきました。

SQLチューニング例

以下6パターンのチューニング方法の事例を紹介いただきました。

テーブルフルスキャン
インデックスフルスキャンとカバーリングインデックス
ソート
JOIN
大量INSERT
大量DELETE

この記事では「インデックスフルスキャンとカバーリングインデックス」「ソート」のパターンをピックアップします。

インデックスフルスキャンとカバーリングインデックス

インデックスフルスキャンとは、rowsが大きく、typeがindex（ possible_keysが NULL ですが、 key が設定されているもの）のものがこのパターンに該当します。type が index となっているので一見、効率的な処理と勘違いされますがインデックスをフルスキャンしているので非効率な処理になります。

カバーリングインデックスとは、Extra が Using indexとなっているものです。読取りがインデックスで完結しており、テーブルにはアクセスしないため高速になります。

参照するカラムのみで構成するインデックスを作成することによってテーブルの読取りを減らすテクニックになります。ただし、多用するとインデックスが大きくなり過ぎてしまうので多用すべきではない手法になります。

ソート

Extra が Using filesortとなっているものが該当します。「file」と付いているが、意識する必要はなくソート処理が行われていると認識すればよいです。三谷さんの経験上、CPU負荷の原因になっているケースが多いとのことです。

ソートしているカラムに対してインデックスを貼ることによって、インデックスツリーを辿ることによってソート済みデータが取得できるので解消されます。

ORDER BY狙いのインデックスとLIMIT句を組み合わせると最適化の効果が高くなります。これはソートの順番にデータを取得していきWHERE句の条件を見た椅子レコードが 10件見つかった時点で処理を完了させることが出来るため高速になりやすいです。

ただし、 LIMIT句が深すぎる場合やWHERE条件にマッチするレコードがなかなか見つからない場合は「LIMIT句とORDER BYカラムのインデックス」による効果があまり出ません。

ソートを最適化するのが必ずしもベストとは限りません。インデックスは原則１テーブルについて１つしか利用されないため、全体のうちWHERE条件を満たすレコード数の比率やLIMIT句による打ち切りが期待できるかなどの要素を元にWHERE 狙いのインデックスを狙うかORDER BY狙いのインデックスを狙うか選択する必要があります。

MySQL8.0からは降順インデックスもサポートされました。データの一覧画面などで作成日の降順に並べて表示したいケースなどで有用そうですね。

まとめ

ワークロードごとの特徴から始まり、用途に適したDBMSを使うこと、インデックス作成時の勘所であるカーディナリティやカラム順などの基礎知識の説明をしていただきました。また具体例を元に問題クエリの見つけ方、実行計画の確認観点、クエリの改善方法と実践的な内容でした。

私は特にSQLチューニングの余地を事前に予測するために Rows_examined と Rows_sent の比較する手法は知らなかったので参考になりました。以前まではRows_examined の値を確認してチューニング余地がありそうか見ていたのですが、今後はより予測精度を高めてチューニングに向き合えそうな気がします。