新着記事

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 の .objects が QuerySet を生成する
2. QuerySet が Query を作成する
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

とモデルが関係していますが、今回、なぜ半端な位置の 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 のインスタンスを用いて関数の引き回しをすることにしました。

Google Analytics は、API経由で様々な数値を取得することができます。
今回は、API経由でサイトのアクティブユーザー数を取得して、Nuxtで作ったダッシュボード風JSアプリに表示してみます。

Google Analytics の APIから値を表示してウェブページに表示する場合、APIとの通信をサーバサイドで PHP や Python のプログラムで取得してウェブブラウザに表示時する方法と、ウェブブラウザ自体が直接 Google Analytics API にリクエストして表示する方法と、どちらの方法でも実現できます。

サーバサイドで行う場合、認証情報やシステムを隠蔽でき、またHTTPリクエストが無くともバックグラウンドで値を取得し続けることができるなど、大きな利点がありますが、常時起動するサーバを用意しておかないといけないため構築が若干手間です。

今回は、手間をかけずに実現したかったため、ブラウザと静的リソースのみで動作するものを作ります。
APIへの認証は、ブラウザにログインしている Google ユーザーが行います。

認証のしくみ

Googleの提供している APIは、OAuth2 の認証・認可が必要となります。

アプリのページを開くと、Google のログインページを表示して認証を求め、認証されるとダッシュボードアプリの指定したコールバックURL にリダイレクトされるようにします。

リダイレクトされると、コールバックURL末尾の # (ハッシュ, フラグメント) の後にアクセストークンが含まれた状態になりますので、それを nuxt 内の JSでパースし、ブラウザのメモリ(変数)に格納します。

注意点として、認証情報(アクセストークン)がブラウザの変数に格納されるため、XSSが発生した際にアクセストークンが漏洩するリスクがあります。これはJSで認証情報を扱う以上避けられません。

XSSによる認証情報漏洩を防ぐには、GoogleとのAPIリクエストをサーバサイドで行い、サーバとの認証は httpOnly属性のついたセッションクッキーで行うべきですが、今回は、静的リソースなのでXSSは通常発生しないという前提の元、リスクを受容した上でクライアントサイドJSで機能を提供するものとします。

コールバックURLから取得したアクセストークンを認証ヘッダに含めて、ブラウザから Google Analytics にAPIリクエストをすることで、アクティブユーザー数などを取得できます。

アクセストークンは、localStorage に保存すると、セッション終了後も保持されるため漏洩の危険性が上がります。そのため変数として保存するだけにとどめます。ただし、OAuth2の state パラメータは、ブラウザセッションが変更されても維持する必要があるため、localStorage に一時的に保存します。

アプリのページを開くたびに、OAuth2のアクセストークンをリクエストし続けるとすると、毎回 Google のアカウント選択ページが出て面倒なように感じられるかもしれませんが、実際に使ってみるとそんなことはなく、一度ブラウザと Google の認証セッションクッキーが作られればあとはリダイレクトしか発生せず、1秒程度で自動的に認証が終わりアクセストークンが取得済みの状態になりますので、使用感として悪くはありません。

 試しにAPIにリクエストする

https://developers.google.com/analytics/devguides/reporting/realtime/v3/reference/data/realtime/get?#try-it

こちらの Google アナリティクスのページを見ると、APIの説明と共に、中央もしくは右側にAPIのテストリクエストができるフォームが表示されます。

ids に、 ga: を先頭につけた GoogleAnalytics のID番号を書き、metrics は rt:activeUsers を入れ、

下部 「Google OAuth 2.0」と「API key」にチェックを入れ、「Execute」 ボタンを押します。

すると、さらに下部にレスポンスデータが表示されます。
この情報を、nuxt 等ウェブアプリケーションで取得できます。

作り方

Google Cloud Platform のプロジェクトを作る

まず、Google APIを使うため、GCPのプロジェクトを作ります。

GCPのダッシュボードの上部ヘッダから、プロジェクトを新しく作ります。
https://console.cloud.google.com/home/dashboard

ライブラリの追加

プロジェクトを作ったら、APIとサービスページを開きます。
https://console.cloud.google.com/apis/dashboard

左メニューの「ライブラリ」をクリックし、ライブラリの追加ページで、Google Analytics API と Google Analytics Reporting API を有効にします。

認証情報の追加

次は、認証情報ページを開きます。
https://console.cloud.google.com/apis/credentials

上部「認証情報を作成」リンクから、「API キー」を選択し、APIキーを一つ作ります。

続いて、「認証情報を作成」リンクから、「OAuthクライアント ID」を選択します。

アプリケーションの種類 は、今回は「ウェブアプリケーション」で、名前は、適当に「nuxt client」とかにします。

「承認済みの JavaScript 生成元」は、クロスオリジンリクエストを許可するオリジン名です。
平文 http でもOKなので、

http://localhost:3000

などを追加しておきます。

「承認済みのリダイレクト URI」は、OAuth2 の認証フローのリダイレクトURL(コールバックURL)です。
認証完了時、このURLに、アクセストークンが URLに含まれた形でリダイレクトされます。
今回は、

http://localhost:3000/code

としました。
本番用のURLが決まっていたら、その分も追加します。後からでも追加できます。

スクリーンショットの例は、ローカルPCで docker + Nginx でポート80 でサービスする場合を考慮し、
http://localhost も追加してあります。

ちなみに、クライアントID とクライアントシークレット が作成されますが、ブラウザからのリクエストの場合はクライアントシークレットは使いません。

OAuth 同意画面は、今回は社内用途のみ考慮しているので「内部」を選択し、作成します。

プロジェクトの状態によっては、この選択肢は無いかもしれません。

これで、GCP のプロジェクトの設定は終了です。

アプリを書く

今回は、nuxt + TypeScript で書きます。すべてを載せると冗長なので、要点のみ記載します。

流れとしては、

1.
まずブラウザで / ページ表示時。
最初はアクセストークンが無いため、
store/auth.ts の navigateTokenRequestUrl がコールされる。

2.
navigateTokenRequestUrl の中では、ステートパラメータを作ってローカルストレージに保存し、
Google の認証URLに遷移。

3.
認証が完了すると、リダイレクトURL である /code に着地します。

4.
/code では、URLのハッシュ(フラグメント) から、アクセストークンとステートを取得し、
ステートの一致を検証後、アクセストークンを保存し、/ に遷移します。

5.
/ では、今回はアクセストークンがあるため、
コンポーネント内でそのアクセストークンを使って Google API でリアルタイムユーザー数を取得して表示します。

6.
このアクセストークンの寿命は1時間のため、1時間するとGoogle API は http 401 を返すようになります。
その場合、もう一度アクセストークン取得URLに遷移することで、アクセストークンの取り直しをします。

この時、Google とのセッションクッキーが認証済みであれば、ユーザー操作は不要で、一瞬画面がちらつくだけでアクセストークンの更新が完了します。

設定ファイル settings.ts

export const GOOGLE_API = {
  authAPIEndpoint: 'https://accounts.google.com/o/oauth2/v2/auth',
  analyticsAPIEndpoint: 'https://www.googleapis.com/analytics/v3/data/realtime',
  clientId: '<GoogleAPIの認証情報で作成されてたクライアントID>.apps.googleusercontent.com',
  apyKey: 'AIz<GoogleAPIの認証情報で作成されてたAPIキー>o04',
  scope: 'https://www.googleapis.com/auth/analytics.readonly'
}

export const GOOGLE_ANALYTICS_ACCOUNTS = [
  {
    id: '157xxxxx',
    title: 'サイト1'
  },
  {
    id: '872xxxxx',
    title: 'サイト2'
  },
]

型ファイル types/tasks.d.ts

interface TaskResult {
  success: boolean;
  message: string;
}

Vuex Store モジュール store/auth.ts

import { Action, Module, Mutation, VuexModule } from 'vuex-module-decorators'
import { GOOGLE_API } from '~/settings'

// OAuth2 state パラメータの localStorage一時保存用
const LOCAL_STORAGE_AUTH_STATE_KEY = 'authState'

@Module({
  name: 'auth',
  stateFactory: true,
  namespaced: true
})
export default class extends VuexModule {
  accessToken: string = ''

  /**
   * 認証済みか?
   */
  get authorized () {
    return this.accessToken !== ''
  }

  /**
   * アクセストークンを保存
   */
  @Mutation
  setAccessToken (token: string) {
    this.accessToken = token
  }

  /**
   * OAuth2 認証URLに移動する
   */
  @Action
  navigateTokenRequestUrl () {
    // 乱数で state パラメータを作成
    const authState = [...Array(30)].map(
      () => Math.random().toString(36)[2]).join('')
    // state パラメータをローカルストレージに保存する
    window.localStorage.setItem(LOCAL_STORAGE_AUTH_STATE_KEY, authState)
    const params = [
      ['scope', GOOGLE_API.scope],
      ['include_granted_scopes', 'true'],
      ['response_type', 'token'],
      ['state', authState],
      ['redirect_uri', `${window.location.origin}/code`],
      ['client_id', GOOGLE_API.clientId]
    ]

    window.location.href = `${GOOGLE_API.authAPIEndpoint}?` +
      params.map(i => `${i[0]}=${encodeURIComponent(i[1])}`).join('&')
  }

  /**
   * OAuth2認証完了後のURLのハッシュ(フラグメント)を解析して、アクセストークンを保存
   */
  @Action
  parseResponseParamsString (paramsString: string) : TaskResult {
    // #key=value&key2=value2 形式の文字列を URLSearchParams にする
    const usp = new URLSearchParams(
      paramsString.replace(/^#/, '')) as any
    // URLSearchParams を辞書型(マップ型)変数に変換
    const paramsDict = [...usp.entries()].reduce(
      (dict, e) => ({ ...dict, [e[0]]: e[1] }), {})
    // localStorage に保存されている state と一致しているか検証
    if (paramsDict.state !== window.localStorage.getItem(
      LOCAL_STORAGE_AUTH_STATE_KEY)) {
      return {
        success: false,
        message: 'stateが一致していません'
      }
    }
    // state はもう使わないので消す
    window.localStorage.removeItem(LOCAL_STORAGE_AUTH_STATE_KEY)
    // アクセストークンがあるか検証
    if (!paramsDict.access_token) {
      return {
        success: false,
        message: 'アクセストークンが取得できません'
      }
    }
    // アクセストークンがあったので変数に保存する。認証成功。
    this.setAccessToken((paramsDict.access_token))
    return {
      success: true,
      message: ''
    }
  }
}

pages/index.vue

ユーザーが最初に表示するページ

<template>
  <div>
    <header class="d-flex text-white p-2">
      <div class="flex-grow-1 py-1">
        Developer Dashboard
      </div>
    </header>
    <div v-if="authorized">
      <div class="container-fluid">
        <div class="row">
          <div
            v-for="account in googleAnalyticsAccounts"
            :key="account.id"
            class="col-6 col-md-4 col-xl-3 my-3"
          >
            <RealtimePanel
              :analytics-id="account.id"
              :title="account.title"
            />
          </div>
        </div>
      </div>
    </div>
  </div>
</template>

<script lang="ts">
import { Component, Vue } from 'nuxt-property-decorator'
import { authStore } from '~/store'
import RealtimePanel from '~/components/RealtimePanel.vue'
import { GOOGLE_ANALYTICS_ACCOUNTS } from '~/settings'
@Component({
  components: {
    RealtimePanel
  }
})
export default class Index extends Vue {
  get accessToken () {
    return authStore.accessToken
  }

  get authorized () {
    return authStore.authorized
  }

  get googleAnalyticsAccounts () {
    return GOOGLE_ANALYTICS_ACCOUNTS
  }

  requestToken () {
    authStore.navigateTokenRequestUrl()
  }

  mounted () {
    // 認証済みでなければトークン取得URLへ遷移
    if (!authStore.authorized) {
      this.requestToken()
    }
  }
}
</script>

pages/code/index.vue

OAuth API 認証後のリダイレクトURL(コールバックURL)

<!--
OAuth2 認証後にリダイレクトされるURL
URLに含まれるハッシュ(フラグメント)から、アクセストークンを取得して変数に格納する。
-->
<template>
  <div>
    code received
  </div>
</template>

<script lang="ts">
import { Component, Vue } from 'nuxt-property-decorator'
import { authStore } from '~/store'
@Component({
  components: {
  }
})
export default class Index extends Vue {
  getRequestToken () {
    authStore.navigateTokenRequestUrl()
  }

  async mounted () {
    const taskResult = await authStore.parseResponseParamsString(
      window.location.hash.replace(/^#/, ''))
    if (!taskResult.success) {
      throw new Error(taskResult.message)
    }
    this.$router.push('/')
  }
}
</script>

components/RealtimePanel.vue

<!--
GAのアカウント1つに対応
一定時間ごとに、リアルタイムユーザー数を更新し続けるコンポーネント
-->
<template>
  <div class="card">
    <div class="card-header h2 py-3 text-truncate">
      {{ title }}
    </div>
    <div class="card-body">
      <div v-if="errorMessage" class="my-4">
        {{ errorMessage }}
      </div>
      <div v-if="responseSuccess" class="text-center my-4">
        <div class="display-1 fw-bold">
          {{ activeUsers|addComma }}
        </div>
        <div class="text-muted small">
          Active User
        </div>
      </div>
    </div>
  </div>
</template>
<script lang="ts">
import { Component, Vue, Prop } from 'nuxt-property-decorator'
import { authStore } from '~/store'
import { GOOGLE_API } from '~/settings'

@Component({
  components: {
  }
})
export default class extends Vue {
  activeUsers: number | null = 0
  responseSuccess: boolean = false
  errorMessage: string = ''

  @Prop({
    type: String,
    required: true
  })
  title!: string

  @Prop({
    type: String,
    required: true
  })
  analyticsId!: string

  mounted () {
    this.reloadPolling()
  }

  reloadPolling () {
    this.reload()
    setTimeout(() => {
      this.reloadPolling()
    }, 20000)
  }

  async reload () {
    const response = await this.$axios.get(
      GOOGLE_API.analyticsAPIEndpoint, {
        params: {
          key: GOOGLE_API.apyKey,
          ids: `ga:${this.analyticsId}`,
          metrics: 'rt:activeUsers'
        },
        headers: {
          Accept: 'application/json',
          Authorization: `Bearer ${authStore.accessToken}`
        },
        validateStatus: _ => true
      }
    )
    // トークン期限切れ
    if (response.status === 401) {
      authStore.navigateTokenRequestUrl()
      return
    }
    // 403: アクセス過多など
    if (response.status !== 200) {
      this.responseSuccess = false
      this.errorMessage = `ERROR: ${response.status}`
      return
    }
    if (response.data) {
      this.activeUsers = parseInt(response.data.totalsForAllResults['rt:activeUsers'])
      this.responseSuccess = true
      this.errorMessage = ''
    }
  }
}
</script>