結論

• 如果你的項目需要強一致性、復雜查詢、靈活數(shù)據(jù)模型，選 PostgreSQL
• 如果你的項目是簡單讀密集型應用，追求快速上手，選 MySQL
• 初創(chuàng)公司快速迭代選 MySQL
• 企業(yè)級應用/金融系統(tǒng)選 PostgreSQL
• 內(nèi)容平臺/IoT 項目選 PostgreSQL（JSONB、地理空間支持）
• 數(shù)據(jù)分析平臺選 PostgreSQL（窗口函數(shù)、CTE、全文搜索）
• 超高并發(fā)的寫入場景，如大型電商秒殺，MySQL 更勝一籌，如 Uber 從 PostgreSQL 轉(zhuǎn)MySQL
• PostgreSQL 功能豐富且強大，意味著學習使用和管理的難度更大
• PostgreSQL 為了實現(xiàn)其強大的功能和優(yōu)化，需要更多的內(nèi)存來達到更好的性能，占用磁盤空間相對 MySQL 較高

許可證

• MySQL 社區(qū)版使用 GPL 許可證，開發(fā)出的軟件整體也需開源。2010 年 MySQL 所在的 Sun 公司被 Oracle 收購后，原始開發(fā)團隊推出了 MariaDB
• PostgreSQL 使用它自己的許可證，類似 BSD 或 MIT，使用和二開沒限制，可以閉源商業(yè)化

性能

• MySQL 在簡單查詢的高并發(fā)讀場景下性能更好，適用于電子商務、博客
• PostgreSQL 在復雜查詢、高并發(fā)寫入場景下更穩(wěn)定，適用于金融、科學計算、數(shù)據(jù)倉庫、地理信息系統(tǒng)
• 對于大多數(shù)工作來說，PostgreSQL 和 MySQL 的性能旗鼓相當
• JSON 操作，PostgreSQL 比 MySQL 好得多
  • PostgreSQL：寫2279ms 讀31.65ms 更新26.26ms
  • MySQL：寫3501ms 讀49.99ms 更新62.45ms
• PostgreSQL 簡單查詢 QPS 60w，最高 200 w。讀寫TPS（4寫1讀）7w，最高 14 萬
• 極限條件下， PostgreSQL 簡單查詢性能顯著壓倒MySQL，其他場景基本與 MySQL 持平

使用Node.js的TypeORM庫，分別對MySQL和PostgreSQL跑了3次寫入讀取更新操作，取均值（2060條數(shù)據(jù)）

對象層次結構

• MySQL 采用了 4 級結構：實例、數(shù)據(jù)庫、表、列
• PostgreSQL 采用了 5 級結構：實例（集群）、數(shù)據(jù)庫、模式、表、列
• PostgreSQL多了一層，便于：
  • 數(shù)據(jù)組織和隔離：如不同模塊用同一個數(shù)據(jù)庫，每個模塊有一個模式，將數(shù)據(jù)隔離開。
  • 多租戶環(huán)境支持：每個租戶有單獨一個模式，確保數(shù)據(jù)安全性，維護也更高效。
  • 細粒度的權限控制：可授予用戶在某模式下只讀權限。

ACID事務

四種事務隔離級別：讀未提交、讀已提交、可重復讀、串行化

數(shù)據(jù)安全

• MySQL 和 PostgreSQL 都支持基于角色的訪問控制 RBAC（Role-Based Access Control）：用戶、角色、權限。例如用戶A可以查詢表T，但不能修改表T中的數(shù)據(jù)。
• PostgreSQL 還支持行級安全 RLS（Row-Level Security）：根據(jù)數(shù)據(jù)行中的某些屬性值來決定用戶是否能夠訪問該行數(shù)據(jù)，如不同租戶只能訪問自己的數(shù)據(jù)，避免了數(shù)據(jù)泄露的風險。例如員工信息表中，只有部門經(jīng)理才能看本部門員工的詳細信息，通過行級安全可以根據(jù)員工數(shù)據(jù)行中的部門字段來實現(xiàn)這種精細的訪問控制。

查詢優(yōu)化器

• MySQL
  • 主要目標是提高查詢速度，特別是簡單查詢，優(yōu)化器能快速生成執(zhí)行計劃，適合讀取密集型場景。但在處理復雜查詢時，存在一定局限性。
• PostgreSQL
  • 致力于實現(xiàn)復雜查詢的高性能，優(yōu)化器能更好地選擇連接順序和算法，擅長處理多表連接、子查詢、窗口函數(shù)等。
  • 不僅關注速度，還關注不同負載和復雜查詢下的性能平衡。
  • 支持復雜數(shù)據(jù)類型的優(yōu)化，例如數(shù)組、JSON 等數(shù)據(jù)類型。

性能優(yōu)化工具

• MySQL
  • id：查詢中的第幾個部分，用于區(qū)分多個SELECT
  • select_type：查詢類型，SIMPLE為簡單查詢
  • table：涉及的表名
  • partitions：分區(qū)信息
  • type：訪問類型，如ref用了索引
  • possible_kyes：可能用到的索引
  • key：實際用到的索引
  • key_len：索引長度，反映索引的覆蓋范圍
  • ref：索引中使用的列或常量，分別對應WHERE的兩個條件
  • rows：掃描的行數(shù)，用于評估查詢成本，越小越好
  • filtered：條件過濾后的行占讀取行的百分比，越大越好
  • Extra：額外的信息，如Using index表示使用了索引

• PostgreSQL
  • Index Scan using index_name on table_name：對某表的查詢用了某索引
  • cost=A…B：A是啟動成本，B是總成本
  • rows：結果行數(shù)
  • width：結果平均行寬
  • actual time=A…B：實際執(zhí)行時間
  • loops：循環(huán)次數(shù)
  • Index Cond：索引條件
  • Planning Time：查詢優(yōu)化器的耗時
  • Execution Time：查詢耗時

Sort：啟動成本717.34，總成本717.59，預計返回101行，每行寬度488字節(jié)。實際執(zhí)行時間7.761-7.774毫秒，返回100行，循環(huán)1次。排序鍵為t1.fivethous，排序方法快速排序，使用內(nèi)存77KB。
Hash Join：啟動成本230.47，總成本713.98，預計返回101行，每行寬度488字節(jié)。實際執(zhí)行時間0.711-7.427毫秒，返回100行，循環(huán)1次。連接條件為t2.unique2 = t1.unique2。
Seq Scan on tenk2 t2：對tenk2表進行順序掃描，成本為0-445，預計返回10000行，每行寬度244字節(jié)。實際執(zhí)行時間0.007-2.583 毫秒，實際返回10000行，循環(huán)1次。
Hash：啟動成本229.20，總成本229.20，預計返回101行，每行寬度4244字節(jié)。實際執(zhí)行時間0.659-0.659毫秒，返回100行，循環(huán)1次。
Buckets：哈希桶數(shù)量1024，批次1，使用內(nèi)存28KB。
Bitmap Heap Scan on tenk1 t1：對tenk1表進行位圖堆掃描，啟動成本5.07-229.20，預計返回101行，每行寬度244字節(jié)。實際執(zhí)行時間0.080-0.526毫秒，實際返回100行，循環(huán)1次。
Recheck Cond：重新檢查條件為unique1<100。
Bitmap Index Scan on tenk1_unique1：對tenk1表的tenk1_unique1索引進行位圖索引掃描，啟動成本0.00-5.04，預計返回101行，每行寬度0字節(jié)。實際執(zhí)行時間0.049-0.049毫秒，實際返回100行，循環(huán)1次。
Index Cond：索引條件為unique1<100。
Planning time：查詢計劃生成時間為0.194毫秒。
Execution time：查詢執(zhí)行總時間為8.008毫秒。

監(jiān)控工具

• MySQL
  • SHOW STATUS 和 SHOW VARIABLES命令
  • 第三方工具 MySQL Workbench
  • 慢查詢?nèi)罩?/li>
• PostgreSQL
  • pg_stat_activity 系統(tǒng)視圖，查看會話和鎖信息
  • pg_stat_statements 擴展收集查詢統(tǒng)計信息
  • 自帶開源免費的可視化工具 pgAdmin

JSON操作

窗口函數(shù)

窗口函數(shù)也叫 OLAP 函數(shù)，對數(shù)據(jù)庫數(shù)據(jù)進行實時分析處理，實現(xiàn)合計和分類匯總，如學生成績表計算出總分、平均分、按課程匯總。適合做數(shù)據(jù)倉庫。

插件

• MySQL：插件較少
• PostgreSQL：支持多種擴展
  • PostGIS：地理空間數(shù)據(jù)庫擴展
  • pg_cron：定時任務，如定期備份、數(shù)據(jù)清理
  • pgcrypto：加解密，存儲敏感信息
  • plpython3u：使用 Python 編寫函數(shù)和存儲過程
  • citext：忽略大小寫的數(shù)據(jù)類型
  • pg_jieba：中文分詞
  • smlar：相似查詢
  • imgsmlr：圖像搜索，基于 Haar 小波變換算法
  • zombodb：類似 ES 的索引
  • PgBouncer：連接池

# 啟用擴展
CREATE EXTENSION postgis;

# 創(chuàng)建空間數(shù)據(jù)表
CREATE TABLE city (
    id SERIAL PRIMARY KEY,
    city_name VARCHAR(50),
    location GEOMETRY(Point, 4326)
);

# 插入數(shù)據(jù)（EPSG:4326：WGS84坐標系，全球通用）
INSERT INTO city (city_name, location)
VALUES 
    ('深圳', ST_SetSRID(ST_MakePoint(114.0596, 22.5429), 4326)),
    ('廣州', ST_SetSRID(ST_MakePoint(113.2644, 23.1291), 4326)),
    ('武漢', ST_SetSRID(ST_MakePoint(114.3052, 30.5928), 4326)),
    ('青島', ST_SetSRID(ST_MakePoint(120.3830, 36.0662), 4326)),
    ('北京', ST_SetSRID(ST_MakePoint(116.4074, 39.9042), 4326));

# 查詢兩個城市之間的距離（EPSG:4527：基于國家大地坐標系的投影坐標系）
SELECT ST_Distance(
        ST_Transform((SELECT location FROM city WHERE city_name = '深圳'), 4527),
        ST_Transform((SELECT location FROM city WHERE city_name = '北京'), 4527));
# 1938597.1881722098

# 查詢200公里內(nèi)的城市
SELECT city_name FROM city 
WHERE ST_DWithin(
        ST_Transform(location, 4527),
        ST_Transform((SELECT location FROM city WHERE city_name = '深圳'), 4527), 
        200000);
# 深圳
# 廣州

易用性

連接模型

• MySQL：在每個連接上生成一個新線程
• PostgreSQL：在每個連接上生成一個新進程
  • 隔離性更好，例如內(nèi)存錯誤只會導致單個進程崩潰，而不是整個數(shù)據(jù)庫（記不記得測試環(huán)境數(shù)據(jù)庫偶爾會崩掉）
  • 進程模型的內(nèi)存消耗更大
  • 最好使用連接池，如 PgBouncer 或 pgcat （ORM自帶連接池）

可運維性

• MySQL 適合簡單應用和快速部署
• PostgreSQL 適合復雜應用、高級功能和性能優(yōu)化有較高要求的場景

書寫SQL

如何從MySQL遷移到PostgreSQL

規(guī)劃與準備

1. 分析 MySQL 數(shù)據(jù)庫的結構，包括表、視圖、存儲過程、觸發(fā)器等。確定數(shù)據(jù)量大小、數(shù)據(jù)類型、復雜程度，評估遷移的難度和工作量。
2. 檢查 MySQL 中使用的特定功能，如自增主鍵、日期時間格式、字符集等，確保在 PostgreSQL 中有替代方案。
3. 安裝并配置 PostgreSQL 數(shù)據(jù)庫。
4. 準備好遷移工具，如手動導入使用mysqldump結合psql，自動化工具使用pgloader。

遷移表結構和數(shù)據(jù)

• 使用 pgloader 自動遷移：目前 pgloader 最新版本為 2022 年發(fā)布的 3.6.9，不足以支持 2024 年發(fā)布的 PostgreSQL 17
• 使用 mysqldump 結合 psql 手動遷移
  • INT 改為 INTEGER，DATETIME 改為 TIMESTAMP
  • 自動遞增 AUTO_INCREMENT 改為 SERIAL
  • 表字段注釋改為 COMMENT ON COLUMN table.column IS 'xxx’;
  • VARCHAR 字符集一致
  • 時區(qū)改為與應用層一致
  • PostgreSQL 的表名和字段名默認區(qū)分大小寫

遷移存儲過程、函數(shù)、觸發(fā)器

• MySQL 定義存儲過程要用 DELIMITER，PostgreSQL 不用
• 注意具體的實現(xiàn)語法可能存在差異
  • 事務 START TRANSACTION 改為 BEGIN TRANSACTION
  • ROLLUP 刪掉 WITH

測試與驗證

• 數(shù)據(jù)一致性檢查：COUNT(*) 對比數(shù)據(jù)行數(shù)
• 應用回歸測試：確保所有功能正常，性能相近
• 時間規(guī)劃：規(guī)劃好遷移時間，提前通知用戶，減少對業(yè)務的影響

總結 

到此這篇關于PostgreSQL與MySQL完整對比教程的文章就介紹到這了,更多相關PostgreSQL與MySQL對比內(nèi)容請搜索腳本之家以前的文章或繼續(xù)瀏覽下面的相關文章希望大家以后多多支持腳本之家！

最新評論