Ryota Kondo

Ryota Kondo

2023/12/23

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

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;

おわりに

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

関連タグの記事

Ryota Kondo
Ryota Kondo

システムエンジニア・プログラマー|このブログサイトの運営もしており、思いついたことをまとめて記事を書いています💡