¿Por qué el mayor desplazamiento LIMIT de MYSQL ralentiza la consulta?
Escenario en resumen: una tabla con más de 16 millones de registros [2 GB de tamaño]. Cuanto mayor sea el desplazamiento de LIMIT con SELECT, más lenta se vuelve la consulta, cuando se usa ORDER BY *primary_key*
Entonces
SELECT * FROM large ORDER BY `id` LIMIT 0, 30
toma mucho menos que
SELECT * FROM large ORDER BY `id` LIMIT 10000, 30
Eso solo pide 30 registros y lo mismo de todos modos. Entonces no son los gastos generales de ORDER BY.
Ahora, para recuperar las últimas 30 filas, se necesitan alrededor de 180 segundos. ¿Cómo puedo optimizar esa simple consulta?
Yo mismo tuve exactamente el mismo problema. Dado el hecho de que desea recopilar una gran cantidad de estos datos y no un conjunto específico de 30, probablemente ejecutará un bucle e incrementará el desplazamiento en 30.
Entonces, lo que puedes hacer en su lugar es:
- Mantenga la última identificación de un conjunto de datos (30) (por ejemplo, lastId = 530)
- Añade la condición
WHERE id > lastId limit 0,30
Así siempre podrás tener una compensación CERO. Te sorprenderá la mejora del rendimiento.
Es normal que los desplazamientos más altos ralenticen la consulta, ya que la consulta necesita contar los primeros OFFSET + LIMITregistros (y tomar solo LIMITellos). Cuanto mayor sea este valor, más tiempo se ejecutará la consulta.
La consulta no puede ir directamente OFFSETporque, en primer lugar, los registros pueden tener diferente longitud y, en segundo lugar, puede haber espacios en los registros eliminados. Necesita verificar y contar cada registro en su camino.
Suponiendo que idsea la clave principal de una tabla MyISAM, o un campo de clave no principal único en una tabla InnoDB, puedes acelerarlo usando este truco:
SELECT t.*
FROM (
SELECT id
FROM mytable
ORDER BY
id
LIMIT 10000, 30
) q
JOIN mytable t
ON t.id = q.id
Vea este artículo:
- Rendimiento de MySQL ORDER BY / LIMIT: búsquedas tardías de filas
