在 MySQL 表中存储数组的 BLOB 与 VARCHAR

Question

我需要做出设计决策，并正在寻找一些最佳实践建议。我有一个java程序，需要在MySQL数据库中存储大量（每天几百个）浮点数组。数据是长度为 300 的固定长度 Double 数组。我可以看到三个合理的选项：

1. 将数据存储为 BLOB。
2. 序列化数据并将其存储为 VARCHAR。
3. 将数据作为二进制文件写入磁盘并存储对其的引用。

我还应该提到的是，这些数据将被频繁读取和更新。

我想使用 BLOB，因为这是我过去所做的，而且它似乎是最有效的方法（例如，保持固定宽度且无需转换为逗号分隔的字符串）。然而，我的同事坚持认为我们应该序列化并使用 varchar，其原因似乎很教条。

如果这些方法中的一种比另一种更好，那么原因是 Java 或 MySQL 特定的吗？

Answer 1

您是否有理由不创建子表以便每行存储一个浮点值而不是数组？

假设您每天存储 1000 个数组，每个数组包含 300 个元素。即每天 300,000 行，或每年 1.095 亿行。没有什么可以轻视的，但在 MySQL 或任何其他 RDBMS 的能力范围内。

---

回复您的评论：

当然，如果订单很重要，您可以为订单添加另一列。以下是我设计表格的方式：

CREATE TABLE VectorData (
  trial_id INT NOT NULL,
  vector_no SMALLINT UNSIGNED NOT NULL,
  order_no SMALLINT UNSIGNED NOT NULL,
  element FLOAT NOT NULL,
  PRIMARY KEY (trial_id, vector_no),
  FOREIGN KEY (trial_id) REFERENCES Trials (trial_id)
);

• 一行向量数据的总空间：300x(4+2+2+4) = 3600 字节。再加上 16 字节的 InnoDB 记录目录（内部内容）。

• 序列化 300 个浮点的 Java 数组时的总空间 = 1227 字节？

因此，通过存储数组可以节省大约 2400 字节，即 67% 的空间。但假设您有 100GB 的空间来存储数据库。存储序列化数组允许您存储 8750 万个向量，而标准化设计仅允许您存储 2980 万个向量。

你说你每天存储几百个向量，所以你只需 81 年而不是 239 年就能填满那个 100GB 分区。

---

回复您的评论： INSERT 的性能 是一个重要的问题，但您每天只存储几百个向量。

大多数 MySQL 应用程序无需过多的魔法即可实现每秒数百或数千次插入。

如果您需要最佳性能，请考虑以下事项：

• 显式事务
• 多行 INSERT 语法
• INSERT DELAYED（如果您仍然使用 MyISAM）
• LOAD DATA INFILE
• ALTER TABLE DISABLE KEYS，执行插入，ALTER TABLE ENABLE KEYS

搜索短语在您最喜欢的搜索引擎上“mysql inserts per Second”可以阅读许多讨论此问题的文章和博客。

Answer 2

像这样存储为 BLOB（请参阅下面的代码示例）。我认为这可能比使用java序列化更好，因为java的内置序列化将需要2427字节，并且非java应用程序将更难处理数据。也就是说，将来是否应该有任何非 Java 应用程序查询数据库......如果没有，那么内置序列化会少几行。

public static void storeInDB() throws IOException, SQLException {

    double[] dubs = new double[300];

    ByteArrayOutputStream bout = new ByteArrayOutputStream();
    DataOutputStream dout = new DataOutputStream(bout);
    for (double d : dubs) {
        dout.writeDouble(d);
    }
    dout.close();
    byte[] asBytes = bout.toByteArray();

    PreparedStatement stmt = null;  // however we normally get this...
    stmt.setBytes(1, asBytes);

}

public static double[] readFromDB() throws IOException, SQLException {

    ResultSet rs = null;  // however we normally get this...
    while (rs.next()) {
        double[] dubs = new double[300];
        byte[] asBytes = rs.getBytes("myDoubles");
        ByteArrayInputStream bin = new ByteArrayInputStream(asBytes);
        DataInputStream din = new DataInputStream(bin);
        for (int i = 0; i < dubs.length; i++) {
            dubs[i] = din.readDouble();
        }
        return dubs;
    }

}

编辑：我希望使用 BINARY(2400)，但 MySQL 说：

mysql> create table t (a binary(2400)) ;
ERROR 1074 (42000): Column length too big for column 'a' (max = 255);
use BLOB or TEXT instead

Answer 3

如果您只想将数据存储为 Java 数组的二进制转储，那么请务必使用 BLOB。您的朋友可能会建议不要这样做，因为您可能希望某些非 Java 程序在以后使用该信息，因此二进制转储可能很难解释。

通过序列化为 VARCHAR，您了解数据格式，并且可以使用任何应用程序轻松读取它。

当然，如果您有哪怕一丁点想要操作或报告单个浮点数的可能性，它们都应该以数据库友好的格式存储。换句话说，不是二进制转储，不是序列化，不是 CSV 列。

按照 Codd 的意图，以第三范式存储它们。

顺便说一下，每天几百个300个元素的浮点数组并不是一个大数据库。从使用 DB2 在大型机上工作的人那里得知，大多数 DBMS 都可以轻松处理这种容量。我们每天都会将数千万行数据收集到我们的应用程序中，而且它甚至不费吹灰之力。

Answer 4

使用数据库来存储一维数组真是太痛苦了！
甚至更多地使用 rdm，其中存储的数据之间没有关系。
抱歉，但恕我直言，最好的解决方案是使用文件并按照您喜欢的方式写入数据。
二进制或 txt 形式。
因此，300xsize 的长文本或 300x1 行的 txt 是一个数组。