2개의 조인 및 구별로 그룹화된 mysql 쿼리 최적화
10~20초 걸리는 문의가 있습니다만, 최적화할 수 있다고 확신하고 있습니다.그렇게 할 수 있는 것은 아닙니다.유사한 문의에 적용할 수 있도록 도움과 설명을 부탁드립니다.여기 질문이 있습니다.
SELECT
`store_formats`.`Store Nbr`,
`store_formats`.`Store Name`,
`store_formats`.`Format Name`,
`eds_sales`.`Date`,
sum(`eds_sales`.`EPOS Sales`) AS Sales,
sum(`eds_sales`.`EPOS Quantity`) AS Quantity
FROM
`eds_sales`
INNER JOIN `item_codes` ON `eds_sales`.`Prime Item Nbr` = `item_codes`.`Customer Item`
INNER JOIN `store_formats` ON `eds_sales`.`Store Nbr` = `store_formats`.`Store Nbr`
WHERE
`eds_sales`.`Store Nbr` IN ($storenbr) AND
`eds_sales`.`Date` BETWEEN '$startdate' AND '$enddate' AND
`eds_sales`.`Client` = '$customer' AND
`eds_sales`.`Retailer` IN ($retailer) AND
`store_formats`.`Format Name` IN ($storeformat) AND
`item_codes`.`Item Number` IN ($products)
GROUP BY
`store_formats`.`Store Name`,
`store_formats`.`Store Nbr`,
`store_formats`.`Format Name`,
`eds_sales`.`Date`
다음은 설명 출력입니다.
보시다시피 저는 별로 성공하지 못한 컬럼으로 몇 가지 인덱스를 작성했습니다.임시 테이블로 복사하는 것이 주된 지연이라고 생각합니다.
관련된 표는 다음과 같습니다.
store_module:
CREATE TABLE `store_formats` (
`id` int(12) NOT NULL,
`Store Nbr` smallint(5) UNSIGNED DEFAULT NULL,
`Store Name` varchar(27) DEFAULT NULL,
`City` varchar(19) DEFAULT NULL,
`Post Code` varchar(9) DEFAULT NULL,
`Region #` int(2) DEFAULT NULL,
`Region Name` varchar(10) DEFAULT NULL,
`Distr #` int(3) DEFAULT NULL,
`Dist Name` varchar(26) DEFAULT NULL,
`Square Footage` varchar(7) DEFAULT NULL,
`Format` int(1) DEFAULT NULL,
`Format Name` varchar(23) DEFAULT NULL,
`Store Type` varchar(20) DEFAULT NULL,
`TV Region` varchar(12) DEFAULT NULL,
`Pharmacy` varchar(3) DEFAULT NULL,
`Optician` varchar(3) DEFAULT NULL,
`Home Shopping` varchar(3) DEFAULT NULL,
`Retailer` varchar(15) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
ALTER TABLE `store_formats`
ADD PRIMARY KEY (`id`),
ADD UNIQUE KEY `uniqness` (`Store Nbr`,`Store Name`,`Format`),
ADD KEY `Store Nbr_2` (`Store Nbr`,`Format Name`,`Store Name`);
eds_sales:
CREATE TABLE `eds_sales` (
`id` int(12) UNSIGNED NOT NULL,
`Prime Item Nbr` mediumint(7) NOT NULL,
`Prime Item Desc` varchar(255) NOT NULL,
`Prime Size Desc` varchar(255) NOT NULL,
`Variety` varchar(255) NOT NULL,
`WHPK Qty` int(5) NOT NULL,
`SUPPK Qty` int(5) NOT NULL,
`Depot Nbr` int(5) NOT NULL,
`Depot Name` varchar(50) NOT NULL,
`Store Nbr` smallint(5) UNSIGNED NOT NULL,
`Store Name` varchar(255) NOT NULL,
`EPOS Quantity` smallint(3) NOT NULL,
`EPOS Sales` decimal(13,2) NOT NULL,
`Date` date NOT NULL,
`Client` varchar(10) NOT NULL,
`Retailer` varchar(50) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1;
ALTER TABLE `eds_sales`
ADD UNIQUE KEY `uniqness` (`Prime Item Nbr`,`Prime Item Desc`,`Prime Size Desc`,`Variety`,`WHPK Qty`,`SUPPK Qty`,`Depot Nbr`,`Depot Name`,`Store Nbr`,`Store Name`,`Date`,`Client`) USING BTREE,
ADD KEY `Store Nbr` (`Store Nbr`),
ADD KEY `Prime Item Nbr_2` (`Prime Item Nbr`,`Date`),
ADD KEY `id` (`id`) USING BTREE,
ADD KEY `Store Nbr_2` (`Prime Item Nbr`,`Store Nbr`,`Date`,`Client`,`Retailer`) USING BTREE,
ADD KEY `Client` (`Client`,`Store Nbr`,`Date`),
ADD KEY `Date` (`Date`,`Client`,`Retailer`);
item_module:
CREATE TABLE `item_codes` (
`id` int(12) NOT NULL,
`Item Number` varchar(30) CHARACTER SET latin1 NOT NULL,
`Customer Item` mediumint(7) NOT NULL,
`Description` varchar(255) CHARACTER SET latin1 NOT NULL,
`Status` varchar(15) CHARACTER SET latin1 NOT NULL,
`Customer` varchar(30) CHARACTER SET latin1 NOT NULL,
`Sort Name` varchar(255) CHARACTER SET latin1 NOT NULL,
`EquidataCustomer` varchar(30) CHARACTER SET latin1 NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
ALTER TABLE `item_codes`
ADD PRIMARY KEY (`id`),
ADD UNIQUE KEY `uniq` (`Item Number`,`Customer Item`,`Customer`,`EquidataCustomer`),
ADD KEY `Item Number_2` (`Item Number`,`Sort Name`,`EquidataCustomer`),
ADD KEY `Customer Item` (`Customer Item`,`Item Number`,`Sort Name`,`EquidataCustomer`),
ADD KEY `Customer Item_2` (`Customer Item`,`Item Number`,`EquidataCustomer`);
그래서 질문: 보시다시피 저는 3개의 테이블에 참여하게 되었습니다.매장 형식별로 날짜별 매출을 찾고 있습니다.저는 다른 종류의 가입을 시도하고 있습니다.예를 들어, sales를 item_codes나 store_formats에 가입시키는 대신 store_formats를 다른 것에 가입시키는 등, 같은 결과를 얻고 있습니다.어플리케이션의 Select Box에 의해 입력되는 IN을 사용한 변수 배열도 전달하고 있습니다.
- 이러한 테이블을 결합하는 가장 좋은 방법
- 테이블당 최적의 인덱스 제안
- 왜 임시 테이블이 생기는 거죠?그룹 by 때문인가요? 해결 방법이 있나요?
- 임시 테이블이 필요한 경우 이 작성 속도를 높일 수 있는 방법이 있습니까? (이미 8개의 디스크로 구성된 RAID에 데이터 폴더가 있지만 여전히 느립니다.
- 물론 어떤 대안이라도 환영입니다.
UPDATE: 코멘트에서 제안하는 내용으로 테이블을 갱신했습니다.
업데이트 : my.cnf를 퍼포먼스 향상으로 변경(램은 8GB, 코어 2개, /data/tmp는 8드라이브 RAID로 데이터 위치와 동일)
tmpdir = /dev/shm/:/data/tmp:/tmp
lc-messages-dir = /usr/share/mysql
skip-external-locking
expire_logs_days = 10
max_binlog_size = 100M
innodb_buffer_pool_size = 6G
innodb_buffer_pool_instances = 6
query_cache_type=1
(댓글을 다는 것은 무리입니다.답변을 사용하는 것에 대해 양해해 주십시오.
가지고 있을 때INDEX(a)그리고.INDEX(a,b)전자는 용장성이므로 삭제해야 합니다.그런 경우가 5건 정도 있어요.
각각store_nbr딱 하나 있다store_name이 경우, 이 기능을 사용하는 것은 장황합니다.store_name둘 이상의 테이블에 있습니다.의 의도를 모르겠다store_formats하지만 내 생각엔 저 테이블이 저 테이블인 것 같아store_name두 데이터 타입의 사이즈가 일치하지 않는 것에 주의해 주세요.store_name의 컬럼과store_nbr열!
모든 스토어에 고유 번호가 있어야 할 것 같습니다. 있다면 고유 키 추가uniqness )Store Nbr ,Store Name)은, 다음과 같이 변환될 가능성이 있습니다.PRIMARY KEY(store_nbr)(죄송합니다만, 컬럼명에 공백은 넣지 않습니다.)
이 되지 KEY를 합니다.Date_2 )Date ,Client그 대신에, 를 추가합니다.INDEX(Client, store_nbr, Date); 이는 쿼리 속도에 직접적인 영향을 미칩니다. '아, 아, 아, 아, 아, 아, 아, 아, 아, 아, 아, 아, 아, 아, 아, 아.EXPLAIN SELECT...바
int(4) SMALLINT UNSIGNED
있다Datein a a a a UNIQUE (오류)PRIMARY의 키는 보통 "입니다.) 키는 보통 "displayed"입니다. 같은 날 두 번 고객'이 같은 물건을 같은 날 두 번 구입한 것은 무엇입니까?
변경 후 좀 더 이야기를 나누도록 하겠습니다.
일관성을 ★★★★★★★★★★★★★★★★★★★★★★★★★★★★★★★★★★★★★★★★★★★★★★★★★★★★★★★★★★★★★★★SHOW CREATE TABLE.
이 구성을 피하십시오.
FROM ( SELECT ... )
JOIN ( SELECT ... ) ON ...
이 방법은 비효율적입니다. 왜냐하면 두 서브쿼리 중 어느 쪽에도JOIN율적입입니니다
선택 항목을 하위 쿼리로 이동하여 결합 항목을 최소화합니다.MySQL은 이미 당신을 위해 그것을 해줬을 것입니다.저는 그 정보를 위해 실행 계획을 확인하겠습니다.
SELECT
stores.nbr, stores.name, stores.format,
epos.date,
sum(epos.sales) AS Sales,
sum(epos.qty) AS Quantity
FROM
(SELECT `Date` as `date`, `EPOS Sales` as sales,`EPOS Quantity` as qty, `Prime Item Nbr` as item_number, `Store Nbr` as store_number
FROM
`eds_sales`
WHERE
`eds_sales`.`Store Nbr` IN ($storenbr) AND
`eds_sales`.`Date` BETWEEN '$startdate' AND '$enddate' AND
`eds_sales`.`Client` = '$customer' AND
`eds_sales`.`Retailer` IN ($retailer)) as epos
INNER JOIN
(SELECT `Customer Item` as custItem
FROM `item_codes`
WHERE
`item_codes`.`Item Number` IN ($products)) as items ON epos.item_number = items.custItem
INNER JOIN
(SELECT `Store Nbr` as nbr, `Store Name` as name, `Format Name` as format
FROM
`store_formats`
WHERE
`store_formats`.`Format Name` IN ($storeformat)) as stores ON epos.store_number = stores.nbr
GROUP BY
stores.name,
stores.nbr,
stores.format,
epos.date
한다.WHERE의 조항ON§:
SELECT
`store_formats`.`Store Nbr`,
`store_formats`.`Store Name`,
`store_formats`.`Format Name`,
`eds_sales`.`Date`,
sum(`eds_sales`.`EPOS Sales`) AS Sales,
sum(`eds_sales`.`EPOS Quantity`) AS Quantity
FROM `eds_sales`
JOIN `item_codes`
ON `eds_sales`.`Prime Item Nbr` = `item_codes`.`Customer Item`
AND `item_codes`.`Item Number` IN ($products)
JOIN `store_formats`
ON `eds_sales`.`Store Nbr` = `store_formats`.`Store Nbr`
AND `store_formats`.`Format Name` IN ($storeformat)
WHERE `eds_sales`.`Store Nbr` IN ($storenbr)
AND `eds_sales`.`Date` BETWEEN '$startdate' AND '$enddate'
AND `eds_sales`.`Client` = '$customer'
AND `eds_sales`.`Retailer` IN ($retailer)
GROUP BY
`store_formats`.`Store Name`,
`store_formats`.`Store Nbr`,
`store_formats`.`Format Name`,
`eds_sales`.`Date`
다음 인덱스를 만듭니다.
CREATE INDEX IDX001 ON eds_sales (Client,`Store Nbr`,`Retailer`,`Date`);
CREATE INDEX IDX002 ON store_formats (`Store Nbr`,`Format Name`);
효과가 있으면 알려주시면 이유를 설명해 드릴게요.
언급URL : https://stackoverflow.com/questions/39293012/optimising-a-mysql-query-with-2-joins-and-group-by-clauses
'source' 카테고리의 다른 글
| 왼쪽 결합이 이중인 MySQL Update 행, 첫 번째 일치 제한 (0) | 2022.12.13 |
|---|---|
| Java Generics: 목록에 캐스트할 수 없는가? (0) | 2022.12.13 |
| PHP는 T_PAAMAYIM_NEKUDOTAYIM을 기대합니까? (0) | 2022.12.13 |
| Kohana 기반 웹사이트의 속도와 확장성 최적화 (0) | 2022.12.13 |
| 배열에서 처음 N개의 요소를 가져오는 방법 (0) | 2022.12.13 |