BLOB по сравнению с VARCHAR для хранения массивов в таблице MySQL
У меня есть проектное решение сделать, и ищу некоторый совет лучшей практики. У меня есть программа Java, которая должна сохранить большое количество (небольшое количество сотни в день) массивов с плавающей точкой в базе данных MySQL. Данные являются фиксированной длиной Double массив длины 300. Я вижу три разумных опции:
- Храните данные как BLOB.
- Сериализируйте данные и сохраните их как VARCHAR.
- Запишите данные в диск как двоичный файл и сохраните ссылку на него вместо этого.
Я должен также упомянуть, что эти данные будут считаны с и часто обновляться.
Я хочу использовать BLOB начиная с того, именно это я сделал в прошлом, и оно походит на наиболее эффективный способ (например, поддерживает зафиксированную ширину, и никакая потребность преобразовать в запятую не разделила строку). Однако мой коллега настаивает, чтобы мы сериализировали и использовать varchar по причинам, которые кажутся главным образом догматичными.
Если один из этих методов лучше, чем другой, причины Java или конкретный MySQL?
4 ответа
Сохранить как большой двоичный объект (см. Пример кода ниже). Я думаю, что это, вероятно, лучше, чем использование 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
Есть ли причина, по которой вы не создаете дочернюю таблицу, чтобы хранить одно значение с плавающей точкой в строке, а не массив?
Скажем, вы храните тысячу массивов по 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 лет.
В ответ на ваш комментарий: Производительность INSERT - важный вопрос, но вы храните всего несколько сотен векторов в день.
Большинство приложений MySQL могут достигать сотен или тысяч вставок в секунду без излишнего мудрствования.
Если вам нужна оптимальная производительность, вот некоторые вещи, на которые стоит обратить внимание:
- Явные транзакции
- Синтаксис многорядного INSERT
- INSERT DELAYED (если вы все еще используете MyISAM)
- LOAD DATA INFILE
- ALTER TABLE DISABLE KEYS, сделайте вставки, ALTER TABLE ENABLE KEYS
Найдите фразу "mysql inserts per second" в вашей любимой поисковой системе, чтобы прочитать множество статей и блогов, говорящих об этом.
Если вы просто хотите хранить данные в виде двоичного дампа массива Java, то, конечно, используйте BLOB. Ваш друг вполне может посоветовать отказаться от этого, поскольку вы можете захотеть, чтобы какая-нибудь не Java-программа использовала эту информацию позднее, и двоичные дампы, вероятно, будет больно интерпретировать.
При сериализации в VARCHAR вы знаете формат данных и можете легко прочитать их в любом приложении.
Конечно, если есть хоть малейший шанс, что вы захотите манипулировать отдельными плавающими числами или создавать отчеты, их следует хранить в формате, удобном для работы с базой данных. Другими словами, не двоичный дамп, не сериализованный, не столбец CSV.
Храните их так, как задумал Кодд, в третьей нормальной форме.
Кстати, несколько сотен 300-элементных массивов с плавающей запятой каждый день - это не большая база данных. Поверьте человеку, работавшему на мэйнфрейме с DB2, большинство СУБД легко справятся с таким объемом. Мы собираем десятки миллионов строк каждый день в наше приложение, и оно даже не вспотело.
Использование базы данных для хранения одномерного массива - это головная боль! Даже больше, используя rdm, где нет связи между сохраненными данными. извините, но лучшее решение imho - использовать файл и просто записывать данные так, как вам нравится. двоичный или как txt. Таким образом, размер длинной строки 300x1 или строки текста размером 300x1 составляет один массив.