5.3 적절한 테이블 및 열 속성 설정으로 착한 쿼리 만들기

테이블 및 열의 속성을 변경하여 튜닝해보자

---

잘못된 열 속성으로 비효율적으로 작성한 나쁜 SQL 문

1. 튜닝 전

SELECT 부서명, 비고
FROM 부서
WHERE 비고 = 'active'
	AND ASCII(SUBSTR(비고,1,1)) = 97
    AND ASCII(SUBSTR(비고,2,1)) = 99;

 

2. 튜닝 전 실행 계획 분석

각 조건절 실행해서 소문자 여부를 판단하려고 한다는 것을 알았음

소문자 여부를 판단하려고 굳이 아스키 코드를 추출하는 함수까지 사용해야 될까??

 

3. 튜닝

열의 콜레이션 변경 (utf8_genereal_ci > UTF8MB4_bin) > substr(), ascii() 함수가 수행하던 불필요한 작업 제거

ALTER TABLE 부서
CHANGE COLUMN 비고 비고 VARCHAR(40) NULL DEFAULT NULL
COLLATE 'UTF8MB4_bin';

 

불필요한 함수 제거

SELECT 부서명, 비고
FROM 부서
WHERE 비고 = 'active'

 

4. 결과

 

---

대소문자 섞인 데이터와 비교하는 나쁜 SQL 문

1. 튜닝 전

소요 시간 : 0.18초

SELECT 이름, 성, 성별, 생년월일
FROM 사원
WHERE LOWER(이름) = LOWER('MARY')
	AND 입사일자 >= STR_TO_DATE('1990-01-01', '%Y-%m-%d');

 

2. 튜닝 전 실행계획 분석

type : ALL

 

각 조건 나눠서 쿼리 해보기

입사일자 : 13만 건

이름 : 224건 - CARDINALITY 가 높음

 

3. 튜닝

대소문자 구분안하는 이름 열 추가

ALTER TABLE 사원 ADD COLUMN 소문자_이름 VARCHAR NOT NULL AFTER 이름;

 

UPDATE

SELECT 이름, 성, 성별, 생년월일
FROM 사원
WHERE LOWER(이름) = LOWER('MARY')
	AND 입사일자 >= STR_TO_DATE('1990-01-01', '%Y-%m-%d');

 

 

 

추가한 COL INDEX 추가

ALTER TABLE 사원 ADD INDEX I_소문자이름(소문자_이름);

 

lower 제거

SELECT 이름, 성, 성별, 생년월일
FROM 사원
WHERE LOWER(이름) = LOWER('MARY')
	AND 입사일자 >= STR_TO_DATE('1990-01-01', '%Y-%m-%d');

 

4. 결과

소요시간 : 0.18초 > 0.00초

---

분산 없이 큰 규모의 데이터를 사용하는 나쁜 SQL 문

1. 튜닝 전

 

소요 시간 : 1.23초

SELECT COUNT(1)
FROM 급여
WHERE 시작일자 BETWEEN STR_TO_DATE('2000-01-01','%Y-%m-%d)
            AND BETWEEN STR_TO_DATE('2000-12-31','%Y-%m-%d);

 

2. 튜닝 전 실행 계획 분석

전체 데이터가 2844047건

SELECT COUNT(1)
FROM 급여

 

연도별 분포도 확인 > 고루 퍼져 있음을 확인

SELECT YEAR(시작일자), COUNT(1)
FROM 급여
GROUP BY YEAR(시작일자)

 

3. 튜닝

파티션 설정

ALTER TABLE 급여
partition by range COLUMNS (시작일자)
(
	partition p85 values less than ('1985-12-31'),
    partition p86 values less than ('1986-12-31'),
    ...
    partition p86 values less than (MAXVALUE)
);

 

 

4. 결과

소요 시간 : 1.23초 > 0.19초