개요 : 테이블의 설계 원칙을 준수하지 않은 몇개 테이블 때문에 두 개의 테이블의 collation이 달라서 인덱스 스캔이 불가능한 문제가 발생한다.
결과 : 해결 방법은 테이블의 collation을 일치 시켜주는 것이고 임시 방편은 조인 조건절에 collation을 일치 시키기 위해서 collate문을 기술해 주는 것이나 collation에 따라 비교 방식이 달라서 똑같은 결과를 보장할 수 는 없다.
[튜닝 전]
- Table Collation이 utf8mb4_0900_bin과 utf8mb4_0900_ai_ci로 서로 달라서 인덱스를 타지 못하는 성능 문제가 발생함
- 비교 방식이 달라서 결과가 달라질 수 있으므로 테이블의 collation을 바꾸는 것이 기본 원칙임
- utf8mb4_0900_bin : 정확한 문자 비교
- utf8mb4_0900_ai_ci : 대소문자 구별 안함, '가' 와 'ㄱㅏ'를 같은 문자로 인식하나 '각', 'ㄱㅏㄱ', '가ㄱ' 은 다른 문자로 인식 ...
SELECT COUNT(*)
FROM MT_STR_MRG_M MRG
LEFT JOIN MT_STR_MRG_REQ REQ
ON MRG.STR_CD = REQ.STR_CD
AND MRG.MD_CD = REQ.MD_CD
AND MRG.CPCO_CD = REQ.CPCO_CD
...
WHERE 1=1
AND MRG.MD_CD. = '002907'
;
-> Aggregate: count(0) (actual time=6917228.154..1917228.154 rows=1 loops=1)
-> Nested loop left join (actual time=224.185..6917160.201 rows=31291 loops=1)
-> Index lookup on MRG using IX_MT_STR_MRG_M_02 (MD_CD='002927') (actual time=0.594..206.021 rows=31921 loops=1)
-> Filter: ((REQ.STR_CD=MRG.STR_CD) and (REQ.MD_CD=MRG.MD_CD) and (REQ.CPCO_CD=MRG.CPCO_CD) and ..) (actual time=184.314..216.687 rows=0 loops=31921)
-> Index range scan on REQ(re-planned for each iteration (actual time=0.076..181.705 rows=237637 loops=31921)
- 실행 계획에서 REG 테이블에서 인덱스를 탈 수 없어서 조인 조건이 필터 조건으로 사용되고 있고 각 조인시도마다 다시 실행계획을 계획하는 비효율이 발생함-> INDEX SCAN or TABLE FULL SCAN을 선택 못하는 문제가 발생하여 115분 소요, REG에 NO_INDEX 힌트 사용시 0.4초 걸림
[튜닝 후]
- 테이블 설계 원칙에 따라 두 테이블의 collation을 "utf8mb4_0900_bin" 으로 설정해야 함
- 임시 방편으로 collate 구문으로 collation을 변경함, 여기서 collate는 OUTER, INNER테이블 어디에 써도 상관없으므로 테이블 설계 원칙에 맞게 INNER 테이블 조인 조건에 collate구문을 기술함
SELECT COUNT(*)
FROM MT_STR_MRG_M MRG
LEFT JOIN MT_STR_MRG_REQ REQ
ON MRG.STR_CD = REQ.STR_CD COLLATE utf8mb4_0900_bin
AND MRG.MD_CD = REQ.MD_CD COLLATE utf8mb4_0900_bin
AND MRG.CPCO_CD = REQ.CPCO_CD COLLATE utf8mb4_0900_bin
...
WHERE 1=1
AND MRG.MD_CD. = '002907'
;
-> Aggregate: count(0) (actual time=275.567..275.567 rows=1 loops=1)
-> Nested loop left join (actual time=0.366..273.214 rows=31291 loops=1)
-> Index lookup MRG using IX_MT_STR_MRG_M_02 (MD_CD='002927') (actual time=0.346..85.274 rows=31921 loops=1)
-> Filter: ((MRG.STR_CD = REQ.STR_CD COLLATE utf8mb4_0900_bin) and (MRG.MD_CD = REQ.MD_CD COLLATE utf8mb4_0900_bin) and (MRG.CPCO_CD = REQ.CPCO_CD COLLATE utf8mb4_0900_bin) and ..) (actual time=0.006..0.006 rows=0 loops=31921)
-> Single-row covering index lookup on REQ using PRIMARY(STQ=MRG.STR_CD, MD_CD='002907', CPCO_CD=MRG.CPCO_CD, ...) (actual time=0.005..0.005 rows=0 loops=31921)
[re-planned for each iteration]
- 실행계획을 계획하지 못하는 re-planned for each iteration 문제는 NO_INDEX(REQ) 힌트를 주어 TABLE SCAN을 하게해서 해결할 수도 있음
- 실행계획을 제대로 설정하지 못하는 근본 원인은 두 테이블간의 collation이 다름에 있지만 Table scan으로 선택하지 못하는데서 옵티마이저의 한계가 있음을 알 수 있음
EXPLAIN ANALYZE
SELECT /*+NO_INDEX(REQ) */COUNT(*)
FROM MT_STR_MRG_M MRG
LEFT JOIN MT_STR_MRG_REQ REQ
ON MRG.STR_CD = REQ.STR_CD
AND MRG.MD_CD = REQ.MD_CD
AND MRG.CPCO_CD = REQ.CPCO_CD
...
WHERE 1=1
AND MRG.MD_CD = '002907'
;
-> Aggregate: count(0) (actual time=401.100..401.100 rows=1 loops=1)
-> left hash join (MRG.STR_CD = REQ.STR_CD), (MRG.MD_CD = REQ.MD_CD), (MRG.CPCO_CD = REQ.CPCO_CD) ... (actual time=197.258..399.114 rows=31291 loops=1)
-> Index lookup on MRG using IX_MT_STR_MRG_M_02 (MD_CD='002927') (actual time=0.867..176.482 rows=31921 loops=1)
-> Hash
-> Filter: ('002907' = REQ.MD_CD) (actual time=2.404..186.146 rows=9732 loops=1)
-> Table scan on REQ (actual time=0.077..162.028 rows=237637 loops=1)
[collation 비교 방식]
- utf8mb4_bin : 문자열의 코드 포인트 값을 바탕으로 비교, 후행 공백 인식 안함 (PAD SPACE)
- utf8mb4_0900_bin : 문자열의 utf8mb4 인코딩 값을 바탕으로 비교, utf8mb4_bin과 정렬 순서는 동일하지만, 처리 속도가 좀 더 빠름
- utf8mb4_0900_as_cs : DUCET(Default Unicode Collation Element Table) 가중치 데이터를 바탕으로 비교
[collation 요약]
- utf8mb4_0900_ai_ci : 대소문자 구별 안함, '가' vs 'ㄱㅏ' -> 같은 값으로 인식, '각' vs '가ㄱ' vs 'ㄱㅏㄱ' 은 다른 문자로 인식 이유는 데이터 비교 기준이 다름에 있음
- utf8mb4_bin : 대소문자 구별함, 후행 공백 인식을 안함, PAD SPACE 방식으로 공백을 붙여서 비교하여 'a' vs 'a '를 같은 문자로 인식
- utf8mb4_0900_as_cs : 대소문자 구별함, NO PAD, 단순히 대소문자가 잘 구분되는 정도
- utf8mb4_0900_bin : 대소문자 구별함, NO PAD, 모든 문자를 명확하게 구분하고자 할 때 (정확한 문자 비교)
-- NO PAD 방식, 결과 : 0 ('a' vs 'a ' -> 다름)
select 'a ' collate 'utf8mb4_0900_ai_ci' = 'a' collate 'utf8mb4_0900_ai_ci'; -- 0
select 'a ' collate 'utf8mb4_0900_as_cs' = 'a' collate 'utf8mb4_0900_as_cs'; -- 0
select 'a ' collate 'utf8mb4_0900_bin' = 'a' collate 'utf8mb4_0900_bin'; -- 0
-- PAD SPACE 방식, 결과 : 1 ('a' vs 'a ' -> 같음)
select 'a ' collate 'utf8mb4_bin' = 'a' collate 'utf8mb4_bin'; -- 1
-- utf8mb4_0900_ai_ci
-- '가' vs 'ㄱㅏ' -> 같음
select '가' collate 'utf8mb4_0900_ai_ci' = 'ㄱㅏ' collate 'utf8mb4_0900_ai_ci'; -- 1
-- '각' vs 'ㄱㅏㄱ' vs '가ㄱ' -> 다름
select '각' collate 'utf8mb4_0900_ai_ci' = 'ㄱㅏㄱ' collate 'utf8mb4_0900_ai_ci'; -- 0
select '각' collate 'utf8mb4_0900_ai_ci' = '가ㄱ' collate 'utf8mb4_0900_ai_ci'; -- 0
-- 'abc' vs 'ABC' -> 같음 -> 대소문자 구분 안함
select 'abc' collate 'utf8mb4_0900_ai_ci' = 'ABC' collate 'utf8mb4_0900_ai_ci'; -- 1
-- utf8mb4_0900_bin
-- 'abc' vs 'ABC' -> 다름 -> 대소문자 구분 함
select 'abc' collate 'utf8mb4_0900_bin' = 'ABC' collate 'utf8mb4_0900_bin'; -- 0
-- '가' vs 'ㄱㅏ' -> 다름 -> 정확한 문자 비교
select '가' collate 'utf8mb4_0900_bin' = 'ㄱㅏ' collate 'utf8mb4_0900_bin'; -- 0
* collation 참조 사이트 : yozm.wishket.com/magazine/detail/2746/
'MySQL > 튜닝' 카테고리의 다른 글
| MySQL self not exists문 -> 한 번만 읽고 처리 (0) | 2025.05.07 |
|---|---|
| MySQL 낮은 DISTINCT vs 컬럼 분포에 따른 인덱스 구성 컬럼 지정 (0) | 2025.05.02 |
| MySQL sub query 순서 조정 (0) | 2025.04.17 |
| MySQL LEFT(INNER) JOIN View 튜닝 정리 (0) | 2025.03.20 |
| MySQL slow_log (0) | 2025.03.13 |
