데이터베이스 질문과 답변
안녕하세요~ 미세먼지때문에 날씨도 흐리흐리한데 다들 건강하시죠?
---------------------------------------------------------------------------------------------------------
다름이 아니라 Maria DB 10.2를 사용중인데 Log성 데이터가 기록되는 한 테이블이 있습니다.
그런데 너무 느려서 따로 인덱스를 잡고 기존 5분이 걸리던걸 5초내에 나왔었는데,
한달지났는데 데이터가 누적되면서 다시 느려져서(20초내외) 문의드립니다.
현재 테이블 구조는 아래와 같습니다.
[테이블구조]
| Field | Type | Collation | Null | Key | index(btree) |
| 사이트 코드 | int(11) | NO | MUL | 1 | |
| 프로파일 코드 | varchar(20) | utf8_general_ci | YES | 2 | |
| 프로파일 명 | varchar(300) | utf8_general_ci | YES | ||
| 결과수신일 | varchar(8) | utf8_general_ci | NO | ||
| 캠페인 | varchar(300) | utf8_general_ci | YES | ||
| 국가 | varchar(50) | utf8_general_ci | YES | 3 | |
| 광고 매체 | varchar(300) | utf8_general_ci | YES | MUL | 4 |
| TERM | varchar(1000) | utf8_general_ci | YES | ||
| CONTENT | varchar(300) | utf8_general_ci | YES | ||
| T1 | decimal(9,0) | YES | |||
| ....... | decimal(9,0) | YES | |||
| ..... | decimal(9,0) | YES | |||
| T25 | decimal(9,0) | YES | |||
| 등록일 | datetime | NO | |||
| 등록자 | varchar(50) | utf8_general_ci | NO |
이 테이블을 조회했을 시
[단순 조회쿼리]
SELECT T1, T2, T3, T4, T5, 사이트 코드, 국가, 프로파일 코드, 광고 매체, 결과수신일
FROM TABLE
속도가 20초 소요(풀 스캔 할 수 밖에 없는 구조)
------------------------------------------------------------------------------------------------------------------------------
[참조]카디널리티
SELECT
COUNT(DISTINCT(사이트_코드)) AS 사이트_코드,
COUNT(DISTINCT(국가)) AS 국가,
COUNT(DISTINCT(프로파일_코드)) AS 프로파일_코드,
COUNT(DISTINCT(광고매체)) AS 광고매체,
COUNT(DISTINCT(결과수신일)) AS 결과수신일
FROM
TABLE;
[결과]
| 사이트코드 | 국가 | 프로파일_코드 | 광고매체 | 결과수신일 |
| 28 | 242 | 8 | 13050 | 239 |
------------------------------------------------------------------------------------------------------------------------------
인덱스를 저렇게 잡은 이유는 리포트 쿼리에서
JOIN을 걸떄 사이트코드,국가,프로파일코드,광고매체,결과수신일이 키로 사용되기 때문입니다.
보통 인덱스 생성시 가장 가지수가 많은 것부터 인덱스를 거는 것이 정석으로 알고 있으나,
해당 부분에선 더 느려지더군요..
이와 같을 때 성능 향상을 위해 할 수 있는 방법 이 무엇이 있을까요?
예를 들면 Index를 바꾼다던지 파티셔닝을 이용한다던지 등의 방법...
(정규화가 제대로 되지 않은 DB 구조입니다)
도움 말씀 부탁드리며, 늘 건강하세요.
SELECT
COUNT(DISTINCT(사이트_코드)) AS 사이트_코드,
COUNT(DISTINCT(국가)) AS 국가,
COUNT(DISTINCT(프로파일_코드)) AS 프로파일_코드,
COUNT(DISTINCT(광고매체)) AS 광고매체,
COUNT(DISTINCT(결과수신일)) AS 결과수신일
FROM
RP_SITE_RESULT_SOURCE_MEDIUM;
이게 쿼리 전부인가요?
이건 해당 컬럼별 카디널리티 보시라고 조회해본거에요^^
실제 본 쿼리는 테이블 두개 내츄럴 조인에 그룹핑 합니다만, 나머지 한 테이블은 건수가 몇건 되지 않아 성능에 영향을 미치지 않는 것으로 파악하고 있습니다.
네추럴 조인 쓰는데가 있나보네요. 제가 마리아db 를 몰라서..
전체양은 몇건인지 궁금하네요.
풀스캔 탈수밖에 없다 하시면 인덱스가 의미가 없지요.
실시간 데이터가 필요한게 아니시면 테이블 만들어서 batch로 가져가시는 방법도 고민해보세요.
답변 감사합니다.
해당 테이블은 현재 약 300만건 정도이며 계속 일일 배치를 통해 데이터를 수신해오기 때문에
더욱 늘어날 것으로 추정됩니다.
report에서 사용 되어지는 쿼리이기 때문에 실시간 데이터가 필요하며, 따로 테이블을 구성하려고 해도..
이미 설계 자체가 그런 것을 고려하지 않고 진행 되었기 때문에, 일이 너무 방대해 지지 않을까 노심초사 걱정됩니다.
(실제 운영 중이기 때문에 설계변경은 리스크가 너무 큽니다)
풀스캔 타는데 인덱스를 건 것은 타 테이블과 조인시 인덱스를 안주었을 때는 5분 이상 걸렸으나, 강제 인덱싱을 하고
태우니 3초 밖에 안걸리더라구요..
현재 큰 문제점은 3초면 적당한 수준이나, 시간이 지날수록(데이터 량은 비슷) 느려진다라는 점에 있습니다..
그래서 이런 상황에선 어떤 방안을 가지고 튜닝을 해야할지 궁금했었습니다~
도움 말씀 감사합니다.
1. 테이블 하나만 볼게 아니라 두개 다 인덱스를 살펴봐야 합니다.
2. 조인 조건 외에 검색조건에 대한 인덱스는 없는지도 살펴봐야 하구요.
3. 그룹핑을 한다면? (조인>그룹핑) 이 아닌 (그룹핑>조인) 으로 바꿀 수는 없는지도 고려해야죠.
도움말씀 감사합니다^^
1. 테이블 하나만 볼게 아니라 두개 다 인덱스를 살펴봐야 합니다.
-> sub 테이블이 50건 정도 밖에 안되는데도 인덱스 영향을 받나요?(릴레이션 관계x)
2. 조인 조건 외에 검색조건에 대한 인덱스는 없는지도 살펴봐야 하구요.
3. 그룹핑을 한다면? (조인>그룹핑) 이 아닌 (그룹핑>조인) 으로 바꿀 수는 없는지도 고려해야죠.
-> 감사합니다. 고려하여 무슨 문제인지 파악 해보겠습니다^^
1. 건수가 적으면 성능에 영향이 없고 인덱스도 필요 없다???
- 기본적으로 PK 는 있어야 하고요.
- 작은 테이블이라도 조인의 경우엔 성능애 영향이 있을 수 있습니다.
2. 조인은 하는데 관계가 없다???
- 조인을 한다는 것 자체가 관계가 있는 거죠.
3. 전체 자료가 필요한가요?
- 검색조건으로 일부만 검색하지 않나요?
) 버튼을 클릭하여 작성 하시면 됩니다.