ぽんこつです。MySQL Casual Advent Calendarですが、ぼくはぶっちゃけプログラマ寄りの人間だし、仕事でもMySQLに限らずチューニング系のお仕事が多いので、クエリチューニングの話をひたすらします。
ちなみに仕事でも趣味でも基本的にMariaDBが多いので、特に断りの無い限りMariaDB 10.0系の話になってます。とはいえ基礎的な話が多いのであんまり関係ないかもしれません。
MyFleetGirlsとは
今回例で挙げるMyFleetGirlsはぼくが開発運用している艦これツールです。オープンソースだし実際の変更を追いやすいので題材に選んでみました。最近は登録している提督数が1000越えており、アクティブユーザは300ぐらいです。ただ見てるだけの人がもうちょっといるかと思っています。多分会社でDB運用してるのに比べれば大したことないです。
ただ、あんまりお金を掛けたくないので、Sakuraのメモリ2GBインスタンス1台でJVMのアプリケーションサーバ(Scala + Playframework)とMariaDBが両方動いており、メモリがかなりカツカツなのが特徴です。(クエリキャッシュが当たってないと、サーバを見てくれたインフラエンジニアがぼやいてました)
また、初期に書いたクエリがユーザの少なさを反映してかなり牧歌的だったりするので、今回題材に挙げるのはそういう初歩的な修正がメインです。最後に仕事で面白かった修正を1つだけ載せておきます。
範囲を絞る
どのDBでも共通ですが、読み込むRow sizeを減らせば当然早くなるので、range indexの効いた列で範囲を絞り込むと劇的に早くなることが良くあります。定番ですね。
リファクタ込みで分かりづらいですが、戦闘結果の取得を180日間に制限した修正ですね。普通に仕様変更込のパターン(Speedup DBというコミットログは酷い)。ここはずっと遅くて何度か修正してる箇所ですね。
issueも残ってる珍しいパターンなのでissueも。ship_historyにデータを突っ込むときに、既に似たようなデータがあったらinsertしない、みたいな処理の「似たようなデータ」を探すクエリそのものが重いという問題です。
最初partitionを試してそこそこ効果はあったのだが確か半分ぐらいにしかならなくて、その後暗黙に範囲の条件があったのを思い出してwhereを追加したら更に6分の1とかになりました。
バグというレベルで遅くなってサービス提供不能になった事例もあります。
複数indexを使う
Twitterで教えてもらった修正。サブクエリを使うと高速化することがあります。おそらくサブクエリにすることによって複数のindexが使えるようになった為だと推測しています。
やってることは海戦ドロップのカウントで、ステージ(areaId, infoNo, cell)とwinRankでgroupByしたカウントの結果に対して、ステージ情報をjoinで付与するものです。groupByした要素で複合indexを持ってるので、それが使えるようになったのでは。
キャッシュする
バグの温床になりやすいのでできるだけ避けたいですが、キャッシュがしやすいデータだと効果は劇的なので多用しがちですね。
limitを追加する
定番。確実ではないけどRow sizeを減らせることが多いです。あとDBからの転送データ量は確実に減ります。(ここがネックになったのを見たことないけど)
selectする列を絞る
定番。indexだけで処理できるようになったりするととても効果が高い。ただプログラマ的にはコード量が増えるので結構つらいことが多い。select * を禁止した話とかも聞きましたがプログラマ的には「とてもつらい」。けど速度が問題になるなら手を付けたいですね。
ここは実は全然つらくない場所なので付与しました。というかcountで*ってなんだ…。手抜きにもほどがある…。
index tableだけで結果を返す
分かり辛いですが、前はfromとtoの引数が無かったときに、初期値を指定して絞り込みをしていましたが、両方無いときは全範囲になることが分かっているので外しました。不要な列を*で読んでたものの修正を併せて行うことで、引数なし(これば一番良くあるアクセスパターンでした)でindex tableだけ見れば良くなるようにしました。
Index追加
定番ですね。MyFleetGirlsのデータ量だとIndex無しでも何とかなる時期が長く、殆ど無いようなテーブルも多いので、slow queryのlogを見つつ問題がありそうなものは対応するような感じです。
特に注意しないといけないのは、
- 複数のindexを1度の処理で扱えない(joinや内部クエリとかで分ければ可能)
- 昇順降順を定義できないので、複合indexの場合昇順と降順の組み合わせ如何では複合indexが使えない
上の制約があるので、重いクエリが決まっている場合は決まった複合index張ればいいとか思ってると、たまに下の制約に引っ掛かります。
commit漁ると結構index追加している差分が出てくるんですが、残念ながらどのQueryが問題になったのか明記されてなくて良く分かりませんでした。whereの絞り込み条件に対してindexを追加することが多いです。
joinの順序を変える
仕事で偶然見つけた最適化です。4〜5個のjoinがあるクエリで、本番サーバ(MariaDB5.5)と手元の環境(MariaDB 10.0)のexplain結果が違うので発覚しました。
要するにMariaDB 10以降でjoin順序の決定が上手くなったらしくて、5.5でも同じことをさせるためにSTRAIGHT JOINでjoinの順序を固定させる、というものです。特に古いMySQLを使っていて、joinが沢山あるクエリで困ったら、最新版のMySQLなりMariaDBなりにexplain投げるとヒントが得られるかもしれません。ぼくの場合、数倍高速化した記憶があるので劇的でした。
勿論Version上げられるに越したことはない気がしますが。MariaDBもちゃんと進化してますね。
おしまい
完全に経験ベースで散文ですが参考になったのなら幸いです。ちなみに会社だとPartition信者してるのですが、Migrationの時間がつらくて実行されないことも多いですね。