[업무에 바로 쓰는 SQL 튜닝] 5. 악성 SQL 튜닝으로 전문가 되기

양바른 님의 "Real MySQL" 책을 정리한 포스팅 입니다.

 

1. SQL 문 재작성으로 착한 쿼리 만들기

처음부터 모든 데이터를 가져오는 SQL문

explain
select 사원.사원번호, 급여.평균연봉, 급여.최고연봉, 급여.최저연봉
  from 사원, (
    select 사원번호, round(avg(연봉), 0) 평균연봉, round(min(연봉), 0) 최고연봉, round(min(연봉), 0) 최저연봉 
    from 급여 
    group by 사원번호
  ) 급여
  where 사원.사원번호 = 급여.사원번호 and 사원.사원번호 between 10001 and 10100;

• 급여 테이블에서 그룹핑 시, 거의 전체 테이블을 스캔함
• 사원 테이블에서 필터링된 데이터는 100건에 불과함

 

explain
select 사원.사원번호, 
(select round(avg(연봉), 0) from 급여 as 급여1 where 사원번호 = 사원.사원번호) 평균연봉,
(select round(max(연봉), 0) from 급여 as 급여2 where 사원번호 = 사원.사원번호) 최고연봉,
(select round(min(연봉), 0) from 급여 as 급여3 where 사원번호 = 사원.사원번호) 최저연봉
  from 사원
  where 사원.사원번호 between 10001 and 10100;

• 급여 테이블에서 각 사원별 통계정보를 출력함
• 전체 사원 데이터가 아닌 필요한 사원정보에만 접근
  • 각각의 사원번호별 급여 정보만 접근함

 

비효율적인 페이징을 수행하는 SQL문

explain
select 사원.사원번호, 사원.이름, 사원.성, 사원.입사일자 
from 사원, 급여
where 사원.사원번호 = 급여.사원번호 and 사원.사원번호 between 10001 and 50000
group by 사원.사원번호
order by sum(급여.연봉) desc
limit 150,10;

• 드라이빙 테이블인 사원 테이블은 그루핑과 정렬 연산을 위해 임시 테이블을 생성한 뒤, 정렬 작업을 수행함
• LIMIT 연산을 위한 10개의 데이터를 위해 수십만개의 데이터를 대상으로 조인하고 그룹핑하고 정렬함

 

explain
select 사원.사원번호, 사원.이름, 사원.성, 사원.입사일자 
from 
(
    select 사원번호 from 급여
    where 사원번호 between 10001 and 50000
    group by 사원번호 
    order by sum(급여.연봉) desc 
    limit 150,10
) 급여,
사원
where 사원.사원번호 = 급여.사원번호;

• 드라이빙 테이블을 급여로 하고, 인덱스 레인지 스캔 및 임시 테이블을 사용하여 생성함
• 인라인 뷰인 급여 테이블을 기준으로 사원 테이블에 반복해 접근
• 데이터 샘플 자체를 줄여 불필요한 조인과 정렬을 줄일 수 있음

 

필요 이상으로 많은 정보를 가져오는 SQL문

explain
select count(사원번호) as 카운트
from (
  select 사원.사원번호, 부서관리자.부서번호
    from (select * from 사원 where 성별 = 'M' and 사원번호 > 300000) 사원 
    left join 부서관리자 on 사원.사원번호 = 부서관리자.사원번호
) 서브쿼리;

• 중첩 루프 조인으로 외부 조인

 

explain
select count(사원번호) as 카운트
from 사원 where 성별 = 'M' and 사원번호 > 300000;

• 건수를 세는데 있어 외부 조인은 필요하지 않음

 

대량의 데이터를 가져와 조인하는 SQL문

explain
select distinct 매핑.부서번호 
from 부서관리자 관리자, 부서사원_매핑 매핑 
where 관리자.부서번호 = 매핑.부서번호
order by 매핑.부서번호;

• 부서관리자 테이블에서 인덱스 풀 스캔함
• 매핑 테이블에서 인덱스로 하나씩 대응하는 데이터 읽어옴
• 부서관리자 테이블에서 임시 테이블을 사용하여 distinct 및 정렬 수행

 

explain
select 매핑.부서번호 
from (select distinct 부서번호 from 부서사원_매핑) 매핑 
where exists (select 1 from 부서관리자 where 부서번호 = 매핑.부서번호)
order by 매핑.부서번호;

• 매핑 테이블에서 부서번호를 미리 중복제거 함 (Using Index for group-by)
• 부서관리자 테이블은 같은 부서번호 데이터가 있는지 확인
• 부서관리자 테이블에서 임시 테이블을 사용하여 distinct 및 정렬 수행

 

2. 인덱스 조정으로 착한 쿼리 만들기

인덱스 없이 작은 규모의 데이터를 조회하는 SQL문

• 조건절에 해당하는 열들이 자주 호출된다면, 인덱스로 빠른 데이터 접근을 유도하도록 튜닝함

 

인덱스를 하나만 사용하는 SQL문

• 각 조건절이 각각의 인덱스를 사용해 접근하도록 튜닝
• 소량의 데이터를 가져올 때면, 인덱스를 사용하는 것이 효율적

 

큰 규모의 데이터 변경으로 인덱스에 영향을 주는 SQL문

• 데이터 수정 시, 인덱스도 동기화해야 하므로 성능상 추가적인 비용이 발생함
• 조회 성능과 비교해서 추가 및 수정이 많을경우, 인덱스 삭제를 고려

 

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

• 조회 시 사용하는 조건 컬럼이 인덱스의 컬럼과 순서가 일치해야 인덱스를 사용할 수 있음

 

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

잘못된 열 속성으로 비효율적인 SQL문

• 콜레이션을 사용하여 타입간 비교
• 불필요한 조건과 함수를 사용하는 것은 바람직하지 않음

 

예시

• utf8_general_ci 일 경우

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

 

 

• utf8mb4_bin으로 콜레이션 변경

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

 

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

explain
select 이름, 성, 성별, 생년월일
from 사원
where lower(이름) = lower('MARY');

• 대소문자 구분 없이 비교하고 싶을 경우 컬럼을 가공하여 동등 조건으로 비교함
• 그러나 인덱스를 타지 못해 성능상 손해가 발생함

 

실패 사례 - 콜레이션

• 콜레이션을 대소문자 구분 없는 것으로 바꾸게 될 경우, 들어오는 데이터가 소문자로 저장됨 
  • 대소문자 구분을 유지하여 저장하면서 비교만 구분없이 할 수는 없음

 

해결책 - 추가 컬럼 생성

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

• 이러한 경우 대소문자를 구분하지 않는 콜레이션의 새로운 컬럼을 하나 생성해서 따로 저장하면 성능이 보장됨

 

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

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 (MAXVALUE)
);

• 데이터가 크고 특정 열로 구분될 수 있으면 파티셔닝을 하여 전체 데이터 양을 줄여 조회 성능을 높일 수 있음