mysql - que - mongodb español

Almacenamiento de JSON en la base de datos contra una nueva columna para cada clave (9)

Actualizado el 4 de junio de 2017

Dado que esta pregunta / respuesta ha ganado algo de popularidad, pensé que valía la pena una actualización.

Cuando esta pregunta se publicó originalmente, MySQL no tenía soporte para los tipos de datos JSON y el soporte en PostgreSQL estaba en su infancia. Desde 5.7, MySQL ahora es compatible con un tipo de datos JSON (en un formato de almacenamiento binario), y PostgreSQL JSONB ha madurado significativamente. Ambos productos proporcionan tipos de JSON ejecutables que pueden almacenar documentos arbitrarios, incluido el soporte para indexar claves específicas del objeto JSON.

Sin embargo, sigo de acuerdo con mi afirmación original de que su preferencia predeterminada, cuando se utiliza una base de datos relacional, debe seguir siendo columna por valor. Las bases de datos relacionales todavía se basan en el supuesto de que los datos dentro de ellas estarán bastante bien normalizados. El planificador de consultas tiene una mejor información de optimización cuando mira las columnas que cuando mira las teclas en un documento JSON. Las claves externas se pueden crear entre columnas (pero no entre claves en documentos JSON). Es importante destacar que si la mayoría de su esquema es lo suficientemente volátil como para justificar el uso de JSON, es posible que desee al menos considerar si una base de datos relacional es la opción correcta.

Dicho esto, algunas aplicaciones son perfectamente relacionales o están orientadas a documentos. La mayoría de las aplicaciones tienen alguna combinación de ambos. Aquí hay algunos ejemplos en los que personalmente he encontrado que JSON es útil en una base de datos relacional:

Al almacenar direcciones de correo electrónico y números de teléfono para un contacto, almacenarlos como valores en una matriz JSON es mucho más fácil de administrar que varias tablas separadas
Guardar preferencias arbitrarias de clave / valor del usuario (donde el valor puede ser booleano, textual o numérico, y no desea tener columnas separadas para diferentes tipos de datos)
Almacenamiento de datos de configuración que no tienen un esquema definido (si está compilando Zapier o IFTTT y necesita almacenar datos de configuración para cada integración)

Estoy seguro de que hay otros también, pero estos son solo algunos ejemplos rápidos.

Respuesta original

Si realmente desea poder agregar tantos campos como desee sin limitación (que no sea un límite de tamaño de documento arbitrario), considere una solución NoSQL como MongoDB.

Para bases de datos relacionales: use una columna por valor. Al poner un blob JSON en una columna, es prácticamente imposible realizar consultas (y se ralentiza mucho cuando se encuentra una consulta que funciona).

Las bases de datos relacionales aprovechan los tipos de datos al indexar, y están destinadas a implementarse con una estructura normalizada .

Como nota al margen: esto no quiere decir que nunca se debe almacenar JSON en una base de datos relacional. Si está agregando metadatos verdaderos, o si su JSON está describiendo información que no necesita ser consultada y solo se usa para mostrar, puede ser excesivo crear una columna separada para todos los puntos de datos.

Estoy implementando el siguiente modelo para almacenar datos relacionados con el usuario en mi tabla: tengo 2 columnas: uid (clave principal) y una meta columna que almacena otros datos sobre el usuario en formato JSON.

uid | meta -------------------------------------------------- 1 | {name:[''foo''], | emailid:[''[email protected]'',''[email protected]'']} -------------------------------------------------- 2 | {name:[''sann''], | emailid:[''[email protected]'',''[email protected]'']} --------------------------------------------------

¿Es esta una mejor manera (en términos de rendimiento, diseño) que el modelo de una columna por propiedad, donde la tabla tendrá muchas columnas como uid , name , emailid .

Lo que me gusta del primer modelo es que puedes agregar tantos campos como sea posible sin limitaciones.

Además, me preguntaba, ahora que he implementado el primer modelo. ¿Cómo realizo una consulta en él, por ejemplo, quiero buscar a todos los usuarios que tienen un nombre como ''foo''?

Pregunta : ¿Cuál es la mejor forma de almacenar datos relacionados con el usuario (teniendo en cuenta que la cantidad de campos no es fija) en la base de datos usando - JSON o columna por campo? Además, si se implementa el primer modelo, ¿cómo consultar la base de datos como se describe arriba? ¿Debo usar ambos modelos, almacenando todos los datos que pueden ser buscados por una consulta en una fila separada y los otros datos en JSON (es una fila diferente)?

Actualizar

Dado que no habrá demasiadas columnas en las que necesite realizar búsquedas, ¿es aconsejable utilizar ambos modelos? ¿Clave por columna para los datos que necesito buscar y JSON para los demás (en la misma base de datos MySQL)?

Básicamente, el primer modelo que está utilizando se denomina almacenamiento basado en documentos. Debería echar un vistazo a la popular base de datos basada en documentos NoSQL como MongoDB y CouchDB . Básicamente, en db''s basados en documentos, usted almacena datos en archivos json y luego puede consultar en estos archivos json.

El segundo modelo es la popular estructura de base de datos relacional.

Si desea utilizar una base de datos relacional como MySql, le sugiero que solo use el segundo modelo. No tiene sentido utilizar MySql y almacenar datos como en el primer modelo .

Para responder a su segunda pregunta, no hay forma de consultar un nombre como ''foo'' si usa el primer modelo .

Como la mayoría de las cosas "depende". No es correcto o incorrecto / bueno o malo en sí mismo almacenar datos en columnas o JSON. Depende de lo que tengas que hacer después. ¿Cuál es tu forma de acceder a esta información? ¿Necesitarás cruzar otros datos de referencia?

Otras personas han respondido bastante bien cuáles son las desventajas técnicas.

No muchas personas han discutido que su aplicación y características evolucionan con el tiempo y cómo esta decisión de almacenamiento de datos afecta a su equipo.

Debido a que una de las tentaciones del uso de JSON es evitar el esquema de migración y, por lo tanto, si el equipo no es disciplinado, es muy fácil agregar otro par de clave / valor en un campo JSON. No hay migración para eso, nadie recuerda para qué sirve. No hay validación en eso.

Mi equipo usó JSON junto a columnas tradicionales en postgres y al principio fue lo mejor desde el pan rebanado. JSON era atractivo y poderoso, hasta que un día nos dimos cuenta de que la flexibilidad tenía un costo y de repente es un verdadero punto de dolor. A veces ese punto se incrementa muy rápido y luego se vuelve difícil cambiar porque hemos construido muchas otras cosas además de esta decisión de diseño.

En horas extras, al agregar nuevas funciones, tener los datos en JSON llevó a consultas de aspecto más complicado de lo que podría haber sido agregado si nos aferramos a las columnas tradicionales. Entonces comenzamos a recuperar ciertos valores clave en columnas para poder hacer uniones y hacer comparaciones entre valores. Mala idea. Ahora tuvimos duplicación. ¿Un nuevo desarrollador se uniría y estaría confundido? ¿Cuál es el valor en el que debería guardar de nuevo? ¿El JSON uno o la columna?

Los campos JSON se convirtieron en cajones de basura para pequeñas piezas de esto y aquello. Sin validación de datos en el nivel de la base de datos, sin coherencia o integridad entre los documentos. Eso llevó toda esa responsabilidad a la aplicación en lugar de obtener el tipo duro y la comprobación de restricciones de las columnas tradicionales.

Mirando hacia atrás, JSON nos permitió iterar muy rápido y sacar algo de la puerta. Fue grandioso. Sin embargo, después de alcanzar cierto tamaño de equipo, su flexibilidad también nos permitió colgarnos con una larga cuerda de deuda técnica que luego ralentizó el posterior progreso de la evolución de las características. Usar con precaución.

Piensa largo y tendido sobre cuál es la naturaleza de tus datos. Es la base de tu aplicación. ¿Cómo se usarán los datos a lo largo del tiempo? ¿Y cómo es probable CAMBIAR?

Como otros han señalado, las consultas serán más lentas. Sugeriría agregar al menos una columna ''_ID'' para consultar por eso.

Está tratando de adaptar un modelo no relacional a una base de datos relacional, creo que sería mejor utilizar una base de datos NoSQL como MongoDB . No hay un esquema predefinido que se ajuste a su requisito de no tener ninguna limitación en el número de campos (consulte el ejemplo típico de la colección MongoDB). Consulte la documentación de MongoDB para tener una idea de cómo consultar sus documentos, por ej.

db.mycollection.find( { name: ''sann'' } )

Parece que estás dudando sobre si usar un modelo relacional o no.

Tal como están las cosas, su ejemplo se ajustaría razonablemente bien a un modelo relacional, pero el problema puede surgir cuando necesite hacer evolucionar este modelo.

Si solo tiene uno (o algunos niveles de atributos predeterminados) para su entidad principal (usuario), podría seguir utilizando un modelo de Valor de atributo de entidad (EAV) en una base de datos relacional. (Esto también tiene sus pros y sus contras)

Si anticipa que obtendrá valores menos estructurados que querrá buscar utilizando su aplicación, es posible que MySQL no sea la mejor opción aquí.

Si estuviera usando PostgreSQL, podría obtener lo mejor de ambos mundos. (Esto realmente depende de la estructura real de los datos aquí ... MySQL tampoco es necesariamente la opción incorrecta, y las opciones de NoSQL pueden ser de interés, solo estoy sugiriendo alternativas.)

De hecho, PostgreSQL puede construir un índice sobre funciones (inmutables) (que MySQL no puede hasta donde yo sé) y en versiones recientes, podría usar PLV8 en los datos JSON directamente para construir índices sobre elementos específicos de interés de JSON, lo que mejoraría la velocidad de sus consultas cuando busca esos datos.

EDITAR:

Dado que no habrá demasiadas columnas en las que necesite realizar búsquedas, ¿es aconsejable usar ambos modelos? ¿Clave por columna para los datos que necesito buscar y JSON para los demás (en la misma base de datos MySQL)?

Mezclar los dos modelos no es necesariamente incorrecto (suponiendo que el espacio extra es insignificante), pero puede causar problemas si no se asegura de que los dos conjuntos de datos se mantengan sincronizados: su aplicación nunca debe cambiar uno sin actualizar también el otro .

Una buena forma de lograr esto sería hacer que un desencadenador realice la actualización automática, ejecutando un procedimiento almacenado dentro del servidor de la base de datos siempre que se realice una actualización o una inserción. Por lo que sé, el lenguaje de procedimientos almacenados de MySQL probablemente carezca de soporte para cualquier tipo de procesamiento JSON. De nuevo, PostgreSQL con soporte PLV8 (y posiblemente otros RDBMS con lenguajes de procedimientos almacenados más flexibles) debería ser más útil (actualizar su columna relacional automáticamente usando un desencadenador es bastante similar a actualizar un índice de la misma manera).

Solo lanzándolo, pero WordPress tiene una estructura para este tipo de cosas (al menos WordPress fue el primer lugar donde lo observé, probablemente se originó en otro lugar).

Permite claves ilimitadas, y es más rápido de buscar que utilizando un blob JSON, pero no tan rápido como algunas de las soluciones NoSQL.

uid | meta_key | meta_val ---------------------------------- 1 name Frank 1 age 12 2 name Jeremiah 3 fav_food pizza .................

EDITAR

Para almacenar historial / claves múltiples

uid | meta_id | meta_key | meta_val ---------------------------------------------------- 1 1 name Frank 1 2 name John 1 3 age 12 2 4 name Jeremiah 3 5 fav_food pizza .................

y consultar a través de algo como esto:

select meta_val from `table` where meta_key = ''name'' and uid = 1 order by meta_id desc

algún tiempo se unirá a la mesa será una sobrecarga. digamos para OLAP. si tengo dos tablas, una es la tabla ÓRDENES y la otra es ORDER_DETAILS. Para obtener todos los detalles de la orden, tenemos que unir dos tablas, esto hará que la consulta sea más lenta cuando no haya filas en las tablas, digamos en millones más o menos. La unión izquierda / derecha es demasiado lenta como la unión interna. Creo que si agregamos cadena JSON / Objeto en la entrada respectiva de ÓRDENES se evitará la UNIÓN. agregar generación de informes será más rápido ...

el inconveniente del enfoque es exactamente lo que usted mencionó:

hace que sea MUY lento para encontrar cosas, ya que cada vez que necesita realizar una búsqueda de texto en él.

el valor por columna en su lugar coincide con la cadena completa.

Su enfoque (datos basados en JSON) está bien para los datos que no necesita buscar, y solo necesita mostrar junto con sus datos normales.

Editar: solo para aclarar, lo anterior se aplica a las bases de datos relacionales clásicas. NoSQL utiliza JSON internamente, y es probable que sea una mejor opción si ese es el comportamiento deseado.