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

비효율적인 인덱스를 사용하는 나쁜 SQL 문

튜닝 전 SQL 문

select 사원번호, 이름, 성	
from 사원
where 성별 = 'M'	
and 성 = 'Baba';

튜닝 전 실행 계획

explain		
select 사원번호, 이름, 성		
from 사원	
where 성별 = 'M'		
and 성 = 'Baba';

I_성별_성 인덱스를 활용해서 사원테이블에 접근
성별과 성 컬럼에 고정된 값으로 조건절
ref 항목 : const, const를 작성해서 인덱스 스캔으로 원하는 데이터에 접근

 

튜닝 수행

select count(distinct 성) 성_개수,			
        count(distinct 성별) 성별_개수		
from 사원;

사원 테이블의 인덱스

show index 
from 사원;

데이터값이 다양하지 않은 성별 컬럼을 선두로 구성한 인덱스는 과연 효율적일까?
성별 컬럼보다는 성 컬럼이 더 다양한 종류의 값을 가지므로 성 컬럼을
먼저 활용하면 데이터 접근 범위를 상당히 줄일 수 있다.

 

인덱스 튜닝

alter table 사원
drop index I_성별_성,
add index I_성_성별(성, 성별)

따라서 기존의 '성별+성' 순서로 구성된 I_성별_성 인덱스를
성 + 성별' 순서의 I_성_성별 인덱스로 변경한다.

 

튜닝 후 SQL 문

select 사원번호, 이름, 성	
	from 사원
    where 성별 = 'M'	
    and 성 = 'Baba';

 

튜닝 후 실행계획

explain		
select 사원번호, 이름, 성		
	from 사원	
    where 성별 = 'M'		
    and 성 = 'Baba';

---

적절한 테이블 및 컬럼 속성 설정으로 착한 쿼리 생성

튜닝 전 SQL 문

select 부서명, 비고
from 부서
where 비고 = 'active'
and ascii(substr(비고, 1, 1)) = 97
and ascii(substr(비고, 2, 1)) = 99

튜닝 수행

1) 조건문 : a'ctive'
2) scii 비교 : saca

 

select 부서명, 비고
from 부서
where 비고 = 'active';

콜레이션 조회

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

 

출력결과는 utf8로 대소문자 구분이 없는 utf8_general_ci 콜레이션임을 확인
특정 컬럼의 대소문자를 구분해야 할 때는 콜레이션 변경이 불가피

 

비고 컬럼의 콜레이션을 기존 utf8_general_ci 에서 이모지까지 지원하는
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 = '부서';

컬럼 콜레이션값이 변경되었음을 확인

 

튜닝 결과

select 부서명, 비고
from 부서
where 비고 = 'active';

동등 조건만으로 소문자 active 데이터만 출력된다.

원상 복구

alter table 부서
change column 비고 비고 varchar(40) null default null
collate 'utf8_general_ci';