Pourquoi L'offset de limite supérieure de MYSQL ralentit la requête?

Scénario en bref: Une table avec plus de 16 millions de disques [2 go de taille]. La limite supérieure offset avec SELECT, le plus lent la requête devient, en utilisant L'ordre par * primary_key*

Donc

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

prend beaucoup moins que

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

qui ne commande que 30 disques et la même voie. Donc ce n'est pas les frais généraux de ORDER BY.

Maintenant, quand chercher les 30 dernières rangées il faut environ 180 secondes. Comment puis-je optimiser cette simple requête?

121
demandé sur Brendan Long 2010-12-19 06:01:04

5 réponses

il est normal que les offsets supérieurs ralentissent la requête, puisque la requête doit compter les premiers OFFSET + LIMIT enregistrements (et ne prendre que LIMIT d'entre eux). Plus cette valeur est élevée, plus la requête s'exécute.

la requête ne peut pas aller directement à OFFSET parce que, premièrement, les enregistrements peuvent être de longueur différente, et, deuxièmement, il peut y avoir des lacunes dans les enregistrements supprimés. Il doit vérifier et compter chaque enregistrement sur son chemin.

en supposant que id est un PRIMARY KEY d'une table MyISAM , vous pouvez l'accélérer en utilisant ce truc:

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

voir cet article:

142
répondu Quassnoi 2010-12-21 18:06:02

j'ai eu le même problème moi-même. Étant donné le fait que vous voulez recueillir une grande quantité de ces données et non un ensemble spécifique de 30, vous serez probablement en cours d'exécution d'une boucle et l'augmentation de l'offset de 30.

donc ce que vous pouvez faire à la place est:

  1. dernier id d'un ensemble de données(30) (par exemple lastId = 530)
  2. ajouter la condition WHERE id > lastId limit 0,30

ainsi vous pouvez toujours avoir un Le décalage de ZÉRO. Vous serez surpris par l'amélioration de la performance.

144
répondu Nikos Kyr 2016-08-30 07:59:27

MySQL ne peut pas aller directement à l'enregistrement 10000th (ou le 80000th byte comme votre suggestion) parce qu'il ne peut pas supposer qu'il est emballé/commandé comme cela (ou qu'il a des valeurs continues dans 1 à 10000). Bien qu'il puisse en être ainsi dans la réalité, MySQL ne peut pas supposer qu'il n'y a pas de trous/lacunes/identifiants supprimés.

donc, comme bobs l'a noté, MySQL devra aller chercher 10000 lignes (ou parcourir 10000e entrées de l'index sur id ) avant de trouver les 30 à retourner.

EDIT : pour illustrer mon point

noter que bien que

SELECT * FROM large ORDER BY id LIMIT 10000, 30 

serait lente(er) ,

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

serait fast(er) , et retournerait les mêmes résultats à condition qu'il n'y ait pas de id s manquant (e.g. lacunes).

15
répondu Riedsio 2010-12-27 18:14:08

la partie fastidieuse des deux requêtes consiste à extraire les lignes de la table. Logiquement parlant, dans la version LIMIT 0, 30 , seulement 30 lignes doivent être récupérées. Dans la version LIMIT 10000, 30 , 10000 lignes sont évaluées et 30 lignes sont retournées. Il peut y avoir une certaine optimisation du processus de lecture des données, mais considérez ce qui suit:

et si vous aviez une clause où dans les requêtes? Le moteur doit retourner toutes les lignes éligibles, et puis trier les données, et finalement obtenir les 30 lignes.

considère également le cas où les lignes ne sont pas traitées dans l'ordre séquentiel. Toutes les rangées admissibles doivent être triées pour déterminer quelles rangées doivent être retournées.

5
répondu bobs 2010-12-19 03:28:53

j'ai trouvé un exemple intéressant pour optimiser L'ordre des requêtes SELECT par ID LIMIT X, Y. J'ai 35 millions de rangées donc ça a pris 2 minutes pour trouver une gamme de rangées.

voici le truc:

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

vient de mettre le où avec le dernier id que vous avez obtenu augmenter beaucoup la performance. Pour moi, il était de 2minutes à 1 seconde:)

autres astuces intéressantes ici: http://www.iheavy.com/2013/06/19/3-ways-to-optimize-for-paging-in-mysql /

Il fonctionne aussi avec des chaînes

3
répondu sym 2015-10-01 17:04:18