0️⃣ INPUT-OUTPUT의 기본 단위
데이터베이스에서의 INPUT과 OUTPUT의 기본 단위를 Data Block 이라 한다.
데이터베이스 Data Block이란, OS를 기반으로 존재하는 디스크 블록들로 구성된다. 즉, 하나의 데이터 베이스 블럭은 여러개의 OS 상의 디스크 블럭으로 존재하는 것이다.
테이블과 INDEX는 아래 그림과 같이 BLOCK들로 구성된다.
1️⃣ 인덱스란?
인덱스는, 테이블과 클러스터와 연관된 독립적인 객체를 말하며, 추가적인 쓰기 작업과 저장 공간을 활용하여 데이터베이스 테이블의 검색 속도를 향상시키기 위한 자료구조이다.
만약 우리가 책에서 원하는 내용을 찾는다고 하면, 책의 모든 페이지를 찾아 보는것은 오랜 시간이 걸린다. 그렇기 때문에 책의 저자들은 책의 맨 앞 또는 맨 뒤에 색인을 추가하는데, 데이터베이스의 index는 책의 색인과 같다.
마찬가지로, 데이터베이스에서도 테이블의 모든 데이터를 검색하면 시간이 오래 걸리기 때문에 데이터와 데이터의 위치를 포함한 자료구조를 생성하여 빠르게 조회할 수 있도록 돕고 있다.
DB에 저장된 자료를 더욱 빠르게 조회하기 위해서 사용되기 때문에 따라서 적절하게 사용한다면 조회 속도를 개선 시킬 뿐만 아니라 디스크 I/O를 줄일 수 있는 수단이 된다.
✅인덱스의 관리
DBMS는 index를 항상 최신의 정렬된 상태로 유지해야 원하는 값을 빠르게 탐색할 수 있다. 그렇기 때문에 인덱스가 적용된 컬럼에 INSERT, UPDATE, DELETE가 수행된다면 각각 다음과 같은 연산을 추가적으로 해주어야 하며 그에 따른 오버헤드가 발생한다.
INSERT: 새로운 데이터에 대한 인덱스를 추가함
DELETE: 삭제하는 데이터의 인덱스를 사용하지 않는다는 작업을 진행함
UPDATE: 기존의 인덱스를 사용하지 않음 처리하고, 갱신된 데이터에 대해 인덱스를 추가함
✅인덱스 장점과 단점
1)장점
1.테이블을 조회하는 속도와 그에 따른 성능을 향상시킬 수 있다.
2.전반적인 시스템의 부하를 줄일 수 있다.
2) 단점
1.인덱스를 관리하기 위해 DB의 약 10%에 해당하는 저장공간이 필요하다.
2.인덱스를 관리하기 위해 추가 작업이 필요하다.
3.인덱스를 잘못 사용할 경우 오히려 성능이 저하되는 역효과가 발생할 수 있다.
👉만약 CREATE, DELETE, UPDATE가 빈번한 속성에 인덱스를 걸게 되면 인덱스의 크기가 비대해져서 성능이 오히려 저하되는 역효과가 발생할 수 있다.
그러한 이유 중 하나는 DELETE와 UPDATE 연산 때문이다. 앞에서 설명한대로, UPDATE와 DELETE는 기존의 인덱스를 삭제하지 않고 '사용하지 않음' 처리를 해준다고 하였다. 만약 어떤 테이블에 UPDATE와 DELETE가 빈번하게 발생된다면 실제 데이터는 10만건이지만 인덱스는 100만 건이 넘어가게 되어, SQL문 처리 시 비대해진 인덱스에 의해 오히려 성능이 떨어지게 될 것이다.
2️⃣ B* Tree 인덱스
B-Tree 인덱스는 RDB(관계형 DB)에서 가장 많이 사용하고 있는 인덱스이다. ⭐인덱스 칼럼의 값이 정렬된 채로 저장되어 있으며, 널(NULL)값을 저장하지 않는다.
또한, =로 검색하는 일치(Exact Match)검색과 BETWEEN, > 등과 같은 연산자로 검색하는 범위(Range)검색 모두에 적합한 구조를 띄고 있다.
인덱스 값을 찾는 과정
1 단계) 브랜치 블록의 가장 왼쪽 값이 찾고자 하는 값보다 작거나 같으면 왼쪽 포인터로 이동
2 단계) 찾고자 하는 값이 브랜치 블록의 값 사이에 존재하면 가운데 포인터로 이동
3 단계) 오른쪽에 있는 값보다 크면 오른쪽 포인터로 이동
이 과정을 리프 블록을 찾을 때까지 반복하며, 리프 블록에서 찾고자 하는 값이 존재하면 해당 값
을 찾은 것이고, 해당 값이 없으면 해당 값은 존재하지 않아 검색에 실패한다.
1) Leaf node
입력한 인덱스 칼럼의 모든 값이 저장되어 있다. 자세히 말하면, 인덱스 칼럼의 값과 함께 ROWID(데이터를 가지고 있는 행위 위치를 가리키는 레코드 식별자)가 함께 저장되어 있다.
인덱스 데이터는 인덱스를 구성하는 칼럼의 값으로 정렬되며, 만약 인덱스 데이터의 값이 동일하면 레코드 식별자의 순서로 저장된다.
ROWID 정보는 다음과 같이 나타낼 수 있다.
하나의 ROWID는 하나의 BLOCK 정보를 가지고 있으며, 실행 계획에서 ROW ID가 인덱스 시 사용 된 것을 볼 수 있다.
또한, 리프 블록은 ⭐양방향 링크(Double Link)를 가지고 있기 때문에 이것을 통해서 오름 차순(Ascending Order)과 내림 차순(Descending Order) 검색을 쉽게 할 수 있다.
2) B* Tree 인덱스 구조의 활용
1) ORDER BY로 인한 정렬 작업을 대체
SELECT deptno, ename
FROM emp
WHERE deptno in (10, 20)
ORDER BY deptno DESC;현재 주어진 테이블에는 deptno가 인덱스 정보로 있으며, 인덱스를 사용하여 where 절이 작성되어 있고 인덱스에 대한 정렬 기준이 마련되어 있다.
실행 계획을 살펴보자.
3번(INDEX RANGE SCAN DESCENDING)을 주목해보면, 애초에 인덱스 자체를 내림차순의 결과로 범위 검색을 하고 있는 것을 알 수 있다. 즉, ORDER BY로 인한 정렬 작업을 대체했다고 볼 수 있다.
2) MIN 또는 MAX 값을 효율적으로 처리
SELECT /*+ INDEX_DESC(E JOB_SAL_IDX) */
sal
FROM emp E
WHERE job = 'SALESMAN'
AND ROWNUM = 1;현재 주어진 테이블에는 JOB+SAL이 인덱스 정보로 있으며, WHERE 조건을 만족하는 값들 중 sal의 최대값을 찾고자 INDEX_DESC()힌트를 넣어 내림차순으로 접근 하도록 하고 있다. 또한, 최댓값이 찾아지고 나면 더 이상 데이터를 처리하지 않도록 STOP키인 ROWNUM = 1 조건을 추가하였다.
실행 계획을 보자.
2번을 보면, SQL 힌트를 통해 조건에 만족하는 데이터 중 내림차순으로 접근하게 하여 목표로 하는 최댓값을 찾아낸 것을 확인 할 수 있다.
단, 이것이 가능한 것은 1)MAX/MIN값을 찾고자 하는 컬럼을 데이터로 하는 인덱스가 있고, 2)이 인덱스는 데이터를 SORTING 해놓고 있음을 주의하자.
3)인덱스 스캔의 원리
Block이 36개, 처리해야 할 block을 5개라고 가정해보자.
결국 총 5번의 테이블로의 접근이 생성되는데, 이유는 ROWID에 의존하고 있기 때문이다.
하나의 ROWID에는 하나의 블럭의 정보가 저장되어 있기 때문에, 인덱스 사용에 의해 테이블로 접근할때 한번에 하나의 블록을 처리하게 된다.
그렇다면, 모든 쿼리들은 인덱스를 사용해야 하는가?
일반적으로 인덱스는 테이블의 전체 데이터 중에서 10~15$미만을 처리하는 경우에 효율적이다.
4)인덱스 선정
선정 기준은 다음과 같다.
- 분포도가 좋은 칼럼
- 자주 조합되어 사용되는 칼럼
- 수정이 빈번하지 않은 칼럼
- Foreign key로 사용되는 칼럼
- MIN 또는 MAX 값을 자주 구하는 칼럼
- 정렬 기준으로 자주 사용되는 칼럼
여기서 분포도 나쁘다는 것은, 컬럼이 Y/N 처럼 종류가 적을때 테이블의 데이터가 많아질 수록 중복되는 값이 많아지게 되는 것을 말한다.
반대로, 고객 ID와 같이 값의 종류가 다양하여 데이터가 많아져도 중복 값이 적기 때문에 분포도가 좋다고 한다.
참고자료.
https://mangkyu.tistory.com/96 [MangKyu's Diary]
https://www.youtube.com/c/전광철OCP
SQLD 전문가 가이드 책
