特定の種類のクエリの最適化
特定の種類の最適化問題の軽いまとめ
以下における最適化の例はMySQLのバージョンに依存するものであり、将来のバージョンに当てはまらない可能性もあります。
また、サーバーがこれらの最適化の一部または全てを勝手に行うようにアップデートされる可能性もあります。
COUNTクエリの最適化
COUNT集計関数とそれを使用するクエリ最適化の方法は、おそらくMYSQLにおいて最も誤解されている問題の一つである。Webで検索してみれば思った以上に誤った情報が流れていることがわかるだろう。
最適化を行う前にCOUNT関数が何を行うのかを理解することが重要である。
※実践ハイパフォーマンスSQL第二版から引用
COUNTは2つの挙動をする関数です。
- 値をカウントする（値はNULLではない）
- 行をカウントする
値をカウント
列名や他の式を括弧で囲んで指定すると、COUNT関数はその式が値を持つ回数をカウントします。
行をカウント
括弧で囲まれている式が決してNULLにならないことをMySQLが知っている場合、COUNT関数は行を返します。
例: COUNT(*)
一般的な間違いの一つとして,行をカウントしたい場合に列名を括弧で囲んで指定することである。
結果として行の数を知りたい場合は、常にCOUNT(*)を使用する必要がある。これにより、自分の意図が明確に伝わり、パフォーマンスを低下させずに済む。
※実践ハイパフォーマンスSQL第二版から引用
実際の最適化の話
ハイパフォーマンスMySQL第二版ではMyISAMストレージエンジンの場合の最適化が記述されていますが、データの整合性とりたい場面での信頼性が低いので割愛します。
mysqlでの行カウントの最適化で議論が行われているので気になる方は見てみてください。
JOINクエリの最適化
コマンドに落とし込むと非常に長くなるのでポイントを2つ書きます。
ON句、USING句の列にインデックスがあることを確認する。
インデックスを追加する際には、結合の順序を考慮に入れるべきである。
具体例 ⇨ テーブルAとBを列Cで結合していて、クエリオプティマイザがB,Aの順にテーブルを結合する場合、テーブルBの列にインデックスをつける必要はありません。
使用されないインデックスはオーバーヘッドを増やすだけになります。
この場合は他の理由で必要にならない限り、インデックスを追加するのは結合順序二つ目のAのテーブルだけで十分です。GROUP BYまたはORDER BY式では1つのテーブルの列だけを参照させて、MySQLがその操作でインデックスを使用できるようにする。
公式リファレンス ⇨ LEFT JOIN および RIGHT JOIN の最適化
詳しく書かれている記事 ⇨ 実例で学ぶ、JOIN (NLJ) が遅くなる理屈と対処法
GROUP BYとDISTINCTの最適化
MySQLはGROUP BYクエリとDISTINCTクエリを同じように最適化し、必要であれば最適化プロセスにおいてそれらの間で変換を行います。どちらのクエリについてもインデックスが効果的に働き、それがこれらのクエリを最適化する最も重要な方法となっています。
MySQLがインデックスを使用できない場合、GROUP BYを使用する方法は、
一時テーブルを使用する方法
ファイルソートを使ってグループ化を実行する方法
の２種類に分かれます。
一応公式リファレンスも載せておきます。
GROUP BY句を満たすもっとも一般的な方法は、テーブル全体をスキャンし、各グループのすべての行が連続する新しい一時テーブルを作成することであり、それにより、この一時テーブルを使用してグループを見つけて、集約関数 (ある場合) を適用できます。場合によって、MySQL はインデックスアクセスを使用することで、それよりはるかに適切に実行し、一時テーブルの作成を回避できます。
GROUP BYにインデックスを使用するためのもっとも重要な前提条件は、すべてのGROUP BYカラムが同じインデックスから属性を参照することと、インデックスがそのキーを正しい順序で格納する (たとえば、これはBTREEインデックスで、HASHインデックスではありません) ことです。一時テーブルの使用をインデックスアクセスに置き換えられるかどうかは、クエリー内でインデックスのどの部分が使用されているか、その部分に指定された条件、および選択された集約関数にもよります。インデックスアクセスによって
GROUP BYクエリーを実行する方法は 2 つあります。最初の方法では、グループ化操作はすべての範囲述語 (ある場合) とともに適用されます。2 つめの方法では、まず範囲スキャンを実行し、次に結果タプルをグループ化します。
LIMITとOFFSETの最適化
LIMITやOFFSETが含まれたクエリはページングを行うシステムでよく使用され、ほぼ決まってORDER BYと共に使用されます。順序付けをサポートするインデックスがあれば効果的ですが、そうでない場合はサーバーが多くのファイルソートを実行する必要があります。
よくある問題として、オフセットの数が大きいとき、クエリがLIMIT 10000, 10のようなものである場合は10010行を生成して最初の10000行を捨てることになるので、非常にコストがかかります。
これの対処法として、
- オフセットを完全な行ではなく、カバリングインデックスで実行する
- 制限位置があらかじめ決められている場合、where句で範囲指定
が挙げられます。