[SQL 튜닝 실습] SQL 튜닝 중급 5

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

튜닝 전 SQL문

튜닝 전 SQL문은 사원테이블에서 입사일자가 1990년 이후이고 이름이 MARY인 사원정보를 조회하는 쿼리로

MARY와 1990-01-01은 화면에서 입력된 변숫값이므로 매번 입력되는 영문의 대소문자는 고정되지 않는다.

 

select 이름, 성, 성별, 생년월일
from 사원
where lower(이름) = lower('MARY')
and 입사일자 >= STR_TO_DATE('1990-01-01', '%Y-%m-%d')

튜닝 전 실행계획

where 조건문에 이름과 입사일자 컬럼이 명시되어 있지만 
테이블 풀 스캔으로 수행된다
이름 컬럼은 기본적으로 lower() 함수가 가공하므로
이름 컬럼으로 만들어진 인덱스가 존재하더라도 활용은 불가능하다.

 

튜닝 수행

조건절로 조회되는 데이터 건수를 확인
먼저 사원 테이블에 30만건정도 데이터가 있으며
그 중 입사일자 컬럼을 활용하는 조건문에 해당하는 데이터는 13만 건정도
고정적이지 않은 변수인 LOWER(이름)를 활용하는 조건문에 해당하는 데이터는 224건

즉, 입사일자 열의 조건문에는 전체 데이터 건수 대비 약 43%에 달하는 데이터가 있으므로
입사일자 열의 인덱스를 활용할 수 없다는 것을 예상할 수있다.
반면 함수에 의해 가공된 이름 조건절은 매우 적은 범위의 데이터에 접근할 수 있다.

 

select count(1)
from 사원;

 

select count(1)
from 사원
where 입사일자 >= STR_TO_DATE('1990-01-01', '%Y-%m-%d')

 

select count(1)
from 사원
where lower(이름) = lower('MARY');

 

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

 

show index
from 사원

변별력이 좋은 이름 컬럼을 기준으로 튜닝을 진행하기 위해
가공된 함수를 제거하고 입력된 변숫값만으로 출력결과를 확인해본다
그 결과 대문자로 구성된 MARY라는 값은 없다는 것을 확인가능하다
근데 업무에서 mary, Mary 등이 입력되더라도 대소문자 구분없이 Mary라는 결과를 출력해야한다.

대소문자를 구분하는 이유는?
사원 테이블의 콜레이션을 조회해보니 이름 열의 콜레이션이 utf8_bin 이다.
이것은 대소문자를 구분하는 콜레이션으로 데이터 정렬 시 대소문자를 구분하여 처리한다.

 

select column_name, collation_name
from information_schema.COLUMNS
where table_schema = 'tuning'
and table_name = '사원';

 

 

최초 사용자 등록 시 입력된 대소문자를 그대로 유지하려면 이름 열의 콜레이션이 utf8_bin 으로 설정되어야 한다
검색할 때는 대소문자 구분이 없어야하므로 강제적으로 이름 열에 lower() 함수를 적용한 결과 
인덱스가 있어도 활용할 수 없는 상황에 이르렀다고 추측할 수 있다

 

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

신규 열은 별도의 콜레이션을 명시하지 않는 한 테이블의 콜레이션값을 상속받으므로
utf8_general_ci 로 설정된 것이다.
utf8_general_ci 는 ci라는 약자로 알 수 있듯이 대소문자 구분을 하지 않는다.
신규 열 생성 시 기존 이름 열 데이터를 소문자 형태로 변경하여 소문자_이름 열 값으로 업데이트한다.

 

alter table 사원		
add column 소문자_이름		
varchar(14)		
not null 		
after 이름;

update 사원		
set 소문자_이름 = lower(이름);

 

1175 에러 발생 시

이후 

alter table 사원 
add index I_소문자이름(소문자_이름);

 

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

 

alter table 사원 
add index I_소문자이름(소문자_이름);

 

desc 사원;

select 이름, 소문자_이름
from 사원
limit 10;

사원 테이블에서 기존 이름 열과 소문자_이름 열의 데이터 조회 후
소문자_이름 열에 저장되어 있음을 알 수 있다.

 

튜닝 후 SQL 문

select 이름, 성, 성별, 생년월일
from 사원
where 소문자_이름 = 'MARY'
and 입사일자 >= '1990-01-01';

실행 계획

카디널리티가 높은 소문자_이름 열로 인해 인덱스(I_소문자이름)를 활용해서 데이터를 조회
사원 테이블에서 이름 열에는 대소문자가 구분된 정확한 사원의 이름이 저장되고
소문자_이름 열에는 대소문자 구분없이 이름을 검색할 때 활용할 데이터가 저장된다
이름 데이터가 중복되므로 디스크 용량이 낭비되는 비효율적 방식처럼 보일 수 있지만
인덱스를 활용하여 변별력이 좋은 열을 적절하게 사용하는 쿼리 튜닝방법이다

 

explain
select 이름, 성, 성별, 생년월일
from 사원
where 소문자_이름 = 'MARY'
and 입사일자 >= '1990-01-01';

원상 복구

alter table 사원
drop column 소문자_이름;

desc 사원;

---

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

튜닝 전 SQL 문

급여 테이블에서 시작일자가 2000년 1월 1일부터 2000년 12월 31일에 해당하는 데이터를 모두 집계하는 쿼리

select count(1)							
from 급여							
where 시작일자 between STR_TO_DATE('2000-01-01', '%Y-%m-%d')							
			     and STR_TO_DATE('2000-12-31', '%Y-%m-%d')

 

튜닝 전 실행계획

explain							
select count(1)							
from 급여							
where 시작일자 between STR_TO_DATE('2000-01-01', '%Y-%m-%d')							
			     and STR_TO_DATE('2000-12-31', '%Y-%m-%d')

I_사용여부 인덱스를 활용해서 커버링 인덱스로 수행

 

튜닝수행

조건절을 살펴보면, 2000년도 데이터는 전체 급여 데이터중 약 9% 수준을 조회한다

255,785 / 2,8444,047 * 100

select count(1)
from 급여;

 

주로 특정 월 또는 연 대상으로 서비스가 호출되므로
모든 연도의 데이터에 접근할 필요는 없다

select year(시작일자), count(1)
from 급여
group by year(시작일자);

이렇게 하나의 테이블로 구성된 급여테이블을 시작일자 라는 열로 논리적으로 분할하는 파티셔닝을 할 수 있다.
1985년 12월 31일보다 작은 시작일자 데이터는 p85 라는 파티션에 적재되고,
1986년 12월 31일보다 작은 시작일자 데이터는 p86 파티션에 적재되는 방식
시작일자가 범위 기준으로 호출되므로 범위방식 파티션으로 설정했다

 

alter table 급여	
partition by range columns (시작일자)	
(	
  partition p85 values less than ('1985-12-31'),	
  partition p86 values less than ('1986-12-31'),	
  partition p87 values less than ('1987-12-31'),	
  partition p88 values less than ('1988-12-31'),	
  partition p89 values less than ('1989-12-31'),	
  partition p90 values less than ('1990-12-31'),	
  partition p91 values less than ('1991-12-31'),	
  partition p92 values less than ('1992-12-31'),	
  partition p93 values less than ('1993-12-31'),	
  partition p94 values less than ('1994-12-31'),	
  partition p95 values less than ('1995-12-31'),	
  partition p96 values less than ('1996-12-31'),	
  partition p97 values less than ('1997-12-31'),	
  partition p98 values less than ('1998-12-31'),	
  partition p99 values less than ('1999-12-31'),	
  partition p00 values less than ('2000-12-31'),	
  partition p01 values less than ('2001-12-31'),	
  partition p02 values less than ('2002-12-31'),	
  partition p03 values less than (MAXVALUE)	
);

튜닝 후 SQL 문

select count(1)							
from 급여							
where 시작일자 between STR_TO_DATE('2000-01-01', '%Y-%m-%d')							
			     and STR_TO_DATE('2000-12-31', '%Y-%m-%d')

튜닝 후 실행계획

explain							
select count(1)							
from 급여							
where 시작일자 between STR_TO_DATE('2000-01-01', '%Y-%m-%d')							
			     and STR_TO_DATE('2000-12-31', '%Y-%m-%d')

원상복구

alter table 급여
remove partitioning;