高速化のため Slick2 で SQL に書きなおしたときの手法

Scala のプロジェクトに参加したのですが、そこで使われていた Slick2 があまりにも遅く見づらい SQL を出力していたため SQL を直に叩くようなコードに書き換え高速化を行いました。その時の手法を記しておきます。Slick2 が出力する SQL ではインデックスが効かなくなる事も多かったので、かなりの高速化になりました。

検索条件を引数に取らない SQL の実行

もっともシンプルな SQL ですね。 queryNA を使用します。

import play.api.db.slick._
import scala.slick.jdbc.{StaticQuery => Q}

// 全てのユーザーの id を返す
def users(): List[Int] = {
  DB withSession {
    Q.queryNA[Int]("""
      select id from users
    """).list
  }
}

戻り値が複数の場合はタプルで指定します。

// 全てのユーザーの id と 名前を返す
def select(): List[(Int, String)] = {
  DB withSession {
    Q.queryNA[(Int, String)]("""
      select id, name from users
    """).list
  }
}

検索条件を引数に取る SQL の実行

プログラムから where に値を与えたい場合は query を使用します。第一引数に SQL の引数の型、第二引数に戻り値の型を指定します。

// 名前が一致するユーザーを返す
def user(name: String): Option[Int] = {
  DB withSession {
    val query = Q.query[String, Int]("""
      select id from users
        where name = ?
    """)
    query(name).firstOption
  }
}

引数が複数の場合はタプルで渡します。

// city, age が一致するユーザーを返す
def uesrs(city: String, age: Int): Option[Int] = {
  DB withSession {
    val query = Q.query[(Int, Int), Int]("""
      select id from users
        where age = ? and city = ?
    """)
    query((age, city))..firstOption
  }
}

引数に日時を取る

引数に日時を取る場合は文字列に変換してから渡します。

import org.joda.time.DateTime

// 指定された時間より後に作成されたユーザーを返す
def users(dateTime: DateTime): List[Int] = {
  DB withSession {
    val query = Q.query[String, Long]("""
      select id from users
        where createdAt > ?
    """)
    query(
      dateTime.toString("yyyy-MM-dd HH:mm:ss")
    ).list
  }
}

in を使う

List を引数に与えるような方法は見つけられませんでした。また、文字列を引数に与えると展開時にダブルクォーテーションで囲われてしまうので（ IN ("1,2,3") ）となり意図した SQL になりません。そこで以下のように文字を埋め込みます。

// 指定されたユーザー id が存在すれば返す
def users(ids: List[Int]): List[Int] = {
  DB withSession {
    val in = ids.mkString(",")
    Q.queryNA[Int)](s"""
      select id from users
        where id in (${in})
    """).list
  }
}

List が空の場合も考慮して、以下の書き方の方が行儀がよいかもしれません。

// ユーザーを取得
def users(ids: List[Int]): List[Int] = {
  DB withSession {
    val in = ids.isEmpty match {
      case false => "false"
      case true => "id in (" ids.mkString(",") + ")"
    }
    Q.queryNA[Int](s"""
      select id, screenName from users
        where ${in}
    """).list
  }
}

List が空だった場合は以下の SQL になります。

select id from users
  where false or age >= 20

offset, limit を指定する

in と同様のやりかたで文字を埋め込みます。

// ユーザーを取得
def users(offsetLimitOpt: Option[(Int, Int)]): List[Int] = {
  DB withSession {
    val queryOffsetLimit = (offsetOpt, limitOpt) match {
      case (Some(offset), Some(limit)) => s"limit ${offset}, ${limit}"
      case _ => ""
    }
    Q.queryNA[Long](s"""
      select id, screenName from users
        ${queryOffsetLimit}
    """).list
  }
}

戻り値をオブジェクトで受け取る方法

戻り値をオブジェクトで受け取る場合は GetResult を使いマッピングのための処理を定義する必要があります。

case class User (
  id: Int,
  name: String,
  email: String
)

// ユーザーを取得
def users(): List[User] = {
  DB withSession {
    implicit val getUser = GetResult(r =>
      User(r.<<, r.<<, r.<<)
    )
    Q.queryNA[User]("""
      select * from users
    """).list
  }
}

この時 DB でのカラムの並びとクラスの変数定義の並びが同じでないと別のカラムに値が入ってしまので注意が必要です。

+--------------------+
| Field              |
+--------------------+
| id                 |
| name               |
| email              |
+--------------------+

case class User (
  id: Int,
  email: String,
  name: String
)

email <-> name の値が入れ替わってしまう

GetResult では scala に定義されている型は r.<< と省略して表記することできますが、以下のような場合は正しく記述する必要がある。

• DateTime -> new DateTime(r.nextTimestamp)
• Optin[DateTime] -> r.nextTimestampOption.map(t => new DateTime(t))
• Option[String] -> r.nextStringOption

// 例
case class User (
  id: Int,
  name: String,
  email: String,
  createdAt: DateTime
)

// ユーザーを取得
def users(): List[User] = {
  DB withSession {
    implicit val getUser = GetResult(r =>
      User(r.<<, r.<<, r.<<, new DateTime(r.nextTimestamp))
    )
    Q.queryNA[User]("""
      select * from users
    """).list
  }
}

join を使って複数テーブルのオブジェクトを取得

GetResult をオブジェクト数分定義してあげれば、あとは今までのやり方の応用になります。

// ユーザーとユーザーのログ情報を取得
def users(): List[(User, UserLog)] = {
  DB withSession {
    implicit val getUser = GetResult(r => User(r.<<, r.<<, r.<<))
    implicit val getUserLog = GetResult(r => UserLog(r.<<, r.<<, r.<<))
     Q.queryNA[(User, UserLog)]("""
      select users.*, userLogs.* from users
        inner join userLogs on userLogs.userId = users.id
    """).list
  }
}

実行時にスロークエリをみてみると、join を使った箇所が多くヒットします。Slick2 で join を行うとサブクエリが作成され、そのせいかどうが分からないのですがインデックスが使用されなくなっていました。全て SQL で書き直すことでかなりの高速化が行えます。

SQL に置き換えるだけで速くなる

以下の様な単純なものでも実行時間が６ミリ秒から２ミリ秒に短縮されました。

// 修正前
slickUsers.filter(
  _.id === id
).firstOption

// 修正後
val query = Q.query[Int, Int]("""
  select id from users
  where id = ?
""")
query(id).firstOption

できない事

User に UserLog を left join し、UserLog が存在しない場合でも User をとりたいような場合。戻り値を(User, Option[UserLog]) の形式で受け取るような方法は見つかりませんでした。

おわりに

SQL で書きなおせばたいてい速くなります。速度で問題になったときの参考にしてください。