Organizar registros en tablas

[xinxan22]

Buenas tardes,

me gustaría saber vuestra opinión respecto a la "organización" de tablas de una base de datos.


Tengo 100.000 registros diarios. Como sería la mejor forma, eficiente y veloz, para guardar esos registros en tablas?

1.- Una para cada día?

tabla_2013_07_09
tabla_2013_07_10

2.- Una para cada mes?

tabla_2013_07
tabla_2013_08

3.- Una para cada año?

tabla_2013
tabla_2014

4.- Una para todo?

tabla

5.- Alguna otra opción?


Al día son 100.000 registros aproximadamente.

Al mes 3.000.000 registros aproximadamente.

Al año 36.000.000 registros aproximadamente.

No es lo mismo hacer un SELECT de 100.000 registros que de 36.000.000 de registros.

Que opinan?

Saludos

[gnzsoloyo]

1: NO.
2: NO.
3: NO.
4: Depende. habría que analizar el sistema, la estructura de la base, y las necesidades de las aplicaciones.

Las tres primeras opciones son un error de fundamentos de BBDD. Ni siquiera son opciones para tener en cuenta.
Los problemas de diseño de una BBDD no se restringen a considerar solamente la cantidad de registros de una tabla. Eso no es un problema de diseño, sino en todo caso de implementación y performance.
En una de las bases de datos que administré, había tablas que almacenaban más de 70 millones de registros, y de todos modos era eficiente. En otro caso que hoy manejo, una tabla registra 36 millones de registros diarios, y ni siquiera pestañea.
Pero lo esencial es que las bases están bien diseñadas, y la tabla es sólo parte del sistema...

Explicanos como es la estructura, el tipo de datos y veremos.

No es lo mismo hacer un SELECT de 100.000 registros que de 36.000.000 de registros.

Error. Una consulta en una base no optimizada, puee tardar más en revisar 3.000 registros que en analizar 3.000.000.
Y con una consulta sin optimización, puede tardar más en hacer un JOIN entre dos tablas de 1000 registros cada una, que en verificar una tabla con 145 millones de registros en un join con otra de 15 millones.

No es la cantidad de registros, sino la optimización lo que cuenta. El sólo tener un indice bien definido para un caso dado, puede reducir el tiempo de una consulta de 2:48 horas, a sólo siete segundos. O inclusive un proceso que toma 5 días, llevarlo a ejecutarse en 12 minutos (y no exagero, lo hicimos a principios de este año), sólo por cambiar dos parámetros en el WHERE.

[xinxan22]

Gracias por responder.

Las pruebas que estoy realizando es una sola tabla en una base de datos. No hay consultas externas, no hay aplicativos externos, no hay página web, nada. Solo pruebas mías en local.

Si hago un SELECT en esa tabla con 100.000 registros o con 36.000.000 el tiempo es diferente.

La tabla tiene 40 campos.
Un id clave principal autonumérica.
Dos campos indexados (uno de fecha para buscar por día y otro con un id especial para buscar)

Los datos almacenados son de varios tipos:

integer, smallint, datetime, varchar(255).

El SELECT es coger varios campos de esa tabla con un WHERE id_indexado='valor' AND fecha>='dia_inicio' AND fecha<='dia_fin';



La base de datos tendrá variedad de tablas con aplicativos externos que insertarán, eliminarán y actualizarán la información en cosa de segundos.

El resto de tablas serán más o menos fijas en registros (tendrán entre 1.000 y 100.000 registros fijos) de las cuales puede ser que se acceda cada 5 minutos o solo una vez al día.

La duda que se me plantea es, como guardo esa información que sé que cada día tendré (los 100.000 registros) para después poderla consultar y utilizar.

Me he encontrado a veces que para eliminar información de una tabla de grandes cantidades de registros tarda varios minutos.
Otros casos, es que cuando he realizado una copia de seguridad a parte del tiempo, problemas para restaurarla.

[leonardo_josue]

Hola xinxan22:

Si hago un SELECT en esa tabla con 100.000 registros o con 36.000.000 el tiempo es diferente.

Efectivamente, puede haber diferencias en cuanto a los tiempos, pero hay que ver si esta diferencia es realmente significativa... la consulta tal como lo tienes en realidad no se puede optimizar mucho que digamos... podrías intentar utilizar la función BETWEEN en la condición de las fechas, pero en realidad no esperaría una variación significativa.

Aquí tendrías qué evaluar dos cosas:

1. ¿La diferencia de tiempos es realmente considerable? es decir, si estamos hablando de una diferencia de milésimas de segundos, o inclusive de algunos pocos segundos entonces es posible que la definición de los índices no sea la adecuada, para eso tendrías que hacer un SHOW CREATE TABLE para ver cómo los tienes definidos y de quizás plantear alguna otra alternativa distinta.

2. ¿Qué tanto impacto tiene en tus procesos esta variación en tiempos? es decir, cuál es tu costo/beneficio de esta variación.

Haciendo una analogía con el deporte, para un corredor de 100 metros planos una variación de una décima de segundo a la llegada a la meta con respecto al puntero puede implicar la pérdida de una medalla de oro. Sin embargo, para un ciclista de la Tour de Francia, perder en una carrera varios minutos con respecto a los punteros puede no afectar en absoluto el resultado de la carrera.

Lo mismo pasa con las BD's. Si los tiempos de ejecución de tus consultas afectan realmente el desarrollo de otras actividades, entonces podrías implementar algún tipo de técnica con tablas separadas, aunque la teoría de BD's te diga que no es lo mejor... Si en realidad la ejecución de las consultas no implica un riesgo en tus procesos, entonces has caso a la teoría y mantén tu información en una sola tabla.

En esto del diseño de BD no todo está escrito, no estamos hablando de una fórmula matemática o de una ciencia exacta... si bien soy de la misma opinión de gnzsoloyo acerca de que no es conveniente que tengas información en tablas separadas (que la teoría te diga que esta es una mala decisión), en la práctica puedes optar por alguna alternativa como estas...

Otro punto a considerar es también el tipo de consultas que vas a hacer... si la información que vas a consultar o comparar corresponde a más de un día, o de un año, si vas a comparar la información de varios días a la vez, con un sistema de tablas separadas tendrías que implementar estrategias como el uso de UNION's o JOIN's con lo cual podría a final de cuentas complicar de más la consulta, lo que terminaría por hacerlas más tardadas.

Si por el contrario las consultas son siempre referentes a un día en específico, a una semana en específico o a una año en específico e insistiendo en que tengas tiempos de respuesta realmente significativos, entonces si podrías pensar en una opción con tablas separadas.

Insisto, no hay una "mejor" forma de hacer las cosas... aquí tu función como DBA es evaluar todos estos escenarios y hacer un análisis de riesgos, costos y beneficios y elegir la "menos mala".

Saludos
Leo.