Comment accélérer "select count(*)" par "groupe" et "où"?
Comment accélérer select count(*) avec group by?
Il est trop lent et est utilisé très fréquemment.
J'ai beaucoup de mal à utiliser select count(*) et group by avec une table ayant plus de 3 000 000 lignes.
select object_title,count(*) as hot_num
from relations
where relation_title='XXXX'
group by object_title
Relation_title, object_title est de type varchar. où relation_title= 'XXXX' , qui renvoie plus de 1 000 000 lignes, conduit aux index sur object_title n'a pas pu fonctionner correctement.
8 réponses
Voici plusieurs choses que j'essaierais, par ordre de difficulté croissante:
(plus facile) - assurez-vous que vous avez le droit couvrant index
CREATE INDEX ix_temp ON relations (relation_title, object_title);
Cela devrait maximiser perf compte tenu de votre schéma existant, puisque (à moins que votre version de l'optimiseur de mySQL ne soit vraiment stupide!) il minimisera la quantité d'E / S nécessaire pour satisfaire votre requête (contrairement à si l'index est dans l'ordre inverse où l'index entier doit être analysé) et il couvrira la requête de sorte que vous n'aurez pas à le faire touchez l'index cluster.
(un peu plus difficile) - assurez-vous que votre champs varchar sont aussi petits que possible
L'un des défis perf avec les index varchar sur MySQL est que, lors du traitement d'une requête, la taille totale déclarée du champ sera tirée dans la RAM. Donc, si vous avez un varchar (256) mais n'utilisez que 4 caractères, vous payez toujours l'utilisation de la RAM de 256 octets pendant le traitement de la requête. Ouch! Donc, si vous pouvez réduire vos limites varchar facilement, cela devrait accélérer vos requêtes.
(plus dur) - normaliser
30% de vos lignes ayant une seule valeur de chaîne est un cri clair pour la normalisation dans une autre table de sorte que vous ne dupliquez pas des chaînes des millions de fois. Envisagez de normaliser en trois tables et d'utiliser des ID entiers pour les joindre.
Dans certains cas, vous pouvez normaliser sous les couvertures et masquer la normalisation avec des vues qui correspondent au nom de la table courante... ensuite, il vous suffit de faire votre insertion / mise à jour / supprimer les requêtes conscientes de la normalisation mais peuvent laisser vos sélections seules.
(plus dur) - hachez vos colonnes de chaîne et indexez les hachages
Si normaliser signifie changer trop de code, mais que vous pouvez changer un peu votre schéma, vous pouvez envisager de créer des hachages 128 bits pour vos colonnes de chaîne (en utilisant la fonction MD5). Dans ce cas (contrairement à la normalisation), vous n'avez pas à modifier toutes vos requêtes, seulement les insertions et certaines des sélections. De toute façon, vous voudrez hacher vos champs de chaîne, puis créer un index sur les hachages, par exemple
CREATE INDEX ix_temp ON relations (relation_title_hash, object_title_hash);
Notez que vous devrez jouer avec le SELECT pour vous assurer que vous faites le calcul via l'index de hachage et ne pas tirer dans l'index cluster (requis pour résoudre la valeur de texte réelle de object_title afin de satisfaire la requête).
De plus, si relation_title a une petite taille varchar mais que le titre de l'objet a une taille longue, vous pouvez potentiellement hacher uniquement object_title et créez l'index sur (relation_title, object_title_hash).
Notez que cette solution n'aide que si un ou les deux champs sont très longs par rapport à la taille des hachages.
Notez également qu'il y a des impacts intéressants sur la sensibilité à la casse/le classement du hachage, car le hachage d'une chaîne en minuscules n'est pas le même que celui d'une chaîne en majuscules. Vous devrez donc vous assurer d'appliquer la canonicalisation aux chaînes avant de les hacher-en d'autres termes, ne hachez que les minuscules Si vous êtes dans une base de données insensible à la casse. Vous pouvez également découper les espaces du début ou de la fin, en fonction de la façon dont votre base de données gère les espaces de début/fin.
Indexer les colonnes de la clause GROUP BY serait la première chose à essayer, en utilisant un index composite. Une requête comme celle-ci peut potentiellement être répondue en utilisant uniquement les données d'index, évitant ainsi d'avoir à analyser la table. Puisque les enregistrements de l'index sont triés, le SGBD ne devrait pas avoir besoin d'effectuer un tri séparé dans le cadre du traitement de groupe. Cependant, l'index ralentira les mises à jour de la table, alors soyez prudent si votre table subit de lourdes mises à jour.
Si vous utilisez InnoDB pour le stockage de la table, les lignes de la table seront physiquement regroupées par l'index de clé primaire. Si cela (ou une partie de celui-ci) correspond à votre groupe par clé, cela devrait accélérer une requête comme celle-ci car les enregistrements associés seront récupérés ensemble. Encore une fois, cela évite d'avoir à effectuer un tri séparé.
En général, les index bitmap seraient une autre alternative efficace, mais MySQL ne les supporte pas actuellement, pour autant que je sache.
A matérialisé view serait une autre approche possible, mais encore une fois cela n'est pas supporté directement dans MySQL. Toutefois, si vous n'avez pas besoin que les statistiques de comptage soient complètement à jour, vous pouvez exécuter périodiquement une instruction CREATE TABLE ... AS SELECT ... pour mettre en cache manuellement les résultats. C'est un peu moche comme elle n'est pas transparente, mais peut être acceptable dans votre cas.
Vous pouvez également maintenir une table de cache de niveau logique à l'aide de déclencheurs. Cette table aurait une colonne pour chaque colonne de votre clause GROUP BY, avec un nombre colonne pour stocker le nombre de lignes pour cette valeur de clé de regroupement particulière. Chaque fois qu'une ligne est ajoutée ou mise à jour dans la table de base, insérez ou incrémentez/décrémentez la ligne de compteur dans la table récapitulative pour cette clé de regroupement particulière. Cela peut être mieux que l'approche de vue matérialisée factice, car le résumé mis en cache sera toujours à jour, et chaque mise à jour est effectuée progressivement et devrait avoir moins d'impact sur les ressources. Je pense que vous devriez faire attention aux conflits de verrouillage sur le cache tableau, cependant.
Si vous avez InnoDB, count (*) et toute autre fonction d'agrégat feront une analyse de table. Je vois quelques solutions ici:
- Utilisez des déclencheurs et stockez des agrégats dans une table séparée. Avantages: intégrité. Inconvénients: mises à jour lentes
- Utiliser les files d'attente de traitement. Avantages: rapide des mises à jour. Inconvénients: l'ancien état peut persister jusqu'à ce que la file d'attente soit traitée afin que l'utilisateur puisse ressentir un manque d'intégrité.
- séparez complètement la couche d'accès au stockage et stockez les agrégats dans une table séparée. La couche de stockage sera conscient de la structure de données et peut appliquer des deltas au lieu de faire des comptes complets. Par exemple, si vous fournissez une fonctionnalité "addObject", vous saurez quand un objet a été ajouté et donc l'agrégat sera affecté. Ensuite, vous ne faites qu'un
update table set count = count + 1. Avantages: mises à jour rapides, intégrité (vous pouvez utiliser un verrou si plusieurs clients peuvent modifier le même enregistrement). Inconvénients: vous associez un peu de logique métier et de stockage.
Je vois que quelques personnes ont demandé quel moteur vous utilisiez pour la requête. Je vous recommande fortement D'utiliser MyISAM pour les raisons suivantes:
InnoDB - @ Sorin Mocanu correctement identifié que vous allez faire une analyse complète de la table indépendamment des index.
MyISAM - garde toujours le nombre de lignes actuel à portée de main.
Enfin, comme l'a déclaré @ justin, assurez-vous d'avoir l'index de couverture approprié:
CREATE INDEX ix_temp ON relations (relation_title, object_title);
Essai compte (myprimaryindexcolumn) et comparez les performances à votre compte (*)
Il y a un point où vous avez vraiment besoin plus de RAM / CPU / IO. Vous avez peut-être frappé que pour votre matériel.
Je noterai qu'il n'est généralement pas efficace d'utiliser des index (à moins qu'ils ne le soient couvrant) pour les requêtes qui touchent plus de 1-2% du total des lignes dans une table. Si votre grande requête fait des recherches d'index et des recherches de signets, cela pourrait être en raison d'un plan mis en cache qui provenait d'une simple requête day-total. Essayez d'ajouter dans avec (INDEX = 0) pour forcer une analyse de table et voir si elle est plus rapide.
Si vous connaissez la taille de la table entière, vous devriez interroger les tables meta ou le schéma info (qui existent sur tous les SGBD que je connais, mais je ne suis pas sûr de MySQL). Si votre requête est sélective, vous devez vous assurer qu'il existe un index pour cela.
AFAIK il n'y a rien de plus que vous pouvez faire.
Je suggère d'archiver les données à moins qu'il n'y ait une raison spécifique de les conserver dans la base de données ou que vous puissiez partitionner les données et exécuter des requêtes séparément.