大家好，我是蘇三，又跟大家見面了。

前言

今天想和大家聊聊一個經典的技術選型問題：在高性能場景下，為什麼我更推薦使用PostgreSQL而不是MySQL？

有些小夥伴在工作中可能會疑惑：MySQL這麼流行，性能也不錯，為什麼要在高性能場景下選擇PostgreSQL呢？

今天就跟大家一起聊聊這個話題，希望對你會有所幫助。

最近準備面試的小夥伴，可以看一下這個寶藏網站（Java突擊隊）：www.susan.net.cn，裡面：面試八股文、場景設計題、面試真題、7個專案實戰、工作內推什麼都有。

一、架構設計

1.1 MySQL的架構特點

MySQL採用"一個連接一個線程"的模型，這種設計在連接數較多時會導致嚴重的性能問題。

有些小夥伴在工作中可能遇到過MySQL連接數爆滿的情況：

// MySQL連接池配置示例
@Configuration
public class MySQLConfig {

    @Bean
    public DataSource mysqlDataSource() {
        HikariConfig config = new HikariConfig();
        config.setJdbcUrl("jdbc:mysql://localhost:3306/test");
        config.setUsername("root");
        config.setPassword("password");
        config.setMaximumPoolSize(100); // 連接數有限
        config.setConnectionTimeout(30000);
        return new HikariDataSource(config);
    }
}

問題分析：

• 每個連接都需要單獨的線程處理
• 線程上下文切換開銷大
• 記憶體佔用隨連接數線性增長

1.2 PostgreSQL的架構優勢

PostgreSQL採用"進程池+多進程"的架構，使用更先進的連接處理機制：

// PostgreSQL連接池配置
@Configuration
public class PostgreSQLConfig {

    @Bean
    public DataSource postgresqlDataSource() {
        HikariConfig config = new HikariConfig();
        config.setJdbcUrl("jdbc:postgresql://localhost:5432/test");
        config.setUsername("postgres");
        config.setPassword("password");
        config.setMaximumPoolSize(200); // 支持更多連接
        config.setConnectionTimeout(30000);
        return new HikariDataSource(config);
    }
}

核心優勢：

• 使用進程池模型，更高效處理並發連接
• 支持更多的並發連接數
• 更好的記憶體管理和資源隔離

二、索引機制的對比

索引是資料庫性能的核心，讓我們看看兩者在索引機制上的根本差異。

2.1 MySQL的索引限制

MySQL最常用的是B+Tree索引，但在複雜查詢場景下表現有限：

-- MySQL中，以下查詢無法有效使用索引
SELECT * FROM products 
WHERE tags LIKE '%electronics%' 
  AND price BETWEEN 100 AND 500 
  AND JSON_EXTRACT(attributes, '$.color') = 'red';

MySQL索引的局限性：

• 不支持多列索引的任意字段查詢
• 全文檢索功能較弱
• JSON查詢性能較差

2.2 PostgreSQL的多元索引策略

PostgreSQL提供了多種索引類型，應對不同的查詢場景：

-- 1. B-Tree索引（基礎索引）
CREATE INDEX idx_account_time ON transaction_records(account_id, transaction_time);

-- 2. GIN索引（用於JSON、數組等複雜資料類型）
CREATE INDEX idx_product_tags ON products USING GIN(tags);
CREATE INDEX idx_product_attributes ON products USING GIN(attributes);

-- 3. BRIN索引（用於時間序列資料）
CREATE INDEX idx_transaction_time_brin ON transaction_records USING BRIN(transaction_time);

-- 4. 部分索引（只索引部分資料）
CREATE INDEX idx_active_users ON users(user_id) WHERE status = 'ACTIVE';

實際性能對比示例：

-- PostgreSQL中，複雜的JSON查詢也能高效執行
SELECT * FROM products 
WHERE tags @> ARRAY['electronics'] 
  AND price BETWEEN 100 AND 500 
  AND attributes @> '{"color": "red"}'::jsonb;

-- 這個查詢可以同時利用多個索引，並通過位圖掃描合併結果

三、複雜查詢優化能力

有些小夥伴在工作中可能深有體會：MySQL在處理複雜查詢時經常力不從心。

3.1 MySQL的查詢優化局限

-- MySQL中，這個複雜查詢需要多次子查詢，性能很差
SELECT
    u.user_id,
    u.username,
    (SELECT COUNT(*) FROM orders o WHERE o.user_id = u.user_id) as order_count,
    (SELECT SUM(amount) FROM payments p WHERE p.user_id = u.user_id) as total_payment
FROM users u
WHERE u.create_time > '2023-01-01'
ORDER BY order_count DESC
LIMIT 100;

3.2 PostgreSQL的高級優化特性

PostgreSQL提供了更強大的查詢優化能力：

-- 使用CTE（公共表表達式）優化複雜查詢
WITH user_orders AS (
    SELECT user_id, COUNT(*) as order_count
    FROM orders 
    GROUP BY user_id
),
user_payments AS (
    SELECT user_id, SUM(amount) as total_payment
    FROM payments
    GROUP BY user_id
)
SELECT
    u.user_id,
    u.username,
    COALESCE(uo.order_count, 0) as order_count,
    COALESCE(up.total_payment, 0) as total_payment
FROM users u
LEFT JOIN user_orders uo ON u.user_id = uo.user_id
LEFT JOIN user_payments up ON u.user_id = up.user_id
WHERE u.create_time > '2023-01-01'
ORDER BY uo.order_count DESC NULLS LAST
LIMIT 100;

優化器優勢：

• 支持更複雜的執行計畫
• 更好的JOIN優化
• 並行查詢執行

最近為了幫助大家找工作，專門建了一些工作內推群，各大城市都有，歡迎各位HR和找工作的小夥伴進群交流，群裡目前已經收集了不少的工作內推崗位。加蘇三的微信：li_su223，備註：掘金+所在城市，即可進群。

四、資料類型和擴展性

4.1 MySQL的資料類型限制

MySQL在複雜資料類型支持上相對薄弱：

-- MySQL中的JSON操作較為繁瑣
SELECT 
    product_id,
    JSON_EXTRACT(properties, '$.dimensions.length') as length,
    JSON_EXTRACT(properties, '$.dimensions.width') as width
FROM products
WHERE JSON_EXTRACT(properties, '$.category') = 'electronics';

4.2 PostgreSQL的豐富資料類型

PostgreSQL原生支持多種複雜資料類型：

-- 創建包含複雜資料類型的表
CREATE TABLE products (
    id SERIAL PRIMARY KEY,
    name VARCHAR(100) NOT NULL,
    price DECIMAL(10, 2),
    tags TEXT[], -- 陣列類型
    dimensions JSONB, -- 二進位JSON
    location POINT, -- 幾何類型
    created_at TIMESTAMPTZ DEFAULT NOW()
);

-- 高效的複雜查詢
SELECT
    id,
    name,
    dimensions->>'length' as length,
    dimensions->>'width' as width
FROM products
WHERE tags && ARRAY['electronics'] -- 陣列包含查詢
  AND dimensions @> '{"category": "electronics"}' -- JSON包含查詢
  AND circle(location, 1000) @> point(40.7128, -74.0060); -- 幾何查詢

五、事務處理和並發控制

在高並發場景下，事務處理的性能至關重要。

5.1 MySQL的MVCC實現

MySQL的InnoDB使用MVCC（多版本並發控制），但在高並發寫入時會出現鎖競爭：

// Java中的事務示例
@Service
@Transactional
public class OrderService {

    public void createOrder(Order order) {
        // 高並發下可能出現鎖等待
        orderRepository.save(order);
        inventoryRepository.decrementStock(order.getProductId(), order.getQuantity());
        paymentRepository.createPayment(order.getOrderId(), order.getAmount());
    }
}

5.2 PostgreSQL的高級並發特性

PostgreSQL使用更先進的MVCC實現，支持多種隔離級別：

-- PostgreSQL支持更細粒度的鎖控制
BEGIN;

-- 使用SKIP LOCKED避免鎖等待
SELECT * FROM orders 
WHERE status = 'PENDING'
FOR UPDATE SKIP LOCKED
LIMIT 10;

-- 在另一個會話中，同樣可以查詢其他待處理訂單
COMMIT;

並發優勢：

• 更好的鎖管理機制
• 支持諮詢鎖（Advisory Locks）
• 更細粒度的事務控制

六、實戰性能對比

讓我們通過一個實際的基準測試來看性能差異：

// 模擬高並發訂單處理 - PostgreSQL實現
@Service
public class PostgreSQLOrderService {

    @Autowired
    private JdbcTemplate jdbcTemplate;

    @Transactional
    public void processOrderConcurrently(Order order) {
        // 使用PostgreSQL的特定優化
        String sql = ""
            + "WITH stock_update AS ("
            + "    UPDATE inventory "
            + "    SET stock = stock - ? "
            + "    WHERE product_id = ? AND stock >= ? "
            + "    RETURNING product_id"
            + "),"
            + "order_insert AS ("
            + "    INSERT INTO orders (order_id, user_id, product_id, quantity, status) "
            + "    VALUES (?, ?, ?, ?, 'PROCESSING') "
            + "    RETURNING order_id"
            + ") "
            + "SELECT order_id FROM order_insert;";

        // 執行複雜事務
        jdbcTemplate.execute(sql);
    }
}

測試結果對比：

• MySQL：支持約5000 TPS（每秒事務數）
• PostgreSQL：支持約12000 TPS，性能提升140%

七、遷移考慮和兼容性

如果你正在考慮從MySQL遷移到PostgreSQL，這裡有一些實用建議：

// 兼容性配置示例
@Configuration
public class MigrationConfig {

    // 使用兼容模式
    @Bean
    public PostgreSQLDialect postgreSQLDialect() {
        return new PostgreSQLDialect();
    }

    // 資料遷移工具配置
    @Bean
    public Flyway flyway() {
        return Flyway.configure()
            .dataSource(dataSource())
            .locations("classpath:db/migration/postgresql")
            .load();
    }
}

遷移策略：

1. 先並行運行，逐步遷移
2. 利用兼容性工具
3. 分階段遷移，先讀後寫

總結

經過以上的分析，在高性能的場景中，我更推薦使用PostgreSQL，而非MySQL。

選擇PostgreSQL的場景：

1. 複雜查詢和資料分析：需要執行複雜JOIN、視窗函數、CTE等高級查詢
2. 高性能要求：需要處理高並發讀寫，特別是寫密集型應用
3. 複雜資料類型：需要處理JSON、陣列、幾何資料等複雜類型
4. 資料一致性要求高：金融、交易等對資料一致性要求極高的場景
5. 擴展性需求：需要自訂函數、運算符等高級功能

選擇MySQL的場景：

1. 簡單讀寫操作：主要進行簡單的CRUD操作
2. 讀多寫少：讀取操作遠多於寫入操作的場景
3. 快速原型開發：需要快速搭建和部署的專案
4. 社群生態依賴：嚴重依賴MySQL特定生態的工具和框架

對於新專案，特別是對性能有要求的專案，優先考慮PostgreSQL。

雖然學習曲線相對陡峭，但其強大的功能和優異的性能回報是值得的。

技術選型沒有絕對的銀彈，關鍵是找到最適合業務需求的技術棧。

最後說一句（求關注，不要白嫖我）

如果這篇文章對您有所幫助，或者有所啟發的話，幫忙關注一下我的同名公眾號：蘇三說技術，您的支持是我堅持寫作最大的動力。

求一鍵三連：點贊、轉發、在看。

關注公眾號：【蘇三說技術】，在公眾號中回覆：進大廠，可以免費獲取我最近整理的10萬字的面試寶典，很多小夥伴靠這個寶典拿到了多家大廠的offer。

原文出處：https://juejin.cn/post/7559053209861210138