1. 인덱스(Index)
1-1. 인덱스란?
- 데이터를 좀 더 빠르게 찾을 수 있도록 해주는 도구
조금 더 자세히 설명하자면, Disk에서 발생하는 I/O 시간은 Main Memory 에서 발생하는 I/O에 비해 매우 크다.
인덱스를 설정함으로서 DB의 데이터를 검색시 Diks 의 I/O가 빈번히 일어나지 않도록 하는 것이다.
- 속성 값을 기준으로 이미 정렬되어 있으며, 그에 해당하는 레코드 주소가 있어 빠르게 데이터 접근이 가능
- 인덱스 검색을 위한 조건은 WHERE 절에 인덱스로 설정된 컬럼명이 나와야 함
1-2. 장/단점
- 장점
- 검색 속도가 빨라진다.
-> (무조건 그런 것은 아니다.) : 인덱싱보다 전체 테이블 검색(Full Scan)이 더 나은 경우
- 적은 처리량으로 결과를 얻을 수 있어 쿼리 부하가 줄어든다.
-> 다른 요청 처리가 가능하고, 전체 시스템 성능이 올라간다.
- 단점
- DB 공간이 추가로 필요하다.(약 10%)
- 처음 생성할 때 시간이 많이 소요 할 수 있다.
- 데이터 변경작업이 자주 일어나게 되면 오히려 성능이 나빠질 수 있다.
2. 클러스터형 인덱스(Clustered Index)
2-1. 클러스터형 인덱스란?
- 테이블 전체가 정렬된 인덱스가 되는 방식(테이블 레코드들이, 인덱스 컬럼의 정렬 순서대로 적재되어 있는 것)
찾아보기의 끝, 즉 리프 페이지에는 실제 데이터가 저장되어 있다.
- 데이터와 함게 전체 테이블이 물리적으로 정렬된다.
- 테이블당 하나만 생성 할 수 있다.
2-2. Clustered Index 생성
- Primary Key 로 지정한 컬럼(열)
- UNIQUE NOT NULL 로 지정한 컬럼
UNIQUE(NULL)로 지정한 열은 보조 인덱스가 생성된다
- 위 2개가 함께 있으면 Primary Key 에만 생성
테이블당 1개의 Primary Key, Clustered Index 가 생성된다.
- 인덱스 생성 순서
- 행 데이터를 해당 열로 정렬
- 루트 페이지를 만든다.
2-3. 구조적 특징
- 클러스트형 인덱스는 B+Tree 형태로 구성되어 있다.
- Insert 시 Data 가 정렬되고 Index 는 Data Block 의 첫 번째 레코드의 주소값을 가지게 되며, Index 가 곧 바로 Data Block 에 접근해서 Secondary Index(Non-Clustered Index) 보다 보통 동작이 빠르다.
- Data 가 정렬되어 저장되므로, Secondary Index 에 비해 범위로 질의를 하는 것에 유리하다.(빈번한 I/O 가 덜 발생, But CUD에 대해서는 더 느리다.)
- 기본적으로 인덱스 칼럼 기준으로 레코드들이 오름차순으로 정렬된다.
- 인덱스 자체의 리프 페이지가 데이터 -> 인덱스 자체에 데이터가 포함
- 성능은 좋지만 테이블에 1개만 생성이 가능하여 어떤 컬럼에 생성하느냐에 따라 성능이 달라진다.
2-4. 주의할 점
- 생성 시, 데이터 페이지가 전체 재정렬이 된다.
- 많은 데이터가 이미 저장된 상태에서 클러스트형 인덱스를 생성하게 되면, 데이터 양에 따라 많은 시간이 소요할 수 있다.
업무 시간에 기존에 운영되던 테이블 작업 시 주의가 필요한 것이다.
3. 비클러스터형 인덱스(Non-Clustered Index = Secondary Index,보조인덱스)
3-1. 비클러스터형 인덱스란?
- 보조 인덱스(Secondary Index)라고도 불리며, 클러스터형 인덱스와 다르게 물리적으로 테이블을 정렬하지 않는다.
- 정렬된 별도의 인덱스 페이지를 생성하고 관리한다.
실제 데이터를 함께 가지고 있지 않다.
- 테이블 당 여러개 생성이 가능하다.
3-2. Non-Clusterd Index 생성
- 생성 순서
- 리프 페이지에는 인덱스로 선택된 컬럼의 값이 정렬
- 해당 컬럼 값마다 실제 데이터가 저장된 위치의 고유 값(포인터)가 생성(데이터 위치 포인터는 주소 값)
3-3. 구조적 특징
- Primary Key 이외에 필요한 정렬 기준이 있을 경우 사용한다.
- 테이블 당 여러 개를 가질 수 있다.
- Secondary Index 는 인덱스 페이지와 데이터 페이지가 구분되어 있다.
Index 의 리프 페이지는 데이터 자체가 아니고 데이터가 위치하는 주소의 고유값
- Data Record가 정렬되어 있지 않다.
- Unique 하지 않아도 가능하다.
3-4. 주의할 점
- 클러스터 인덱스보다 검색 속도는 느리지만 데이터의 CUD 는 덜 느리다.
- Data Record 가 Index 순서대로 정렬되어 있지 않아 범위 조건으로 검색 시에 많은 I/O가 발생할 수 있다.(성능 저하)
- 테이블 당 여러개의 Non-Clustered Index 를 생성할 수 있지만 남용하면 시스템의 성능 저하가 발생할 수 있다.
3-5. 클러스터형 인덱스와 비클러스터형 인덱스 비교
- 범위로 검색하는 경우 성능차이
- 클러스터형 인덱스
- 생성 시 정렬이 되어 있어 해당 범위의 리프 페이지만 읽으면 쉽게 데이터 조회가 가능하다. - 비클러스터형 인덱스(보조 인덱스)
- 범위에 해당하는 데이터가 서로 다른 데이터 페이지에 존재하여 원하는 데이터를 찾기 위해 더 많은 데이터 페이지를 읽어야 데이터를 얻을 수 있다.
- 데이터 Insert(저장)의 경우
- 클러스터형 인덱스
- 데이터 삽입을 위해 페이지 분할이 일어난다. - 비클러스터형 인덱스(보조 인덱스)
- 데이터 페이지를 정렬할 필요는 없으므로 데이터 페이지의 뒤쪽 빈 부분에 삽입한다.
4. Clustered, Non-Clustred(Secondry) 혼합
- 대부분 하나의 테이블에 클러스트형 인덱스와 비클러스트형 인덱스가 혼합되어 있는 경우가 많다.
- PK 는 기본적으로 존재하고, 추가로 조회가 자주 발생하는 컬럼에 대해 보조 인덱스를 추가한다.
- 두 개를 혼합하여 사용하는 경우 보조인덱스를 먼저 조회 후 클러스트형 인덱스를 조회하여 데이터를 검색한다.
이때, 보조인덱스는 (데이터 페이지 번호 + #오프셋) 대신 클러스트형 인덱스에 대한 컬럼 값을 찾는다.
4-1. 혼합해서 사용하는 이유
- 별도 구성되어 있다면, 데이터 삽입 시 클러스터와 보조 인덱스 모두 다시 재구성해야한다.
- INSETION 으로 클러스터형 인덱스의 리프 페이지(데이터 페이지)가 재구성이 된다면 데이터 페이지의 번호 및 각 데이터의 페이지 + # 오프셋이 대폭 변경이 된다.
- 보조 인덱스의 데이터 주소값도 대폭 변경이되어야 하고 이로 인해 시스템 부하가 일어날 수 있다.
4-2. 혼합 구성 시 주의할 점
- 보조 인덱스가 클러스터 인덱스의 컬럼 값을 저장하게 된다.
클러스터 인덱스의 속성값들이 클 수록 보조 인덱스가 차지하는 공간도 커지게 된다.
-> 클러스터 인덱스로 설정할 열의 속성값이 자리수가 적은 것이 좋다.
참고
'BackEnd > DataBase' 카테고리의 다른 글
| [DB] MySQL/MariaDB Pivot(피벗) 테이블 사용/만들기 (0) | 2022.12.01 |
|---|---|
| [DB] CouchBase 설치하기(With. Intel Mac) (2) | 2022.11.13 |
| [DB] NoSQL DataBase 정리 (2) | 2022.11.12 |
| [MySQL] DATEDIFF, TIMESTAMPDIFF 함수를 통한 날짜 차이 계산하기 (2) | 2022.08.29 |
| [MYSQL] IN, EXISTS 절 활용하기 (0) | 2022.08.27 |
