¿Por qué MYSQL higher LIMIT offset ralentiza la consulta?

Escenario en resumen: Una tabla con más de 16 millones de registros [2GB de tamaño]. Cuanto mayor sea el desplazamiento de LÍMITE con SELECT, más lenta será la consulta cuando se use ORDER BY * primary_key *

So 

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

Toma mucho menos que 

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

Que solo ordena 30 registros y el mismo camino. Así que no es la sobrecarga de ORDEN POR.
Ahora, al obtener las últimas 30 filas, toma alrededor de 180 segundos. ¿Cómo puedo optimizar esa simple consulta?

Author: Brendan Long, 2010-12-19
Source

5 answers

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

La consulta no puede ir directamente a OFFSET porque, en primer lugar, los registros pueden ser de diferente longitud, y, en segundo lugar, puede haber huecos de registros eliminados. Necesita verificar y contar cada registro en su camino.

Suponiendo que id es un PRIMARY KEY de una tabla MyISAM, puede 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

Ver este artículo:

 142
Author: Quassnoi,
Warning: date(): Invalid date.timezone value 'Europe/Kyiv', we selected the timezone 'UTC' for now. in /var/www/agent_stack/data/www/ajaxhispano.com/template/agent.layouts/content.php on line 61
2010-12-21 18:06:02

Yo tenía 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.

Así que lo que puedes hacer en su lugar es:

  1. Mantener el último id de un conjunto de datos (30) (por ejemplo, lastId = 530)
  2. Añadir la condición WHERE id > lastId limit 0,30

Así que siempre puedes tener un desplazamiento CERO. Usted se sorprenderá por la mejora del rendimiento.

 144
Author: Nikos Kyr,
Warning: date(): Invalid date.timezone value 'Europe/Kyiv', we selected the timezone 'UTC' for now. in /var/www/agent_stack/data/www/ajaxhispano.com/template/agent.layouts/content.php on line 61
2016-08-30 07:59:27

MySQL no puede ir directamente al registro 10000 (o al byte 80000 como sugiere) porque no puede asumir que está empaquetado/ordenado de esa manera (o que tiene valores continuos de 1 a 10000). Aunque podría ser de esa manera en la actualidad, MySQL no puede asumir que no hay agujeros/huecos/identificadores eliminados.

Por lo tanto, como bobs señaló, MySQL tendrá que obtener 10000 filas (o atravesar las 10000 entradas del índice en id) antes de encontrar las 30 para devolver.

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 devolvería los mismos resultados siempre que no falten id s (es decir, lagunas).

 15
Author: Riedsio,
Warning: date(): Invalid date.timezone value 'Europe/Kyiv', we selected the timezone 'UTC' for now. in /var/www/agent_stack/data/www/ajaxhispano.com/template/agent.layouts/content.php on line 61
2010-12-27 18:14:08

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 alguna optimización se puede hacer mi el 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 califican, y luego ordenar los datos, y finalmente obtener el 30 filas.

También considere el caso en el que las filas no se procesan en el ORDEN POR secuencia. Todas las filas calificadas deben ordenarse para determinar qué filas devolver.

 5
Author: bobs,
Warning: date(): Invalid date.timezone value 'Europe/Kyiv', we selected the timezone 'UTC' for now. in /var/www/agent_stack/data/www/ajaxhispano.com/template/agent.layouts/content.php on line 61
2010-12-19 03:28:53

He encontrado un ejemplo interesante para optimizar las consultas SELECT ORDER BY id LIMIT X,Y. Tengo 35million de filas por lo que tomó como 2 minutos para encontrar un rango de filas.

Aquí está el truco :

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

Basta con poner el DÓNDE con el último id que tienes aumentar 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

 3
Author: sym,
Warning: date(): Invalid date.timezone value 'Europe/Kyiv', we selected the timezone 'UTC' for now. in /var/www/agent_stack/data/www/ajaxhispano.com/template/agent.layouts/content.php on line 61
2015-10-01 17:04:18