mysql - speed - ¿DEBO COUNT(*) o no?
mysql tuning speed (14)
Sé que generalmente es una mala idea hacer consultas como esta:
SELECT * FROM `group_relations`Pero cuando solo quiero el recuento, debería ir a esta consulta, ya que permite que la tabla cambie, pero aún produce los mismos resultados.
SELECT COUNT(*) FROM `group_relations`
Como su pregunta lo implica, el motivo por el cual SELECT * es desacertado es que los cambios en la tabla podrían requerir cambios en su código. Eso no se aplica a COUNT(*) . Es bastante raro querer el comportamiento especializado que SELECT COUNT(''group_id'') le brinda; generalmente desea saber la cantidad de registros. Para eso es COUNT(*) , así que úselo.
Sé que generalmente es una mala idea hacer consultas como esta:
SELECT * FROM `group_relations`
Pero cuando solo quiero el recuento, debería ir a esta consulta, ya que permite que la tabla cambie, pero aún produce los mismos resultados.
SELECT COUNT(*) FROM `group_relations`
O el más específico
SELECT COUNT(`group_id`) FROM `group_relations`
Tengo la sensación de que este último podría ser más rápido, pero ¿hay otras cosas que considerar?
Actualización : estoy usando InnoDB en este caso, lo siento por no ser más específico.
Si prueba SELECT COUNT (1) FROM group_relations, será un poco más rápido porque no intentará recuperar información de sus columnas.
COUNT (1) solía ser más rápido que COUNT (*), pero eso ya no es cierto, ya que los DBMS modernos son lo suficientemente inteligentes como para saber que no quieres saber acerca de las columnas
Buscar alternativas
Como ha visto, cuando las tablas crecen, COUNT consultas se vuelven lentas. Creo que lo más importante es considerar la naturaleza del problema que estás tratando de resolver. Por ejemplo, muchos desarrolladores usan COUNT consultas al generar la paginación para grandes conjuntos de registros con el fin de determinar el número total de páginas en el conjunto de resultados.
Sabiendo que las consultas COUNT aumentarán lentamente, podría considerar una forma alternativa de mostrar los controles de paginación que simplemente le permiten dejar de lado la consulta lenta. La paginación de Google es un excelente ejemplo.
Denormalizar
Si es absolutamente necesario conocer la cantidad de registros que coinciden con un conteo específico, considere la técnica clásica de desnormalización de datos. En lugar de contar el número de filas en el tiempo de búsqueda, considere incrementar un contador en la inserción de registros y disminuir ese contador en la eliminación de registros.
Si decide hacer esto, considere usar operaciones transaccionales idempotentes para mantener sincronizados esos valores desnormalizados.
BEGIN TRANSACTION;
INSERT INTO `group_relations` (`group_id`) VALUES (1);
UPDATE `group_relations_count` SET `count` = `count` + 1;
COMMIT;
Alternativamente, podría usar desencadenadores de base de datos si su RDBMS los admite.
Dependiendo de su arquitectura, podría tener sentido usar una capa de almacenamiento en caché como memcached para almacenar, incrementar y disminuir el valor desnormalizado, y simplemente caer en la consulta COUNT lenta cuando falta la clave de caché. Esto puede reducir la contención de escritura general si tiene datos muy volátiles, aunque en casos como este, querrá considerar soluciones para el efecto de pila de perros .
COUNT (*) cuenta todas las filas mientras que COUNT (column_name) contará solo las filas sin valores NULL en la columna especificada.
Importante tener en cuenta en MySQL:
COUNT () es muy rápido en las tablas MyISAM para columnas * o no nulas, ya que el recuento de filas se almacena en caché. InnoDB no tiene caché de recuento de fila, por lo que no hay diferencia en el rendimiento para COUNT (*) o COUNT (column_name), independientemente de si la columna puede ser nula o no. Puede leer más sobre las diferencias en esta publicación en el blog de rendimiento de MySQL.
Depende de lo que realmente está tratando de lograr, como ya lo dijo Sebastian, es decir, ¡deje claras sus intenciones! Si solo está contando las filas, proceda con el COUNT (*) o contando una sola columna para el COUNT (columna).
También podría valer la pena consultar a su proveedor de DB. Antes, cuando solía usar Informix, tenía una optimización para COUNT (*) que tenía un costo de ejecución de plan de consulta de 1 en comparación con el conteo de columnas simples o mutliple que daría como resultado una cifra más alta
El consejo que recibí de MySQL sobre este tipo de cosas es que, en general, tratar de optimizar una consulta basada en trucos como este puede ser una maldición a largo plazo. Hay ejemplos en la historia de MySQL donde la técnica de alto rendimiento de alguien que depende de cómo funciona el optimizador termina siendo el cuello de botella en la próxima versión.
Escriba la consulta que responde a la pregunta que está haciendo: si desea contar todas las filas, use COUNT (*). Si desea un conteo de columnas que no sean nulas, use COUNT (col) WHERE col IS NOT NULL. Indique apropiadamente y deje la optimización al optimizador. Intentar hacer sus propias optimizaciones de nivel de consulta a veces puede hacer que el optimizador incorporado sea menos efectivo.
Dicho esto, hay cosas que puedes hacer en una consulta para que sea más fácil para el optimizador acelerarlo, pero no creo que COUNT sea una de ellas.
Editar: Las estadísticas en la respuesta anterior son interesantes, sin embargo. No estoy seguro de si realmente hay algo en juego en el optimizador en este caso. Solo estoy hablando de optimizaciones de nivel de consulta en general.
Las tablas MySQL ISAM deben tener optimización para COUNT (*), omitiendo el escaneo completo de la tabla.
Lo mejor es contar por una columna indexada como una clave principal.
SELECT COUNT(`group_id`) FROM `group_relations`
Si la columna en cuestión NO ES NULA, ambas consultas son equivalentes. Cuando group_id contiene valores nulos,
select count(*)
contará todas las filas, mientras que
select count(group_id)
solo contará las filas donde group_id no es nulo.
Además, algunos sistemas de bases de datos, como MySQL, emplean una optimización cuando se solicita el recuento (*), lo que hace que esas consultas sean un poco más rápidas que las específicas.
Personalmente, cuando solo estoy contando, estoy contando (*) para estar seguro con los nulos.
Si prueba SELECT COUNT(1) FROM group_relations, será un poco más rápido porque no intentará recuperar información de sus columnas.
Editar: Acabo de investigar y descubrí que esto solo ocurre en algunos DB. En sqlserver es lo mismo usar 1 o *, pero en Oracle es más rápido usar 1.
Aparentemente no hay diferencia entre ellos en mysql, como sqlserver, el analizador parece cambiar la consulta para seleccionar (1). Lo siento si te confundo de alguna manera.
Si recuerdo bien, en MYSQL COUNT (*) cuenta todas las filas, mientras que COUNT (column_name) cuenta solo las filas que tienen un valor no NULL en la columna dada.
Tenía curiosidad sobre esto yo mismo. Está bien leer la documentación y las respuestas teóricas, pero me gusta equilibrarlas con evidencia empírica.
Tengo una tabla MySQL (InnoDB) que tiene 5,607,997 registros en ella. La tabla está en mi propia caja de arena privada, así que sé que los contenidos son estáticos y que nadie más está usando el servidor. Creo que esto elimina efectivamente todos los efectos externos sobre el rendimiento. Tengo una tabla con un campo Auto_increment Primary Key (Id) que sé que nunca será nulo y que usaré para mi prueba where clause (WHERE id IS NOT NULL).
El único otro problema posible que veo en las pruebas de ejecución es el caché. La primera vez que se ejecuta una consulta siempre será más lenta que las consultas posteriores que usan los mismos índices. Me referiré a eso a continuación como la llamada de Siembra de caché. Solo para mezclarlo un poco, lo ejecuté con una cláusula WHERE que sé que siempre se evaluará como verdadera independientemente de cualquier dato (VERDADERO = VERDADERO).
Eso dicho aquí son mis resultados:
Tipo de consulta
| w/o WHERE | where id is not null | where true=true
CONTAR()
| 9 min 30.13 sec ++ | 6 min 16.68 sec ++ | 2 min 21.80 sec ++
| 6 min 13.34 sec | 1 min 36.02 sec | 2 min 0.11 sec
| 6 min 10.06 se | 1 min 33.47 sec | 1 min 50.54 sec
COUNT (Id)
| 5 min 59.87 sec | 1 min 34.47 sec | 2 min 3.96 sec
| 5 min 44.95 sec | 1 min 13.09 sec | 2 min 6.48 sec
COUNT (1)
| 6 min 49.64 sec | 2 min 0.80 sec | 2 min 11.64 sec
| 6 min 31.64 sec | 1 min 41.19 sec | 1 min 43.51 sec
++ Esto se considera la llamada de siembra de caché. Se espera que sea más lento que el resto.
Yo diría que los resultados hablan por sí mismos. COUNT (Id) generalmente bordea a los demás. Agregar una cláusula Where reduce drásticamente el tiempo de acceso incluso si es una cláusula que usted sabe que evaluará como verdadera. El punto óptimo parece ser COUNT (Id) ... WHERE id NO ES NULO.
Me encantaría ver los resultados de otras personas, quizás con tablas más pequeñas o con cláusulas where en campos diferentes de los que está contando. Estoy seguro de que hay otras variaciones que no he tenido en cuenta.
Un asterisco en COUNT no tiene nada que ver con un asterisco para seleccionar todos los campos de la tabla. Es una pura tontería decir que COUNT (*) es más lento que COUNT (campo)
Intuyo que seleccionar COUNT (*) es más rápido que seleccionar COUNT (campo). Si el RDBMS detectó que especifica "*" en COUNT en lugar de en el campo, no es necesario evaluar nada para incrementar el recuento. Mientras que si especifica el campo en COUNT, el RDBMS siempre evaluará si su campo es nulo o no lo contará.
Pero si su campo es nulo, especifique el campo en COUNT.
CUENTA (*) hechos y mitos:
MITO : "InnoDB no maneja bien las consultas de conteo (*)":
La mayoría de las consultas de recuento (*) se ejecutan de la misma manera en todos los motores de almacenamiento si tiene una cláusula WHERE, de lo contrario, InnoDB tendrá que realizar un escaneo completo de la tabla.
HECHO : InnoDB no optimiza las consultas de recuento (*) sin la cláusula where