sql - todas - ¿La forma más rápida de contar el número exacto de filas en una tabla muy grande?

sql contar registros agrupados (24)

¿Hay una mejor manera de obtener el recuento EXACTO del número de filas de una tabla?

Para responder a su pregunta simplemente, no .

Si necesita una forma independiente de DBMS para hacer esto, la forma más rápida siempre será:

SELECT COUNT(*) FROM TableName

Algunos proveedores de DBMS pueden tener formas más rápidas de trabajar solo para sus sistemas. Algunas de estas opciones ya están publicadas en otras respuestas.

COUNT(*) debe ser optimizado por el DBMS (al menos cualquier DB digno de PROD) de todos modos, así que no intente pasar por alto sus optimizaciones.

En otros comentarios:
Estoy seguro de que muchas de sus otras consultas también tardan mucho tiempo en completarse debido al tamaño de su mesa. Cualquier problema de rendimiento probablemente debería abordarse pensando en el diseño de su esquema teniendo en cuenta la velocidad. Me doy cuenta de que dijo que no es una opción para cambiar, pero puede resultar que las consultas de más de 10 minutos tampoco sean una opción. 3rd NF no siempre es el mejor enfoque cuando se necesita velocidad, y algunas veces los datos se pueden dividir en varias tablas si los registros no tienen que almacenarse juntos. Algo sobre lo que pensar...

Me he encontrado con artículos que afirman que SELECT COUNT(*) FROM TABLE_NAME será lento cuando la tabla tenga muchas filas y muchas columnas.

Tengo una tabla que puede contener incluso miles de millones de filas [tiene aproximadamente 15 columnas]. ¿Hay una mejor manera de obtener el recuento EXACTO del número de filas de una tabla?

Por favor considere lo siguiente antes de su respuesta:

Estoy buscando una solución independiente de proveedor de base de datos. Está bien si cubre MySQL , Oracle , MS SQL Server . Pero si realmente no hay una solución independiente del proveedor de base de datos, me conformaré con diferentes soluciones para diferentes proveedores de base de datos.
No puedo usar ninguna otra herramienta externa para hacer esto. Estoy buscando principalmente una solución basada en SQL.
No puedo normalizar más el diseño de mi base de datos. Ya está en 3NF y, además, ya se ha escrito una gran cantidad de código.

Me he encontrado con artículos que afirman que SELECT COUNT (*) FROM TABLE_NAME será lento cuando la tabla tenga muchas filas y muchas columnas.

Eso depende de la base de datos. Algunos recuentos de aceleración, por ejemplo, haciendo un seguimiento de si las filas están vivas o muertas en el índice, lo que permite que un índice solo escanee para extraer el número de filas. Otros no, y por lo tanto requieren visitar la tabla completa y contar las filas en vivo una por una. Cualquiera será lento para una mesa enorme.

Tenga en cuenta que, por lo general, puede extraer una buena estimación mediante el uso de herramientas de optimización de consultas, estadísticas de tablas, etc. En el caso de PostgreSQL, por ejemplo, podría analizar el resultado del explain count(*) from yourtable de explain count(*) from yourtable y obtener una estimación razonablemente buena de número de filas. Lo que me lleva a tu segunda pregunta.

Tengo una tabla que puede contener incluso miles de millones de filas [tiene aproximadamente 15 columnas]. ¿Hay una mejor manera de obtener el recuento EXACTO del número de filas de una tabla?

¿Seriamente? :-) ¿Realmente te refieres al recuento exacto de una tabla con miles de millones de filas? ¿Estas realmente seguro? :-)

Si realmente lo hace, podría hacer un seguimiento del total utilizando los desencadenantes, pero tenga en cuenta la concurrencia y los puntos muertos si lo hace.

Bueno, tarde por 5 años y no estoy seguro si ayuda:

Estaba tratando de contar el no. de filas en una tabla de SQL Server utilizando MS SQL Server Management Studio y se encontró con un error de desbordamiento, luego usé el siguiente:

seleccione count_big (1) FROM [dbname]. [dbo]. [FactSampleValue];

El resultado :

24296650578 filas

Con PostgreSQL:

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

Encontré este buen artículo SQL Server – CÓMO HACERLO: recupere rápidamente el recuento de filas preciso para la tabla de martijnh1 que proporciona un buen resumen de cada escenario.

Necesito que esto se amplíe donde necesito proporcionar un recuento basado en una condición específica y cuando resuelva esta parte, actualizaré esta respuesta aún más.

Mientras tanto, aquí están los detalles del artículo:

Método 1:

Consulta:

SELECT COUNT(*) FROM Transactions

Comentarios:

Realiza un escaneo completo de la tabla. Lento en grandes mesas.

Método 2:

Consulta:

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

Comentarios:

Forma rápida de recuperar el recuento de filas. Depende de las estadísticas y es inexacto.

Ejecute DBCC UPDATEUSAGE (Database) CON COUNT_ROWS, lo que puede llevar mucho tiempo para tablas grandes.

Método 3:

Consulta:

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''))

Comentarios:

La forma en que el estudio de administración de SQL cuenta las filas (consulte las propiedades de la tabla, el almacenamiento, el conteo de filas). Muy rápido, pero sigue siendo un número aproximado de filas.

Método 4:

Consulta:

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

Comentarios:

Operación rápida (aunque no tan rápida como el método 2) e igualmente importante, confiable.

La forma más rápida por mucho en MySQL es:

SHOW TABLE STATUS;

Instantáneamente obtendrá todas sus tablas con el recuento de filas (que es el total) junto con mucha información adicional si lo desea.

Llego tarde a esta pregunta, pero esto es lo que puedes hacer con MySQL (ya que uso MySQL). Estoy compartiendo mis observaciones aquí:

1) SELECT COUNT(*) AS TOTAL_ROWS FROM <TABLE_NAME>

Resultado
Cantidad de filas: 508534
Salida de consola: Filas afectadas: 0 Filas encontradas: 1 Advertencias: 0 Duración para 1 consulta: 0.125 seg.
Toma un tiempo para una tabla con un gran número de filas, pero el recuento de filas es muy exacto.

2) SHOW TABLE STATUS or SHOW TABLE STATUS WHERE NAME="<TABLE_NAME>"

Resultado
Cuenta de fila: 511235
Salida de consola: Filas afectadas: 0 Filas encontradas: 1 Advertencias: 0 Duración para 1 consulta: 0.250 seg. Resumen: el conteo de filas no es exacto.

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

Resultado
Cuenta de fila: 507806
Salida de consola: Filas afectadas: 0 Filas encontradas: 48 Advertencias: 0 Duración para 1 consulta: 1.701 seg.
El conteo de filas no es exacto.

No soy experto en MySQL o en bases de datos, pero he encontrado que para tablas muy grandes, puede usar la opción 2 o 3 y obtener una "idea justa" de cuántas filas están presentes.

Necesitaba obtener estos recuentos de filas para mostrar algunas estadísticas en la interfaz de usuario. Con las consultas anteriores, sabía que las filas totales eran más de 500,000, por lo que se me ocurrieron mostrando estadísticas como "Más de 500,000 filas" sin mostrar el número exacto de filas.

Tal vez realmente no he respondido la pregunta del OP, pero estoy compartiendo lo que hice en una situación en la que se necesitaban tales estadísticas. En mi caso, mostrar las filas aproximadas fue aceptable y así lo anterior funcionó para mí.

No creo que haya una solución general siempre más rápida: algunas versiones / RDBMS tienen una optimización específica para SELECT COUNT(*) que usa opciones más rápidas mientras que otras simplemente escanean la tabla. Necesitará ir a la documentación / sitios de soporte para el segundo conjunto, que probablemente necesitará que se escriba alguna consulta más específica, generalmente una que llegue a un índice de alguna manera.

EDITAR:

Este es un pensamiento que podría funcionar, dependiendo de su esquema y distribución de datos: ¿tiene una columna indexada que hace referencia a un valor creciente, un ID numérico creciente, por ejemplo, o incluso una marca de tiempo o fecha? Luego, suponiendo que las eliminaciones no se realicen, debería ser posible almacenar el recuento hasta algún valor reciente (la fecha de ayer, el valor de ID más alto en algún punto de muestra reciente) y agregar el recuento más allá de eso, lo que debería resolverse muy rápidamente en el índice . Muy dependiente de los valores e índices, por supuesto, pero aplicable a casi cualquier versión de cualquier DBMS.

No es exactamente una solución independiente de DBMS, pero al menos el código de su cliente no verá la diferencia ...

Cree otra tabla T con solo una fila y un campo entero N ¹ , y cree INSERT TRIGGER que solo ejecuta:

UPDATE T SET N = N + 1

También crea un BORRADOR DE BORRADO que ejecuta:

UPDATE T SET N = N - 1

Un DBMS que vale su sal garantizará la atomicidad de las operaciones por encima de ² , y N contendrá el conteo preciso de filas en todo momento, lo cual es súper rápido de obtener simplemente:

SELECT N FROM T

Si bien los activadores son específicos de DBMS, la selección de T no lo es y su código de cliente no tendrá que cambiar para cada DBMS compatible.

Sin embargo, esto puede tener algunos problemas de escalabilidad si la tabla es INSERT o DELETE intensiva, especialmente si no se COMPRUEBE inmediatamente después de INSERT / DELETE.

¹ Estos nombres son solo marcadores de posición: use algo más significativo en la producción.

^{2 Es} decir, no se puede cambiar N por una transacción concurrente entre lectura y escritura en N, siempre que la lectura y la escritura se realicen en una sola instrucción SQL.

No soy tan experto como otros que respondieron, pero tuve un problema con un procedimiento que estaba usando para seleccionar una fila aleatoria de una tabla (no demasiado relevante) pero necesitaba saber el número de filas en mi tabla de referencia. Para calcular el índice aleatorio. Usando el trabajo tradicional de Count (*) o Count (1) pero ocasionalmente obtenía hasta 2 segundos para que se ejecutara mi consulta. Así que en lugar de eso (para mi tabla llamada ''tbl_HighOrder'') estoy usando:

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

Funciona muy bien y los tiempos de consulta en Management Studio son cero.

Obtuve este script de otra pregunta / respuesta de :

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);

Mi tabla tiene 500 millones de registros y los resultados anteriores en menos de 1 ms. Mientras tanto,

SELECT COUNT(id) FROM MyTable

lleva un total de 39 minutos, 52 segundos!

Producen el mismo número exacto de filas (en mi caso, exactamente 519326012).

No sé si ese sería siempre el caso.

Para el servidor SQL intente esto

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

Poner un índice en alguna columna. Eso debería permitir que el optimizador realice una exploración completa de los bloques de índice, en lugar de una exploración completa de la tabla. Eso reducirá sus costos de IO muy abajo. Mira el plan de ejecución antes y después. Luego mida el reloj de pared en ambas direcciones.

Puedes probar esto sp_spaceused (Transact-SQL)

Muestra el número de filas, el espacio en disco reservado y el espacio en disco utilizado por una tabla, vista indizada o cola de Service Broker en la base de datos actual, o muestra el espacio en disco reservado y utilizado por toda la base de datos.

Si está utilizando Oracle, ¿qué le parece esto? (Suponiendo que las estadísticas de la tabla estén actualizadas):

select <TABLE_NAME>, num_rows, last_analyzed from user_tables

last_analyzed mostrará la hora en que se recopilaron las estadísticas por última vez.

Si la edición de SQL Server es 2005/2008, puede usar DMV para calcular el recuento de filas en una tabla:

-- 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

Para el motor de base de datos SQL Server 2000, los sysindexes funcionarán, pero se recomienda encarecidamente evitar su uso en futuras ediciones de SQL Server, ya que puede eliminarse en un futuro próximo.

Código de ejemplo tomado de: Cómo obtener conteos de filas de tablas de forma rápida y sin dolor

Si tiene una clave principal (valor único) en algún lugar de su tabla, puede usar MAX(yourId) para esencialmente darle el recuento de filas totales. A continuación se muestra un fragmento de código:

SELECT MAX(yourId) FROM YourTable

Si tiene una estructura de tabla típica con una columna de clave primaria de incremento automático en la que las filas nunca se eliminan, la siguiente será la forma más rápida de determinar el recuento de registros y debería funcionar de manera similar en la mayoría de las bases de datos compatibles con ANSI:

SELECT TOP(1) <primarykeyfield> FROM <table> ORDER BY <primarykeyfield> DESC;

Trabajo con tablas de MS SQL que contienen miles de millones de filas que requieren tiempos de respuesta inferiores a un segundo para los datos, incluidos los recuentos de registros. Un SELECCIONAR CUENTA similar (*) tomaría minutos para procesar por comparación.

Tal vez un poco tarde pero esto podría ayudar a otros para MSSQL

; CON RecordCount AS (SELECT ROW_NUMBER () OVER (ORDER BY COLUMN_NAME) COMO [RowNumber] DE TABLE_NAME) SELECT MAX (RowNumber) DE RecordCount

Una respuesta literalmente insana, pero si tiene algún tipo de sistema de replicación configurado (para un sistema con mil millones de filas, espero que lo haga), puede usar un estimador aproximado (como MAX(pk) ), dividir ese valor por el número de esclavos que tienes, ejecuta varias consultas en paralelo.

En su mayor parte, dividiría las consultas entre esclavos según la mejor clave (o la clave principal, supongo), de tal manera (usaremos 250000000 como nuestras filas / esclavos):

-- 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

Pero solo necesitas SQL Que busto Ok, entonces digamos que eres un sadomasoquista. En el maestro (o el esclavo más cercano) lo más probable es que necesites crear una tabla para esto:

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

Entonces, en lugar de tener solo las selecciones ejecutándose en tus esclavos, tendrías que hacer una inserción, similar a esto:

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

Puedes tener problemas con los esclavos que escriben en una tabla en el maestro. Puede que necesites ponerte aún más triste, quiero decir, creativo:

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

Al final, debe tener un esclavo que exista por última vez en la ruta recorrida por el gráfico de replicación, en relación con el primer esclavo. Ese esclavo debería tener ahora todos los demás valores de contador y debería tener sus propios valores. Pero para cuando hayas terminado, probablemente hay filas agregadas, por lo que tendrías que insertar otra que compense el pk máximo registrado en tu counter_table y el pk máximo actual.

En ese punto, tendrías que hacer una función agregada para averiguar cuál es el total de filas, pero eso es más fácil ya que lo estarías ejecutando en la mayoría de las filas de "número de esclavos que tienes y de cambio".

Si estás en la situación en la que tienes tablas separadas en los esclavos, puedes UNION para obtener todas las filas que necesitas.

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

O bien, puede ser un poco menos demente y migrar sus datos a un sistema de procesamiento distribuido, o tal vez usar una solución de almacenamiento de datos (que también le proporcionará datos impresionantes en el futuro).

Tenga en cuenta que esto depende de qué tan bien esté configurada su replicación. Debido a que el cuello de botella primario es más probable que sea el almacenamiento persistente, si tiene almacenamiento con poca capacidad o almacenes de datos poco segregados con un fuerte ruido de vecino, es probable que esto se ejecute más lentamente que solo esperar un solo SELECT COUNT(*) ...

Pero si tiene una buena replicación, entonces sus ganancias de velocidad deberían estar directamente relacionadas con el número o los esclavos. De hecho, si demora 10 minutos ejecutar la consulta de conteo solo y tiene 8 esclavos, reduciría su tiempo a menos de un par de minutos. Tal vez una hora para resolver los detalles de esta solución.

Por supuesto, nunca obtendría una respuesta asombrosamente precisa ya que esta solución distribuida introduce un poco de tiempo donde se pueden eliminar e insertar filas, pero puede intentar obtener un bloqueo de filas distribuido en la misma instancia y obtener un conteo preciso de las filas en la tabla para un momento particular en el tiempo.

En realidad, esto parece imposible, ya que básicamente estás atascado con una solución solo para SQL, y no creo que te proporcionen un mecanismo para ejecutar una consulta fragmentada y bloqueada en múltiples esclavos, al instante. Tal vez si tuvieras el control del archivo de registro de replicación ... lo que significa que literalmente estarías haciendo esclavos para este propósito, que sin duda es más lento que ejecutar la consulta de recuento en una sola máquina de todos modos.

Así que ahí están mis dos centavos de 2013.

seleccione filas de sysindexes donde id = Object_ID (''TableName'') e indid <2

yo suelo

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

Respuesta simple:

Solución independiente del proveedor de la base de datos = usar el estándar = COUNT(*)
Existen soluciones de SQL Server aproximadas , pero no use COUNT (*) = fuera de alcance

Notas:

COUNT (1) = COUNT (*) = COUNT (PrimaryKey) por si acaso

Editar:

Ejemplo de SQL Server (1.4 mil millones de filas, 12 columnas)

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

1 carreras, 5:46 minutos, cuenta = 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 carreras, ambas en 1 segundo, cuentan = 1,401,659,670

El segundo tiene menos filas = incorrecto. Sería el mismo o más dependiendo de las escrituras (las eliminaciones se realizan fuera de las horas aquí)

Si el activador de inserción es demasiado costoso de usar, pero podría permitirse un activador de eliminación , y hay un id incremento automático , luego de contar la tabla completa una vez y recordar el conteo como last-count y el last-counted-id ,

luego, cada día solo debe contar para id > last-counted-id count last-counted-id , agregar eso a last-count y almacenar el nuevo last-counted-id last-count last-counted-id .

El desencadenante de eliminación disminuiría el último recuento, si la identificación del registro eliminado <= última cuenta contada.