column - update order by mysql

¿Por qué MYSQL LIMIT offset más alto ralentiza la consulta?

Question

Escenario en resumen: una tabla con más de 16 millones de registros [2 GB de tamaño]. Cuanto mayor es la compensación de LÍMITES con SELECCIONAR, más lenta es la consulta, al usar ORDER BY * primary_key *

Asi que

SELECT * FROM large ORDER BY `id` LIMIT 0, 30

toma mucho menos que

SELECT * FROM large ORDER BY `id` LIMIT 10000, 30

Eso solo ordena 30 registros y lo mismo. Por lo tanto, no es la sobrecarga de ORDER BY.
Ahora, cuando busca las últimas 30 filas, demora alrededor de 180 segundos. ¿Cómo puedo optimizar esa simple consulta?

Answer 1

Encontré un ejemplo interesante para optimizar las consultas SELECT ORDER BY id LIMIT X, Y. Tengo 35 millones de filas, así que tardé 2 minutos en encontrar una variedad de filas.

Aquí está el truco:

select id, name, address, phone
FROM customers
WHERE id > 990
ORDER BY id LIMIT 1000;

Simplemente coloque el DONDE con la última identificación que obtuvo, aumente mucho el rendimiento. Para mí fue de 2 minutos a 1 segundo :)

Otros trucos interesantes aquí: http://www.iheavy.com/2013/06/19/3-ways-to-optimize-for-paging-in-mysql/

Funciona también con cadenas

Answer 2

Es normal que las compensaciones más altas reduzcan la velocidad de la consulta, ya que la consulta necesita contar los primeros registros de OFFSET + LIMIT (y tomar solo el LIMIT de ellos). Cuanto mayor sea este valor, más tiempo se ejecutará la consulta.

La consulta no puede ir a OFFSET porque, en primer lugar, los registros pueden ser de diferente duración y, en segundo lugar, pueden existir espacios vacíos en los registros eliminados. Necesita verificar y contar cada registro en su camino.

Asumiendo que id es una PRIMARY KEY de una tabla MyISAM , puedes acelerarla 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

Ver este artículo:

• Rendimiento de ORDER BY / LIMIT de MySQL: búsquedas tardías de filas

Answer 3

La parte que consume mucho tiempo de las dos consultas es recuperar las filas de la tabla. Lógicamente hablando, en la versión LIMIT 0, 30 , solo se necesitan recuperar 30 filas. En la versión LIMIT 10000, 30 , se evalúan 10000 filas y se devuelven 30 filas. Puede haber algo de optimización en mi proceso de lectura de datos, pero considere lo siguiente:

¿Qué pasaría si tuviera una cláusula WHERE en las consultas? El motor debe devolver todas las filas que califiquen, y luego ordenar los datos, y finalmente obtener las 30 filas.

Considere también el caso en que las filas no se procesan en la secuencia ORDER BY. Todas las filas que califican deben ordenarse para determinar qué filas devolver.

Answer 4

MySQL no puede ir directamente al registro número 10000 (o el 80000º como sugeriría) porque no puede suponer que está empaquetado / ordenado así (o que tiene valores continuos en 1 a 10000). Aunque podría ser de esa manera en la actualidad, MySQL no puede suponer que no hay agujeros / lagunas / identificadores borrados.

Entonces, como notó bobs, MySQL tendrá que buscar 10000 filas (o recorrer 10000 entradas del índice en id ) antes de encontrar el 30 para regresar.

EDITAR : Para ilustrar mi punto

Tenga en cuenta que aunque

SELECT * FROM large ORDER BY id LIMIT 10000, 30

sería lento (er) ,

SELECT * FROM large WHERE id > 10000 ORDER BY id LIMIT 30

sería rápido (er) y arrojaría los mismos resultados siempre que no falten id (es decir, huecos).

Answer 5

Yo mismo tuve exactamente el mismo problema. Dado que desea recopilar una gran cantidad de estos datos y no un conjunto específico de 30, probablemente ejecutará un ciclo e incrementará el desplazamiento en 30.

Entonces, lo que puedes hacer es:

1. Mantener la última identificación de un conjunto de datos (30) (por ejemplo, lastId = 530)
2. Agregue la condición WHERE id > lastId limit 0,30

Por lo tanto, siempre puedes tener un desplazamiento CERO. Te sorprenderá la mejora del rendimiento.