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

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

현황 분석

| 튜닝 전 실행 계획 |

부서 테이블의 비고 열값이 소문자 'active' 일 때의 데이터를 조회하는 쿼리

SUBSTR(비고,1,1)과 SUBSTR(비고,2,1)라는 조건절로 비고 열의 데이터에서 첫 번째 문자와 두번째 문자를 각각 추출한 뒤 ASCII 코드값을 비교(아스키 코드 값 : 97 = a, 99 = c 에 해당)

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

 

- SQL문 결과 : 총 4건의 결과(active인 값들) 출력, 0.4ms 소요 (소요 시간이 매우 짧음)

 

튜닝 수행

- 각각 조건절 분석

# 첫번째 조건

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

 

- 대소문자가 섞인 상태로 총 7건 데이터 출력

 

# 두번째 조건

SELECT 비고,
	SUBSTR(비고,1,1) 첫번째,
	ASCII(SUBSTR(비고,1,1)) 첫번째_아스키,
	SUBSTR(비고,1,1) 두번째,
	ASCII(SUBSTR(비고,2,1)) 두번째_아스키
	FROM 부서
	WHERE 비고 = 'active';

- 아스키 코드 값 : A=65, a=97, C=67, c=99

- 소문자로만 작성된 값을 얻고자 아스키 코드값을 조건절에 작성했다는 사실을 유추할 수 있음

(이때 첫번째 문자와 두번째 문자가 소문자라면 이후의 문자들도 모두 소문자라고 가정)

- 소문자 여부를 판단하기 위해 아스키 코드를 추출하는 함수까지 사용할 필요가 있는지 확인 필요

 

다음은 부서 테이블의 열에 대한 콜레이션을 조회하는 쿼리

information_schema.columns라는 시스템 테이블을 활용해서 부서 테이블의 열별 콜레이션값을 조회

SELECT COLUMN_NAME, collation_name
	FROM information_schema.COLUMNS
	WHERE table_schema = 'tuning' AND TABLE_NAME = '부서';

- 출력 결과는 utf8로, 대소문자 구분이 없는 utf8_general_ci 콜레이션임을 확인

- 이때, 비고 열의 콜레이션을 기존 utf8_general_ci에서 이모지(emoji)까지 지원하는 UTF8MB4_bin으로 변경

- 튜닝 전 SQL문에서 substr(), ascii() 함수가 수행하던 불필요한 작업을 제거할 수 있음

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

 

- 변경한 뒤 조회

SELECT COLUMN_NAME, collation_name
	FROM information_schema.COLUMNS
	WHERE table_schema = 'tuning' AND TABLE_NAME = '부서';

 

튜닝 결과

| 튜닝 후 실행 계획 |

콜레이션을 변경한 뒤의 SQL문은 불필요한 함수를 제외한 채 단순히 비고 열에 대한 조건절만 유지

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

- 조건절의 비고 열에 대한 동등 조건만으로도 소문자 active 데이터만 출력 (SQL 문이 간결해짐)

 

* 참고

- 업무에 바로쓰는 SQL 튜닝(도서) - 5장 악성 SQL 튜닝으로 전문가 되기