PostgreSQL 和 MySQL 适用性分析

概述

对于您的 IoT 软总线系统（大数据量、高频写入、复杂查询），PostgreSQL 和 MySQL 也是值得考虑的选择。本文档分析它们在您场景下的适用性。

PostgreSQL 分析

✅ 优点

强大的 SQL 支持
- 完整的 SQL 标准支持（比 MySQL 更接近标准）
- 支持复杂查询、窗口函数、CTE（公用表表达式）
- 支持 JSON/JSONB 类型，适合存储协议数据
时间序列数据支持
- TimescaleDB 扩展：专门优化时间序列数据
- 自动分区、压缩、连续聚合
- 非常适合 IoT 传感器数据场景
扩展性好
- 支持多种数据类型（JSONB、数组、范围类型等）
- 支持自定义函数和存储过程
- 支持多种索引类型（B-tree、Hash、GiST、GIN 等）
并发控制优秀
- MVCC（多版本并发控制）
- 读写不阻塞
- 适合多设备同时写入
数据完整性
- ACID 事务保证
- 外键约束、触发器
- 数据一致性保证

❌ 缺点

部署复杂度
- 需要单独的数据库服务器
- 需要配置、启动、维护服务
- 不适合嵌入式/单机应用
资源占用
- 内存占用较大（默认配置可能需要 1GB+）
- 不适合资源受限的设备
Qt 集成
- 需要安装 PostgreSQL 客户端库
- 需要配置连接字符串
- 网络延迟（如果远程连接）
写入性能
- 虽然性能优秀，但不如 RocksDB 的纯 KV 写入
- 需要事务提交，可能成为瓶颈
- 对于超高频率写入（>10万/秒），可能不如 RocksDB
单机场景
- 如果只是单机应用，使用 PostgreSQL 可能过度设计

MySQL 分析

✅ 优点

广泛使用
- 生态系统成熟，文档丰富
- 社区支持好
性能优化
- InnoDB 引擎提供良好的并发性能
- 支持多种存储引擎（MyISAM、InnoDB、Memory 等）
简单易用
- 配置相对简单
- 工具链成熟
Qt 集成
- Qt 原生支持 MySQL 驱动
- 连接配置简单

❌ 缺点

时间序列数据支持弱
- 没有类似 TimescaleDB 的扩展
- 时间序列查询性能不如 PostgreSQL + TimescaleDB
JSON 支持较弱
- MySQL 5.7+ 支持 JSON 类型，但功能不如 PostgreSQL JSONB
- 查询性能一般
部署复杂度
- 同样需要单独的数据库服务器
- 不适合嵌入式场景
写入性能
- 同样不如 RocksDB 的纯 KV 写入
- 事务开销较大
扩展性
- 不如 PostgreSQL 灵活
- 自定义函数和类型支持有限

方案对比

场景假设

指标	描述
数据量	TB 级别（持续采集）
写入频率	高频（每秒数千到数万条）
查询需求	复杂查询（多字段、时间范围、聚合）
部署方式	单机应用 or 分布式？
资源限制	内存、CPU 是否受限？

方案对比表

方案	写入性能	查询能力	部署复杂度	资源占用	适用场景
RocksDB	⭐⭐⭐⭐⭐	⭐⭐	⭐⭐⭐⭐⭐	⭐⭐⭐⭐	纯写入，简单查询
SQLite	⭐⭐⭐	⭐⭐⭐⭐	⭐⭐⭐⭐⭐	⭐⭐⭐⭐⭐	中小规模，单机应用
RocksDB + SQLite	⭐⭐⭐⭐⭐	⭐⭐⭐⭐	⭐⭐⭐⭐	⭐⭐⭐⭐	大数据量，单机应用 ⭐ 推荐
PostgreSQL	⭐⭐⭐⭐	⭐⭐⭐⭐⭐	⭐⭐⭐	⭐⭐⭐	分布式，需要强查询
PostgreSQL + TimescaleDB	⭐⭐⭐⭐	⭐⭐⭐⭐⭐	⭐⭐⭐	⭐⭐⭐	时间序列，分布式场景 ⭐ 推荐
MySQL	⭐⭐⭐⭐	⭐⭐⭐	⭐⭐⭐	⭐⭐⭐	传统应用，简单场景

详细对比：PostgreSQL vs MySQL vs 混合架构

1. 写入性能

PostgreSQL

-- 批量插入示例
INSERT INTO raw_data (device_id, data, timestamp, creator, data_level) 
VALUES 
  (1, 'data1', 1234567890, 'user1', 5),
  (2, 'data2', 1234567891, 'user2', 6);
-- 性能：约 5-10万行/秒（取决于配置）

MySQL

-- 批量插入示例（类似）
INSERT INTO raw_data (device_id, data, timestamp, creator, data_level) 
VALUES 
  (1, 'data1', 1234567890, 'user1', 5),
  (2, 'data2', 1234567891, 'user2', 6);
-- 性能：约 5-10万行/秒（取决于配置）

RocksDB + SQLite 混合架构

// 直接写入 RocksDB，无事务开销
m_rawDb->Put(key, value);  // 性能：10-50万行/秒
// SQLite 索引异步写入
storeRawDataIndex(...);    // 可批量写入，延迟低

结论：RocksDB 写入性能最优，PostgreSQL/MySQL 接近但需要事务开销。

2. 复杂查询能力

PostgreSQL（TimescaleDB）

-- 时间序列查询 + 聚合
SELECT 
  time_bucket('1 hour', timestamp) AS hour,
  device_id,
  COUNT(*) as count,
  AVG(data_level) as avg_level,
  creator
FROM raw_data
WHERE timestamp >= NOW() - INTERVAL '24 hours'
  AND creator = '张三'
  AND data_level >= 5
GROUP BY hour, device_id, creator
ORDER BY hour DESC;
-- 性能：优秀，TimescaleDB 自动优化

MySQL

-- 类似查询
SELECT 
  DATE_FORMAT(timestamp, '%Y-%m-%d %H:00:00') AS hour,
  device_id,
  COUNT(*) as count,
  AVG(data_level) as avg_level,
  creator
FROM raw_data
WHERE timestamp >= DATE_SUB(NOW(), INTERVAL 24 HOUR)
  AND creator = '张三'
  AND data_level >= 5
GROUP BY hour, device_id, creator
ORDER BY hour DESC;
-- 性能：一般，需要良好的索引设计

RocksDB + SQLite 混合架构

// SQLite 支持复杂 SQL
QString sql = R"(
    SELECT rocksdb_key FROM raw_data_index
    WHERE device_id = ? 
      AND create_time >= ?
      AND create_time <= ?
      AND creator = ?
      AND data_level >= ?
    ORDER BY create_time DESC
)";
// 然后从 RocksDB 读取实际数据
// 性能：良好，但需要两次查询

结论：PostgreSQL + TimescaleDB 查询能力最强，SQLite 次之，MySQL 一般。

3. 部署复杂度

PostgreSQL

1. 安装 PostgreSQL 服务器
   sudo apt-get install postgresql
   
2. 创建数据库和用户
   sudo -u postgres createdb soft_bus
   sudo -u postgres psql -c "CREATE USER bus_user WITH PASSWORD 'password';"
   
3. 安装 TimescaleDB 扩展
   sudo apt-get install timescaledb-postgresql-14
   sudo timescaledb-tune
   
4. Qt 代码中连接
   QSqlDatabase db = QSqlDatabase::addDatabase("QPSQL");
   db.setHostName("localhost");
   db.setDatabaseName("soft_bus");
   db.setUserName("bus_user");
   db.setPassword("password");

MySQL

1. 安装 MySQL 服务器
   sudo apt-get install mysql-server
   
2. 创建数据库和用户
   sudo mysql -e "CREATE DATABASE soft_bus;"
   sudo mysql -e "CREATE USER 'bus_user'@'localhost' IDENTIFIED BY 'password';"
   sudo mysql -e "GRANT ALL ON soft_bus.* TO 'bus_user'@'localhost';"
   
3. Qt 代码中连接
   QSqlDatabase db = QSqlDatabase::addDatabase("QMYSQL");
   db.setHostName("localhost");
   db.setDatabaseName("soft_bus");
   db.setUserName("bus_user");
   db.setPassword("password");

RocksDB + SQLite

1. RocksDB：已集成，无需额外安装
2. SQLite：Qt 自带，无需安装
3. 直接使用，零配置

结论：RocksDB + SQLite 部署最简单，PostgreSQL/MySQL 需要额外安装和配置。

4. 资源占用

方案	内存占用	CPU 占用	磁盘占用	网络
PostgreSQL	较高（默认 1GB+）	中等	正常	需要（如果远程）
MySQL	较高（默认 512MB+）	中等	正常	需要（如果远程）
RocksDB + SQLite	低（可配置）	低	正常	不需要

5. 数据迁移成本

从 RocksDB 迁移到 PostgreSQL

-- 需要编写迁移脚本，从 RocksDB 读取数据，写入 PostgreSQL
-- 可能耗时较长（TB级别数据）

从 RocksDB 迁移到 MySQL

-- 类似，需要迁移脚本

迁移到混合架构

// 可以渐进式迁移：
// 1. 先启用 SQLite 索引（不影响现有 RocksDB）
// 2. 新数据同时写入 RocksDB 和 SQLite
// 3. 逐步迁移查询逻辑到 SQLite
// 4. 历史数据可选择性迁移

结论：混合架构迁移成本最低，PostgreSQL/MySQL 需要全量迁移。

PostgreSQL + TimescaleDB 实现示例

1. 安装和初始化

# 安装 PostgreSQL
sudo apt-get install postgresql-14

# 安装 TimescaleDB
sudo apt-get install timescaledb-postgresql-14

# 配置
sudo timescaledb-tune

# 启用扩展
sudo -u postgres psql -d postgres -c "CREATE EXTENSION IF NOT EXISTS timescaledb;"

2. 创建表结构

-- 创建原始数据表
CREATE TABLE raw_data (
    id BIGSERIAL,
    device_id INTEGER NOT NULL,
    data BYTEA NOT NULL,
    timestamp TIMESTAMPTZ NOT NULL,
    creator TEXT,
    data_level INTEGER DEFAULT 0,
    protocol_type TEXT,
    metadata JSONB
);

-- 转换为时序表
SELECT create_hypertable('raw_data', 'timestamp');

-- 创建索引
CREATE INDEX idx_raw_data_device_time ON raw_data(device_id, timestamp DESC);
CREATE INDEX idx_raw_data_creator ON raw_data(creator);
CREATE INDEX idx_raw_data_level ON raw_data(data_level);
CREATE INDEX idx_raw_data_protocol ON raw_data(protocol_type);

-- 创建总线消息表
CREATE TABLE bus_messages (
    id BIGSERIAL,
    message_id TEXT UNIQUE NOT NULL,
    source TEXT NOT NULL,
    destination TEXT NOT NULL,
    payload JSONB NOT NULL,
    timestamp TIMESTAMPTZ NOT NULL,
    creator TEXT,
    data_level INTEGER DEFAULT 0,
    protocol_type TEXT,
    metadata JSONB
);

-- 转换为时序表
SELECT create_hypertable('bus_messages', 'timestamp');

-- 创建索引
CREATE INDEX idx_bus_msg_source_dest ON bus_messages(source, destination);
CREATE INDEX idx_bus_msg_time ON bus_messages(timestamp DESC);
CREATE INDEX idx_bus_msg_creator ON bus_messages(creator);
CREATE INDEX idx_bus_msg_level ON bus_messages(data_level);

3. Qt 代码集成

// soft_bus_core.h
#include <QSqlDatabase>
#include <QSqlQuery>

class SoftBusCore : public QObject {
    // ... 其他代码 ...
    
private:
    QSqlDatabase m_pgDb;  // PostgreSQL 连接
    bool m_pgDbInitialized;
    
    bool initPostgreSQL(const QString &connectionString);
    void storeRawDataToPG(int deviceId, const QByteArray &data,
                          const QString &creator, int dataLevel,
                          const QString &protocolType);
};

// soft_bus_core.cpp
bool SoftBusCore::initPostgreSQL(const QString &connectionString) {
    // 解析连接字符串：host=localhost;port=5432;dbname=soft_bus;user=bus_user;password=xxx
    m_pgDb = QSqlDatabase::addDatabase("QPSQL", "postgresql_connection");
    
    // 解析连接参数
    QStringList parts = connectionString.split(';');
    for (const QString &part : parts) {
        QStringList kv = part.split('=');
        if (kv.size() == 2) {
            QString key = kv[0].trimmed();
            QString value = kv[1].trimmed();
            
            if (key == "host") m_pgDb.setHostName(value);
            else if (key == "port") m_pgDb.setPort(value.toInt());
            else if (key == "dbname") m_pgDb.setDatabaseName(value);
            else if (key == "user") m_pgDb.setUserName(value);
            else if (key == "password") m_pgDb.setPassword(value);
        }
    }
    
    if (!m_pgDb.open()) {
        qCritical() << "Failed to open PostgreSQL:" << m_pgDb.lastError().text();
        return false;
    }
    
    m_pgDbInitialized = true;
    return true;
}

void SoftBusCore::storeRawDataToPG(int deviceId, const QByteArray &data,
                                   const QString &creator, int dataLevel,
                                   const QString &protocolType) {
    if (!m_pgDbInitialized || !m_pgDb.isOpen()) {
        return;
    }
    
    QSqlQuery query(m_pgDb);
    query.prepare(R"(
        INSERT INTO raw_data (device_id, data, timestamp, creator, data_level, protocol_type)
        VALUES (?, ?, NOW(), ?, ?, ?)
    )");
    
    query.addBindValue(deviceId);
    query.addBindValue(data);  // BYTEA 类型
    query.addBindValue(creator.isEmpty() ? "system" : creator);
    query.addBindValue(dataLevel);
    query.addBindValue(protocolType);
    
    if (!query.exec()) {
        qWarning() << "Failed to store raw data to PostgreSQL:" << query.lastError().text();
    }
}

// 复杂查询示例
QList<QByteArray> SoftBusCore::queryRawDataAdvancedPG(int deviceId,
                                                       qint64 startTime,
                                                       qint64 endTime,
                                                       const QString &creator,
                                                       int minLevel,
                                                       int maxLevel,
                                                       const QString &protocolType) {
    QList<QByteArray> results;
    
    if (!m_pgDbInitialized || !m_pgDb.isOpen()) {
        return results;
    }
    
    QSqlQuery query(m_pgDb);
    QString sql = "SELECT data FROM raw_data WHERE device_id = ?";
    QVariantList bindValues;
    bindValues << deviceId;
    
    if (startTime > 0) {
        sql += " AND timestamp >= ?";
        bindValues << QDateTime::fromMSecsSinceEpoch(startTime);
    }
    
    if (endTime > 0) {
        sql += " AND timestamp <= ?";
        bindValues << QDateTime::fromMSecsSinceEpoch(endTime);
    }
    
    if (!creator.isEmpty()) {
        sql += " AND creator = ?";
        bindValues << creator;
    }
    
    if (minLevel >= 0) {
        sql += " AND data_level >= ?";
        bindValues << minLevel;
    }
    
    if (maxLevel >= 0) {
        sql += " AND data_level <= ?";
        bindValues << maxLevel;
    }
    
    if (!protocolType.isEmpty()) {
        sql += " AND protocol_type = ?";
        bindValues << protocolType;
    }
    
    sql += " ORDER BY timestamp ASC";
    
    query.prepare(sql);
    for (const QVariant &value : bindValues) {
        query.addBindValue(value);
    }
    
    if (!query.exec()) {
        qWarning() << "Query failed:" << query.lastError().text();
        return results;
    }
    
    while (query.next()) {
        QByteArray data = query.value("data").toByteArray();
        results.append(data);
    }
    
    return results;
}

4. CMakeLists.txt 修改

# 添加 PostgreSQL 支持
find_package(Qt6 REQUIRED COMPONENTS Core Sql)
target_link_libraries(soft_bus
    Qt6::Core
    Qt6::Sql
    # PostgreSQL 客户端库（需要系统安装）
    # libpq (PostgreSQL 客户端库)
)

最终建议

如果您的场景是：

单机应用 → 推荐 RocksDB + SQLite 混合架构
分布式系统 → 推荐 PostgreSQL + TimescaleDB
传统应用 → 推荐 MySQL（如果数据量不大）

决策树

是否需要分布式部署？
├─ 否 → 单机应用
│   ├─ 数据量是否很大（TB级别）？
│   │   ├─ 是 → RocksDB + SQLite 混合架构 ⭐
│   │   └─ 否 → SQLite 单独使用
│   └─ 资源是否受限？
│       ├─ 是 → RocksDB + SQLite 混合架构 ⭐
│       └─ 否 → SQLite 单独使用
│
└─ 是 → 分布式系统
    ├─ 是否需要时间序列优化？
    │   ├─ 是 → PostgreSQL + TimescaleDB ⭐
    │   └─ 否 → PostgreSQL 或 MySQL
    └─ 团队是否熟悉？
        ├─ PostgreSQL → PostgreSQL + TimescaleDB ⭐
        └─ MySQL → MySQL

总结

对于您的 IoT 软总线系统：

如果单机部署：RocksDB + SQLite 混合架构 是最佳选择
如果分布式部署：PostgreSQL + TimescaleDB 是最佳选择
MySQL 适合传统应用场景，但时间序列支持较弱

您可以根据实际部署场景选择最适合的方案。

PostgreSQL和MySQL分析.md 17 KB Verlauf Originalformat

PostgreSQL 和 MySQL 适用性分析

概述

PostgreSQL 分析

✅ 优点

❌ 缺点

MySQL 分析

✅ 优点

❌ 缺点

方案对比

场景假设

方案对比表

详细对比：PostgreSQL vs MySQL vs 混合架构

1. 写入性能

PostgreSQL

MySQL

RocksDB + SQLite 混合架构

2. 复杂查询能力

PostgreSQL（TimescaleDB）

MySQL

RocksDB + SQLite 混合架构

3. 部署复杂度

PostgreSQL

MySQL

RocksDB + SQLite

4. 资源占用

5. 数据迁移成本

从 RocksDB 迁移到 PostgreSQL

从 RocksDB 迁移到 MySQL

迁移到混合架构

推荐方案

场景1：单机应用，大数据量，高频写入

场景2：分布式系统，多节点，需要强一致性

场景3：传统应用，中等数据量，简单查询

PostgreSQL + TimescaleDB 实现示例

1. 安装和初始化

2. 创建表结构

3. Qt 代码集成

4. CMakeLists.txt 修改

最终建议

如果您的场景是：

决策树

总结

PostgreSQL和MySQL分析.md 17 KB

Verlauf Originalformat