Come ottimizzare le prestazioni COUNT (*) su InnoDB usando l’indice

Ho una tabella InnoDB ampia ma stretta con record di ~ 9m. Il count(*) o il count(id) sul tavolo è estremamente lento (6+ secondi):

 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; 

Anche se la dichiarazione non viene eseguita troppo spesso, sarebbe bello ottimizzarla. Secondo http://www.cloudspace.com/blog/2009/08/06/fast-mysqlinnodb-count-really-fast/ questo dovrebbe essere ansible forzando InnoDB ad usare un indice:

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

Il piano spiega bene:

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

Sfortunatamente l’affermazione è lenta come prima. Secondo “SELECT COUNT (*)” è lento, anche con la clausola where ho provato anche a ottimizzare la tabella senza successo.

Quale / è / è un modo per ottimizzare le prestazioni COUNT(*) su InnoDB?

Per ora ho risolto il problema usando questa approssimazione:

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

Il numero approssimativo di righe può essere letto dalla colonna delle rows del piano di spiegazioni quando si utilizza InnoDB come mostrato sopra. Quando si utilizza MyISAM questo rimane EMPTY come il riferimento della tabella è ottimizzato lontano, quindi se fallback vuoto al tradizionale SELECT COUNT invece.

Basato sul codice @Che, puoi anche utilizzare i trigger su INSERT e su UPDATE su perf2 per mantenere aggiornato il valore nella tabella delle statistiche.

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

Ciò avrebbe il vantaggio di eliminare il problema di prestazioni dell’esecuzione di un conteggio (*) e verrebbe eseguito solo quando i dati cambiano nella tabella perf2

A partire da MySQL 5.1.6 è ansible utilizzare l’ Event Scheduler e inserire regolarmente il conteggio in una tabella delle statistiche.

Prima crea una tabella per contenere il conteggio:

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

Quindi crea un evento per aggiornare la tabella:

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

Non è perfetto ma offre una soluzione autonoma (senza cronjob o coda) che può essere facilmente adattata per essere eseguita tutte le volte che serve la freschezza del conteggio.