Análisis de rendimiento y optimización del uso de límites de mySQL y consultas paginadas

En primer lugar, el uso del límite

Cuando usamos declaraciones de consulta, a menudo tenemos que devolver las primeras filas de datos o las del medio, ¿qué debemos hacer en este momento? No se preocupe, mysql ya nos proporciona esa función.

SELECT * FROM table LIMIT [offset,] rows | `rows OFFSET offset ` 
(LIMIT offset, `length`)
SELECT
*
FROM table
where condition1 = 0
and condition2 = 0
and condition3 = -1
and condition4 = -1
order by id asc
LIMIT 2000 OFFSET 50000

La cláusula LIMIT se puede usar para obligar a una declaración SELECT a devolver un número específico de registros. LIMIT acepta uno o dos parámetros numéricos. El argumento debe ser una constante entera. Si se dan dos parámetros, el primer parámetro especifica el desplazamiento de la primera fila de registro devuelta, y el segundo parámetro especifica el número máximo de filas de registro devueltas. El desplazamiento de la fila de registro inicial es 0 (no 1): para la compatibilidad con PostgreSQL, MySQL también admite la sintaxis: LIMIT # OFFSET #.

mysql> SELECT * FROM table LIMIT 5,10; 

Para recuperar todas las filas de registros desde un desplazamiento hasta el final del conjunto de registros, puede especificar un segundo parámetro de -1:

mysql> SELECT * FROM table LIMIT 95,-1; 

Si solo se proporciona un argumento, significa que se devolvió el número máximo de filas de registros:
mysql> SELECT * FROM table LIMIT 5; En
otras palabras, LIMIT n es equivalente a LIMIT 0,n.

En segundo lugar, el análisis de rendimiento de las declaraciones de consulta paginadas de Mysql.

La declaración SQL paginada de MySql, si se compara con la sintaxis TOP de MSSQL, entonces la sintaxis LIMIT de MySQL es mucho más elegante. Es natural usarlo para la paginación.

El método de paginación más básico:

SELECT ... FROM ... WHERE ... ORDER BY ... LIMIT ... 

En el caso de volúmenes de datos pequeños y medianos, un SQL de este tipo es suficiente, y el único problema al que hay que prestar atención es asegurarse de que se utiliza el índice:
por ejemplo, si el SQL real es similar a la siguiente declaración, entonces es mejor construir un índice compuesto en las dos columnas category_id, id:

SELECT * FROM articles WHERE category_id = 123 ORDER BY id LIMIT 50, 10

Cómo paginar una subconsulta:

A medida que aumenta la cantidad de datos, también aumentará la cantidad de páginas y el SQL para las próximas páginas puede ser similar:
SELECCIONE * DE artículos DONDE category_id = 123 ORDEN POR id LÍMITE 10000, 10

En resumen, cuanto más retroceda en la página, mayor será el desplazamiento de la instrucción LIMIT y más lento será.
En este momento, podemos mejorar la eficiencia de la paginación mediante subconsultas, más o menos de la siguiente manera:

SELECT * FROM articles WHERE  id >=  
(SELECT id FROM articles  WHERE category_id = 123 ORDER BY id LIMIT 10000, 1) LIMIT 10 

ÚNETE al modo de paginación

SELECT * FROM `content` AS t1   
JOIN (SELECT id FROM `content` ORDER BY id desc LIMIT ".($page-1)*$pagesize.", 1) AS t2   
WHERE t1.id <= t2.id ORDER BY t1.id desc LIMIT $pagesize; 

Después de mis pruebas, la eficiencia de la paginación de unión y la paginación de subconsulta está básicamente al mismo nivel, y el tiempo consumido es básicamente el mismo.
explicar declaración SQL:

id select_type table type possible_keys key key_len ref rows Extra
1 PRIMARY <derived2> system NULL NULL NULL NULL 1  
1 PRIMARY t1 range PRIMARY PRIMARY 4 NULL 6264 Using where
2 DERIVED content index NULL PRIMARY 4 NULL 27085 Using index

¿Porqué es eso? Debido a que la consulta se realiza en el índice y la consulta ordinaria se realiza en el archivo de datos, en general, el archivo de índice es mucho más pequeño que el archivo de datos, por lo que la operación será más eficiente.

De hecho, puede usar un patrón de política similar para lidiar con la paginación, como juzgar que si está dentro de las 100 páginas, use el método de paginación más básico, y si tiene más de 100 páginas, use el método de paginación de las subconsultas.

En tercer lugar, para las tablas mysql con grandes volúmenes de datos, existen serios problemas de rendimiento con la paginación USING LIMIT.

Consulta 30 registros de 1000000 después de:

SELECT * FROM `cdb_posts` ORDER BY pid LIMIT 1000000 , 30

SELECT * FROM `cdb_posts` WHERE pid >= (SELECT pid FROM  
`cdb_posts` ORDER BY pid LIMIT 1000000 , 1) LIMIT 30

Porque a sacar todo el contenido del campo, el primero necesita abarcar una gran cantidad de bloques de datos y eliminarlos, mientras que el segundo básicamente elimina el contenido correspondiente colocándolos directamente de acuerdo con el campo de índice, y la eficiencia es, naturalmente, mucho mejor. La optimización del límite no usa directamente el límite, sino que primero obtiene la identificación de la oferta y luego usa directamente el tamaño del límite para obtener los datos.

Se puede ver que cuanto más atrás esté la página, mayor será el desplazamiento de la declaración LIMIT y más obvia será la diferencia de velocidad entre los dos.

En aplicaciones prácticas, puede usar un patrón de política similar para manejar la paginación, como juzgar que si está dentro de las 100 páginas, use el método de paginación más básico, y si tiene más de 100 páginas, use el método de paginación de las subconsultas.

Idea de optimización: evite escanear demasiados registros cuando el volumen de datos es grande

Para asegurarse de que las columnas de índice de índice sean contiguas, puede agregar un campo de incremento automático a cada tabla y agregar un índice

Cuarto, consulta de paginación, número total de páginas, cómo limitar la implementación

1. Limita la paginación

limitar la paginación: curPage es la página actual; PageSize es cuántos registros por página

select * from student limit (curPage-1)*pageSize , pageSize;

2. Número total de páginas

(1) Número total de páginas: totalRecord es el número total de registros; PageSize es cuántos registros se dividen en una página

int totalPageNum = (totalRecord +pageSize - 1) / pageSize;

2) Número total de consultas: totalRecord es el número total de registros

SELECT COUNT(*) FROM courses

Se el primero en comentar

Deje un comentario

Tu dirección de correo electrónico no será publicada.


*