데이터베이스 질문과 답변
1. 테이블 구조 및 데이터 예시
- tableA -> 마스터 테이블
key : varchar. pk.
| key |
|---|
| a1 |
| b1 |
| c1 |
| d1 |
- tableB -> tableA 와 1:1 관계
key : varchar. pk - tableA의 key와 동일한 값.
date : datetime
| key | date |
|---|---|
| a1 | 2020-04-01 15:00:00 |
| b1 | 2019-08-01 15:00:00 |
| c1 | 2019-04-01 15:00:00 |
| d1 | 2018-08-01 15:00:00 |
- tableC -> tableA 와 1:N 관계
key : varchar. pk - tableA의 key와 동일한 값.
ymd : varchar. pk - 연월일 데이터
hh : varchar. pk - 시 데이터
data : decimal. 사용량 데이터
| key | ymd | hh | data |
|---|---|---|---|
| a1 | 20190402 | 00 | 10 |
| a1 | 20190402 | 01 | 11 |
| a1 | 20190402 | 02 | 12 |
| b1 | 20190402 | 00 | 7 |
| b1 | 20190402 | 01 | 8 |
| b1 | 20190402 | 02 | 9 |
| c1 | 20190402 | 00 | 15 |
| c1 | 20190402 | 01 | 16 |
| c1 | 20190402 | 02 | 17 |
| d1 | 20190402 | 00 | 20 |
| d1 | 20190402 | 01 | 21 |
| d1 | 20190402 | 02 | 22 |
| ... | ... | ... | ... |
| a1 | 20200402 | 00 | 10 |
| a1 | 20200402 | 01 | 11 |
| a1 | 20200402 | 02 | 12 |
| b1 | 20200402 | 00 | 7 |
| b1 | 20200402 | 01 | 8 |
| b1 | 20200402 | 02 | 9 |
| c1 | 20200402 | 00 | 15 |
| c1 | 20200402 | 01 | 16 |
| c1 | 20200402 | 02 | 17 |
| d1 | 20200402 | 00 | 20 |
| d1 | 20200402 | 01 | 21 |
| d1 | 20200402 | 02 | 22 |
- tableD -> tableA 와 1:N 관계
key : varchar. pk - tableA의 key와 동일한 값.
ymd : varchar. pk - 연월일 데이터
ym : varchar. 예측 대상 월
data : decimal. 지난 몇주 간 사용량을 토대로 산출한 금월 예측 사용량
| key | ymd | ym | data |
|---|---|---|---|
| a1 | 202004 | 202004 | 6000 |
| b1 | 202004 | 202004 | 5500 |
| c1 | 202004 | 202004 | 6500 |
| d1 | 202004 | 202004 | 7000 |
접근해서 데이터를 가져올 테이블은 이렇게 4가지구요.
테이블 추가 설명을 드리자면,
tableA는 IoT 장비 마스터 정보 테이블
tableB는 tableA의 부가 정보 테이블인데 장비 설치일이 포함(위 예시에서 date) - 레코드수는 약 1억건
tableC는 장비 별로 수집한 사용량 데이터를 1시간 단위로 집계한 테이블
tableD는 장비 별로 수집한 사용량 데이터를 토대로 예측한 월 사용량을 기록한 테이블
요구 사항은 이렇습니다.
1. 모든 장비들의 금일 사용량 평균(0시부터 조회시점 기준 1시전 전 00분) - ex) 14시 37분에 조회했다면 0시~13시까지의 사용량 평균
2. 모든 장비들의 전년 동일 날짜 동일 시간 사용량 평균 - 2020년 4월 2일 14시 37분에 조회했다면 2019년 4월 2일 00시~13시까지의 사용량 평균
3. 1, 2를 비교한 증감률
4. 모든 장비들의 예측 월 사용량 평균
현재 사용 중인 쿼리
SELECT IFNULL(T.avgUse,0) AS avgUse , IFNULL(T.lastAvgUse,0) AS lastAvgUse , ABS(IFNULL((ROUND(((T.avgUse / T.lastAvgUse) * 100), 1) - 100),0)) AS cost , IFNULL(T.predictData,0) AS predictData , CASE WHEN IFNULL((ROUND(((T.avgUse / T.lastAvgUse) * 100), 1) - 100),0) > 0 THEN 'U' WHEN IFNULL((ROUND(((T.avgUse / T.lastAvgUse) * 100), 1) - 100),0) = 0 THEN 'N' WHEN IFNULL((ROUND(((T.avgUse / T.lastAvgUse) * 100), 1) - 100),0) < 0 THEN 'D' ELSE '' END AS upAndDown FROM ( SELECT IFNULL(ROUND(SUM(C.data) / 1000 / COUNT(DISTINCT(A.key)), 0), 0) AS avgUse , ( SELECT IFNULL(ROUND(SUM(C.data) / 1000 / COUNT(DISTINCT(A.key)), 0), 0) AS avgUse FROM tableA A , tableB B , tableC C WHERE A.key = C.key AND A.key = B.key AND C.ymd = DATE_FORMAT(SUBDATE(NOW(), INTERVAL 1 YEAR), '%Y%m%d') AND C.hh <= DATE_FORMAT(SUBDATE(NOW(), INTERVAL 1 HOUR), '%H') AND B.date <= DATE_FORMAT(DATE_SUB(DATE_SUB(NOW(), INTERVAL 1 YEAR), INTERVAL 1 HOUR), '%Y-%m-%d %H:00:00') AND C.data > 0 ) AS lastAvgUse , ( SELECT IFNULL(ROUND( (SUM(D.data) / 1000 / COUNT(A.key)) / DATE_FORMAT(LAST_DAY(NOW()),'%d') , 0), 0) AS predictData FROM tableA A , tableB B , tableD D WHERE A.key = B.key AND A.key = D.key AND D.ymd = DATE_FORMAT(NOW(), '%Y%m%d') AND B.date <= DATE_FORMAT(DATE_SUB(NOW(), INTERVAL 1 HOUR), '%Y-%m-%d %H:00:00') AND D.data > 0 ) AS predictData FROM tableA A , tableB B , tableC C WHERE A.key = C.key AND A.key = B.key AND C.ymd = DATE_FORMAT(NOW(), '%Y%m%d') AND C.hh <= DATE_FORMAT(SUBDATE(NOW(), INTERVAL 1 HOUR), '%H') AND B.date <= DATE_FORMAT(DATE_SUB(NOW(), INTERVAL 1 HOUR), '%Y-%m-%d %H:00:00') AND C.data > 0 ) T;
이런 쿼리를 현재 사용하고 있는데 약 3초 정도 걸리는데 1초 이내로 줄이는 것이 목표입니다.....만 도저히 방법이 떠오르질 않네요.
고수님들의 도움을 기다리겠습니다ㅠㅠ
실제 수행 후, xplan 을 올려주시는게
도움 받기 더 좋으실 것 같습니다.
mysql에서 explain format=json 해서 나온 실행계획 정보입니다!
{
"query_block" : {
"select_id" : 1,
"table" : {
"access_type" : "system",
"filtered" : 100,
"materialized_from_subquery" : {
"cacheable" : true,
"dependent" : false,
"query_block" : {
"nested_loop" : [
{
"table" : {
"access_type" : "ALL",
"attached_condition" : "((`schema`.`A`.`col1` = 'col1_data') and (`schema`.`A`.`col2` = 'col2_data') and (`schema`.`A`.`col3` = 'col3_data'))",
"filtered" : 100,
"possible_keys" : [
"PRIMARY",
"key_otherColumn_index",
"key"
],
"rows" : 10200,
"table_name" : "A"
}
},
{
"table" : {
"access_type" : "eq_ref",
"attached_condition" : "(`schema`.`B`.`date` <= <cache>(date_format((now() - interval 1 hour),'%Y-%m-%d %H:00:00')))",
"filtered" : 100,
"key" : "PRIMARY",
"key_length" : "62",
"possible_keys" : [
"PRIMARY"
],
"ref" : [
"schema.A.key"
],
"rows" : 1,
"table_name" : "B",
"used_key_parts" : [
"key"
]
}
},
{
"table" : {
"access_type" : "ref",
"attached_condition" : "((`schema`.`C`.`ymd` = '20200403') and (`schema`.`C`.`hh` <= <cache>(date_format((now() - interval 1 hour),'%H'))) and (`schema`.`C`.`data` > 0))",
"filtered" : 100,
"key" : "PRIMARY",
"key_length" : "88",
"possible_keys" : [
"PRIMARY",
"key_ymd_hh_index"
],
"ref" : [
"schema.A.key",
"const"
],
"rows" : 12,
"table_name" : "C",
"used_key_parts" : [
"key",
"ymd"
]
}
}
],
"select_id" : 2,
"select_list_subqueries" : [
{
"cacheable" : true,
"dependent" : false,
"query_block" : {
"nested_loop" : [
{
"table" : {
"access_type" : "ALL",
"attached_condition" : "((`schema`.`A`.`col1` = 'col1_data') and (`schema`.`A`.`col2` = 'col2_data') and (`schema`.`A`.`col3` = 'col3_data'))",
"filtered" : 100,
"possible_keys" : [
"PRIMARY",
"key_otherColumn_index",
"key"
],
"rows" : 10200,
"table_name" : "A"
}
},
{
"table" : {
"access_type" : "eq_ref",
"attached_condition" : "(`schema`.`B`.`date` <= <cache>(date_format((now() - interval 1 hour),'%Y-%m-%d %H:00:00')))",
"filtered" : 100,
"key" : "PRIMARY",
"key_length" : "62",
"possible_keys" : [
"PRIMARY"
],
"ref" : [
"schema.A.key"
],
"rows" : 1,
"table_name" : "B",
"used_key_parts" : [
"key"
]
}
},
{
"table" : {
"access_type" : "eq_ref",
"attached_condition" : "((`schema`.`D`.`ymd` = '20200403') and (`schema`.`D`.`data` > 0))",
"filtered" : 100,
"key" : "PRIMARY",
"key_length" : "88",
"possible_keys" : [
"PRIMARY"
],
"ref" : [
"schema.A.key",
"const"
],
"rows" : 1,
"table_name" : "D",
"used_key_parts" : [
"key",
"ymd"
]
}
}
],
"select_id" : 4
}
},
{
"cacheable" : true,
"dependent" : false,
"query_block" : {
"nested_loop" : [
{
"table" : {
"access_type" : "ALL",
"attached_condition" : "((`schema`.`A`.`col1` = 'col1_data') and (`schema`.`A`.`col2` = 'col2_data') and (`schema`.`A`.`col3` = 'col3_data'))",
"filtered" : 100,
"possible_keys" : [
"PRIMARY",
"key_otherColumn_index",
"key"
],
"rows" : 10200,
"table_name" : "A"
}
},
{
"table" : {
"access_type" : "eq_ref",
"attached_condition" : "(`schema`.`B`.`date` <= <cache>(date_format(((now() - interval 1 year) - interval 1 hour),'%Y-%m-%d %H:00:00')))",
"filtered" : 100,
"key" : "PRIMARY",
"key_length" : "62",
"possible_keys" : [
"PRIMARY"
],
"ref" : [
"schema.A.key"
],
"rows" : 1,
"table_name" : "B",
"used_key_parts" : [
"key"
]
}
},
{
"table" : {
"access_type" : "ref",
"attached_condition" : "((`schema`.`C`.`ymd` = '20190403') and (`schema`.`C`.`hh` <= <cache>(date_format((now() - interval 1 hour),'%H'))) and (`schema`.`C`.`data` > 0))",
"filtered" : 100,
"key" : "PRIMARY",
"key_length" : "88",
"possible_keys" : [
"PRIMARY",
"key_ymd_hh_index"
],
"ref" : [
"schema.A.key",
"const"
],
"rows" : 12,
"table_name" : "C",
"used_key_parts" : [
"key",
"ymd"
]
}
}
],
"select_id" : 3
}
}
]
},
"using_temporary_table" : true
},
"rows" : 1,
"table_name" : "T"
}
}
}
요구사항 1,2번은 쿼리로, 3,4번은 어플리케이션에서 처리하도록 하세요.
쿼리는 C 테이블이 드라이빙 되도록 인덱스 ymd, hh 칼럼 순서대로 복합 인덱스를 잡아주시면 어느정도 될듯 하네요.
그런데 이런건 돈받고 해줘야 할거 같은데요 ㅎㅎ
3번은 1,2번 결과를 가지고 하는 거라서 어플리케이션에서 처리가 가능은 한데, 4번은 별도의 로직이 돌아서 예측 결과를 D테이블에 담거든요.. 사실 1,2번 조회와는 관련이 없다니 아예 별개의 쿼리로 따로 떼는게 나으려나 싶기도 하네요.
말씀하신거처럼 ymd, hh 순서로 인덱스를 추가로 잡고 싶으나 테이블에 레코드가 1억건 정도에 최소 15분에 한번씩은 CRUD가 일어나는 테이블이라서 인덱스 작업은 일단 보류입니다ㅠ
a, b, c, d 를 조인하는데? a, b 조인이 필요한가요? c, d 만 있으면 될 것 같은데요?
d 는 YM 이라고 설명되어 있는데 쿼리에는 YMD 로 쓰고 있네요? 뭐가 맞는 건지?
SELECT avgUse
, IFNULL(lastAvgUse, 0) lastAvgUse
, ABS(IFNULL((ROUND(((avgUse / lastAvgUse) * 100), 1) - 100),0)) cost
, predictData
, CASE SIGN(IFNULL((ROUND(((avgUse / lastAvgUse) * 100), 1) - 100), 0))
WHEN 1 THEN 'U'
WHEN 0 THEN 'N'
WHEN -1 THEN 'D'
END upAndDown
FROM (SELECT IFNULL(CASE gb WHEN 1 THEN avgUse END, 0) avgUse
, CASE gb WHEN 0 THEN avgUse END lastAvgUse -- 0으로 나누는 오류 방지하기 위해 ifnull 처리 안함
FROM (SELECT ymd
, ROUND(SUM(data) / 1000 / COUNT(DISTINCT(key)), 0) AS avgUse
, CASE ymd WHEN DATE_FORMAT(NOW(), '%Y%m%d') THEN 1 ELSE 0 END gb
FROM tableC
WHERE ymd IN ( DATE_FORMAT(NOW(), '%Y%m%d')
, DATE_FORMAT(SUBDATE(NOW(), INTERVAL 1 YEAR), '%Y%m%d') )
AND HH < DATE_FORMAT(NOW(), '%H')
AND data > 0
GROUP BY ymd
) c
) c
, (SELECT IFNULL(ROUND((SUM(data) / 1000 / COUNT(key)) / DATE_FORMAT(LAST_DAY(NOW()),'%d'), 0), 0) AS predictData
FROM tableD
-- WHERE ymd = DATE_FORMAT(NOW(), '%Y%m%d')
WHERE ym = DATE_FORMAT(NOW(), '%Y%m')
AND data > 0
) d
;
본문에서는 너무 복잡해져서 생략했지만 a 테이블에는 사용 여부 등의 다른 조건이 걸려 있고, b 테이블에는 설치일 정보가 있어 a, b에 join을 걸 수밖에 없는 상황입니다. a, b테이블의 조건에 따라서 c, d 테이블에 데이터가 존재하더라도 걸러야하는 부분이 있습니다.
운영중이다보니 컬럼명이랑 정보를 단순화시키는 과정에서 D테이블 정보를 잘못 적었었네요.
본문 수정하고 수정한 부분 파란색으로 표시했습니다.
D테이블의 경우 하루에 한번 이번달 예측 사용량을 산출하는 로직이 배치로 돌고 있구요. 그 결과만 기록하는 테이블입니다.
그래서 key, ymd(배치가 돈 날짜), ym(예측 대상 월), data(예측량) 이렇게 되어 있고 key와, ymd가 pk로 잡혀 있습니다.
a1 20200401 202004 6000
a1 20200402 202004 6100
a1 20200403 202004 6050
이런 식으로 매일 예측량이 조금씩 변화하기에 오늘 날짜의 예측량 데이터를 가지고 오는 부분입니다
SELECT avgUse
, IFNULL(lastAvgUse, 0) lastAvgUse
, ABS(IFNULL((ROUND(((avgUse / lastAvgUse) * 100), 1) - 100),0)) cost
, predictData
, CASE SIGN(IFNULL((ROUND(((avgUse / lastAvgUse) * 100), 1) - 100), 0))
WHEN 1 THEN 'U'
WHEN 0 THEN 'N'
WHEN -1 THEN 'D'
END upAndDown
FROM (SELECT IFNULL(ROUND(AVG(d1) / 1000 / DATE_FORMAT(LAST_DAY(NOW()),'%d'), 0), 0) AS predictData
, IFNULL(ROUND(AVG(c1) / 1000, 0), 0) AS avgUse
, ROUND(AVG(c2) / 1000, 0) AS lastAvgUse
FROM (SELECT a.key
, d.data d1
, SUM(CASE WHEN c.ymd = DATE_FORMAT(NOW(), '%Y%m%d') THEN c.data END) c1
, SUM(CASE WHEN c.ymd < DATE_FORMAT(NOW(), '%Y%m%d') THEN c.data END) c2
FROM tableA a
, tableB b
, tableC c
, tableD d
WHERE a.key = b.key
AND a.key = c.key
AND a.key = d.key
AND c.ymd IN ( DATE_FORMAT(NOW(), '%Y%m%d')
, DATE_FORMAT(SUBDATE(NOW(), INTERVAL 1 YEAR), '%Y%m%d') )
AND c.HH < DATE_FORMAT(NOW(), '%H')
AND b.date < DATE_FORMAT(SUBDATE(NOW(), INTERVAL 1 YEAR), '%Y-%m-%d %H:00:00')
AND d.ymd = DATE_FORMAT(NOW(), '%Y%m%d')
AND c.data > 0
AND d.data > 0
GROUP BY a.key, d.data
) a
) a
;
작성해주신 쿼리로 변경해서 돌려봤는데 시간이 더 오래 걸리네요
구조적인 개선이 필요할 것같다고 말씀드리고 일단 튜닝은 보류했습니다.
시간 내서 고민하시고 덧글 달아주셨는데 힘 빠지게 만든거 아닌가 싶어 죄송해지네요ㅠ
감사합니다 좋은 하루 보내세요!
) 버튼을 클릭하여 작성 하시면 됩니다.