La requête de décompte complet est-elle vraiment si lente sur une grande table MySQL InnoDB ?

Question

Nous avons une grande table avec des millions d'entrées. Le comptage complet est très lent, voir le code ci-dessous. Est-ce courant pour les tables MySQL InnoDB ? N'y a-t-il pas un moyen d'accélérer ce processus ? Même avec la mise en cache des requêtes, cela reste "lent". J'aimerais également savoir pourquoi le décompte de la table « communication » avec 2,8 millions d'entrées est plus lent que le décompte de la table « transaction » avec 4,5 millions d'entrées.

Je sais qu'il serait plus rapide d'utiliser la clause Where. Je me demande simplement si de mauvaises performances sont normales.

Nous utilisons Amazon RDS MySQL 5.7 et m4.xlarge (4 processeurs, 16 Go de RAM, 500 Go de stockage). J'ai également essayé des instances plus grandes avec plus de CPU et de RAM, mais aucun changement majeur dans les temps de requête.

mysql> SELECT COUNT(*) FROM transaction;
+----------+
| COUNT(*) |
+----------+
|  4569880 |
+----------+
1 row in set (1 min 37.88 sec)

mysql> SELECT COUNT(*) FROM transaction;
+----------+
| count(*) |
+----------+
|  4569880 |
+----------+
1 row in set (1.44 sec)

mysql> SELECT COUNT(*) FROM communication;
+----------+
| count(*) |
+----------+
|  2821486 |
+----------+
1 row in set (2 min 19.28 sec)

Answer 1

Ceci est pris en charge à l'aide du Contrôle de concurrence multi-versions (MVCC).

InnoDB permet d'isoler vos requêtes au sein des transactions sans bloquer d'autres clients simultanés qui lisent et écrivent des lignes de données. Ces mises à jour simultanées n’affectent pas la vue des données de votre transaction.

Mais quel est le nombre de lignes dans le tableau, étant donné que de nombreuses lignes sont ajoutées ou supprimées lors du comptage ? La réponse est vague.

Votre transaction ne devrait pas pouvoir « voir » les versions de lignes créées après le démarrage de la transaction. De même, votre transaction doit compter les lignes même si quelqu'un d'autre demande la suppression de lignes, mais il le fait après le début de votre transaction.

La réponse est que lorsque vous exécutez SELECT COUNT(*) ou tout autre type de requête nécessitant la vérification de plusieurs lignes, InnoDB doit accéder à chaque ligne, voir si la version actuelle de cette ligne est visible dans la vue des transactions de la base de données et effectuer une requête sur si c'est visible, comptez.

Dans les tables qui ne prennent pas en charge les transactions ou les mises à jour simultanées (telles que MyISAM), le moteur de stockage conserve le nombre total de lignes comme métadonnées de la table. Le moteur de stockage ne peut pas prendre en charge plusieurs threads mettant à jour les lignes simultanément, le total des lignes est donc moins ambigu. Ainsi, lorsque vous demandez SELECT COUNT(*) à partir d'une table MyISAM, elle ne renverra que le nombre de lignes en mémoire (mais cela ne sert à rien si vous faites un SELECT COUNT(*)) Utilisez la clause WHERE pour effectuer une paire par certains critères Certains sous-ensembles de lignes sont comptés, donc dans ce cas, il doit réellement les compter).

Dans l'ensemble, la plupart des gens pensent que la prise en charge par InnoDB des mises à jour simultanées en vaut la peine, et ils sont prêts à sacrifier SELECT COUNT(*) les optimisations.

Answer 2

Sauf ce que Bill a dit...

Indice minimum

InnoDB sélectionne l'index "minimal" pour effectuer COUNT(*)。可能所有communication的索引都大于transaction的最小索引，因此存在时间差。判断索引的大小时，请将 PRIMARY KEY colonnes incluses avec les éventuels index secondaires :

PRIMARY KEY(id),   -- INT (4 bytes)
INDEX(flag),       -- TINYINT (1 byte)
INDEX(name),       -- VARCHAR(255) (? bytes)

Pour mesurer la taille, PRIMARY KEY 很大，因为它包含（由于集群）表的所有列。 INDEX(flag) 是“5 个字节”。 INDEX(name) 平均可能有几十个字节。 SELECT COUNT(*) 将明确选择 INDEX(flag).

Apparemment交易有一个“小”索引，但通信non.

TEXT/BLOG Les colonnes sont parfois stockées "non journalisées". Ils ne sont donc pas inclus dans la taille de l’indice PK.

Cache de requêtes

Si le « Cache de requêtes » est activé, la deuxième exécution de la requête peut être beaucoup plus rapide que la première fois. Mais cela n’arrive que si le tableau n’a pas changé. Le contrôle qualité est rarement utile dans les systèmes de production car toute modification apportée à une table invalide toutes les entrées contrôle qualité de cette table. Par « plus rapide », j’entends environ 0,001 seconde ; et non 1,44 seconde.

La différence entre 1m38s et 1,44s est probablement due à ce qui est mis en cache dans buffer_pool (la zone de cache générale d'InnoDB). La première exécution n'a probablement pas trouvé d'index "minimal" dans la RAM, elle a donc effectué beaucoup d'E/S, prenant 98 secondes pour obtenir les 4,5 millions de lignes de cet index. La deuxième exécution a révélé que toutes les données étaient mises en cache dans buffer_pool, elles fonctionnaient donc à la vitesse du processeur (pas d'E/S), donc beaucoup plus rapides.

assez bien

Dans ce cas, je remets fondamentalement en question la nécessité de faire respecter COUNT(*). Notez comment vous dites « 2,8 millions d'entrées » comme si 2 chiffres significatifs étaient « assez bons ». Ne serait-ce pas « assez bien » si vous montriez le décompte à l'utilisateur sur l'interface utilisateur ? Si tel est le cas, une solution pour améliorer les performances serait de les compter une fois par jour et de les stocker quelque part. Cela permettra un accès immédiat à des valeurs « assez bonnes ».

Il existe d'autres technologies. La première consiste à utiliser des balises d'activité ou une forme de tableau récapitulatif pour maintenir les compteurs à jour.

Jetez du matériel dessus

Vous avez constaté que changer le matériel n'aide pas.

• 98 fonctionne aussi vite que n’importe quel produit d’E/S de RDS.
• 1,44s fonctionne aussi vite que n'importe quel processeur RDS.
• MySQL (et ses variantes) n'utilise pas plusieurs processeurs par requête.
• Vous disposez de suffisamment de RAM pour que l'intégralité du "petit" index puisse être placé dans le buffer_pool jusqu'à votre deuxième SELECT COUNT(*).. (trop peu de RAM entraînera une deuxième exécution très lente).)