イノベーション エンジニアブログ


株式会社イノベーションのエンジニアたちの技術系ブログです。ITトレンド・List Finderの開発をベースに、業務外での技術研究などもブログとして発信していってます!


このエントリーをはてなブックマークに追加

PostgreSQLとMySQL、使うならどっち? データベース専門家が8つの視点で徹底比較!という記事を読んで勉強したこと

SREチームの城田です。

今回は、
下記のエン・ジャパンさんのブログが私にとってとても興味深く、
大変勉強になりましたので、自分目線でまとめをしてみました。

PostgreSQLとMySQL、使うならどっち? データベース専門家が8つの視点で徹底比較!
https://employment.en-japan.com/engineerhub/entry/2017/09/05/110000

また、
上記はインタビュー形式での記事でした為、
ポイントとなる部分を比較できるよう、自身の解釈で一旦表形式に落とし込みを行いました。

比較表

PostgreSQL MySQL 補足

DDL操作のブロッキング

発生するが、pg_repackという外部ツールを使えば、REINDEXや一部のALTER文を最小限のロックで実行可。

バージョン5.6から、多くのDDL操作でトランザクション中でもテーブルへのブロックがかからないようになった。(ノンブロッキング)

SELECT文パフォーマンス

-

ORDER BYして、テーブルの全データを取得するような、大量データのソートは遅い。

ソートアルゴリズムがそれほど優れていない。

新規10件、100件(Top n レコード)を取得するのは速い。

UPDATE文パフォーマンス

追記型アーキテクチャであり、INSERTに近い処理が行われ、変更前の行に削除フラグのようなものが立てられている。

文字通り本当に変更前の行を上書きしている。

DELETE文パフォーマンス

-

バージョン5.4以前はDELETE文は遅く、データ削除後にセカンダリーインデックスを「同期処理」で貼り直していた。
バージョン5.5からは、その処理はサーバがアイドル状態のときなどに行われる「非同期処理」になり、以前ほどDELETEが遅いということはなくなった。

JOINアルゴリズム

以下3種類ともサポートしている。

* ネステッドループ結合(Nested Loop Join)
* ハッシュ結合(Hash Join)
* ソートマージ結合(Sort Merge Join)

ネステッドループ結合のみサポートしている。

※ネステッドループ結合は、テーブルのどちらかのデータ量が少なくて、もう一方が多いようなとき、もしくは、 インナーテーブル側がインデックススキャンを使えるときに有用。

※ハッシュ結合は、結合対象のデータ量が多いときで、データがソートされていないときに有用。

※ソートマージ結合は、結合対象のデータ量が多いときで、データが既にソートされているときに有用。

トランザクション処理の分離レベル

デフォルトはREAD-COMMITTEDであるが、REPEATABLE-READにしたときのファントムリードの防ぎ方は、ネクストキーロックはせずに別の方法を用いていて、MySQLより優れていると言えるかもしれない。

デフォルトがREPEATABLE-READであり、ファントムリードを避けるためネクストキーロックを採用している。

トランザクション処理の分離レベルは以下の4種類がある。
* SERIALIZABLE
* REPEATABLE-READ
* READ-COMMITTED
* READ-UNCOMMITTED

※ファントムリードとは、並行して動作している他のトランザクションが追加したデータが途中で見えてしまう現象。

※ネクストキーロックとは、主キーのインクリメント先の値までロックをかける仕組み。

ストアドプロシージャ、トリガー

SQL以外にも、
Pythonなどを利用した外部プロシージャが使える。

SQLのみである。

MySQL単体ではストアドプロシージャのステップ実行ができない。

バージョン5.6以前では1テーブルに付き6つまでしかマルチトリガーが仕掛けられなかった。
また、BEFORE INSERT TRIGGERが1テーブルにつき1個しか仕掛けられなかった。
それ以降のバージョンではトリガー数の制限はなくなった。

トリガーの種類は、FOR EACH ROWしかなく、FOR EACH STATEMENTがない。

レプリケーションの論理型と物理型

物理型のみ。

バージョン10からは論理型も使えるようになる。

物理型と論理型がある。

バージョン5.6までは、論理型がデフォルトだった。
バージョン5.7以降では物理型がデフォルトである。
(※変更された背景としては、論理型は良くも悪くも柔軟で、例えばマスターとスレーブのスキーマが違っていてもSQLさえ通ってしまえばエラーにならない。安全側に位置する物理型をデフォルトにした。)

論理型は、SQL文そのものをコピー。
物理型は、変更後の行イメージをコピー。

どちらかにしかない便利機能

ウィンドウ関数、WITH句など集計に適した機能がある。

複数のCPUを利用して処理速度を速めるパラレルクエリというものがある。

地図や幾何学データを扱うための、PostGISというサードパーティのOSSツールがある。

オンラインかつリモートでDBクラスタのベースバックアップが取れるpg_basebackupが便利。

ウィンドウ関数、WITH句は、バージョン8.0から導入予定。

オンラインでのリモートのクラスタベースバックアップはできない。

データ型の暗黙的に処理される型変換や文字列比較

型変換は堅い方に寄せている。

バージョン5.6以前はデータ型のゆるさが問題になることが多かった。
バージョン5.7以降は堅い方に修正されていっている。
とは言え、暗黙的型変換により、以下の式の3つの値は、同じ値とみなされる。
* (int) 1 = (string) '1' = (string) '1Q84'
※int 1 と string 1 の比較でintへの暗黙的型変換が行われるので、
それに伴い、string 1Q84 にもintへの暗黙的型変換が行われ、
初めの1文字目の1がintへ変換されるため。

文字列比較は、デフォルト設定では大文字と小文字の区別はしない。
また、バージョン8.0からは、デフォルト設定だと濁音と半濁音を区別しなくなる。
「はは」と「ぱぱ」と「ばば」はイコールになる。「びょういん」と「びよういん」もイコールになる。
これはUnicodeの仕様に依存しており、厳格さにレベル1〜4が存在し、「は」「ぱ」「ば」を区別するにはレベル2以上が、「びょういん」と「びよういん」を区別するにはレベル3以上が必要だが、MySQLは処理速度を優先しており、MySQL8.0はレベル1を採用している。厳格さと処理速度はトレードオフである。

結論

多機能であることが利点。

シンプルなWebサービスに向いている。

所感

自社プロダクトでは PostgreSQL と MySQL(Aurora) を使い分けておりますが、
今回のように整頓しておけば、設計で迷った時に役立つと思いました。

こちらからは以上です。