현재 약 5 백만 개의 레코드를 포함하는 계속 늘어나는 테이블을 다루고 있습니다. 매일 약 100,000 개의 새 레코드가 추가됩니다.
이 테이블에는 광고 캠페인에 대한 정보가 포함되어 있으며 쿼리를 통해 다른 테이블과 결합됩니다.
CREATE TABLE `statistics` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`ip_range_id` int(11) DEFAULT NULL,
`campaign_id` int(11) DEFAULT NULL,
`payout` decimal(5,2) DEFAULT NULL,
`is_converted` tinyint(1) unsigned NOT NULL DEFAULT '0',
`converted` datetime DEFAULT NULL,
`created` datetime DEFAULT NULL,
PRIMARY KEY (`id`),
KEY `created` (`created`),
KEY `converted` (`converted`),
KEY `campaign_id` (`campaign_id`),
KEY `ip_range_id` (`ip_range_id`),
) ENGINE=InnoDB DEFAULT CHARSET=latin1;
다른 테이블에는 IP 범위가 포함되어 있습니다.
CREATE TABLE `ip_ranges` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`ip_range` varchar(11) NOT NULL,
PRIMARY KEY (`id`),
KEY `ip_range` (`ip_range`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;
집계 쿼리는 다음과 같습니다.
SELECT
SUM(`payout`) AS `revenue`,
(SELECT COUNT(*) FROM `statistics` WHERE `ip_range_id` = `IpRange`.`id`) AS `clicks`,
(SELECT COUNT(*) FROM `statistics` WHERE `ip_range_id` = `IpRange`.`id` AND `is_converted` = 1) AS `conversions`
FROM `ip_ranges` AS `IpRange`
INNER JOIN `statistics` AS `Statistic` ON `IpRange`.`id` = `Statistic`.`ip_range_id`
GROUP BY `IpRange`.`id`
ORDER BY `clicks` DESC
LIMIT 20
쿼리를 완료하는 데 약 20 초가 걸립니다.
이것은 EXPLAIN이 반환하는 것입니다.
id select_type table type possible_keys key key_len ref rows Extra
1 PRIMARY ip_range index PRIMARY PRIMARY 4 NULL 306552 Using index; Using temporary; Using filesort
1 PRIMARY statistic ref ip_range_id ip_range_id 5 db.ip_range.id 8 Using where
3 DEPENDENT SUBQUERY statistics ref ip_range_id ip_range_id 5 func 8 Using where
2 DEPENDENT SUBQUERY statistics ref ip_range_id ip_range_id 5 func 8 Using where; Using index
ip_ranges 테이블의 클릭 및 전환을 추가 열로 캐싱하는 것은 옵션이 아닙니다. campaign_id 열 (및 향후 다른 열)도 필터링 할 수 있어야하기 때문입니다. 따라서 이러한 집계는 다소 실시간이어야합니다.
여러 차원에서 거의 실시간으로 큰 테이블에서 집계를 수행하는 가장 좋은 전략은 무엇입니까?
필자는 단순히 쿼리를 더 좋게 만들려는 것은 아니지만 다른 데이터베이스 시스템 (NoSQL)을 포함하거나 다른 서버에 데이터를 배포하는 등의 전략에도 관심이 있습니다.
쿼리가 지나치게 복잡해 보입니다. 동일한 테이블을 반복해서 쿼리 할 필요가 없습니다.
select
sum(payout) as revenue,
count(*) as clicks,
sum(s.is_converted = 1) as conversions
from ip_ranges r
inner join statistics s on r.id = s.ip_range_id
group by r.id
order by clicks desc
limit 20;
수정 (승인 후) : 다음과 같은 작업을 처리하는 방법에 대한 실제 질문에 관해서는 :
테이블의 모든 데이터 를보고 결과를 최신 상태로 유지하려고합니다 . 그러면 모든 데이터를 읽는 것 외에 다른 옵션이 없습니다 (전체 테이블 스캔). 테이블이 넓다면 (즉, 많은 열이있는 경우) 커버링 인덱스 (예 : 관련된 모든 열을 포함하는 인덱스)를 생성 할 수 있으므로 테이블을 읽는 대신 인덱스를 읽습니다. 글쎄, 또 뭐야? 전체 테이블 스캔에서는 내가 아는 한 MySQL이 제공하지 않는 병렬 액세스를 사용하는 것이 좋습니다. 따라서 다른 DBMS로 전환 할 수 있습니다. 그런 다음 DBMS가 제공하는 다른 기능을 확인하십시오. 병렬 쿼리는 테이블을 분할하면 도움이 될 수 있습니다. 마지막으로 떠오르는 것은 하드웨어, 즉 더 많은 CPU, 더 빠른 드라이브 등입니다.
또 다른 옵션은 테이블에서 오래된 데이터를 제거하는 것입니다. 올해의 세부 정보가 필요하지만 이전 연도의 집계 데이터 만 필요하다고 가정 해보십시오. 그런 다음 필요한 합계와 개수 만 포함하는 다른 테이블 old_statistics를 만듭니다.
table old_statistics
(
ip_range_id,
revenue,
conversions
);
그런 다음 통계에서 데이터를 집계합니다.이 데이터는 현재 연도의 데이터 만 보유 할 것이기 때문에 훨씬 더 작을 것이고, 결과를 얻기 위해 old_statistics를 추가합니다.
이 기사는 인터넷에서 수집됩니다. 재 인쇄 할 때 출처를 알려주십시오.
침해가 발생한 경우 연락 주시기 바랍니다[email protected] 삭제
몇 마디 만하겠습니다