'친절한 SQL 튜닝' 요약정리 3탄: 인덱스 튜닝

(주)디비안 조시형 대표님의  '친절한 SQL 튜닝의 바이블'  

구입하시길 강력 추천드립니다.

책 구입을 원하시는분은 요기를 클릭하시면 됩니다.

-저자 소개
現) 주식회사 디비안 대표이사 
오라클 성능 고도화 원리와 해법 1, 2 저자 
국가공인 SQLP, DAP 자격검정 전문위원
한국데이터진흥원과 한국DB산업협의회가 공동 주관하는 제1회 우수DB人상을 수상
인터넷 카페 디비안 포럼(WWW.DBIAN.NET) 운영
前) 이랜드, 인슈넷, 디엠에스랩, 엔코아, 비투엔


하단의 내용은 제가 예전에 읽었던 내용을 요약 정리한 것입니다.

 

1. 테이블 엑세스 최소화

 

메인 메모리: 데이터를 통으로 메모리에 올려놓고 사용

 

일반 메모리(디스크 메모리): 캐쉬한 데이터만 메모리에 올려놓고 사용

 

메모리 DB 개발 업체들은 오라클 같은 DB를 디스크 DB라고 부름

 

 

 

-인덱스 클러스터링 팩터

 

인덱스 클러스터링 팩터는 특정 컬럼을 기준으로 같은 값을 갖는 데이터가 서로 모여있는 정도를 의미함

CF가 좋은 컬럼에 생성한 인덱스는 검색 효율이 매우 좋음

 

 

-인덱스 손익분기점

 

인덱스 rowid를 이용한 테이블 엑세스는 생각보다 고비용 구조

따라서 읽어야 할 데이터가 일정량을 넘는 순간 테이블 전체를 스캔하는것보다 오히려 느려짐

이 지점을 흔히 인덱스 손익분기점이라고 부름

 

 

-table full scan은 성능이 일정하다.

 

1천만건중 한건을 조회하든 10만건을 조회하든 1천만건 조회하든 차이가 거의 없음

-인덱스를 이용해 테이블을 엑세스 할때는  추출 건수가 많을수록 느려짐

 

인덱스 rowid를 이용한 테이블 엑세스는 랜덤 엑세스 및 single block I/o방식이라  추출건수 많아지면 느려지고 CF가 안좋으면 느려짐.

index range scan에 의한 테이블 엑세스가 Table Full Scan보다 느려지는 지점을 흔히 인덱스 손익분기점이라고 한다.

 

이런 요인에 의해 인덱스 손익분기점은 5~20% 의 낮은 수준에서 결정됨.

이 정도의 손익분기점은 10만건 이내, 많아 봐야 100만건 이내 테이블에 적용되는 수치다.

1천만건 수준의 큰 테이블에선 손익분기점이 더 낮아진다. 

CF가 나쁘면 손익분기점은 5%미만에서 결정되며 심할때는 1%미만으로 낮아진다.

인덱스 CF가 나쁘면 같은 테이블 블록을 여러 번 반복 엑세스하면서 논리적 I/o회수가 늘고, 물리적 i/o횟수도 늘기 때문

1천만건 테이블의 10%는 100만건/100만건 이상 엑세스한다면 캐시 히트율은 극히 낮아질수 밖에 없음

게다가 인덱스 컬럼 기준으로 값이 같은 테이블 레코드가 근처에 모여 있을 가능성도 매우 작음

 

-온라인프로그래밍 vs 배치 프로그래밍 튜닝

온라인 프로그래밍은 보통 소량 데이터를 읽고 갱신하므로  인덱스 +NL조인 유리

대량 데이터를 읽고  갱신하는 배치 프로그래밍은 항상 전체 범위 처리 기준으로 튜닝해야한다. Full Scan과 hash join이 유리하다

초대용량 테이블을 Full Scan하면 상당히 오래 기다려야하고 시스템에 주는 부담도 적지 ㅇ낳다. 따라서 배치 프로그램에서는 파티션 활용전략이 중요한 튜닝 요소이고 병렬 처리까지 더 할 수 있으면 금상첨화다.

 

-인덱스 구조 테이블

고비용인 랜덤 엑세스가 발생하지 않도록 테이블을 인덱스 구조로 생성한게 오라클은 IOT(index-organized table)라 부른다.

참고로 MS-SQL Server는 클러스터형 인덱스라 부른다.

인덱스 리프 블록이 곧 데이터 블록이다. 

IOT는 인덱스 구조 테이블이므로 정렬 상태를 유지하며 데이터를 입력한다.

IOT는 인위적으로 클러스터링 팩터를 좋게 만드는 방법 중 하나다. 같은 값을 가진 레코드들이 100% 정렬된 상태로

모여 있으므로 랜덤 엑세스가 아닌 시퀀셜 방식으로 데이터를 액세스 한다. 이때문에 between이나 부등호 조건으로 넓은 범위를 읽을때 유리하다

 

create table.... organization index;

 

-클러스터 테이블

클러스터 테이블은 인덱스 클러스터와 해시 클러스터 두가지가 있다.

클러스터 키 값이 같은 레코드를 한 블록에 모아서 저장하는 구조다.

심지어 여러 테이블 레코드를 같은 블록에 저장할 수도 있는데 이를 다중 테이블 클러스터라고 부른다. 

일반 테이블은 하나의 데이터 블록을 여러 테이블이 공유할 수 없음을 상기하기 바란다.

클러스터 인덱스를 스캔하면서 값을 찾을 때는 랜덤 액세스가 값 하나당 한 번씩 밖에 발행 하지 ㅇ낳는다.

클러스테 도달해서는 시퀀셜 방식으로 스캔하기 때문에 넓은 범위를 일거다로 비효율이 없는게 핵심원리다.

create cluster ... index;  //클러스터 생성

create index.... cluster; //클러스터 인덱스 생성

create table ... cluster.. // 클러스터 테이블 생성 

 

http://wiki.gurubee.net/pages/viewpage.action?pageId=28117328

https://velog.io/@ymh92730/MySQL-Index-의-종류 

https://jaehoney.tistory.com/57

 

-인덱스 depno+job 

 

from emp where deptno=30 and sal >=2000

인덱스 구성을 depno+sal 순으로 변경하면 좋겠지만 실운영 환경에서는 인덱스 구성을 변경하기가 절대 쉽지 않음

인덱스를 추가하다 보면 테이블 마다 인덱스가 수십개 달려 배보다 배꼽이 더 커지게 됨

이럴때 기존 인덱스에 sal 컬럼을 추가하는 것만으로도 큰 효과를 얻을 수 있음

인덱스 스캔량은 줄지 않지만, 테이블 렌던 액세스 횟수를 줄여주기 때문

 

 

-인덱스 depno+job +sal

 

from emp where deptno=30 and sal >=2000

depno =30 fix  job 나래비, sal 나래비.. 그래서 각 job에서 sal 2천 이상인  애들 금방 찾을 수 있음

 

 

-부분 범위 처리

 

 전체 쿼리 결과집합을 심없이 연속적으로 전송하지 않고 사용자로부터 fetch call이 있을때마다 일정량씩 나누어 전송하는 것을 이른바 부분범위 처리라함

 

 

 

 

2. 인덱스 스캔 효율화

 

-인덱스 스캔 효율성

 

인덱스  c1+c2+c3+c4

 

c1c2c3c4

성능감시

성능개량

성능개선

성능검사

성능검증

성능계수

성능곡선

 

경우1) where c1=성 and c2=능  and c3= 검

성능검사에서 스캔시작해서 2개 검사 3개 레코드 스캔하고 멈춤

 

경우2) where c1=성  and c2 =능  and c4=선

성능으로 시작하는 레코드 모두 스캔한다.  결과 두건 찾음

인덱스가 c1c2c3c4 인데 조건절에 c3가 없어서 비효율발생함

인덱스 선행컬럼이 조건절에 없었기에 인덱스 스캔과정에 비효율이 발생한거임

결론은 선행컬럼이 없는 앞까지가  인덱스 엑세스 조건이고 나머지는 필터 조건임

인덱스 선행 컬럼이 조건절이 없거나 아래와 같이 =조건이 아니면 인덱스 스캔 과정에 비효율이 발생함

 

*선두 컬럼은 인덱스 구성상 맨 앞쪽에 있는 컬럼을 지칭할 때 사용하고 선행컬럼은 어떤 컬럼보다 상대적으로 앞쪽에 놓인 컬럼을 지칭 

 

 

-엑세스 조건과 필터조건

 

인덱스 엑세스 조건은 인덱스 스캔범위를 결정하는 조건절

인덱스 수직적 탐색을 통해 스캔 시작점을 결정하는데 영향을 미치고, 인덱스 리프 블록을 스캔하다가 어디서 멈출지를 결정하느데 영향을 미치는 조건절

인덱스 필터 조건은 테이블로 엑세스할지를 결정하는 조건절

인덱스를 이용하든 테이블을 풀스캔하든 테이블 엑세스 단계에서 처리되는 조건절은 모두 필터조건

경우1)의 경우 c1,c2,c3가 모두 인덱스 엑세스 조건

경우2)의 경우 c1,c2가 인덱스 엑세스 조건이고 c4는 인덱스 필터조건

 

 

 

 

-비교 연산자 종류와 컬럼 순서에 따른 군집성

 

테이블과 달리 인데스에는 같은 값을 갖는 레코드들이 서로 군집해 있음

같은 값을 찾을 때 = 연산자를 사용하므로 인덱스 컬럼을 앞쪽부터 누락없이 = 연산자로 조회하면 조건절을 만족하는 레코들은 모두 모여 있음

어느 하나를 누락하거나, = 조건이 아닌 연산자로 조회하면 조건절을 만족하는 레코드가 서로 흩어진 상태가 됨

 

조건1) where c1=1 and c2=a and c3=나 and c4=a

조건2) where c1=1 and c2=a and c3=나 and c4>=a

조건3) where c1=1 and c2=a and c3 between 가 and 나 and c4=a

c1 =1 ,c2=a,c3가  가~나인거까지는 연속된 범위로 잘 좁혀짐. (2~16번 연속됨 )그러나 거기서 c4 가 a인거는 (5,6,7,8,9,14,15로  흩어져있음 filter해야함)

조건4) where c1=1 and c2<=a and c3=나 and c4 between a and b

조건5) where c1 between 1 and 3  and c2=a and c3=나 and c4=a

 

c1이 선두 컬럼이 범위검색이면 c1조건을 만족하는 레코드는 서로 모두 모여있고 (2~19번) 나머

지 조건까지 만족하는 레코드는 비교 연산자 종류에 상관없이 흩여지게 됨(5,6,7,18번)

 

                       인덱스 엑세스 조건   인덱스 필터 조건

조건절1             c1,c2,c3,c4

조건절2             c1,c2,c3,c4

조건절3             c1,c2,c3                        c4

조건절4             c1,c2                            c3,c4

조건절5             c1                                c2,c3,c4

 

결론은 첫번째 나타나는 범위검색 조건까지(요기까지는 레코들이 서로 군집해있음)  (or 선행컬럼이 없는 앞까지)가  인덱스 엑세스 조건이고 나머지는 필터 조건임

 

 

 

-인덱스 선행 컬럼이 등치 조건이 아닐 때 생기는 비효율

 

인덱스 스캔 효율성은 인덱스 컬럼을 조건절에 모두 등치 조건으로 사용할 때 가장 좋음

인덱스 칼럼 중 일부가 조건절에 없거나 등치 조건이 아니더라도, 그것이 선행컬럼이 아니고 뒤쪽 컬럼일 때는 비효율이 없음

 

밑에는 전부 인덱스 엑세스 조건으로 사용됨

 

인덱스 :아파트시세코드+평형+평형타입+인터넷매물시세

where 아파트시세코드 =x

where 아파트시세코드 =x and 평형 =x

where 아파트시세코드 =x and 평형 =x and 평형타입=x

where 아파트시세코드 =x and 평형 =x and 평형타입 between c and d

 

반면 인덱스 선행컬럼이 조건절에 없거나 부등호,between ,like와 같은 범위 검색 조건이면 인덱스를 스캔하는 단계에서 비효율이 생김

 

 

 

 

 3. 인덱스 설계

 

-인덱스 설계가 어려운 이유

 

1. DML 성능 저하 2. 데이터베이스 사이즈 증가 3. 데이터베이스 관리 및 운영 비용 상승

 

결합인덱스를 구성할 때  첫번째 기준은 조건절에 항상 사용하거나 자주 사용하는 컬럼을 선정하는것임

(인덱스 선두 컬럼을 조건절에 반드시 사용해야 하기 때문. 안그럼 인덱스 range 스캔이 안됨)

두번째 기준은 그렇게 선정한 컬럼 중 = 조건으로 자주 조회하는 컬럼을 앞쪽에 두어야 한다는 것임

 

-인덱스 외 고려해야 할 판단 기준

 

수행빈도,업무상중요도, 클러스터링 팩터, 데이터량,DML부하(=기존 인덱스 개수, 초당 DML 발생량, 자주 갱신하는 컬럼 포함 여부등), 저장공간, 인덱스 관리 비용등

이 중 가장 중요한 하나를 꼽으라면 수행빈도

NL조인할 때 outer 쪽 inner쪽 에서 outer 쪽에 비효율이 있따면 ㅇㅋ ,그런데 이너쪽에 비효율이 있다면 outer 테이블과 엑세스 하는 횟부만큼 비효율적인 스캔을 반복

 

- 결합 인덱스 선택도

 

결합 인덱스 생성 여부를 결정할때는 선택도가 충분히 낮은지가 중요한 판단

선택도란 전체 레코드 중에서 조건절에 의해 선택되는 레코드 비율을 말하며 선택도에 총 레코드 수를 곱해서 카디널리티를 구함

선택도가 높은 (카디널리티가 낮은) 인덱스는 생성해봐야 효용가치가 별로 없음

테이블 엑세스가 많이 발생하기 때문

 

-컬럼 순서 결정시, 선택도 이슈

 

결합 인데스 컬럼 간 순서를 정할 때 선택도가 중요할까?

결합인덱스를 구성할 때 선택도가 낮은(변별력이 높은) 컬럼을 앞에 두는 것이 유리하다고 흔히 알려져있는데

둘다 인덱스 엑세스 조건이므로 어떤 컬럼이 앞으로 오든 인덱스 스캔범위는 똑같음

 

where 성별 =x and 고객번호 =y

인덱스 설계할 때 우리가 할일은 항상 사용하는 컬럼을 앞쪽에 두고 그 중 = 조건을 앞쪽에 위치시키는 것뿐

그 중 선택도가 낮은 컬럼을 앞쪽에 두려는 노력은 의미 없거나 오히려 손해일 수 있음

 

결론적으로 인덱스 생성 여부를 결정할때는 선택도가 매우 중요하지만, 칼럼간 순서를 결정할때는 각 컬럼의 선택도보다 필수사용여부, 연산자 형태가 더 중요한 판단기준임

 

-중복 인덱스 제거

 

인덱스1 계약Id+청약일자

인덱스2 계약Id+청약일자+보험개시일자

인덱스3 계약Id+청약일자+보험개시일자+보험종료일자

인덱스 1, 인덱스 2는 없어도 됨