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?
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:
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:
- dernier id d'un ensemble de données(30) (par exemple lastId = 530)
- 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.
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).
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.
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