雙數(shù)據(jù)庫兼容實(shí)踐：MySQL與PostgreSQL適配全解析)
我的在線客服系統(tǒng)從第一版上線到現(xiàn)在跑了兩年多后端數(shù)據(jù)庫一直用的 MySQL 8.0業(yè)務(wù)上沒出過什么大問題。直到上個(gè)月簽了一個(gè)私有化部署的客戶對方既有技術(shù)棧統(tǒng)一是 PostgreSQL數(shù)據(jù)庫、備份策略、監(jiān)控體系全部圍繞 PG 搭建我?guī)缀鯖]有猶豫老老實(shí)實(shí)開始給客服系統(tǒng)增加 PostgreSQL 支持。這個(gè)改造過程比我想象中要復(fù)雜不少。雖然兩個(gè)庫都支持標(biāo)準(zhǔn) SQL但真到了生產(chǎn)級適配數(shù)據(jù)類型、SQL 方言、事務(wù)模型、連接參數(shù)、備份恢復(fù)、索引策略幾乎每個(gè)環(huán)節(jié)都有差異。既然踩了一輪坑就把整個(gè)過程整理成一篇手記重點(diǎn)講清楚兩件事一是怎么讓在線客服系統(tǒng)同時(shí)跑在 MySQL 和 PostgreSQL 上二是這兩個(gè)數(shù)據(jù)庫在同樣的業(yè)務(wù)場景下到底差在哪里各自適合什么情況。無論你是獨(dú)立開發(fā)者、小團(tuán)隊(duì)技術(shù)負(fù)責(zé)人還是正在做技術(shù)選型這篇內(nèi)容應(yīng)該都能給你一些參考。1. 項(xiàng)目背景為什么客服系統(tǒng)需要同時(shí)兼容 MySQL 與 PostgreSQL1.1 在線客服系統(tǒng)的數(shù)據(jù)模型與存儲(chǔ)需求先交代一下我這套在線客服系統(tǒng)的數(shù)據(jù)模型方便后面講差異的時(shí)候有具體的業(yè)務(wù)載體。核心表有這幾張會(huì)話表conversation保存每一次訪客和坐席之間的會(huì)話包含租戶 ID、訪客 ID、坐席 ID、狀態(tài)字段排隊(duì)中、接入中、已結(jié)束、渠道類型Web、小程序、App消息表message保存聊天內(nèi)容字段包括會(huì)話 ID、發(fā)送方類型訪客/坐席/系統(tǒng)、消息內(nèi)容、發(fā)送時(shí)間訪客表visitor記錄訪客信息和擴(kuò)展屬性我用了一個(gè) JSON 字段存諸如地區(qū)、來源頁面、設(shè)備信息等非結(jié)構(gòu)化數(shù)據(jù)坐席表agent和操作日志表operation_log相對簡單以讀寫為主。這個(gè)業(yè)務(wù)場景有幾個(gè)比較鮮明的存儲(chǔ)特征消息表寫入非常頻繁屬于典型的持續(xù)追加型數(shù)據(jù)會(huì)話狀態(tài)會(huì)不斷更新排隊(duì)轉(zhuǎn)接入、接入轉(zhuǎn)結(jié)束、坐席改派運(yùn)營后臺(tái)需要做大量歷史會(huì)話查詢和聚合統(tǒng)計(jì)客服經(jīng)常要按關(guān)鍵詞搜索聊天記錄。用一句話總結(jié)就是高寫入、有更新、重查詢、需要全文檢索。這些特征在后續(xù)對比 MySQL 和 PostgreSQL 時(shí)會(huì)反復(fù)提到。1.2 獨(dú)立開發(fā)者的默認(rèn)選擇MySQL大部分獨(dú)立開發(fā)者做項(xiàng)目的第一反應(yīng)都是 MySQL我也不例外。理由很現(xiàn)實(shí)資料多、社區(qū)活躍、云上隨便都能買一個(gè)兼容實(shí)例出了問題搜索一下基本都能找到答案。MySQL 8.0 的 JSON 類型、窗口函數(shù)、CTE公共表表達(dá)式這些能力也已經(jīng)補(bǔ)齊對于客服系統(tǒng)這個(gè)體量的應(yīng)用來說功能上完全夠用。另一個(gè)關(guān)鍵點(diǎn)是運(yùn)維成本。獨(dú)立開發(fā)者沒有專職 DBAMySQL 的默認(rèn)配置相對“友善”InnoDB 引擎做了大量自適應(yīng)工作redo log、buffer pool 這些機(jī)制基本不需要人工干預(yù)。相比之下PostgreSQL 的 autovacuum、checkpoint、WAL 歸檔這些概念初次接觸的人容易懵。所以早期選擇 MySQL 是一個(gè)非常典型的“確定性優(yōu)先”決策先把業(yè)務(wù)跑起來把精力放在功能迭代上。1.3 客戶環(huán)境倒逼PostgreSQL 的入場這次的私有化部署客戶點(diǎn)名要求 PostgreSQL原因也很簡單他們內(nèi)部所有業(yè)務(wù)系統(tǒng)都在 PG 上已有的監(jiān)控平臺(tái)、備份腳本、權(quán)限體系都是圍繞 PG 做的不愿為新系統(tǒng)再引入一套 MySQL 運(yùn)維鏈路。這其實(shí)是獨(dú)立開發(fā)者做 to B 業(yè)務(wù)時(shí)經(jīng)常遇到的場景——技術(shù)選型不完全由你決定客戶現(xiàn)有的基礎(chǔ)設(shè)施就是約束條件。我沒有選擇直接遷移而是定了“雙數(shù)據(jù)庫兼容”的改造方向。理由是我現(xiàn)有的存量客戶還在 MySQL 上不可能逼他們切換新客戶要 PG就同時(shí)支持兩邊。這意味著代碼層面要抽象出數(shù)據(jù)庫無關(guān)的訪問方式SQL 層要做好方言隔離測試矩陣要從單庫變成雙庫。代價(jià)不小但收益也很實(shí)在后續(xù)再接任何客戶數(shù)據(jù)庫這一環(huán)就不會(huì)再成為商務(wù)談判的障礙。1.4 雙庫兼容的改造策略與成本評估改造前我先做了一個(gè)粗略的成本評估。我的系統(tǒng)是 Java Spring Boot MyBatis 技術(shù)棧MyBatis 本身不限制數(shù)據(jù)庫方言復(fù)雜的 SQL 寫在 XML 里因此適配思路分為兩層第一層是基礎(chǔ)設(shè)施適配包括多數(shù)據(jù)源配置、驅(qū)動(dòng)切換、連接參數(shù)調(diào)整第二層是 SQL 方言適配涉及所有 XML 里的 SQL 語句逐條審計(jì)。這里要給獨(dú)立開發(fā)者一個(gè)非常直白的建議如果你正在做類似的雙庫兼容改造在項(xiàng)目早期就定好一條鐵律——所有新寫的 SQL 必須預(yù)先考慮雙庫兼容性不要等代碼寫完了再來一句一句改。另外能交給 ORM 框架處理的就不要手寫 SQL比如簡單的 CRUD 完全可以交給 MyBatis-Plus 或 Spring Data JPA 的自動(dòng)方言適配去處理復(fù)雜報(bào)表和特殊查詢才需要手寫方言。我這次改造里大概有 70% 的 SQL 通過框架自動(dòng)適配掉了剩下 30% 的高風(fēng)險(xiǎn) SQL 全部重寫一遍。這個(gè)比例供你參考。2. PostgreSQL 與 MySQL 的核心差異動(dòng)手前的必修課2.1 連接層與驅(qū)動(dòng)JDBC URL、SSL 與連接池兩個(gè)庫在 Java 生態(tài)的連接方式非常接近但細(xì)節(jié)差異很磨人。MySQL 的驅(qū)動(dòng)是com.mysql.cj.jdbc.DriverJDBC URL 長這樣jdbc:mysql://localhost:3306/kf_system?useUnicodetruecharacterEncodingutf8useSSLfalseserverTimezoneAsia/ShanghaiallowPublicKeyRetrievaltruePostgreSQL 的驅(qū)動(dòng)是org.postgresql.DriverJDBC URL 則是jdbc:postgresql://localhost:5432/kf_system?sslmodedisable最容易踩坑的是 SSL 配置。MySQL 8.0 默認(rèn)認(rèn)證插件是caching_sha2_password如果不加allowPublicKeyRetrievaltrue某些 JDBC 版本在非 SSL 連接下會(huì)報(bào)錯(cuò)而useSSLfalse和 PG 的sslmodedisable雖然看起來都是“關(guān)閉 SSL”但語義完全不同。MySQL 的 useSSL 主要決定客戶端是否使用 SSL 加密傳輸PG 的 sslmode 則是一個(gè)多級策略disable表示完全不加密prefer表示優(yōu)先加密但允許降級require表示強(qiáng)制加密但不驗(yàn)證證書verify-ca和verify-full則要求校驗(yàn)證書。生產(chǎn)環(huán)境我建議 MySQL 側(cè)開啟 SSL 并配置證書PG 側(cè)至少用require以上級別單純圖省事全關(guān)掉在公網(wǎng)環(huán)境是有風(fēng)險(xiǎn)的。連接池我用的是 HikariCP大部分參數(shù)兩個(gè)庫可以共用但有兩個(gè)地方需要注意。一是 PG 連接的maxLifetime建議設(shè)置得略短一些官方說明是 PostgreSQL 的連接空閑超時(shí)后會(huì)被服務(wù)端回收如果客戶端連接池的 maxLifetime 太長可能出現(xiàn)連接已被服務(wù)端關(guān)閉但客戶端仍在使用的情況我這邊 PG 數(shù)據(jù)源設(shè)置的maxLifetime是 150000ms15分鐘MySQL 則用默認(rèn)的 1800000ms30分鐘。二是 validation query兩個(gè)庫都支持SELECT 1或SELECT 1;PG 驅(qū)動(dòng)其實(shí)自帶連接校驗(yàn)機(jī)制配置了也可以不配置也沒問題。2.2 數(shù)據(jù)類型自增主鍵、布爾、JSON 和時(shí)間這一塊是改造中改動(dòng)量最大的部分兩種數(shù)據(jù)庫的數(shù)據(jù)類型映射差異直接決定了 DDL 怎么寫。先說自增主鍵。MySQL 是AUTO_INCREMENT建表時(shí)直接寫在字段定義里然后可以用LAST_INSERT_ID()拿到剛插入的 ID。PostgreSQL 有兩種做法傳統(tǒng)的是SERIAL/BIGSERIAL偽類型它底層會(huì)創(chuàng)建一個(gè)序列sequence新一點(diǎn)的是標(biāo)準(zhǔn) SQL 的GENERATED ALWAYS AS IDENTITY本質(zhì)上也是序列但更符合規(guī)范。兩個(gè)庫對應(yīng)用層來說最大的區(qū)別在于MySQL 插入一條記錄后需要在同一條連接上執(zhí)行LAST_INSERT_ID()獲取 ID而 PG 可以在INSERT語句后面直接跟RETURNING id把主鍵帶出來更加干凈。布爾類型也是典型差異。MySQL 沒有原生的布爾類型習(xí)慣上用TINYINT(1)存 0 和 1PostgreSQL 提供真正的BOOLEAN類型接受true、false、1、0等輸入。這個(gè)差異會(huì)影響查詢參數(shù)的寫法比如 MyBatis 里傳一個(gè) Boolean 類型的參數(shù)MySQL 分支需要做一下類型轉(zhuǎn)換否則有些驅(qū)動(dòng)會(huì)把 true 變成字符串 true 導(dǎo)致 SQL 報(bào)錯(cuò)。JSON 列是兩個(gè)數(shù)據(jù)庫差異最大、也最容易引發(fā)問題的地方。MySQL 8.0 的 JSON 類型是二進(jìn)制存儲(chǔ)查詢用JSON_EXTRACT(json_col, $.key)或json_col-$.key提取字段去掉引號(hào)再用JSON_UNQUOTE()包裹。PostgreSQL 則區(qū)分json和jsonb兩種類型其中jsonb 是二進(jìn)制格式推薦使用提取字段用json_col-key語法。兩個(gè)庫的 JSON 操作符長得完全不一樣后續(xù)我會(huì)講到具體改寫方案。時(shí)間類型的選型更值得重視。MySQL 常用的DATETIME不帶時(shí)區(qū)信息TIMESTAMP帶時(shí)區(qū)但范圍有限且受會(huì)話時(shí)區(qū)影響。PostgreSQL 里TIMESTAMP不帶時(shí)區(qū)和TIMESTAMPTZ帶時(shí)區(qū)語義區(qū)分非常嚴(yán)格。我的建議是客服系統(tǒng)所有時(shí)間字段統(tǒng)一存TIMESTAMPTZ/TIMESTAMP并在 JDBC URL 上明確指定時(shí)區(qū)應(yīng)用層讀寫都按 UTC 處理展示時(shí)再轉(zhuǎn)本地時(shí)區(qū)。這樣能省掉后面報(bào)表統(tǒng)計(jì)差 8 小時(shí)一類的無妄之災(zāi)。2.3 SQL 語法分頁、更新、UPSERT 與窗口函數(shù)如果只用最基礎(chǔ)的分頁查詢兩個(gè)數(shù)據(jù)庫的體驗(yàn)幾乎一樣LIMIT ? OFFSET ?兩個(gè)庫都支持?？又饕卦诩?xì)節(jié)里。舉一個(gè)最常見的例子MySQL 存在LIMIT 20, 10這種“偏移量在前、行數(shù)在后”的舊寫法PostgreSQL 只認(rèn)LIMIT 10 OFFSET 20。如果團(tuán)隊(duì)里有人養(yǎng)成了 MySQL 的舊習(xí)慣適配時(shí)容易漏。另外兩個(gè)庫對NULL排序的默認(rèn)行為也不同PostgreSQL 升序默認(rèn)NULLS LASTMySQL 升序時(shí)NULL永遠(yuǎn)排在最前面。如果你的業(yè)務(wù)邏輯依賴排空值順序一定要顯式寫ORDER BY col ASC NULLS LAST或等價(jià)寫法PG 直接用NULLS LAST關(guān)鍵字MySQL 則需要ORDER BY ISNULL(col), col ASC這種技巧。更新關(guān)聯(lián)表是另一個(gè)高發(fā)差異區(qū)。MySQL 支持UPDATE ... JOIN語法直接把兩張表關(guān)聯(lián)后更新而 PostgreSQL 沒有這個(gè)語法要用UPDATE ... FROM子句實(shí)現(xiàn)相同效果。這個(gè)差異在客服系統(tǒng)里特別常見典型場景是“把 VIP 訪客在排隊(duì)中的會(huì)話自動(dòng)分配給某個(gè)坐席”我下面會(huì)給出兩邊完整寫法。UPSERT 的差異也要留意。MySQL 用INSERT ... ON DUPLICATE KEY UPDATEPostgreSQL 用INSERT ... ON CONFLICT (id) DO UPDATE SET ...。看起來實(shí)現(xiàn)效果差不多但 MySQL 的DUPLICATE KEY觸發(fā)條件是所有唯一索引沖突都算PG 的ON CONFLICT必須明確指定沖突的列或約束名。窗口函數(shù)方面MySQL 8.0 和 PostgreSQL 都支持ROW_NUMBER()、RANK()這些標(biāo)準(zhǔn)函數(shù)語法幾乎一樣。但如果你還維護(hù)著 MySQL 5.7 的存量客戶那窗口函數(shù)就用不了只能改用變量寫法或子查詢關(guān)聯(lián)復(fù)雜度直接上一個(gè)臺(tái)階。這算是我這次改造里最深刻的一條認(rèn)知雙庫兼容的難度很多時(shí)候取決于你最低要兼容的 MySQL 版本。2.4 事務(wù)、MVCC 與鎖并發(fā)模型完全不同MySQL InnoDB 和 PostgreSQL 都基于 MVCC 實(shí)現(xiàn)多版本并發(fā)控制但內(nèi)部機(jī)制差異非常大。MySQL 的 MVCC 建立在 undo log 上舊版本數(shù)據(jù)存在回滾段里新版本寫在原數(shù)據(jù)頁上所以更新操作時(shí)頁上只有一份數(shù)據(jù)加上回滾段里的舊版本。PostgreSQL 則是每個(gè)元組會(huì)保存多版本更新時(shí)產(chǎn)生一個(gè)新版本元組舊版本元組留在頁面里等待 VACUUM 清理。這個(gè)機(jī)制差異直接帶來一個(gè)影響PostgreSQL 在頻繁更新下會(huì)產(chǎn)生表膨脹bloat需要 autovacuum 持續(xù)工作MySQL InnoDB 沒有這個(gè)概念undo log 會(huì)被自動(dòng)回收。客服系統(tǒng)的會(huì)話表恰恰是更新頻繁的代表每次坐席接入、結(jié)束會(huì)話都會(huì)觸發(fā) UPDATE因此 PG 側(cè)的表膨脹維護(hù)是我上線后重點(diǎn)關(guān)注的問題后面第 5 章會(huì)詳細(xì)說。隔離級別上MySQL 的默認(rèn)級別是REPEATABLE READPostgreSQL 默認(rèn)是READ COMMITTED。在客服系統(tǒng)中如果需要在同一個(gè)事務(wù)里多次查詢某個(gè)統(tǒng)計(jì)數(shù)字并期望結(jié)果一致PG 默認(rèn)級別下第二次查詢可能看到新提交的數(shù)據(jù)需要把事務(wù)級別手動(dòng)調(diào)成REPEATABLE READ或SERIALIZABLE。PG 的 SERIALIZABLE 實(shí)現(xiàn)是 SSI可串行化快照隔離沖突檢測能力很強(qiáng)適合對一致性要求較高的結(jié)算類場景。鎖行為差異也很明顯。MySQL InnoDB 在 REPEATABLE READ 下會(huì)使用間隙鎖gap lock防止幻讀高并發(fā)插入時(shí)鎖沖突概率比 PG 高容易出現(xiàn)死鎖。PG 的常規(guī)行鎖不會(huì)阻塞讀取寫不阻塞讀是其核心賣點(diǎn)之一。另外一個(gè)冷門但好用的特性是 PG 提供pg_advisory_lock咨詢鎖適合實(shí)現(xiàn)“同一訪客只能被一個(gè)坐席接入”這類分布式互斥需求比 MySQL 的GET_LOCK()更靈活。2.5 索引與擴(kuò)展能力從 B-Tree 到 GIN 和 BRIN兩個(gè)數(shù)據(jù)庫默認(rèn)索引都是 B-Tree基礎(chǔ)查詢場景差距不大。差距體現(xiàn)在高級索引類型上。MySQL 8.0 的索引體系相對集中B-Tree、空間索引、FULLTEXT全文索引。PostgreSQL 則是“瑞士軍刀”提供 GIN適合 JSONB、全文檢索、BRIN適合超大表按物理順序掃描、表達(dá)式索引直接對函數(shù)結(jié)果建索引、部分索引只索引滿足條件的行等一堆能力。表達(dá)式索引和部分索引在實(shí)際業(yè)務(wù)中特別有用。比如訪客表里存了用戶昵稱如果要按昵稱忽略大小寫搜索MySQL 只能先把昵稱轉(zhuǎn)成小寫存一列再建索引或者創(chuàng)建生成列PostgreSQL 則可以CREATE INDEX idx_visitor_name_lower ON visitor (LOWER(nickname))查詢時(shí)寫WHERE LOWER(nickname) ?就能命中索引。部分索引則可以只索引當(dāng)前在線的會(huì)話減少索引體積。還有一點(diǎn)值得注意PostgreSQL 的 BRIN 索引非常適合消息表這種數(shù)據(jù)按時(shí)間順序插入、查詢通常限定時(shí)間范圍的場景。BRIN 索引體積只有 B-Tree 的幾十分之一在超大表上能顯著減少存儲(chǔ)開銷但它的掃描性能取決于數(shù)據(jù)的物理順序和相關(guān)性。如果消息表經(jīng)常刪除舊數(shù)據(jù)導(dǎo)致物理順序混亂BRIN 的效果會(huì)打折扣需要配合定期CLUSTER維護(hù)。3. 客服系統(tǒng) PostgreSQL 適配實(shí)操從連接池到 SQL 改寫3.1 多數(shù)據(jù)源配置與驅(qū)動(dòng)整合改造的第一步是把應(yīng)用改成多數(shù)據(jù)源結(jié)構(gòu)開發(fā)環(huán)境同時(shí)連接 MySQL 和 PostgreSQL方便隨時(shí)切換驗(yàn)證。我用的是 Spring Boot 的DataSourceBuilder動(dòng)態(tài)創(chuàng)建兩個(gè)數(shù)據(jù)源然后在一個(gè)通用查詢方法里根據(jù)一個(gè)dbType枚舉路由到不同連接。這里不展開 Spring 多數(shù)據(jù)源的完整實(shí)現(xiàn)只給一個(gè)最小配置示例spring: datasource: mysql: jdbc-url: jdbc:mysql://localhost:3306/kf_system?useUnicodetruecharacterEncodingutf8useSSLfalseserverTimezoneAsia/ShanghaiallowPublicKeyRetrievaltrue driver-class-name: com.mysql.cj.jdbc.Driver username: kf_user password: xxxxxx postgresql: jdbc-url: jdbc:postgresql://localhost:5432/kf_system?sslmodedisable driver-class-name: org.postgresql.Driver username: kf_user password: xxxxxx關(guān)鍵點(diǎn)在于兩個(gè)數(shù)據(jù)源對應(yīng)的實(shí)體 Bean 必須設(shè)置Primary標(biāo)記否則 Spring 在自動(dòng)注入時(shí)會(huì)因?yàn)榇嬖诙鄠€(gè) DataSource Bean 而報(bào)錯(cuò)。另外連接池參數(shù)別只配一份兩種數(shù)據(jù)庫的推薦值是有差異的簡單復(fù)制配置容易埋坑。3.2 核心表結(jié)構(gòu)改造DDL 對比直接看我改造后的兩張核心表 DDL 對比。MySQL 版本CREATE TABLE conversation ( id BIGINT NOT NULL AUTO_INCREMENT COMMENT 會(huì)話ID, tenant_id BIGINT NOT NULL DEFAULT 0, visitor_id BIGINT NOT NULL, agent_id BIGINT DEFAULT NULL, status TINYINT NOT NULL DEFAULT 0, channel VARCHAR(20) NOT NULL DEFAULT web, ext JSON DEFAULT NULL, created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP, updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, PRIMARY KEY (id), KEY idx_tenant_status (tenant_id, status), KEY idx_visitor (visitor_id) ) ENGINEInnoDB DEFAULT CHARSETutf8mb4; CREATE TABLE message ( id BIGINT NOT NULL AUTO_INCREMENT, conversation_id BIGINT NOT NULL, sender_type TINYINT NOT NULL, content TEXT, created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP, PRIMARY KEY (id), KEY idx_conversation (conversation_id, created_at) ) ENGINEInnoDB DEFAULT CHARSETutf8mb4;PostgreSQL 版本CREATE TABLE conversation ( id BIGSERIAL PRIMARY KEY, tenant_id BIGINT NOT NULL DEFAULT 0, visitor_id BIGINT NOT NULL, agent_id BIGINT, status SMALLINT NOT NULL DEFAULT 0, channel VARCHAR(20) NOT NULL DEFAULT web, ext JSONB DEFAULT NULL, created_at TIMESTAMPTZ NOT NULL DEFAULT now(), updated_at TIMESTAMPTZ NOT NULL DEFAULT now() ); CREATE INDEX idx_tenant_status ON conversation (tenant_id, status); CREATE INDEX idx_visitor ON conversation (visitor_id); CREATE TABLE message ( id BIGSERIAL PRIMARY KEY, conversation_id BIGINT NOT NULL, sender_type SMALLINT NOT NULL, content TEXT, created_at TIMESTAMPTZ NOT NULL DEFAULT now() ); CREATE INDEX idx_conversation ON message (conversation_id, created_at);這份對比能看出一堆有意思的差異。MySQL 建索引是寫在建表語句內(nèi)部的PG 則通常分開寫CREATE INDEX語義上沒有本質(zhì)區(qū)別但 MySQL 的索引名是表級命名空間PG 的索引名是模式級命名空間也就是說 PG 同一模式下所有索引名必須全局唯一。這個(gè)問題在小項(xiàng)目里不容易暴露一旦表多了索引名沖突會(huì)讓你抓狂。ON UPDATE CURRENT_TIMESTAMP是 MySQL 的一個(gè)語法糖PG 原生不支持需要寫觸發(fā)器或者完全在應(yīng)用層賦值。我的處理方式是統(tǒng)一在應(yīng)用層每次更新時(shí)顯式設(shè)置updated_at now()徹底拋棄數(shù)據(jù)庫自動(dòng)更新反而讓行為更可控。自增主鍵BIGSERIAL在 PG 里只是方便真正的長期推薦是GENERATED ALWAYS AS IDENTITY因?yàn)?SERIAL 和普通 sequence 綁定后續(xù)做表結(jié)構(gòu)遷移、序列重置時(shí)不如 IDENTITY 順手。不過這次為了和存量腳本保持風(fēng)格統(tǒng)一我用的還是BIGSERIAL。3.3 關(guān)鍵業(yè)務(wù) SQL 的方言適配下面挑幾個(gè)客服系統(tǒng)里最高頻的 SQL給出兩個(gè)數(shù)據(jù)庫的具體寫法。第一個(gè)是“把 VIP 訪客的排隊(duì)會(huì)話自動(dòng)分配給某個(gè)坐席”的關(guān)聯(lián)更新。MySQLUPDATE conversation c JOIN visitor v ON v.id c.visitor_id SET c.agent_id 1001, c.status 1, c.updated_at NOW() WHERE v.vip_level 1 AND c.status 0;PostgreSQLUPDATE conversation c SET agent_id 1001, status 1, updated_at now() FROM visitor v WHERE v.id c.visitor_id AND v.vip_level 1 AND c.status 0;兩個(gè)寫法的語義類似但 PG 的FROM子句在復(fù)雜場景下功能更強(qiáng)可以在FROM里放子查詢、JOIN 多個(gè)表自由度更高。第二個(gè)是“每個(gè)會(huì)話取最新一條消息”的分組排序需求。MySQL 8.0 和 PG 都可以用窗口函數(shù)SELECT * FROM ( SELECT m.*, ROW_NUMBER() OVER (PARTITION BY conversation_id ORDER BY created_at DESC) AS rn FROM message m ) t WHERE rn 1;這個(gè) SQL 在 MySQL 8.0 和 PG 完全通用但如果你的最小 MySQL 版本是 5.7就不得不換成變量寫法或者GROUP BY GROUP_CONCAT這類土辦法而且行為還未必一致。所以我在項(xiàng)目里強(qiáng)制要求如果某個(gè) SQL 必須依賴 MySQL 8.0 才能寫簡潔版那就在代碼里按版本分支處理不要讓老版本 MySQL 硬扛新語法。第三個(gè)是所謂 UPSERT用于“坐席心跳狀態(tài)更新”。MySQLINSERT INTO agent_status (agent_id, status, updated_at) VALUES (1001, 1, NOW()) ON DUPLICATE KEY UPDATE status VALUES(status), updated_at NOW();MySQL 8.0.20 以后VALUES()函數(shù)已經(jīng)被標(biāo)記為過時(shí)官方建議改成行別名語法所以我實(shí)際生產(chǎn)里用的是新寫法INSERT INTO agent_status (agent_id, status, updated_at) VALUES (1001, 1, NOW()) AS new ON DUPLICATE KEY UPDATE status new.status, updated_at new.updated_at;PostgreSQL 對應(yīng)的寫法是INSERT INTO agent_status (agent_id, status, updated_at) VALUES (1001, 1, now()) ON CONFLICT (agent_id) DO UPDATE SET status EXCLUDED.status, updated_at EXCLUDED.updated_at;注意 PG 的ON CONFLICT后面必須指定唯一的沖突列或約束否則語法報(bào)錯(cuò)這一點(diǎn)比 MySQL 嚴(yán)格得多。3.4 全文檢索讓聊天記錄可以被搜索客服業(yè)務(wù)里幾乎必有“按關(guān)鍵詞搜索聊天記錄”的功能。這個(gè)需求在兩個(gè)數(shù)據(jù)庫上實(shí)現(xiàn)路徑差異巨大。MySQL 的 FULLTEXT 索引用法比較簡單中文場景建議使用ngram解析器ALTER TABLE message ADD FULLTEXT INDEX ft_content (content) WITH PARSER ngram; SELECT * FROM message WHERE MATCH(content) AGAINST(退款 IN BOOLEAN MODE) AND conversation_id 123;MySQL 的 ngram 分詞器內(nèi)置了對中文的分詞支持雖然粒度比較粗但勝在開箱即用對絕大多數(shù)客服搜索場景足夠。PostgreSQL 的全文檢索體系更強(qiáng)大也更復(fù)雜。核心概念是tsvector文檔向量和tsquery查詢向量用操作符做匹配。但PG 默認(rèn)沒有內(nèi)置中文分詞器這是最大的坑。如果你直接用默認(rèn)配置中文內(nèi)容會(huì)被當(dāng)作連續(xù)的一整段處理to_tsquery(退款)匹配不出任何結(jié)果。常用的解決方案有兩個(gè)一是安裝zhparser或pg_jieba擴(kuò)展做中文分詞二是不夠裝擴(kuò)展的時(shí)候用pg_trgm模塊配合 LIKE 查詢實(shí)現(xiàn)近似效果CREATE EXTENSION IF NOT EXISTS pg_trgm; CREATE INDEX idx_message_trgm ON message USING GIN (content gin_trgm_ops); SELECT * FROM message WHERE content LIKE %退款% AND conversation_id 123;pg_trgm對中文的處理是按每連續(xù)三個(gè)字符切分 trigram雖然語義理解不如真正的分詞器但做模糊搜索和關(guān)鍵詞匹配效果已經(jīng)很能打。我的線上方案是能裝zhparser的客戶環(huán)境用全文檢索不能裝擴(kuò)展的降級用pg_trgm兩邊給用戶的搜索體驗(yàn)差異不大。這也算是我這次改造中比較深刻的體會(huì)在線客服系統(tǒng)的全文搜索MySQL 開箱即用PG 要額外付出分詞器選型和安裝的成本。如果你們的客戶環(huán)境卡得比較死不允許裝擴(kuò)展那 PG 側(cè)的中文搜索體驗(yàn)會(huì)明顯弱于 MySQL。3.5 存量數(shù)據(jù)遷移與校驗(yàn)因?yàn)椴皇钦w遷移而是新環(huán)境直連 PG我這邊沒有做全量歷史數(shù)據(jù)搬移只需要把存量客戶的 MySQL 數(shù)據(jù)導(dǎo)出備份再在 PG 上從零初始化。但如果你要把一套已經(jīng)跑了好幾年、積累了大量歷史消息的系統(tǒng)從 MySQL 遷到 PostgreSQL推薦直接用pgloader這個(gè)工具。pgloader 一條命令就能把表結(jié)構(gòu)和數(shù)據(jù)搬過去它內(nèi)置類型映射規(guī)則會(huì)把 MySQL 的AUTO_INCREMENT轉(zhuǎn)成 PG 的BIGSERIALTINYINT轉(zhuǎn)成SMALLINTDATETIME轉(zhuǎn)成TIMESTAMP還能自動(dòng)創(chuàng)建序列。基本用法pgloader mysql://kf_user:passlocalhost/kf_system postgresql://kf_user:passlocalhost/kf_system遷移后有一個(gè)必須做的手動(dòng)步驟因?yàn)锽IGSERIAL的序列不會(huì)跟著顯式 ID 插入自動(dòng)更新如果不修復(fù)接下來新插入的記錄可能直接主鍵沖突。手動(dòng)把序列跳到當(dāng)前最大值即可SELECT setval(conversation_id_seq, (SELECT max(id) FROM conversation));遷移后的校驗(yàn)建議分三層做先比對表數(shù)量和行數(shù)是否一致再對每張表做關(guān)鍵維度聚合比對比如 count、max 時(shí)間、sum 某數(shù)值列最后隨機(jī)抽幾十條業(yè)務(wù)記錄逐一對比字段值。只比對行數(shù)是最容易通過的字段類型的隱式轉(zhuǎn)換造成的精度差異往往藏在明細(xì)數(shù)據(jù)里。4. 客服場景下的實(shí)測對比性能、運(yùn)維與選型4.1 讀寫壓測消息寫入與會(huì)話查詢改造完成后我在同一臺(tái) 8 核 16G 的測試機(jī)上分別裝了 MySQL 8.0.36 和 PostgreSQL 16.2用同樣一套客服系統(tǒng)的讀寫腳本做壓力測試。壓測模型是這樣的模擬 200 個(gè)坐席在線1000 個(gè)訪客持續(xù)發(fā)消息每秒并發(fā)寫入約 500 條消息同時(shí)每 5 秒執(zhí)行一次“取每個(gè)會(huì)話最新消息”的列表查詢和“按訪客昵稱模糊搜索會(huì)話”的查詢。先說結(jié)論在 500 TPS 的寫入壓力下兩個(gè)數(shù)據(jù)庫的消息插入響應(yīng)時(shí)間幾乎沒有明顯差距都在個(gè)位數(shù)毫秒級別。這說明對于客服系統(tǒng)這個(gè)量級瓶頸根本不在數(shù)據(jù)庫引擎本身而在應(yīng)用層的連接管理、磁盤 IO 和網(wǎng)絡(luò)開銷。真正拉開差距的是兩類查詢一類是大范圍的聚合統(tǒng)計(jì)比如按小時(shí)統(tǒng)計(jì) 30 天內(nèi)的消息量PG 的優(yōu)化器在一些復(fù)雜 JOIN 場景下估算更準(zhǔn)執(zhí)行計(jì)劃更穩(wěn)定另一類是 JSON 字段的過濾查詢PG 的jsonb配合 GIN 索引比 MySQL 的 JSON 類型更順手索引命中率更高。但 MySQL 也不是全面落敗。在純并發(fā)插入混合少量更新的場景下MySQL InnoDB 的聚簇索引結(jié)構(gòu)讓主鍵范圍掃描非常高效消息表按時(shí)間范圍拉取歷史記錄的查詢MySQL 的響應(yīng)速度甚至略快于 PG。這種差異和存儲(chǔ)結(jié)構(gòu)強(qiáng)相關(guān)InnoDB 是聚簇索引數(shù)據(jù)按主鍵物理存儲(chǔ)主鍵連續(xù)插入時(shí)順序 IO 效率高PG 的 heap 表結(jié)構(gòu)下數(shù)據(jù)按插入順序堆存索引掃描后需要回表隨機(jī) IO 占比更高。4.2 運(yùn)維機(jī)制MVCC 清理、備份與監(jiān)控運(yùn)維層面的差異獨(dú)立開發(fā)者感受最明顯。MySQL 的 InnoDB 引擎把 MVCC 舊版本放在 undo log 里自動(dòng)管理用戶幾乎不需要干預(yù)purge線程會(huì)后臺(tái)清理。PostgreSQL 則不一樣每次 UPDATE 產(chǎn)生的新版本舊元組必須由VACUUM機(jī)制清理。雖然 PG 默認(rèn)開著 autovacuum但在消息表這種寫入量大、更新頻繁的表上如果 VACUUM 跟不上產(chǎn)生速度表膨脹會(huì)越來越嚴(yán)重查詢性能直線下滑。我上線 PG 后遇到過一個(gè)問題會(huì)話表的膨脹率在兩周內(nèi)從 1 倍漲到 3.5 倍統(tǒng)計(jì) SQL 從 80 毫秒退化到 600 多毫秒。原因是我的會(huì)話狀態(tài)更新非常頻繁一個(gè)會(huì)話生命周期里要 UPDATE 好幾次舊版本堆積而 autovacuum 的閾值觸發(fā)不夠積極。解決辦法是把這張表的 autovacuum 參數(shù)調(diào)得更激進(jìn)ALTER TABLE conversation SET (autovacuum_vacuum_scale_factor 0.05, autovacuum_vacuum_threshold 1000);同時(shí)定期手動(dòng)執(zhí)行VACUUM (ANALYZE, VERBOSE) conversation;備份方面MySQL 的主流方案是mysqldump邏輯備份加上 binlog 增量PostgreSQL 則常用pg_dump邏輯備份配合pg_basebackup物理備份和 WAL 歸檔。兩者能力對等但命令參數(shù)和恢復(fù)流程完全不同運(yùn)維腳本要分別維護(hù)。監(jiān)控方面MySQL 用SHOW ENGINE INNODB STATUS、performance_schemaPG 用pg_stat_activity、pg_stat_user_tables、pg_locks等系統(tǒng)視圖兩者監(jiān)控維度都很全但長期運(yùn)維你需要兩套監(jiān)控看板。還有 checkpoint 這個(gè)要點(diǎn)。PG 的 checkpoint 負(fù)責(zé)把 WAL 日志中已提交的事務(wù)刷到數(shù)據(jù)文件里checkpoint_timeout和max_wal_size的配置會(huì)影響崩潰恢復(fù)時(shí)間MySQL 的 redo log 是循環(huán)寫入自動(dòng)管理用戶基本不用管。對于獨(dú)立開發(fā)者來說這就是“省心”和“可控”之間的權(quán)衡。4.3 功能與生態(tài)對比速查表我把這次改造中實(shí)際對比過的維度整理成一張速查表方便你直接拿來參考。對比維度MySQL 8.0PostgreSQL 16默認(rèn)隔離級別REPEATABLE READREAD COMMITTED自增主鍵AUTO_INCREMENTSERIAL / IDENTITY SEQUENCE布爾類型TINYINT(1)BOOLEANJSON 類型JSON無 jsonb 概念json / jsonb中文全文檢索FULLTEXT ngram 開箱即用需裝 zhparser / pg_jieba或用 pg_trgm復(fù)雜 JOIN 優(yōu)化8.0 優(yōu)化器升級明顯基因算法 并行查詢更強(qiáng)UPDATE 關(guān)聯(lián)UPDATE ... JOINUPDATE ... FROMUPSERTON DUPLICATE KEY UPDATEON CONFLICT ... DO UPDATE分組取每組最新窗口函數(shù)或走變量窗口函數(shù)表達(dá)式/部分索引不直接支持原生支持表膨脹維護(hù)無undo 自動(dòng)回收需要 autovacuum / vacuum 手工干預(yù)備份工具mysqldump / binlogpg_dump / pg_basebackup / WAL 歸檔并發(fā)寫入鎖沖突RR 下間隙鎖較多行鎖不阻塞讀沖突更少適合場景通用 CRUD、中小型業(yè)務(wù)、團(tuán)隊(duì)熟悉 MySQL復(fù)雜查詢、JSON/全文檢索、強(qiáng)一致性要求4.4 到底該選哪一個(gè)經(jīng)過這次改造和實(shí)測我自己的判斷是不要帶任何品牌感情去選型邊界條件決定結(jié)果。如果你的在線客服系統(tǒng)是標(biāo)準(zhǔn) SaaS 形態(tài)自己控制運(yùn)行環(huán)境團(tuán)隊(duì)對 MySQL 熟悉業(yè)務(wù)查詢以 CRUD 為主報(bào)表復(fù)雜度有限那么 MySQL 8.0 會(huì)是非常省心的選擇。它開箱即用、運(yùn)維壓力小、中文全文檢索體驗(yàn)好而且云廠商生態(tài)最成熟。反過來如果客戶環(huán)境強(qiáng)制 PG或者你的業(yè)務(wù)對 JSON 數(shù)據(jù)建模、復(fù)雜聚合統(tǒng)計(jì)、地理空間查詢這類能力有強(qiáng)需求同時(shí)團(tuán)隊(duì)愿意投入精力學(xué)習(xí) PG 的 VACUUM、WAL、并發(fā)模型這些運(yùn)維知識(shí)那么 PostgreSQL 16 能給你更長期的功能成長空間。我個(gè)人的態(tài)度是“誰都能跑但你要知道它在為什么場景最優(yōu)”。雙庫兼容本身不復(fù)雜復(fù)雜的是把兩邊的運(yùn)維差異和 SQL 方言都測試到位。5. 排坑實(shí)錄那些文檔里查不到的問題5.1 大小寫敏感導(dǎo)致的“幽靈丟數(shù)據(jù)”上線第一周客戶反饋“訪客昵稱搜索經(jīng)常漏人”。我查了很久最后定位到是排序規(guī)則差異MySQL 的默認(rèn)排序規(guī)則utf8mb4 下通常是 utf8mb4_general_ci 或 utf8mb4_0900_ai_ci大小寫不敏感WHERE nickname ABc能匹配abcPostgreSQL 默認(rèn)的排序規(guī)則是大小寫敏感的ABc和abc是兩個(gè)完全不同的字符串。看起來是個(gè)小問題但會(huì)在搜索、去重、登錄校驗(yàn)等各種環(huán)節(jié)悄無聲息地出現(xiàn)。解決方案是在 PG 側(cè)安裝citext擴(kuò)展讓某個(gè)列在比較時(shí)忽略大小寫CREATE EXTENSION IF NOT EXISTS citext; ALTER TABLE visitor ALTER COLUMN nickname TYPE citext;也可以不加擴(kuò)展、保持原始列類型但在查詢條件里統(tǒng)一寫LOWER(nickname) LOWER(?)并配合第 2.5 節(jié)說的表達(dá)式索引。建議業(yè)務(wù)早期就定好統(tǒng)一規(guī)則不要等線上出了數(shù)據(jù)問題再回頭補(bǔ)。5.2 JSON 字段查詢語法與索引的坑MySQL 的JSON_EXTRACT(ext, $.city)和 PG 的ext-city語法不一致這是繞不開的。更隱蔽的是索引問題MySQL 雖然支持對 JSON 列建多值索引和生成列索引但操作符匹配路徑有限PG 則可以對jsonb直接建 GIN 索引然后使用操作符做包含判斷CREATE INDEX idx_visitor_ext ON visitor USING GIN (ext); SELECT * FROM visitor WHERE ext {city: 上海};這個(gè)查詢能走索引性能非常好。但注意 PG 里ext-city 上海這種寫法通常不會(huì)命中 GIN 索引要走 B-Tree 表達(dá)式索引或接受全表掃描。我一開始沒搞清楚這一點(diǎn)壓測時(shí)發(fā)現(xiàn)走了Seq Scan數(shù)據(jù)量一大就慢了。所以 JSON 查詢不要只看語法對不對要結(jié)合 EXPLAIN ANALYZE 確認(rèn)索引有沒有用上。5.3 時(shí)區(qū)與 TIMESTAMPTZ客服報(bào)表差八小時(shí)報(bào)表統(tǒng)計(jì)顯示“會(huì)話量少了一半”排查發(fā)現(xiàn)是時(shí)區(qū)問題。測試時(shí)直接往 PG 庫插數(shù)據(jù)應(yīng)用連接沒有指定時(shí)區(qū)數(shù)據(jù)庫now()存的是 UTC報(bào)表查詢用date_trunc(hour, created_at AT TIME ZONE Asia/Shanghai)才能得到北京時(shí)間的小時(shí)分組。而 MySQL 側(cè)因?yàn)閟erverTimezoneAsia/Shanghai配在 JDBC URL 里行為始終一致。這個(gè)問題的根源在于MySQL 的時(shí)間行為更依賴于連接參數(shù)PG 的時(shí)間行為更依賴于數(shù)據(jù)庫會(huì)話配置兩邊沒有一個(gè)統(tǒng)一的“標(biāo)準(zhǔn)答案”。我的處理是應(yīng)用層統(tǒng)一用Instant或帶時(shí)區(qū)的OffsetDateTime存時(shí)間數(shù)據(jù)庫層 PG 全用TIMESTAMPTZMySQL 全用DATETIME并確保 JDBC 時(shí)區(qū)和服務(wù)器時(shí)區(qū)一致報(bào)表查詢一律顯式轉(zhuǎn)換時(shí)區(qū)。5.4 SSL 連接報(bào)錯(cuò)與 MySQL socket 問題第 2.1 節(jié)已經(jīng)講過useSSLfalse和sslmodedisable的語義區(qū)別這里補(bǔ)充一個(gè)真實(shí)報(bào)錯(cuò)場景。有一天新同事的本地環(huán)境連 MySQL 一直報(bào)ERROR 2002 (HY000): Cant connect to local MySQL server through socket /tmp/mysql.sock排查發(fā)現(xiàn)是他本機(jī) MySQL 服務(wù)沒有啟動(dòng)而客戶端默認(rèn)走 Unix socket 而不是 TCP。解決辦法是確保 MySQL 服務(wù)啟動(dòng)或者連接時(shí)強(qiáng)制走 TCPmysql -h 127.0.0.1 -P 3306 -u kf_user -p。PG 側(cè)類似的坑是 JDBC 驅(qū)動(dòng)版本和服務(wù)器版本差異。PG 的 JDBC 驅(qū)動(dòng)對sslmodeprefer的處理在歷史上有些微妙變化如果服務(wù)器不支持 SSL 而驅(qū)動(dòng)又強(qiáng)行要 SSL會(huì)報(bào)一個(gè)看起來像“連接被重置”的錯(cuò)誤排查半天才發(fā)現(xiàn)是 SSL 握手失敗。我的建議是本地開發(fā)和內(nèi)網(wǎng)環(huán)境直接顯式sslmodedisable不要依賴默認(rèn)值。5.5 自增序列錯(cuò)亂與數(shù)據(jù)遷移這個(gè)坑在 3.5 節(jié)已經(jīng)提過數(shù)據(jù)導(dǎo)入后不重置序列新插入記錄直接主鍵沖突。這里補(bǔ)充一個(gè)更隱蔽的場景即使沒有做數(shù)據(jù)遷移只要有人在 PG 里手動(dòng)插入過顯式 ID 的行比如管理員手工補(bǔ)數(shù)據(jù)序列同樣不會(huì)自動(dòng)更新。和 MySQL 的AUTO_INCREMENT在插入大號(hào) ID 后會(huì)自動(dòng)修正的行為不同PG 的序列是“事不關(guān)己”的必須手動(dòng)同步。一個(gè)通用修復(fù)腳本SELECT setval( pg_get_serial_sequence(conversation, id), (SELECT max(id) FROM conversation) );建議在每次手工導(dǎo)入數(shù)據(jù)后都跑一遍并且把它寫進(jìn)部署手冊防止下次忘記。5.6 表膨脹、VACUUM 與性能劣化4.2 節(jié)講了膨脹率的問題這里給出一個(gè)量化判斷方法。檢查表的膨脹情況SELECT relname, n_live_tup, n_dead_tup, CASE WHEN n_live_tup 0 THEN round(n_dead_tup * 100.0 / n_live_tup, 1) END AS dead_pct FROM pg_stat_user_tables WHERE relname IN (conversation, message);當(dāng)dead_pct長期超過 20% 時(shí)就該重點(diǎn)處理了。除了調(diào)高這張表的 autovacuum 頻率還可以在業(yè)務(wù)低峰期執(zhí)行VACUUM FULL回收物理空間。注意VACUUM FULL會(huì)持有表級鎖如果在線客服系統(tǒng)是 7x24 小時(shí)運(yùn)行的要慎重安排窗口或者干脆不用它只做普通 VACUUM 讓 autovacuum 持續(xù)工作。還有一個(gè)容易忽略的點(diǎn)checkpoint_timeout和max_wal_size的配置直接影響 WAL 刷盤頻率和恢復(fù)時(shí)間如果 WAL 頻繁觸發(fā) checkpoint磁盤 IO 會(huì)被拖累表現(xiàn)為整體響應(yīng)變慢。我的測試環(huán)境里把max_wal_size調(diào)大到 2GB、checkpoint_timeout保持默認(rèn) 5 分鐘寫峰值下 IO 平穩(wěn)了很多。5.7 死鎖與鎖等待排查雙庫兼容后死鎖排查思路完全不同。MySQL 側(cè)排查死鎖SHOW ENGINE INNODB STATUS;重點(diǎn)看LATEST DETECTED DEADLOCK段落它會(huì)打印出兩個(gè)事務(wù)各自的 SQL 和鎖住的記錄。PG 側(cè)沒有直接打印死鎖詳情的命令需要結(jié)合系統(tǒng)視圖SELECT pid, state, wait_event_type, wait_event, query FROM pg_stat_activity WHERE wait_event_type Lock; SELECT * FROM pg_locks WHERE NOT granted;PG 的死鎖信息會(huì)寫到數(shù)據(jù)庫日志里log_lock_waits on參數(shù)開啟后可以在日志中看到鎖等待超過閾值的會(huì)話。建議從一開始就把log_lock_waits和deadlock_timeout配置到合理值比如deadlock_timeout 2s否則鎖問題排查全靠猜。5.8 問題速查表癥狀MySQL 排查方向PostgreSQL 排查方向連不上數(shù)據(jù)庫服務(wù)是否啟動(dòng)、socket 路徑、useSSL 配置服務(wù)是否啟動(dòng)、sslmode 配置、驅(qū)動(dòng)版本中文搜索查不到FULLTEXT 索引是否用 ngram是否安裝中文分詞擴(kuò)展 / pg_trgm 是否生效時(shí)間統(tǒng)計(jì)差幾小時(shí)JDBC serverTimezone 是否一致是否使用 TIMESTAMPTZ、查詢是否顯式轉(zhuǎn)換時(shí)區(qū)字符串匹配漏數(shù)據(jù)排序規(guī)則是否大小寫不敏感是否受默認(rèn)大小寫敏感影響需 citext插入主鍵沖突較少見AUTO_INCREMENT 自動(dòng)修正序列未 setval需要手動(dòng)修復(fù)表越來越大查詢變慢InnoDB 自動(dòng)管理關(guān)注慢查詢?nèi)罩緉_dead_tup 偏高檢查 autovacuum死鎖SHOW ENGINE INNODB STATUSpg_stat_activity pg_locks 數(shù)據(jù)庫日志關(guān)聯(lián)更新報(bào)錯(cuò)UPDATE ... JOIN 語法 OK需要改寫為 UPDATE ... FROM6. 改造完成后的幾點(diǎn)體會(huì)6.1 雙庫兼容的隱性成本遠(yuǎn)超預(yù)期這次改造最深的感受是讓一套系統(tǒng)同時(shí)跑兩個(gè)數(shù)據(jù)庫真正的成本不在寫代碼而在持續(xù)測試和運(yùn)維。SQL 改寫是有限的工作量改完就完了但每個(gè)版本迭代都要在兩個(gè)數(shù)據(jù)庫上回歸測試每個(gè)客戶環(huán)境可能需要兩套不同的備份、監(jiān)控和調(diào)優(yōu)方案這些才是長期成本。如果你只是為了“多支持一個(gè)數(shù)據(jù)庫”而支持沒有實(shí)際客戶需求在背后驅(qū)動(dòng)我不會(huì)建議你去做。另一個(gè)容易被低估的點(diǎn)是團(tuán)隊(duì)認(rèn)知成本。你的代碼里會(huì)到處出現(xiàn)dbType判斷同事每次寫一條新 SQL 都要想“這個(gè)語法兩個(gè)庫都支持嗎”這個(gè)思考負(fù)擔(dān)會(huì)持續(xù)消耗生產(chǎn)力。我的緩解辦法是寫了一份內(nèi)部 SQL 編寫規(guī)范明確列出哪些語法不允許直接使用比如 MySQL 的LIMIT offset, count、PG 的NULLS FIRST如果要對齊兩邊就得繞開哪些場景必須走方言分支。規(guī)范雖然不能消除全部成本但至少讓團(tuán)隊(duì)有據(jù)可依。6.2 給獨(dú)立開發(fā)者的一句話經(jīng)驗(yàn)如果你也是獨(dú)立開發(fā)、正在做在線客服類產(chǎn)品或任何數(shù)據(jù)密集型業(yè)務(wù)我最后想分享幾條實(shí)際經(jīng)驗(yàn)第一數(shù)據(jù)庫選型要跟著客戶和場景走不要有“個(gè)人偏好”這種情緒。MySQL 很親切PG 很強(qiáng)大但沒有一個(gè)數(shù)據(jù)庫能覆蓋所有場景能用基礎(chǔ)設(shè)施約束直接解決商務(wù)問題才是最重要的。第二如果你預(yù)感到未來可能會(huì)適配 PG那從第一天開始就盡量少寫方言特性。能用LIMIT ? OFFSET ?就用這個(gè)能用標(biāo)準(zhǔn) SQL 窗口函數(shù)就不要碰 MySQL 特有的GROUP_CONCAT替代方案。寫 SQL 時(shí)多問自己一句“這條語句換一個(gè)數(shù)據(jù)庫還成立嗎”后面返工最少。第三工具的差異值得擁抱。PG 的pg_stat_activity、pg_locks、EXPLAIN 的豐富程度確實(shí)比 MySQL 更細(xì)反過來 MySQL 的SHOW ENGINE INNODB STATUS在很多死鎖場景又比 PG 直觀。兩套工具都學(xué)會(huì)不虧。改造完成到現(xiàn)在已經(jīng)跑了一個(gè)多月線上 PG 和 MySQL 兩邊都穩(wěn)定運(yùn)行。最后再分享一個(gè)小技巧雙庫兼容的項(xiàng)目一定要在自動(dòng)化測試?yán)锿瑫r(shí)跑兩個(gè)數(shù)據(jù)源的用例。我最初只在 MySQL 數(shù)據(jù)源上跑單測結(jié)果上 PG 環(huán)境后一連暴露了好幾個(gè)只有 SQL 方言差異才會(huì)觸發(fā)的 bug。把兩個(gè)數(shù)據(jù)源納入 CI 之后這類問題基本絕跡了。數(shù)據(jù)庫沒有絕對的好壞能讓業(yè)務(wù)高效跑起來、團(tuán)隊(duì)維護(hù)得起、客戶接受得了那這個(gè)選擇就是對的。 在線客服系統(tǒng)雙數(shù)據(jù)庫兼容實(shí)踐：MySQL與PostgreSQL適配全解析
我的在線客服系統(tǒng)從第一版上線到現(xiàn)在跑了兩年多后端數(shù)據(jù)庫一直用的 MySQL 8.0業(yè)務(wù)上沒出過什么大問題。直到上個(gè)月簽了一個(gè)私有化部署的客戶對方既有技術(shù)棧統(tǒng)一是 PostgreSQL數(shù)據(jù)庫、備份策略、監(jiān)控體系全部圍繞 PG 搭建我?guī)缀鯖]有猶豫老老實(shí)實(shí)開始給客服系統(tǒng)增加 PostgreSQL 支持。這個(gè)改造過程比我想象中要復(fù)雜不少。雖然兩個(gè)庫都支持標(biāo)準(zhǔn) SQL但真到了生產(chǎn)級適配數(shù)據(jù)類型、SQL 方言、事務(wù)模型、連接參數(shù)、備份恢復(fù)、索引策略幾乎每個(gè)環(huán)節(jié)都有差異。既然踩了一輪坑就把整個(gè)過程整理成一篇手記重點(diǎn)講清楚兩件事一是怎么讓在線客服系統(tǒng)同時(shí)跑在 MySQL 和 PostgreSQL 上二是這兩個(gè)數(shù)據(jù)庫在同樣的業(yè)務(wù)場景下到底差在哪里各自適合什么情況。無論你是獨(dú)立開發(fā)者、小團(tuán)隊(duì)技術(shù)負(fù)責(zé)人還是正在做技術(shù)選型這篇內(nèi)容應(yīng)該都能給你一些參考。1. 項(xiàng)目背景為什么客服系統(tǒng)需要同時(shí)兼容 MySQL 與 PostgreSQL1.1 在線客服系統(tǒng)的數(shù)據(jù)模型與存儲(chǔ)需求先交代一下我這套在線客服系統(tǒng)的數(shù)據(jù)模型方便后面講差異的時(shí)候有具體的業(yè)務(wù)載體。核心表有這幾張會(huì)話表conversation保存每一次訪客和坐席之間的會(huì)話包含租戶 ID、訪客 ID、坐席 ID、狀態(tài)字段排隊(duì)中、接入中、已結(jié)束、渠道類型Web、小程序、App消息表message保存聊天內(nèi)容字段包括會(huì)話 ID、發(fā)送方類型訪客/坐席/系統(tǒng)、消息內(nèi)容、發(fā)送時(shí)間訪客表visitor記錄訪客信息和擴(kuò)展屬性我用了一個(gè) JSON 字段存諸如地區(qū)、來源頁面、設(shè)備信息等非結(jié)構(gòu)化數(shù)據(jù)坐席表agent和操作日志表operation_log相對簡單以讀寫為主。這個(gè)業(yè)務(wù)場景有幾個(gè)比較鮮明的存儲(chǔ)特征消息表寫入非常頻繁屬于典型的持續(xù)追加型數(shù)據(jù)會(huì)話狀態(tài)會(huì)不斷更新排隊(duì)轉(zhuǎn)接入、接入轉(zhuǎn)結(jié)束、坐席改派運(yùn)營后臺(tái)需要做大量歷史會(huì)話查詢和聚合統(tǒng)計(jì)客服經(jīng)常要按關(guān)鍵詞搜索聊天記錄。用一句話總結(jié)就是高寫入、有更新、重查詢、需要全文檢索。這些特征在后續(xù)對比 MySQL 和 PostgreSQL 時(shí)會(huì)反復(fù)提到。1.2 獨(dú)立開發(fā)者的默認(rèn)選擇MySQL大部分獨(dú)立開發(fā)者做項(xiàng)目的第一反應(yīng)都是 MySQL我也不例外。理由很現(xiàn)實(shí)資料多、社區(qū)活躍、云上隨便都能買一個(gè)兼容實(shí)例出了問題搜索一下基本都能找到答案。MySQL 8.0 的 JSON 類型、窗口函數(shù)、CTE公共表表達(dá)式這些能力也已經(jīng)補(bǔ)齊對于客服系統(tǒng)這個(gè)體量的應(yīng)用來說功能上完全夠用。另一個(gè)關(guān)鍵點(diǎn)是運(yùn)維成本。獨(dú)立開發(fā)者沒有專職 DBAMySQL 的默認(rèn)配置相對“友善”InnoDB 引擎做了大量自適應(yīng)工作redo log、buffer pool 這些機(jī)制基本不需要人工干預(yù)。相比之下PostgreSQL 的 autovacuum、checkpoint、WAL 歸檔這些概念初次接觸的人容易懵。所以早期選擇 MySQL 是一個(gè)非常典型的“確定性優(yōu)先”決策先把業(yè)務(wù)跑起來把精力放在功能迭代上。1.3 客戶環(huán)境倒逼PostgreSQL 的入場這次的私有化部署客戶點(diǎn)名要求 PostgreSQL原因也很簡單他們內(nèi)部所有業(yè)務(wù)系統(tǒng)都在 PG 上已有的監(jiān)控平臺(tái)、備份腳本、權(quán)限體系都是圍繞 PG 做的不愿為新系統(tǒng)再引入一套 MySQL 運(yùn)維鏈路。這其實(shí)是獨(dú)立開發(fā)者做 to B 業(yè)務(wù)時(shí)經(jīng)常遇到的場景——技術(shù)選型不完全由你決定客戶現(xiàn)有的基礎(chǔ)設(shè)施就是約束條件。我沒有選擇直接遷移而是定了“雙數(shù)據(jù)庫兼容”的改造方向。理由是我現(xiàn)有的存量客戶還在 MySQL 上不可能逼他們切換新客戶要 PG就同時(shí)支持兩邊。這意味著代碼層面要抽象出數(shù)據(jù)庫無關(guān)的訪問方式SQL 層要做好方言隔離測試矩陣要從單庫變成雙庫。代價(jià)不小但收益也很實(shí)在后續(xù)再接任何客戶數(shù)據(jù)庫這一環(huán)就不會(huì)再成為商務(wù)談判的障礙。1.4 雙庫兼容的改造策略與成本評估改造前我先做了一個(gè)粗略的成本評估。我的系統(tǒng)是 Java Spring Boot MyBatis 技術(shù)棧MyBatis 本身不限制數(shù)據(jù)庫方言復(fù)雜的 SQL 寫在 XML 里因此適配思路分為兩層第一層是基礎(chǔ)設(shè)施適配包括多數(shù)據(jù)源配置、驅(qū)動(dòng)切換、連接參數(shù)調(diào)整第二層是 SQL 方言適配涉及所有 XML 里的 SQL 語句逐條審計(jì)。這里要給獨(dú)立開發(fā)者一個(gè)非常直白的建議如果你正在做類似的雙庫兼容改造在項(xiàng)目早期就定好一條鐵律——所有新寫的 SQL 必須預(yù)先考慮雙庫兼容性不要等代碼寫完了再來一句一句改。另外能交給 ORM 框架處理的就不要手寫 SQL比如簡單的 CRUD 完全可以交給 MyBatis-Plus 或 Spring Data JPA 的自動(dòng)方言適配去處理復(fù)雜報(bào)表和特殊查詢才需要手寫方言。我這次改造里大概有 70% 的 SQL 通過框架自動(dòng)適配掉了剩下 30% 的高風(fēng)險(xiǎn) SQL 全部重寫一遍。這個(gè)比例供你參考。2. PostgreSQL 與 MySQL 的核心差異動(dòng)手前的必修課2.1 連接層與驅(qū)動(dòng)JDBC URL、SSL 與連接池兩個(gè)庫在 Java 生態(tài)的連接方式非常接近但細(xì)節(jié)差異很磨人。MySQL 的驅(qū)動(dòng)是com.mysql.cj.jdbc.DriverJDBC URL 長這樣jdbc:mysql://localhost:3306/kf_system?useUnicodetruecharacterEncodingutf8useSSLfalseserverTimezoneAsia/ShanghaiallowPublicKeyRetrievaltruePostgreSQL 的驅(qū)動(dòng)是org.postgresql.DriverJDBC URL 則是jdbc:postgresql://localhost:5432/kf_system?sslmodedisable最容易踩坑的是 SSL 配置。MySQL 8.0 默認(rèn)認(rèn)證插件是caching_sha2_password如果不加allowPublicKeyRetrievaltrue某些 JDBC 版本在非 SSL 連接下會(huì)報(bào)錯(cuò)而useSSLfalse和 PG 的sslmodedisable雖然看起來都是“關(guān)閉 SSL”但語義完全不同。MySQL 的 useSSL 主要決定客戶端是否使用 SSL 加密傳輸PG 的 sslmode 則是一個(gè)多級策略disable表示完全不加密prefer表示優(yōu)先加密但允許降級require表示強(qiáng)制加密但不驗(yàn)證證書verify-ca和verify-full則要求校驗(yàn)證書。生產(chǎn)環(huán)境我建議 MySQL 側(cè)開啟 SSL 并配置證書PG 側(cè)至少用require以上級別單純圖省事全關(guān)掉在公網(wǎng)環(huán)境是有風(fēng)險(xiǎn)的。連接池我用的是 HikariCP大部分參數(shù)兩個(gè)庫可以共用但有兩個(gè)地方需要注意。一是 PG 連接的maxLifetime建議設(shè)置得略短一些官方說明是 PostgreSQL 的連接空閑超時(shí)后會(huì)被服務(wù)端回收如果客戶端連接池的 maxLifetime 太長可能出現(xiàn)連接已被服務(wù)端關(guān)閉但客戶端仍在使用的情況我這邊 PG 數(shù)據(jù)源設(shè)置的maxLifetime是 150000ms15分鐘MySQL 則用默認(rèn)的 1800000ms30分鐘。二是 validation query兩個(gè)庫都支持SELECT 1或SELECT 1;PG 驅(qū)動(dòng)其實(shí)自帶連接校驗(yàn)機(jī)制配置了也可以不配置也沒問題。2.2 數(shù)據(jù)類型自增主鍵、布爾、JSON 和時(shí)間這一塊是改造中改動(dòng)量最大的部分兩種數(shù)據(jù)庫的數(shù)據(jù)類型映射差異直接決定了 DDL 怎么寫。先說自增主鍵。MySQL 是AUTO_INCREMENT建表時(shí)直接寫在字段定義里然后可以用LAST_INSERT_ID()拿到剛插入的 ID。PostgreSQL 有兩種做法傳統(tǒng)的是SERIAL/BIGSERIAL偽類型它底層會(huì)創(chuàng)建一個(gè)序列sequence新一點(diǎn)的是標(biāo)準(zhǔn) SQL 的GENERATED ALWAYS AS IDENTITY本質(zhì)上也是序列但更符合規(guī)范。兩個(gè)庫對應(yīng)用層來說最大的區(qū)別在于MySQL 插入一條記錄后需要在同一條連接上執(zhí)行LAST_INSERT_ID()獲取 ID而 PG 可以在INSERT語句后面直接跟RETURNING id把主鍵帶出來更加干凈。布爾類型也是典型差異。MySQL 沒有原生的布爾類型習(xí)慣上用TINYINT(1)存 0 和 1PostgreSQL 提供真正的BOOLEAN類型接受true、false、1、0等輸入。這個(gè)差異會(huì)影響查詢參數(shù)的寫法比如 MyBatis 里傳一個(gè) Boolean 類型的參數(shù)MySQL 分支需要做一下類型轉(zhuǎn)換否則有些驅(qū)動(dòng)會(huì)把 true 變成字符串 true 導(dǎo)致 SQL 報(bào)錯(cuò)。JSON 列是兩個(gè)數(shù)據(jù)庫差異最大、也最容易引發(fā)問題的地方。MySQL 8.0 的 JSON 類型是二進(jìn)制存儲(chǔ)查詢用JSON_EXTRACT(json_col, $.key)或json_col-$.key提取字段去掉引號(hào)再用JSON_UNQUOTE()包裹。PostgreSQL 則區(qū)分json和jsonb兩種類型其中jsonb 是二進(jìn)制格式推薦使用提取字段用json_col-key語法。兩個(gè)庫的 JSON 操作符長得完全不一樣后續(xù)我會(huì)講到具體改寫方案。時(shí)間類型的選型更值得重視。MySQL 常用的DATETIME不帶時(shí)區(qū)信息TIMESTAMP帶時(shí)區(qū)但范圍有限且受會(huì)話時(shí)區(qū)影響。PostgreSQL 里TIMESTAMP不帶時(shí)區(qū)和TIMESTAMPTZ帶時(shí)區(qū)語義區(qū)分非常嚴(yán)格。我的建議是客服系統(tǒng)所有時(shí)間字段統(tǒng)一存TIMESTAMPTZ/TIMESTAMP并在 JDBC URL 上明確指定時(shí)區(qū)應(yīng)用層讀寫都按 UTC 處理展示時(shí)再轉(zhuǎn)本地時(shí)區(qū)。這樣能省掉后面報(bào)表統(tǒng)計(jì)差 8 小時(shí)一類的無妄之災(zāi)。2.3 SQL 語法分頁、更新、UPSERT 與窗口函數(shù)如果只用最基礎(chǔ)的分頁查詢兩個(gè)數(shù)據(jù)庫的體驗(yàn)幾乎一樣LIMIT ? OFFSET ?兩個(gè)庫都支持?？又饕卦诩?xì)節(jié)里。舉一個(gè)最常見的例子MySQL 存在LIMIT 20, 10這種“偏移量在前、行數(shù)在后”的舊寫法PostgreSQL 只認(rèn)LIMIT 10 OFFSET 20。如果團(tuán)隊(duì)里有人養(yǎng)成了 MySQL 的舊習(xí)慣適配時(shí)容易漏。另外兩個(gè)庫對NULL排序的默認(rèn)行為也不同PostgreSQL 升序默認(rèn)NULLS LASTMySQL 升序時(shí)NULL永遠(yuǎn)排在最前面。如果你的業(yè)務(wù)邏輯依賴排空值順序一定要顯式寫ORDER BY col ASC NULLS LAST或等價(jià)寫法PG 直接用NULLS LAST關(guān)鍵字MySQL 則需要ORDER BY ISNULL(col), col ASC這種技巧。更新關(guān)聯(lián)表是另一個(gè)高發(fā)差異區(qū)。MySQL 支持UPDATE ... JOIN語法直接把兩張表關(guān)聯(lián)后更新而 PostgreSQL 沒有這個(gè)語法要用UPDATE ... FROM子句實(shí)現(xiàn)相同效果。這個(gè)差異在客服系統(tǒng)里特別常見典型場景是“把 VIP 訪客在排隊(duì)中的會(huì)話自動(dòng)分配給某個(gè)坐席”我下面會(huì)給出兩邊完整寫法。UPSERT 的差異也要留意。MySQL 用INSERT ... ON DUPLICATE KEY UPDATEPostgreSQL 用INSERT ... ON CONFLICT (id) DO UPDATE SET ...。看起來實(shí)現(xiàn)效果差不多但 MySQL 的DUPLICATE KEY觸發(fā)條件是所有唯一索引沖突都算PG 的ON CONFLICT必須明確指定沖突的列或約束名。窗口函數(shù)方面MySQL 8.0 和 PostgreSQL 都支持ROW_NUMBER()、RANK()這些標(biāo)準(zhǔn)函數(shù)語法幾乎一樣。但如果你還維護(hù)著 MySQL 5.7 的存量客戶那窗口函數(shù)就用不了只能改用變量寫法或子查詢關(guān)聯(lián)復(fù)雜度直接上一個(gè)臺(tái)階。這算是我這次改造里最深刻的一條認(rèn)知雙庫兼容的難度很多時(shí)候取決于你最低要兼容的 MySQL 版本。2.4 事務(wù)、MVCC 與鎖并發(fā)模型完全不同MySQL InnoDB 和 PostgreSQL 都基于 MVCC 實(shí)現(xiàn)多版本并發(fā)控制但內(nèi)部機(jī)制差異非常大。MySQL 的 MVCC 建立在 undo log 上舊版本數(shù)據(jù)存在回滾段里新版本寫在原數(shù)據(jù)頁上所以更新操作時(shí)頁上只有一份數(shù)據(jù)加上回滾段里的舊版本。PostgreSQL 則是每個(gè)元組會(huì)保存多版本更新時(shí)產(chǎn)生一個(gè)新版本元組舊版本元組留在頁面里等待 VACUUM 清理。這個(gè)機(jī)制差異直接帶來一個(gè)影響PostgreSQL 在頻繁更新下會(huì)產(chǎn)生表膨脹bloat需要 autovacuum 持續(xù)工作MySQL InnoDB 沒有這個(gè)概念undo log 會(huì)被自動(dòng)回收。客服系統(tǒng)的會(huì)話表恰恰是更新頻繁的代表每次坐席接入、結(jié)束會(huì)話都會(huì)觸發(fā) UPDATE因此 PG 側(cè)的表膨脹維護(hù)是我上線后重點(diǎn)關(guān)注的問題后面第 5 章會(huì)詳細(xì)說。隔離級別上MySQL 的默認(rèn)級別是REPEATABLE READPostgreSQL 默認(rèn)是READ COMMITTED。在客服系統(tǒng)中如果需要在同一個(gè)事務(wù)里多次查詢某個(gè)統(tǒng)計(jì)數(shù)字并期望結(jié)果一致PG 默認(rèn)級別下第二次查詢可能看到新提交的數(shù)據(jù)需要把事務(wù)級別手動(dòng)調(diào)成REPEATABLE READ或SERIALIZABLE。PG 的 SERIALIZABLE 實(shí)現(xiàn)是 SSI可串行化快照隔離沖突檢測能力很強(qiáng)適合對一致性要求較高的結(jié)算類場景。鎖行為差異也很明顯。MySQL InnoDB 在 REPEATABLE READ 下會(huì)使用間隙鎖gap lock防止幻讀高并發(fā)插入時(shí)鎖沖突概率比 PG 高容易出現(xiàn)死鎖。PG 的常規(guī)行鎖不會(huì)阻塞讀取寫不阻塞讀是其核心賣點(diǎn)之一。另外一個(gè)冷門但好用的特性是 PG 提供pg_advisory_lock咨詢鎖適合實(shí)現(xiàn)“同一訪客只能被一個(gè)坐席接入”這類分布式互斥需求比 MySQL 的GET_LOCK()更靈活。2.5 索引與擴(kuò)展能力從 B-Tree 到 GIN 和 BRIN兩個(gè)數(shù)據(jù)庫默認(rèn)索引都是 B-Tree基礎(chǔ)查詢場景差距不大。差距體現(xiàn)在高級索引類型上。MySQL 8.0 的索引體系相對集中B-Tree、空間索引、FULLTEXT全文索引。PostgreSQL 則是“瑞士軍刀”提供 GIN適合 JSONB、全文檢索、BRIN適合超大表按物理順序掃描、表達(dá)式索引直接對函數(shù)結(jié)果建索引、部分索引只索引滿足條件的行等一堆能力。表達(dá)式索引和部分索引在實(shí)際業(yè)務(wù)中特別有用。比如訪客表里存了用戶昵稱如果要按昵稱忽略大小寫搜索MySQL 只能先把昵稱轉(zhuǎn)成小寫存一列再建索引或者創(chuàng)建生成列PostgreSQL 則可以CREATE INDEX idx_visitor_name_lower ON visitor (LOWER(nickname))查詢時(shí)寫WHERE LOWER(nickname) ?就能命中索引。部分索引則可以只索引當(dāng)前在線的會(huì)話減少索引體積。還有一點(diǎn)值得注意PostgreSQL 的 BRIN 索引非常適合消息表這種數(shù)據(jù)按時(shí)間順序插入、查詢通常限定時(shí)間范圍的場景。BRIN 索引體積只有 B-Tree 的幾十分之一在超大表上能顯著減少存儲(chǔ)開銷但它的掃描性能取決于數(shù)據(jù)的物理順序和相關(guān)性。如果消息表經(jīng)常刪除舊數(shù)據(jù)導(dǎo)致物理順序混亂BRIN 的效果會(huì)打折扣需要配合定期CLUSTER維護(hù)。3. 客服系統(tǒng) PostgreSQL 適配實(shí)操從連接池到 SQL 改寫3.1 多數(shù)據(jù)源配置與驅(qū)動(dòng)整合改造的第一步是把應(yīng)用改成多數(shù)據(jù)源結(jié)構(gòu)開發(fā)環(huán)境同時(shí)連接 MySQL 和 PostgreSQL方便隨時(shí)切換驗(yàn)證。我用的是 Spring Boot 的DataSourceBuilder動(dòng)態(tài)創(chuàng)建兩個(gè)數(shù)據(jù)源然后在一個(gè)通用查詢方法里根據(jù)一個(gè)dbType枚舉路由到不同連接。這里不展開 Spring 多數(shù)據(jù)源的完整實(shí)現(xiàn)只給一個(gè)最小配置示例spring: datasource: mysql: jdbc-url: jdbc:mysql://localhost:3306/kf_system?useUnicodetruecharacterEncodingutf8useSSLfalseserverTimezoneAsia/ShanghaiallowPublicKeyRetrievaltrue driver-class-name: com.mysql.cj.jdbc.Driver username: kf_user password: xxxxxx postgresql: jdbc-url: jdbc:postgresql://localhost:5432/kf_system?sslmodedisable driver-class-name: org.postgresql.Driver username: kf_user password: xxxxxx關(guān)鍵點(diǎn)在于兩個(gè)數(shù)據(jù)源對應(yīng)的實(shí)體 Bean 必須設(shè)置Primary標(biāo)記否則 Spring 在自動(dòng)注入時(shí)會(huì)因?yàn)榇嬖诙鄠€(gè) DataSource Bean 而報(bào)錯(cuò)。另外連接池參數(shù)別只配一份兩種數(shù)據(jù)庫的推薦值是有差異的簡單復(fù)制配置容易埋坑。3.2 核心表結(jié)構(gòu)改造DDL 對比直接看我改造后的兩張核心表 DDL 對比。MySQL 版本CREATE TABLE conversation ( id BIGINT NOT NULL AUTO_INCREMENT COMMENT 會(huì)話ID, tenant_id BIGINT NOT NULL DEFAULT 0, visitor_id BIGINT NOT NULL, agent_id BIGINT DEFAULT NULL, status TINYINT NOT NULL DEFAULT 0, channel VARCHAR(20) NOT NULL DEFAULT web, ext JSON DEFAULT NULL, created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP, updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, PRIMARY KEY (id), KEY idx_tenant_status (tenant_id, status), KEY idx_visitor (visitor_id) ) ENGINEInnoDB DEFAULT CHARSETutf8mb4; CREATE TABLE message ( id BIGINT NOT NULL AUTO_INCREMENT, conversation_id BIGINT NOT NULL, sender_type TINYINT NOT NULL, content TEXT, created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP, PRIMARY KEY (id), KEY idx_conversation (conversation_id, created_at) ) ENGINEInnoDB DEFAULT CHARSETutf8mb4;PostgreSQL 版本CREATE TABLE conversation ( id BIGSERIAL PRIMARY KEY, tenant_id BIGINT NOT NULL DEFAULT 0, visitor_id BIGINT NOT NULL, agent_id BIGINT, status SMALLINT NOT NULL DEFAULT 0, channel VARCHAR(20) NOT NULL DEFAULT web, ext JSONB DEFAULT NULL, created_at TIMESTAMPTZ NOT NULL DEFAULT now(), updated_at TIMESTAMPTZ NOT NULL DEFAULT now() ); CREATE INDEX idx_tenant_status ON conversation (tenant_id, status); CREATE INDEX idx_visitor ON conversation (visitor_id); CREATE TABLE message ( id BIGSERIAL PRIMARY KEY, conversation_id BIGINT NOT NULL, sender_type SMALLINT NOT NULL, content TEXT, created_at TIMESTAMPTZ NOT NULL DEFAULT now() ); CREATE INDEX idx_conversation ON message (conversation_id, created_at);這份對比能看出一堆有意思的差異。MySQL 建索引是寫在建表語句內(nèi)部的PG 則通常分開寫CREATE INDEX語義上沒有本質(zhì)區(qū)別但 MySQL 的索引名是表級命名空間PG 的索引名是模式級命名空間也就是說 PG 同一模式下所有索引名必須全局唯一。這個(gè)問題在小項(xiàng)目里不容易暴露一旦表多了索引名沖突會(huì)讓你抓狂。ON UPDATE CURRENT_TIMESTAMP是 MySQL 的一個(gè)語法糖PG 原生不支持需要寫觸發(fā)器或者完全在應(yīng)用層賦值。我的處理方式是統(tǒng)一在應(yīng)用層每次更新時(shí)顯式設(shè)置updated_at now()徹底拋棄數(shù)據(jù)庫自動(dòng)更新反而讓行為更可控。自增主鍵BIGSERIAL在 PG 里只是方便真正的長期推薦是GENERATED ALWAYS AS IDENTITY因?yàn)?SERIAL 和普通 sequence 綁定后續(xù)做表結(jié)構(gòu)遷移、序列重置時(shí)不如 IDENTITY 順手。不過這次為了和存量腳本保持風(fēng)格統(tǒng)一我用的還是BIGSERIAL。3.3 關(guān)鍵業(yè)務(wù) SQL 的方言適配下面挑幾個(gè)客服系統(tǒng)里最高頻的 SQL給出兩個(gè)數(shù)據(jù)庫的具體寫法。第一個(gè)是“把 VIP 訪客的排隊(duì)會(huì)話自動(dòng)分配給某個(gè)坐席”的關(guān)聯(lián)更新。MySQLUPDATE conversation c JOIN visitor v ON v.id c.visitor_id SET c.agent_id 1001, c.status 1, c.updated_at NOW() WHERE v.vip_level 1 AND c.status 0;PostgreSQLUPDATE conversation c SET agent_id 1001, status 1, updated_at now() FROM visitor v WHERE v.id c.visitor_id AND v.vip_level 1 AND c.status 0;兩個(gè)寫法的語義類似但 PG 的FROM子句在復(fù)雜場景下功能更強(qiáng)可以在FROM里放子查詢、JOIN 多個(gè)表自由度更高。第二個(gè)是“每個(gè)會(huì)話取最新一條消息”的分組排序需求。MySQL 8.0 和 PG 都可以用窗口函數(shù)SELECT * FROM ( SELECT m.*, ROW_NUMBER() OVER (PARTITION BY conversation_id ORDER BY created_at DESC) AS rn FROM message m ) t WHERE rn 1;這個(gè) SQL 在 MySQL 8.0 和 PG 完全通用但如果你的最小 MySQL 版本是 5.7就不得不換成變量寫法或者GROUP BY GROUP_CONCAT這類土辦法而且行為還未必一致。所以我在項(xiàng)目里強(qiáng)制要求如果某個(gè) SQL 必須依賴 MySQL 8.0 才能寫簡潔版那就在代碼里按版本分支處理不要讓老版本 MySQL 硬扛新語法。第三個(gè)是所謂 UPSERT用于“坐席心跳狀態(tài)更新”。MySQLINSERT INTO agent_status (agent_id, status, updated_at) VALUES (1001, 1, NOW()) ON DUPLICATE KEY UPDATE status VALUES(status), updated_at NOW();MySQL 8.0.20 以后VALUES()函數(shù)已經(jīng)被標(biāo)記為過時(shí)官方建議改成行別名語法所以我實(shí)際生產(chǎn)里用的是新寫法INSERT INTO agent_status (agent_id, status, updated_at) VALUES (1001, 1, NOW()) AS new ON DUPLICATE KEY UPDATE status new.status, updated_at new.updated_at;PostgreSQL 對應(yīng)的寫法是INSERT INTO agent_status (agent_id, status, updated_at) VALUES (1001, 1, now()) ON CONFLICT (agent_id) DO UPDATE SET status EXCLUDED.status, updated_at EXCLUDED.updated_at;注意 PG 的ON CONFLICT后面必須指定唯一的沖突列或約束否則語法報(bào)錯(cuò)這一點(diǎn)比 MySQL 嚴(yán)格得多。3.4 全文檢索讓聊天記錄可以被搜索客服業(yè)務(wù)里幾乎必有“按關(guān)鍵詞搜索聊天記錄”的功能。這個(gè)需求在兩個(gè)數(shù)據(jù)庫上實(shí)現(xiàn)路徑差異巨大。MySQL 的 FULLTEXT 索引用法比較簡單中文場景建議使用ngram解析器ALTER TABLE message ADD FULLTEXT INDEX ft_content (content) WITH PARSER ngram; SELECT * FROM message WHERE MATCH(content) AGAINST(退款 IN BOOLEAN MODE) AND conversation_id 123;MySQL 的 ngram 分詞器內(nèi)置了對中文的分詞支持雖然粒度比較粗但勝在開箱即用對絕大多數(shù)客服搜索場景足夠。PostgreSQL 的全文檢索體系更強(qiáng)大也更復(fù)雜。核心概念是tsvector文檔向量和tsquery查詢向量用操作符做匹配。但PG 默認(rèn)沒有內(nèi)置中文分詞器這是最大的坑。如果你直接用默認(rèn)配置中文內(nèi)容會(huì)被當(dāng)作連續(xù)的一整段處理to_tsquery(退款)匹配不出任何結(jié)果。常用的解決方案有兩個(gè)一是安裝zhparser或pg_jieba擴(kuò)展做中文分詞二是不夠裝擴(kuò)展的時(shí)候用pg_trgm模塊配合 LIKE 查詢實(shí)現(xiàn)近似效果CREATE EXTENSION IF NOT EXISTS pg_trgm; CREATE INDEX idx_message_trgm ON message USING GIN (content gin_trgm_ops); SELECT * FROM message WHERE content LIKE %退款% AND conversation_id 123;pg_trgm對中文的處理是按每連續(xù)三個(gè)字符切分 trigram雖然語義理解不如真正的分詞器但做模糊搜索和關(guān)鍵詞匹配效果已經(jīng)很能打。我的線上方案是能裝zhparser的客戶環(huán)境用全文檢索不能裝擴(kuò)展的降級用pg_trgm兩邊給用戶的搜索體驗(yàn)差異不大。這也算是我這次改造中比較深刻的體會(huì)在線客服系統(tǒng)的全文搜索MySQL 開箱即用PG 要額外付出分詞器選型和安裝的成本。如果你們的客戶環(huán)境卡得比較死不允許裝擴(kuò)展那 PG 側(cè)的中文搜索體驗(yàn)會(huì)明顯弱于 MySQL。3.5 存量數(shù)據(jù)遷移與校驗(yàn)因?yàn)椴皇钦w遷移而是新環(huán)境直連 PG我這邊沒有做全量歷史數(shù)據(jù)搬移只需要把存量客戶的 MySQL 數(shù)據(jù)導(dǎo)出備份再在 PG 上從零初始化。但如果你要把一套已經(jīng)跑了好幾年、積累了大量歷史消息的系統(tǒng)從 MySQL 遷到 PostgreSQL推薦直接用pgloader這個(gè)工具。pgloader 一條命令就能把表結(jié)構(gòu)和數(shù)據(jù)搬過去它內(nèi)置類型映射規(guī)則會(huì)把 MySQL 的AUTO_INCREMENT轉(zhuǎn)成 PG 的BIGSERIALTINYINT轉(zhuǎn)成SMALLINTDATETIME轉(zhuǎn)成TIMESTAMP還能自動(dòng)創(chuàng)建序列。基本用法pgloader mysql://kf_user:passlocalhost/kf_system postgresql://kf_user:passlocalhost/kf_system遷移后有一個(gè)必須做的手動(dòng)步驟因?yàn)锽IGSERIAL的序列不會(huì)跟著顯式 ID 插入自動(dòng)更新如果不修復(fù)接下來新插入的記錄可能直接主鍵沖突。手動(dòng)把序列跳到當(dāng)前最大值即可SELECT setval(conversation_id_seq, (SELECT max(id) FROM conversation));遷移后的校驗(yàn)建議分三層做先比對表數(shù)量和行數(shù)是否一致再對每張表做關(guān)鍵維度聚合比對比如 count、max 時(shí)間、sum 某數(shù)值列最后隨機(jī)抽幾十條業(yè)務(wù)記錄逐一對比字段值。只比對行數(shù)是最容易通過的字段類型的隱式轉(zhuǎn)換造成的精度差異往往藏在明細(xì)數(shù)據(jù)里。4. 客服場景下的實(shí)測對比性能、運(yùn)維與選型4.1 讀寫壓測消息寫入與會(huì)話查詢改造完成后我在同一臺(tái) 8 核 16G 的測試機(jī)上分別裝了 MySQL 8.0.36 和 PostgreSQL 16.2用同樣一套客服系統(tǒng)的讀寫腳本做壓力測試。壓測模型是這樣的模擬 200 個(gè)坐席在線1000 個(gè)訪客持續(xù)發(fā)消息每秒并發(fā)寫入約 500 條消息同時(shí)每 5 秒執(zhí)行一次“取每個(gè)會(huì)話最新消息”的列表查詢和“按訪客昵稱模糊搜索會(huì)話”的查詢。先說結(jié)論在 500 TPS 的寫入壓力下兩個(gè)數(shù)據(jù)庫的消息插入響應(yīng)時(shí)間幾乎沒有明顯差距都在個(gè)位數(shù)毫秒級別。這說明對于客服系統(tǒng)這個(gè)量級瓶頸根本不在數(shù)據(jù)庫引擎本身而在應(yīng)用層的連接管理、磁盤 IO 和網(wǎng)絡(luò)開銷。真正拉開差距的是兩類查詢一類是大范圍的聚合統(tǒng)計(jì)比如按小時(shí)統(tǒng)計(jì) 30 天內(nèi)的消息量PG 的優(yōu)化器在一些復(fù)雜 JOIN 場景下估算更準(zhǔn)執(zhí)行計(jì)劃更穩(wěn)定另一類是 JSON 字段的過濾查詢PG 的jsonb配合 GIN 索引比 MySQL 的 JSON 類型更順手索引命中率更高。但 MySQL 也不是全面落敗。在純并發(fā)插入混合少量更新的場景下MySQL InnoDB 的聚簇索引結(jié)構(gòu)讓主鍵范圍掃描非常高效消息表按時(shí)間范圍拉取歷史記錄的查詢MySQL 的響應(yīng)速度甚至略快于 PG。這種差異和存儲(chǔ)結(jié)構(gòu)強(qiáng)相關(guān)InnoDB 是聚簇索引數(shù)據(jù)按主鍵物理存儲(chǔ)主鍵連續(xù)插入時(shí)順序 IO 效率高PG 的 heap 表結(jié)構(gòu)下數(shù)據(jù)按插入順序堆存索引掃描后需要回表隨機(jī) IO 占比更高。4.2 運(yùn)維機(jī)制MVCC 清理、備份與監(jiān)控運(yùn)維層面的差異獨(dú)立開發(fā)者感受最明顯。MySQL 的 InnoDB 引擎把 MVCC 舊版本放在 undo log 里自動(dòng)管理用戶幾乎不需要干預(yù)purge線程會(huì)后臺(tái)清理。PostgreSQL 則不一樣每次 UPDATE 產(chǎn)生的新版本舊元組必須由VACUUM機(jī)制清理。雖然 PG 默認(rèn)開著 autovacuum但在消息表這種寫入量大、更新頻繁的表上如果 VACUUM 跟不上產(chǎn)生速度表膨脹會(huì)越來越嚴(yán)重查詢性能直線下滑。我上線 PG 后遇到過一個(gè)問題會(huì)話表的膨脹率在兩周內(nèi)從 1 倍漲到 3.5 倍統(tǒng)計(jì) SQL 從 80 毫秒退化到 600 多毫秒。原因是我的會(huì)話狀態(tài)更新非常頻繁一個(gè)會(huì)話生命周期里要 UPDATE 好幾次舊版本堆積而 autovacuum 的閾值觸發(fā)不夠積極。解決辦法是把這張表的 autovacuum 參數(shù)調(diào)得更激進(jìn)ALTER TABLE conversation SET (autovacuum_vacuum_scale_factor 0.05, autovacuum_vacuum_threshold 1000);同時(shí)定期手動(dòng)執(zhí)行VACUUM (ANALYZE, VERBOSE) conversation;備份方面MySQL 的主流方案是mysqldump邏輯備份加上 binlog 增量PostgreSQL 則常用pg_dump邏輯備份配合pg_basebackup物理備份和 WAL 歸檔。兩者能力對等但命令參數(shù)和恢復(fù)流程完全不同運(yùn)維腳本要分別維護(hù)。監(jiān)控方面MySQL 用SHOW ENGINE INNODB STATUS、performance_schemaPG 用pg_stat_activity、pg_stat_user_tables、pg_locks等系統(tǒng)視圖兩者監(jiān)控維度都很全但長期運(yùn)維你需要兩套監(jiān)控看板。還有 checkpoint 這個(gè)要點(diǎn)。PG 的 checkpoint 負(fù)責(zé)把 WAL 日志中已提交的事務(wù)刷到數(shù)據(jù)文件里checkpoint_timeout和max_wal_size的配置會(huì)影響崩潰恢復(fù)時(shí)間MySQL 的 redo log 是循環(huán)寫入自動(dòng)管理用戶基本不用管。對于獨(dú)立開發(fā)者來說這就是“省心”和“可控”之間的權(quán)衡。4.3 功能與生態(tài)對比速查表我把這次改造中實(shí)際對比過的維度整理成一張速查表方便你直接拿來參考。對比維度MySQL 8.0PostgreSQL 16默認(rèn)隔離級別REPEATABLE READREAD COMMITTED自增主鍵AUTO_INCREMENTSERIAL / IDENTITY SEQUENCE布爾類型TINYINT(1)BOOLEANJSON 類型JSON無 jsonb 概念json / jsonb中文全文檢索FULLTEXT ngram 開箱即用需裝 zhparser / pg_jieba或用 pg_trgm復(fù)雜 JOIN 優(yōu)化8.0 優(yōu)化器升級明顯基因算法 并行查詢更強(qiáng)UPDATE 關(guān)聯(lián)UPDATE ... JOINUPDATE ... FROMUPSERTON DUPLICATE KEY UPDATEON CONFLICT ... DO UPDATE分組取每組最新窗口函數(shù)或走變量窗口函數(shù)表達(dá)式/部分索引不直接支持原生支持表膨脹維護(hù)無undo 自動(dòng)回收需要 autovacuum / vacuum 手工干預(yù)備份工具mysqldump / binlogpg_dump / pg_basebackup / WAL 歸檔并發(fā)寫入鎖沖突RR 下間隙鎖較多行鎖不阻塞讀沖突更少適合場景通用 CRUD、中小型業(yè)務(wù)、團(tuán)隊(duì)熟悉 MySQL復(fù)雜查詢、JSON/全文檢索、強(qiáng)一致性要求4.4 到底該選哪一個(gè)經(jīng)過這次改造和實(shí)測我自己的判斷是不要帶任何品牌感情去選型邊界條件決定結(jié)果。如果你的在線客服系統(tǒng)是標(biāo)準(zhǔn) SaaS 形態(tài)自己控制運(yùn)行環(huán)境團(tuán)隊(duì)對 MySQL 熟悉業(yè)務(wù)查詢以 CRUD 為主報(bào)表復(fù)雜度有限那么 MySQL 8.0 會(huì)是非常省心的選擇。它開箱即用、運(yùn)維壓力小、中文全文檢索體驗(yàn)好而且云廠商生態(tài)最成熟。反過來如果客戶環(huán)境強(qiáng)制 PG或者你的業(yè)務(wù)對 JSON 數(shù)據(jù)建模、復(fù)雜聚合統(tǒng)計(jì)、地理空間查詢這類能力有強(qiáng)需求同時(shí)團(tuán)隊(duì)愿意投入精力學(xué)習(xí) PG 的 VACUUM、WAL、并發(fā)模型這些運(yùn)維知識(shí)那么 PostgreSQL 16 能給你更長期的功能成長空間。我個(gè)人的態(tài)度是“誰都能跑但你要知道它在為什么場景最優(yōu)”。雙庫兼容本身不復(fù)雜復(fù)雜的是把兩邊的運(yùn)維差異和 SQL 方言都測試到位。5. 排坑實(shí)錄那些文檔里查不到的問題5.1 大小寫敏感導(dǎo)致的“幽靈丟數(shù)據(jù)”上線第一周客戶反饋“訪客昵稱搜索經(jīng)常漏人”。我查了很久最后定位到是排序規(guī)則差異MySQL 的默認(rèn)排序規(guī)則utf8mb4 下通常是 utf8mb4_general_ci 或 utf8mb4_0900_ai_ci大小寫不敏感WHERE nickname ABc能匹配abcPostgreSQL 默認(rèn)的排序規(guī)則是大小寫敏感的ABc和abc是兩個(gè)完全不同的字符串。看起來是個(gè)小問題但會(huì)在搜索、去重、登錄校驗(yàn)等各種環(huán)節(jié)悄無聲息地出現(xiàn)。解決方案是在 PG 側(cè)安裝citext擴(kuò)展讓某個(gè)列在比較時(shí)忽略大小寫CREATE EXTENSION IF NOT EXISTS citext; ALTER TABLE visitor ALTER COLUMN nickname TYPE citext;也可以不加擴(kuò)展、保持原始列類型但在查詢條件里統(tǒng)一寫LOWER(nickname) LOWER(?)并配合第 2.5 節(jié)說的表達(dá)式索引。建議業(yè)務(wù)早期就定好統(tǒng)一規(guī)則不要等線上出了數(shù)據(jù)問題再回頭補(bǔ)。5.2 JSON 字段查詢語法與索引的坑MySQL 的JSON_EXTRACT(ext, $.city)和 PG 的ext-city語法不一致這是繞不開的。更隱蔽的是索引問題MySQL 雖然支持對 JSON 列建多值索引和生成列索引但操作符匹配路徑有限PG 則可以對jsonb直接建 GIN 索引然后使用操作符做包含判斷CREATE INDEX idx_visitor_ext ON visitor USING GIN (ext); SELECT * FROM visitor WHERE ext {city: 上海};這個(gè)查詢能走索引性能非常好。但注意 PG 里ext-city 上海這種寫法通常不會(huì)命中 GIN 索引要走 B-Tree 表達(dá)式索引或接受全表掃描。我一開始沒搞清楚這一點(diǎn)壓測時(shí)發(fā)現(xiàn)走了Seq Scan數(shù)據(jù)量一大就慢了。所以 JSON 查詢不要只看語法對不對要結(jié)合 EXPLAIN ANALYZE 確認(rèn)索引有沒有用上。5.3 時(shí)區(qū)與 TIMESTAMPTZ客服報(bào)表差八小時(shí)報(bào)表統(tǒng)計(jì)顯示“會(huì)話量少了一半”排查發(fā)現(xiàn)是時(shí)區(qū)問題。測試時(shí)直接往 PG 庫插數(shù)據(jù)應(yīng)用連接沒有指定時(shí)區(qū)數(shù)據(jù)庫now()存的是 UTC報(bào)表查詢用date_trunc(hour, created_at AT TIME ZONE Asia/Shanghai)才能得到北京時(shí)間的小時(shí)分組。而 MySQL 側(cè)因?yàn)閟erverTimezoneAsia/Shanghai配在 JDBC URL 里行為始終一致。這個(gè)問題的根源在于MySQL 的時(shí)間行為更依賴于連接參數(shù)PG 的時(shí)間行為更依賴于數(shù)據(jù)庫會(huì)話配置兩邊沒有一個(gè)統(tǒng)一的“標(biāo)準(zhǔn)答案”。我的處理是應(yīng)用層統(tǒng)一用Instant或帶時(shí)區(qū)的OffsetDateTime存時(shí)間數(shù)據(jù)庫層 PG 全用TIMESTAMPTZMySQL 全用DATETIME并確保 JDBC 時(shí)區(qū)和服務(wù)器時(shí)區(qū)一致報(bào)表查詢一律顯式轉(zhuǎn)換時(shí)區(qū)。5.4 SSL 連接報(bào)錯(cuò)與 MySQL socket 問題第 2.1 節(jié)已經(jīng)講過useSSLfalse和sslmodedisable的語義區(qū)別這里補(bǔ)充一個(gè)真實(shí)報(bào)錯(cuò)場景。有一天新同事的本地環(huán)境連 MySQL 一直報(bào)ERROR 2002 (HY000): Cant connect to local MySQL server through socket /tmp/mysql.sock排查發(fā)現(xiàn)是他本機(jī) MySQL 服務(wù)沒有啟動(dòng)而客戶端默認(rèn)走 Unix socket 而不是 TCP。解決辦法是確保 MySQL 服務(wù)啟動(dòng)或者連接時(shí)強(qiáng)制走 TCPmysql -h 127.0.0.1 -P 3306 -u kf_user -p。PG 側(cè)類似的坑是 JDBC 驅(qū)動(dòng)版本和服務(wù)器版本差異。PG 的 JDBC 驅(qū)動(dòng)對sslmodeprefer的處理在歷史上有些微妙變化如果服務(wù)器不支持 SSL 而驅(qū)動(dòng)又強(qiáng)行要 SSL會(huì)報(bào)一個(gè)看起來像“連接被重置”的錯(cuò)誤排查半天才發(fā)現(xiàn)是 SSL 握手失敗。我的建議是本地開發(fā)和內(nèi)網(wǎng)環(huán)境直接顯式sslmodedisable不要依賴默認(rèn)值。5.5 自增序列錯(cuò)亂與數(shù)據(jù)遷移這個(gè)坑在 3.5 節(jié)已經(jīng)提過數(shù)據(jù)導(dǎo)入后不重置序列新插入記錄直接主鍵沖突。這里補(bǔ)充一個(gè)更隱蔽的場景即使沒有做數(shù)據(jù)遷移只要有人在 PG 里手動(dòng)插入過顯式 ID 的行比如管理員手工補(bǔ)數(shù)據(jù)序列同樣不會(huì)自動(dòng)更新。和 MySQL 的AUTO_INCREMENT在插入大號(hào) ID 后會(huì)自動(dòng)修正的行為不同PG 的序列是“事不關(guān)己”的必須手動(dòng)同步。一個(gè)通用修復(fù)腳本SELECT setval( pg_get_serial_sequence(conversation, id), (SELECT max(id) FROM conversation) );建議在每次手工導(dǎo)入數(shù)據(jù)后都跑一遍并且把它寫進(jìn)部署手冊防止下次忘記。5.6 表膨脹、VACUUM 與性能劣化4.2 節(jié)講了膨脹率的問題這里給出一個(gè)量化判斷方法。檢查表的膨脹情況SELECT relname, n_live_tup, n_dead_tup, CASE WHEN n_live_tup 0 THEN round(n_dead_tup * 100.0 / n_live_tup, 1) END AS dead_pct FROM pg_stat_user_tables WHERE relname IN (conversation, message);當(dāng)dead_pct長期超過 20% 時(shí)就該重點(diǎn)處理了。除了調(diào)高這張表的 autovacuum 頻率還可以在業(yè)務(wù)低峰期執(zhí)行VACUUM FULL回收物理空間。注意VACUUM FULL會(huì)持有表級鎖如果在線客服系統(tǒng)是 7x24 小時(shí)運(yùn)行的要慎重安排窗口或者干脆不用它只做普通 VACUUM 讓 autovacuum 持續(xù)工作。還有一個(gè)容易忽略的點(diǎn)checkpoint_timeout和max_wal_size的配置直接影響 WAL 刷盤頻率和恢復(fù)時(shí)間如果 WAL 頻繁觸發(fā) checkpoint磁盤 IO 會(huì)被拖累表現(xiàn)為整體響應(yīng)變慢。我的測試環(huán)境里把max_wal_size調(diào)大到 2GB、checkpoint_timeout保持默認(rèn) 5 分鐘寫峰值下 IO 平穩(wěn)了很多。5.7 死鎖與鎖等待排查雙庫兼容后死鎖排查思路完全不同。MySQL 側(cè)排查死鎖SHOW ENGINE INNODB STATUS;重點(diǎn)看LATEST DETECTED DEADLOCK段落它會(huì)打印出兩個(gè)事務(wù)各自的 SQL 和鎖住的記錄。PG 側(cè)沒有直接打印死鎖詳情的命令需要結(jié)合系統(tǒng)視圖SELECT pid, state, wait_event_type, wait_event, query FROM pg_stat_activity WHERE wait_event_type Lock; SELECT * FROM pg_locks WHERE NOT granted;PG 的死鎖信息會(huì)寫到數(shù)據(jù)庫日志里log_lock_waits on參數(shù)開啟后可以在日志中看到鎖等待超過閾值的會(huì)話。建議從一開始就把log_lock_waits和deadlock_timeout配置到合理值比如deadlock_timeout 2s否則鎖問題排查全靠猜。5.8 問題速查表癥狀MySQL 排查方向PostgreSQL 排查方向連不上數(shù)據(jù)庫服務(wù)是否啟動(dòng)、socket 路徑、useSSL 配置服務(wù)是否啟動(dòng)、sslmode 配置、驅(qū)動(dòng)版本中文搜索查不到FULLTEXT 索引是否用 ngram是否安裝中文分詞擴(kuò)展 / pg_trgm 是否生效時(shí)間統(tǒng)計(jì)差幾小時(shí)JDBC serverTimezone 是否一致是否使用 TIMESTAMPTZ、查詢是否顯式轉(zhuǎn)換時(shí)區(qū)字符串匹配漏數(shù)據(jù)排序規(guī)則是否大小寫不敏感是否受默認(rèn)大小寫敏感影響需 citext插入主鍵沖突較少見AUTO_INCREMENT 自動(dòng)修正序列未 setval需要手動(dòng)修復(fù)表越來越大查詢變慢InnoDB 自動(dòng)管理關(guān)注慢查詢?nèi)罩緉_dead_tup 偏高檢查 autovacuum死鎖SHOW ENGINE INNODB STATUSpg_stat_activity pg_locks 數(shù)據(jù)庫日志關(guān)聯(lián)更新報(bào)錯(cuò)UPDATE ... JOIN 語法 OK需要改寫為 UPDATE ... FROM6. 改造完成后的幾點(diǎn)體會(huì)6.1 雙庫兼容的隱性成本遠(yuǎn)超預(yù)期這次改造最深的感受是讓一套系統(tǒng)同時(shí)跑兩個(gè)數(shù)據(jù)庫真正的成本不在寫代碼而在持續(xù)測試和運(yùn)維。SQL 改寫是有限的工作量改完就完了但每個(gè)版本迭代都要在兩個(gè)數(shù)據(jù)庫上回歸測試每個(gè)客戶環(huán)境可能需要兩套不同的備份、監(jiān)控和調(diào)優(yōu)方案這些才是長期成本。如果你只是為了“多支持一個(gè)數(shù)據(jù)庫”而支持沒有實(shí)際客戶需求在背后驅(qū)動(dòng)我不會(huì)建議你去做。另一個(gè)容易被低估的點(diǎn)是團(tuán)隊(duì)認(rèn)知成本。你的代碼里會(huì)到處出現(xiàn)dbType判斷同事每次寫一條新 SQL 都要想“這個(gè)語法兩個(gè)庫都支持嗎”這個(gè)思考負(fù)擔(dān)會(huì)持續(xù)消耗生產(chǎn)力。我的緩解辦法是寫了一份內(nèi)部 SQL 編寫規(guī)范明確列出哪些語法不允許直接使用比如 MySQL 的LIMIT offset, count、PG 的NULLS FIRST如果要對齊兩邊就得繞開哪些場景必須走方言分支。規(guī)范雖然不能消除全部成本但至少讓團(tuán)隊(duì)有據(jù)可依。6.2 給獨(dú)立開發(fā)者的一句話經(jīng)驗(yàn)如果你也是獨(dú)立開發(fā)、正在做在線客服類產(chǎn)品或任何數(shù)據(jù)密集型業(yè)務(wù)我最后想分享幾條實(shí)際經(jīng)驗(yàn)第一數(shù)據(jù)庫選型要跟著客戶和場景走不要有“個(gè)人偏好”這種情緒。MySQL 很親切PG 很強(qiáng)大但沒有一個(gè)數(shù)據(jù)庫能覆蓋所有場景能用基礎(chǔ)設(shè)施約束直接解決商務(wù)問題才是最重要的。第二如果你預(yù)感到未來可能會(huì)適配 PG那從第一天開始就盡量少寫方言特性。能用LIMIT ? OFFSET ?就用這個(gè)能用標(biāo)準(zhǔn) SQL 窗口函數(shù)就不要碰 MySQL 特有的GROUP_CONCAT替代方案。寫 SQL 時(shí)多問自己一句“這條語句換一個(gè)數(shù)據(jù)庫還成立嗎”后面返工最少。第三工具的差異值得擁抱。PG 的pg_stat_activity、pg_locks、EXPLAIN 的豐富程度確實(shí)比 MySQL 更細(xì)反過來 MySQL 的SHOW ENGINE INNODB STATUS在很多死鎖場景又比 PG 直觀。兩套工具都學(xué)會(huì)不虧。改造完成到現(xiàn)在已經(jīng)跑了一個(gè)多月線上 PG 和 MySQL 兩邊都穩(wěn)定運(yùn)行。最后再分享一個(gè)小技巧雙庫兼容的項(xiàng)目一定要在自動(dòng)化測試?yán)锿瑫r(shí)跑兩個(gè)數(shù)據(jù)源的用例。我最初只在 MySQL 數(shù)據(jù)源上跑單測結(jié)果上 PG 環(huán)境后一連暴露了好幾個(gè)只有 SQL 方言差異才會(huì)觸發(fā)的 bug。把兩個(gè)數(shù)據(jù)源納入 CI 之后這類問題基本絕跡了。數(shù)據(jù)庫沒有絕對的好壞能讓業(yè)務(wù)高效跑起來、團(tuán)隊(duì)維護(hù)得起、客戶接受得了那這個(gè)選擇就是對的。