Requête de sélection avec un décalage de la limite est trop lent

C'est lent parce qu'il a besoin pour localiser le haut offset lignes et analyse les 100 prochaines. Aucune somme d'optimisation changera que lorsque vous faites affaire avec d'énormes décalages.

C'est parce que votre requête littéralement instruire le moteur DB à visiter, beaucoup de lignes en utilisant offset 3900000 -- ce qui est de 3,9 M lignes. Options pour accélérer les choses un peu, ne sont pas nombreux.

Super-rapide de RAM, Ssd, etc. aidera. Mais vous aurez tout à gagner par un facteur constant en agissant de la sorte, c'est simplement de remettre les choses à la route jusqu'à ce que vous atteindre une plus grande assez de décalage.

Assurer le tableau s'inscrit dans la mémoire, avec beaucoup plus de rechange contribuera également par un plus grand facteur constant -- à l'exception de la première fois. Mais cela peut ne pas être possible avec une assez grande table ou d'un index.

Assurer que vous êtes en train de faire de l'indice-seulement des analyses de travailler à une mesure. (Voir velis de réponse, il a beaucoup de mérite.) Le problème ici est que, à toutes fins pratiques, vous pouvez penser à un index d'une table de stockage d'un emplacement de disque et les champs indexés. (C'est plus optimisé que ça, mais c'est une première approximation raisonnable.) Avec assez de lignes, vous aurez toujours être en cours d'exécution dans problèmes avec une plus grande assez de décalage.

Essayer de stocker et de conserver la position exacte de la ligne, est lié à une approche coûteuse aussi.(Ceci est suggéré par ex. benjist.) Bien que techniquement possible, il souffre de limitations similaires à celles découlant de l'utilisation des MPTT avec une structure en arbre: vous allez acquérir de manière significative sur le lit mais finira par l'excès de temps d'écriture lorsqu'un nœud est inséré, mis à jour ou supprimé de telle manière que de gros morceaux de données doit être mise à jour à côté.

Comme je l'espère, de plus en plus clair, il n'y a pas une vraie magie puce lorsque vous traitez avec des décalages de cette dimension. Il est souvent préférable de s'adresser à d'autres approches.

Si vous êtes à la pagination en fonction de l'ID (ou un champ de date, ou de toute autre indexables ensemble de champs), un potentiel de truc (utilisé par blogspot, par exemple) serait de rendre votre requête commencer à un point arbitraire dans l'index.

Mettre une autre manière, au lieu de:

example.com?page_number=[huge]

Faire quelque chose comme:

example.com?page_following=[huge]

De cette façon, vous gardez une trace de l'endroit où vous êtes dans votre index, et la requête devient très vite parce qu'il peut vous rendre directement au point de départ correct sans labour par une foule de lignes:

select * from foo where ID > [huge] order by ID limit 100

Naturellement, vous perdez la capacité de sauter, par exemple la page 3000. Mais donner ce quelque honnête de la pensée: à quand remonte la dernière fois que vous avez sauté à un grand numéro de page sur un site au lieu d'aller tout droit pour son mensuel des archives ou à l'aide de sa zone de recherche?

Si vous êtes à la pagination mais que vous voulez garder le décalage de page par tous les moyens, encore une autre approche consiste à interdire l'usage du plus grand nombre de pages. Il n'est pas idiot: c'est ce que Google est en train de faire avec les résultats de la recherche. Lors de l'exécution d'une requête de recherche, Google vous donne une estimation du nombre de résultats (vous pouvez obtenir un nombre raisonnable à l'aide de explain), et ensuite vous permettra de sourcils haut à quelques milliers de résultats -- rien de plus. Entre autres choses, ils le font pour des raisons de performance -- précisément celui que vous êtes en cours d'exécution dans.

OriginalL'auteur Denis de Bernardy

Vous n'avez pas dit si vos données sont principalement en lecture seule ou mis à jour souvent. Si vous pouvez gérer pour créer votre table à la fois, et seulement de la mettre à jour chaque maintenant et puis (disons toutes les quelques minutes) de votre problème facile à résoudre:

• Ajouter une nouvelle colonne "offset_id"
• Pour votre jeu de données complet commandé par ID, créez une offset_id simplement par incrémentation des numéros: 1,2,3,4...
• Au lieu de "décalage ... la limite de 100" utiliser "où offset_id >= 3900000 limite de 100"

OriginalL'auteur benjist

Tout d'abord, vous devez définir limit et offset avec la clause order by ou vous obtiendrez incompatible résultat.

Pour accélérer la requête, vous pouvez avoir un indice calculé, mais seulement pour ces condition :

1. Nouvellement inséré des données est strictement dans l'ordre d'id
2. Pas supprimer ni de mise à jour sur la colonne id

Voici comment Vous pouvez le faire :

1. De créer une ligne de la position de la fonction

create or replace function id_pos (id) returns bigint
as 'select count(id) from test_table where id <= $1;'
language sql immutable;

1. Créer un indice calculé sur id_pos fonction

create index table_by_pos on test_table using btree(id_pos(id));

Voici comment Vous l'appelez (décalage de 3900000 limite de 100):

select * from test_table where id_pos(id) >= 3900000 and sales_pos(day) < 3900100;

De cette façon, la requête ne sera pas calculer la 3900000 de décalage de données, mais seulement de calculer les 100 de données, ce qui rend beaucoup plus rapide.

Veuillez noter que les 2 conditions où cette approche peut avoir lieu, ou la position va changer.

OriginalL'auteur Soni Harriz

Que diriez-vous si paginer basé sur les numéros au lieu de décalage/limite?

La requête suivante donnera des Id qui se sont séparés de tous les enregistrements en morceaux de taille per_page. Il ne dépend pas de dossiers ont été supprimés ou non

SELECT id AS from_id FROM (
  SELECT id, (ROW_NUMBER() OVER(ORDER BY id DESC)) AS num FROM test_table
) AS rn
WHERE num % (per_page + 1) = 0;

Avec ces from_IDs vous pouvez ajouter des liens à la page. Itérer sur :from_ids avec index et ajouter le lien suivant vers la page:

<a href="/test_records?from_id=:from_id">:from_id_index</a>

Lorsque l'utilisateur visite la page récupérer des enregistrements avec l'ID qui est plus grand que ce qui est demandé :from_id:

SELECT * FROM test_table WHERE ID >= :from_id ORDER BY id DESC LIMIT :per_page

Pour le premier lien de la page avec from_id=0 travaillera

<a href="/test_records?from_id=0">1</a>

OriginalL'auteur Hirurg103