Entradas en "sql"
Tamaño de las tablas en BBDD
Pequeña consulta útil para controlar el tamaño de las tablas en MySQL.
SELECT table_name AS "Tables", round(((data_length + index_length)/1024/1024),2) "Size en MiBs" FROM information_schema.TABLES WHERE table_schema = "BD_NAME" ORDER BY (data_length + index_length) DESC;Leer más
Sé lo que estás mirando
Aquí el va el primer problema de algunos que iré proponiendo. Yo publicaré mi solución en unos días (4… o 5), dejando así un tiempo prudente ;]
/* si sois hábiles, no debería llevaros más que unos pocos minutos; pero en cualquier caso la velocidad no significa nada, lo importante es llegar a una buena solución; -es sobre todo un ejercicio mental, de diseño – */
Es muy sencillo pero, como nota, os diré que cuando vi la implementación de esto mismo en un proyecto grande… casi me echo a llorar.
***
Fue una de las primeras cosas que tuve que resolver hace algunos años para LoG85. Se trata de diseñar un fragmento del esquema de una base de datos para guardar, para cada usuario, qué publicaciones ha visitado y cuáles no. Las publicaciones pueden ser fotos, entradas, comentarios… lo que queráis, es lo de menos.
De partida tenemos una tabla usuarios y otra publicaciones, con los campos y restricciones que queráis; tenéis que conseguir que en la base de datos se guarden qué publicaciones han sido visitadas por qué usuarios. Tenéis libertad absoluta para crear o reformar tablas, escribir código, diseñar cachés… la película que queráis. Lo importante es que el sistema tiene que ser elegante (sencillo, claro, eficiente) y escalable (el número de usuarios y de publicaciones, así como su relación, puede crecer ad infinitum). Y por supuesto en todo momento la bd ha de encontrarse en un estado coherente; manteniendo la integridad referencial.
Si dais la solución en prosa, aseguraos de que todo está bien especificado 😉
*** SOLUCIÓN ***
¡Gracias a todos los que habéis participado! Bien vía comentarios, bien vía email o teléfono. Comento un poco las respuestas:
Todas las soluciones se han centrado en crear una tabla que representa la relación entre publicaciones y usuarios; las entradas de esta tabla nueva guardan qué usuarios han visitado qué publicación. Han habido diferentes versiones en las que se guardaban fechas, número de visitas, etc. Pero en cualquier caso, la versión básica de esta tabla, que sólo contendría dos claves ajenas a las primarias de las tablas usuarios y publicaciones, valdría (la primaria de la nueva tabla sería una composición de las dos ajenas).
Esta es la solución “trivial” que funciona. Pero no cumple uno de los objetivos del enunciado; no es escalable.
Primero, hablaremos de volumen; en un proyecto mediano que tenga unos 20.000 usuarios, es fácil que en pocos meses esos usuarios hayan visto una media de 1.000 publicaciones (fotos, tweets, entradas… lo que sea). Esto significa que nuestra tabla auxiliar tendrá 20.000.000 de filas. Y lo que es peor, crece geométricamente conforme aumenta el número de publicaciones y usuarios; a n*m. En pocos años tendremos una tabla con varios cientos de millones de filas, sólo para almacenar quién ha visto qué.
Aunque SGBD’s grandes tipo MySQL, MariaDB, Oracle… se comportan relativamente bien con tablas grandes, también tenemos que contemplar la posibilidad de que nuestra aplicación pueda correr sobre sistemas de gestión de bases de datos más modestos como SQLite. No sólo eso, para tener una velocidad aceptable es imprescindible tener los segmentos de las tablas más utilizados cacheados en RAM; con tablas tan grandes estamos invalidando la caché casi en cada consulta; llegado cierto punto límite en el que la tabla no quepa en memoria el rendimiento bajará de golpe varios órdenes de magnitud. Un diseño como este puede ser la diferencia entre que una aplicación vaya como un rayo en un hosting compartido o requiera un servidor dedicado entero para medio-funcionar. Y por supuesto, no podríamos ni plantearnos usar un sistema de instancias virtuales tipo Amazon, donde pagamos por consumo de memoria y ciclos de CPU.
La información mínima:
Este problema es peligroso porque es trivial encontrar una solución que “funciona”. Es fácil comformarse. Funciona en un “programa juguete” de estar por casa; pero muere en un entorno real. Tampoco hay una solución directa, dado que la información que tenemos que almacenar es esta y aparentemente no podemos reducirla más.
¿No podemos?
En realidad sí, y aquí está la magia.
Mi solución es usar una tabla “views” para las relaciones de esta forma:
| id_user | secuence_start | secuence_end |
|---|---|---|
| 34 | 5 | 5 |
| 34 | 7 | 10 |
| 34 | 12 | 12 |
| 36 | 20 | 28 |
| 785 | 5 | 5 |
La idea es la siguiente: lo más probable que es un usuario visualice muchas publicaciones con ids consecutivos (fotos de un album, respuestas de una entrada, entradas consecutivas, etc). Aún en el caso de que por la naturaleza de nuestra aplicación esta estadística no fuera cierta, más adelante veremos como forzar a que así sea.
Sabiendo esto, podemos explotar esta característica para almacenar segmentos de ids visitados, con un inicio y un final. Es decir, guardamos cosas como “el usuario 34 ha visitado todas las publicaciones de la 7 a la 10”.
A esto, añadimos un pequeño algoritmo de inserción, el consumo de CPU en la inserción es despreciable frente al consumo de CPU que supone consultas en una tabla cargada; además la inserción será una operación poco habitual.
Consulta para saber si una publicación ha sido visitada:
select true from views where id_user=$id_user and
secuence_start<=$id_post and secuence_end>=$id_post;
Algoritmo de inserción:
1) Comprobar si la publicación ya ha sido leída con la consulta anterior. Si es así, terminamos aquí.
2) Ejecutar:
update views set secuence_start=$id_post where
id_user=$id_user and secuence_start=$id_post+1;
Es decir, si queremos insertar como vista la publicación 6, y tenemos un segmento que empieza en la 7 para ese usuario, simplemente cambiamos el 7 por un 6.
El driver nos devuelve el número de filas cambiadas después de la operación, si es uno (se ha encontrado un segmento que cambiar), vamos al paso 2A. Si es cero, saltamos al 3.
2A) Comprobamos si el nuevo inicio de segmento es inmediatamente posterior a un final de segmento. Esto implica fusionar y desfragmentar segmentos que deben ser uno solo.
select secuence_start from views where id_user=$id_user
and secuence_end=$id_post-1;
Guardamos el resultado en una variable $tmp. Si $tmp tiene valor (hay resultado), actualizamos el segmento siguiente y eliminamos el que quedará con información redundante:
update views set secuence_start=$tmp where id_user=$id_user and secuence_start=$id_post; delete from views where id_user=$id_user and secuence_start=$tmp and secuence_end=$id_post-1;
Hemos terminado nuestra inserción y fusión 🙂
3) Ejecutar:
update views set secuence_end=$id_post where
id_user=$id_user and secuence_end=$id_post-1;
Es lo mismo que la operación anterior, pero para los finales de segmento. Preguntamos al driver el número de filas modificadas. Si es uno, pasamos a 3A, si no a 4.
3A) Repetimos las operaciones en 2A por el mismo motivo, adaptadas a una inserción de final de segmento.
select secuence_end from views where id_user=$id_user and secuence_start=$id_post+1; --guardado en variable tmp update views set secuence_end=$tmp where id_user=$id_user and secuence_end=$id_post; delete from views where id_user=$id_user and secuence_start=$id_post+1 and secuence_end=$tmp;
Terminada la inserción y fusión.
4) Llegados a este punto, no hay ningún segmento para el que nuestra entrada vaya a formar parte.
Ejecutamos:
insert into views values($id_user,$id_post,$id_post);
Y fin.
En cuanto pueda os pondré números reales de lo que supone realmente este algoritmo en rendimiento y reducción de tablas; basándome en la bd de log85 😉
¿Cómo llegué a esta solución?
Esto es lo más importante; la verdadera lección de este problema. A menudo tenemos que trabajar con cantidades enormes de información que no podemos comprimir en base a su grado de entropía usando métodos tradicionales.
Pero, como mentes inteligentes y cumbres del pensamiento abstracto que somos (nos queremos), debemos aprovechar nuestra condición humana para encontrar patrones y características que vuelvan a esa información vulnerable. Ya no en almacenamiento; en procesamiento cualquier “información sobre la información” que nos restrinja de una entrada de palabras aleatorias puede marcar la diferencia entre encontrar un algoritmo que se ejecute en 100ms, o tener que usar otro “default” que necesitará, literalmente, varios cientos años para darnos una respuesta.
Esta es sólo una idea. Puede mejorarse sobre la misma base y forzar la estadística para que los ids de lo que ven los usuarios sean más secuenciales. Por ejemplo; las entradas pueden tener ids como 100, 200, 300, 400… y los comentarios a las entradas como unidades sumadas a la id de la entrada: 101, 102, 103… así al ver entradas y comentarios tenemos un buen segmento asegurado. Esto no significa un límite de 100 comentarios por entradas. Cuando nos quedemos sin ids para la secuencia podremos romperla… al fin y al cabo, lo único importante de un id es que sea único (no necesariamente secuencial). Podéis jugar con esto para reducir la fragmentación entre ids de lo que realmente se ve.
Si lo probáis, veréis como la mejora con esta solución es espectacular. Espero que os haya gustado 🙂
Leer más