絶対完全無料ツールの紹介

エンジニア目線のブログです

トップ > DB > #4 MySQLとPostgreSQLの比較を他のネットではやっていない観点から比較

#4 MySQLとPostgreSQLの比較を他のネットではやっていない観点から比較

DB

www.youtube.com

今回はDBの比較の話です。
MySQL vs Postgres です。
ず〜〜っと、長い間議論、比較されている両者のDBなので
あえて今比較する必要もないのかもですが、色々な判断材料
が欲しい時もあるかと思うので、動画を作ってみました。

今回フォーカスした点は３つです。
シーケンステーブルの有無
Postgresは筋肉質
人気

Postgresはシーケンステーブルが合って、それをインクリメントしてるだけなので、
データ量が多くなってもSELECT NEXTVAL('id_seq')のスピードは変わらないのに対して、
MySQLはテーブルの値のMAXを取得する方法に近いので、LAST_INSERT_ID
が遅くなる可能性がある

他のWEBサイトを参照しても遅くなるのではないかっていう記事があり。
実際検証してみました。

MySQLに１０００万件以上のデータを登録して、insertしてLAST_INSERT_IDでIDを取得
してみます。

想定とは違い、LAST_INSERT_IDがすぐに取得できました。なので、LAST_INSERT_ID
が遅いというデメリットはなくなりました。

ただ、シーケンステーブルがないので、どうやって"LAST"つまり最後の値を取得している
のか気になったので、２つの端末で試してみました。
下の画面で登録したIDが上の画面で登録されるんじゃないかと疑ってみたのですが、
上は上の値、下は下の値がしっかり取得できていたので、同じコネクションでの
最後のIDが取得できるみたいです。

ただ、コネクションを別にするとどう取得できるのか見てみると
LAST_INSERT_IDが０と取得できませんでした。
同じコネクションでないとLAST_INSERT_IDっていうのが取得できないと言っても
そこまでのデメリットではないかと思いますが、少し不安と言えば不安です。

シーケンステーブルをプログラミングをする時に書き方が異なってきます。
シーケンステーブルがある場合は、まずはじめにユニークで且つauto incrementされた
IDを取得してそのIDを各テーブルやファイル名などに使えるのですが、
シーケンステーブルがない場合は、まずinsertしてそのLAST_INSERT_IDで取得しないと
いけません。
この実装方法だと何がまずいかというと、IDを取得するためだけにトランザクションを
追加するか範囲を広くする必要が出てきます。できるだけシンプルにコーディングしたい
時の妨げになります。
例えば支払い系の処理でIDを取得するためにinsertしてIDを取得して
そのIDで購入処理のAPIアクセスしてエラーが出た場合は初めにinsertしたレコードには
フラグを立てるか削除するとかの処理が必要になってきます。

他にシーケンステーブルのメリットはIDのローテーションがしやすい所です。
MySQLはそのテーブルの値のMAXを取得する方法に近いので、
なんらかの原因で大きな値が入ったら後戻りができないです。

LAST_INSERT_IDはプライマリーキーにしか対応していないので、
それ以外で利用したい時はできないです。

例えば、配送会社からトラッキングナンバーの範囲１万〜２万のナンバーを契約して
２万になったら、また１万から始めるというときは、DBのプライマリーキーではなく
ローテーションが必要なauto incrementなので便利です。

そこまでシーケンステーブルが欲しいのであれば、MySQLでも自前で作ればいいのでは
と思い作成した事があるですが、デッドロックが頻発したりして安定稼働までは
そこそこの工数が取られました。
Postgresの場合は「select nextval」であればトランザクションなしでも
ユニークで最新のIDが保証されているので、とても便利です。

PostgresとMySQLを調べるとPostgresは筋肉質でバランスが取れていると記載されています。
どういう事かというと、MySQLは買収を重ねているので、開発の方向性がよく変わる！？
らしいです。
オープンソースといってもDBのオラクルの会社のものなので、突然何が起こるかは
オラクル社次第となります。
実際に２０２０年１２月にCentOSが終了を発表して、
不安定なテスト版のstreamを使用して下さいってアナウンス
がRedHat社から突然発表されました。
なので、MySQLエンタープライズ版を作って有料にして、オープンソース版は終了という
ことも考えられなくもないです。
しかしPostgresのようなコミュニティベースでも、そのコミュニティが不活性になれば
開発終了という可能性もゼロではないです。

他に筋肉質と言われる理由はMySQLの場合は
プロセスの確認の時は「show full processlist」で見れるのですが、
クエリを全て見たい時はINFORMATION_SCHEMA.PROCESSLIST
から見ないといけないなど、一通りの方法ではなく方法がいっぱいある所が
あり、Postgresはpg_stat_activityを見ればわかるという一通りで済む所が
筋肉質だという理由みたいです。

MySQLにはテーブルエンジンをInnoDBやMyISAMなど選択できますが、事実上使えるのは
InnoDBのみです。間違えてMyISAMで運用している所があり、テーブルロックがかかって
大変な現場も目にしています。
Postgresはテーブルエンジンという概念がそもそもないので使う側が選択ミスせずにすみます。
そういった所も筋肉質と言われる理由かと思います。

シーケンステーブルの有無や筋肉質な点で
個人的にはPostgresが好きなのですが、
Google TrendではMySQLの方が人気のようです。
オープンソースで人気がどうかは結構大事で誰も使われなくなったら、
オンラインドキュメントの情報も少なくなったり、そもそも
開発がとまってしまう事もあります。
さらにはUberがPostgresが遅いから
MySQLに切り替えたというニュースがありました。
その切り替えに関してはPostgres側はそもそものアプリケーションの作り方が
悪いとの指摘などもあったのですが、実績がある有名な企業の切り替えだったので、
自分が携わる大規模案件の時にMySQLを選んだ大きな理由の１つになりました。
https://eng.uber.com/postgres-to-mysql-migration/

MySQLのもう一つのメリットは人気なのが原因なのもあってか周辺ツールは豊富です。
phpMyAdmin, MySQL Workbenchの方がPgAdminよりはるかに使いやすいです。

総合的な観点で会社を背負っての大規模なシステムであればMySQLかなぁっと思います。
社内ツールのような小さいシステムであればPostgresでやってもいいかと思います。
あと、チームの同意が取れればPostgresを個人的には選択したいのはあります。
比較している記事はよく見かけるのですが、シーケンステーブルのような
他の記事では見かけない事柄を取り上げて比較してみました。

ちなみにGCEのメモリ１GBのサーバーでテストデータを作成する時にPostgres
では簡単に１億件作成できたのですが、同じスペックでMySQLで作成しようと
insert into load1 (name)  select load1.name from load1 limit 200000;
このSQLを２回実行するだけで、ハングったりして結構時間がかかりました。