오늘 학습할 내용은 2절이다.
2.1 💡 인덱스 구조 및 탐색
데이터를 찾는 방식은 크게 2가지 방법이 있다.
- 테이블 전체를 스캔하는 방법
- 인덱스를 이용하는 방법
테이블 전체 스캔과 관련해서는 튜닝 요소가 많지 않으나, 인덱스와 관련해서는 튜닝 요소가 매우 많고 기법 또한 다양하다.
✅ 인덱스 튜닝의 두 가지 핵심 요소.
인덱스는 주로 테이블에서 소량 데이터를 검색할 때 사용한다.
특히 OLTP 시스템에서 소량 데이터를 주로 탐색하므로 인덱스 튜닝이 가장 중요하다.핵심요소는 크게 2가지이다.
- 인덱스 스캔 효율화 튜닝 : 인덱스 스캔 과정에서 발생하는 비효율을 줄이는 것
- 랜덤 액세스 최소화 튜닝 : 테이블 액세스 횟수를 줄이는 것 (인덱스 스캔 후 테이블 레코드를 읽을 때 랜덤 I/O 방식을 사용하므로)
둘 중 성능에 미치는 영향이 더 큰 것은 랜덤 액세스 최소화 튜닝이다.
1절부터 강조하는 것 중 하나이다.
바로, DB 성능이 느린 이유는 디스크 I/O 때문이다. 읽어야 할 데이터가 많고, 그 과정에 디스크 I/O가 많이 발생할수록 느릴 수 밖에 없다. 인덱스를 많이 사용하는 OLTP일수록 디스크 I/O 중에서도 랜덤 I/O가 특히 중요하다.2.1.2. ✍️ 인덱스 구조
인덱스는 대용량 테이블에서 필요한 데이터만 빠르게 효율적으로 액세스하기 위해 사용하는 오브젝트다.
인덱스를 통해 우리는 DB내 일부 데이터만 읽고 멈출 수 있는 작업을 수행할 수 있다. 즉 범위 스캔이 가능하다. 범위 스캔이 가능한 이유는 마찬가지로 인덱스라는 존재가 있기에 데이터가 정렬되어 있기 때문이다.DBMS는 일반적으로 B※Tree 인덱스를 이용한다. 루트를 기반으로 브랜치를 거쳐 리프로 펼쳐지는 형태이다.
이 중에서 루트와 브랜치 블록에는 키 값을 갖지 않는 특별한 레코드인 LMC가 있다.
LMC (Leftmost Child) : 자식 노드 중 가장 왼쪽 끝에 위치한 블록LMC가 가리키는 주소를 찾아간 블록에는 키값을 갖는 첫 번째 레코드보다 작거나 같은 레코드가 저장되어 있다.
리프 블록에는 키값 순으로 정렬되어 있을 뿐만 아니라 ROWID 또한 가지고 있어, 인덱스 키값과 ROWID를 기반으로 정렬되어 있다.
인덱스를 스캔하는 이유는 주어진 범위 내의 데이터를 빨리 찾고 거기서 ROWID를 얻기 위해서이다.
ROWID는 DBA(데이터 블록 주소)와 로우 번호로 구성되므로 이 값을 알면 테이블 레코드를 찾을 수 있다.
- ROWID : 데이터 블록 주소 + 로우 번호
- 데이터 블록 주소 : 데이터 파일 번호 + 블록 번호
- 블록 번호 : 데이터 파일 내에서 부여한 상대적 순번
- 로우 번호 : 블록 내 순번
인덱스 탐색 과정은 2가지로 나뉜다.
1. 수직적 탐색 : 인덱스 스캔 시작지점을 찾는 과정
2. 수평적 탐색 : 데이터를 찾는 과정우선 수직적 탐색부터 살펴보자..!
2.1.3. ✍️ 인덱스 수직적 탐색
정렬된 인덱스 레코드 중 조건을 만족하는 첫 번째 레코드를 찾는 과정.
인덱스 수직적 탐색은 루트 블록에서 부탁 시작하여 브랜치 블록에 저장된 각 인덱스 레코드는 하위 블록에 대한 주소값을 갖는다.
2.1.4. ✍️ 인덱스 수평적 탐색
수직적 탐색을 통해 스캔 시작점을 찾았으면, 찾고자 하는 데이터가 더 나오지 않을 때까지 인덱스 리프 블록을 수평적으로 스캔한다.
인덱스 리프 블록은 양방향 연결 구조로 되어 있어 서로 앞뒤 블록에 대한 주소값을 갖는다.
인덱스를 수평적으로 탐색하는 이유는,
첫째 조건절을 만족하는 데이터를 모두 찾기 위함이고
둘쨰 ROWID를 얻기 위해서이다.2.1.5. ✍️ 결합 인덱스 구조와 탐색
2개 이상의 컬럼을 결합해서 인덱스를 만들 수도 있다.
※ 참고 사항 BTree의 B는 Balanced를 의미한다.
(어떤 값을 탐색하더라도 인덱스 루트에서 리프 블록에 도달하기까지 읽는 블록 수가 같음.
따라서 루트 ~ 리프 블록까지의 높이는 항상 같다.)2.2 💡 인덱스 기본 사용법
인덱스를 Range Scan하는 방법을 의미한다.
그런데, 인덱스를 Range Scan 할 수 없는 순간도 있다.
"인덱스 컬럼을 가공하면 인덱스를 정상적으로 사용할 수 없다.
-> 인덱스 컬럼을 가공한다면, 인덱스 스캔 시작점을 찾을 수가 없다.예를 들면 이런 것이다.
where nvl(주문수량, 0) < 100가공하지 않은 주문수량으로 인덱스를 만들었는데, 값이 NULL이면 0으로 치환한 값을 기준으로 100보다 작은 레코드를 찾는 쿼리를 실행하면, 스캔 시작지점을 확인할 수 없기에 테이블을 전체 스캔하게 된다.
또한, 다음 예시는 UNION ALL을 통해 해결할 수 있다
[AS-IS] SELECT * FROM 고객 WHERE 전화번호 IN (:tel_no1, :tel_no2) [TO-BE] SELECT * FROM 고객 WHERE 전화번호 =:tel_no1 UNION ALL SELECT * FROM 고객 WHERE 전화번호 =:tel_no2
위와 같은 방식으로 수행한다면, 각 브랜치 별로 인덱스 스캔 시작점을 찾을 수 있어 Range Scan이 가능하다.
인덱스를 Range Scan 하기 위한 가장 첫 번째 조건은 인덱스 선두 컬럼이 가공하지 않은 상태로 조건절에 있어야 한다는 사실이다.
그러나 문제는 또 존재한다.
인덱스를 Range Scan한다고 해서 또 성능이 무조건 좋은 건 아니라는 사실이다.
정말이지 반복되는 방식으로 문제가 계속해서 나온다 ㅋㅋㅋㅋ
-> 인덱스 Range Scan을 한다의 기준은 인덱스 리프 블록에서 스캔하는 양을 기준으로 결정해야 한다.
추가로, 조건절에 데이터 형변환이 이루어진 경우에도 Range Scan을 할 수 없다.
ex) 날짜 = 19981225
(오라클에서는 자동으로 형변환 처리를 해준다고 한다.)그렇지만, 혹시 모를 문제를 대비하여 날짜나 특정 데이터 형태의 포맷을 정확히 지정해주는 습관이 중요하다.
ex) 날짜 = TO_DATE('01-JAN-2018', 'DD-MON-YYYY').위와 같이 TO_CHAR, TO_DATE, TO_NUMBER와 같은 형변환 함수를 적극적으로 활용하여 조건 절에 입력해 형변환으로 인한 문제가 발생해선 안되도록 해야 한다.