인덱스를 사용하여 InnoDB에서 COUNT(*) 성능을 최적화하는 방법

인덱스를 사용하여 InnoDB에서 COUNT(*) 성능을 최적화하는 방법

저는 ~9m의 기록이 있는 크고 좁은 InnoDB 테이블을 가지고 있습니다.하고있다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(*)"에 따르면, 저는 또한 테이블을 최적화하려고 노력했지만 성공하지 못했습니다.

최적화하는 방법은 무엇입니까?COUNT(*)InnoDB에서의 성능?

MySQL 5.1.6부터는 Event Scheduler를 사용하고 정기적으로 카운트를 통계 테이블에 삽입할 수 있습니다.

먼저 카운트를 유지할 테이블을 만듭니다.

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 또는 큐 없음)을 제공합니다.

당분간은 이 근사치를 사용하여 문제를 해결했습니다.

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

행의 대략적인 수는rows위와 같이 InnoDB를 사용할 때의 설명 계획의 열.MyISAM을 사용할 때 테이블 참조가 최적화되고 있으므로 비어 있으면 기존 상태로 돌아갑니다.SELECT COUNT대신.

@Che code를 기반으로 트리거를 사용할 수도 있습니다.INSERT등에UPDATE로.perf2통계 테이블의 값을 실시간으로 최신 상태로 유지할 수 있습니다.

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

그러면:

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테이블은 이 쿼리를 사용하여 실시간으로 읽을 수 있습니다.

SELECT `value` FROM `stats` WHERE `key` = 'perf2_count';

이를 통해 성능 문제를 해결할 수 있습니다.COUNT(*)데이터가 변경될 때만 실행됩니다.perf2.

언급URL : https://stackoverflow.com/questions/19267507/how-to-optimize-count-performance-on-innodb-by-using-index