More than 3 years have passed since last update.

MySQL SlowQueryの対処法

Posted at 2020-01-29

概要

MySQLでSQLの実行が遅い場合の原因と対処法についてメモ

SlowQueryの改善の前に、まずはMySQLがどのような順にSQLを評価し実行するかを
確認しておく必要があります。MySQLでは、

FROM → JOIN
→ WHERE → GROUP BY → HAVING → SELECT → DISTINCT
→ ORDER BY → LIMIT

上記の順に評価&実行しデータを取得しますが、大まかに考えると、

のような流れでデータを取得します。
上記の流れを意識して、SlowQueryの対処方法について説明していきたいと思います。

基本的にHAVING句はWHERE句に書き換えが可能です。
また、HAVING句はMySQLがメモリ上で1レコードずつ条件判定をしてデータ集約して対象を絞り込むため、データ取得にかかる時間はテーブル数が大きくなるほどWHERE句で絞り込むよりも遅くなる傾向があります。

SQLの評価順序で、WHERE句よりもJOIN句の方が先に実行されるため、
JOIN時の条件を必要なデータのみに絞る(ex. WHEREで絞る前にJOINで絞る)ことで、
WHERE時の計算コストを下げることができます。

EXPLAIN文を用いてQueryの実行計画を調べると、

Using Index Condition
- 指定したソート順(=order句)+検索条件(=where句)を作成済みのINDEXを使ってデータの取得ができる
Using Temporary
- 指定した検索条件でデータを取得しようとした際にJOINするデータがメモリに乗らない
Using Filesort
- 指定したソート順+検索条件でデータがINDEXされていない(=検索条件ではデータがINDEXされているが、ソート順ではINDEXされていない)

のように表示される場合があります。SlowQueryと考えられるQueryは基本的に
Using Temporary もしくは Using Filesort と表示されます。
以下でそれぞれ解説していきます。

EXPLAIN文を用いてQueryの実行計画を調べると Using Temporary と表示される場合がありますが、これは取得するデータが設定したメモリのバッファーサイズに収まらないときに表示されます。

JOIN句を実行してデータを取得する際に、取得するデータが設定したメモリのバッファーサイズに乗らなくなるとMySQLは一時テーブルを作成してJOINEDテーブルを作成しますが、このようなデータに対してEXPLAINを実行すると Using Temporary と表示されます。

Using Temporary が発生してしまうと、MySQLでは一時テーブルにデータを展開した後、quicksortを実行してデータのソートを行い結果を返すため、実行されるSQLは非常に遅くなります。

上記の場合はメモリのバッファーサイズを変えることで解決するので、

をそれぞれ必要なサイズに変更してみて下さい。

EXPLAIN文を用いてクエリの実行計画を調べると Using Filesort と表示される場合がありますが、このように表示されるのは、 検索条件とソートの条件の両方に利用できるINDEXが作成されていない ことが原因となります。

MySQLの評価順序のうち、 INDEXが利用できるのはWHERE句とORDER句のみ です。
したがって、Using Filesort を解消しINDEXのみで検索&ソートを実行してデータを取得するためには検索とソートに適した複合INDEXを作成する必要があります。

複合INDEXを作成する際は、基本的にはカーディナリティが大きいものから順にカラムを指定して複合INDEXを作成するほうがMySQLの探索コストが小さくなり、検索が早くなります。

また、条件に合致する複合INDEXを作成しても Using Filesort が表示され続けてしまう場合があります。このような場合、 条件に最適なINDEXとは別のINDEXが検索またはソートに使われてしまっている 可能性が考えられます。

MySQLのQueryでは 1つのQueryに使えるINDEXは1つまで であるため、
不要なINDEXは基本的に消しておくことをおすすめします。

システムを運用時に起こりそうなSlowQueryのパターンをいくつかあげて、
その対処法を簡単にまとめてみました。