[SQL 튜닝] 대소문자가 섞인 데이터와 비교하는 나쁜 SQL 문

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

현황 분석

| 튜닝 전 실행 계획 |

사원 테이블에서 입사일자가 1990년 이후이고, 이름이 MARY인 사원정보를 조회하는 쿼리

이때 MARY와 1990-01-01은 입력된 변수 값이므로 매번 입력되는 영문의 대소문자는 고정되지 않음

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

- SQL문 결과 : 총 96건의 결과 출력, 88.6ms 소요

- 테이블 풀 스캔(type 항목 : ALL)으로 수행

- 이름 열은 기본적으로 LOWER() 함수가 가공, 이름 열로 만들어진 인덱스가 존재하더라도 활용할 수 없음

 

튜닝 수행

- 사원 테이블 : 약 30만 건

- 입사일자 열을 활용하는 조건문 : 약 13만 건

- LOWER(이름) : 224건

- 즉, 입사일자 열의 조건문에는 전체 데이터 건수 대비 약 43%(13만 건/ 30만 건)에 달하는 데이터가 있으므로 입사일자 열의 인덱스를 활용할 수 없음

- 반면, 함수에 의해 가공된 이름 조건절은 매우 적은 범위(224건/30만 건)의 데이터에 접근할 수 있음

/* 300024 */
SELECT COUNT(1) FROM 사원;

/* 135227 */
SELECT COUNT(1) FROM 사원
WHERE 입사일자 >= STR_TO_DATE('1990-01-01', '%Y-%m-%d'); 

/* 224 */
SELECT COUNT(1) FROM 사원
WHERE LOWER(이름) = LOWER('MARY');

WHERE 절의 조건문에 명시된 열 중 인덱스가 생성된 열은 I_입사일자 인덱스를 구성하는 입사일자 열

SHOW INDEX FROM 사원;

변별력이 좋은 이름 열 기준으로 튜닝을 진행하기 위해 가공된 함수를 제거하고 입력된 변수 값만으로 출력 결과를 확인

SELECT * FROM 사원
WHERE 이름 = 'MARY';

- mary, Mary, marY, maRy, MARY 등이 입력되더라도 대소문자의 구분 없이 MARY라는 결과를 출력하게 만들어야 함

- 그래서 LOWER() 함수를 사용하여 소문자로 변경하여 비교

 

시스템 테이블인 information_schema.columns에서 사원 테이블의 콜레이션을 조회

SELECT COLUMN_NAME, COLLATION_NAME
	FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_NAME = '사원' AND TABLE_SCHEMA = 'tuning';

- 이름 열의 콜레이션 = utf8_bin → 대소문자를 구분하는 콜레이션으로, 데이터 정렬과 비교 시 대소문자를 구분하여 처리

- 이름 정보를 검색할 때는 대소문자 구분이 없어야 하므로 강제적으로 이름 열에 LOWER() 함수를 적용한 결과 인덱스가 있어도 활용할 수 없는 상황에 이르렀다고 추측 가능

 

이름 열 옆에 소문자_이름이라는 신규 열을 추가

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

UPDATE 사원
 SET 소문자_이름 = LOWER(이름); /* 6.60s 소요 */

- 신규 열은 별도의 콜레이션을 명시하지 않는 한 테이블의 콜레이션값을 상속받으므로 utf8_general_ci로 설정

- utf8_general_ci → 대소문자를 구분하지 않음

- 신규 열을 생성하면 기존 이름 열의 데이터를 소문자 형태로 변경하여 소문자_이름 열값으로 업데이트

- 소문자_이름 열은 대소문자를 구분하지 않는 열이므로 LOWER() 함수를 사용할 필요 없음

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

- 이름 정보를 비교하는 로직이 업무상 자주 호출되다는 가정 하에 새로 생성한 소문자_이름 열로 I_소문자이름 인덱스를 위와 같이 생성

- 사원 테이블의 상세 설명을 DESC 명령어로 확인

DESC 사원;

- 먼저 소문자_이름 열이 이름 열 다음에 생성되어 있음

- Key 항목을 통해 소문자_이름 열에 인덱스가 생성되었음

 SELECT 이름, 소문자_이름
 FROM 사원
 LIMIT 10;

- 이름 열을 활용해서 소문자 데이터가 소문자_이름 열에 저장되어 있음

 

튜닝 결과

| 튜닝 후 실행 계획 |

소문자_이름 = 'MARY' 조건문으로 변경, MARY라는 변수는 대소문자 구분 없이 사용자가 입력한 값으로 판단

EXPLAIN
SELECT 이름, 성, 성별, 생년월일
	FROM 사원
WHERE 소문자_이름= 'MARY'
AND 입사일자 >= '1990-01-01';

- SQL문 결과 :   88.6ms → 0.9ms (시간 단축)

- 카디널리티가 높은 소문자_이름 열로 인덱스(key 항목 : I_소문자이름)을 활용하여 데이터를 조회

- 즉, 사원 테이블에서 이름 열에는 대소문자가 구분된 정확한 사원의 이름이 저장되고, 소문자_이름 열에는 대소문자 구분 없이 이름을 검색할 때 활용할 데이터가 저장

- 이름 데이터가 중복되므로 디스크 용량이 낭비되는 비효율적 방식처럼 보일 수 있겠지만, 인덱스를 활용하여 변별력이 좋은 열을 적절하게 사용하는 쿼리 튜닝 방법

 

* 참고

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