¿Qué es más eficiente: múltiples tablas MySQL o una tabla grande?

Guardo varios detalles de usuario en mi base de datos MySQL. Originalmente se configuró en varias tablas, lo que significa que los datos se vinculan con UserIds y se envían a través de llamadas a veces complicadas para visualizar y manipular los datos según sea necesario. Al configurar un nuevo sistema, casi tiene sentido combinar todas estas tablas en una gran tabla de contenido relacionado.

  • ¿Va a ser esto una ayuda o un obstáculo?
  • Consideraciones de velocidad al llamar, actualizar o buscar / manipular?

Aquí hay un ejemplo de algunas de las estructuras de mi tabla:

  • usuarios – UserId, nombre de usuario, correo electrónico, contraseña encriptada, fecha de registro, ip
  • user_details – datos de cookie, nombre, dirección, detalles de contacto, afiliación, datos demográficos
  • user_activity – contribuciones, último en línea, última visualización
  • user_settings – configuración de visualización de perfil
  • user_interests: publicidad de variables orientables
  • user_levels – derechos de acceso
  • user_stats – hits, counties

Editar: He actualizado todas las respuestas hasta ahora, todas tienen elementos que básicamente responden a mi pregunta.

La mayoría de las tablas tienen una relación 1: 1 que fue la razón principal para denormalizarlas.

¿Habrá problemas si la tabla abarca más de 100 columnas cuando es probable que una gran parte de estas células permanezca vacía?

Varias tablas ayudan en las siguientes formas / casos:

(a) si diferentes personas van a desarrollar aplicaciones que involucren diferentes tablas, tiene sentido dividirlas.

(b) Si desea otorgar diferentes tipos de autoridades a diferentes personas para diferentes partes de la recostackción de datos, puede ser más conveniente dividirlas. (Por supuesto, puede ver definir puntos de vista y darles autorización apropiadamente).

(c) Para mover datos a diferentes lugares, especialmente durante el desarrollo, puede tener sentido utilizar tablas que den como resultado tamaños de archivo más pequeños.

(d) Una huella más pequeña puede brindar comodidad mientras desarrolla aplicaciones sobre la recostackción de datos específicos de una sola entidad.

(e) Es una posibilidad: lo que usted pensó como un único valor de datos puede llegar a ser realmente valores múltiples en el futuro. por ejemplo, el límite de crédito es un campo de valor único a partir de ahora. Pero mañana, puede decidir cambiar los valores como (fecha de, fecha, valor de crédito). Las tablas divididas pueden ser útiles ahora.

Mi voto sería para varias tablas, con datos divididos adecuadamente.

Buena suerte.

La combinación de las tablas se denomina desnormalización.

Puede (o no) ayudar a hacer algunas consultas (que hacen un montón de JOIN s) para correr más rápido a expensas de crear un infierno de mantenimiento.

MySQL es capaz de usar solo el método JOIN , es decir NESTED LOOPS .

Esto significa que para cada registro en la tabla de conducción, MySQL ubica un registro coincidente en la tabla impulsada en un bucle.

La localización de un registro es una operación bastante costosa que puede tomar docenas de veces más que el escaneo de registro puro.

Mover todos sus registros en una sola tabla lo ayudará a deshacerse de esta operación, pero la tabla en sí misma crece y la exploración de la tabla toma más tiempo.

Si tiene muchos registros en otras tablas, el aumento en el escaneo de la tabla puede sobreponderar los beneficios de los registros que se escanean secuencialmente.

El infierno de mantenimiento, por otro lado, está garantizado.

¿Son todas ellas relaciones 1: 1? Quiero decir, si un usuario puede pertenecer, por ejemplo, a diferentes niveles de usuario, o si los intereses de los usuarios se representan como varios registros en la tabla de intereses del usuario, entonces la fusión de esas tablas estaría fuera de cuestión inmediatamente.

En cuanto a las respuestas anteriores sobre la normalización, debe decirse que las reglas de normalización de la base de datos han ignorado por completo el rendimiento, y solo están mirando qué es un diseño limpio de la base de datos. Eso es a menudo lo que quiere lograr, pero hay momentos en los que tiene sentido desnormalizarse activamente en busca del rendimiento.

Con todo, diría que la pregunta se reduce a cuántos campos hay en las tablas y con qué frecuencia se accede a ellos. Si la actividad del usuario a menudo no es muy interesante, puede ser una molestia tenerla siempre en el mismo registro, por razones de rendimiento y mantenimiento. Si se accede a algunos datos, como la configuración, por ejemplo, con demasiada frecuencia, pero simplemente contiene demasiados campos, puede que tampoco sea conveniente fusionar las tablas. Si solo está interesado en la ganancia de rendimiento, puede considerar otros enfoques, como mantener la configuración por separado, pero guardarlos en una variable de sesión propia para que no tenga que consultar la base de datos con mucha frecuencia.

¿ Todas esas tablas tienen una relación de 1-to-1 ? Por ejemplo, ¿cada fila de usuarios solo tendrá una fila correspondiente en user_stats o user_levels ? Si es así, podría tener sentido combinarlos en una sola tabla. Sin embargo, si la relación no es de 1 to 1 , probablemente no tenga sentido combinarlos (desnormalizarlos).

Sin embargo, tenerlos en tablas separadas frente a una tabla probablemente tendrá poco efecto en el rendimiento a menos que tenga cientos de miles o millones de registros de usuarios. La única ganancia real que obtendrá es simplificar sus consultas combinándolas.

ETA:

Si su preocupación es tener demasiadas columnas , piense en qué cosas suele usar juntas y combínelas , dejando el rest en una tabla separada (o varias tablas separadas si es necesario).

Si miras la forma en que usas los datos, supongo que encontrarás que algo así como el 80% de tus consultas usan el 20% de esos datos, y el 80% restante de los datos se usa solo ocasionalmente. Combine ese 20% frecuentemente utilizado en una sola tabla, y deje el 80% que no suele usar en tablas separadas y probablemente tenga un buen compromiso.

¿Por qué no utilizar el mismo enfoque que WordPress al tener una tabla de usuarios con información básica del usuario que todos tienen y luego agregar una tabla “user_meta” que básicamente puede ser cualquier clave, par de valores asociados con la identificación de usuario. Entonces, si necesita encontrar toda la metainformación para el usuario, puede agregarla a su consulta. Además, no siempre tendrá que agregar la consulta adicional si no es necesario para iniciar sesión. El beneficio de este enfoque también deja su mesa abierta para agregar nuevas características a sus usuarios, como almacenar su identificador de Twitter o cada interés individual. Tampoco tendrá que lidiar con un laberinto de identificaciones asociadas porque tiene una tabla que gobierna todos los metadatos y la limitará a una sola asociación en lugar de a 50.

WordPress específicamente hace esto para permitir que las características se agreguen a través de complementos, lo que permite que su proyecto sea más escalable y no requerirá una revisión completa de la base de datos si necesita agregar una nueva característica.

Crear una tabla masiva va en contra de los principios de las bases de datos relacionales. No los combinaría en una sola tabla. Obtendrá múltiples instancias de datos repetidos. Si su usuario tiene tres intereses, por ejemplo, tendrá 3 filas, con los mismos datos de usuario solo para almacenar los tres intereses diferentes. Definitivamente opta por el enfoque de tabla múltiple ‘normalizado’. Consulte esta página Wiki para la normalización de la base de datos.

Editar: He actualizado mi respuesta, ya que ha actualizado su pregunta … Estoy de acuerdo con mi respuesta inicial aún más desde …

una gran parte de estas células es probable que permanezcan vacías

Si, por ejemplo, un usuario no tiene intereses, si se normaliza, entonces simplemente no tendrá una fila en la tabla de intereses para ese usuario. Si tiene todo en una mesa masiva, tendrá columnas (y aparentemente muchas de ellas) que solo contienen NULL.

He trabajado para una empresa de telefonía donde ha habido toneladas de tablas, obtener datos podría requerir muchas combinaciones. Cuando el rendimiento de la lectura de estas tablas era crítico, se creaban procedimientos que podían generar una tabla plana (es decir, una tabla desnormalizada) que no requería uniones, cálculos, etc. a los que los informes pudieran apuntar. Estos se usaron luego junto con un agente de servidor SQL para ejecutar el trabajo a ciertos intervalos (es decir, una vista semanal de algunas estadísticas se ejecutaría una vez por semana, etc.).

Creo que esta es una de esas situaciones de “depende”. Tener múltiples tablas es más limpio y probablemente teóricamente mejor. Pero cuando tiene que unirse a 6-7 tablas para obtener información sobre un solo usuario, puede comenzar a replantearse ese enfoque.

Yo diría que depende de lo que realmente significan las otras tablas. ¿Un user_details contiene más de 1 más / usuarios y así sucesivamente? El nivel de normalización más adecuado para sus necesidades depende de sus demandas.

Si tiene una tabla con un buen índice, probablemente sea más rápida. Pero, por otro lado, probablemente sea más difícil de mantener.

Para mí, parece que podría saltarse User_Details ya que probablemente sea una relación de 1 a 1 con los usuarios. ¿Pero el rest probablemente sean muchas filas por usuario?