Perché lo scostamento LIMIT superiore di MySQL rallenta la query verso il basso?

Scenario in breve: una tabella con oltre 16 milioni di record [dimensioni di 2 GB]. L’offset LIMIT più alto con SELECT, più lentamente diventa la query, quando si utilizza ORDER BY * primary_key *

Così

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

richiede molto meno di

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

Questo ordina solo 30 record e lo stesso in ogni caso. Quindi non è l’overhead di ORDER BY.
Ora quando si recuperano le ultime 30 righe ci vogliono circa 180 secondi. Come posso ottimizzare quella semplice query?

È normale che offset più alti rallentino la query, poiché la query deve contare sui primi record OFFSET + LIMIT (e richiedere solo LIMIT di essi). Più è alto questo valore, più lunga è la query.

La query non può andare direttamente a OFFSET perché, in primo luogo, i record possono essere di lunghezza diversa e, in secondo luogo, possono esserci lacune da record cancellati. Ha bisogno di controllare e contare ogni record sulla sua strada.

Supponendo che id sia un PRIMARY KEY di una tabella MyISAM , puoi accelerarlo usando questo trucco:

 SELECT t.* FROM ( SELECT id FROM mytable ORDER BY id LIMIT 10000, 30 ) q JOIN mytable t ON t.id = q.id 

Vedi questo articolo:

  • Prestazioni MySQL ORDER BY / LIMIT: ricerche in fila tardiva

Ho avuto lo stesso identico problema me stesso. Dato che vuoi raccogliere una grande quantità di questi dati e non un set specifico di 30, probabilmente stai eseguendo un ciclo e incrementando l’offset di 30.

Quindi quello che puoi fare invece è:

  1. Mantieni l’ultimo ID di un insieme di dati (30) (ad esempio lastId = 530)
  2. Aggiungi la condizione WHERE id > lastId limit 0,30

Quindi puoi sempre avere un offset ZERO. Rimarrai stupito dal miglioramento delle prestazioni.

MySQL non può andare direttamente al 10000 ° record (o al 80000esimo byte come suggerito) perché non può presumere che sia impacchettato / ordinato in quel modo (o che abbia valori continui tra 1 e 10000). Anche se potrebbe essere così in attualità, MySQL non può presumere che non ci siano buchi / lacune / id cancellati.

Quindi, come notato da Bob, MySQL dovrà recuperare 10000 righe (o attraversare le 10000 voci dell’indice su id ) prima di trovare il 30 da restituire.

EDIT : per illustrare il mio punto

Si noti che sebbene

 SELECT * FROM large ORDER BY id LIMIT 10000, 30 

sarebbe lento (er) ,

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

sarebbe veloce (er) , e restituirebbe gli stessi risultati a condizione che non ci siano id mancanti (cioè spazi vuoti).

La parte dispendiosa in termini di tempo delle due query sta recuperando le righe dalla tabella. Logicamente parlando, nella versione LIMIT 0, 30 , è necessario recuperare solo 30 righe. Nella versione LIMIT 10000, 30 , vengono valutate 10000 righe e vengono restituite 30 righe. Ci può essere qualche ottimizzazione può essere fatto il mio processo di lettura dei dati, ma considera quanto segue:

Cosa succede se hai una clausola WHERE nelle query? Il motore deve restituire tutte le righe qualificate e quindi ordinare i dati e ottenere infine le 30 righe.

Considerare anche il caso in cui le righe non vengono elaborate nella sequenza ORDER BY. Tutte le righe di qualifica devono essere ordinate per determinare quali file restituire.

Ho trovato un esempio interessante per ottimizzare le query SELECT ORDER BY id LIMIT X, Y. Ho 35 milioni di righe, quindi ci sono voluti 2 minuti per trovare un intervallo di righe.

Ecco il trucco:

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

Basta inserire il WHERE con l’ultimo id che hai ottenuto aumentando molto le prestazioni. Per me è stato da 2 minuti a 1 secondo 🙂

Altri trucchi interessanti qui: http://www.iheavy.com/2013/06/19/3-ways-to-optimize-for-paging-in-mysql/

Funziona anche con le stringhe