Accélération du comptage de lignes dans MySQL

Question

Supposons, à titre d'exemple, vous exécutez une bibliothèque à l'aide d'un simple MySQL "livres" tableau avec trois colonnes:

(id, titre, statut)

• id est la clé primaire
• le titre est le titre du livre
• le statut pourrait être un enum décrivant le livre de l'état actuel (par exemple: DISPONIBLE, CHECKEDOUT, de TRAITEMENT, de MANQUE)

Une requête simple pour combien de livres tombent dans chaque etat est la suivante:

SELECT status, COUNT(*) FROM books GROUP BY status

ou pour trouver combien de livres sont disponibles:

SELECT COUNT(*) FROM books WHERE status = "AVAILABLE"

Cependant, une fois que la table atteint plusieurs millions de lignes, ces requêtes prendre plusieurs secondes. L'ajout d'un index de la colonne "statut" ne semble pas faire une différence dans mon expérience.

Outre périodiquement la mise en cache des résultats ou explicitement mise à jour des informations de résumé dans un tableau distinct à chaque fois qu'un livre change d'état (via des déclencheurs ou un autre mécanisme), existe-il des techniques pour accélérer à ces types de requêtes? Il semble que le nombre de requêtes a l'air à chaque ligne, et (sans connaître plus de détails) je suis un peu surpris que cette information ne peut en quelque sorte être déterminée à partir de l'index.

Mise à JOUR

En utilisant l'exemple de la table (avec un indexée colonne "statut") avec 2 millions de lignes, je comparés au GROUPE PAR la requête. En utilisant le moteur de stockage InnoDB, la requête prend 3.0 - 3.2 secondes sur ma machine. À l'aide de MyISAM, la requête prend 0.9 - 1.1 secondes. Il n'y avait pas de différence significative entre count(*), count(statut), ou de comptage(1) dans les deux cas.

MyISAM est certes un peu plus vite, mais j'étais curieux de voir si il y avait moyen de faire un équivalent de l'exécution de la requête beaucoup plus rapide (par exemple de 10 à 50 ms -- assez rapide pour être appelé sur chaque page de site demande pour un faible trafic de site) sans le mental généraux de la mise en cache et les déclencheurs. Il semble que la réponse est "il n'y a pas moyen d'exécuter la requête directe rapidement", qui est ce que j'attendais - je voulais juste m'assurer que je n'étais pas en manque d'une alternative simple.

Answer 1

La question est donc de

existe-il des techniques pour accélérer à ces types de requêtes?

Eh bien, pas vraiment. Une colonne de stockage basé sur le moteur serait probablement plus vite avec ceux SELECT COUNT(*) les requêtes, mais il serait moins performant pour à peu près toute autre requête.

Votre meilleur pari est de maintenir un tableau de synthèse à l'aide de déclencheurs. Il n'a pas beaucoup de frais généraux et de la partie SELECT sera instantané n'importe comment grand la table. Voici une partie du code réutilisable:

DELIMITER //

CREATE TRIGGER ai_books AFTER INSERT ON books
FOR EACH ROW UPDATE books_cnt SET total = total + 1 WHERE status = NEW.status
//
CREATE TRIGGER ad_books AFTER DELETE ON books
FOR EACH ROW UPDATE books_cnt SET total = total - 1 WHERE status = OLD.status;
//
CREATE TRIGGER au_books AFTER UPDATE ON books
FOR EACH ROW
BEGIN
    IF (OLD.status <> NEW.status)
    THEN
    	UPDATE books_cnt SET total = total + IF(status = NEW.status, 1, -1) WHERE status IN (OLD.status, NEW.status);
    END IF;
END
//

Answer 2

MyISAM est en fait assez rapide avec un count(*) l'inconvénient est que le stockage MyISAM n'est pas fiable et il faut éviter autant que l'intégrité des données est critique.

InnoDB peut être très lent pour effectuer count(*) les requêtes de type, car il est conçu pour permettre simultané de plusieurs vues des mêmes données. Donc, à tout point dans le temps, ce n'est pas assez pour aller à l'index pour obtenir le nombre.

De: http://www.mail-archive.com/mysql@lists.mysql.com/msg120320.html

La base de données commence avec 1000 enregistrements j'démarrer une transaction de commencer une transaction-je supprimer 50 enregistrements Vous ajouter 50 enregistrements je fais un COUNT() et voir 950 dossiers. Tu fais un COUNT() et voir 1050 enregistrements. Je remets mon opération - base de données a maintenant 950 dossiers à tout le monde, mais vous. Vous vous engagez votre la transaction de base de données a 1000 les enregistrements de nouveau.

Comment InnoDB conserve avec les enregistrements sont "visibles" ou "modifiable" avec l'égard de toute transaction par le biais de le verrouillage de ligne, de la transaction les niveaux d'isolation, et multi-versionnage. http://dev.mysql.com/doc/refman/4.1/en/innodb-transaction-model.html http://dev.mysql.com/doc/refman/4.1/en/innodb-multi-versioning.html

C'est ce qui rend le calcul du nombre d' chaque personne peut voir n'est pas si straight-forward.

Donc ligne de fond est que vous aurez besoin de regarder à la mise en cache le compte d'une certaine manière plutôt que d'aller à la table si vous avez besoin d'accéder à cette information fréquemment et rapidement.

Answer 3

de: http://dev.mysql.com/doc/refman/5.0/en/innodb-restrictions.html

InnoDB ne pas garder un décompte interne de lignes dans une table. (Dans la pratique, ce serait un peu compliqué en raison de multi-versionnage.) Pour traiter un SELECT COUNT(*) à PARTIR de t instruction, InnoDB doit numérisation d'un index de la table, qui prend un peu de temps si l'index n'est pas entièrement dans le pool de mémoire tampon.

La solution proposée est:

Pour obtenir un décompte rapide, vous devez utiliser un compteur de table que vous créez vous-même et laissez votre demande de mettre à jour selon les insertions et les suppressions il n'. AFFICHER la TABLE de STATUT peut également être utilisé si une évaluation approximative du nombre de lignes est suffisant.

En bref: count(*) (sur innoDB) prend beaucoup de temps pour les tables qui contiennent un grand nombre de lignes. C'est par la conception et ne peut pas être aidé.

Écrivez votre propre solution de contournement.