Как оптимизировать производительность COUNT (*) в InnoDB с помощью индекса

У меня есть довольно большая, но узкая таблица InnoDB с записями ~ 9 м. Выполнение count(*) или count(id) на столе происходит очень медленно (6+ секунд):

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;

Хотя утверждение не запускается слишком часто, было бы неплохо его оптимизировать. Согласно http://www.cloudspace.com/blog/2009/08/06/fast-mysql-innodb-count-really-fast/ это должно быть возможно, заставив InnoDB использовать индекс:

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

План объяснения кажется прекрасным:

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

К сожалению, инструкция выполняется так же медленно, как и раньше. Согласно "SELECT COUNT (*)" выполняется медленно, даже с предложением where. Я также попытался оптимизировать таблицу без успеха.

Что/является/re способом оптимизации производительности count(*) на InnoDB?

Ответы

Ответ 1

В настоящее время я решил проблему, используя это приближение:

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

Примерное количество строк можно прочитать из столбца rows плана объяснения при использовании InnoDB, как показано выше. При использовании MyISAM это останется ПУСТОЙ, так как ссылка на таблицу оптимизирована, поэтому, если пустой резерв вместо традиционного SELECT COUNT.

Ответ 2

На основе кода @Che вы также можете использовать триггеры для INSERT и UPDATE для perf2, чтобы обновить значение в таблице статистики.

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

Это будет иметь преимущество в устранении проблемы производительности при выполнении счета (*) и будет выполняться только при изменении данных в таблице perf2

Ответ 3

Начиная с MySQL 5.1.6 вы можете использовать Планировщик событий и регулярно вставлять счет в таблицу статистики.

Сначала создайте таблицу для хранения count:

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

Затем создайте событие для обновления таблицы:

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

Это не идеально, но он предлагает автономное решение (без cronjob или queue), которое может быть легко адаптировано для работы так часто, как требуемая свежесть подсчета.