PostgreSQLとMySQL、使うならどっち? データベース専門家が8つの視点で徹底比較!

オープンソースのデータベースとしてよく比較されるPostgreSQLとMySQL。どんな長所・短所があるのでしょう? それぞれの専門家による対談で明らかにします。

PostgreSQLとMySQL、使うならどっち? データベース専門家が8つの視点で徹底比較!

エンジニアとして働いていると必ず直面する悩み。それは、「どのリレーショナル・データベース(以下、RDB)を選ぶのが最善なのか?」です。

RDBごとに長所と短所は異なっています。そのため自社サービスにマッチしないRDBを選んでしまうと、それがボトルネックとなり開発・運用にトラブルが生じるケースは少なくありません。

なかでもよく比較検討されるのが、PostgreSQLMySQL。ともにオープンソースRDBのデファクトスタンダードであり、高い性能と数多くの機能を持っています。

では、両者は具体的にどのような長所・短所があるのでしょうか。それを徹底解剖すべく、PostgreSQLの専門家である澤田雅彦さんとMySQLの専門家である田中翼さんの対談を実施。各機能ごとに特徴を比較しました。

RDBについて日本トップレベルの知見を持つ2人の意見。ぜひ、PostgreSQLとMySQLを選定する際の参考にしてください!

澤田雅彦(さわだ・まさひこ) 1@sawada_masahiko (写真・左)
2012年、NTTデータに入社。以降、PostgreSQLに関する業務に従事し、主にPostgreSQLの本体開発、技術支援および、国内外問わずさまざまなカンファレンスにて講演を行っている。2016年より、NTT OSSセンタに勤務。PostgreSQLコミュニティでは、Contributorとしてレプリケーション、VACUUM、分散トランザクション機能の開発やバグ修正を通してコア開発に貢献。
田中翼(たなか・つばさ) 2@yoku0825 (写真・右)
LinuxサーバーのオペレーターからMySQLの代理店を経て、GMOメディアでMySQL専門のDBAとしてキャリアを積む。主な著書に『MySQL即効クエリチューニング』(インプレス、2016年)。MySQL分野のOracle ACEで「MySQL 5.7 Community Contributor Award」を受賞したピンクのおとうふ

【比較ポイント(1)】DDL操作のノンブロッキング

―― 本日はよろしくお願いします。まずDDL(データ定義言語)について比較したいと思います。MySQLから教えてください。

田中 MySQLは、多くのDDL操作をノンブロッキング(トランザクション中でもテーブルへのブロックがかからない)で実行できるというメリットがあります。この機能はMySQLバージョン5.6から実装されました。
 また、対象のカラムのみをスキャンするようなALTER TABLE(カラム名を変更する、カラムを追加するなど)の場合、テーブルをゼロからリビルドしないため処理速度が速く、サーバー全体の負荷が低減できるという特徴もあります。

―― 一方のPostgreSQLは、ALTER TABLEなどのDDL操作はノンブロッキングではないのでしょうか?

澤田 そうですね。どのようなDDL文を発行するかによっても変わってきますが、たとえばカラムを追加するなどテーブルを書き換える操作は、テーブルへのブロックが発生してしまい、参照もできなくなってしまいます。

―― とはいえ、本番環境のDBに対してALTER TABLEをかけたいケースもあるかと思います。その際には、どのような方法を取るべきなのでしょうか?

澤田 pg_repackというメンテナンス用の外部ツールが使用されることが多いです。それを使えば、REINDEXや一部のALTER TABLE操作を最小限のロックで実行できます。

3pg_repack -- PostgreSQLデータベースのテーブルを最小限のロックで再編成します4

―― PostgreSQLを保守・運用している方は、そのツールの存在をぜひ覚えておきたいですね。

【比較ポイント(2)】DML文のパフォーマンス

―― 次は、各種のDML(データ操作言語)を比較していきたいと思います。まず、SELECT文ですが。

田中 シンプルなSELECTなら、MySQLもPostgreSQLもそれほど変わらないと思います。いい勝負なんじゃないでしょうか。

澤田 そうですね。SELECTはあまり変わらないです。

田中 ただし、大量データのソートが必要なSELECT(ORDER BYをしたうえで、テーブルの全データを取得するなど)はMySQLだと遅くなってしまいます。
 なぜなら、PostgreSQLと比較するとMySQLはソートのアルゴリズムがそれほど優れていないためです。MySQLは、大量データをソートすることを、基本的にユースケースとして想定していません。

―― どのような条件のSELECTなら、MySQLは強みを発揮するのですか?

田中 MySQLは、新規10件とか100件のデータ(TOP n レコード)を取得するような、たとえばTwitterのようなユースケースに特化しています。そういった場面では、PostgreSQLよりも速いです。

―― 他のDML文についてはどうですか? たとえばUPDATEは。

田中 UPDATEは、MySQLの方がパフォーマンスに優れていますね。

澤田 私もそう思います。

―― それはなぜでしょう?

澤田 PostgreSQLは追記型アーキテクチャといって、UPDATEする際にはINSERTに近い処理が実行されています。どういうことかというと、変更前の行に削除フラグのようなものを立てたうえで、変更後のデータを持った新しい行を追加しているんです。

田中 一方でMySQLは、UPDATE対象となる行の値を直接上書きしています。文字通りに“更新”しているんです。

―― そのアーキテクチャであれば確かにMySQLの方がUPDATE処理は早くなりそうですね。それでは、DELETEに関してはどうでしょうか?

田中 かつて、MySQLにはDELETEが遅いという欠点がありました。これは、データ削除後にセカンダリーインデックス(クラスタインデックス以外のすべてのインデックス)を同期処理で貼り直しており、その処理に時間がかかっていたためです。
 ですが、バージョン5.5でかなり解消されました。セカンダリーインデックスの非同期のチェンジバッファ(セカンダリーインデックスエントリへの変更をバッファリングしておき、サーバがアイドル状態にあるときなどに変更内容をマージする仕組み)が効くようになったため、以前ほど「DELETEが遅い」ということはなくなりました。

5

【比較ポイント(3)】テーブル結合(JOIN)のアルゴリズム

―― 次はテーブル結合を比較します。よく使われるテーブル結合のアルゴリズムには「ネステッドループ結合(Nested Loop Join)」「ハッシュ結合(Hash Join)」「ソートマージ結合(Sort Merge Join)」の3種類がありますが。

田中 MySQLは、基本的にネステッドループ結合しかサポートしていません。なぜなら、MySQLは「複雑なアルゴリズムはなるべくサポートしない」という設計思想に基づいているからです。

―― どうしてMySQLはそういう設計思想になったのですか?

田中 Webアプリケーションに使われるようになる前に、MySQLはもともと組み込み系のシステムで使われていたことに起因しています。
 組み込み機器の非常に容量が小さいディスクやメモリの中でDBを稼働させる必要があり、複雑なアルゴリズムをなるべくそぎ落とす方針で設計されてきたんです。

―― 納得ですね。一方のPostgreSQLはどうですか?

澤田 PostgreSQLは、3種類ともサポートしています。

―― それぞれの結合パターンは、どのようなユースケースに向いているのでしょうか?

澤田 結合対象のデータ量が多いときには、ハッシュ結合やソートマージ結合を使った方がいいと思います。そのデータが既にソートされている場合にはソートマージ結合の方がよく、そうでなければハッシュ結合がおすすめです。
 ネステッドループ結合が最善の選択肢となるのは、結合されるテーブルどちらかのデータ量が少なくてもう一方が多いようなとき。もしくはインナーテーブル側がインデックススキャンを使える場合などですね。これは、MySQLにおいても同様です。

6

【比較ポイント(4)】トランザクション処理の分離レベル

―― 次は、トランザクション処理についてお聞きします。PostgreSQLとMySQLでは、それぞれのデフォルトのトランザクション分離レベル(トランザクション処理が複数同時に実行された場合に、どれほどのデータ一貫性・正確性で実行するかを定義したもの)が異なっているという話を聞いたことがあります。

田中 そうですね。MySQLは、デフォルトがREPEATABLE-READとなっています。この方式だと、読み取り対象のデータが途中で他のトランザクションから変更されてしまう心配はありません。
 ただし、ファントムリード(並行して動作している他のトランザクションが追加したデータが途中で見えてしまう現象)が起こる可能性があります。MySQLでは、このファントムリードを避けるため、ネクストキーロックという仕組みを採用しています。

―― それはどのようなものですか?

田中 トランザクションが走っている最中にレコードが増えないよう、主キーのインクリメント先の値までロックをかけるというものです。この仕組みによってデータの堅牢性は保たれるのですが、同時にこれが原因で意図せぬロックがかかり、トラブルの原因になってしまうこともあります。
 たとえば、SELECT FOR UPDATEなどでWHERE句に「<(不等号)」を使用し、「IDが10以上」のレコードを検索したとします。すると、10以上のキーが全てロックされてしまうんです。
 こうなってしまうと新たな主キーが生成できず、INSERTできなくなってしまいます。この仕様はけっこうハマりどころなので、注意しておいた方がいいでしょう。
 ロック競合を減らすため、トランザクション分離レベルを、より低いREAD-COMMITTED(常にコミット済みの最新データを読み取る形式)に変更して運用するケースもあります。

7

―― PostgreSQLでは、デフォルトのトランザクション分離レベルは何ですか?

澤田 READ-COMMITTEDです。この方式の場合、ファントムリードやノンリピータブルリード(同じトランザクション中でも同じデータを読み込むたびに値が変わってしまう現象)が起こる可能性があるため、運用ではその点に気をつける必要があります。
 また、PostgreSQLではトランザクション分離レベルをREPEATABLE-READに変更したとしても、ネクストキーロックを取らず、違う方法でファントムリードを防いでいます。そのため、ロック競合を防ぎやすいという点はMySQLよりも優れているかもしれません。

8

【比較ポイント(5)】ストアドプロシージャ、トリガー

エンジニアHubに会員登録すると
続きをお読みいただけます(無料)。
登録のメリット
  • すべての過去記事を読める
  • 過去のウェビナー動画を
    視聴できる
  • 企業やエージェントから
    スカウトが届く