SHOEISHA iD

※旧SEメンバーシップ会員の方は、同じ登録情報(メールアドレス&パスワード)でログインいただけます

EnterpriseZine(エンタープライズジン)編集部では、情報システム担当、セキュリティ担当の方々向けに、EnterpriseZine Day、Security Online Day、DataTechという、3つのイベントを開催しております。それぞれ編集部独自の切り口で、業界トレンドや最新事例を網羅。最新の動向を知ることができる場として、好評を得ています。

直近開催のイベントはこちら!

EnterpriseZine Day 2022

2022年6月28日(火)13:10

EnterpriseZine(エンタープライズジン)編集部では、情報システム担当、セキュリティ担当の方々向けの講座「EnterpriseZine Academy」や、すべてのITパーソンに向けた「新エバンジェリスト養成講座」などの講座を企画しています。EnterpriseZine編集部ならではの切り口・企画・講師セレクトで、明日を担うIT人材の育成をミッションに展開しております。

お申し込み受付中!

MySQLチューニング虎の巻

Block Nested Loop Join/Batched Key Access Join

002


MySQL 5.6で追加された新しい最適化

 現在最新の開発版であるMySQL 5.6では、オプティマイザの改良がいくつか行われている。

  •  BNLJがLEFT/RIGHT JOINに対応。(以前はInner JOINのみ)
  •  Batched Key Access Joinアルゴリズムの追加
  •  Multi Range Read最適化の追加
  •  Index Condition Pushdown最適化の追加
  •  FROM句のサブクエリの評価の先延ばし(その結果マテリアライズする必要性が低下)

 本稿では、新しいJOINアルゴリズムであるBatched Key Access Joinについて解説するが、BKAJが内部的に利用する最適化アルゴリズムであるMulti Range Readというアルゴリズムについて先に触れよう。

Multi Range Read

 Multi Range Read(以下MRR)は、簡単にいうと「ディスクの並び順に従ってデータファイルを読み取る」というもので、RDBMSでは非常に古くからある最適化手法である。セカンダリインデックスを使って検索を行うと、レコード全体を取得する際の操作はランダムアクセスになってしまう。InnoDBの場合はクラスタインデックスとして主キーにレコードが含まれており、MyISAMの場合にはレコードは独立した"テーブル名.MYD"という名前のファイルに格納されている。一般的にはセカンダリインデックスとレコードの順序には相関がないので、セカンダリインデックスの順序でレコードをフェッチすると、レコードへのアクセスはランダムアクセスになってしまうのだ。レコードがメモリ上のバッファにすべて存在する場合にはランダムアクセスでも問題にならないが、ディスク上にある場合にはランダムアクセスは効率が悪い。それをシーケンシャルアクセスに変更するのがMRR最適化なのだ。

 MySQLでは今回が初登場であるが、既に様々なRDBMSで当たり前のように実装されている最適化アルゴリズムある。ストレージエンジンを前提とした標準的なアルゴリズムを実装したというところがミソである。

 MRRでは、ファイル(ディスク上のオブジェクト)へのアクセスを最適化するものなので、全てのデータがメモリに乗っているような場合には意味がない。最近はそのような環境も増えてきたので、MRRに対する過度の期待は禁物である。

Batched Key Access Join

 Batched Key Access Join(以下BKAJ)は、BNLJと非常によく似たアルゴリズムである。BKAJはBNLJと同じく、駆動表から条件にマッチするレコードをフェッチして、いったんJOINバッファにためる。動作が異なるのはそこから先で、BNLJでは内部表に対してスキャンを行うのに対して、BKAでは内部表に対してMRR最適化を用い、インデックスを用いてアクセスするという点だ。

 これにより、InnoDBやMyISAMでは内部表へのアクセスがランダムアクセスではなくシーケンシャルアクセスになる。BNLJとは違って、内部表はすべてスキャンされるのではなく、必要なレコードだけがフェッチされるようになる。しかもデータファイル内におけるレコードの並び順でである。ただし、実装方法としてBNLJとBKAJは似ているが、両者のパフォーマンスを比較するのはナンセンスである点に留意したい。元々BKAJは、内部表へのアクセスにおいてインデックスが利用できる場合のアルゴリズムなので、インデックスが利用できないために内部表をスキャンする必要があるBNLJとはユースケースが全く異なる。BKAJと比較すべきなのは、内部表へのアクセスにインデックスが利用できるNLJなのである。

 BKAJはMRRを前提としたアルゴリズムなので、MRR同様すべてのレコードがメモリ内に収まっている場合には性能向上は期待できないだろう。反対に、データ量が巨大でバッファの何倍もあるような場合には、BKAJによって劇的な性能向上が見込めることになる。

 また、BKAJはMySQL Clusterのようにデータがリモートのホストに格納されている場合にはかなりの効果が期待できる。インデックスの値をまとめてストレージエンジンに渡し、それに対応したレコードをまとめて受け取ることによって、ネットワークのラウンドトリップが少なくなるからだ。ただし、現行執筆時点での最新正式バージョン(MySQL Cluster 7.1)および最新の開発版(MySQL Cluster 7.2)では、残念ながらまだBKAJは利用できない。将来のバージョンで、SQLノードとしてMySQL 5.6以降のバージョンが採用されるのを待とう。

次のページ
まとめ

この記事は参考になりましたか?

  • Facebook
  • Twitter
  • Pocket
  • note
MySQLチューニング虎の巻連載記事一覧

もっと読む

この記事の著者

奥野 幹也 (オクノ ミキヤ)

※プロフィールは、執筆時点、または直近の記事の寄稿時点での内容です

この記事は参考になりましたか?

この記事をシェア

EnterpriseZine(エンタープライズジン)
https://enterprisezine.jp/article/detail/3606 2012/02/10 18:19

Job Board

PR

おすすめ

アクセスランキング

アクセスランキング

イベント

EnterpriseZine(エンタープライズジン)編集部では、情報システム担当、セキュリティ担当の方々向けに、EnterpriseZine Day、Security Online Day、DataTechという、3つのイベントを開催しております。それぞれ編集部独自の切り口で、業界トレンドや最新事例を網羅。最新の動向を知ることができる場として、好評を得ています。

2022年6月28日(火)13:10

新規会員登録無料のご案内

  • ・全ての過去記事が閲覧できます
  • ・会員限定メルマガを受信できます

メールバックナンバー

アクセスランキング

アクセスランキング