데이터베이스 질문과 답변
쿼리는 다음과 같습니다. 약 11초씩이나 걸리는;;;
SELECT /* SQL_CALC_FOUND_ROWS STRAIGHT_JOIN SQL_BIG_RESULT SQL_NO_CACHE */
A.ParentGroupID
, B.`Name`
, B.`Type`
, (SELECT `Name` FROM SoftwareDist WHERE DistID = B.DistID LIMIT 1) AS DistName
, B.Company
, COUNT(A.ParentGroupID) `설치수량`
, COUNT(C.BuyID) `정식수량`
, ROUND(COUNT(C.BuyID)*(SUM(IFNULL(D.BuyPrice,0))/CASE COUNT(D.GroupID) WHEN 0 THEN 1 ELSE COUNT(D.GroupID) END) )`정식금액 (정식수량 * 평균단가)`
, SUM(CASE B.Type WHEN 3 THEN 1 ELSE 0 END) `프리수량`
, COUNT(A.ParentGroupID) - COUNT(C.BuyID) - SUM(CASE B.Type WHEN 3 THEN 1 ELSE 0 END) `불법수량(설치수량 - 정식수량 - 프리수량)`
, ROUND((COUNT(A.ParentGroupID) - COUNT(C.BuyID) - SUM(CASE B.Type WHEN 3 THEN 1 ELSE 0 END))*(SUM(IFNULL(E.BuyPrice,0))/CASE COUNT(E.GroupID) WHEN 0 THEN 1 ELSE COUNT(E.GroupID) END)) `구매 예상비용(불법 수량 * 최종구매단가)`
, (SELECT Sam FROM SoftwareDetail WHERE GroupID = B.GroupID LIMIT 1 ) AS Sam
, B.AssetCode
FROM `SWPCInstallInfoView` A
JOIN SoftwareGroup B ON B.GroupID = A.ParentGroupID AND (B.RegFlag IS NOT NULL OR B.RegFlag != 2) AND (B.`Type` IS NULL OR B.`TYPE` != 4)
LEFT JOIN SWLicenseMID C ON C.GroupID = B.GroupID AND C.MID = A.MID AND BuyID IS NOT NULL
LEFT JOIN SWBuyInfo D ON D.GroupID = B.GroupID
LEFT JOIN (SELECT GroupID, BuyPrice
FROM SWBuyInfo AA
WHERE BuyNumber = (SELECT BuyNumber FROM SWBuyInfo WHERE GroupID = AA.GroupID ORDER BY BuyDate DESC LIMIT 1)) E ON E.GroupID = B.GroupID
LEFT JOIN SoftwareDist F ON F.DistID = B.DistID
GROUP BY A.ParentGroupID, B.`Name`, B.`Type`, B.Company, B.AssetCode
ORDER BY A.ParentGroupID
튜닝을 해보려고 하는데 잘 안되네요;; 원본 쿼리는 이것 보다 더 지저분하고 느리지만;;
EXPLAIN EXTENDED 결과 첨부 합니다.
전문가분들의 지도 편달 부탁드립니다.
아참 빼먹은 swpcinstallinfoview 테이블 입니다.
CREATE
ALGORITHM = UNDEFINED
DEFINER = `root`@`%`
SQL SECURITY DEFINER
VIEW `swpcinstallinfoview` AS
SELECT straight_join sql_cache
`softwareinfo`.`MID` AS `MID`,
`softwareid`.`ParentGroupID` AS `ParentGroupID`
FROM
(`softwareinfo`
JOIN `softwareid` ON ((`softwareid`.`AppID` = `softwareinfo`.`AppID`)))
GROUP BY `softwareinfo`.`MID` , `softwareid`.`ParentGroupID`
쿼리는 조금 보기가 힘들고;;
플랜만 보면, 최종 ID 1레벨을 보면 건수가 별로 없네요.
그럼 서브에서 필터링이 많이 되지 않았다는 것 같은데,
서브에 해당하는 것에 타입을 보면 풀스캔 1건, 인덱스 전체 읽는 것 1건이 있는데
풀스캔은 전체 건수가 얼마 없고 인덱스 전체 읽는 것이 건수가 많이 있네요.
7번째 softwareinfo 테이블 조회 하는 부분이 인덱스를 타고 있는 것이 아니라 인덱스를 전체 읽고 있습니다.
이게 ref(b.group_id) type으로 drived 된 것 보면 건수(67건)가 별로 없는데,
이 정보를 가지고 튜닝하면 속도가 많이 올라 갈 것 같습니다.
softwareinfo 테이블 조회하는 서브 질의(인라인뷰)를 처음부터 조건절로 건수를 줄여 보세요.
플랜은 원본 질의에 대한 것인가요~ @.@)?
통계를 생성하기위한 기초 테이블 입니다.
두 테이블의 관계가 1:m이고, 또다른 두테이블의 관계가 1:n 일 경우
이 세 테이블을 한거번에 조인하면
조인시 1:m:n 이 되면서 데이터가 원치않게 복사됩니다.
위 쿼리는 카티션 프러덕이 발생할 것으로 예상됩니다.
즉, 위와 같이 조인하면 안될것 같습니다.
PK 리스트 입니다
SoftwareInfo(MID, AppID) 설명: MID 는 PC의 번호, AppID는 프로그램 ID
SoftwareId(AppID)
SoftwareGroup(GroupID) 설명: GroupID는 AppID의 대표 번호
SWLicenseMID(MID, GroupID)
SWBuyInfo(GroupID, BuyNumber)
아참 SWPCInstallInfoView 는 뷰테이블 입니다;;
SELECT straight_join sql_cache
softwareinfo.MID AS MID,
softwareid.ParentGroupID AS ParentGroupID
FROM softwareinfo
JOIN softwareid ON softwareid.AppID = softwareinfo.AppID
GROUP BY softwareinfo.MID , softwareid.ParentGroupID
그룹바이 한 후 조인하셔야 할 듯 하네요.
- 변경전 : 조인 > Group By
- 변경전 : Group By > 조인
먼저 GroupBy 후
1) GroupBy + 카운트만
2) 1)의 결과에 BuyInfo를 이용한 평균값을 구함
문제
2)에서 1) 결과에 평균값을 구하려면 GroupID가 필요한데 그러면
3) 다시 GroupBy 가 있어야 함
그래서
서브쿼리로 평균값을 구하려고했더니 잘;; 안되눈;;
열시미 하고 있습니당;;
Using temporary, Using file sort 발생하면 쿼리 실행 시간이 많이 발생합니다.
위 부분이 발생하지 않도록 하면 시간을 줄일수 있습니다.
.
아래와 같이 실행 한다음 실행값(profile ) 을 올려 주실수 있는지요
SELECT straight_join sql_cache
softwareinfo.MID AS MID,
softwareid.ParentGroupID AS ParentGroupID
FROM softwareinfo
JOIN softwareid ON softwareid.AppID = softwareinfo.AppID
GROUP BY softwareinfo.MID , softwareid.ParentGroupI
mysql> SHOW PROFILES;
mysql> SHOW PROFILE FOR QUERY 1;!
그리고 위 쿼리 explain 까지 같이
mysql> SHOW PROFILES;
+----------+------------+-----------------------------------------------------------+
| Query_ID | Duration | Query |
+----------+------------+-----------------------------------------------------------+
| 1 | 1.69080625 | SELECT straight_join sql_cache
softwareinfo.MID AS MID,
softwareid.ParentGroupID AS ParentGroupID
FROM softwareinfo
JOIN softwareid ON softwareid.AppID = softwareinfo.AppID
GROUP BY softwareinfo.MID , softwareid.ParentGroupID |
+----------+------------+-----------------------------------------------------------+
1 row in set, 1 warning (0.00 sec)
mysql> SHOW PROFILE FOR QUERY 1;
+------------------------------+----------+
| Status | Duration |
+------------------------------+----------+
| Creating sort index | 0.000408 |
| Waiting for query cache lock | 0.000002 |
| Creating sort index | 0.000403 |
| Waiting for query cache lock | 0.000002 |
| Creating sort index | 0.000420 |
| Waiting for query cache lock | 0.000005 |
| Creating sort index | 0.000410 |
| Waiting for query cache lock | 0.000002 |
| Creating sort index | 0.000406 |
| Waiting for query cache lock | 0.000002 |
| Creating sort index | 0.000416 |
| Waiting for query cache lock | 0.000002 |
| Creating sort index | 0.000403 |
| Waiting for query cache lock | 0.000002 |
| Creating sort index | 0.000414 |
| Waiting for query cache lock | 0.000002 |
| Creating sort index | 0.000414 |
| Waiting for query cache lock | 0.000002 |
| Creating sort index | 0.000414 |
| Waiting for query cache lock | 0.000002 |
| Creating sort index | 0.000428 |
| Waiting for query cache lock | 0.000002 |
| Creating sort index | 0.000435 |
| Waiting for query cache lock | 0.000002 |
| Creating sort index | 0.000406 |
| Waiting for query cache lock | 0.000002 |
| Creating sort index | 0.000404 |
| Waiting for query cache lock | 0.000002 |
| Creating sort index | 0.000418 |
| Waiting for query cache lock | 0.000002 |
| Creating sort index | 0.000406 |
| Waiting for query cache lock | 0.000005 |
| Creating sort index | 0.000408 |
| Waiting for query cache lock | 0.000002 |
| Creating sort index | 0.000413 |
| Waiting for query cache lock | 0.000002 |
| Creating sort index | 0.000394 |
| Waiting for query cache lock | 0.000002 |
| Creating sort index | 0.000381 |
| Waiting for query cache lock | 0.000002 |
| Creating sort index | 0.000377 |
| Waiting for query cache lock | 0.000002 |
| Creating sort index | 0.000392 |
| Waiting for query cache lock | 0.000004 |
| Creating sort index | 0.000378 |
| Waiting for query cache lock | 0.000003 |
| Creating sort index | 0.000378 |
| Waiting for query cache lock | 0.000002 |
| Creating sort index | 0.000391 |
| Waiting for query cache lock | 0.000002 |
| Creating sort index | 0.000378 |
| Waiting for query cache lock | 0.000002 |
| Creating sort index | 0.000379 |
| Waiting for query cache lock | 0.000002 |
| Creating sort index | 0.000395 |
| Waiting for query cache lock | 0.000002 |
| Creating sort index | 0.000379 |
| Waiting for query cache lock | 0.000002 |
| Creating sort index | 0.000380 |
| Waiting for query cache lock | 0.000002 |
| Creating sort index | 0.000379 |
| Waiting for query cache lock | 0.000002 |
| Creating sort index | 0.000391 |
| Waiting for query cache lock | 0.000002 |
| Creating sort index | 0.000386 |
| Waiting for query cache lock | 0.000002 |
| Creating sort index | 0.000380 |
| Waiting for query cache lock | 0.000002 |
| Creating sort index | 0.000394 |
| Waiting for query cache lock | 0.000003 |
| Creating sort index | 0.000380 |
| Waiting for query cache lock | 0.000002 |
| Creating sort index | 0.000378 |
| Waiting for query cache lock | 0.000002 |
| Creating sort index | 0.000392 |
| Waiting for query cache lock | 0.000002 |
| Creating sort index | 0.000378 |
| Waiting for query cache lock | 0.000002 |
| Creating sort index | 0.000379 |
| Waiting for query cache lock | 0.000002 |
| Creating sort index | 0.000378 |
| Waiting for query cache lock | 0.000002 |
| Creating sort index | 0.000389 |
| Waiting for query cache lock | 0.000002 |
| Creating sort index | 0.000380 |
| Waiting for query cache lock | 0.000002 |
| Creating sort index | 0.000378 |
| Waiting for query cache lock | 0.000002 |
| Creating sort index | 0.000389 |
| Waiting for query cache lock | 0.000002 |
| Creating sort index | 0.000396 |
| Waiting for query cache lock | 0.000002 |
| Creating sort index | 0.094565 |
| end | 0.000014 |
| removing tmp table | 0.000009 |
| end | 0.000005 |
| query end | 0.000009 |
| closing tables | 0.000014 |
| freeing items | 0.000020 |
| cleaning up | 0.000100 |
+------------------------------+----------+
100 rows in set, 1 warning (0.00 sec)
정확하게 보려면 RESET QUERY CACH 하신후 다시 profile 을 뜨셔야할것 같습니다.
같은 쿼리 라도 캐싱이 된 상태에서 하면 아래처럼 정확하지 않습니다.
위의 profile을 보면 Waiting for query cache lock 너무 많습니다. 버그 일지도 모르지만
OLTP 환경이 아니라면 query cache 사이즈를 확인해볼필요가 있을것 같습니다.
mysql> SHOW PROFILES;
+----------+------------+-----------------------------------------------------------+
| Query_ID | Duration | Query |
+----------+------------+-----------------------------------------------------------+
| 1 | 0.00019225 | RESET QUERY CACHE |
| 2 | 0.00016500 | SET PROFILING = 1 |
| 3 | 1.64354150 | SELECT straight_join sql_cache
softwareinfo.MID AS MID,
softwareid.ParentGroupID AS ParentGroupID
FROM softwareinfo
JOIN softwareid ON softwareid.AppID = softwareinfo.AppID
GROUP BY softwareinfo.MID, softwareid.ParentGroupID |
| 4 | 1.63982025 | SELECT straight_join sql_no_cache
softwareinfo.MID AS MID,
softwareid.ParentGroupID AS ParentGroupID
FROM softwareinfo
JOIN softwareid ON softwareid.AppID = softwareinfo.AppID
GROUP BY softwareinfo.MID, softwareid.ParentGroupID |
+----------+------------+-----------------------------------------------------------+
4 rows in set, 1 warning (0.00 sec)
mysql> SHOW PROFILE FOR QUERY 4;
+--------------------------------+----------+
| Status | Duration |
+--------------------------------+----------+
| starting | 0.000031 |
| Waiting for query cache lock | 0.000007 |
| init | 0.000006 |
| checking query cache for query | 0.000124 |
| checking permissions | 0.000009 |
| checking permissions | 0.000009 |
| Opening tables | 0.000043 |
| init | 0.000054 |
| System lock | 0.000019 |
| optimizing | 0.000023 |
| statistics | 0.000054 |
| preparing | 0.000032 |
| Creating tmp table | 0.000050 |
| Sorting result | 0.000009 |
| executing | 0.000007 |
| Sending data | 1.417025 |
| Creating sort index | 0.235793 |
| end | 0.000012 |
| removing tmp table | 0.000009 |
| end | 0.000004 |
| query end | 0.000009 |
| closing tables | 0.000014 |
| freeing items | 0.000021 |
| cleaning up | 0.000023 |
+--------------------------------+----------+
24 rows in set, 1 warning (0.00 sec)
sql_no_cache 힌트로 위와 같은 결과가 나왔습니다.
SELECT STRAIGHT_JOIN SQL_BIG_RESULT SQL_NO_CACHE A.ParentGroupID , B.`Name` , B.`Type` , (SELECT `Name` FROM SoftwareDist WHERE DistID = B.DistID LIMIT 1) AS DistName , B.Company , COUNT(A.ParentGroupID) `설치수량` , COUNT(C.BuyID) `정식수량` , ROUND(COUNT(C.BuyID)*(SUM(IFNULL(D.BuyPrice,0))/CASE COUNT(D.GroupID) WHEN 0 THEN 1 ELSE COUNT(D.GroupID) END) )`정식금액 (정식수량 * 평균단가)` , SUM(CASE B.Type WHEN 3 THEN 1 ELSE 0 END) `프리수량` , COUNT(A.ParentGroupID) - COUNT(C.BuyID) - SUM(CASE B.Type WHEN 3 THEN 1 ELSE 0 END) `불법수량(설치수량 - 정식수량 - 프리수량)` , ROUND((COUNT(A.ParentGroupID) - COUNT(C.BuyID) - SUM(CASE B.Type WHEN 3 THEN 1 ELSE 0 END))*(SUM(IFNULL(E.BuyPrice,0))/CASE COUNT(E.GroupID) WHEN 0 THEN 1 ELSE COUNT(E.GroupID) END)) `구매 예상비용(불법 수량 * 최종구매단가)` , (SELECT Sam FROM SoftwareDetail WHERE GroupID = B.GroupID LIMIT 1 ) AS Sam , B.AssetCode FROM SWPCInstallInfoView A JOIN SoftwareGroup B FORCE INDEX(PRIMARY) ON B.GroupID = A.ParentGroupID AND (B.RegFlag IS NOT NULL OR B.RegFlag != 2) AND (B.`Type` IS NULL OR B.`TYPE` != 4) LEFT JOIN SWLicenseMID C ON C.MID = A.MID AND C.GroupID = B.GroupID AND BuyID IS NOT NULL LEFT JOIN SWBuyInfo D ON D.GroupID = B.GroupID LEFT JOIN (SELECT GroupID, BuyPrice FROM SWBuyInfo AA WHERE BuyNumber = (SELECT BuyNumber FROM SWBuyInfo WHERE GroupID = AA.GroupID ORDER BY BuyDate DESC LIMIT 1)) E ON E.GroupID = B.GroupID LEFT JOIN SoftwareDist F ON F.DistID = B.DistID GROUP BY A.ParentGroupID, B.`Name`, B.`Type`, B.Company, B.AssetCode ORDER BY A.ParentGroupID
아직도 헤메는 중이지만 약간의 성능향상은 되었습니다.;; Duration / Fetch 2.906 sec / 0.906 sec 좀더 튜닝해서 1초대로 가고 싶은데 잘 안되네요;;
마농님의 조언을 적용 하고 싶은데 잘 안되구 있음;;
) 버튼을 클릭하여 작성 하시면 됩니다.