mysql - tiempo - Cómo optimizar el rendimiento de COUNT(*) en InnoDB utilizando el índice

optimizar mariadb

Question

Tengo una tabla InnoDB grande pero estrecha con ~ 9m registros. Hacer count(*) o count(id) en la tabla es extremadamente lento (6+ segundos):

DROP TABLE IF EXISTS `perf2`;

CREATE TABLE `perf2` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `channel_id` int(11) DEFAULT NULL,
  `timestamp` bigint(20) NOT NULL,
  `value` double NOT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `ts_uniq` (`channel_id`,`timestamp`),
  KEY `IDX_CHANNEL_ID` (`channel_id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

RESET QUERY CACHE;
SELECT COUNT(*) FROM perf2;

Si bien la declaración no se ejecuta con demasiada frecuencia, sería bueno optimizarla. De acuerdo con http://www.cloudspace.com/blog/2009/08/06/fast-mysql-innodb-count-really-fast/ esto debería ser posible forzando a InnoDB a usar un índice:

SELECT COUNT(id) FROM perf2 USE INDEX (PRIMARY);

El plan de explicación parece estar bien:

id  select_type table   type    possible_keys   key     key_len ref     rows    Extra
1   SIMPLE      perf2   index   NULL            PRIMARY 4       NULL    8906459 Using index

Desafortunadamente la afirmación es tan lenta como antes. Según "SELECT COUNT (*)" es lento, incluso con la cláusula where , también he intentado optimizar la tabla sin éxito.

¿Cuál / es / es la manera de optimizar el rendimiento de COUNT(*) en InnoDB?

Answer 1

A partir de MySQL 5.1.6 puede usar el Programador de eventos e insertar el recuento en una tabla de estadísticas con regularidad.

Primero crea una tabla para mantener el conteo:

CREATE TABLE stats (
`key` varchar(50) NOT NULL PRIMARY KEY,
`value` varchar(100) NOT NULL);

Luego crea un evento para actualizar la tabla:

CREATE EVENT update_stats
ON SCHEDULE
  EVERY 5 MINUTE
DO
  INSERT INTO stats (`key`, `value`)
  VALUES (''data_count'', (select count(id) from data))
  ON DUPLICATE KEY UPDATE value=VALUES(value);

No es perfecto, pero ofrece una solución autónoma (sin trabajo cronológico o cola) que se puede adaptar fácilmente para que se ejecute tan a menudo como la frescura necesaria del conteo.

Answer 2

Basado en el código de @Che, también puede usar los disparadores en INSERT y en UPDATE para realizar perf2 para mantener actualizado el valor de la tabla de estadísticas.

CREATE TRIGGER `count_up` AFTER INSERT ON `perf2` FOR EACH ROW UPDATE `stats`
SET 
  `stats`.`value` = `stats`.`value` + 1 
WHERE
  `stats`.`key` = "perf2_count";

CREATE TRIGGER `count_down` AFTER DELETE ON `perf2` FOR EACH ROW UPDATE `stats`
SET 
  `stats`.`value` = `stats`.`value` - 1 
WHERE
  `stats`.`key` = "perf2_count";

Esto tendría la ventaja de eliminar el problema de rendimiento al realizar un conteo (*) y solo se ejecutaría cuando los datos cambien en la tabla perf2

Answer 3

Por el momento he resuelto el problema usando esta aproximación:

EXPLAIN SELECT COUNT(id) FROM data USE INDEX (PRIMARY)

El número aproximado de filas se puede leer en la columna de rows del plan de explicación cuando se usa InnoDB como se muestra arriba. Cuando se usa MyISAM, esto quedará VACÍO ya que la referencia de la tabla se está optimizando, por lo tanto, si está vacío, recurra al tradicional SELECT COUNT lugar.

Answer 4

select max(id) - min(id) from xxx_table where ...

Esto utilizará "Seleccionar tablas optimizadas", y es muy rápido!

Note max(id) - min(id) es en realidad más grande que count(1) .