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マッパーを追っていくと、
- まず
Modelの.objectsがQuerySetを生成する QuerySetがQueryを作成する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
とモデルが関係していますが、今回、なぜ半端な位置の Query に sql_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 のインスタンスを用いて関数の引き回しをすることにしました。
