BLOB 与 VARCHAR，用于在 MySQL 表中存储数组

arrays java mysql blob varchar

2022-09-03 05:51:37

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

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

我还应该提到，这些数据将被读取并经常更新。

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

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

答案 1

您不创建子表以便每行存储一个浮点值而不是数组，这是有原因的吗？

假设您每天存储一千个包含 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 个浮点数 = 1227 字节的 Java 数组，则总空间？

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

您说您每天存储几百个矢量，因此您将在短短81年内（而不是239年）填满100GB的分区。

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

大多数MySQL应用程序可以实现每秒数百或数千次插入，而无需过多的巫术。

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

显式事务
多行插入语法
插入延迟（如果您仍在使用 MyISAM）
加载文件中的数据
更改表禁用键，执行插入，更改表启用键

在您喜欢的搜索引擎上搜索短语“mysql每秒插入数”，以阅读许多讨论此内容的文章和博客。

答案 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