インターンでSQLパフォーマンス改善に取り組みました

インターンでSQLパフォーマンス改善に取り組みました

こんにちは、インターン生の加藤です。 インターンでは レストランボード の SQL 改善を担当しました。

本記事ではインターン中に遭遇したパフォーマンスが悪いクエリとその改善方法について紹介します。

なぜか遅いクエリ

レストランボードでは Oracle Database を利用しているため、データベースの監視には Oracle Enterprise Manager (OEM) を利用します。 なかでも今回のインターンでは OEM の監視された SQL 実行という機能を多用していました。 監視された SQL 実行は次のような画面で直近に実行されたクエリのうち、 Oracle が悪いと判断したクエリが表示されています。

execution plan

監視された SQL 実行では次のようなクエリが頻繁に確認されました。

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
SELECT
    A.ID,
    MAX(A.SOME_DATE) AS SOME_DATE
FROM
    (
        SELECT  X.ID,
                Z.SOME_DATE
        FROM    X
                INNER JOIN  Y
                ON          X.ID = Y.X_ID
                INNER JOIN  Z
                ON          Y.X_ID = Z.X_ID
                        AND Y.ID   = Z.Y_ID
        WHERE   X.TAG            = :1
                AND Y.ANSWER     = 42
                AND Z.Z_COLUMN1  = :2
                AND Z.Z_COLUMN2  = :3
                AND Z.SOME_DATE <= :4
                AND Z.SOME_DATE >= :5
    )
    A
GROUP BY A.ID

※ 実際のSQLは長大でしたが、本記事の内容を損なわない程度に簡略化しテーブル名カラム名をダミーにしております

原因の特定

SQL のパフォーマンスチューニングを行う場合、まずは RDBMS がどのようなクエリ最適化を行っているのかを知る必要があります。

そこで使われるのが実行計画です。問題となっているクエリの実行計画は次のようなものでした。

execution plan

この実行計画から INDEX SKIP SCAN という部分で、大量の IO アクセスを行っており時間が掛かっていることが確認できます。しかし、インデックスが使われていそうな雰囲気があるのになぜ時間が掛かってしまうのでしょうか。

なぜ時間が掛かっているのかを理解するためには、 INDEX SKIP SCAN がどのような処理であるかを知る必要があります。

INDEX SKIP SCAN とは

INDEX SKIP SCAN は複合インデックスを利用した絞り込みを行う際に起こり得る事象です。

具体的には、複合インデックスの 1 列目に対する条件指定がなく、2 列目以降の条件指定がある場合に使用される可能性があります。 ただし、 INDEX SKIP SCAN は、複合インデックスの 1 列目を絞り込み条件として使わないため B ツリーのリーフブロックを全て辿る必要があり、 INDEX RANGE SCAN と比べてパフォーマンスが劣化します。

参考: Indexes and Index-Organized Tables

なぜ INDEX SKIP SCAN したのか

なぜ INDEX SKIP SCAN が行われていたのかを調べるために、まずは INDEX SKIP SCAN 実行時に選択されたインデックスの構造を確認します。

列名 データ型 ソート順
SOME_DATE DATE ASC
Z_COLUMN1 CHAR ASC
Z_COLUMN2 CHAR ASC

インデックスの構造とクエリを見比べると、複合インデックスを構成する全てのカラムがクエリ中の条件指定に登場していることが確認できます。 それにも関わらず、INDEX SKIP SCAN となってしまうのはなぜでしょうか。

これを調査するために、INDEX SKIP SCAN 実行時のアクセス述語とフィルタ述語を調べてみます。 アクセス述語はインデックスをスキャンする範囲を定義するもので、フィルタ述語は取得したデータから対象データを抜粋する際のルールになります。 そのため、アクセス述語による絞り込みの方が高速であり、可能な限りアクセス述語による絞り込みを行うことがパフォーマンス向上に繋がります。

意図通りにインデックスが効いていれば、アクセス述語に複合インデックスを構成するカラムが全て登場するはずです。 INDEX SKIP SCAN 実行時の述語情報は次のようになっていました。

種別 述語情報
アクセス述語 "Z"."Z_COLUMN1"=:2 AND "Z"."Z_COLUMN2"=:3
フィルタ述語 (INTERNAL_FUNCTION("Z"."SOME_DATE")>=:5 AND INTERNAL_FUNCTION("Z"."SOME_DATE")<=:4 AND "Z"."Z_COLUMN1"=:2 AND "Z"."Z_COLUMN2"=:3)

アクセス述語から SOME_DATE がインデックススキャン時に使用されていないことが読み取れます。 さらにフィルタ述語に着目すると、SOME_DATEINTERNAL_FUNCTION という関数に覆われています。

INTERNAL_FUNCTION とは型変換を行う関数です。SOME_DATE は DATE 型として定義されたカラムですが、その比較対象が異なる型であるために、INTERNAL_FUNCTION による暗黙的な型変換が発生したと予想されます。

なぜ型変換が発生したのか

SOME_DATE カラムとの比較対象はバインドパラメータで渡されています。 そこで、実際にバインドパラメータでどのような情報が渡されているのか調べてみます。

bind parameter

すると比較対象は TIMESTAMP 型で渡されていることが分かります。DATE 型と TIMESTAMP 型の比較が行われていたため、INTERNAL_FUNCTIONが発生していたことが分かりました。

ただしまだ根本的な問題の特定には至っておりません。なぜバインドパラメータが TIMESTAMP 型として渡されたのでしょう。 レストランボードは Java で実装されており、このクエリは JDBC ドライバを利用して実行時にバインドパラメータを渡すことで実行されます。 このとき、 Java のデータ型と SQL のデータ型との間で型のマッピング問題が起こっていたのです。

詳しく確認したところ、 Java からは SOME_DATE に対するバインドパラメータは java.util.Date 型として渡されていました。 どうやらこの型が JDBC ドライバを経由して、 TIMESTAMP 型として SQL に渡されてきたようです。

改善方法

改善のアプローチは2つ考えられます。

  1. Java のデータ型を、 SQL に DATE 型として渡される型に修正する
  2. SQLを修正して、渡されたパラメータを DATE 型にキャストする

今回は 2. のアプローチを取ることを選択しました。 こちらを選択したのは 1. のアプローチと比較して、修正の動作確認を行う際の手間が少ないことが主な理由です (さらには本番で動作している Java のバージョン、 Oracle のバージョン、 JDBC ドライバのバージョンを合わせた環境を再現するという難しさがあったという背景があります)。

暗黙の型変換を防ぐために、SQL 上で明示的に型変換を行います。 またSOME_DATEカラムに対して型変換を行ってしまうと、インデックスが使われなくなってしまうため、バインドパラメータに対して型変換を行うようにします。

具体的には以下のような修正になります。

1
2
3
4
5
6
                AND Z.Z_COLUMN2  = :3
-               AND Z.SOME_DATE <= :4
-               AND Z.SOME_DATE >= :5
+               AND Z.SOME_DATE <= CAST(:4 AS DATE)
+               AND Z.SOME_DATE >= CAST(:5 AS DATE)
    )

改善の見込み

この修正は月次のアップデートに組み込まれる予定なので、実際のデータに対してどれ程の改善があったのかはインターン期間中には分かりません。

しかし改善後のクエリの実行計画を本番環境で取得したところ、INDEX SKIP SCAN が解消され、全体の処理時間も向上していることが確認できており、 OEM の 監視された SQL 実行には上がってこないようになることが期待されます。

まとめ

以上のようなプロセスで、他のクエリに対しても原因特定、改善案の検討、改善の実施を行いました。 監視に上がっていた SQL 実行のうち 2/3 程度に対処し、半分程度の実行は監視に上がらないようになると見込んでいます。

普段の開発では、大量のデータを扱うことがなく、「そんなところでネックになるんだ」ということをたくさん経験できて面白かったです。 実行計画を見ることすら初めての自分でしたが、インターンを通じて圧倒的成長ができたと感じています。 インターンを通じて得たことを今後のキャリアに活かしていきたいと思います。

加藤 佐之輔

(飲食プロダクト開発グループ(インターン))

カメラ、お酒、dotfiles盆栽に入門中。

Tags

NEXT