AllYouNeedIsBackend

Soy un gran fan de las bases de datos. Incluso quise hacer mi propio DBMS cuando estaba en la universidad. Ahora trabajo tanto con RDBMS como con soluciones NoSQL, y estoy muy entusiasmado con ello. No hay un Martillo de Oro, cada problema tiene su propia solución. O bien, un subconjunto de soluciones.

En la serie de entradas del blog El SQL que me gusta <3 os recorro algunos problemas resueltos con SQL que me han parecido especialmente interesantes. Las soluciones se prueban utilizando una tabla con más de 100 millones de registros. Todos los ejemplos utilizan MySQL, pero las ideas se aplican a otros almacenes de datos relacionales como PostgreSQL, Oracle y SQL Server.

Este capítulo se centra en el escaneo eficiente de una tabla grande utilizando la paginación con offset en la clave primaria. Esto también se conoce como paginación keyset.

Contexto

En el capítulo, utilizamos la siguiente estructura de base de datos como ejemplo. El ejemplo canónico sobre los usuarios debería ajustarse a cualquier dominio.

CREATE TABLE `users` ( `user_id` int(11) unsigned NOT NULL AUTO_INCREMENT, `external_id` varchar(32) NOT NULL, `name` varchar(100) COLLATE utf8_unicode_ci NOT NULL, `metadata` text COLLATE utf8_unicode_ci, `date_created` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP, PRIMARY KEY (`user_id`), UNIQUE KEY `uf_uniq_external_id` (`external_id`), UNIQUE KEY `uf_uniq_name` (`name`), KEY `date_created` (`date_created`)) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;

Algunos comentarios sobre la estructura:

• external_id la columna almacena la referencia al mismo usuario en otro sistema en formato UUID
• name representa Firstname Lastname
• metadata la columna contiene JSON blob con todo tipo de datos no estructurados

La tabla es relativamente grande y contiene alrededor de 100 000 000 registros. Comencemos nuestro viaje de aprendizaje.

Escaneo de una tabla grande

Problema: Necesitas recorrer la tabla, extraer cada registro, transformarlo dentro del código de tu aplicación e insertarlo en otro lugar. Nos centramos en la primera etapa en el post – escanear la tabla.

Solución obvia y errónea

SELECT user_id, external_id, name, metadata, date_createdFROM users;

En mi caso con 100 000 000 registros, la consulta nunca se termina. El SGBD simplemente la mata. ¿Por qué? Probablemente, porque llevó al intento de cargar toda la tabla en la RAM. Antes de devolver los datos al cliente. Otra suposición: se tardó demasiado tiempo en precargar los datos antes de enviarlos y la consulta se agotó. De todos modos, nuestro intento de obtener todos los registros en el tiempo falló. Necesitamos encontrar alguna otra solución.

Solución #2

Podemos intentar obtener los datos en páginas. Dado que los registros no están garantizados para ser ordenado en una tabla en el nivel físico o lógico – tenemos que ordenar en el lado DBMS con ORDER BY cláusula.

SELECT user_id, external_id, name, metadata, date_createdFROM usersORDER BY user_id ASCLIMIT 0, 10 000;10 000 rows in set (0.03 sec)

Sweet. Ha funcionado. Pedimos la primera página de 10 000 registros, y sólo tardó 0.03 segundos en devolverla. Sin embargo, ¿cómo funcionaría para la página 5000?

SELECT user_id, external_id, name, metadata, date_createdFROM usersORDER BY user_id ASCLIMIT 50 000 000, 10 000; --- 5 000th page * 10 000 page size10 000 rows in set (40.81 sec)

De hecho, esto es muy lento. Veamos cuánto tiempo se necesita para obtener los datos de la última página.

SELECT user_id, external_id, name, metadata, date_createdFROM usersORDER BY user_id ASCLIMIT 99 990 000, 10 000; --- 9999th page * 10 000 page size10 000 rows in set (1 min 20.61 sec)

Esto es una locura. Sin embargo, puede estar bien para las soluciones que se ejecutan en el fondo. Un problema más oculto con el enfoque puede ser revelado si usted trata de eliminar un registro de la tabla en el medio de la exploración. Digamos que ha terminado la décima página (ya se han visitado 100 000 registros) y va a escanear los registros entre 100 001 y 110 000. Pero los registros 99 998 y 99 999 se borran antes de la siguiente ejecución de SELECT. En ese caso, la siguiente consulta devuelve el resultado inesperado:

 SELECT user_id, external_id, name, metadata, date_created FROM users ORDER BY user_id ASC LIMIT 100 000, 10 000; N, id, ... 1, 100 003, ... 2, 100 004, ...

Como puede ver, la consulta omitió los registros con los ids 100 001 y 100 002. No serán procesados por el código de la aplicación con el método porque después de las dos operaciones de borrado aparecen en los primeros 100 000 registros. Por lo tanto, el método no es fiable si el conjunto de datos es mutable.

Solución #3 – la definitiva para hoy

El enfoque es muy similar al anterior porque sigue utilizando la paginación, pero ahora en lugar de confiar en el número de registros escaneados, utilizamos el user_id del último registro visitado como el offset.

Algoritmo simplificado:

1. Obtenemos PAGE_SIZE número de registros de la tabla. El valor de offset inicial es 0.
2. Usamos el valor máximo devuelto para user_id en el lote como el offset para la siguiente página.
3. Obtenemos el siguiente lote de los registros que tienen user_id valor superior al actual offset.

La consulta en acción para la página 5 000, cada página contiene datos sobre 10 000 usuarios:

SELECT user_id, external_id, name, metadata, date_createdFROM usersWHERE user_id > 51 234 123 --- value of user_id for 50 000 000th recordORDER BY user_id ASCLIMIT 10 000;10 000 rows in set (0.03 sec)

Nótese, que los valores de user_id no son secuenciales y pueden tener huecos como 25 348 está justo después de 25 345. La solución también funciona si se eliminan los registros de las páginas futuras, incluso en ese caso la consulta no se salta los registros. Genial, ¿verdad?

Explicando el rendimiento

Para seguir aprendiendo, recomiendo investigar los resultados de EXPLAIN EXTENDED para cada versión de la consulta para obtener los siguientes 10 000 registros después de 50 000 000.

Centrémonos en la diferencia clave entre los planes de ejecución de la 2ª y 3ª solución ya que la 1ª no es útil en la práctica para tablas grandes.

• Tipo de join: index vs range. La primera significa que se escanea todo el árbol de índices para encontrar los registros. El tipo range nos dice que el índice se utiliza sólo para encontrar filas coincidentes dentro de un rango especificado. Por lo tanto, el tipo range es más rápido que index.
• Posibles claves: NULL frente a PRIMARY. La columna muestra las claves que pueden ser utilizadas por MySQL. BTW, mirando en la columna de claves, podemos ver que eventualmente PRIMARY clave se utiliza para las dos consultas.
• Filas: 50 010 000 frente a 50 000 000. El valor muestra el número de registros analizados antes de devolver el resultado. Para la 2ª consulta, el valor depende de la profundidad de nuestro scroll. Por ejemplo, si intentamos obtener los siguientes 10 000 registros después de la página 9999 entonces se examinan 99 990 000 registros. Por el contrario, la tercera consulta tiene un valor constante; no importa si cargamos los datos de la primera página o de la última. Siempre es la mitad del tamaño de la tabla.
• Filtrado: 200.00 frente a 100.00. La columna indica el porcentaje estimado de la tabla que hay que filtrar antes de procesarla. El valor más alto es mejor. El valor de 100.00 significa que la consulta busca en toda la tabla. Para la segunda consulta, el valor no es constante y depende del número de página: si pedimos la primera página, el valor de la columna filtrada sería 1000000.00. Para la última página, sería 100.00.
• Extra: NULL vs Using where. Proporciona información adicional sobre cómo MySQL resuelve la consulta. El uso de WHERE en la clave PRIMARY hace que la ejecución de la consulta sea más rápida.

Sospecho que el tipo de join es el parámetro de la consulta que más ha contribuido al rendimiento para que la 3ª consulta sea más rápida. Otra cosa importante es que la 2ª consulta es extremadamente dependiente del número de las páginas a desplazar. Una paginación más profunda es más lenta en ese caso.

Más orientación sobre la comprensión de la salida para el comando EXPLAIN se puede encontrar en la documentación oficial de su RDBMS.

Resumen

El tema principal de la entrada del blog estaba relacionado con el escaneo de una tabla grande con 100 000 000 registros utilizando offset con una clave primaria (paginación keyset). En general, se revisaron 3 enfoques diferentes y se probaron en el conjunto de datos correspondiente. Recomiendo sólo uno de ellos si necesita escanear una tabla grande mutable.

Además, revisamos el uso del comando EXPLAIN EXTENDED para analizar el plan de ejecución de las consultas de MySQL. Estoy seguro de que otros RDBMS tienen análogos para la funcionalidad.

En el próximo capítulo, vamos a prestar atención a la agregación de datos y la optimización del almacenamiento. Estén atentos!

¿Cuál es su método para escanear tablas grandes?

¿Recuerda algún otro propósito de usar la paginación de conjuntos de claves como en la Solución #3?