# PostgreSQL 和 MySQL 适用性分析

## 概述

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

---

## PostgreSQL 分析

### ✅ 优点

1. **强大的 SQL 支持**
   - 完整的 SQL 标准支持（比 MySQL 更接近标准）
   - 支持复杂查询、窗口函数、CTE（公用表表达式）
   - 支持 JSON/JSONB 类型，适合存储协议数据

2. **时间序列数据支持**
   - TimescaleDB 扩展：专门优化时间序列数据
   - 自动分区、压缩、连续聚合
   - 非常适合 IoT 传感器数据场景

3. **扩展性好**
   - 支持多种数据类型（JSONB、数组、范围类型等）
   - 支持自定义函数和存储过程
   - 支持多种索引类型（B-tree、Hash、GiST、GIN 等）

4. **并发控制优秀**
   - MVCC（多版本并发控制）
   - 读写不阻塞
   - 适合多设备同时写入

5. **数据完整性**
   - ACID 事务保证
   - 外键约束、触发器
   - 数据一致性保证

### ❌ 缺点

1. **部署复杂度**
   - 需要单独的数据库服务器
   - 需要配置、启动、维护服务
   - 不适合嵌入式/单机应用

2. **资源占用**
   - 内存占用较大（默认配置可能需要 1GB+）
   - 不适合资源受限的设备

3. **Qt 集成**
   - 需要安装 PostgreSQL 客户端库
   - 需要配置连接字符串
   - 网络延迟（如果远程连接）

4. **写入性能**
   - 虽然性能优秀，但不如 RocksDB 的纯 KV 写入
   - 需要事务提交，可能成为瓶颈
   - 对于超高频率写入（>10万/秒），可能不如 RocksDB

5. **单机场景**
   - 如果只是单机应用，使用 PostgreSQL 可能过度设计

---

## MySQL 分析

### ✅ 优点

1. **广泛使用**
   - 生态系统成熟，文档丰富
   - 社区支持好

2. **性能优化**
   - InnoDB 引擎提供良好的并发性能
   - 支持多种存储引擎（MyISAM、InnoDB、Memory 等）

3. **简单易用**
   - 配置相对简单
   - 工具链成熟

4. **Qt 集成**
   - Qt 原生支持 MySQL 驱动
   - 连接配置简单

### ❌ 缺点

1. **时间序列数据支持弱**
   - 没有类似 TimescaleDB 的扩展
   - 时间序列查询性能不如 PostgreSQL + TimescaleDB

2. **JSON 支持较弱**
   - MySQL 5.7+ 支持 JSON 类型，但功能不如 PostgreSQL JSONB
   - 查询性能一般

3. **部署复杂度**
   - 同样需要单独的数据库服务器
   - 不适合嵌入式场景

4. **写入性能**
   - 同样不如 RocksDB 的纯 KV 写入
   - 事务开销较大

5. **扩展性**
   - 不如 PostgreSQL 灵活
   - 自定义函数和类型支持有限

---

## 方案对比

### 场景假设

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

### 方案对比表

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

---

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

### 1. 写入性能

#### PostgreSQL
```sql
-- 批量插入示例
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
```sql
-- 批量插入示例（类似）
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 混合架构
```cpp
// 直接写入 RocksDB，无事务开销
m_rawDb->Put(key, value);  // 性能：10-50万行/秒
// SQLite 索引异步写入
storeRawDataIndex(...);    // 可批量写入，延迟低
```

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

---

### 2. 复杂查询能力

#### PostgreSQL（TimescaleDB）
```sql
-- 时间序列查询 + 聚合
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
```sql
-- 类似查询
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 混合架构
```cpp
// 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
```sql
-- 需要编写迁移脚本，从 RocksDB 读取数据，写入 PostgreSQL
-- 可能耗时较长（TB级别数据）
```

#### 从 RocksDB 迁移到 MySQL
```sql
-- 类似，需要迁移脚本
```

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

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

---

## 推荐方案

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

**推荐：RocksDB + SQLite 混合架构**

**理由**：
- ✅ 保持 RocksDB 的高性能写入
- ✅ SQLite 提供复杂查询能力
- ✅ 零部署复杂度
- ✅ 资源占用低
- ✅ 渐进式迁移，风险低

**适用条件**：
- 单机部署
- 数据量 TB 级别
- 需要复杂查询
- 资源受限或需要轻量级方案

---

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

**推荐：PostgreSQL + TimescaleDB**

**理由**：
- ✅ 时间序列数据优化
- ✅ 支持分布式查询
- ✅ 强一致性保证
- ✅ 强大的 SQL 能力
- ✅ 适合多客户端访问

**适用条件**：
- 多服务器部署
- 需要多客户端同时访问
- 需要强一致性
- 有专门的运维团队

---

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

**推荐：MySQL**

**理由**：
- ✅ 简单易用
- ✅ 生态系统成熟
- ✅ Qt 支持好

**适用条件**：
- 数据量中等（< 100GB）
- 查询需求简单
- 团队熟悉 MySQL

---

## PostgreSQL + TimescaleDB 实现示例

### 1. 安装和初始化

```bash
# 安装 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. 创建表结构

```sql
-- 创建原始数据表
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 代码集成

```cpp
// 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 修改

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

---

## 最终建议

### 如果您的场景是：

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

### 决策树

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

---

## 总结

对于您的 IoT 软总线系统：

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

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