最近、CourseraのArgorithms, Part1という講義を受けている。そこでソートの講義を受けて、そういえばMySQLのORDER BYでfilesortになったときってどのソートが使われているのだろうと気になってきたので調べてみた。
調べてみると非常に難解で、結局いまいち分からなかったが、今の段階の調べた内容をひとまず書いておく。MySQLのコードを読んだのも初めてで、しかもちゃんと読み解くことができなかったので、情報が間違っている可能性も非常に高い。間違ってたら指摘してもらえるとうれしいです。
調査結果
最初に調査結果を書いておく。たぶんこれは非常に単純化したもので、詳しく見るともっといろいろチューニングされてそう。
- sort_buffer_size以内のメモリ量でソートが可能な場合、メモリ内でのみソートされる
- ソートにsort_buffer_size以上のメモリが必要な場合、sort_buffer_sizeでメモリが足りる単位でメモリ内ソートが行われ、それぞれの結果を一時ファイルに書き出す。最後に一時ファイルからマージソートする
- メモリ内のソートには、クイックソートか、Priority Queueを使ったTop-kソートが用いられる
- ソートしなければならない行数が、最終的に必要な行数の3倍を超える時、Priority Queueを使ってソートされる。そうでなければクイックソートが使われる
MySQL :: MySQL 5.6 リファレンスマニュアル :: 8.2.1.15 ORDER BY の最適化 を見てみると、filesortのアルゴリズムが書かれていた。これによると、filesortのアルゴリズムには、「元のアルゴリズム」と「変更されたアルゴリズム」の二つがあるらしい。「変更されたアルゴリズム」は「行を 2 回読み取ることを回避する最適化が行われたもの」らしいので、今回は考えないことにして、「元のアルゴリズム」の内容を見てみる。
「元のアルゴリズム」は次のステップで進むらしい。
1. キーに従って、またはテーブルスキャンによって、すべての行を読み取ります。WHERE 句に一致しない行をスキップします。
2. 行ごとに、ソートバッファーに値のペア (ソートキー値と行 ID) を格納します。
3. すべてのペアがソートバッファーに収まる場合は、一時ファイルが作成されません。そうでない場合は、ソートバッファーがいっぱいになると、メモリー内でそれに対して qsort (quicksort) が実行され、それが一時ファイルに書き込まれます。ソートされたブロックへのポインタを保存します。
4. すべての行が読み取られるまで、前の手順を繰り返します。
5. 別の一時ファイルで、最大 MERGEBUFF (7) 領域の 1 つのブロックへのマルチマージを実行します。最初のファイルのすべてのブロックが 2 番目のファイルに格納されるまで、この処理を繰り返します。
6. 残りが MERGEBUFF2 (15) ブロックより少なくなるまで、次を繰り返します。
7. 最後のマルチマージで、行 ID (値のペアの最後の部分) のみが結果ファイルに書き込まれます。
8. 結果ファイルで、行 ID を使用して、ソートされた順序で行を読み取ります。これを最適化するには、行 ID の大きなブロックを読み取り、それらをソートして、それらを使用して、ソートされた順序で行を行バッファーに読み込みます。行バッファーサイズは read_rnd_buffer_size システム変数値です。この手順のコードは sql/records.cc ソースファイルにあります。
単純に書くと
- sort_buffer_sizeに収まりソートできるなら、quicksortが実行され、結果として返される
- 収まらないなら、分割してquicksortした結果を一時ファイルに書き出しておき、最後にマージソートしていく
というイメージだろうか。
メモリ上でソートするなら、クイックソートは計算時間も速く、かつ無駄な空間も使わないので効率的である。また一旦ファイルに書き出したソート済配列を結合するなら、シーケンシャルに先頭から見ていけばソートが完了するマージソートの手法が効率的である。これからクイックソート+マージソートの組み合わせになるのは自然だなと感じた。
まとめ
アルゴリズムでソートの勉強をしていて、ふとMySQLのfilesortはどうやっているんだろうと気になったので、軽く調査をしてみた。MySQLのコード初めて読んだのだけど、非常に難しくて正しく読み解くことが出来たか怪しい。間違っている可能性も大いにあるので、何かおかしいことがあれば指摘してもらえるとうれしいです。