El mundo de MySQL & MariaDB

LIMIT y OFFSET en MySQL: paginación y rendimiento óptimos

Operadores y cláusulas
目次

1. Uso básico de LIMIT y OFFSET

En MySQL, LIMIT y OFFSET son funciones muy útiles para obtener de manera eficiente un rango específico de datos de la base de datos. Al comprenderlas, puedes obtener solo los datos necesarios de forma eficiente y mejorar el rendimiento de la aplicación. Primero, explicaremos en detalle su uso básico.

Función de la cláusula LIMIT y la cláusula OFFSET

LIMIT restringe la cantidad de datos a obtener. Por otro lado, OFFSET especifica la posición desde la cual comenzar a obtener los datos. Al combinar ambos, es posible extraer solo el rango necesario de datos entre una gran cantidad.

Ejemplo de uso

En el siguiente ejemplo, se obtienen 10 filas a partir de la fila 20.
SELECT * FROM nombre_tabla LIMIT 10 OFFSET 20;
En esta consulta, OFFSET hace que la obtención comience a partir de la fila 20, y LIMIT obtiene 10 filas de datos. Además, si se omite OFFSET, se considera 0, por lo que se obtienen los datos desde el principio según la cantidad especificada.

Comportamiento cuando se omite la cláusula OFFSET

Si se omite OFFSET, se obtienen datos desde el inicio de la tabla según la cantidad especificada por LIMIT. Por ejemplo, la siguiente consulta obtiene las primeras 10 filas de la tabla.
SELECT * FROM nombre_tabla LIMIT 10;

2. Métodos de implementación de paginación

Al manejar grandes cantidades de datos en sitios web o aplicaciones, no es realista mostrar todos los datos a la vez. Por eso, es común implementar la “paginación”, que divide los datos y los muestra página por página. Al aprovechar LIMIT y OFFSET, se pueden obtener diferentes datos en cada página.

Configuración de LIMIT y OFFSET según el número de página

Si se establecen 10 registros por página, la primera página tiene OFFSET 0, la segunda página OFFSET 10, la tercera 20… y así sucesivamente, lo que permite configurar LIMIT y OFFSET según el número de página.

Ejemplo de uso

Consulta de la primera página：
SELECT * FROM nombre de tabla LIMIT 10 OFFSET 0;
Consulta de la segunda página：
SELECT * FROM nombre de tabla LIMIT 10 OFFSET 10;
De esta manera, al variar OFFSET según el número de página, se pueden obtener correctamente los datos de diferentes páginas.

Consideraciones al usar paginación

Al implementar la paginación concode>OFFSET, es importante tener en cuenta que el tiempo de procesamiento de la consulta aumenta a medida que la posición del registro objetivo se encuentra más adelante. Esto se debe a que la base de datos debe leer todas las filas hasta alcanzar el OFFSET.

3. Consideraciones de rendimiento al usar OFFSET

Cuando la cantidad de datos es enorme, el uso excesivo de OFFSET puede degradar significativamente el rendimiento de la consulta. Por ejemplo, al obtener la parte posterior de una tabla que contiene varios millones de filas, la base de datos debe escanear todas las filas hasta alcanzar la posición especificada por OFFSET.

Ejemplo de degradación del rendimiento

Si se obtienen 10 registros a partir de la fila 1,000,000, la base de datos debe omitir las primeras 999,990 filas. Por lo tanto, a medida que el valor de OFFSET aumenta, el tiempo de procesamiento también se incrementa.

Mejora del rendimiento mediante el uso de índices

Al usar LIMIT o OFFSET, establecer índices adecuados puede reducir el tiempo de ejecución de la consulta. En particular, al crear índices sobre las condiciones de búsqueda, la base de datos puede localizar los datos objetivo de manera más eficiente.

4. Método de búsqueda para una paginación eficiente

Cuando la cantidad de datos es grande, el método de búsqueda (paginación por conjunto de claves) que no utiliza OFFSET es eficaz en términos de rendimiento. Con este método, se obtiene la página siguiente basándose en el último registro de la página anterior, por lo que no es necesario realizar un proceso de salto. Es especialmente efectivo con conjuntos de datos a gran escala.

Ejemplo de uso del método de búsqueda

En el método de búsqueda, no se utiliza OFFSET, sino que se obtienen los datos basándose en el valor de la clave, como el id del último registro de la página anterior. Con este método, la base de datos puede recuperar directamente los datos objetivo, lo que mejora la eficiencia a medida que se avanza a páginas posteriores.

Ejemplo

En la siguiente consulta, se obtienen los datos siguientes basándose en el último id obtenido en la página anterior.
SELECT * FROM nombre_tabla WHERE id > ultimo_id ORDER BY id ASC LIMIT 10;
Con este método, el resultado de la consulta siempre comienza desde la última fila de la página anterior, lo que mejora significativamente el rendimiento.

Método de búsqueda con una clave diferente

En algunos casos, es posible realizar la paginación utilizando una columna distinta al id (por ejemplo, una fecha). Cuando se usa una fecha, se pueden obtener los datos posteriores a la fecha especificada, permitiendo avanzar la página de la misma manera. De este modo, el método de búsqueda puede emplearse incluso cuando no se trata de una clave primaria.

5. Mejores prácticas al usar LIMIT y OFFSET

Para usar LIMIT y OFFSET de manera eficiente, existen algunas mejores prácticas. A continuación se enumeran los puntos clave y se explica cómo maximizar el rendimiento.

Aplicar índices

Al usar LIMIT o OFFSET, establecer índices de acuerdo con las condiciones de búsqueda permite que la base de datos realice búsquedas de manera eficiente. En particular, al crear índices en las columnas que se utilizan frecuentemente en la paginación, es posible obtener datos rápidamente incluso con grandes volúmenes.

Basarse en la clave primaria

Al utilizar la clave primaria para obtener datos en la paginación, los índices funcionan de manera eficiente. Esto permite que la base de datos procese la consulta rápidamente.

Utilizar el método de búsqueda (seek)

Cuando la cantidad de datos es grande, usar el método de búsqueda en lugar de OFFSET mantiene el rendimiento incluso en las páginas posteriores. Es una técnica especialmente eficaz al manejar grandes volúmenes de datos.

6. Resumen

Los LIMIT y OFFSET de MySQL son herramientas poderosas para obtener datos de manera eficiente. Sin embargo, según la cantidad de datos y el método de obtención, también existe el riesgo de una disminución del rendimiento. Es importante mantener el rendimiento y al mismo tiempo implementar la paginación de forma eficiente mediante una configuración adecuada de índices y el uso de técnicas de búsqueda. Cuando se manejan grandes volúmenes de datos, conviene considerar la adopción de técnicas de búsqueda.