굉장히 좋은 책이다. DB 원리를 간결하고 명쾌하게 설명한다. 다른 사람들도 꼭 읽어봤으면 한다.
아직은 인덱스를 쓸 일이 많지 않아서 아쉽지만, 대용량 트래픽을 경험하는 날이 오면 반드시 다시 읽어봐야겠다.
아래 글은 책 일부를 정리 및 가공한 것이다. 내 수준에 맞게 기초만 정리해 놓았다.
인덱스 튜닝과 관련된 심화 설명은 책을 참고하길 바란다.
1장 SQL 처리 과정과 I/O
소프트 파싱과 하드 파싱 [프로시저]
라이브러리 캐시 : SQL 파싱, 최적화, 로우 소스 생성 과정을 거쳐 생성한 내부 프로시저를 반복 재사용할 수 있도록 캐싱해두는 메모리 공간
소프트 파싱 : SQL 파싱을 할 때, 해당 SQL이 라이브러리 캐시에 존재한다면 곧바로 실행단계로 넘어가는 것을 소프트 파싱이라 한다.
하드 파싱 : SQL이 라이브러리 캐시에 존재하지 않아서 최적화 및 로우 소스 생성 단계까지 모두 거치는 것을 하드 파싱이라 한다.
데이터베이스에서 일어나는 대부분의 작업은 I/O 버스트 작업이지만, 하드 파싱은 CPU 자원을 많이 쓰는 CPU 버스트 작업이다.
논리적 I/O와 물리적 I/O [데이터]
어떤 조회 작업이 일어나면,DB는 먼저 DB 버퍼 캐시를 확인한다. 만약 캐시 히트를 하면, 디스크를 읽을 필요가 없으므로 전체 I/O 시간이 확연히 줄어든다.
(왜냐하면 디스크를 읽기는 물리적인 액세스 암이 회전하는 작업을 수반하는 작업이기 때문이다. 디스크 읽기가 메모리 읽기보다 최소 100배, 보통 1만배 느리다. 따라서 메모리 읽기인 DB 버퍼 캐시에서 히트가 일어나면 굉장히 많이 시간을 줄일 수 있다.)
논리적 I/O는 SQL을 수행하면서 읽은 총 블록 I/O (DB 버퍼캐시에서 블록을 읽은 I/O) 또는 메모리 I/O이다.
반면 물리적 I/O는 DB 버퍼캐시에서 블록을 찾지 못해 디스크에서 읽은 블록 I/O이다.
SQL 튜닝의 핵심
물리적 I/O= 논리적 I/O * (100%-버퍼캐시 히트율)
물리적 I/O는 통제 불가능한 변수이고 논리적 I/O는 튜닝을 통해 줄일 수 있는 통제가능한 변수이다. 따라서 SQL 튜닝이란 논리적 I/O를 줄임으로써 물리적 I/O를 줄이는 것이다.
Single Block I/O와 MultiBlock I/O
Single Block I/O : 한 번에 한 블록씩 요청해서 메모리에 적재하는 방식.
인덱스는 대량의 데이터를 가진 테이블에서 소량의 데이터를 조회할 때 효과적인 도구라서 Single Block I/O 방식을 사용한다.
MultiBlock I/O : 한 번에 여러 블록씩 요청해서 메모리에 적재하는 방식. 캐시에서 찾지 못한 특정 블록을 읽으려고 I/O 요청을 할 때, 디스크 상에서 그 블록과 인접한 블록들을 한꺼번에 읽어 미리 캐시에 적재한다.
테이블 풀 스캔의 경우 MultiBlock I/O 방식을 사용한다. 테이블이 클수록 MultiBlock I/O 단위가 크면 좋다. 왜냐하면 해당 단위가 클수록 한꺼번에 캐싱을 더 많이 하기 때문에 물리적 I/O를 줄일 확률이 높아지기 때문이다.
2장 인덱스 기본
인덱스 수직적 탐색과 수평적 탐색
수직적 탐색 : 정렬된 인덱스 레코드 중 조건을 만족하는 첫 번째 레코드를 찾는 과정(인덱스 스캔 시작지점을 찾는 과정)
수평적 탐색 : 수직적 탐색을 통해 스캔 시작점을 찾아낸 후, 찾고자하는 데이터가 더 이상 나타나지 않을 때까지 인덱스 리프 블록을 수평적으로 스캔하는 과정(인덱스 리프 블록끼리는 더블 링크드 리스트로 되어 있어서 가능하다.)
인덱스 컬럼을 가공하면 인덱스를 정상적으로 사용할 수 없다.
인덱스의 컬럼을 가공하게 되면 인덱스 스캔 시작 지점을 찾을 수 없게 되어 Index Range Scan을 사용할 수 없게 된다.
예를 들어, 다음과 같은 SQL문들은 인덱스가 작동하지 않는다.
인덱스(생년월일)이고 생년월일이 '20070101'과 같은 형식일 때,
SELECT * FROM person WHERE substr(생년월일,5,2)='05'; 와 같이 작성하면, 인덱스에는 가공되지 않은 값이 저장되있는데 가공된 값을 기준으로 검색해야해서 인덱스 스캔 시작 지점을 찾을 수 없다.
아래와 같이 Like 중간값 검색도 인덱스 스캔 시작 지점을 찾을 수 없다. (반면 준% 일 경우에는 특정 구간 내에 모여 있으므로 인덱스 스캔이 가능하다.)
select * from person where name like '%준%';그리고 IN과 OR 조건 역시 스캔 시작 지점을 특정할 수 없기 때문에 인덱스가 정상 작동하지 않는다.
인덱스의 선두 컬럼이 조건절에 있어야 인덱스가 정상 작동한다.
예를 들어, 인덱스를 (소속팀,사원명, 연령)으로 정했다고 하자.
이는 데이터를 소속팀 순 정렬 후에, 소속팀이 같으면 사원명 순으로 정렬하고, 사원명도 같으면 연령 순으로 정렬한다는 의미이다.
SELECT * FROM company WHERE employee_name='임준혁'; 위와 같은 조건으로 검색을 하게 되면, 조건을 만족하는 데이터가 리프 블록 전 구간에 흩어지기 때문에 인덱스 스캔 시작 지점을 찾을 수 없다.
3장 인덱스 튜닝
인덱스 클러스터링 팩터
인덱스 클러스터링 팩터 : 특정 컬럼을 기준으로 같은 값을 갖는 데이터가 서로 모여 있는 정도를 뜻한다.
이 지수가 높은 컬럼에 생성한 인덱스는 검색 효율이 매우 좋다.
인덱스 손익분기점
인덱스 손익분기점 = 인덱스 스캔에 의한 테이블 액세스가 Table Full Scan 보다 느려지는 지점
Table Full Scan 의 경우 전체에서 몇 건을 조회하든 성능 상 차이가 없다.
반면 인덱스를 이용하면 전체 중에 몇 건을 조회하느냐에 따라 성능이 크게 달라진다. 특히 인덱스 클러스터링 팩터가 나쁘면, 같은 테이블 블록을 여러 번 액세스하기 때문에 I/O가 크게 늘어난다.
대용량 트래픽의 웹 프로그램의 경우, 전체 건수 중 극소량의 데이터만 읽고 쓰기에 NL 조인과 인덱스를 사용하는 게 유리하다.
대량 데이터를 읽고 갱신하는 배치 프로그램은 전체를 빠르게 처리해야 하기 때문에 Table Full Scan 과 해시 조인이 유리하다.
액세스 조건과 필터 조건
인덱스 액세스 조건 : 인덱스 스캔 범위를 결정하는 조건절. 인덱스 수직적 탐색을 통해 스캔 시작점을 결정하는데 영향을 미치며, 인덱스 리프 블록을 스캔하다가 어디서 멈출지 영향을 미친다.
인덱스 필터 조건 : 인덱스 리프 블록에서 실제로 테이블로 액세스할지 결정하는 조건절.
테이블 필터 조건 : 쿼리 수행 다음 단계로 전달하거나 최종 결과 집합에 포함할 지 결정한다.
비교 연산자 종류와 컬럼 순서에 따른 군집성
인덱스는 정렬되어 있기 때문에 같은 값을 같는 레코드들이 모여 있다. 인덱스 컬럼을 앞쪽부터 누락 없이 = 연산자로 조회하면, 조건절을 만족하는 레코드는 모두 군집해 있다.
반면, 어느 하나를 누락하거나 = 조건이 아닌 연산자로 조회하면, 조건절을 만족하는 레코드가 뿔뿔이 흩어진 상태가 된다.
전부 = 으로 비교할 경우
where CO1 = 1
and Col2 = 'A'
and Col3 = '나'
and Col4 = 'a'
5~ 7번에 군집해 있다.
선행 컬럼이 = 이고 맨 마지막 컬럼만 범위 검색 조건인 경우
where CO1 = 1
and Col2 = 'A'
and Col3 = '나'
and Col4 >= 'a'
5 ~ 10번에 군집해 있다.
중간 컬럼이 범위 검색 조건인 경우
where CO1 = 1
and Col2 = 'A'
and Col3 betwen `가` and `다`
and Col4 = 'a'
2,3,5,6,7,11 번으로 레코드가 흩어지게 된다.
결론
첫 번째로 나타나는 범위 검색 조건까지만 만족하는 인덱스 레코드는 모두 연속해서 모여 있다.
하지만, 그 이하 조건부터 만족하는 레코드는 비교 연산자가 무엇이든 상관없이 흩어지게 된다.
따라서 결합 인덱스를 구성할 때, 순서상 앞에 나오는 컬럼들이 =을 많이 쓰는지, 범위 검색을 많이 쓰는지 확인할 필요가 있다.
인덱스와 선택도
선택도는 데이터에서 특정 값을 잘 골라낼 수 있는 정도를 뜻한다. 만약 선택도가 1이면 모든 데이터가 유일하다. 따라서 선택도가 낮은 컬럼일수록 인덱스에 적합하다.
예를 들어, 성별은 두 가지 밖에 없기 때문에 선택도가 매우 높아서 성별인덱스를 써봤자 소용이 없다. 왜냐하면 인덱스 엑세스를 할 때 범위 스캔에 도움이 안되기 때문이다.
컬럼 순서 결정시, 선택도 이슈
그런데 결합 인덱스 컬럼 간 순서에는 선택도가 중요하지 않을 수가 있다.
예를 들어 SQL이 다음과 같다면
where gender = :GENDER
and customer_num = :CUST_NO둘 다 인덱스 액세스 조건이므로 어떤 컬럼이 오든 인덱스 스캔 범위는 같다.
(성별+고객 번호이든, 고객 번호 + 성별 이든간에 상관없이 인덱스 선두 컬럼을 모두 =으로 검색하게 되면 어느 컬럼을 인덱스 앞 쪽에 두든 블록 I/O 개수는 같다.)
따라서 인덱스 생성 여부에 있어서는 선택도를 따져야 하지만, 결합 인덱스에서 칼럼 간 순서를 결정할 때는 선택도가 의미가 없다.
4장 조인 튜닝
자연 조인, 소트 머지 조인, 해시 조인의 작동 원리
https://dataonair.or.kr/db-tech-reference/d-guide/sql/?mod=document&uid=356
