✋🏻 🧝🏻 👈🏽 MySQLにはないPostgreSQL機能、およびその逆 ☣️ 🍥 🎆

多くの人は、それが与えるものを漠然としか理解していないという事実のため、「筋肉」から「迷走」に切り替えることを恐れています。一部の人々は、Postgresはおそらくあまりにも複雑で、トレーニングが必要であるという考えにとらわれています。また、移行に伴い何かが失われる可能性があります。状況を少し明確にしようと思います。

一般的に言えば、誰かが複雑さを恐れている場合、初心者は通常のようにできます。新機能を使用せずに、MySQLからPostgreSQLに「ダム」スイッチします。 SQL-アフリカSQLにもあります。ロケット科学ではありません。この移行では、（いわゆるプログラミングでは）複雑なことは何もありません。さて、引用符は異なりますが、構文は少し厳密です。つまり pgをmysqlとして使用し、他の引用符を付けて開始し、プレイの進行とともに学習します。

さて、一方にはあるが、他方にはない、またはその逆について。パンの種類を理解する。もちろん、これは完全なリストではなく、重要度で並べ替えられているわけではありませんが、それでもなお並べられています。

おそらく筋肉にはない罪の欠点から始めましょう。

進行中の実稼働環境で作業するには、専門的にセットアップする必要があります。 shared_buffers、自動バキューム設定などを正しく設定しないと、深刻な負荷の下ですべてがゆっくりと動作します。特に厄介なのは、高負荷プロジェクトの場合、ほとんど常にpgbouncer（これはサードパーティの開発）が必要なことです。これにより、キューに接続が蓄積され、進行状況自体への接続数が制限されます。これがpostgres自体に組み込まれていないのは奇妙です
自動バキューム。簡単に言えば、書き込み/削除の高速化を実現するために、プログレッションはガベージの山を残し、その後、特別に訓練されたデーモンによってクリーンアップされます。特に非常に忙しいベースで自動バキュームを設定したり、愚か者をオフにしたりするのが間違っている場合、テーブルが占める場所が膨らみ、遅かれ早かれ、詰まる、または膨らむことなく、詰まる可能性のあるものすべてが詰まり、ベースが単に立ち上がることができますトランザクションIDが終了しました。各会議では、誰かがどのように自動バキュームで英雄的に戦って勝ったかについての3-4のレポートがあります。
最近まで、INSERT IGNOREおよびINSERT ON DUPLICATE KEY UPDATEはありませんでした。 9.5から、アナログが登場しました。誰もが必要とするような機能の実装に長い間引きずられていたことは非常に奇妙です。
Mysqlでは、クエリ内の変数を直接操作できます。
```
	 SELECT @x：= 0;
         SELECT @x：= @ x + 1 FROMテーブル;
```
進行中のそのようなことはありません、少なくとも私はそれを見つけませんでした（あなたが間違えた場合に書いてください）。つまり、もちろん、何でもできるストレージを作成することはできますが、リクエストに応じて適切に作成することはできません。
通常のphpmyadminに対応するものはありません。実際、私が知っているクールな同僚のほとんどは、最初は慣れるのが難しいコマンドラインでSQLを使用しています。いいえ、あらゆる種類のpgMyAdminなどがありますが、それぞれに何らかの欠陥があります。たぶん良い有料のものがあるので、私はチェックしませんでした。
Mysqlは誰もが知っている、postgresqlは誰も知らない。したがって、多くの場合、新しいプロジェクトはpostgresqlで開始することを恐れています。「データベース」という言葉とmysqlという言葉がまったく同じ同義語である、PHPプログラマーのクラスがあります。つまり彼らが貝殻か何かから抜け出すことはどういうわけか困難です。
彼らは、安価なホスティングは管理がより難しいので、postgresqlを本当に好きではないと言います。たとえば、postgresにログインできるユーザーを作成するには、sqlクエリを実行してpg_hba.confに書き込むという2つの場所でこれを行う必要があります。

現時点でmysqlと比較した場合の欠点のうち。他に何か特別なことを知っている場合、mysqlにあるものとpostgresqlにないものをコメントで書きます。これで、postgresqlの利点：

CTE（共通テーブル式）

簡単な方法で説明すると、サブクエリを個別に記述して、名前を付け、データベースへの単一クエリのフレームワーク内でこれらすべてを記述することができます。例えば
```
 WITH subquery1 AS（
      選択...  
       JOIN ...
       JOIN ...
      GROUP BY ....

 ）、
 subquery2 AS（
      選択...
       WHERE ....
 ）

選択* 
 FROM subquery1
     JOINサブクエリ2
           オン...
```
名前付きサブクエリがなくても、結合およびサブクエリブラケットを使用して頭脳全体を壊すことができる、非常に複雑なクエリには非常に便利です。もちろん、パフォーマンスには多くの微妙な違いがありますが、それでも非常に便利です。これはMySQLにはありません。ところで、CTEのサブクエリは再帰的に使用できます。たとえば、「id、parent_id」という形式のテーブルのサブツリー全体を取得できます。
IPアドレスを操作します。たとえば、ip-addressで都市/国をすばやく特定する必要があります。

ここでは、進行中にカスタムデータ型があり、これらの型で動作する演算子さえあることを言わなければなりません。一部は自分で行うことができ、一部は回線に拡張機能を追加することで取得できます。たとえば、次のようなことができるip4r拡張があります。
```
 -IP範囲を持つテーブルを作成する
テーブルip_ranges（
     ip_range ip4r
 ）;
 ip_rangesに挿入します
値 
 （ '2.2.3.4-2.2.3.10'）、
 （ '1.2.0.0/16'）;
```
これで、&&演算子を使用して、指定されたipと交差する範囲のリストを取得できます。
```
 test => select * from ip_ranges where ip_range && '1.2.1.1';
   ip_range  
 ------------
  1.2.0.0/16
 （1行）
```
ヒープまでは他の演算子があります：互いの範囲など。検索を非常に高速にするために、特別なGISTインデックスを作成できます。
```
 CREATE INDEX ip_ranges_idx ON ip_ranges USING GIST（ip_range）;
```
そして、大量のデータでもすべてが「飛ぶ」だけです。 mysqlでこれを行う方法は想像できませんが、おそらくいくつかの方法がありますか？
さまざまな制約、つまり整合性のためのデータベースの制約。 MySQLにはUNIQUE、NOT NULL、FOREIGN KEYなどもあります。しかし、これはどうですか：

前の例のテーブルを変更します。
```
 ALTER TABLE ip_ranges
   制約の追加ip_ranges_exclude
    GISTを使用して除外（ip_range WITH &&）;
```
このエントリにより、テーブル内のIP範囲のみが互いに交差しないことが保証されます。 IPの一部がすでにテーブルに含まれている範囲を挿入しようとすると、不正使用が発生します。
```
テスト=> ip_ranges値に挿入（ '1.2.3.4/32'）;
エラー：キー値の競合が除外制約「ip_ranges_exclude」に違反しています
詳細：キー（ip_range）=（1.2.3.4）は既存のキー（ip_range）=（1.2.0.0/16）と競合します。
```
同様に、たとえばcircleデータ型を使用して、互いに素な円がテーブルに保存されていることを確認できます。ちなみに、いくつかの幾何学タイプとそれらを使用した操作は、標準配信に直接組み込まれています：円、ボックス、多角形など。

別の便利な制約：
```
テーブル商品の作成（
     id bigint、
   価格の小数（11,2）、
      ...
    
    チェック（price> = 0.01）
 ）
```
また、誤ってゼロ価格の製品をそこに挿入することはありません。もちろん、チェック内の条件はどれでもかまいません。
最新バージョンのsgresのキラー機能はjsonbタイプで、これによりジェイソンで非常に迅速に検索できます。このことについての2番目の記事では、すべての耳がガラガラしているので、詳しくは説明しません。

いわゆる「ウィンドウ関数」。たとえば、サブクエリとグループ化を使用せずに、従業員ごとに彼の給与と同じ行の部門の平均給与を配る必要があります。

選択 
 depname、 
 empno、 
給料 
平均（給与）オーバー（depnameによるパーティション） 
 FROM empsalary;

   depname |  empno | 給与| 平均          
 ----------- + ------- + -------- + --------------------- -
 開発する|  11 |  5200 |  5020.0000000000000000
 開発する|  7 |  4200 |  5020.0000000000000000
 開発する|  9 |  4500 |  5020.0000000000000000
 開発する|  8 |  6000 |  5020.0000000000000000
 開発する|  10 |  5200 |  5020.0000000000000000
 人事|  5 |  3500 |  3700.0000000000000000
 人事|  2 |  3900 |  3700.0000000000000000
 販売|  3 |  4800 |  4866.666666666666666667
 販売|  1 |  5000 |  4866.666666666666666667
 販売|  4 |  4800 |  4866.666666666666666667
 （10行）

ウィンドウ関数を使用すると、タスクのクラス全体を簡素化できます。たとえば、分析や請求に非常に役立ちます。

ストアドプロシージャは、さまざまな言語で作成できます。puresql、pl / pgsql（SQLベースでの作業に便利ですが、遅い）、javascript（pl / v8）、真珠で、神は他のことを知っています。 siを知っていて、かなり熱心な場合は、好きな言語を種牡馬に添付することもできます。これはpgdayで詳細に議論されました。私の意見では、postgresqlでは、保存されたファイルの言語ではすべてがそれほどスムーズではありませんが、mysqlよりも100倍優れています。
フィールドだけでなく、フィールドの関数によってもインデックスを作成できます。
レプリケーション（ホットスタンバイ）は賢明に行われます。迅速かつ一貫して機能します。
スピード。私の主観的な感覚によると、私は両方のデータベースで長年働いていたため、Postgresql全体はMySQLよりもはるかに高速です。時々。挿入と読み取りの両方。もちろん、正しく構成されていれば。

これは、mysqlが単に処理できない複雑なクエリを実行し、一時テーブルをフェンスする必要がある場合に特に当てはまります。
すべての厳しさ。 mysqlでは、厳密なデフォルトモードのみが5.7で作成されたようです（チェックしませんでした、本当にそうですか？）。これに先立ち、10進数（5,2）型のフィールドに位置より大きい数値を挿入することが可能でした。その結果、静かに999.99を取得しました。線などのサイレントトリミングそのような冗談は闇があります。これがデフォルトの動作です。 Postgresqlは骨と呪いをかけますが、あいまいな要求を静かに実行しません。
トランザクション。 CREATE TABLE、ALTER TABLEなどは、単純なクエリと同様に、単一のトランザクションで実行したり、何か問題がある場合は途中でトランザクションをロールバックしたりできます。時間が経てば、それなしでmysqlを使用する方法を理解できなくなります。
すぐに使用できる全文検索。そこで、私の意見では、構文は普通の人には少し珍しいですが、すべてが機能し、スフィンクスのようなサードパーティのガジェットを接続する必要はありません。
シーケンス mysqlでは、テーブルフィールドごとにAUTO_INCREMENTのみがあり、一度に1つずつチェックされます。 postgresqlでは、このメカニズムはテーブルとは独立して存在し、さまざまなニーズに使用できます。さらに、ループで作成することもできます。
DBAはpostgresqlをメインのトランザクションマシンと見なしているようです。そこにあるトランザクションは深く、うまく組み込まれているため、挿入と読み取りの両方で、すべてが迅速かつ確実に機能します。 mysqlでは、システムが異なり、ベースがあり、個別のエンジン（innodb、myisamなど）があり、エンジンはすべてトランザクションではありません。このトランザクションの分離にはいくつかの問題があります。たとえば、myisamはトランザクションではなく、innodbはトランザクションであり、同じクエリで両方のテーブルを使用できます。基地が同時にどのように機能するか、予測することは想定していません。おそらく困難で松葉杖です。
主観的に、postgresqlのバグは少ないです。彼らがどのようにこれを達成しているかはわかりませんが、私にとってこれは事実です-非常に安定した信頼性の高いシステムであり、重い負荷とデータ量の下でも。

これはHabr（サンドボックス）に関する私の最初の投稿なので、強く、建設的に批判してください。

これらのベースの特定の利点と欠点は何ですか？コメントを書いてください。

MySQLにはないPostgreSQL機能、およびその逆

More articles: