MySQLの小手先チューニング

概要

MySQL(5.7)でクエリが重たいとき、インデックスの付け替え等を試してもイマイチ早くならなかった。
そんなときにクエリの形状を変更してパフォーマンスが上がらないかということで、試していることです。
コード解析はしていないので、推測で書いている部分は根拠ゼロです。

whereの順番

元々postgresを使っており、whereの最適化はオプティマイザがやってくれるものだと思っていました。
ところが先日、whereの順番を並び替えることでパフォーマンスが劇的に改善したので、少し調査してみました。

おそらくですが、インデックスが使われる句以外はwhereに記載した順で比較されます。
そのため、行数を多く減らせる句を前に、行数が少ないほど良い相関サブクエリは末尾に持っていくと、パフォーマンスが向上すると思います。
（実際に並び替えで速度が変わることをは確認しています。）

カバリングインデックスに基づいてクエリを分割する

データサイズが大きいクエリでは、SELECTに書くのはidなどのカバリングインデックスが効く範囲のみにし、別途本体行を取得することでパフォーマンスが向上することがあります。

例えば下記のようなクエリです

SELECT
  u.id,
  u.name,
  u.address,
  p.name
  ...（カラム一杯）
FROM users u
INNER JOIN user_billings ub ON ub.user_id = u.id
INNER JOIN products p ON p.id = ub.product_id
...（JOIN複数回）
WHERE
  u.age > 20
  AND u.address LIKE '東京都%'
...
LIMIT 10

カバリングインデックスを使わない場合、最終的な結果行以外のデータも取得し、余分な時間がかかることがあります。
（データサイズが大きい場合顕著です）

以下のように書き直すと早くなることがあります。

SELECT
  u.id
  ...
FROM users u
INNER JOIN user_billings ub ON ub.user_id = u.id
...
INNER JOIN (
  SELECT
    id -- ← 検索等を行うクエリでは、idのみなど、余分なデータをとらないようにする
  FROM users u2
  WHERE 
    ...
  LIMIT 10
) tmp ON tmp.id = u.id -- ← 事前に絞ったidと結合し、そちらで必要なデータを取得する

最後に

以上が小手先チューニング方法です。
（特に後者については、）常にパフォーマンスが向上するわけではないですが、割とお手軽に試せることですので、困ったときにお試しください。