단순 SELECT 쿼리가 큰 테이블에서 느림

단순 SELECT 쿼리가 큰 테이블에서 느림

다음과 같은 구조의 테이블이 있습니다.

SHOW CREATE TABLE data_temperature;

CREATE TABLE `data_temperature` (
  `temperature_id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
  `created` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `data_id` bigint(20) unsigned NOT NULL,
  `x_id` varchar(32) DEFAULT NULL,
  `x_sn` varchar(16) DEFAULT NULL,
  `x_unit` char(1) DEFAULT NULL,
  `x_value` decimal(6,2) DEFAULT NULL,
  PRIMARY KEY (`temperature_id`),
  KEY `created` (`created`),
  KEY `data_id` (`data_id`),
  KEY `x_value` (`x_value`)
) ENGINE=InnoDB AUTO_INCREMENT=6274618 DEFAULT CHARSET=latin1

여기서 데이터를 가져오는 것은 매우 느린 기본적인 질문입니다.그래서 저는 이 질문을 더 간단한 용어로 나누었고 이 매우 간단한 질문이 느리다는 것을 발견했습니다(17.52초).

SELECT data_temperature.x_value FROM data_temperature WHERE data_temperature.created BETWEEN '2015-02-02 18:28:42' AND '2015-03-04 18:28:42';

이 테이블에는 6,274,617개의 행이 있습니다.실은.SELECT COUNT(*) FROM data_temperature또한 3.66초가 걸립니다.

이 쿼리가 실행 중인 시스템은 Ubuntu 14.04를 실행하는 쿼드코어, 4GB RAM, 솔리드 스테이트 드라이브인 내 개발 시스템입니다.

이와 같은 쿼리를 실행하는 데 걸리는 시간에 대한 것입니까, 아니면 제가 잘못하고 있는 것입니까?데이터를 더 효율적으로 반환할 수 있는 방법이 있습니까?

출력에 몇 개의 행이 있는지 생각해 보십시오.이러한 행에 사용된 디스크 공간을 생각해 보십시오.디스크가 얼마나 느리게 실행되는지 생각해 보십시오.그 모든 행으로 무엇을 할 것인지 생각해 보십시오.17초가 적당합니다.

마찬가지로 COUNT(*)는 대부분의 요인 때문에 3.66초가 걸렸습니다.

좀 더 깊이 파고 들어보겠습니다.

SELECT COUNT(*) FROM tblInnoDB 테이블에서 인덱스 중 하나를 완전히 검색하여 행 수를 계산합니다.인덱스는 약 100MB일 수 있습니다.이미 캐시된 모든 것을 제외하고 디스크에서 100MB를 가져와야 했습니다.그리고 그것은 6M의 각 행을 살펴야 했습니다. 그것이 진행되는 동안 숫자를 세면서 말이죠.합계: 3.66초.

이제 다른 질문을 살펴보겠습니다.그것은 더 복잡하기 때문에 더 느립니다.

WHERE 조항에 대한 좋은 색인을 가지고 있습니다.INDEX(생성됨).그것은 BTree에 있습니다.먼저 '2015-02-02 18:28:42' 이후의 첫 번째 항목을 찾습니다.그런 다음 '2015-03-04 18:28:42'에 도달할 때까지 선형 스캔합니다.이 작업은 아마도 3.66초 미만이 소요될 것입니다.그렇지만.....

인덱스의 각 항목에 대해 다음을 검색해야 합니다.value이것은 먼저 기본 키를 찾는 것으로 얻을 수 있습니다.temperature_id바로 옆에 있는 같은 BTree에 있습니다.created사용temperature_id다른 BTree, 즉 PK와 데이터가 있는 BTree를 통과하여 해당 행을 찾습니다.거기서 발견됩니다.value이것은 매우 반복됩니다.created사정권에 있는이러한 조회에 필요한 블록은 훨씬 더 많은 MB의 데이터입니다.

SELECT를 더 빨리 실행할 수 있지만 이는 다음 한 가지 쿼리에만 도움이 됩니다.INDEX(created, value)이것은 "포함" 색인입니다.즉, SELECT에 필요한 모든 열이 인덱스에서 발견됩니다.따라서 다른 BTree에 도달할 필요가 없습니다.이 경우 시간이 3.66초 이하가 될 수 있습니다.

언급URL : https://stackoverflow.com/questions/28870832/simple-select-query-is-slow-with-large-table