Quelle est la différence entre postgresql et mysql

Différences : 1. PGSQL n'a pas de limite sur le nombre de cœurs de processeur, alors que mysql a une limite ; 2. PGSQL a un total de 255 paramètres de fichier de configuration, tandis que MySQL a un total de 707 paramètres ; informations statistiques multi-champs, mais pas MySQL ; 4. PGSQL prend en charge la compilation à la volée des plans d'exécution, mais MySQL ne la prend pas en charge.

L'environnement d'exploitation de ce tutoriel : système windows7, version PostgreSQL 11&&MySQL5.7, ordinateur Dell G3.

PostgreSQL est un système de gestion de bases de données relationnelles objet (ORDBMS) logiciel gratuit doté de fonctionnalités très complètes.

PostgreSQL prend en charge la plupart des normes SQL et fournit de nombreuses autres fonctionnalités modernes, telles que des requêtes complexes, des clés étrangères, des déclencheurs, des vues, l'intégrité des transactions, le contrôle de concurrence multi-version, etc. De même, PostgreSQL peut être étendu de plusieurs manières, par exemple en ajoutant de nouveaux types de données, fonctions, opérateurs, fonctions d'agrégation, méthodes d'index, langages procéduraux, etc. De plus, grâce à la licence flexible, n'importe qui peut utiliser, modifier et distribuer gratuitement PostgreSQL à n'importe quelle fin.

Comparaison des différences entre postgresql et mysql

Version comparative : PostgreSQL 11 VS MySQL5.7 (moteur innodb) Situation des droits d'auteur de l'édition communautaire officielle d'Oracle : PostgreSQL 11 (open source gratuit), MySQL5.7 Oracle officiel édition communautaire (gratuite et open source)

1. Limite du processeur

PGSQL n'a pas de limite sur le nombre de cœurs de processeur. Vous pouvez utiliser autant de cœurs de processeur que vous en avez

MySQL peut utiliser un processeur à 128 cœurs. plus de 128 cœurs ne peuvent pas être utilisés

2. Paramètres du fichier de configuration

PGSQL a un total de 255 paramètres, et environ 80 d'entre eux sont utilisés. Vous pouvez également démarrer la grande base de données actuelle en utilisant. le fichier de configuration de la grande version précédente.

MySQL a un total de 707 paramètres, et environ 80 d'entre eux sont utilisés, les paramètres sont en constante augmentation, même les petites versions augmenteront les paramètres, et certains paramètres seront incompatibles entre les grandes versions

. 3. Dépendance aux outils tiers

PGSQL n'a besoin que de clusters à haute disponibilité pour s'appuyer sur des middlewares tiers, par exemple : patroni+etcd, repmgr

La plupart des opérations de MySQL reposent sur des outils tiers (percona-toolkit , XtraBackup) de la société Percona. Il y a trop de commandes d'outils et des coûts d'apprentissage élevés. Les clusters à haute disponibilité nécessitent également un middleware tiers. Le cluster MGR officiel n'est pas encore mature

4. -réplication esclave

La réplication de flux physique PGSQL est une réplication physique, tout comme la mise en miroir de SQL Server/AlwaysOn, elle est strictement cohérente et n'a aucune possibilité de provoquer des incohérences, des performances et de la fiabilité. En termes de performances, la réplication physique bat la réplication logique et est facile à maintenir.La réplication maître-esclave MySQL appartient à la réplication logique.(Des paramètres incorrects tels que sql_log_bin et binlog_format entraîneront une incohérence maître-esclave.) La réplication parallèle de transactions volumineuses est inefficace. Les outils pt-table-checksum et pt-table-sync de percona-toolkit comparent et réparent régulièrement la cohérence maître-esclave Lorsque de graves erreurs de réplication maître-esclave se produisent, il est nécessaire de rétablir la réplication logique maître-esclave de MySQL. et n'empêche pas deux bases de données incohérentes d'établir une relation de réplication

5. Statut en lecture seule de la base de données

Le système PGSQL définit automatiquement la base de données esclave en lecture seule par défaut, aucune intervention manuelle n'est requise, et la maintenance est simple

La base de données esclave MySQL doit définir manuellement le paramètre super_read_only=on pour définir la base de données esclave en lecture seule. Il y a un bug dans le paramètre super_read_only, lien : https://baijiahao.baidu.com/s? id=1636644783594388753&wfr=spider&for=pc

6. Version branch

PGSQL n'a que la version communautaire, il n'y a pas d'autre version branche, PGSQL est officiellement unifié Développement, maintenance unifiée, la version communautaire a toutes les fonctions, contrairement à SQL Server. et MySQL, qui sont divisés en version standard, version entreprise, version classique, version communautaire, version de développement et version Web. Il existe également des bases de données nationales et étrangères basées sur PGSQL pour le développement secondaire, telles que : Enterprise DB, Hangao. Base de données, etc. Bien entendu, ce ne sont que des développements secondaires et ne sont pas considérés comme des branches indépendantes. Pour des raisons historiques, MySQL est divisé en trois versions de branche, la branche MariaDB, la branche Percona, la branche officielle d'Oracle, et a été développée jusqu'à présent. Les branches sont fondamentalement incompatibles les unes avec les autres. Les branches officielles d'Oracle sont également divisées en versions, qui sont divisées en version standard, version entreprise, version classique et version communautaire

7. 94 types, SQL La prise en charge de la syntaxe la plus complète, par exemple : prend en charge les expressions de table communes (AVEC requête)

La prise en charge des fonctionnalités MySQL SQL prend en charge 36 types, la prise en charge de la syntaxe SQL est relativement faible, par exemple : ne prend pas en charge les expressions de table communes (AVEC requête) ) À propos de la prise en charge des fonctionnalités SQL Pour comparaison, vous pouvez vous référer à : http://www.sql-workbench.net/dbms_comparison.html

8. Sécurité de la réplication maître-esclave

Réplication en streaming synchrone PGSQL, synchronisation forte (à distance appliquer), haute sécurité, pas de perte de données Réplication de flux synchrone PGSQL : toutes les bases de données esclaves sont en panne, la base de données maître se mettra en grève et la base de données maître ne pourra pas passer automatiquement à la réplication de flux asynchrone (mode asynchrone), ce qui doit être résolu par augmenter le nombre de bases de données esclaves.

L'environnement de production général dispose d'au moins deux bibliothèques esclaves. Solution manuelle : modifiez le paramètre synchronous_standby_names ='' dans la bibliothèque principale du PG, et exécutez la commande : pgctl reload. Les données maître-esclave sont. complètement cohérent. Il s’agit de la première étape de la commutation à haute disponibilité.

Il est donc compréhensible que PGSQL choisisse la base de données maître à frapper. MySQL améliore la réplication semi-synchrone

La semi-synchronisation améliorée de la version mysql5.7 peut garantir qu'aucune donnée n'est perdue lors de la réplication maître-esclave de Mysql5.7. Paramètres liés à la réplication synchrone : Paramètre rpl_semi_sync_master_wait_for_slave_count au moins le nombre de bases de données esclaves à attendre. Après avoir reçu le journal binaire, la bibliothèque principale soumettra la transaction. Généralement défini sur 1. Le paramètre de performance le plus élevé rpl_semi_sync_master_timeout est le nombre de millisecondes à attendre. passe automatiquement en mode asynchrone s'il n'y a pas de réponse. Généralement défini sur infini pour empêcher la bibliothèque principale de passer automatiquement en mode asynchrone. Toutes les bases de données esclaves sont en panne et la base de données principale se mettra en grève car elle ne pourra recevoir aucun paquet de réponse de la part de la bibliothèque principale. base de données esclave. Solution manuelle : modifiez le paramètre rpl_semi_sync_master_wait_for_slave_count=0 dans la base de données maître MySQL

9. Informations statistiques multi-champs

PGSQL prend en charge plusieurs champs Statistiques

MySQL ne prend pas en charge les statistiques multi-champs

10. Type d'index

Types d'index multiples PGSQL (btree, hash, gin, gist, sp-gist, brin, bloom, rum, zombodb, bitmap, index partiel, index d'expression)

Indice MySQLbtree, index de texte intégral (inefficace) , index d'expression (nécessité de créer une colonne virtuelle), index de hachage uniquement dans la table mémoire

11. Algorithme de connexion de table physique

PGSQL prend en charge la jointure en boucle imbriquée, la jointure par hachage, la jointure par fusion

MySQL ne prend en charge que les boucles imbriquées. rejoindre

12. Performances des sous-requêtes et des vues

Sous-requête PGSQL, optimisation des vues, performances relativement élevées

Le refoulement des conditions de prédicat de vue MySQL a de nombreuses restrictions, il existe de nombreuses restrictions d'extraction de sous-requête

13. compilation temporelle des plans d'exécution

PGSQL prend en charge la compilation juste à temps du plan d'exécution JIT, à l'aide du compilateur LLVM

MySQL ne prend pas en charge la compilation juste à temps des plans d'exécution

14. requête parallèle (une variété de méthodes d'optimisation de requêtes parallèles), la requête parallèle est généralement vue dans les bases de données commerciales, c'est une fonction lourde

MySQL est limité et ne prend en charge que les requêtes parallèles à clé primaire

15. Vue matérialisée

PGSQL prend en charge. vue matérialisée

MySQL ne prend pas en charge les vues matérialisées

16. Fonction de plug-in

PGSQL prend en charge les fonctions de plug-in, qui peuvent enrichir les fonctions de PGSQL, le plug-in de géographie SIG, le plug-in de base de données de séries chronologiques, plug-in d'exécution vectorisé, etc.

MySQL ne prend pas en charge les fonctions de plug-in

17. Contraintes de vérification

PGSQL prend en charge les contraintes de vérification

MySQL ne prend pas en charge les contraintes de vérification Vous pouvez écrire des contraintes de vérification, mais le stockage. Le moteur ignorera son effet, donc les contraintes de vérification ne fonctionneront pas (prise en charge de Mariadb)

18. GPU Accélérer SQL

PGSQL peut utiliser le GPU pour accélérer la vitesse d'exécution de SQL

MySQL ne prend pas en charge le GPU pour accélérer l'exécution. vitesse de SQL

19. Types de données

PGSQL a des types de données riches, tels que ltree, hstore, type de tableau, type ip, type de texte, varchar n'est plus nécessaire. les champs font 1 Go

Les types de données MySQL ne sont pas assez riches

20. Requête entre bases de données

PGSQL ne prend pas en charge les requêtes entre bases de données

.

21. Sauvegarde et restauration

La sauvegarde et la restauration PGSQL sont très simples. L'opération de restauration ponctuelle est encore plus simple que la sauvegarde complète de SQL Server + la sauvegarde d'archive wal (incrémentielle). cluster maître-esclave, vous pouvez effectuer une sauvegarde complète et une sauvegarde d'archive wal sur n'importe quel nœud

La sauvegarde et la restauration MySQL ne sont relativement pas simples, la sauvegarde complète + la sauvegarde binlog (incrémentielle) la sauvegarde complète nécessite l'outil XtraBackup de percona pour la sauvegarde physique, MySQL lui-même Sauvegarde physique Les opérations de restauration à un instant donné ne sont pas prises en charge. Les étapes sont lourdes et compliquées

22. Vue des performances

PGSQL doit installer le plug-in pg_stat_statements. Le plug-in pg_stat_statements fournit des vues riches sur les performances : événements en attente, statistiques système, etc. L'inconvénient est que l'installation du plug-in nécessite le redémarrage de la base de données et que la base de données qui doit collecter des informations sur les performances doit exécuter une commande : créez une commande d'extension pg_stat_statements, sinon aucune information sur les performances ne sera collectée. , ce qui est plus gênant

MySQL est livré avec la bibliothèque PS, de nombreuses fonctions ne sont pas activées par défaut et PS est activé La fonction d'affichage des performances de la bibliothèque a un impact sur les performances (comme l'utilisation de la mémoire conduisant à des bogues MOO)

23. Méthode d'installation

PGSQL a des packages RPM, des packages Deb, etc. Par rapport à MySQL, il manque généralement des packages binaires. Le temps d'installation est généralement utilisé. et il y aura plus de commandes à exécuter.

MySQL a des packages rpm, des packages deb, etc. pour diverses plates-formes. La compilation et l'installation du code source, l'installation des packages binaires, utilisent généralement des packages binaires pour l'installation, ce qui est pratique et rapide

24. Opérations DDL

PGSQL ajoute des champs et modifie la longueur des types de champs de longueur variable sans verrouiller la table. Toutes les opérations DDL ne nécessitent pas l'utilisation d'outils tiers et, comme les bases de données commerciales, les opérations DDL peuvent être annulées. , garantissant la cohérence des transactions

MySQL Étant donné que la plupart des opérations DDL verrouillent la table, telles que l'ajout de champs et la modification de la longueur des types de champs de longueur variable, vous devez utiliser l'outil pt-online-schema-change dans percona-toolkit pour terminer l'opération et réduire l'impact au minimum, en particulier pour les opérations DDL sur les grandes tables, les opérations DDL ne peuvent pas être annulées

25.

PGSQLPGSQL publie une version majeure chaque année. Elle peut être mise dans l'environnement de production au cours de la deuxième année après la sortie de la version majeure. La vitesse d'itération de la version est très rapide. La version officielle de PGSQL 9.6 a été lancée en 2016. de PGSQL 10 a été lancé en 2017. La version officielle de PGSQL 11 a été lancée en 2017. : Heure de lancement de la version officielle de PGSQL 12 en 2018 : 2019

MySQL Il faut généralement 2 à 3 ans pour qu'une version majeure de MySQL soit publiée. Généralement, une version majeure ne peut être mise dans l'environnement de production qu'au cours de la deuxième année après sa sortie pour éviter les pièges. Comparaison des vitesses de publication des versions Lent La version officielle de MySQL 5.5 a été lancée : 2010 La version officielle de MySQL 5.6 a été lancée : 2013 La version officielle de MySQL 5.7 a été lancée. version a été lancée : 2015 La version officielle de MySQL 8.0 a été lancée : 2018

26. syntaxe de retour

PGSQL prend en charge la syntaxe de retour, la clause de retour prend en charge l'ensemble de résultats de retour DML, réduisant ainsi une interaction client <-> ne prend pas en charge la syntaxe de retour

27. Architecture interne

Architecture multi-processus PGSQL, le nombre de connexions simultanées ne peut pas être trop élevé, tout comme Oracle, car c'est la même chose qu'Oracle, de nombreuses méthodes d'optimisation sont également les mêmes, tels que : activer une grande mémoire de page

Architecture multithread MySQL. Bien que l'architecture soit multithread, il existe une limite officielle sur le nombre de connexions en raison de la concurrence du système. La vitesse est limitée s'il y en a trop. threads, la capacité de traitement du système diminuera. À mesure que le nombre de connexions augmentera, les performances diminueront. Généralement, il ne peut gérer que 200 à 300 connexions à la base de données en même temps

28. L'index clusterisé

PGSQL ne prend pas en charge. it Index clusterisé, provoqué par le mécanisme d'implémentation MVCC de PGSQL lui-même

MySQL prend en charge l'index clusterisé

29. La fonction de terminaison des transactions inactives

PGSQL met fin aux transactions inactives en définissant le paramètre ralenti_in_transaction_session_timeout, par exemple : si le code de l'application oublie de le faire. fermez une transaction ouverte Transactions, PGSQL vérifiera et tuera automatiquement ce type de transaction de session

MySQL ne prend pas en charge la fonction de terminaison des transactions inactives

30 Faire face à des volumes de données extrêmement importants

PGSQL ne peut pas gérer des données extrêmement volumineuses. volumes. En raison du problème de conception MVCC de PGSQL lui-même, il est nécessaire Pour le garbage collection, nous ne pouvons nous attendre qu'à une optimisation dans les versions majeures ultérieures

MySQL ne peut pas faire face à des volumes de données extrêmement importants et à des problèmes avec la propre architecture de MySQL

31. . Evolution distribuée

Base de données PGSQLHTAP : cockroachDB, cluster de partitionnement Tencent Tbase : Postgres- XC, Postgres - Au niveau du système d'exploitation (lorsque la base de données est arrêtée), comprendre clairement les noms de fichiers et les règles de dénomination de la base de données, le nombre de. fichiers et la taille des fichiers. Une fois que le système d'exploitation subit une perte de fichier ou un dommage au disque dur, il est très difficile de le récupérer car les données de la table PGSQL n'ont même pas de nom. La règle de dénomination/stockage des fichiers physiques est la suivante : sous. un espace table, si aucun espace table n'est créé, la valeur par défaut est l'espace table par défaut, qui est le dossier de base, par exemple : /data/base/16454/3599base : l'emplacement physique où se trouve le dossier pg_default de l'espace table par défaut. 16454 : oid3599 de la base de données où se trouve la table : est l'oid de l'objet table. Bien entendu, lorsque la taille d'une table dépasse 1 Go, plusieurs fichiers physiques seront générés, ainsi que le fichier fsm et le fichier vm du. table, donc une grande table aura en fait plusieurs fichiers physiques. Puisque PGSQL a trop de contenu de présentation de fichiers de données, vous pouvez vérifier les informations pertinentes. Bien sûr, cela ne peut pas être entièrement imputé à PGSQL. En tant qu'administrateur de base de données, c'est la bonne façon de le faire. effectuez une sauvegarde de la base de données et une récupération après sinistre à tout moment. La récupération des médias est généralement un dernier recours. Cela ne sera effectué que dans certaines circonstances. Le nom de la base de données MySQL est le nom du dossier, et sous le dossier de la base de données se trouvent les fichiers de données de la table. un fichier frm correspondant et un fichier ibd pour stocker les métadonnées et les données de table/index. C'est clair et simple. La récupération de média ou le transfert d'espace de table est très pratique

33.

PGSQLPGSQL est assez délicat en termes de conception d'autorisations. En mettant de côté les autorisations d'instance et les autorisations d'espace table, le niveau d'autorisation de PGSQL est un peu comme SQL Server db=》schema=》object. ici. Utilisez Oracle comme analogie. Avant ORACLE 12C, les instances et les bases de données étaient individuelles, ce qui signifie qu'une instance ne peut avoir qu'une seule base de données. Contrairement à MySQL et SQL Server, une instance peut avoir plusieurs bases de données et effectuer des requêtes sur plusieurs bases de données. à volonté. PGSQL ne peut pas interroger plusieurs bases de données. La raison est également la même. Par analogie avec ORACLE, il existe plusieurs instances (les instances et les bases de données mentionnées précédemment sont une à une). équivaut à une instance. Parce que PGSQL autorise plusieurs instances, PGSQL seul. Une instance n'est pas appelée une instance, elle est appelée un cluster. Vous pouvez vous référer aux informations pertinentes de PGSQL pour le concept de cluster. dans PGSQL est équivalent à une base de données, donc le concept de ce schéma correspond à la base de données de MySQL Remarque : Parce qu'une base de données est équivalente à une instance, PGSQL autorise plusieurs instances/bases de données, donc les bases de données sont logiquement isolées les unes des autres. Le problème est que les opérations ne peuvent pas être effectuées sur toutes les bases de données d'un cluster PGSQL en même temps et doivent être effectuées une par une. Pour fonctionner, par exemple, installez le plug-in pg_stat_statements mentionné ci-dessus si vous devez effectuer une collecte de performances sur toutes les bases de données. sous le cluster PGSQL, vous devez exécuter la commande de chargement une par une. Par exemple, une requête inter-bases de données nécessite le plug-in dblink ou le plug-in fdw, deux requêtes entre bases de données équivaut à une requête entre deux instances. instances étendues, donc le plug-in dblink ou fdw est requis, donc le principe est très simple. Les opérations d'autorisation sont également effectuées base de données par base de données. Un autre est PGSQL, bien qu'il soit comme SQL L'autorisation. La hiérarchie du serveur est db=》schema=》object, mais elle est en réalité plus compliquée que SQL Server. De plus, la table nouvellement créée doit être autorisée séparément dans PGSQL. Les rôles et les utilisateurs sont les mêmes, ce qui est utile pour les utilisateurs novices. Parfois, je n'arrive pas à faire la différence et je ne sais pas comment utiliser les rôles, donc PGSQL est vraiment délicat en termes de conception des autorisations

MySQL utilise les 5 tables d'autorisations de la bibliothèque MySQL pour effectuer le mappage des autorisations, ce qui est le cas. simple et clair. Le seul problème est qu'il manque le rôle d'autorisation user table db table host table tables_priv table columns_priv table

34 Historique de développement

PGSQL En 1995, les développeurs Andrew Yu et Jolly Chen ont ajouté un SQL (Structured Query Language). , Structured Query Language) vers Postgres ), cette version s'appelle Postgres95 et est publiée dans la communauté open source. En 1996, des changements majeurs ont été apportés à Postgres95, et il a été nommé PostgresSQL version 6.0 et publié. Le nom de PostgresSQL a été finalisé à partir de 1995, il a une histoire d'environ 24 ans

MySQL En 1996, MySQL 1.0 Release, elle n'est accessible qu'à un petit groupe de personnes, ce qui équivaut à une libération interne. En octobre 1996, MySQL 3.11.1 est sorti (MySQL n'a pas de version 2.x). Au début, il ne fournissait qu'une version binaire sous le système d'exploitation Solaris. Un mois plus tard, la version Linux est apparue. il a une histoire d'environ 23 ans

【Recommandations associées : tutoriel vidéo mysql】

Ce qui précède est le contenu détaillé de. pour plus d'informations, suivez d'autres articles connexes sur le site Web de PHP en chinois!