Django の ORマッパーで生成されたSQLを実行前に置換し、JOIN時のインデックスを強制する

Django には、便利な ORマッパーが搭載されており、SQLを一切書かずともRDBの操作が行えます。

今回、SQL実行時に思ったようにインデックスが使用されず、パフォーマンスが出ない問題がありました。

これは、生SQLを実行した場合も同様にインデックスが使われなかったため、Django の問題ではなく、実行計画が最適に作成されなかったというSQL上の問題(というか仕様?)です。

SQL内にインデックスヒントを強引に書き込むことでインデックスを強制した所、正常なパフォーマンスとすることができたので、その方法を書きます。

MySQLの強制インデックス

今回、SQLを発行したページは Django の Admin 内で、子モデルの Admin ページから、親モデルを select_related して、リスト表示するというコードとなっていました。

@admin.register(models.Child)
class ChildAdmin(admin.ModelAdmin):
    ...
list_select_related = (
    'parent',
)

DB の種類は MySQL です。発行される SQL は以下のようになります。

SELECT *
FROM child
INNER JOIN parent
ON child.title_id = parent.title_id
ORDER BY child.id
LIMIT 100

※ 実際には SELECT 句の中は各フィールドが明示的に書き出されます

実際にこのSQLをSQLコンソールから実行してみると、50秒ほどかかりました。

(child は約100万レコード、parent は約40万レコードほどの分量です。)

予想より多くの時間がかかっており、テーブルフルスキャンが発生している印象です。

EXPLAIN してみると、possible_keys に title_id というキーを認識しているものの、実際にはキーが使用されると判断されない状況でした。

EXPLAIN 結果

※ 実際のテーブル名は child, parent ではないため、画像を加工しています。

ためしに、認識して使用されていないキー title_id の使用を強制し、SQLを発行してみます。

SELECT *
FROM child
  INNER JOIN parent
  FORCE INDEX (title_id)
  ON child.title_id = parent.title_id
ORDER BY child.id
LIMIT 100

FORCE INDEX (title_id) を追加しています。

すると、50ミリ秒 ほどでSQLを完了することができました。今回はインデックスが活用されています。

EXPLAIN で見ても、key が使われています。

解決へのアプローチ

インデックスが使われない問題は MySQL のサーバ側にあります。インデックスの再構築などを行うことで改善する可能性はあります。

ただし、今回は MySQL サーバへの変更などは行わず、コード上の変更により、 インデックス強制の SQL をなんとか発行する方針としました。

Django の SQL 生成のフック

Django は、搭載されている OR マッパーで SQL を組み上げます。その中には、テーブルジョインする際にインデックスを指定するオプションはありません

そこで、生成されたSQL を発行前にフックし、何らかの処理(置換)を行うことで、インデックス強制をさせることを考えました。

Python は、変数のほかクラス・関数などあらゆるものがファーストクラスオブジェクトであり、使用時に上書きや代入することができます。

特に、コア機能のメソッドを外部から上書きすることで動作を変更させる手法はパッチ、もしくはモンキーパッチと言われ、フレームワークやライブラリの主要機能を一時的に書き換えることも容易に行えます。

今回は、Djangoで生成されるSQL部分をパッチすることで、SQLの置換が行えそうです。

動作の安定性を失ったり(特にスレッド安全性など)、セキュリティ上の問題も生みやすく、動作を追うことが困難になるため、安直に行うべきものではありませんが、課題の解決やビジネス成長につながるのであれば選択肢として考えることはできます。Pythonは、速度など多くの犠牲のもと、柔軟な拡張性を獲得していると言えます。

SQL生成の箇所

Django のORマッパーを追っていくと、

  1. まず Model.objectsQuerySet を生成する
  2. QuerySetQuery を作成する
  3. Query が、 SQLCompier (django.db.models.sql.compiler.SQLCompiler) を作り、 as_sql メソッドを使ってSQL を組み上げる

という動作をします。

Model > QuerySet > Query > SQLCompiler

的な感じです。(記号はイメージです。has a 関係のようなものを表しているつもりですが、厳密ではありません)

SQLCompiler.as_sql を読んでみると、200行ほどからなるそこそこ分量のあるメソッドで、その中には JOIN 時にインデックス強制をできそうなフックポイントなどは無いため、簡単に処理は追加できません。

今回は、この as_sql を一旦どこかに退避し、新しくSQL置換機能をもった as_sql を勝手に作って SQLCompler の as_sql として置き換え、その勝手に作った as_sql の中で、もともとの as_sql を呼ぶことで、機能の拡張を行うものとします。

パッチのコード

具体的なコードとしては

def patch_sql_transformer():
    """
    queryset.query に sql_transformer というメソッドを付与すると、
    生成済み SQL をその sql_transformer で処理(置換など)して返す
    """
    from django.db.models.sql.compiler import SQLCompiler
    o_as_sql = SQLCompiler.as_sql

    def _decorate_as_sql(self, *args, **kwargs):
        sql, params = o_as_sql(self, *args, **kwargs)
        transformer = getattr(self.query, 'sql_transformer', None)
        if transformer:
            sql = transformer(sql)
        return sql, params

    SQLCompiler.as_sql = _decorate_as_sql

このようなコードとしました。このコードを、アプリの起動時のどこかで1回だけ実行することで、as_sql の機能が書き換わります。

機能としては、クエリセット内の Query のインスタンスに、 sql_transformer という関数がついていれば(つけていれば)、生成された SQL をその関数を通してから実行します。

今回、sql_transformer として実行したい関数は下記のものです。

def _transform_sql(sql):
    return sql.replace(
    'INNER JOIN `parent` ON',
        'INNER JOIN `parent` FORCE INDEX (title_id) ON')

近距離パワー型のアプローチですが、INNER JOIN 句を文字列置換して、強制的にインデックスを使わせます。

今回は Admin ページ内だけでこれを適用したかったため、このメソッドを ModelAdmin の get_queryset 内で作り、Query に仕込みます。

@admin.register(models.Child)
class ChildAdmin(admin.ModelAdmin):
   ...
    def get_queryset(self, request):
        qs = super().get_queryset(request)

        def _transform_sql(sql):
            return sql.replace(
                'INNER JOIN `parent` ON',
                'INNER JOIN `parent` FORCE INDEX (title_id) ON')
        qs.query.sql_transformer = _transform_sql
        return qs
 
    list_select_related = (
        'parent',
    )
...

これにより、インデックスが強制指定で使われるようになり、速度を改善させることができました。

なぜ Query にメソッドを作るのか?

SQL の生成時は

Model > QuerySet > Query > SQLCompiler

とモデルが関係していますが、今回、なぜ半端な位置の Querysql_transformer を生やす、というデザインにしたかというと、それ以外に適した場所がなかったからです。

クエリセットを生成中の、QuerySet や、 SQLCompiler は、クエリセットをチェーンする時

例えば

qs = MyModel.objects.filter(active=True)
qs = qs.filter(item_type=xxx)
qs = qs.order_by('-id')
qs = qs.limit(100)

のようなコードの場合 (実際、Django の ModelAdmin の中では上記相当のコードが発行されています)、毎回 queryset インスタンスが作り直されます。

なので、queryset インスタンスになにかフック用の関数を付与したとしても、SQLCompiler の SQL 構築まではインスタンスが無くなり、メソッドも失われてしまいます。同様に、SQLCompiler になにか関数を作っても、クエリセットが作り直される時に消えます。

では、その中間にある Query はというと、QuerySet のコンストラクト引数として常に引き渡され、場合によっては deepcopy され、インスタンスのプロパティや変数などは大元の QuerySet がすべて消えるまで生存しており、かつ SQLCompiler の第一引数でもあるためアクセスが容易ですので、今回は Query のインスタンスを用いて関数の引き回しをすることにしました。

Currently unrated

コメント

コメントを投稿
コメントするには TORICO-ID にログインしてください。
ログイン コメント利用規約

Recent Tweets

  • ytyng

    ytyng @ytyng

    あんまりPC詳しくない人とか普通に踏んじゃうと思うので、キヤノン製品は控えることにする
    2 weeks, 6 days ago

  • ytyng

    ytyng @ytyng

    ダメサイトっぽい https://t.co/GBlGq9T3Oe 公式はこちらでした https://t.co/sPvhEowLdt
    2 weeks, 6 days ago

  • ytyng

    ytyng @ytyng

    whoisも怪しすぎるんだけど
    2 weeks, 6 days ago

  • ytyng

    ytyng @ytyng

    ドライバダウンロードページに広告つけるものだろうか。
    2 weeks, 6 days ago

  • ytyng

    ytyng @ytyng

    キャノンのドライバダウンロードページに出てる広告がいかがわしすぎてドライバダウンロードできない。しかも広告JSがドライバダウンロードリンクを乗っ取ってるっぽいんだけど。大丈夫かコレ https://t.co/FRZD81Th9R… https://t.co/zc7VhJoEbM
    2 weeks, 6 days ago