Database」、オープンソースRDBMSの両雄である「PostgreSQL」、「MySQL」には、それぞれのRDBMSの個性を活かせる適用領域があります。Database」、「PostgreSQL」、「MySQL」の特長を整理し、適材適所で活用するヒントを探ります。Plus(ポストグレスプラス)」、Webサービスのシステムにて採用されているRDBMSとしてはデファクト・スタンダードになりつつあるコミュニティ版の「MySQL(マイエスキュエル)」と、そのブランチである「MariaDB(マリアディビー)」のサポートを同年12月から開始しています。DWH専用に設計されたDBMSソフトウェアとして、2012年3月よりCalpont社の「InfiniDB(インフィニディビー)」の取り扱いを開始しました。Database」、オープンソースRDBMSの両雄である「PostgreSQL」、「MySQL」のサポート・サービスを開始したことで、それぞれの適用領域に関するご相談をいただくようになりました。開発要員や運用人員のスキルの有無、移行コスト、開発コスト、同じ業界やシステムでの採用事例の有無、社内標準RDBMSであるかどうかなど、多種多様な採用ポイントがあります。Database」、「PostgreSQL」、「MySQL」の特長を整理します。アクセス・パス「アクセス・パス」とはデータベースからデータを取り出す「経路」のことです。図1のようにRDBMSでは、アプリケーションから発行されたSQLを「解析」して「実行」しており、「解析」では、SQLの構文チェック、参照されているオブジェクトの存在チェックが行われた後、データにアクセスするための経路であるアクセス・パスが決定されます。友人との待ち合わせ場所まで、どの経路を使って、どのような交通手段で移動するのが一番効率的かを考えるのと似ています。代表的なアクセス・パスには、表の行データを1行1行読み取って、条件に合うデータを取り出す「全表スキャン」と、本の巻末の索引と同じく、表の各列のデータとそのデータの格納位置を示すアドレス情報を持つ「索引」を利用した「索引スキャン」があります。現実世界で目的地に行く場合も、交通状況や交通機関の遅延情報などを考慮して目的地までのルートを探すように、RDBMSもテーブルに格納されている行数や、列に格納されている値の種類、値の分布などの統計情報と呼ばれる情報を利用して、表に対して最もコストの低いと考えられるアクセス・パスを選択します。このアクセス・パスは各RDBMSで実装されている種類ごとに異なり、また、各RDBMSのデータを保持する物理構造の違いと相まって、適用領域を考える際にポイントとなります。全表スキャン「全表スキャン」は表のすべての行が順に読み取られ、選択基準を満たす行が取り出される表データのスキャンです。シンプルな処理であるため、表データを格納する物理構造により特長が生まれます。データ格納の最小単位であるデータ・ブロック(ページ)の構造、およびデータ・ブロックの連続した塊で表データが構成される構造が類似していますが、ブロックを読み取る方式が異なります。マルチ・ブロック読み込みと呼ばれる、複数のデータ・ブロックを1回の読み込みで処理することで、全表スキャンでの読み込み回数を軽減する機能が実装されています。MySQLのデフォルト・ストレージ・エンジンである「Innodb」では、表データはクラスタ・インデックス構造として格納されています。図2のようにクラスタ・インデックス構造では、表の主キー(主キーが存在しなければ、行を一意に識別する内部行ID)によって、いわゆるB-Tree構造のリーフ・ブロックに相当するデータ・ブロックに、主キーと行データが格納されています。全表スキャンでは、B-Tree構造のルート・ブロック、ブランチ・ブロックからリーフ・ブロックに存在する行データを読み込む方式となります。ある表に対して連続したブロックを読み込んだ場合、全表スキャンであることを認識し、後続のブロックを先読みしてバッファ・キャッシュに配置する機能を有しています。以上のように、どのRDBMSでもアクセス・パスとして全表スキャンは実装されていますが、物理構造とデータ・ブロックの読み取り方式に違いを考慮する必要があります。DatabaseとのSQL互換、PL/SQL互換、SQLヒントや待機イベント、GUIツールなどエンタープライズ向け機能が強化されている。AB社が中心となって開発しているMySQLブランチであるRDBMS。GPLとして提供されている。一意索引スキャン索引には主キーや一意キーに作成される列値の一意性を保障する一意索引と、一意性を保証しない非一意索引があります。場合、いずれの索引も表とは異なるオブジェクトとして作成されます。図3のように、主キーを利用した一意索引スキャンの場合、一意索引のリーフ・ブロックから物理的な行の格納位置を示すアドレス情報を参照し、表を構成するデータ・ブロックにアクセスします。MySQLの場合、行データは主キーによるクラスタ・インデックスのリーフ・ブロックに格納されているため、主キーを利用した一意索引スキャンでは、クラスタ・インデックスから行データを直接参照することができます。PostgreSQLでは、一意索引のルート、ブランチ、リーフの各ブロックと表のデータ・ブロックの合計4ブロックへのアクセスが必要であるのに対し、MySQLではクラスタ・インデックスのルート、ブランチ、リーフの3ブロックで行データにアクセスできるため、主キーを利用した一意索引スキャンでは、それぞれの特長を考慮する必要があります。動作は一意索引スキャンと同様です。非一意索引のリーフ・ブロックから物理的な行の格納位置を示すアドレス情報を参照し、表を構成するデータ・ブロックにアクセスします。非一意索引の場合、WHERE句に等価条件が指定された場合でも、非一意索引のリーフ・ブロックからは複数の行アドレスが参照されるため、表のデータ・ブロックに対しては、複数の行アドレス情報によるアクセスが繰り返されることになります。MySQLの場合、図4のように非一意索引のリーフ・ブロックには、索引を作成した列の値と、主キー値が格納されています。非一意索引スキャンでは、非一意索引のリーフ・ブロックから該当する主キー値が参照され、クラスタ・インデックス構造である表データに主キーでアクセスされます。Database、PostgreSQLでは、非一意索引のルート、ブランチ、リーフの各ブロックと表のデータ・ブロックの合計4ブロックへのアクセスが基本となるのに対し、MySQLでは非一意索引のルート、ブランチ、リーフ、クラスタ・インデックスのルート、ブランチ、リーフの6ブロックで行データにアクセスすることになるため、非一意索引スキャンではこの特長を考慮する必要があります。図4:非一意索引の範囲走査その他、索引を利用したアクセス・パスとしては、図5の「全索引スキャン」「高速全索引スキャン」「索引スキップ・スキャン」が存在しますが、各RDBMSで実装されているアクセス・パスに違いがあるため、RDBMS選定時には図4のアクセス・パスを選択しないと性能要件を満たせないSQLが存在するかどうかの確認を行うのが良いと思われます。Pushdownアルゴリズムが実装され、複合索引(コンポジット索引)によって評価される範囲が広がり、従来バージョンと比べてフェッチする行数が少なくなるなど、機能強化が続いているので、それぞれのRDBMSの最新バージョンの情報を確認することも重要です。表結合とは、ある表の行データと関連する、もう一方の表の行データをつなぎ合わせる処理のことです。結合対象となる表同士には、結合のキーとなる情報を格納する列が存在し、結合キーの列値が一致する行データ同士を、あたかも1つの表の行データのように扱うことができます。結合の順番は、図7のように1つの表(行ソース)と、もう一方の表(行ソース)を結合し、その結果の行ソースを別の表(行ソース)と結合する手順を繰り返します。表結合の方法には図6のようにネスティッド・ループ結合、ソート・マージ結合、ハッシュ結合があります。ネスティッド・ループ結合は、結合の駆動軸となる外側の表の行データに一致する行を内側の表から見つけ出す方法です。外側の表の行データに一致する行を内側の表から見つける走査を繰り返すため、外側の表には、件数の少ない表を配置して繰り返し回数を少なくすること、内側の表の結合条件となる列には索引を作成し、索引スキャンを採用することがネスティッド・ループ結合を効率化させるポイントです。ソート・マージ結合やハッシュ結合は、主に結合結果の行ソース同士を結合させる必要が生じるなど、索引を利用した結合を行うことができない場合や、索引を利用する結合が適切ではない場合に、ネスティッド・ループ結合より効率的に結合を行う方法です。ネスティッド・ループ結合、ソート・マージ結合、ハッシュ結合が実装されていますが、MySQLはネスティッド・ループ結合のみが実装されているため、結合結果の行ソース同士を結合しなければならない多数表の結合の場合、結合の種類に考慮したSQL設計も必要となってきます。図8:結合方法と結合順序評価なお、表の結合順序を算出する際には、表や索引の統計情報と結合条件や検索条件などを考慮して導出される予想行数の小さい順に結合順序を評価をしたり、結合順序の評価過程では、今までの最良の計算済みコストと、現在評価中の結合順序コストを比較し、結合順序の評価途中に最良コストを超える場合は、その組み合わせはそれ以上評価しないなど、余分な評価を実施しない仕組みが実装されています。Database、PostgreSQL、MySQL共に、結合順序評価の組み合わせ数を制御するパラメータが実装されており、多数表の結合におけるSQL解析時間をコントロールすることができます。システム変数を変更するのが望ましいと思います。PostgreSQL、MySQLの違いを説明しましたが、同じRDBMSでも個性があり、適用領域が少しずつ異なることがわかると思います。MySQLのinnodbストレージ・エンジンでは、表は主キーを用いたクラスタ・インデックス構造であるため、主キーによるデータ操作に向いており、その特性を踏まえてデータ・モデルとSQLを設計すると期待した効果が得られます。このように、アクセス・パスでの適材適所を考えた場合、アプリケーション側で、採用するデータベースの特長を考慮してデータモデルやSQLを構成できるかがポイントとなります。アプリケーション側でRDBMSの特長を考慮することが難しい場合は、アクセスパスの選択肢が多く、また、それを適切に選択するオプティマイザを有しているRDBMSが利用しやすいのではないでしょうか。隆ビジネスインフラ技術本部1995年入社。現在は「データベースのアシスト」を目指した事業戦略やプロダクトマーケティングを担当している。趣味:フットサル、ゴルフ、スキー、キャンプ、子供のサッカーチームのカメラマン担当。俊洋ビジネスインフラ技術本部2006年入社。データベース・システムの構築や運用トラブルの解決といった業務を経験し、その後新製品の検証やソリューションの立ち上げを経てエバンジェリストへ。2016年にクラウド事業を立ち上げ、現在はクラウドとデータベースの二足の草鞋を履いている。最後までご覧いただきありがとうございました。本記事でご紹介した製品・サービスに関するコンテンツをご用意しています。この記事の他にも、IT技術情報に関する執筆記事を多数公開しておりますのでぜひご覧ください。2019の様子を紹介します。SQLを拡張したプログラミング言語であるPL/SQLを理解し、活用していくための実践講座です。Databaseで発生するトラブルを「どんな方法で」「どのように」解決していくか。データベースの運用管理において、より円滑に業務を進めるために必要なノウハウを紹介していきます。