[친절한SQL튜닝/온라인스터디]인덱스 튜닝

지방러의 한계로 지인들과 함께하는 스터디가 아닌 외부 스터디는 꿈도 못꾸고 있었는데 자격증 카페에서 SQLP 온라인스터디를 모집한다는 것을 알고 급하게 들어가게 됐다. 온라인스터디는 처음이라 진행방식이 생소하기도하고 신기하다. 다행히 한분께서 스터디 리더역할을 해주셔서 벌써 2주차 진행중이다.

 

진행방식은 대부분 직장인들이라 토요일 오전 9시부터 12시까지 ZOOM을 통한 온라인 스터디로 진행중이다. 첫 스터디때  구글시트를 통해 간단한 룰과 함께 서로 발표파트를 나누게 되었다. 앞부분이 좀 더 발표하기는 수월할텐데 어차피 발표준비에 드는 시간은 비슷할 것같아 그냥 두었더니 6주차의 DML튜닝 파트를 맡게되었다. 아직 발표까지 시간은 많이 남았지만 뭔가 마음의 부담때문에 강제로 DML튜닝부분만 열심히 읽고있는중이다. 다른 부분도 공부를 해야하는데 오히려 중요파트인 인덱스와 조인은 여러번 읽어서 너무 익숙해진것 같다.(완벽하게 마스터했다는 의미는 아니다ㅠ 막상 설명하려면 막막하긴 매한가지다.) 

 

2주차 스터디에서 나온 질문사항을 정리해보고자 한다.

 

1. 클러스터 테이블이 랜덤액세스가 발생하지 않는 이유. (친절한SQL튜닝 3.1.7 클러스터 테이블)

클러스터 테이블에는 인덱스 클러스터 테이블과 해시 클러스터 테이블이 있다. 인덱스 클러스터 테이블이란 클러스터 키값이 같은 레코드를 한 블록에 모아서 저장하는 구조이다. 이런 구조적 특성 때문에 클러스터 인덱스를 스캔하면서 값을 찾을 때는 랜덤 액세스가 값 하나당 한 번 씩 밖에 발생하지 않는다. 해시 클러스터 테이블은 인덱스를 사용하지 않고 해시 알고리즘을 사용해 클러스터를 찾아간다는 점만 다르다. 즉, 클러스터 테이블은 키 값으로 레코드를 한 블록에 모아서 저장해 놓기때문에 정확히는 랜덤액세스가 키 값 하나당 한번씩만 발생한다.

 

2. Index fragmentation 관련 책 설명

국가공인 SQLP 문제집과 병행하여 진행하다보니 친절한SQL튜닝에서 자세히 설명되지 않은 내용들이 꽤 있었다. 다행히 이번 발표자 분께서 따로 자세히 정리해주셔서 공유받을 수 있었다. 친절한SQL튜닝에서 Index fragmentation 주요 현상인 Index Skew, Index Sparse 현상에 대해 자세히 설명하고있지는 않지만 2장 인덱스 기본 에서 인덱스 구조 설명중 Balaned의 의미라는 제목으로 B-tree인덱스는 루트로부터 모든 리프 블록까지의 높이는 모두 같다라는 내용을 설명하고 있다. 아래는 해당 현상을 설명한 블로그 글이다. 

https://jungmina.com/736

[Oracle] 오라클 인덱스 단편화, Index Fragmentation

 

 

3. 왜 substr(컬럼, 1, 2)은 index 스캔이 안되는지 like '문자%' 처럼 되야하는 것 아닌지?

컬럼을 가공하면 인덱스를 타지 못한다. 라는 대전제에 질문을 던져 본적이 없어 생각지 못했는데 처음 튜닝을 접하는 분들께는 의문이 들 수 있겠다는 생각이 들었다. 인덱스란 테이블의 특정 컬럼을 기준으로 정렬되어있는 별도의 오브젝트이다. like 조건을 사용할 경우 중간문자열, 끝문자열 조회는 인덱스 사용이 불가하지만 첫시작문자열 조회는 인덱스 사용이 가능하다. 

--1. 첫문자열 조회(index range scan 가능)
select * 
from emp
where ename like '김%';

--2. 중간문자열 조회(index range scan 불가능)
select * 
from emp
where ename like '%영%';

--3. 끝문자열 조회(index range scan 불가능)
select * 
from emp
where ename like '%훈';

대량의 데이터에서 소량의 데이터를 찾기위해 정렬되어있는 오브젝트이기때문에 첫문자열 조회에는 사용이 가능한 것이다. 그렇다면 해당 논리에 따르면 substr(컬럼, 1, 2)처럼 substr을 사용한 첫문자열 조회시에는 인덱스사용이 가능 할 수 있지 않을까? 결론은 substr(컬럼, 1, 2) 를 정렬해 놓은 오브젝트가 아니기때문에 불가하다. 인덱스는 해당 컬럼을 별도 관리하는것이지 함수사용 후 return값을 저장해놓은게 아니다.  SQL수행방식을 생각하더라도 해당쿼리를 index range scan 하는것은 이상하다. 컬럼명확인 -> 함수수행 -> 조건 확인 순으로 쿼리가 수행되야 할텐데 return 값을 저장해 놓지 않는 이상 index range scan을 할수가 없다. 만약 해당 함수를 사용하면서 인덱스 스캔을 하게 하려면 FBI(Function Based Index)를 사용하는 방법이 있다.

--1. 첫문자열 조회(index range scan 불가능)
select * 
from emp
where substr(ename,1,1) = '김';

--2. 두번째문자열 조회(index range scan 불가능)
select * 
from emp
where substr(ename,2,1) = '영';

--3. 세번째문자열 조회(index range scan 불가능)
select * 
from emp
where substr(ename,3,1) = '훈';