BKA JOINについて

MySQL Casual Talks @gumi福岡2012/06/27

自己紹介

名前： 松崎 学

所属： 株式会社キャム （SaaS型の経営管理システムを提供中） http://www.cam-net.co.jp/

Twitter： matsumana

最近のお仕事：Javaプログラマ（Rubyもほんの少し）、 インフラ

最近の興味： Scala, Ruby, Python, Play, Node.js, AWS,

Hadoop, Asakusa Framework, MongoDB, Fluentd, Vyatta

データベースと私Oracle歴14年 (7.1～ on Windows , Solaris, Linux)

PostgreSQL歴1.5年 (9.0～ on Linux)

MySQL歴１ヶ月程 (今回のLTをきっかけに勉強を始めました)

初学者ですが、よろしくお願いします m(_ _)m

http://www.flickr.com/photos/dapperscoo/166797625/

JOINアルゴリズムあれこれ

Nested Loop JOIN

片方のテーブルを外部表(駆動表)とし、もう片方のテーブルをループで回してJOINする。（要するに２重ループ）外側のループ＝外部表 内側のループ＝内部表

Merge JOINまず、両方のテーブルをJOIN項目でソートして、先頭から順次読み込んでJOINする。

Hash JOINまず、JOIN項目でHashテーブルを作成し、そのHashテーブルを元にJOINする。

SQL実行計画に出てくるアレです

MySQLにはNested Loop Join

しかない。※だから遅いという事では決してありません。

クエリ次第だと思います。

しかし、

Block Nested Loop Join

が実装されているので、

特定の条件を満たせば、内部表のループ回数が大幅に

減少する。

http://dev.mysql.com/doc/refman/5.1-olh/ja/nested-loop-joins.html

ブロック入れ子ループ join アルゴリズム

ブロック入れ子ループ (BNL、Block Nested-Loop) join アルゴリズムは、外側のループで読み取った行のバッファリングを使用して、内側のループで必要となるテーブルの読み取り回数を減らします。たとえば、バッファーに 10 行を読み込み、このバッファーを次の内側ループに渡すと、内側ループで読み取る各行をバッファー内の 10 行すべてと比較できます。これにより、内側のテーブルを読み取る必要のある回数が大幅に減少します。

MySQL 5.1 リファレンスマニュアル 抜粋

新しいJOINアルゴリズム登場！

Batched Key Access JOIN (BKA JOIN)5.6.3 m6から利用可能。

5.6は2011/04にリリースされた、まだDevelopment Releaseなバージョンです。

※今日時点の最新は 「5.6.5 m8」

http://nippondanji.blogspot.jp/2011/10/mysqlmysql-563-m6.html

BKA JOINは変形NLJ（Nested Loop JOIN）とも言うべきもので、内部表からのレコードのフェッチをひとつずつではなくまとめて行う。先に駆動表から結合するキーの値をいくつかピックアップし、それを内部表に対してまとめて渡す。（Index

Condition Pushdownする。）すると、内部表ではMRR（Multi Range Read）最適化によってアクセスがソートされ、必要なレコードを最適な順序でフェッチすることになり、JOINが高速化するというわけだ。

InnoDBやMyISAMでは、MRRによる高速化が見込めるのははセカンダリインデックスによるアクセス時だけである。（主キーでアクセスする場合には別段変わらない。）従って内部表のセカンダリインデックスを使って結合する場合には高速化が見込めるだろう。BKAが使われているかどうかは、EXPLAINでExtraフィールドを見れば分かる。

BKA JOINとは？

ICP (Index Condition Pushdown)これまでストレージエンジンからフェッチしたレコードをMySQLが評価してWHERE句の条件による絞り込みを行っていたが、インデックスが貼られたカラムを用いた評価については、ストレージエンジンへ条件式を渡し（プッシュダウンし）、ストレージエンジン側で評価を行わせることによってオーバーヘッドの低減をはかる。

MRR (Multi Range Read)セカンダリインデックスの条件に合致するレコードを複数フェッチする場合、レコードはセカンダリインデックスとは無関係に並んでいるため、多くのランダムI/Oが発生する。先にセカンダリインデックスのキーだけを読み取って、主キーの順にソートしてからレコードをフェッチすることにより、ランダムディスクI/Oの低減を期待できる。

http://nippondanji.blogspot.jp/2011/04/mysql-56.html

MySQL 5.6 Reference Manual

Batched Key Access Joinshttp://dev.mysql.com/doc/refman/5.6/en/bka-optimization.html

Index Condition Pushdown Optimizationhttp://dev.mysql.com/doc/refman/5.6/en/index-condition-pushdown-optimization.html

Multi-Range Read Optimizationhttp://dev.mysql.com/doc/refman/5.6/en/mrr-optimization.html

要約すると

BKA JOINは駆動表のJOINキーをバッファリングした後、ストレージエンジンにまとめて送る。

内部表のフェッチはストレージエンジンで行われる。ストレージエンジンとのやり取り回数が減るので、オーバーヘッドが低減。

内部表はMRRにより最適な順番でフェッチされるため、ランダムアクセスI/Oが低減。

BNL JOINよりだいぶ早そうですね！！

BKA JOIN

ICP+

MRR

http://www.flickr.com/photos/robwithtwobs/310185817/

BKA JONがICPとMRRの上に立脚しているイメージ

お薦め書籍本当はソースを理解して、内部までお話したかったのですが、

力不足でした・・・＞＜興味のある方は sql/sql_select.cc あたりからぜひ読んでみてください。

NetBeansやEclipseを使えばカジュアルにソースが読めます

MySQLはドキュメント、書籍、

ブログエントリが充実していて、初学者でも取っ付きやすい

カジュアルなデータベースである。

まとめ

福岡インフラ勉強会

https://www.facebook.com/groups/100825430047874/

Facebookのグループにもぜひご参加ください

ご清聴ありがとうございました。