TOP>企業情報>コラム>技術情報>「性能要件」に深く関係する「アクセス・パス」の観点から、「Oracle Database」「PostgreSQL」「MySQL」の特長を整理しよう!

「性能要件」に深く関係する「アクセス・パス」の観点から、「Oracle Database」「PostgreSQL」「MySQL」の特長を整理しよう!

データベース

商用RDBMSのデファクト・スタンダードである「Oracle Database」、オープンソースRDBMSの両雄である「PostgreSQL」、「MySQL」には、それぞれのRDBMSの個性を活かせる適用領域があります。RDBMSの「性能要件」に深く関係する「アクセス・パス」を題材に、「Oracle Database」、「PostgreSQL」、「MySQL」の特長を整理し、適材適所で活用するヒントを探ります。

「性能要件」に深く関係する「アクセス・パス」の観点から、「Oracle Database」「PostgreSQL」「MySQL」の特長を整理しよう!


アシストでは1987年に取り扱いを開始した「Oracle Database」に加え、2009年10月より「PostgreSQL(ポストグレスキュエル)」、2011年10月にはPostgreSQLをベースとしOracle Databaseとの互換性機能を有する「Postgres Plus(ポストグレスプラス)」、Webサービスのシステムにて採用されているRDBMSとしてはデファクト・スタンダードになりつつあるコミュニティ版の「MySQL(マイエスキュエル)」と、そのブランチである「MariaDB(マリアディビー)」のサポートを同年12月から開始しています。また、DWH専用に設計されたDBMSソフトウェアとして、2012年3月よりCalpont社の「InfiniDB(インフィニディビー)」の取り扱いを開始しました。

RDBMS領域で、商用RDBMSのデファクト・スタンダードである「Oracle Database」、オープンソースRDBMSの両雄である「PostgreSQL」、「MySQL」のサポート・サービスを開始したことで、それぞれの適用領域に関するご相談をいただくようになりました。RDBMSを採用する要因は、機能要件、非機能要件以外に、開発要員や運用人員のスキルの有無、移行コスト、開発コスト、同じ業界やシステムでの採用事例の有無、社内標準RDBMSであるかどうかなど、多種多様な採用ポイントがあります。今回は、「性能要件」に深く関係する「アクセス・パス」の観点から、「Oracle Database」、「PostgreSQL」、「MySQL」の特長を整理します。

アクセス・パス


「アクセス・パス」とはデータベースからデータを取り出す「経路」のことです。図1のようにRDBMSでは、アプリケーションから発行されたSQLを「解析」して「実行」しており、「解析」では、SQLの構文チェック、参照されているオブジェクトの存在チェックが行われた後、データにアクセスするための経路であるアクセス・パスが決定されます。

図1:アクセス・パスの決定

図1:アクセス・パスの決定

例えば、友人との待ち合わせ場所まで、どの経路を使って、どのような交通手段で移動するのが一番効率的かを考えるのと似ています。

代表的なアクセス・パスには、表の行データを1行1行読み取って、条件に合うデータを取り出す「全表スキャン」と、本の巻末の索引と同じく、表の各列のデータとそのデータの格納位置を示すアドレス情報を持つ「索引」を利用した「索引スキャン」があります。また、現実世界で目的地に行く場合も、交通状況や交通機関の遅延情報などを考慮して目的地までのルートを探すように、RDBMSもテーブルに格納されている行数や、列に格納されている値の種類、値の分布などの統計情報と呼ばれる情報を利用して、表に対して最もコストの低いと考えられるアクセス・パスを選択します。

このアクセス・パスは各RDBMSで実装されている種類ごとに異なり、また、各RDBMSのデータを保持する物理構造の違いと相まって、適用領域を考える際にポイントとなります。

全表スキャン


「全表スキャン」は表のすべての行が順に読み取られ、選択基準を満たす行が取り出される表データのスキャンです。シンプルな処理であるため、表データを格納する物理構造により特長が生まれます。図2のように、Oracle Database と、PostgreSQL は、データ格納の最小単位であるデータ・ブロック(ページ)の構造、およびデータ・ブロックの連続した塊で表データが構成される構造が類似していますが、ブロックを読み取る方式が異なります。PostgreSQLはシングル・ブロック読み込みのため1回の読み込み命令で1つのデータ・ブロックにアクセスしますが、Oracle Database ではマルチ・ブロック読み込みと呼ばれる、複数のデータ・ブロックを1回の読み込みで処理することで、全表スキャンでの読み込み回数を軽減する機能が実装されています。一方、MySQLのデフォルト・ストレージ・エンジンである「Innodb」では、表データはクラスタ・インデックス構造として格納されています。図2のようにクラスタ・インデックス構造では、表の主キー(主キーが存在しなければ、行を一意に識別する内部行ID)によって、いわゆるB-Tree構造のリーフ・ブロックに相当するデータ・ブロックに、主キーと行データが格納されています。全表スキャンでは、B-Tree構造のルート・ブロック、ブランチ・ブロックからリーフ・ブロックに存在する行データを読み込む方式となります。ただし、ある表に対して連続したブロックを読み込んだ場合、全表スキャンであることを認識し、後続のブロックを先読みしてバッファ・キャッシュに配置する機能を有しています。以上のように、どのRDBMSでもアクセス・パスとして全表スキャンは実装されていますが、物理構造とデータ・ブロックの読み取り方式に違いを考慮する必要があります。

図2:全表スキャンの比較

図2:全表スキャンの比較

表記:各EXPLAINコマンドの表示結果から

PPAS
EnterpriseDB社が提供するPostgreSQLベースの商用RDBMS「Postgres Plus Advanced Server」の略。PostgreSQLベースとし、Oracle DatabaseとのSQL互換、PL/SQL互換、SQLヒントや待機イベント、GUIツールなどエンタープライズ向け機能が強化されている。

MariaDB
Monty Program AB社が中心となって開発しているMySQLブランチであるRDBMS。GPLとして提供されている。

一意索引スキャン


索引には主キーや一意キーに作成される列値の一意性を保障する一意索引と、一意性を保証しない非一意索引があります。Oracle Database と PostgreSQL の場合、いずれの索引も表とは異なるオブジェクトとして作成されます。図3のように、主キーを利用した一意索引スキャンの場合、一意索引のリーフ・ブロックから物理的な行の格納位置を示すアドレス情報を参照し、表を構成するデータ・ブロックにアクセスします。一方、MySQLの場合、行データは主キーによるクラスタ・インデックスのリーフ・ブロックに格納されているため、主キーを利用した一意索引スキャンでは、クラスタ・インデックスから行データを直接参照することができます。つまり、Oracle Database と、PostgreSQLでは、一意索引のルート、ブランチ、リーフの各ブロックと表のデータ・ブロックの合計4ブロックへのアクセスが必要であるのに対し、MySQLではクラスタ・インデックスのルート、ブランチ、リーフの3ブロックで行データにアクセスできるため、主キーを利用した一意索引スキャンでは、それぞれの特長を考慮する必要があります。

図3:主キーを利用した一意索引スキャン

図3:主キーを利用した一意索引スキャン

非一意索引スキャン


非一意索引スキャンの場合も、図4のようにOracle Database と PostgreSQL の動作は一意索引スキャンと同様です。非一意索引のリーフ・ブロックから物理的な行の格納位置を示すアドレス情報を参照し、表を構成するデータ・ブロックにアクセスします。ただし、非一意索引の場合、WHERE句に等価条件が指定された場合でも、非一意索引のリーフ・ブロックからは複数の行アドレスが参照されるため、表のデータ・ブロックに対しては、複数の行アドレス情報によるアクセスが繰り返されることになります。MySQLの場合、図4のように非一意索引のリーフ・ブロックには、索引を作成した列の値と、主キー値が格納されています。非一意索引スキャンでは、非一意索引のリーフ・ブロックから該当する主キー値が参照され、クラスタ・インデックス構造である表データに主キーでアクセスされます。つまり、Oracle Database、PostgreSQLでは、非一意索引のルート、ブランチ、リーフの各ブロックと表のデータ・ブロックの合計4ブロックへのアクセスが基本となるのに対し、MySQLでは非一意索引のルート、ブランチ、リーフ、クラスタ・インデックスのルート、ブランチ、リーフの6ブロックで行データにアクセスすることになるため、非一意索引スキャンではこの特長を考慮する必要があります。

図4:非一意索引の範囲走査

図4:非一意索引の範囲走査

その他、索引を利用したアクセス・パスとしては、図5の「全索引スキャン」「高速全索引スキャン」「索引スキップ・スキャン」が存在しますが、各RDBMSで実装されているアクセス・パスに違いがあるため、RDBMS選定時には図4のアクセス・パスを選択しないと性能要件を満たせないSQLが存在するかどうかの確認を行うのが良いと思われます。

また、複数の列に索引を作成した複合索引(コンポジット索引)を利用した検索を行う場合、Oracle DatabaseとPostgreSQLと同様にMySQL5.6ではIndex Condition Pushdownアルゴリズムが実装され、複合索引(コンポジット索引)によって評価される範囲が広がり、従来バージョンと比べてフェッチする行数が少なくなるなど、機能強化が続いているので、それぞれのRDBMSの最新バージョンの情報を確認することも重要です。

図5:その他の索引を利用したアクセス・パス

図5:その他の索引を利用したアクセス・パス

表結合の方法


表結合とは、ある表の行データと関連する、もう一方の表の行データをつなぎ合わせる処理のことです。結合対象となる表同士には、結合のキーとなる情報を格納する列が存在し、結合キーの列値が一致する行データ同士を、あたかも1つの表の行データのように扱うことができます。結合の順番は、図7のように1つの表(行ソース)と、もう一方の表(行ソース)を結合し、その結果の行ソースを別の表(行ソース)と結合する手順を繰り返します。また、表結合の方法には図6のようにネスティッド・ループ結合、ソート・マージ結合、ハッシュ結合があります。

ネスティッド・ループ結合は、結合の駆動軸となる外側の表の行データに一致する行を内側の表から見つけ出す方法です。外側の表の行データに一致する行を内側の表から見つける走査を繰り返すため、外側の表には、件数の少ない表を配置して繰り返し回数を少なくすること、内側の表の結合条件となる列には索引を作成し、索引スキャンを採用することがネスティッド・ループ結合を効率化させるポイントです。ソート・マージ結合やハッシュ結合は、主に結合結果の行ソース同士を結合させる必要が生じるなど、索引を利用した結合を行うことができない場合や、索引を利用する結合が適切ではない場合に、ネスティッド・ループ結合より効率的に結合を行う方法です。Oracle Database 、PostgreSQL は、ネスティッド・ループ結合、ソート・マージ結合、ハッシュ結合が実装されていますが、MySQLはネスティッド・ループ結合のみが実装されているため、結合結果の行ソース同士を結合しなければならない多数表の結合の場合、結合の種類に考慮したSQL設計も必要となってきます。

図6:結合の順序

図6:結合の順序

図7:結合方法

図7:結合方法

図8:結合方法と結合順序評価

図8:結合方法と結合順序評価

なお、表の結合順序を算出する際には、表や索引の統計情報と結合条件や検索条件などを考慮して導出される予想行数の小さい順に結合順序を評価をしたり、結合順序の評価過程では、今までの最良の計算済みコストと、現在評価中の結合順序コストを比較し、結合順序の評価途中に最良コストを超える場合は、その組み合わせはそれ以上評価しないなど、余分な評価を実施しない仕組みが実装されています。また、Oracle Database、PostgreSQL、MySQL共に、結合順序評価の組み合わせ数を制御するパラメータが実装されており、多数表の結合におけるSQL解析時間をコントロールすることができます。なお、MySQLの場合、optimizer_search_depth システム変数のデフォルト値は62となっており、62表までの結合順序(62の階乗分)を評価するため、多数表を結合しなければならない場合には図8を参考にoptimizer_search_depth システム変数を変更するのが望ましいと思います。

以上のように、主なアクセス・パスと結合方法だけにフォーカスして、Oracle Database 、PostgreSQL、MySQLの違いを説明しましたが、同じRDBMSでも個性があり、適用領域が少しずつ異なることがわかると思います。

例えば、MySQLのinnodbストレージ・エンジンでは、表は主キーを用いたクラスタ・インデックス構造であるため、主キーによるデータ操作に向いており、その特性を踏まえてデータ・モデルとSQLを設計すると期待した効果が得られます。

このように、アクセス・パスでの適材適所を考えた場合、アプリケーション側で、採用するデータベースの特長を考慮してデータモデルやSQLを構成できるかがポイントとなります。アプリケーション側でRDBMSの特長を考慮することが難しい場合は、アクセスパスの選択肢が多く、また、それを適切に選択するオプティマイザを有しているRDBMSが利用しやすいのではないでしょうか。

  • Postgres Plus は、EDB Postgres の旧製品名です。


執筆者紹介

岸和田 隆

岸和田 隆(Takashi Kishiwada)

株式会社アシスト データベース技術本部

アシスト入社後、Oracle Database の研修講師、フィールド・ サポート、新バージョンの検証を経て、2007年 自社ブランド 「DODAI」の準アプライアンス製品の企画・開発、2009年 PostgreSQL、2011年 EDB Postgres、MySQL / MariaDB の事業立上を担当。 現在は「データベースのアシスト」を目指した活動を行っている。

岸和田の紹介記事はこちら



関 俊洋

関 俊洋(Toshihiro Seki)

株式会社アシスト データベース技術本部

2006年、株式会社アシスト入社。データベース・システムの構築や運用トラブルの解決といったフィールド・サポート業務を経験し、その後は新製品の検証やハードウェアとデータベースを組み合わせたソリューション(DODAI)の立ち上げに従事。現在はデータベースの価値や魅力を伝えるための執筆・講演活動を行っている。『SQL逆引き大全363の極意』共著。

関の紹介記事はこちら

関連製品/サービス


Facebookで情報をお届けしています

Facebookでは、アシストの「今」を週3回のペースでお届けしています。「めげない、逃げない、あまり儲けない」を合言葉に日々頑張っておりますので、応援よろしくお願いします。



ページの先頭へ戻る