MySQLのテーブル設計で結合行数の見積をしようという話

概要

MySQLはシンプルで扱いやすいRDBMSですが、性能を下げずに運用を続けるには、テーブル設計の段階から気をつけなければならないことが幾つかあると感じています。Zenn記事でそれらをまとめていきたいと思います。今回は結合に関してです。

前提知識

MySQLがサポートしている結合方法は、NLP（ネステッドループ）です。^[1]

SELECT * FROM t1 LEFT JOIN t2 ON t1.keyCol = t2.keyCol

MySQLの内部的には、for文が入れ子になっているイメージで処理が実行されます。
先に行がフェッチされるt1を駆動表、t2を内部表といいます。

for row1 in fetch(t1):
    for row2 in fetch(t2, { "keyCol": row1.keyCol }):
       （row1+row2を返却）

結合する行数同士の掛け算で全体のテーブル探索行数が決まりますが、内部表のキー列にインデックスを貼ってあれば、一回ごとに内部表の全スキャンをしなくてすみます。

しかし駆動表の件数が多すぎると、その回数分、インデックスを使った問い合わせが行なわれるので、内部表のキー列にインデックスがあるだけでは、結果的に速度はあまり改善されません。

こうした場合は駆動表の件数を減らすべく、サブクエリを使い条件で絞り込んだものを駆動表にするパターンを取りますが、設計上それが使えない場合があります。
毎回、表の全て or 大部分をそのまま結合する必要のある場合です。

危ない例1　垂直分割

垂直分割とは何かといえば、正規化でテーブルを分けるのではなく、特定の列の組でテーブルを分割することです。
主な目的としては、この列だけ更新トランザクションが激しいとか、同時に参照されることが多い等の理由がある時に、行全体がロックされるのを防ぐために、特定の列の組だけ別テーブルにします。

列で分けるので、分割元と分割先のテーブルで行数は変わりません。
先述の通り、MySQLでこの設計を進めると、後になって結合が遅くて困るときがあります。（ありました）

分割元のテーブルと結合する際に普通にID等で結合すると、駆動表の行数は多いままです。100万行のテーブル同士の結合だと、内部表にインデックスがあっても最小で100万行の内部表探索が行なわれるため、遅くなります。

テーブルの行数が概ね10万件〜以上に増えることが見込める場合、垂直分割は避けた方が無難そうです。^[2]

危ない例2　サブタイプ分割

概念モデルをスーパータイプ・サブタイプに分ける論理設計をした時、実装方法として、実際にテーブル分割するというパターンがあります。この場合も、MySQLでは制限がかかると思います。
分けたときに2つのテーブルの行数は同じになるはずで、結合処理の性能に懸念が出るためです。

スーパータイプにサブタイプ区分子を持たせ、全体の2分の1, 3分の1の行数でサブタイプとの結合を行うとしても、全体の行数が多ければ、結合行数を十分に減らせないことが想像できます。^[3]

その他　中間テーブルについて

結合行数に注意するという意味では、中間テーブルも組み合わせで列を持つ分、テーブル全体の行数が多くなりやすいため、気をつけた方が良いと思います。

これの場合は先の例2つに比べて、ほとんどの行を結合させる想定で作られるものではないので、ネステッドループ結合のベストプラクティスである「駆動表を小さくし、行数の多いテーブル（この場合、中間テーブル）を内部表にして、内部表の列をインデックス参照させる」形での対策が、比較的しやすいと思います。

ただ内部表へのインデックスが毎回使われる必要があるので、そのあたりは複合キーを使ったり、カーディナリティの低いカラムに貼ることを避ける等して、最適なインデックス設計を考えるべきかと。

まとめ

垂直分割の問題は実際に直面したため、コストをかけて分割テーブルの統合で対応しました。
何かややこしく書いてしまいましたが、とりあえずMySQLではテーブル行数の多いことが見込まれるとき、そのテーブルの大部分を結合する設計には気をつけようということを共有できれば幸いです。

脚注

1. MySQL8.0以降では、ハッシュジョインもサポートしています。しかし、任意のタイミングで使えるオプティマイザスイッチはありません（MySQL以外のRDBMSでは、バージョンを問わずハッシュジョイン、ソートマージジョインがサポートされ、オプティマイザスイッチがある認識です）

2. 分散DBサーバでは、問い合わせごとに効率の良い結合をする目的で、垂直分割したものを各サーバに置くことがあり、その場合は、結合コストのことを検討済みと思われるため問題ないと思われます。
   （分散DBサーバの採用理由には、書き込み処理の負荷分散を狙ったものが多く、作りがシンプルで同時書き込み性能の良いMySQLは、こうした使い方には向いていると思います）

3. 実際に運用して感じた訳でなく申し訳ないのですが、理屈として、垂直分割に準拠するリスクがあるはずなので記載しました。