Comment puis-je accélérer une requête MySQL avec un décalage important dans la clause LIMIT?
J'ai des problèmes de performance quand LIMIT ing un mysql SELECT avec un grand décalage:
SELECT * FROM table LIMIT m, n;
Si le décalage m est, disons, supérieur à 1 000 000, l'opération est très lente.
Je dois utiliser limit m, n; je ne peux pas utiliser quelque chose comme id > 1,000,000 limit n.
Comment puis-je optimiser cette instruction pour de meilleures performances?
6 réponses
Peut-être pourriez-vous créer une table d'indexation qui fournit une clé séquentielle relative à la clé de votre table cible. Ensuite, vous pouvez joindre cette table d'indexation à votre table cible et utiliser une clause where pour obtenir plus efficacement les lignes souhaitées.
#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;
Il y a un article de blog quelque part sur internet sur la meilleure façon de faire en sorte que la sélection des lignes à afficher soit aussi compacte que possible, donc: juste les identifiants; et produire les résultats complets devrait à son tour récupérer toutes les données que vous voulez pour seulement les lignes que vous avez sélectionnées.
Ainsi, le SQL pourrait être quelque chose comme (non testé, Je ne suis pas sûr qu'il fera du bien):
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 votre moteur SQL est trop primitif pour autoriser ce type D'instructions SQL, ou cela n'améliore rien, contre l'espoir, il pourrait être utile de diviser cette instruction unique en plusieurs instructions et de capturer les identifiants dans une structure de données.
Update : j'ai trouvé le billet de blog dont je parlais: C'était de Jeff Atwood"toutes les Abstractions sont des Abstractions échouées" sur Coding Horror.
Si les enregistrements sont volumineux, la lenteur peut provenir du chargement des données. Si la colonne id est indexée, la sélectionner sera beaucoup plus rapide. Vous pouvez ensuite faire une deuxième requête avec une clause IN pour les ID appropriés (ou formuler une clause WHERE en utilisant les ID min et max de la première requête.)
Lent:
SELECT * FROM table ORDER BY id DESC LIMIT 10 OFFSET 50000
Rapide:
SELECT id FROM table ORDER BY id DESC LIMIT 10 OFFSET 50000
SELECT * FROM table WHERE id IN (1,2,3...10)
La réponse de Paul Dixon est en effet une solution au problème, mais vous devrez maintenir la table de séquence et vous assurer qu'il n'y a pas de lacunes de ligne.
Si cela est possible, une meilleure solution serait simplement de s'assurer que la table d'origine n'a pas de lacunes de ligne, et commence à partir de l'id 1. Ensuite, prenez les lignes en utilisant l'id pour la pagination.
Sélectionnez * dans la table A où id > = 1 et id Sélectionnez * dans la table A où id > = 1001 et idEt ainsi de suite...
Je ne pense pas qu'il soit nécessaire de créer un index séparé si votre table en a déjà un. Si c'est le cas, vous pouvez commander par cette clé primaire, puis utiliser les valeurs de la clé pour passer à travers:
SELECT * FROM myBigTable WHERE id > :OFFSET ORDER BY id ASC;
Une autre optimisation serait de ne pas utiliser SELECT * mais juste L'ID afin qu'il puisse simplement lire l'index et ne pas avoir à localiser toutes les données (réduire la surcharge D'E / S). Si vous avez besoin de certaines des autres colonnes, vous pourriez peut-être les ajouter à l'index afin qu'elles soient lues avec le clé primaire ( qui sera très probablement tenue en mémoire et ne nécessite donc pas de recherche de disque) - bien que cela ne soit pas approprié pour tous les cas, vous devrez donc avoir un jeu.
, j'ai écrit un article avec plus de détails:
Http://www.4pmp.com/2010/02/scalable-mysql-avoid-offset-for-large-tables/
J'ai rencontré ce problème récemment. Le problème était deux parties à résoudre. D'abord, j'ai dû utiliser une sélection interne dans ma clause FROM qui a fait ma limitation et ma compensation pour moi sur la clé primaire seulement:
$subQuery = DB::raw("( SELECT id FROM titles WHERE id BETWEEN {$startId} AND {$endId} ORDER BY title ) as t");
Ensuite, je pourrais l'utiliser comme partie de ma requête:
'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 première fois que j'ai créé cette requête, j'avais utilisé le décalage et la limite dans MySql. Cela a bien fonctionné jusqu'à ce que je dépasse la page 100, puis le décalage a commencé à devenir insupportablement lent. Changer cela à entre dans mon intérieur requête accéléré pour n'importe quelle page. Je ne sais pas pourquoi MySql n'a pas accéléré le décalage, mais entre semble le ramener.