MysqlとPostgresql比較 - tixture55’s diary

Selectの速度は、大量データのソートが必要なselect(order byをしたうえでテーブルの全データを取得する場合)ではpostgresqlに軍配があがります。

なぜならPostgresqlに比べてmysqlはソートのアルゴリズムがそれほど優れてはいないため。 Mysqlは大量データをソートすることを基本的にユースケースとして想定していません。

新規の10件とか100件を取得するようなTwitterのようなアプリでは、postgresqlよりもmysqlの方が優位です。

Updateはmysqlの方が優れています。私も実験でMysql5.2できちんとインデックスを張った100万行のレコードを全件取得するクエリ流したことありますが、がっつり固まってすごい時間かかってました。さらにorder byとかつけるとたぶんもう待ってられない時間かかったでしょうね。

　Postgresqlではupdateする際にはinsertに近い処理が実行されています（追記型アーキテクチャ）。変更前の行にフラグのようなものを立てたうえで、変更後のデータを持った新しい行を追加しています。

　一方、mysqlのupdateは文字通り、上書きしています。
Mysqlは基本的にネステッドループ結合しかサポートしていません。なぜなら、Mysqlは「複雑なアルゴリズムはなるべくサポートしない」という設計思想に基づいているからです。

結合対象のデータ量が多い時にはハッシュ結合やソート結合を使った方がよいです。そのデータがすでにソートされている場合はソート結合の方がよく、そうでなければハッシュ結合がおすすめです。

Mysqlのデフォルトのトランザクション分離レベルはRepeatable-Readとなっており、この方式だと読み取り対象のデータが途中でトランザクションから変更されてしまう心配はありません。ただしファントムリードが起こる場合があります。

Postgresqlのデフォルトの分離レベルはファントムリードやノンリピータブルリード（同じトランザクション中でも同じデータを読み込むたびに値が変わってしまう現象）が起こる場合があるため、運用時は気をつける必要があります。

Postgresqlはrepeatable-readに変更したとしても、ネクストキーロックをとらず、違う方法でファントムリードを防いでいます。ロック競合を防ぎやすい面ではmysqlより優れています。

PostgresqlはSQL以外にもpythonなどを利用した外部プロシージャが使えるのは利点だと思います。 Mysql5.6以前では１テーブルにつき最大６つまでしかマルチトリガーが仕掛けられないという欠点がありました。現在ではトリガー個数の制限はなくなっています。mysqlにはFor each statementがないため、注意する必要がある。

select文の実行前にサブクエリを作成できるwith句など、集計に適した機能がpostgresqlだけにありました。多くのエンジニアがPostgresqlを選択する理由として挙げるのがPostGISというサードパーティのOSSツールですね。地図や幾何データの情報を扱うためのものです。

また、pg_basebackupがすごく便利。オンラインかつリモートでデータベースクラスタのベースバックアップがとれるので Mysqlの場合、int(1)とString(1)と(string)1Q84 暗黙的に1Q84は数値へ型変換します。

Mysqlはデフォルト設定では文字列比較大文字小文字の区別はしません。「は」と「ば」と「ぱ」はイコールになります。これはunicodeの仕様によるもの。レベル２や３に設定しないとこれらは区別できない。レベルを２や３になると処理は重たくなっていきます。