MySQL 그룹이 느려지는 이유는 무엇입니까?

MySQL 그룹이 느려지는 이유는 무엇입니까?

20M 행에 가까워지는 분할 테이블(월별)에 대해 문의하려고 합니다.date(transaction_utc)와 country_id로 그룹화해야 합니다.그룹을 끄고 집약을 하면 반환되는 행은 40k를 조금 넘습니다.이는 많지 않지만 그룹을 추가하면 해당 GROUP BY가 transaction_utc 열에 없는 한 쿼리가 상당히 느려지고 이 경우 FAST가 됩니다.

쿼리나 인덱스를 조정하여 아래의 첫 번째 쿼리를 최적화하려고 했지만(처음보다 약 2배 빠름) 45k 행을 요약하기 위한 5s 쿼리는 여전히 너무 많은 것 같습니다.

참고로 이 상자는 서버상의 인덱스 공간보다 훨씬 더 많은 INODB 버퍼 풀을 사용할 수 있는 새로운 24개의 논리 코어, 64GB RAM, Mariadb-5.5.x 서버이므로 RAM이나 CPU에 대한 부담이 없습니다.

그래서 저는 무엇이 이렇게 느려지는지 아이디어와 속도를 높일 수 있는 방법을 찾고 있습니다.피드백을 주시면 감사하겠습니다. : )

자, 자세한 내용은...

다음 쿼리(실제로 필요한 쿼리)는 약 5초(+/-)가 소요되며 100개 미만의 행을 반환합니다.

SELECT lss.`country_id` AS CountryId
, Date(lss.`transaction_utc`) AS TransactionDate
, c.`name` AS CountryName,  lss.`country_id` AS CountryId
, COALESCE(SUM(lss.`sale_usd`),0) AS SaleUSD
, COALESCE(SUM(lss.`commission_usd`),0) AS CommissionUSD  
FROM `sales` lss  
JOIN `countries` c ON lss.`country_id` = c.`country_id`  
WHERE ( lss.`transaction_utc` BETWEEN '2012-09-26' AND '2012-10-26' AND lss.`username` = 'someuser' )  GROUP BY lss.`country_id`, DATE(lss.`transaction_utc`)

동일한 쿼리에 대한 EXPLY SELECT는 다음과 같습니다.transaction_utc 키를 사용하지 않습니다.대신 내 커버링 인덱스를 사용해야 하는 거 아니야?

id  select_type table   type    possible_keys   key key_len ref rows    Extra
1   SIMPLE  lss ref idx_unique,transaction_utc,country_id   idx_unique  50  const   1208802 Using where; Using temporary; Using filesort
1   SIMPLE  c   eq_ref  PRIMARY PRIMARY 4   georiot.lss.country_id  1   

이제 무슨 일이 일어나고 있는지 알아내려고 했던 몇 가지 다른 방법들을 생각해 봅시다

다음 쿼리(그룹 변경 기준)에는 약 5초(+/-)가 소요되며 3행만 반환됩니다.

SELECT lss.`country_id` AS CountryId
, DATE(lss.`transaction_utc`) AS TransactionDate
, c.`name` AS CountryName,  lss.`country_id` AS CountryId
, COALESCE(SUM(lss.`sale_usd`),0) AS SaleUSD
, COALESCE(SUM(lss.`commission_usd`),0) AS CommissionUSD  
FROM `sales` lss  
JOIN `countries` c ON lss.`country_id` = c.`country_id`  
WHERE ( lss.`transaction_utc` BETWEEN '2012-09-26' AND '2012-10-26' AND lss.`username` = 'someuser' )  GROUP BY lss.`country_id`

다음 쿼리(삭제된 그룹 기준)에는 4-5초(+/-)가 소요되며 1개의 행을 반환합니다.

SELECT lss.`country_id` AS CountryId
    , DATE(lss.`transaction_utc`) AS TransactionDate
    , c.`name` AS CountryName,  lss.`country_id` AS CountryId
    , COALESCE(SUM(lss.`sale_usd`),0) AS SaleUSD
    , COALESCE(SUM(lss.`commission_usd`),0) AS CommissionUSD  
    FROM `sales` lss  
    JOIN `countries` c ON lss.`country_id` = c.`country_id`  
    WHERE ( lss.`transaction_utc` BETWEEN '2012-09-26' AND '2012-10-26' AND lss.`username` = 'someuser' )

다음 쿼리에는 .00X초(+/-)가 소요되며 최대 45k개의 행을 반환합니다.최대 45K개의 행을 100개 미만의 그룹으로 그룹화하려고 합니다(초기 쿼리와 같이).

SELECT lss.`country_id` AS CountryId
    , DATE(lss.`transaction_utc`) AS TransactionDate
    , c.`name` AS CountryName,  lss.`country_id` AS CountryId
    , COALESCE(SUM(lss.`sale_usd`),0) AS SaleUSD
    , COALESCE(SUM(lss.`commission_usd`),0) AS CommissionUSD  
    FROM `sales` lss  
    JOIN `countries` c ON lss.`country_id` = c.`country_id`  
    WHERE ( lss.`transaction_utc` BETWEEN '2012-09-26' AND '2012-10-26' AND lss.`username` = 'someuser' )
GROUP BY lss.`transaction_utc`

표 스키마:

CREATE TABLE IF NOT EXISTS `sales` (
  `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
  `user_linkshare_account_id` int(11) unsigned NOT NULL,
  `username` varchar(16) NOT NULL,
  `country_id` int(4) unsigned NOT NULL,
  `order` varchar(16) NOT NULL,
  `raw_tracking_code` varchar(255) DEFAULT NULL,
  `transaction_utc` datetime NOT NULL,
  `processed_utc` datetime NOT NULL ,
  `sku` varchar(16) NOT NULL,
  `sale_original` decimal(10,4) NOT NULL,
  `sale_usd` decimal(10,4) NOT NULL,
  `quantity` int(11) NOT NULL,
  `commission_original` decimal(10,4) NOT NULL,
  `commission_usd` decimal(10,4) NOT NULL,
  `original_currency` char(3) NOT NULL,
  PRIMARY KEY (`id`,`transaction_utc`),
  UNIQUE KEY `idx_unique` (`username`,`order`,`processed_utc`,`sku`,`transaction_utc`),
  KEY `raw_tracking_code` (`raw_tracking_code`),
  KEY `idx_usd_amounts` (`sale_usd`,`commission_usd`),
  KEY `idx_countries` (`country_id`),
  KEY `transaction_utc` (`transaction_utc`,`username`,`country_id`,`sale_usd`,`commission_usd`)
) ENGINE=InnoDB  DEFAULT CHARSET=utf8
/*!50100 PARTITION BY RANGE ( TO_DAYS(`transaction_utc`))
(PARTITION pOLD VALUES LESS THAN (735112) ENGINE = InnoDB,
 PARTITION p201209 VALUES LESS THAN (735142) ENGINE = InnoDB,
 PARTITION p201210 VALUES LESS THAN (735173) ENGINE = InnoDB,
 PARTITION p201211 VALUES LESS THAN (735203) ENGINE = InnoDB,
 PARTITION p201212 VALUES LESS THAN (735234) ENGINE = InnoDB,
 PARTITION pMAX VALUES LESS THAN MAXVALUE ENGINE = InnoDB) */ AUTO_INCREMENT=19696320 ;

문제가 되는 부분은 아마GROUP BY DATE(transaction_utc)당신은 이 쿼리에 대한 커버리지 인덱스를 가지고 있다고 주장하지만, 저는 아무것도 보이지 않습니다.5열 인덱스에 쿼리에 사용된 모든 열이 있지만 최상의 순서는 아닙니다(즉, 다음과 같습니다).WHERE-GROUP BY-SELECT).

따라서 엔진은 유용한 인덱스를 찾지 못해 모든 20M 행에 대해 이 함수를 평가해야 합니다.실제로, 이것은 다음과 같이 시작하는 인덱스를 찾습니다.username(the)idx_unique그리고 그것을 사용하기 때문에 (120만 행에 대해서만) 함수를 평가해야 합니다.(transaction_utc) ★★★(username, transaction_utc)세 가지 중에서 가장 유용한 것을 선택할 것이다.

열을 날짜 및 시간 부분으로 분할하여 테이블 구조를 변경할 수 있습니까?경우 의 합니다.(username, country_id, transaction_date)의 컬럼의 ), (그룹화에 사용되는2개의 의 순서 변경),(username, transaction_date, country_id)꽤 효율적일 겁니다.

(username, country_id, transaction_date, sale_usd, commission_usd)더 좋아.

---

현재 구조를 유지하려면 5열 색인 내의 순서를 다음과 같이 변경해 보십시오.

(username, country_id, transaction_utc, sale_usd, commission_usd)

또는 대상:

(username, transaction_utc, country_id, sale_usd, commission_usd)

---

MariaDB를 사용 중이므로 기존 열을 변경하지 않고 열 기능을 사용할 수 있습니다.

가상(영구적) 열과 적절한 인덱스를 추가합니다.

ALTER TABLE sales 
    ADD COLUMN transaction_date DATE NOT NULL
               AS DATE(transaction_utc) 
               PERSISTENT 
    ADD INDEX special_IDX 
        (username, country_id, transaction_date, sale_usd, commission_usd) ;

언급URL : https://stackoverflow.com/questions/13103850/why-is-my-mysql-group-by-so-slow