Tengo un largish pero estrecho de tabla InnoDB con ~9m registros. Haciendo count(*) o count(id) sobre la mesa 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;

Mientras que la instrucción es no correr demasiado a menudo sería bueno para optimizarlo. De acuerdo a http://www.cloudspace.com/blog/2009/08/06/fast-mysql-innodb-count-really-fast/ esto debería ser posible, obligando InnoDB utilizar un índice:

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

El explicar el plan parece 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

Por desgracia, la declaración es tan lento como antes. De acuerdo a «SELECT COUNT(*)» es lento, incluso con la cláusula where también he intentado optimizar la tabla sin éxito.

¿Cuál/es de la/re una forma de optimizar COUNT(*) rendimiento en InnoDB?

  • Cambiar a MyISAM puede hacer milagros y que cuesta sólo un solo clic en PHPMyAdmin .)
  • También los costos de la aplicación de extranjero claves y el uso de transacciones.
  • ahora entiendo, pensé que significaba transacciones o claves foráneas eran obligatorias para MyISAM, que yo creo que no. Incomprendido «hacer cumplir»
  • cierto, pero no la pregunta :/
  • El índice de channel_id es redundante con el que inicia con la columna de la caída de la antigua.
  • james es la forma en doctrine orm crea los índices.
  • (Voy a añadir a mi lista de razones para evitar la 3ª parte de los paquetes.)
  • Oh… RESET QUERY CACHE nunca se necesita para obtener el valor actual de una SELECT. Sólo es útil para evitar engañosa de distribución. (Yo prefiero agregar SQL_NO_CACHE.)

InformationsquelleAutor andig | 2013-10-09

4 Comentarios

  1. 17

    Por el momento he solucionado el problema con esta aproximación:

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

    El número aproximado de filas que se pueden leer en el rows columna de explicar el plan al usar InnoDB como se muestra arriba. Al usar MyISAM este permanecerá VACÍA de la tabla de referencia isbeing optimizado lejos – de modo que si está vacío recurso alternativo a la tradicional SELECT COUNT lugar.

    • Recuerde que la «aproximación» aquí está lejos de la realidad, y para una tabla con 1 millón de filas puede volver en cualquier lugar entre 100 MIL y 10M. SHOW TABLE STATUS es similar. Es muy poco fiable. Creo que muchos de los problemas de rendimiento se fija en MySQL 5.7, aunque.
    • Esta aproximación es muy poco fiable que no puedo pensar en una situación en la que yo lo uso. Para una mesa grande, prefiero ejecutar el pleno COUNT(*) periódicamente y utilizar ese valor hasta el siguiente recuento.
    • El Auto_increment campo dentro de SHOW TABLE STATUS es mucho más precisa para un recuento de toda la tabla, con mucha mayor velocidad.
    • tenga cuidado si se tienen registros se eliminan de la tabla, a continuación, Auto_increment será una sobre-estimación.
  2. 15

    MySQL 5.1.6 puede utilizar el Programador De Eventos e inserte el recuento de una tabla de estadísticas regularmente.

    Primero se crea una tabla para sostener a la cuenta:

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

    A continuación, crear 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 auto-contenida (no cronjob o cola) que puede ser fácilmente adaptada para ejecutar tan a menudo como se requiera la frescura de la cuenta.

    • No sabía acerca de los eventos, gracias. Todavía sería un asesino para mi pequeña RasPi ejecución de la DB 🙁
    • Esto es bueno y útil
  3. 13

    Basa en @Che código, también se puede utilizar desencadenadores en INSERTAR y ACTUALIZAR a perf2 en el fin de mantener el valor en la tabla de estadísticas hasta la fecha.

    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 de la realización de un count(*) y sólo se ejecuta cuando los cambios de los datos en la tabla perf2

  4. -4

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

    Este será de uso «Seleccionar tablas optimizadas de distancia», y es muy rápido!!!

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

Dejar respuesta

Please enter your comment!
Please enter your name here