java - BLOB vs. VARCHAR para almacenar arreglos en una tabla MySQL

arrays

Question

Tengo que tomar una decisión de diseño y estoy buscando algunos consejos de mejores prácticas. Tengo un programa java que necesita almacenar una gran cantidad (unos pocos cientos al día) de arreglos de punto flotante en una base de datos MySQL. Los datos son una matriz fija de longitud Double de longitud 300. Puedo ver tres opciones razonables:

1. Almacena los datos como un BLOB.
2. Serializar los datos y almacenarlos como un VARCHAR.
3. Escriba los datos en el disco como un archivo binario y almacene una referencia a él en su lugar.

También debo mencionar que estos datos se leerán y actualizarán con frecuencia.

Quiero usar un BLOB ya que eso es lo que he hecho en el pasado y parece ser el método más eficiente (por ejemplo, mantiene el ancho fijo y no es necesario convertirlo en una cadena separada por comas). Sin embargo, mi compañero de trabajo insiste en que deberíamos serializar y usar varchar por razones que parecen en su mayoría dogmáticas.

Si uno de estos métodos es mejor que el otro, ¿son las razones específicas de Java o MySQL?

Answer 1

¡Usar una base de datos para almacenar una matriz unidimensional es una molestia! Aún más usando un rdm donde no hay relación entre los datos almacenados. Lo siento, pero la mejor solución es usar un archivo y simplemente escribir los datos como quieras. binario o como txt. Por lo tanto, 300xsize de una línea larga o 300x1 de txt es una matriz.

Answer 2

¿Hay alguna razón por la que no cree una tabla secundaria para poder almacenar un valor de punto flotante por fila, en lugar de una matriz?

Digamos que almacena mil arreglos de 300 elementos cada uno por día. Eso es 300,000 filas por día, o 109.5 millones por año. Nada para estornudar, pero dentro de las capacidades de MySQL o cualquier otro RDBMS.

Re sus comentarios:

Claro, si el orden es significativo, agregue otra columna para el pedido. Así es como yo diseñaría la mesa:

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)
);

• Espacio total para una fila de datos vectoriales: 300x (4 + 2 + 2 + 4) = 3600 bytes. Más directorio de registro InnoDB (cosas internas) de 16 bytes.

• ¿Espacio total si serializa una matriz Java de 300 flotantes = 1227 bytes?

Por lo tanto, ahorra aproximadamente 2400 bytes, o el 67% del espacio almacenando la matriz. Pero suponga que tiene 100 GB de espacio para almacenar la base de datos. El almacenamiento de una matriz serializada le permite almacenar 87.5 millones de vectores, mientras que el diseño normalizado solo le permite almacenar 29.8 millones de vectores.

Usted dijo que almacena unos pocos cientos de vectores por día, por lo que llenará esa partición de 100 GB en solo 81 años en lugar de 239 años.

Con respecto a su comentario: el rendimiento de INSERT es un problema importante, pero solo está almacenando unos pocos cientos de vectores por día.

La mayoría de las aplicaciones MySQL pueden lograr cientos o miles de inserciones por segundo sin un uso excesivo de la magia.

Si necesita un rendimiento óptimo, aquí hay algunas cosas que debe considerar:

• Transacciones explícitas
• Sintaxis INSERT de varias filas
• INSERTAR EN RETRASO (si todavía usas MyISAM)
• Datos de carga infile
• ALTERAR LAS TECLAS DE DESHABILITACIÓN DE LA TABLA, hacer las inserciones, ALTER TABLE ENABLE CLAVES

Busque la frase "inserciones de mysql por segundo" en su motor de búsqueda favorito para leer muchos artículos y blogs que hablan sobre esto.

Answer 3

Almacene como un BLOB así (vea el ejemplo de código a continuación). Creo que esto es probablemente mejor que usar la serialización de Java ya que la serialización incorporada de Java necesitará 2427 bytes, y las aplicaciones que no sean de Java tendrán más dificultades para manejar los datos. Es decir, en caso de que alguna vez haya aplicaciones no Java que consulten la base de datos en el futuro ... de lo contrario, la serialización integrada es un par de líneas menos.

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;
    }

}

Edición: esperaba usar BINARY (2400), pero MySQL dice:

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 4

Si solo desea almacenar los datos como un volcado binario de la matriz de Java, entonces, por supuesto, use un BLOB. Es posible que su amigo no lo advierta, ya que es posible que desee que algún programa que no sea de Java utilice la información en una fecha posterior, por lo que es probable que sea difícil interpretar los volcados binarios.

Con la serialización a un VARCHAR, conoce el formato de datos y puede leerlo fácilmente con cualquier aplicación.

Por supuesto, si existe la más mínima posibilidad de que quiera manipular o informar sobre los flotadores individuales, deben almacenarse en un formato compatible con la base de datos. En otras palabras, no es un volcado binario, no se serializa, no es una columna CSV.

Almacénelos según lo previsto por Codd, en tercera forma normal.

Por cierto, unos pocos cientos de arreglos de punto flotante de 300 elementos cada día no son una gran base de datos. Tómelo de alguien que trabaje en el mainframe con DB2, la mayoría de los DBMS manejarán fácilmente ese tipo de volumen. Recopilamos decenas de millones de filas cada día en nuestra aplicación y ni siquiera se pone a sudar.