SQL｜EXPLAIN（実行計画）を見ても速度改善できなかった時にとる手段

概要

やり方は単純で「クエリのパーツをコメントアウトし、実行して速度をみる」をコメントアウトするパーツを変えて繰り返すだけです。こうすることで何が遅いのかを明確にできるので、その遅いパーツに対して改善策を講じます。

実行計画を見るのと何が違うのかというと、実行計画でコストが大きいと表示されるパーツと、実際に遅いパーツが違うことがあるのでそれに気づくことができます。実行計画ではjoinのコストが大きいと表示されましたが、実際にはorder byが原因といったことがありました。

改善策はケースバイケースですが、遅いパーツが明確になることにより下の方法もとれるようになります。

• 特定のテーブルを結合したとき遅くなるのであれば、別のクエリで遅いテーブルのデータを取得後、プログラム上でjoinさせる。
• where句が遅いのであれば、条件式の書き方を変えてみる。または、サブクエリにしたり、テーブルの結合順を変えてみたりする。
• order byが遅いのであれば、データ取得後にプログラム上でソート処理をする。

具体例

下のサンプルSQLを例として説明していきます。

select
    顧客.顧客ID
    , 顧客.顧客名
    , 購入履歴.購入日
    , 購入履歴.商品ID 商品.商品名 
from
    顧客テーブル as 顧客 
    inner join 購入履歴テーブル as 購入履歴 
        on 顧客.顧客ID = 購入履歴.顧客ID 
    inner join 商品テーブル as 商品 
        on 購入履歴.商品ID = 商品.商品ID 
order by
    顧客.顧客ID ASC
limit 20
offset 0;

1. select句のコメントアウト

後々のコメントアウトで結合されるテーブルが変わってくるのでselect句はメインテーブルの項目を残し、他のテーブルの項目をコメントアウトします。

select
    顧客.顧客ID
    , 顧客.顧客名
    /* select句のコメントアウト
    , 購入履歴.購入日
    , 購入履歴.商品ID 商品.商品名 
    */
from
    顧客テーブル as 顧客 
    inner join 購入履歴テーブル as 購入履歴 
        on 顧客.顧客ID = 購入履歴.顧客ID 
    inner join 商品テーブル as 商品 
        on 購入履歴.商品ID = 商品.商品ID 
order by
    顧客.顧客ID ASC
limit 20
offset 0;

2. join句のコメントアウト

次にjoinするテーブルを一つずつコメントアウトして実行してみます。サンプルでは購入履歴テーブルをコメントアウトしていますが、次は購入履歴テーブルのコメントアウトを外して、商品テーブルをコメントアウトして実行しています。

select
    顧客.顧客ID
    , 顧客.顧客名
    /* select句のコメントアウト
    , 購入履歴.購入日
    , 購入履歴.商品ID 商品.商品名 
    */
from
    顧客テーブル as 顧客 
    /* join句のコメントアウト
    inner join 購入履歴テーブル as 購入履歴 
        on 顧客.顧客ID = 購入履歴.顧客ID 
    */
    inner join 商品テーブル as 商品 
        on 購入履歴.商品ID = 商品.商品ID 
order by
    顧客.顧客ID ASC
limit 20
offset 0;

3. その他の句のコメントアウト

他にorder byやoffsetなどを使っているクエリの場合はそのパーツごとにコメントアウトして実行してみます。その時は遅いパーツが明確となるようjoin句のコメントアウトは外しておきます。

select
    顧客.顧客ID
    , 顧客.顧客名
    /* select句のコメントアウト
    , 購入履歴.購入日
    , 購入履歴.商品ID 商品.商品名 
    */
from
    顧客テーブル as 顧客 
    inner join 購入履歴テーブル as 購入履歴 
        on 顧客.顧客ID = 購入履歴.顧客ID 
    inner join 商品テーブル as 商品 
        on 購入履歴.商品ID = 商品.商品ID 
/* その他の句のコメントアウト
order by
    顧客.顧客ID ASC
*/
limit 20
offset 0;

おわりに

説明は以上ですが、ポイントはとにかくコメントアウトするパーツを変えて実行してみることなので、いろいろなパーツをコメントアウトして実行してみてください。