guardar - insertar json en mysql

Soporte nativo de JSON en MYSQL 5.7: ¿cuáles son los pros y los contras del tipo de datos JSON en MYSQL?

Question

En MySQL 5.7 se ha agregado un nuevo tipo de datos para almacenar datos JSON en tablas MySQL . Obviamente será un gran cambio en MySQL. Enumeraron algunos beneficios

Validación de documentos : solo los documentos JSON válidos se pueden almacenar en una columna JSON, por lo que obtendrá una validación automática de sus datos.

Acceso eficiente : lo que es más importante, cuando almacena un documento JSON en una columna JSON, no se almacena como un valor de texto sin formato. En cambio, se almacena en un formato binario optimizado que permite un acceso más rápido a los miembros del objeto y los elementos de la matriz.

Rendimiento : mejore el rendimiento de su consulta creando índices en valores dentro de las columnas JSON. Esto se puede lograr con "índices funcionales" en columnas virtuales.

Conveniencia : la sintaxis en línea adicional para las columnas JSON hace que sea muy natural integrar consultas de documentos dentro de su SQL. Por ejemplo (features.feature es una columna JSON): SELECT feature->"$.properties.STREET" AS property_street FROM features WHERE id = 121254;

GUAUU ! Incluyen algunas características excelentes. Ahora es más fácil manipular datos. Ahora es posible almacenar datos más complejos en columna. Así que MySQL ahora está aromatizado con NoSQL.

Ahora puedo imaginar una consulta para datos JSON algo así como

SELECT * FROM t1
WHERE JSON_EXTRACT(data,"$.series") IN
(
SELECT JSON_EXTRACT(data,"$.inverted")
FROM t1 | {"series": 3, "inverted": 8}
WHERE JSON_EXTRACT(data,"$.inverted")<4 );

Entonces, ¿puedo almacenar grandes relaciones pequeñas en pocas columnas json? ¿Esta bien? ¿Rompe la normalización? Si esto es posible, supongo que actuará como NoSQL en una columna de MySQL . Realmente quiero saber más sobre esta característica. Pros y contras del tipo de datos MySQL JSON.

Answer 1

Desde mi experiencia, la implementación de JSON al menos en MySql 5.7 no es muy útil debido a su bajo rendimiento. Bueno, no es tan malo para leer datos y validación. Sin embargo, la modificación JSON es 10-20 veces más lenta con MySql que con Python o PHP. Imaginemos JSON muy simple:

{ "name": "value" }

Supongamos que tenemos que convertirlo en algo así:

{ "name": "value", "newName": "value" }

Puede crear un script simple con Python o PHP que seleccionará todas las filas y las actualizará una por una. No está obligado a realizar una gran transacción, por lo que otras aplicaciones pueden usar la tabla en paralelo. Por supuesto, también puede hacer una gran transacción si lo desea, por lo que obtendrá la garantía de que MySql realizará "todo o nada", pero es probable que otras aplicaciones no puedan usar la base de datos durante la ejecución de la transacción.

Tengo una tabla de 40 millones de filas, y el script de Python lo actualiza en 3-4 horas.

Ahora tenemos MySql JSON, por lo que ya no necesitamos Python o PHP, podemos hacer algo así:

UPDATE `JsonTable` SET `JsonColumn` = JSON_SET(`JsonColumn`, "newName", JSON_EXTRACT(`JsonColumn`, "name"))

Se ve simple y excelente. Sin embargo, su velocidad es 10-20 veces más lenta que la versión de Python, y es una transacción única, por lo que otras aplicaciones no pueden modificar los datos de la tabla en paralelo.

Entonces, si solo queremos duplicar la clave JSON en la tabla de 40 millones de filas, no debemos usar la tabla durante 30-40 horas. No tiene sentido.

Acerca de la lectura de datos, desde mi experiencia, el acceso directo al campo JSON a través de JSON_EXTRACT en WHERE también es extremadamente lento (mucho más lento que TEXT con LIKE en una columna no indexada). Las columnas virtuales generadas funcionan mucho más rápido, sin embargo, si conocemos nuestra estructura de datos de antemano, no necesitamos JSON, podemos usar columnas tradicionales en su lugar. Cuando usamos JSON donde es realmente útil, es decir, cuando la estructura de datos es desconocida o cambia con frecuencia (por ejemplo, configuraciones de complementos personalizados), la creación de columnas virtuales de manera regular para cualquier posible nueva columna no parece una buena idea.

Python y PHP hacen que la validación JSON sea un encanto, por lo que es cuestionable si necesitamos validación JSON en el lado de MySql. ¿Por qué no validar también los documentos XML, Microsoft Office o revisar la ortografía? ;)

Answer 2

Lo siguiente de MySQL 5.7 trae de vuelta sexy con JSON me suena bien:

El uso del tipo de datos JSON en MySQL tiene dos ventajas sobre el almacenamiento de cadenas JSON en un campo de texto:

Validación de datos. Los documentos JSON se validarán automáticamente y los documentos no válidos generarán un error. Formato de almacenamiento interno mejorado. Los datos JSON se convierten a un formato que permite el acceso de lectura rápida a los datos en un formato estructurado. El servidor puede buscar subobjetos o valores anidados por clave o índice, lo que permite una mayor flexibilidad y rendimiento.

...

Los sabores especializados de las tiendas NoSQL (bases de datos de documentos, tiendas de valores clave y bases de datos de gráficos) son probablemente mejores opciones para sus casos de uso específicos, pero la adición de este tipo de datos podría permitirle reducir la complejidad de su pila de tecnología. El precio está acoplado a bases de datos MySQL (o compatibles). Pero eso no es un problema para muchos usuarios.

Tenga en cuenta el lenguaje sobre la validación de documentos, ya que es un factor importante. Supongo que es necesario realizar una batería de pruebas para comparar los dos enfoques. Esos dos son:

1. Mysql con tipos de datos JSON
2. Mysql sin

La red tiene compartidas diapositivas poco profundas a partir de ahora sobre el tema de mysql / json / performance por lo que estoy viendo.

Quizás tu publicación pueda ser un centro para ello. O tal vez el rendimiento es un pensamiento posterior, no estoy seguro, y simplemente está emocionado de no crear un montón de tablas.

Answer 3

Me metí en este problema recientemente y resumo las siguientes experiencias:

1, no hay una manera de resolver todas las preguntas. 2, debe usar el JSON correctamente.

Un caso:

Tengo una tabla llamada: CustomField , y debe tener dos columnas: name , fields . name es una cadena localizada, su contenido debería gustar:

{
"en":"this is English name",
"zh":"this is Chinese name"
...(other languages)
}

Y los fields deberían ser así:

[
{
"filed1":"value",
"filed2":"value"
...
},
{
"filed1":"value",
"filed2":"value"
...
}
...
]

Como puede ver, tanto el name como los fields se pueden guardar como JSON, ¡y funciona!

Sin embargo, si uso el name para buscar en esta tabla con mucha frecuencia, ¿qué debo hacer? ¿Utiliza JSON_CONTAINS , JSON_EXTRACT ...? Obviamente, ya no es una buena idea guardarlo como JSON, deberíamos guardarlo en una tabla independiente: CustomFieldName .

Del caso anterior, creo que debes tener en cuenta estas ideas:

1. ¿Por qué MYSQL admite JSON?
2. ¿Por qué quieres usar JSON? ¿Tu lógica de negocios solo necesitaba esto? ¿O hay algo más?
3. Nunca seas perezoso

Gracias

Answer 4

SELECT * FROM t1
WHERE JSON_EXTRACT(data,"$.series") IN ...

El uso de una columna dentro de una expresión o función como esta arruina cualquier posibilidad de que la consulta use un índice para ayudar a optimizar la consulta. La consulta que se muestra arriba se ve obligada a hacer un escaneo de tabla.

La afirmación sobre el "acceso eficiente" es engañosa. Significa que después de que la consulta examina una fila con un documento JSON, puede extraer un campo sin tener que analizar el texto de la sintaxis JSON. Pero todavía se necesita un escaneo de tabla para buscar filas. En otras palabras, la consulta debe examinar cada fila.

Por analogía, si busco en una guía telefónica personas con el primer nombre "Bill", todavía tengo que leer cada página de la guía telefónica, incluso si los nombres se han resaltado para que sea un poco más rápido detectarlos.

MySQL 5.7 le permite definir una columna virtual en la tabla y luego crear un índice en la columna virtual.

ALTER TABLE t1
ADD COLUMN series AS (JSON_EXTRACT(data, ''$.series'')),
ADD INDEX (series);

Luego, si consulta la columna virtual, puede usar el índice y evitar el escaneo de la tabla.

SELECT * FROM t1
WHERE series IN ...

O incluso si consulta la expresión exacta en la que se basa la columna virtual (como en su consulta original), también puede usar el índice.

Esto es bueno, pero pierde el sentido de usar JSON. La parte atractiva de usar JSON es que le permite agregar nuevos atributos sin tener que hacer ALTER TABLE. Pero resulta que debe definir una columna adicional (virtual) de todos modos, si desea buscar campos JSON con la ayuda de un índice.

Pero no tiene que definir columnas e índices virtuales para cada campo en el documento JSON, solo aquellos en los que desea buscar u ordenar. Podría haber otros atributos en el JSON que solo necesita extraer en la lista de selección como los siguientes:

SELECT JSON_EXTRACT(data, ''$.series'') AS series FROM t1
WHERE <other conditions>

En general, diría que esta es la mejor manera de usar JSON en MySQL. Solo en la lista de selección.

Cuando hace referencia a columnas en otras cláusulas (JOIN, WHERE, GROUP BY, HAVING, ORDER BY), es más eficiente usar columnas convencionales, no campos dentro de documentos JSON.

Presenté una charla llamada Cómo usar JSON en MySQL Wrong en la conferencia Percona Live en abril de 2018. Actualizaré y repetiré la charla en Oracle Code One en el otoño.

Hay otros problemas con JSON. Por ejemplo, en mis pruebas requirió 2-3 veces más espacio de almacenamiento para documentos JSON en comparación con las columnas convencionales que almacenan los mismos datos.

MySQL está promoviendo sus nuevas capacidades JSON agresivamente, en gran medida para disuadir a las personas de no migrar a MongoDB. Pero el almacenamiento de datos orientado a documentos como MongoDB es fundamentalmente una forma no relacional de organizar datos. Es diferente de lo relacional. No digo que una sea mejor que la otra, es solo una técnica diferente, adecuada para diferentes tipos de consultas.

Debe elegir usar JSON cuando JSON hace que sus consultas sean más eficientes.

No elija una tecnología solo porque sea nueva o por el bien de la moda.