100줄이 넘는 쿼리를 조금씩 수정해서 덧붙여 나가면서 쓰다보면 쿼리가 왠지 늦어지는 것 같은데..? 하는 생각이 들고 쿼리 튜닝 욕구가 올라온다. 이번에 업무량이 과하지 않아서 조금씩 튜닝을 해 볼 기회가 생겨 틈틈이 공부하는 중인데, 마침 어제 동료 한 분이 요새 공부 잘 되어 가냐고 물어봐서 정리한 것을 공유한다.
동료의 한마디..
목표 : 인덱스 잘 타네요 라고 말할줄 아는 사람이 된다.
Mysql 튜닝에 관한 내용입니다. (빅쿼리 X . 빅쿼리는 쿼리콘솔에서 따로 확인 가능)
1인덱스 2잘 타네요 에서 인덱스란.
인덱스란 튜닝 용어임.
튜닝할때 오브젝트를 스캔하는 유형은 테이블 스캔과 인덱스 스캔이 있음.
테이블 스캔 : 인덱스를 거치지 않고 바로 디스크에 위치한 테이블 데이터에 접근하는 유형.
인덱스 스캔 : 인덱스로 테이블 데이터를 찾아가는 유형.
스캔 방식
현업에서 주로 사용하는 스캔 방식으로는, 테이블 스캔 유형 중에서는 테이블 풀 스캔 방식, 인덱스 스캔 유형 중에서는 인덱스 범위 스캔, 인덱스 풀 스캔, 인덱스 고유 스캔, 인덱스 루스 스캔, 인덱스 병합 스캔 방식이 있음.
테이블 풀 스캔
인덱스 안 거치고 테이블로 바로 직행해서 처음부터 끝까지 데이터 훑어보는 방식. where절의 조건문을 기준으로 활용할 인덱스가 없다거나, 전체 데이터 대비 대량의 데이터가 필요할때 수행.
성능 면에서 부정적으로 해석됨. 테이블 풀 스캔은 인덱스 없이 사용하는 유일한 방식임.
인덱스 범위 스캔
인덱스를 범위 기준으로 스캔한 뒤 스캔 결과를 토대로 테이블 데이터 찾아감. between~and , < , > , like 구문 등 비교 연산, 비교 구문에 포함될 경우 수행.
좁은 범위를 스캔할때는 성능적으로 매우 효율적이나, 넓은 범위를 스캔할때는 오히려 비효율적임.
인덱스 풀 스캔
인덱스를 처음부터 끝까지 수행. 테이블에 접근하지 않고 인덱스로 구성된 열 정보만 요구하는 Sql문에서 수행.
테이블 풀 스캔 방식보다 성능상 유리하지만, 인덱스라는 오브젝트 전 영역을 모두 검색하는 방식인 만큼 검색 범위를 최대한 줄이는 방향으로 튜닝 필요.
인덱스 고유 스캔
기본키나 고유 인덱스로 테이블에 접근하는 방식. where절에 = 조건으로 작성. 해당 조인 열이 기본키 또는 고유 인덱스의 선두 열로 설정되었을때 활용.
가장 효율적인 스캔 방법
인덱스 루스 스캔
인덱스의 필요한 부분들만 골라 스캔. where + group by , Max() , min() 함수가 포함되면 작동. 이미 오름차순으로 정렬된 인덱스에서 최댓값이나 최솟값이 필요한 경우.
where 절 조건문 기준으로 필요 데이터와 불필요 데이터 구분한뒤 불필요 인덱스 무시.
인덱스 병합 스캔
테이블 내에 생성된 인덱스들을 통합(결합, 교차)해서 스캔하는 방식. WHERE 문 조건절들의 열들이 서로 다른 인덱스로 존재하면 옵티마이저가 해당 인덱스를 가져와서 모두 활용.
물리적으로 존재하는 개별 인덱스를 각각 수행하므로 인덱스 접근 시간이 배로 걸림. 따라서 별개로 생성된 인덱스를 보통 하나의 인덱스로 통합 하거나 sql 문자체를 독립된 하나의 인덱스만 수행하도록 변경하는 튜닝을 진행.
실무를 하다보면 결국 join (inner join)과 left join 을 가장 많이 쓰게 되는데 실제 데이터와 쿼리를 보면서 어떤식으로 결과가 나오는지 확인해본다.
데이터는 Join을 이해하기 쉽도록 포인트 적립과 사용에 대한 데이터를 예제로 사용해본다.
데이터 준비
- write (DDL) 쿼리가 안되는 환경을 고려해서 다음과 같이 데이터를 넣는다.
상황 : AA, BB 두명의 유저가 있고 포인트를 쌓거나 사용하고 있으며 각각 get_point, use_point에 로그를 남긴다. .
point 테이블 : 간단하게 idx(index, Primary Key값, 고유번호를 의미하는 컬럼)만 넣은 테이블
get_point 테이블 : 포인트 적립 (p_idx, idx, uid, point) , p_idx로 point.idx와 엮을 수 있다.
use_point 테이블 : 포인트 사용 (p_idx, idx, uid, point) , p_idx로 point.idx와 엮을 수 있다.
*원래 idx , p_idx , uid, point 순서가 더 자연스러운데 중간에 수정하다보니.. 글을 전체 수정하기 어려워서 이대로 감.
with point as (
select 1 IDX union all
select 2 union all
select 3 union all
select 4 union all
select 5 union all
select 6 union all
select 7 union all
select 8 union all
select 9 union all
select 10 union all
select 11 union all
select 12 union all
select 13 union all
select 14 union all
select 15 union all
select 16
)
,get_point as (
select 1 p_IDX, 1 IDX, 'AA' UID, 1000 POINT union all
select 2 , 2 , 'AA' , 1500 union all
select 3 , 3 , 'BB' , 1000 union all
select 4 , 4 , 'AA' , 1000 union all
select 6, 5 , 'BB' , 2000 union all
select 7, 6 , 'AA' , 500 union all
select 10, 7 , 'BB' , 5000 union all
select 11, 8 , 'AA' , 10000 union all
select 15, 9 , 'BB' , 3000 union all
select 16, 10 , 'AA' , 5000
)
,use_point as (
select 5 p_IDX, 1 IDX, 'AA' UID, -1500 POINT union all
select 8, 2 , 'AA' , -200 union all
select 9, 3 , 'BB' , -100 union all
select 12, 4 , 'AA' , -100 union all
select 13, 5 , 'BB' , -2800 union all
select 14, 6 , 'AA' , -500
)
데이터 확인
point 테이블
select * from point p
get_point 테이블
select * from get_point gp
use_point 테이블
select * from use_point up
inner join 결과
이때, 만약 전체를 join 한다면?
select * from point p
join get_point gp on gp.p_idx = p.idx
join use_point up on up.p_idx = p.idx
=> 아무것도 나오지 않는다.
호옥..시 point.idx별로 join 이 되어서 전체 적립/사용 테이블이 나오길 기대했나?
아니다. 그걸 보기 위해서는 Left join 으로 처리해야한다.
left join 결과
select * from point p
Left join get_point gp on p.idx=gp.p_idx
left join use_point up on p.idx=up.p_idx
이렇게 했더니 원하는 것을 볼 수 있게 되었다.
case when 사용해서 정리하기
위 쿼리의 결과로 나온 테이블은 뭔가 정리가 안 되어 있음. 원하는것에 아직 한발 부족함.
그럴때는 case when을 사용함.
먼저 위의 Left join 두개 사용한 쿼리를 With 문으로 서브쿼리로 만들어 주고, 해당 테이블을 다시 사용하면서 case 문을 이용하는 방식
,tbl as (
select p.idx,
gp.idx gp_idx, gp.p_idx gp_p_idx, gp.uid gp_uid, gp.point gp_point,
up.idx up_idx, up.p_idx up_p_idx, up.uid up_uid, up.point up_point
from point p
left join get_point gp on p.idx=gp.p_idx
left join use_point up on p.idx=up.p_idx
)
select tbl.idx,
case when gp_idx is not null then gp_idx else up_idx end gu_idx,
case when gp_idx is not null then gp_point else up_point end point,
case when gp_idx is not null then gp_uid else up_uid end uid
from tbl
# Write your MySQL query statement below
select gender, day, sum(score_points) over( partition by gender order by gender, day) as total
from Scores group by 1,2 order by 1,2
- 누적집계할 컬럼을 sum() 안에 넣어준다 => sum(score_points) - over() 안에 값을 입력 -크게는 gender 별로 누적집계를 보고 싶은 것이니 partition by gender (누적되어 나오는 마지막 값은 성별 누적값을 보고 싶다는 뜻.) -누적 집계는 순서가 중요함. order by 다음에 gender, day 로 날짜별로 누적되어 나오게 설정.
결과
꿀팁 Sum over 쓸때 partition by 를 안해도 되고 (이때는 전체 테이블이 Order by 기준으로만 누적집계 되어 나옴) order by 가 안들어가도 됨 (거의 쓸일 없을듯? 뭐.. 한달 급여 테이블이 있고 사원이 있고 지불된 총 한달 급여를 보고 싶으면 쓰겠지만 웬만해서는 Order by 가 필요할 것 같음.)
