mysql - tipos - sentencia top sql

¿Cómo puedo acelerar una consulta MySQL con una compensación grande en la cláusula LIMIT?

Question

Obtengo problemas de rendimiento al LIMIT un mysql SELECT con un desplazamiento grande:

SELECT * FROM table LIMIT m, n;

Si el desplazamiento m es, digamos, mayor que 1,000,000, la operación es muy lenta.

Tengo que usar el limit m, n ; No puedo usar algo como id > 1,000,000 limit n .

¿Cómo puedo optimizar esta declaración para un mejor rendimiento?

Answer 1

Hay una publicación de blog en algún lugar de Internet sobre cómo hacer que la selección de las filas para mostrar sea lo más compacta posible, así: solo los identificadores; y producir los resultados completos debería, a su vez, obtener todos los datos que desee solo para las filas que seleccionó .

Por lo tanto, el SQL podría ser algo así como (no probado, no estoy seguro de que realmente lo haga):

select A.* from table A 
  inner join (select id from table order by whatever limit m, n) B
  on A.id = B.id
order by A.whatever

Si su motor SQL es demasiado primitivo para permitir este tipo de declaraciones SQL, o no mejora nada, contra toda esperanza, podría valer la pena dividir esta única declaración en varias declaraciones y capturar las identificaciones en una estructura de datos.

Actualización : Encontré la publicación del blog de la que estaba hablando: fue "All Abstractions Are Failed Abstractions" de Jeff Atwood en Coding Horror.

Answer 2

La respuesta de Paul Dixon es de hecho una solución al problema, pero deberá mantener la tabla de secuencias y asegurarse de que no haya espacios vacíos.

Si eso es factible, una mejor solución sería simplemente asegurarse de que la tabla original no tenga espacios vacíos, y comience desde la identificación 1. Luego tome las filas usando la identificación para la paginación.

SELECCIONE * FROM la tabla A WHERE id> = 1 AND id <= 1000;
SELECCIONE * DE la tabla A WHERE id> = 1001 AND id <= 2000;

y así...

Answer 3

Me he encontrado con este problema recientemente. El problema fue dos partes para arreglar. Primero tuve que usar una selección interna en mi cláusula FROM que limitaba y compensaba solo en la clave primaria:

$subQuery = DB::raw("( SELECT id FROM titles WHERE id BETWEEN {$startId} AND {$endId}  ORDER BY title ) as t");  

Entonces podría usar eso como parte de mi consulta:

''titles.id'',
                            ''title_eisbns_concat.eisbns_concat'', 
                            ''titles.pub_symbol'', 
                            ''titles.title'', 
                            ''titles.subtitle'', 
                            ''titles.contributor1'', 
                            ''titles.publisher'', 
                            ''titles.epub_date'', 
                            ''titles.ebook_price'', 
                            ''publisher_licenses.id as pub_license_id'', 
                            ''license_types.shortname'',
                            $coversQuery
                        )
                        ->from($subQuery)
                        ->leftJoin(''titles'',  ''t.id'',  ''='', ''titles.id'')
                        ->leftJoin(''organizations'', ''organizations.symbol'', ''='', ''titles.pub_symbol'') 
                        ->leftJoin(''title_eisbns_concat'', ''titles.id'', ''='', ''title_eisbns_concat.title_id'') 
                        ->leftJoin(''publisher_licenses'', ''publisher_licenses.org_id'', ''='', ''organizations.id'') 
                        ->leftJoin(''license_types'', ''license_types.id'', ''='', ''publisher_licenses.license_type_id'')

La primera vez que creé esta consulta, utilicé el OFFSET y LIMIT en MySql. Esto funcionó bien hasta que pasé de la página 100 a continuación, el desplazamiento comenzó a ser insoportablemente lento. Cambiar eso a BETWEEN en mi consulta interna lo aceleró para cualquier página. No estoy seguro de por qué MySql no ha acelerado el DESPLAZAMIENTO pero parece que lo vuelve a enrollar.

Answer 4

No creo que haya necesidad de crear un índice separado si su tabla ya tiene uno. Si es así, puede ordenar con esta clave principal y luego usar los valores de la clave para avanzar:

SELECT * FROM myBigTable WHERE id > :OFFSET ORDER BY id ASC;

Otra optimización sería no usar SELECT * sino simplemente la ID para que simplemente pueda leer el índice y no tenga que localizar todos los datos (reducir la sobrecarga de IO). Si necesita algunas de las otras columnas, entonces tal vez podría agregarlas al índice para que se lean con la clave principal (que probablemente se mantendrá en la memoria y, por lo tanto, no requerirá una búsqueda de disco), aunque esto no será apropiado. para todos los casos, entonces tendrás que jugar.

Escribí un artículo con más detalles:

http://www.4pmp.com/2010/02/scalable-mysql-avoid-offset-for-large-tables/

Answer 5

Si los registros son grandes, la lentitud puede provenir de la carga de los datos. Si la columna de identificación está indexada, simplemente seleccionarla será mucho más rápido. A continuación, puede hacer una segunda consulta con una cláusula IN para los identificadores apropiados (o podría formular una cláusula WHERE utilizando los id. Máx. Y máx. De la primera consulta).

lento:

SELECT * FROM table ORDER BY id DESC LIMIT 10 OFFSET 50000

rápido:

SELECT id FROM table ORDER BY id DESC LIMIT 10 OFFSET 50000

SELECT * FROM table WHERE id IN (1,2,3...10)

Answer 6

Tal vez podría crear una tabla de indexación que proporcione una clave secuencial relacionada con la clave en su tabla de destino. Luego puede unir esta tabla de indexación a su tabla de destino y usar una cláusula where para obtener de manera más eficiente las filas que desea.

#create table to store sequences
CREATE TABLE seq (
   seq_no int not null auto_increment,
   id int not null,
   primary key(seq_no),
   unique(id)
);

#create the sequence
TRUNCATE seq;
INSERT INTO seq (id) SELECT id FROM mytable ORDER BY id;

#now get 1000 rows from offset 1000000
SELECT mytable.* 
FROM mytable 
INNER JOIN seq USING(id)
WHERE seq.seq_no BETWEEN 1000000 AND 1000999;