Le moyen le plus rapide de compter le nombre exact de lignes dans une très grande table?

Je suis tombé sur des articles qui indiquent que SELECT COUNT(*) FROM TABLE_NAME sera lent lorsque la table a beaucoup de lignes et beaucoup de colonnes.

J’ai une table qui pourrait contenir des milliards de lignes [elle a environ 15 colonnes]. Existe-t-il un meilleur moyen d’obtenir le décompte EXACT du nombre de lignes d’une table?

Veuillez considérer ce qui suit avant votre réponse:

  • Je recherche une solution indépendante du fournisseur de firebase database. C’est correct si cela couvre MySQL , Oracle , MS SQL Server . Mais s’il n’y a pas vraiment de solution indépendante pour les fournisseurs de bases de données, je me contenterai de différentes solutions pour différents fournisseurs de bases de données.

  • Je ne peux utiliser aucun autre outil externe pour cela. Je recherche principalement une solution basée sur SQL.

  • Je ne peux pas normaliser la conception de ma firebase database. Il est déjà dans 3NF et de plus, beaucoup de code a déjà été écrit autour de lui.

Réponse simple:

  • Solution indépendante du fournisseur de firebase database = utiliser la norme = COUNT(*)
  • Il existe des solutions SQL Server approximatives , mais n’utilisez pas COUNT (*) = hors de scope

Remarques:

COUNT (1) = COUNT (*) = COUNT (PrimaryKey) juste au cas où

Modifier:

Exemple SQL Server (1,4 milliard de lignes, 12 colonnes)

 SELECT COUNT(*) FROM MyBigtable WITH (NOLOCK) -- NOLOCK here is for me only to let me test for this answer: no more, no less 

1 série, 5:46 minutes, compte = 1 401 659 700

 --Note, sp_spaceused uses this DMV SELECT Total_Rows= SUM(st.row_count) FROM sys.dm_db_partition_stats st WHERE object_name(object_id) = 'MyBigtable' AND (index_id < 2) 

2 passages, tous deux inférieurs à 1 seconde, compte = 1 401 659 670

Le second a moins de lignes = faux. Serait la même ou plus selon les écritures (les suppressions sont faites en dehors des heures ici)

Le moyen le plus rapide de MySQL est:

 SHOW TABLE STATUS; 

Vous obtiendrez instantanément toutes vos tables avec le nombre de lignes (qui est le total) avec beaucoup d’informations supplémentaires si vous voulez.

Je suis tombé sur des articles qui indiquent que SELECT COUNT (*) FROM TABLE_NAME sera lent lorsque la table a beaucoup de lignes et beaucoup de colonnes.

Cela dépend de la firebase database. Certains accélèrent les comptages, par exemple en vérifiant si les lignes sont actives ou inactives dans l’index, ce qui permet d’parsingr uniquement les index pour extraire le nombre de lignes. D’autres ne le font pas, et par conséquent, ils doivent visiter la table entière et compter les rangées en direct une par une. L’un ou l’autre sera lent pour une grande table.

Notez que vous pouvez généralement extraire une bonne estimation en utilisant des outils d’optimisation de requête, des statistiques de table, etc. Dans le cas de PostgreSQL, par exemple, vous pouvez parsingr la sortie du explain count(*) from yourtable et obtenir une estimation raisonnable. Nombre de rangées. Ce qui m’amène à votre deuxième question.

J’ai une table qui pourrait contenir des milliards de lignes [elle a environ 15 colonnes]. Existe-t-il un meilleur moyen d’obtenir le décompte EXACT du nombre de lignes d’une table?

Sérieusement? 🙂 Vous voulez vraiment dire le compte exact d’une table avec des milliards de lignes? Êtes-vous vraiment sûr? 🙂

Si vous le faites vraiment , vous pouvez garder une trace du total en utilisant des déclencheurs, mais la concomitance mentale et les blocages si vous le faites.

Vous pouvez essayer cette commande sp_spaceused (Transact-SQL)

Affiche le nombre de lignes, l’espace disque réservé et l’espace disque utilisé par une table, une vue indexée ou une queue Service Broker dans la firebase database actuelle ou affiche l’espace disque réservé et utilisé par l’ensemble de la firebase database.

Existe-t-il un meilleur moyen d’obtenir le décompte EXACT du nombre de lignes d’une table?

Pour répondre simplement à votre question, non .

Si vous avez besoin d’une méthode indépendante du SGBD, le moyen le plus rapide sera toujours:

 SELECT COUNT(*) FROM TableName 

Certains fournisseurs de SGBD peuvent avoir des moyens plus rapides qui ne fonctionneront que pour leurs systèmes. Certaines de ces options sont déjà affichées dans d’autres réponses.

COUNT(*) doit de toute façon être optimisé par le SGBD (au moins toute firebase database PROD). N’essayez donc pas de contourner leurs optimisations.

Sur une note de côté:
Je suis sûr que beaucoup d’autres requêtes prennent beaucoup de temps à se terminer en raison de la taille de votre table. Tous les problèmes de performance doivent être résolus en pensant à la conception de votre schéma en tenant compte de la rapidité. Je me rends compte que vous avez dit que ce n’est pas une option pour changer, mais il se peut que les requêtes de 10 minutes ou plus ne soient pas une option non plus. La 3ème norme NF n’est pas toujours la meilleure approche lorsque vous avez besoin de rapidité, et parfois les données peuvent être partitionnées dans plusieurs tables si les enregistrements ne doivent pas être stockés ensemble. Quelque chose à quoi penser…

j’utilise

 select /*+ parallel(a) */ count(1) from table_name a; 

Je suis loin d’être aussi expert que ceux qui ont répondu mais j’avais un problème avec une procédure que j’utilisais pour sélectionner une ligne aléatoire dans une table (pas trop pertinente) mais j’avais besoin de connaître le nombre de lignes dans ma table de référence pour calculer l’indice aléatoire. En utilisant la méthode classique Count (*) ou Count (1), je travaillais de temps en temps jusqu’à 2 secondes. Donc, à la place (pour ma table nommée ‘tbl_HighOrder’), j’utilise:

 Declare @max int Select @max = Row_Count From sys.dm_db_partition_stats Where Object_Name(Object_Id) = 'tbl_HighOrder' 

Cela fonctionne très bien et les temps de requête dans Management Studio sont nuls.

Si l’édition SQL Server est 2005/2008, vous pouvez utiliser les DMV pour calculer le nombre de lignes dans une table:

 -- Shows all user tables and row counts for the current database -- Remove is_ms_shipped = 0 check to include system objects -- i.index_id < 2 indicates clustered index (1) or hash table (0) SELECT o.name, ddps.row_count FROM sys.indexes AS i INNER JOIN sys.objects AS o ON i.OBJECT_ID = o.OBJECT_ID INNER JOIN sys.dm_db_partition_stats AS ddps ON i.OBJECT_ID = ddps.OBJECT_ID AND i.index_id = ddps.index_id WHERE i.index_id < 2 AND o.is_ms_shipped = 0 ORDER BY o.NAME 

Pour le moteur de firebase database SQL Server 2000, sysindexes fonctionnera, mais il est fortement recommandé d'éviter de l'utiliser dans les éditions futures de SQL Server, car il pourrait être supprimé dans un avenir proche.

Exemple de code extrait de: Comment obtenir rapidement le nombre de lignes d'une table et sans douleur

Eh bien, en retard de 5 ans et incertain si cela aide:

J’essayais de compter le non. de lignes dans une table SQL Server à l’aide de MS SQL Server Management Studio et a rencontré des erreurs de débordement, j’ai utilisé les éléments suivants:

sélectionnez count_big (1) FROM [nom_base]. [dbo]. [FactSampleValue];

Le résultat :

24296650578 lignes

Je ne pense pas qu’il existe une solution générale toujours la plus rapide: certaines versions de SGBDR / SGB ont une optimisation spécifique pour SELECT COUNT(*) qui utilise des options plus rapides tandis que d’autres simplifient l’parsing par tables. Vous devrez vous rendre sur les sites de documentation / support pour le second jeu, ce qui nécessitera probablement une requête plus spécifique pour être écrite, généralement une requête qui frappe un index d’une manière ou d’une autre.

MODIFIER:

Voici une idée qui pourrait fonctionner selon votre schéma et la dissortingbution des données: avez-vous une colonne indexée qui référence une valeur croissante, un identifiant numérique croissant, ou même un horodatage ou une date? Ensuite, en supposant que les suppressions ne se produisent pas, il devrait être possible de stocker le compte jusqu’à une valeur récente (date d’hier, valeur d’ID la plus élevée à un point d’échantillonnage récent) et d’append le compte au-delà. . Très dépendant des valeurs et des indices, bien sûr, mais applicable à peu près à toute version de tout SGBD.

Pas exactement une solution indépendante du SGBD, mais au moins votre code client ne verra pas la différence …

Créez une autre table T avec juste une ligne et un champ entier N 1 et créez INSERT TRIGGER qui exécute juste:

 UPDATE T SET N = N + 1 

Créez également un DELETE TRIGGER qui exécute:

 UPDATE T SET N = N - 1 

Un SGBD digne de ce nom garantira l’atomicité des opérations au-dessus de 2 , et N contiendra le nombre exact de lignes à tout moment, ce qui est très rapide à obtenir simplement:

 SELECT N FROM T 

Bien que les déclencheurs soient spécifiques aux SGBD, leur sélection à partir de T n’est pas nécessaire et votre code client n’a pas besoin d’être modifié pour chaque SGBD pris en charge.

Cependant, cela peut avoir des problèmes d’évolutivité si la table est INSERT ou DELETE-intensive, surtout si vous ne vous engagez pas immédiatement après INSERT / DELETE.


1 Ces noms ne sont que des espaces réservés – utilisez quelque chose de plus significatif dans la production.

2 Ie N ne peut pas être modifié par une transaction simultanée entre lecture et écriture sur N, tant que la lecture et l’écriture se font dans une seule instruction SQL.

Une réponse littéralement folle, mais si vous avez un système de réplication configuré (pour un système avec un milliard de lignes, j’espère que vous le faites), vous pouvez utiliser un estimateur approximatif (comme MAX(pk) ), divisez cette valeur par le nombre d’esclaves que vous avez, exécutez plusieurs requêtes en parallèle.

Pour la plupart, vous partitionneriez les requêtes entre les esclaves en fonction de la meilleure clé (ou de la clé primaire que je suppose), de cette manière (nous allons utiliser 250000000 comme nos lignes / esclaves):

 -- First slave SELECT COUNT(pk) FROM t WHERE pk < 250000000 -- Ith slave where 2 <= I <= N - 1 SELECT COUNT(pk) FROM t WHERE pk >= I*250000000 and pk < (I+1)*250000000 -- Last slave SELECT COUNT(pk) FROM t WHERE pk > (N-1)*250000000 

Mais vous avez uniquement besoin de SQL. Quel buste Ok, alors disons que vous êtes un sadomasochiste. Sur le maître (ou esclave le plus proche), vous devrez probablement créer une table pour ceci:

 CREATE TABLE counter_table (minpk integer, maxpk integer, cnt integer, slaveid integer) 

Ainsi, au lieu d’avoir seulement les sélections en cours d’exécution dans vos esclaves, vous devrez faire un insert, semblable à ceci:

 INSERT INTO counter_table VALUES (I*25000000, (I+1)*250000000, (SELECT COUNT(pk) FROM ... ), @@SLAVE_ID) 

Vous pouvez rencontrer des problèmes avec les esclaves écrivant dans une table sur master. Vous devrez peut-être devenir encore plus sadis, je veux dire, créatif:

 -- A table per slave! INSERT INTO counter_table_slave_I VALUES (...) 

Vous devriez à la fin avoir un esclave qui existe en dernier dans le chemin parcouru par le graphe de réplication, par rapport au premier esclave. Cet esclave devrait maintenant avoir toutes les autres valeurs de compteur et devrait avoir ses propres valeurs. Mais au moment où vous avez terminé, il y a probablement des lignes ajoutées, vous devrez donc en insérer une autre pour compenser le pk max enregistré dans votre counter_table et le pk max actuel.

À ce stade, vous devrez effectuer une fonction d’agrégation pour déterminer le nombre total de lignes, mais c’est plus facile puisque vous utiliserez au plus le nombre de lignes que vous avez et que vous modifiez.

Si vous êtes dans une situation où vous avez des tables séparées dans les esclaves, vous pouvez UNION pour obtenir toutes les lignes dont vous avez besoin.

 SELECT SUM(cnt) FROM ( SELECT * FROM counter_table_slave_1 UNION SELECT * FROM counter_table_slave_2 UNION ... ) 

Ou vous savez, soyez un peu moins fou et migrez vos données vers un système de traitement dissortingbué, ou utilisez peut-être une solution Data Warehousing (ce qui vous donnera des résultats incroyables à l’avenir).

Notez que cela dépend de la configuration de votre réplication. Etant donné que le goulot d’étranglement principal sera probablement le stockage persistant, si vous avez un stockage grossier ou des magasins de données mal séparés avec un bruit de voisinage important, cela vous conduira probablement plus lentement que d’attendre un seul SELECT COUNT(*) ...

Mais si vous avez une bonne réplication, vos gains de vitesse doivent être directement liés au nombre ou aux esclaves. En fait, si 10 minutes suffisent pour exécuter la requête de comptage et que vous avez 8 esclaves, vous réduirez votre temps de quelques minutes. Peut-être une heure pour régler les détails de cette solution.

Bien sûr, vous n’obtiendrez jamais une réponse extrêmement précise car cette résolution dissortingbuée introduit un peu de temps où les lignes peuvent être supprimées et insérées, mais vous pouvez essayer d’obtenir un verrou de lignes dissortingbué sur la même instance et obtenir un compte précis des lignes dans la table pour un moment particulier dans le temps.

En fait, cela semble impossible, puisque vous êtes fondamentalement bloqué par une solution SQL uniquement, et je ne pense pas que vous disposiez d’un mécanisme permettant d’exécuter instantanément une requête fragmentée sur plusieurs esclaves. Peut-être que si vous aviez le contrôle du fichier journal de réplication … ce qui signifie que vous seriez littéralement en train de faire tourner des esclaves à cet effet, ce qui est sans doute plus lent que de simplement exécuter la requête count sur une seule machine.

Donc, il y a mes deux sous de 2013.

Si le déclencheur d’insertion est trop coûteux à utiliser, mais qu’un déclencheur de suppression peut être accordé, et qu’il y a un id incrémentation automatique , après avoir compté une table entière une fois, et mémorisé le compte comme last-count

Il suffit ensuite de compter chaque jour pour id > last-counted-id , d’append cela à last-count et de stocker le nouvel last-counted-id .

Le déclencheur de suppression décrémenterait le dernier décompte, si l’identifiant de l’enregistrement supprimé <= last-counted-id.

Je suis en retard à cette question, mais voici ce que vous pouvez faire avec MySQL (comme j’utilise MySQL). Je partage mes observations ici:

 1) SELECT COUNT(*) AS TOTAL_ROWS FROM  

Résultat
Nombre de lignes: 508534
Sortie de la console: Lignes concernées: 0 Lignes trouvées: 1 Avertissements: 0 Durée pour 1 requête: 0.125 sec.
Cela prend du temps pour une table avec un grand nombre de lignes, mais le nombre de lignes est très précis.

 2) SHOW TABLE STATUS or SHOW TABLE STATUS WHERE NAME="" 

Résultat
Nombre de lignes: 511235
Sortie de la console: Lignes affectées: 0 Lignes trouvées: 1 Avertissements: 0 Durée pour 1 requête: 0.250 sec Résumé: Le nombre de lignes n’est pas exact.

 3) SELECT * FROM information_schema.tables WHERE table_schema = DATABASE(); 

Résultat
Nombre de lignes: 507806
Sortie de la console: Lignes affectées: 0 Lignes trouvées: 48 Avertissements: 0 Durée pour 1 requête: 1.701 sec.
Le nombre de lignes n’est pas exact.

Je ne suis pas un expert MySQL ou une firebase database, mais j’ai trouvé que pour les très grandes tables, vous pouvez utiliser l’option 2 ou 3 et obtenir une «idée juste» du nombre de lignes présentes.

Je devais obtenir ces nombres de lignes pour afficher certaines statistiques sur l’interface utilisateur. Avec les requêtes ci-dessus, je savais que le nombre total de lignes était supérieur à 500 000, alors j’ai proposé des statistiques telles que “Plus de 500 000 lignes” sans afficher le nombre exact de lignes.

Peut-être que je n’ai pas vraiment répondu à la question du PO, mais je partage ce que j’ai fait dans une situation où de telles statistiques étaient nécessaires. Dans mon cas, montrer les lignes approximatives était acceptable et ce qui précède a fonctionné pour moi.

Si vous avez une structure de table typique avec une colonne de clé primaire auto-incrémentée dans laquelle les lignes ne sont jamais supprimées, le moyen suivant sera le moyen le plus rapide de déterminer le nombre d’enregistrements et fonctionnera de manière similaire dans la plupart des bases de données compatibles ANSI:

 SELECT TOP(1)  FROM  ORDER BY  DESC;

Je travaille avec des tables MS SQL contenant des milliards de lignes nécessitant des temps de réponse inférieurs à la seconde pour les données, y compris le nombre d’enregistrements. Un SELECT COUNT similaire (*) prendrait des minutes pour traiter par comparaison.

sélectionnez les lignes de sysindexes où id = Object_ID (‘TableName’) et indid <2

Placez un index sur une colonne. Cela devrait permettre à l’optimiseur d’effectuer une parsing complète des blocs d’index, au lieu d’une parsing complète de la table. Cela réduira considérablement vos coûts d’entrée / sortie. Regardez le plan d’exécution avant et après. Ensuite, mesurez l’heure de l’horloge murale dans les deux sens.

Si vous utilisez Oracle, qu’en est-il (en supposant que les statistiques de la table sont mises à jour):

 select , num_rows, last_analyzed from user_tables 

last_analyzed indiquera l’heure à laquelle les statistiques ont été rassemblées pour la dernière fois.

Pour le serveur SQL, essayez ceci

 SELECT T.name, I.rows AS [ROWCOUNT] FROM sys.tables AS T INNER JOIN sys.sysindexes AS I ON T.object_id = I.id AND I.indid < 2 WHERE T.name = 'Your_Table_Name' ORDER BY I.rows DESC 

J’ai trouvé ce bon article SQL Server – HOW-TO: récupérer rapidement le nombre de lignes exact pour la table de martijnh1 ce qui donne un bon récapitulatif pour chaque scénario.

J’ai besoin que ceci soit étendu là où j’ai besoin de fournir un compte basé sur une condition spécifique et quand je comprendrai cette partie, je mettrai à jour cette réponse plus loin.

En attendant, voici les détails de l’article:

Méthode 1:

Question:

 SELECT COUNT(*) FROM Transactions 

Commentaires:

Effectue une parsing complète de la table. Ralentissez sur les grandes tables.

Méthode 2:

Question:

 SELECT CONVERT(bigint, rows) FROM sysindexes WHERE id = OBJECT_ID('Transactions') AND indid < 2 

Commentaires:

Moyen rapide de récupérer le nombre de lignes. Dépend des statistiques et est inexact.

Exécutez DBCC UPDATEUSAGE (Database) WITH COUNT_ROWS, ce qui peut prendre beaucoup de temps pour les grandes tables.

Méthode 3:

Question:

 SELECT CAST(p.rows AS float) FROM sys.tables AS tbl INNER JOIN sys.indexes AS idx ON idx.object_id = tbl.object_id and idx.index_id < 2 INNER JOIN sys.partitions AS p ON p.object_id=CAST(tbl.object_id AS int) AND p.index_id=idx.index_id WHERE ((tbl.name=N'Transactions' AND SCHEMA_NAME(tbl.schema_id)='dbo')) 

Commentaires:

La façon dont le studio de gestion SQL compte les lignes (examinez les propriétés des tables, le stockage, le nombre de lignes). Très rapide, mais toujours un nombre approximatif de lignes.

Méthode 4:

Question:

 SELECT SUM (row_count) FROM sys.dm_db_partition_stats WHERE object_id=OBJECT_ID('Transactions') AND (index_id=0 or index_id=1); 

Commentaires:

Opération rapide (mais pas aussi rapide que la méthode 2) et tout aussi importante, fiable.

J’ai reçu ce script d’une autre question / réponse de StackOverflow:

 SELECT SUM(p.rows) FROM sys.partitions AS p INNER JOIN sys.tables AS t ON p.[object_id] = t.[object_id] INNER JOIN sys.schemas AS s ON s.[schema_id] = t.[schema_id] WHERE t.name = N'YourTableNameHere' AND s.name = N'dbo' AND p.index_id IN (0,1); 

Ma table a 500 millions d’enregistrements et le retour ci-dessus en moins de 1ms. Pendant ce temps,

 SELECT COUNT(id) FROM MyTable 

prend 39 minutes, 52 secondes!

Ils donnent exactement le même nombre de lignes (dans mon cas, exactement 519326012).

Je ne sais pas si ce serait toujours le cas.

Avec PostgreSQL:

 SELECT reltuples AS approximate_row_count FROM pg_class WHERE relname = 'table_name' 

Peut-être un peu tard mais cela pourrait aider les autres pour MSSQL

; AVEC RecordCount AS (SELECT ROW_NUMBER () OVER (ORDER BY COLUMN_NAME) AS [RowNumber] FROM TABLE_NAME) SELECT MAX (RowNumber) FROM RecordCount