BLOB и VARCHAR для хранения массивов в таблице MySQL

У меня есть дизайнерское решение, и я ищу советы по лучшей практике. У меня есть Java-программа, которая должна хранить большое количество (несколько сотен в день) массивов с плавающей запятой в базе данных MySQL. Данные представляют собой массив с фиксированной длиной Double длиной 300. Я вижу три разумных варианта:

Храните данные в виде BLOB.
Сериализовать данные и сохранить их как VARCHAR.
Запишите данные на диск в виде двоичного файла и сохраните ссылку на него.

Следует также упомянуть, что эти данные будут часто читаться и обновляться.

Я хочу использовать BLOB, поскольку это то, что я делал в прошлом, и он кажется наиболее эффективным методом (например, поддерживает фиксированную ширину и не нуждается в преобразовании в строку, разделенную запятой). Однако мой коллега настаивает на том, что мы должны сериализовать и использовать varchar по причинам, которые кажутся в основном догматическими.

Если один из этих методов лучше, чем другой, являются ли причины Java или MySQL конкретными?

Ответ 1

Храните как 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

Ответ 2

Есть ли причина, по которой вы не создаете дочернюю таблицу, чтобы сохранить одно значение с плавающей запятой в строке вместо массива?

Скажите, что вы храните тысячи массивов по 300 элементов каждый день. Это 300 000 рядов в день, или 109,5 миллионов в год. Ничего не чихать, но в пределах возможностей MySQL или любой другой РСУБД.

Ваши комментарии:

Конечно, если порядок значителен, вы добавляете еще один столбец для заказа. Вот как бы я создал таблицу:

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 байт. Плюс каталог записей InnoDB (внутреннее содержимое) из 16 байтов.
Общая площадь, если вы сериализуете массив Java из 300 поплавков = 1227 байт?

Таким образом, вы сохраняете около 2400 байт или 67% пространства, сохраняя массив. Но предположим, что у вас есть 100 ГБ пространства для хранения базы данных. Сохранение сериализованного массива позволяет хранить 87,5 млн. Векторов, тогда как нормализованный дизайн позволяет хранить 29,8 млн. Векторов.

Вы сказали, что вы храните несколько сотен векторов в день, поэтому вы будете заполнять этот раздел размером 100 ГБ всего за 81 год вместо 239 лет.

Re: Производительность INSERT - важная проблема, но вы сохраняете только несколько сотен векторов в день.

Большинство приложений MySQL могут достигать сотен или тысяч вложений в секунду без чрезмерного колдовства.

Если вам нужна оптимальная производительность, вот некоторые вещи, на которые нужно обратить внимание:

Явные транзакции
Многострочный синтаксис INSERT
INSERT DELAYED (если вы все еще используете MyISAM)
LOAD DATA INFILE
ALTER TABLE DISABLE KEYS, сделайте вкладки, ALTER TABLE ENABLE KEYS

Найдите фразу "mysql inserts per second" в вашей любимой поисковой системе, чтобы прочитать много статей и блогов об этом.

Ответ 3

Если вы просто хотите сохранить данные в виде двоичного дампа массива Java, тогда, во всяком случае, используйте BLOB. Ваш друг, возможно, будет советовать против этого, так как вы можете захотеть, чтобы какая-то не-Java-программа использовала эту информацию позже, поэтому двоичные дампы, вероятно, являются болью для интерпретации.

С сериализацией в VARCHAR вы знаете формат данных и можете легко прочитать его в любом приложении.

Конечно, если есть хоть малейшая вероятность, что вы захотите манипулировать или сообщать об отдельных поплавках, они должны храниться в формате, удобном для базы данных. Другими словами, это не двоичный дамп, а не сериализованный, а не столбец CSV.

Сохраните их как Codd в третьей нормальной форме.

Кстати, несколько сотен 300-элементных массивов с плавающей запятой каждый день не являются большой базой данных. Возьмите его у кого-то, кто работает на мэйнфрейме с DB2, большинство СУБД легко справятся с этим типом тома. Мы собираем десятки миллионов строк каждый день в наше приложение, и оно даже не врывается в пот.

Ответ 4

Использование базы данных для хранения одномерного массива - боль в заднице! Более того, используя rdm, где нет никакой связи между сохраненными данными. извините, но лучшее решение imho использует файл и просто записывает данные так, как вам нравится. двоичный или txt. Таким образом, 300xsize длинной или 300x1 строки txt - это один массив.