Index 의 모든 것

인덱스는 무엇이고 생성방법과 작동방식에 대한 단계별 설명, 인덱스 종류와 유형, 다중인덱스, 예제 케이스를 통한 테이블 컬럼에서 인덱스를 추가할 컬럼을 고르는 기준에 대해 정리해보았습니다.

-A library with tons of books created by DALL’E

인덱스란 무엇인가?

인덱스는 말 그대로 책의 맨 처음 또는 맨 마지막에 있는 색인이라고 할 수 있다. 이 비유를 그대로 가져와서 인덱스를 살펴본다면 데이터는 책의 내용이고 데이터가 저장된 레코드의 주소는 인덱스 목록에 있는 페이지 번호가 될 것이다.

DBMS도 데이터베이스 테이블의 모든 데이터를 검색해서 원하는 결과를 가져 오려면 시간이 오래 걸린다.

그래서 칼럼의 값과 해당 레코드가 저장된 주소를 키와 값의 쌍으로 인덱스를 만들어 두는 것이다.

DBMS의 인덱스는 항상 정렬된 상태를 유지하기 떄문에 원하는 값을 탐색하는데는 빠르지만 새로운 값을 추가하거나 삭제, 수정하는 경우에는 쿼리문 실행 속도가 느려진다. 결론적으로 DBMS에서 인덱스는 데이터의 저장 성능을 희생하고 그 대신 데이터의 읽기 속도를 높이는 기능이다. SELECT 쿼리 문장의 WHERE 조건절에 사용되는 컬럼이라고 전부 인덱스로 생성하면 데이터 저장 성능이 떨어지고 인덱스의 크기가 비대해져서 오히려 역효과만 불러올 수 있다.

어떤 데이터가 디스크의 어느 위치에 있는지에 대한 정보를 가진 주소록
데이터 - ROWID (주소) 쌍으로 저장됨
일반적인 조회 쿼리 실행시 메모리의 database buffer cache를 체크
자주 사용되는 테이블들이 캐싱되어 있어 여기에 데이터가 있을 경우 바로 찾아 출력하고 없을 경우 하드디스크에 있는 데이터 파일에서 데이터를 찾음
인덱스를 사용하면 이런 과정을 거치지 않고 바로 주소를 통해 찾아감

Primary Index vs Secondary Index

클러스터란 여러 개를 하나로 묶는다는 의미로 주로 사용되는데, 클러스터드 인덱스도 크게 다르지 않다.

인덱스에서 클러스터드는 비슷한 것들을 묶어서 저장하는 형태로 구현되는데, 이는 주로 비슷한 값들을 동시에 조회하는 경우가 많다는 점에서 착안된 것이다.

여기서 비슷한 값들은 물리적으로 인접한 장소에 저장되어 있는 데이터들을 말한다.

클러스터드 인덱스는 테이블의 프라이머리 키에 대해서만 적용되는 내용이다.

즉 프라이머리 키 값이 비슷한 레코드끼리 묶어서 저장하는 것을 클러스터드 인덱스라고 표현한다.

클러스터드 인덱스에는 프라이머리 키 값에 의해 레코드의 저장 위치가 결정되며 프라이머리 키 값이 변경되면 그 레코드의 물리적인 저장 위치 또한 변경되어야 한다.

그렇기 때문에 프라이머리 키를 신중하게 결정하고 클러스터드 인덱스를 사용해야 한다.

클러스터드 인덱스는 테이블 당 한 개만 생성할 수 있다. 프라이머리 키에 대해서만 적용되기 떄문이다.

이에 반해 non 클러스터드 인덱스는 테이블 당 여러 개를 생성할 수 있다.

인덱스 생성 방법과 작동 방식에 대한 단계별 설명

인덱싱할 열을 식별 a. 먼저 데이터베이스에서 검색 또는 조인 조건에 자주 사용되는 열 또는 정렬에 사용되는 열을 식별 b. 이러한 열을 인덱싱하면 쿼리 성능이 빨라진다 c. 인덱스를 너무 많이 추가하면 쓰기 성능에 영향을 줄 수 있으므로 현명하게 선택할 필요
색인을 생성 a. 인덱싱할 열을 식별했으면 적절한 SQL 명령을 사용하여 인덱스를 생성할 수 있다 b. 대부분의 관계형 데이터베이스에서 이 작업은 “CREATE INDEX”문 다음에 인덱스 이름, 테이블 이름 및 인덱싱할 열을 사용하여 수행됨, 예를 들어
```
 ```yaml
 CREATE INDEX index_name ON table_name(column_name);
 ```
```
c. 그러면 데이터베이스 관리 시스템이 인덱스 데이터 구조를 생성하고 이를 테이블 데이터와 함께 저장
데이터베이스 관리 시스템(DBMS)이 색인을 업데이트 a. 인덱싱된 열의 데이터가 삽입, 업데이트 또는 삭제될 때마다 DBMS는 이러한 변경 사항을 반영하도록 인덱스를 자동으로 업데이트 b. 이렇게 하면 인덱스가 기본 데이터와 동기화된 상태로 유지됨
쿼리 최적화 a. 쿼리가 실행되면 데이터베이스의 쿼리 최적화 프로그램은 쿼리를 실행하는 가장 효율적인 방법을 평가 b. 쿼리에 인덱싱된 열이 포함된 경우 최적화 프로그램은 검색 또는 정렬 작업 속도를 높이기 위해 인덱스를 사용하도록 선택할 수 있음
인덱스 검색 a. 인덱스를 쿼리에 사용할 때 DBMS는 인덱스 데이터 구조를 검색하여 테이블에서 해당 행을 빠르게 찾는다 b. 인덱스는 일반적으로 효율적인 검색, 삽입 및 삭제 작업을 가능하게 하는 트리와 유사한 데이터 구조인 B-트리로 구현된다. c. DBMS는 루트에서 리프 노드까지 트리 구조를 따라 쿼리 조건과 일치하는 인덱스 값을 찾는다 d. 일치하는 인덱스 값이 발견되면 DBMS는 테이블에서 연관된 행을 검색
쿼리 결과 a. 인덱스를 사용하여 관련 행을 찾은 후 DBMS는 추가 쿼리 조건 또는 정렬 요구 사항을 처리하고 최종 결과를 사용자에게 반환한다

인덱스 사용이 불가한 경우

인덱스 컬럼을 조건절에서 가공하면 인덱스 사용이 불가능
- 인덱스가 있는 컬럼이 조건절에서 사용되고 함수, 산술 연산 또는 원래 컬럼 값을 변경하는 다른 연산에 의해 처리되거나 조작된 경우 인덱스를 사용할수 없습니다. 이는 인덱스가 열의 원래 값을 기반으로 하고 해당 값을 조작하면 인덱스가 비효율적이기 때문입니다. 인덱스를 효과적으로 사용하기 위해서는 쿼리가 어떤 처리나 조작 없이 인덱스 컬럼을 직접 참조해야 합니다
부정형 비교는 인덱스 사용 불가능
- 인덱스는 not in, not like 또는 ‘! =’ 와 같은 부정 유형 비교에 덜 효과적이다. 일반적으로 가능한 일치 범위가 더 넓어지고 데이터베이스 엔진이 인덱스보다 더 많은 행을 검색해야 할 수 있기 때문이다 긍정비교, 이러한 경우 데이터베이스 엔진은 더 효율적일 수 있으므로 인덱스를 사용하지 않고 대신 전체 테이블 스캔을 수행하도록 선택할수 있다. 그러나 이것은 엄격한 규칙이 아니며 인덱스를 사용하거나 사용하지 않는 데이터베이스 엔진의 결정은 쿼리 계획 및 데이터 배포에 따라 다르다.
Is null 조건만으로는 인덱스 사용 불가
- is null 조건은 열 값이 null 인지 확인하여 일반적으로 이 특정 조건은 인덱스를 효과적으로 사용하지않습니다. 인덱스는 특정 값 또는 값 범위의 검색 속도를 높이기 위해 작성되지만 null 값은 인덱싱 구조에서 다르게 취급되는 경우가 많으며 이를 저장하고 액세스하려면 별도의 메커니즘이 필요하다. SQL Server와 같은 일부 데이터베이스는 인덱스에 null 값을 포함하지만 쿼리 속도 향상 효과는 특정 데이터베이스 엔진과 데이터 분포에 따라 다르다.

이러한 경우에는 데이터베이스 엔진이 전체 테이블 스캔을 수행하거나 다른 최적화 기술을 활용하여 쿼리를 효율적으로 처리해야 하는 경우가 많습니다.

인덱스 종류

B-TREE 인덱스

루트 / 브랜치/ 리프 블록

Select, delete, insert 작업에 효율적

분포도가 낮은 컬럼에 불리, OR 연산자에 대해 테이블 전체를 Full scan 하는 것은 위험

테이블의 어느 데이터에 접근하더라도 동일한 성능 보장

이런 방식으로 데이터를 액세스하기 때문에 어떤 empno를 조회하더라도 루트블록, 브랜치 블록, 리프 블록을 하나씩 액세스하게 됨

테이블의 데이터가 수정되면 해당 테이블의 B-Tree 인덱스에서도 insert, update, delete 등이 수행되고 이는 성능 저하의 원인이 됨

인덱스가 적용된 db에서 필요한 값을 찾을 때에는 가장 먼저 테이블을 조회하게 되는데 이과정에서 발생하는 I/O가 랜덤 액세스이기 때문에 B-Tree 인덱스를 사용할 때에는 랜덤 액세스의 양을 특히 신경써야 함

만약 랜덤 액세스의 양이 지나치게 많으면 B-Tree 인덱스를 사용하지 않는게 좋음

And 조건은 처리 범위를 감소시키고 OR 조건은 처리 범위를 증가시킴, B-Tree 인덱스는 처리 범위를 최소화하여 랜덤 액세스를 감소시키는 것이 중요하다.

비트맵 인덱스

0 또는 1로 인덱스를 관리

비트를 이용하여 컬럼값을 저장하고 ROWID 를 자동으로 생성

분포도가 낮은 컬럼에 적용할 때 유용 (컬럼이 갖는 데이터가 몇개 안될 때, ex 성별, 결혼여부 등)

비트를 직접 관리하므로 저장공간이 크게 감소하고 비트 연산을 수행할 수 있음

비트맵 인덱스는 인덱스 키값 + 시작 ROWID + 끝 ROWID + 비트맵 엔트리로 구성

OR 연산자를 포함하는 여러 개의 where 조건을 자주 사용할때 유리

Insert, update, delete 와 같은 쿼리에서는 무의미

REVERSE KEY 인덱스

B-Tree 인덱스와 거의 같지만 인덱스 키 값은 반대로 구성해 B-Tree 인덱스 생성

구조는 B-Tree 인덱스와 같고 단지 저장되는 데이터만 역으로 리프블록에 저장

인덱스 범위 스캔불가

우측 리프블록 경합 방지기능

일반적인 B-Tree 인덱스는 우측 리프블록으로 모든 데이터가 저장되지만 reverse key 인덱스는 인덱스 키 값이 순차적으로 증가하더라도 우측 리프블록에만 추가가 발생하지 않고 모든 인덱스 블록으로 추가되게 됨

역방향 키 인덱스는 인덱스에 저장되기 전에 인덱스 키 값을 역전시키는 방식으로 작동한다. 이 반전(reversal)은 인덱스 키 값이 단조롭게 증가하는 패턴을 갖는 상황에서 경합을 줄이고 성능을 개선하여 인덱스 구조 전체에 삽입을 보다 균등하게 분배하는데 도움이 된다. 그러나 키 반전으로 인해 키의 원래 순서가 깨지기 때문에 반전 키 인덱스는 범위 스캔에는 적합하지 않다.

함수 기반 인덱스

테이블의 컬럼들을 가공한 값으로 인덱스를 생성한 것

인덱스 유형

인덱스는 데이터베이스에서 데이터를 더 빠르게 검색할 수 있게 해주는 데이터 구조입니다. 인덱스에는 여러 가지 유형이 있으며, 각 인덱스에는 고유한 특성과 용도가 있습니다. 가장 일반적인 인덱스 유형은 다음과 같습니다

기본 인덱스

기본 인덱스는 데이터베이스 테이블의 각 레코드를 고유하게 식별하는 데 사용됩니다. 기본 인덱스는 일반적으로 테이블의 각 레코드에 대해 고유한 값을 갖는 열 또는 열 집합입니다.
보조 인덱스

보조 인덱스는 기본 키 이외의 검색 조건이 포함된 쿼리를 지원하는 데 사용됩니다. 보조 인덱스는 일반적으로 기본 키의 일부가 아니지만 쿼리에서 자주 사용되는 열 또는 열 집합입니다.
클러스터된 인덱스

클러스터된 인덱스는 인덱싱된 열의 값을 기반으로 테이블의 데이터 행을 물리적으로 정렬하는 데 사용됩니다. 테이블에는 클러스터된 인덱스가 하나만 있을 수 있으며, 데이터 행은 인덱싱된 열과 동일한 순서로 저장됩니다.
비클러스터 인덱스

클러스터링되지 않은 인덱스는 테이블의 데이터 행과 분리된 추가 인덱스 구조를 만드는 데 사용됩니다. 테이블에는 여러 개의 클러스터링되지 않은 인덱스가 있을 수 있으며 각 인덱스에는 고유한 구조와 데이터 페이지가 있습니다.

개별 인덱스 구성과 다중인덱스 구성 차이

아래를 보면 테이블1은 idx_name, idx_address 로 name과 address 컬럼 각각 인덱스를 구성하였고 테이블 2는 idx_index 로 name과 address 2개 컬럼으로 다중인덱스를 구성하였음.

아래 테이블들을 통해 개별 인덱스와 다중인덱스 구성 차이를 살펴보자

테이블1

Create Table tb_name(

uid int(11) not null auto_increment,

id varchar(20) not null,

name varchar(50) not null,

address varchar(155) not null,

primary key(‘uid’),

key idx_name(name),

key idx_address(address)

)

테이블2

Create Table tb_name(

uid int(11) not null auto_increment,

id varchar(20) not null,

name varchar(50) not null,

address varchar(155) not null,

primary key(‘uid’),

key idx_index(name,address)

)

select * from tb_name where name='홍길동' and address='경기도'

테이블1의 경우 각각의 필드에 Index가 설정되어 있기 때문에 MySQL은 name컬럼과 address컬럼을 보고 둘중에 어떤 컬럼의 수가 더 빠르게 검색되는지 판단후 빠른쪽을 먼저 검색하고 그 다음 다른 컬럼을 검색하게 된다. 이 경우 Index를 둘다 타는 것이 아니다.

그에 반해 테이블2의 경우 바로 원하는 값을 찾는다. 그 이유는 Index를 저장할때 name가 address를 같이 저장하기 때문입니다. 즉 name 과 address의 값을 함께 색인을 한 뒤 검색에서도 홍길동, 경기도 로 검색을 시도하게 됩니다. 이렇게 사용할 경우 테이블1의 경우 보다 테이블2의 경우가 더 빠른 검색을 할 수 있습니다.

하지만 다중 컬럼 인덱스를 다음과 같이 사용 한다면 Index를 타지 못합니다.(항상 A,B 로만 조회해야 한다면 다중, 개별로 조회해야하는 경우도 있으면 각각)

select * from tb_name where address='경기도'

이 경우 name이 함께 검색이 되지 않으므로 Index의 효과를 볼 수 없습니다.

다중 컬럼 인덱스를 사용할때는 index의 제일 왼쪽 컬럼이 where 절에 사용 되어야 합니다.

다중 열 인덱스 생성

CREATE INDEX multi_column_index ON table_name(column1, column2, column3);

다중 열 인덱스에서 맨 왼쪽 열은 인덱스 정의에 나열된 첫 번째 열이다. 위의 예에서 “column1”은 multi_column_index 의 가장 왼쪽 열이다.

DBMS가 쿼리 실행 시 다중 열 인덱스를 효과적으로 활용하기 위해서는 인덱스의 가장 왼쪽 열을 쿼리의 WHERE 절에 사용해야 한다

WHERE 절에서 가장 왼쪽열을 사용하지 않으면 DBMS에서 인덱스를 사용하지 않아 쿼리 실행 효율이 떨어질 수 있다.

# "multi_column_index"를 효과적으로 활용
SELECT * FROM table_name WHERE column1 = 'value1' AND column2 = 'value2';

# 가장 왼쪽 열인 "column1"이 사용되지 않으므로 DBMS에서 "multi_column_index"를 효과적으로 사용하지 못함
SELECT * FROM table_name WHERE column2 = 'value2';

사례기반 테이블 컬럼에 인덱스 추가

포스트 테이블은 id, 콘텐츠내용, 생성일, 수정일, 해시태그 필드를 갖는다

생성일, 수정일은 정렬되어야 하며 해시태그는 검색이 가능하다. 이러한 경우 어떤 컬럼에 인덱스를 설계해야할까?

해시태그: “해시태그” 열을 인덱싱하면 관련 게시물을 찾기 위한 쿼리에 사용되는 기본 필터가 해시태그로 검색 속도가 빨라집니다.
생성일: 생성 날짜별로 정렬해야 한다는 요구 사항을 언급했으므로 이 열을 인덱싱하면 생성 날짜별로 정렬 속도가 빨라지고 정렬된 결과를 더 빨리 검색할 수 있습니다.
수정일: 마찬가지로 “수정일” 열을 인덱싱하면 수정 날짜별 정렬 성능이 최적화되어 관련 결과를 더 빠르게 검색할 수 있습니다.

이러한 인덱스를 사용하면 해시태그로 더 빠르게 검색하고 생성 날짜 및 수정 날짜로 보다 효율적으로 정렬할 수 있습니다. 인덱스를 추가하면 테이블에서 사용하는 저장 공간이 늘어나고 쓰기 성능에 영향을 줄 수 있습니다. 그러나 빠른 쿼리 성능의 이점은 일반적으로 이러한 고려 사항보다 중요합니다.

인덱스 사용 시 고려사항

인덱스 키의 크기는 되도록 작게 설계해야 성능에 유리
분포도가 좋은 칼럼(좁은 범위), 기본 키, 조인의 연결 고리가 되는 칼럼을 인덱스로 구성
단일 인덱스 여러 개보다 다중 칼럼 인덱스의 생성을 고려
업데이트가 빈번하지 않은 칼럼으로 인덱스를 구성
JOIN 시 자주 사용하는 칼럼은 인덱스로 등록
되도록 동등 비교(=)를 사용
WHERE 절에서 자주 사용하는 컬럼에는 인덱스 추가를 고려
인덱스를 많이 생성하는 것은 INSERT/UPDATE/DELETE 의 성능 저하의 원인이 될수 있다
인덱스 스캐이 테이블 순차 스캔보다 항상 빠르지는 않다. 보통 선택도 가 5~10% 이내인 경우에 인덱스 스캔이 우수

참고자료

Interview_Question_for_Beginner

성능 향상을 위한 SQL 작성법