動機の供述
自分の好きなDB分野の話で、基本的ではあるけれど重要な知識が
若手エンジニアの間などであまり共有されていないのを見てきた。
Qiitaに次に何を投稿しようかと思ったとき、いいねも欲しいのでそんな需要のありそうな記事を書こうと思った。
MySQLの特性について
オープン系システム開発において、RDBの選定にMySQLが採用されることは多いのではないでしょうか。
同じOSSのRDBでPostgresという選択肢があると思いますが、
業務実績があるから、社内のエンジニアが慣れているからといった理由で無条件にMySQLが選ばれることがあるかもしれません。
実際のところ私もMySQLの方が慣れてます。
しかし題記のようにMySQLを使う際は、その特性を把握しておかないと思わぬ事故の元になることがあります。
SELECT, UPDATEなど、基本的な処理はPostgresよりも性能が高いものの、複雑なことをしようとすると機能が制限されており、陥りやすいワナがあるからです。
その一つが、JOINに制限が多く、複雑なSQLが苦手という点です。
特定の状況において、MySQLでカジュアルに組まれたクエリは時限爆弾のようなものになります。
例えばDBサーバを水平分割し負荷分散した大規模システムなどはJOINを使わないため、今回の記事とは別になります。しかし小規模なサーバ構成で概ね数十万レコード以上を扱い、複雑なSQLを発行する場合には注意が必要になってきます。
そうした中小規模の開発ではエンジニア側もフレームワーク標準のORMを使うことが多いと思います。それが悪いわけではありませんが、JOINを多用するなどのケースだと、ネイティブSQLの機能を使って組み立てた方がベターでしょう。SQLの実行計画を確認しやすくするためです。
実行計画を把握しておかないと、業務データが増大してスロークエリが頻発してからはじめてトラブルに気付くことになるかもしれません。
はい、自分も通ってきた道です。
備忘録と振り返りをかねてこの辺の話をまとめてみます。
サンプル
例として、施工管理システムなどで、見積に対する受注状況をSQLで抽出するとします。
"SELECT
見積.ID,
見積.担当者,
見積.金額,
IFNULL(受注.ID, '未受注') as 受注ID
FROM
見積
LEFT JOIN
受注 ON 見積.ID = 受注.見積ID"
見積が受注に至るケースは10件に1件程度と仮定しましょう（経営状態が心配になりますが）
その場合このSQLは、見積テーブルのデータの増大にしたがって遅くなっていくと予想されます。
以下で前提となる知識を記載しつつ、その理由を書きます。
MySQLで使えるJOIN方式
現状、MySQLがサポートするJOINアルゴリズムはネステッド・ループ結合のみです。
これは名前の通り入れ子のループによる結合方法で、抽出件数が少ないとき使用メモリ領域が少なく済む利点があります。
for each row1 in t1.filter(it => { return it.col == joinKey } {
for each row2 in t2.filter(it => { return it.col == joinKey } {
sendClient(row1, row2)
}
}
ここでt1テーブルは駆動表、t2テーブルは内部表と呼ばれます。
駆動表と内部表の走査行数を少なくできれば、掛け算で走査対象の行数が減るのがわかると思います。しかしどちらの行数を減らしても同じ結果ではないようで、内部表の方はインデックスをつかった高速な参照ができます。
mysql5.6.22環境で確認したところ、インデックスが貼られている結合条件キーに対して駆動表ではフルテーブルスキャン、内部表ではインデックスが使われる結果になります。
※実行計画表で、上に表示されるのが駆動表です。
内部表の結合キーにインデックス参照を用い、駆動表の件数を少なくすることで参照の回数についても少なくできると、より効率的になります。
・駆動表は行数が少ないものを選ぶ
・駆動表と内部表の結合キーにindexが適用されるようにする
これがネステッド・ループJOINのチューニングの基本形となります。
外部結合
最初の例のSQLに戻ってみると、未受注の見積データも抽出するため、見積と受注テーブルがLEFT JOINで結合されています。
注意しておきたいのは、JOINのパフォーマンス劣化においては、外部結合が問題となることが多いことです。先の駆動表と内部表において、内部結合であればオプティマイザが行数の少ないテーブルを駆動表にしてくれる場合が多いですが、外部結合ではLEFT JOINであれば左辺のテーブルが駆動表に固定されるため、駆動表に件数が多く増える速度も早いテーブルを指定してしまうと、前述のt1 × t2の処理コストが時間とともに増えることになります。ただそれでも結合がひとつで、内部表の参照にインデックスが使われている限りは、問題が表面化しにくいかと思います。しかし多段結合でインデックスも使われない場合は、LEFT JOINでは結合ごとの走査行数が絞り込まれないため、非常にコストがかかるようになります。
ちなみにRIGHT JOINを使えば右辺のテーブルが駆動表になります。
RIGHT JOINはかんたんにLEFT JOINで置き換えできるため、
現場において使わないことが多いですが、あえて使っている場面があれば上記のチューニングの意図があるのかもしれません。（見かけたことはないのですが、すでにある複雑なクエリをいじることなしに、全行が結果に含まれて良い件数の少ないテーブルを結合するような場面で効果がありそう？）
データの絞り込み
駆動表、内部表はできる限り件数を減らしたいので、絞り込み条件を入れたいと思います。
次の例では、多段結合した結果に対して、WHERE句でのデータ絞り込みを行なっています。
"SELECT
（省略）
FROM 見積
LEFT JOIN
受注 ON 見積.ID = 受注.見積ID
LEFT JOIN
工事 ON 受注.ID = 工事.受注ID
WHERE 受注.受注日 BETWEEN '2019-09-01' AND '2019-10-01'"
上記ですが、WHERE句で絞り込むのはチューニングの観点からは誤りです。
SQLの実行順としてはJOIN句 → WHERE句のため、この例ではJOINしてから絞り込むことになります。
外部結合するテーブルを絞り込むとしたら次のようになるでしょう。
"SELECT
（省略）
（
SELECT
（省略） FROM 見積
LEFT JOIN
受注 ON 見積.ID = 受注.見積ID
WHERE 受注.受注日 BETWEEN '2019-09-01' AND '2019-10-01'
) as 見積受注
LEFT JOIN
工事 ON 受注.ID = 工事.受注ID
これで駆動表を絞り込むという目的は達成できるのですが、サブクエリで表を作った結果、その結果表のカラムにインデックスは効かないことには注意が必要です。
上記だとサブクエリの中ではindexが効きますが、例えば外側でORDER句を追加し、結合結果に対して「見積受注.受注日」の並び替えをさせる場合にインデックスは使えません。インデックスの効かない並び替えは非常にコストが高いため、避けたいところです。
一方で、内部結合では次のように、JOIN ON句での絞り込みができます。
（外部結合から内部結合への変更で、抽出結果が変わってしまいますので、
これは抽出要件が別の場合の例です。）
外部結合の場合は結合できなくてもNULL拡張されるため、JOIN ON句での絞り込みはできません。
"SELECT
（省略）
FROM 見積
INNER JOIN
受注 ON 見積.ID = 受注.見積ID AND 受注.受注日 BETWEEN '2019-09-01' AND '2019-10-01'
LEFT JOIN
工事 ON 受注.ID = 工事.受注ID
ORDER BY 受注.受注日
これだとORDER BY句での受注.受注日のインデックスも効きます。
ただしこれは単純な例で、INNER JOINでの結合であってもORDER BYを受注.取引IDで行いたいとかであれば、（受注.受注日, 受注.取引ID）の複合インデックスの設計が必要になります。
MySQLは基本的には1テーブルにつき1つしかindexが効かないためです。（インデックスマージという機能がありますが、index設計は重要であるに対して、オプティマイザ任せになってしまうためおすすめできません）
MySQLのSQLはあとからの変更に弱いため、このクエリはこの処理だけをする、といった設計がindexを含め重要になってきます。
※indexが効かない状況についてまとめると別記事になってしまうので省略しますが、本記事などよりも先に押さえるべき内容だと思います。
QiitaにはMySQL関係の良記事がたくさんありますので、一部をご紹介します。
ソシャゲエンジニアの自分が開発に必須だなと思った知識(MYSQL編)
JOIN周り以外、MySQLを使うのに必要な知識がほとんど網羅されています。
実例で学ぶ、JOIN (NLJ) が遅くなる理屈と対処法
すみません、内容がほとんど被ってしまいました。開き直って参考にさせて頂きました。
外部結合は避けるべき？
なるべくLEFT JOINを使わないべきかについて。
INNER JOINを使うと
・オプティマイザがJOIN順序で適切な表を選んでくれやすい
・多段結合時、インデックスが使われなくなっても件数が絞り込まれるので、比較的安全
・サブクエリを使わずに結合条件で絞り込みできるので、インデックス設計がしやすい
こうした有利な点があるため、むやみなLEFT JOINを避けてINNER JOINに置き換えできないか検討する価値はあるでしょう。
ただ、問題が発生する基本的な理屈としては同じですし、LEFT JOINを利用したいケースももちろんあるので、本記事で触れたような注意事項を把握していれば問題ない話だと思います。
JOINをやめたらどうなるか？
昨今はnoSQLやドキュメント志向DBの台頭もあって、MySQLを索引機能やACID特性を持つストレージとして扱い、JOINは不要と考える向きもあるかもしれません。
しかし分散DB環境のケースを除き、リレーショナルDBからリレーショナルを引いて使うのは、この記事を読んでおられる技術者の方々なら、難色を示されるのでないでしょうか。
とりあえず問題を挙げると、JOIN禁止では更新・削除のトランザクション処理が煩雑になりますし、どこかで更新不整合が発生したときにアプリケーションロジックのミスを疑わなくてはいけなくなります。
また運用が死にます。気軽に複数テーブルからのデータを抽出することができなくなります。例え設計の段階で運用のために結合キーなどを用意しても、アプリケーション側で整合性を保つようにしてくれる保証はありません。途中で忘れられそうです。
そんな状態のDBから複雑な業務要件のデータを抽出するのは、あまり考えたくないことです。
ただしJOINをしないシステムという観点からは、MySQLはPostgresよりも向いているといえるでしょう。
記述に間違い等あれば修正させていただきます。