DB - 인덱스(index)

인덱스(Index)

지정한 컬럼들을 기준으로 메모리 영역에 목차를 생성하는 것

데이터베이스 테이블에 대한 검색 성능의 속도를 높여주는 자료 구조

특정 컬럼에 인덱스를 생성하면, 해당 컬럼의 데이터들을 정렬하여 별도의 메모리 공간에 데이터의 물리적 주소와 함께 저장된다. 인덱스가 생성된 컬럼을 조건으로 거는 쿼리문을 작업하면 옵티마이저에서 판단하여 생성된 인덱스를 탈 수 있다.

옵티마이저란?

https://coding-factory.tistory.com/743

[DB] 데이터베이스 옵티마이저(Optimizer)에 대하여

 

인덱스의 사용 이유

테이블을 만들고 안에 데이터가 쌓이게 되면 테이블의 레코드는 내부적으로 순서가 없이 저장된다. 이럴 경우 Where 절에 특정 조건에 맞는 데이터들을 찾아내려면 처음부터 끝까지 다 읽어서 검색 조건과 맞는지 비교해야 한다.(Full Table Scan)

인덱스 테이블은 데이터들이 정렬되어 저장되어 있기 때문에 해당 조건에 맞는 데이터들을 빠르게 찾아낼 수 있다.

또한 인덱스(Index)를 사용하면 이미 정렬되어 있기 때문에 Sort 과정에서 오는 부하를 피할 수 있다.

데이터가 정렬되어 있기 때문에 효율적으로 MIN, MAX를 찾을 수 있다.

 

인덱스의 단점

인덱스는 정렬된 상태를 계속 유지 시켜줘야 한다. 따라서 INSERT, UPDATE, DELETE가 발생할 경우 성능의 저하가 발생할 수 있다. (인덱스를 다시 정렬해야 하고, 원본 테이블과 인덱스 테이블을 전부 수정해야 하기 때문)

하지만 UPDATE와 DELETE의 경우 UPDATE와 DELETE 과정이 느린 것이지 그 값의 조회는 인덱스를 통해 빠르게 이루어지기 때문에 성능 저하가 없을 수도 있다.

또한 검색 시에도 전체 데이터의 10~15% 이하의 데이터를 처리하는 경우에만 효율적이고 그 이상의 데이터를 처리할 땐 인덱스를 사용하지 않는 것이 더 낫다.(넓은 범위를 인덱스로 처리 시 많은 오버헤드가 발생한다.) 그리고 인덱스를 사용하면 데이터베이스의 약 10%에 해당하는 저장공간이 추가로 필요하다. 또한 지나치게 인덱스가 많은 경우에도 오버헤드가 발생하니 필요한 인덱스만 생성하는 것이 좋다.

 

인덱스 관리

인덱스에 UPDATE와 DELETE가 수행된다면 계속 정렬을 해줘야 하고 부하가 발생한다. 이러한 부하를 줄이기 위해 인덱스는 삭제 대신 사용하지 않는 것으로 표시한다. 사용하지 않는 엔트리는 DBMS의 가비지 컬렉션 프로세스에 의해 정기적 또는 필요할 때 삭제된다.

INSERT : 새로운 데이터에 대한 인덱스 추가

DELETE :  삭제하는 데이터의 인덱스를 사용하지 않음 처리

UPDATE : 기존의 인덱스를 사용하지 않음 처리하고, 갱신된 데이터에 대해 인덱스 추가

 

인덱스 생성 전략

1. 조건절에 자주 등장하는 컬럼

2. 항상 = 으로 비교되는 컬럼

3. 중복 데이터가 최소한인 컬럼(분포도가 좋은 컬럼)

4. ORDER BY 절에서 자주 사용되는 컬럼

5. JOIN 조건으로 자주 사용되는 컬럼

6. 가능한 수정이 비번하지 않은 컬럼

7. 한 컬럼이 여러 인덱스 포함되지 않도록 설계

 

인덱스의 구조

인덱스의 구조로는 트리 기반 인덱스, 비트맵 인덱스, 함수 기반 인덱스 등이 존재하지만 대부분의 상용 DBMS에서는 트리 구조를 기반으로 하는 B+Tree 인덱스를 주로 사용한다.

B+Tree 는 루트에서 리프노드까지 모든 경로의 깊이가 같은 밸런스 트리 형태이며, 다른 인덱스에 비해 대용량 처리의 데이터 삽입과 삭제에 좋은 성능을 유지한다.

 

일반적인 B-Tree

B+Tree

출처 : https://jojoldu.tistory.com/243

인덱스는 루트 노드와 브랜치노드, 리프 노드로 이루어져 있고 페이지 단위로 관리된다.

리프 노드의 값들은 연결 리스트로 되어있기 때문에 범위 검색이 B-Tree보다 용이하다.

 

이렇게 B+Tree 구조를 사용하기 때문에 적은 트리 깊이로도 많은 량의 레코드를 검색할 수 있다.

트리 깊이가 리프 노드 수에 비해 매우 느리게 성장하는 것을 '대수확장성'이라고 한다.

 

인덱스 생성 방법

MySQL 의 경우 클러스터 인덱스와 세컨더리 인덱스가 있다.

 

◎ 클러스터형 인덱스

• 테이블 당 1개만 허용되며, 해당 컬럼을 기준으로 테이블이 물리적으로 정렬된다.
• 데이터는 기본적으로 오름차순으로 정렬을 진행
• 기본키를 설정하면 자동으로 클러스터형 인덱스가 적용된다.
  (MySQL에서는 Primary key가 있으면 Primary key를 클러스터형 인덱스로, 없다면 UNIQUE 하면서 NOT NULL인 컬럼을, 그것도 없으면 임의로 보이지 않는 컬럼을 만들어 클러스터형 인덱스로 지정한다.MongoDB에서는 도큐먼트를 만들면 자동으로 ObjectID가 생성되며 해당 키가 기본키로 설정된다.)
• 인덱스 자체의 리프 페이지가 곧 데이터(인덱스 자체에 데이터가 포함되어 있다고 볼 수 있다. 따라서 별도 인덱스 저장 공간을 적게 사용하면서 테이블 공간 자체를 활용한다.)
• 데이터의 입력, 수정, 삭제 시 항상 정렬 상태를 유지
• 넌클러스터형 인덱스보다 검색 속도는 빠르나, 데이터 입력, 수정, 삭제 시에는 느리다.

참고

https://inpa.tistory.com/entry/MYSQL-%F0%9F%93%9A-%EC%9D%B8%EB%8D%B1%EC%8A%A4index-%ED%95%B5%EC%8B%AC-%EC%84%A4%EA%B3%84-%EC%82%AC%EC%9A%A9-%EB%AC%B8%EB%B2%95-%F0%9F%92%AF-%EC%B4%9D%EC%A0%95%EB%A6%AC#%EB%B3%B4%EC%A1%B0_%EC%9D%B8%EB%8D%B1%EC%8A%A4_secondary_index

[MYSQL] 📚 인덱스(index) 핵심 설계 & 사용 문법 💯 총정리

 

◎ 넌클러스터형 인덱스

• 보조 인덱스(Secondary Index)라고도 한다.
• 후보키(각 데이터를 인식할 수 있는 최소한의 고유 식별 속성 집합)에만 부여 할 수 있는 인덱스
• 테이블 당 약 240 개의 인덱스 생성 가능
• 넌클러스터형 인덱스의 생성 시에는 데이터 페이지는 변화 없이 별도의 인덱스 페이지를 구성한다.
• 따라서 클러스터형과 달리 데이터 페이지의 원본이 정렬되지 않는다.
• 인덱스 자체의 리프 페이지는 데이터가 아니라 데이터가 위치하는 포인터(RID)이기 때문에 클러스터형보다 검색 속도는 더 느리지만 데이터의 입력, 수정, 삭제는 더 빠르다.
• 인덱스를 생성할 때 데이터 페이지는 그냥 둔 상태에서 별도의 인덱스 페이지를 따로 만들기 때문에 용량을 더 차지한다.

-- 인덱스 생성 []는 생략 가능, 복수 컬럼 지정 가능
CREATE [UNIQUE] INDEX 인덱스 명 ON 테이블 명(컬럼명1, 컬럼명2...)
-- 인덱스 삭제
DROP INDEX 인덱스 명
-- 인덱스 수정(ORACLE 의 경우 REBUILD를 하고 MySQL은 DROP후 ADD 한다.)
ALTER INDEX 인덱스 명 REBUILD
------------------------------------------------
ALTER TABLE 테이블 명
	DROP INDEX 인덱스명
    ADD UNIQUE KEY 인덱스명(컬럼1, 컬럼2, ....)

 

복합 인덱스(결합 인덱스)

두 개 이상의 컬럼을 합쳐서 인덱스를 만드는 것. 단일 컬럼으로는 나쁜 분포도를 가지지만 여러 개의 컬럼을 합친다면 좋은 분포도를 가지고, WHERE절에서 AND 조건에 많이 사용되는 컬럼들을 결합 인덱스로 구성한다.

 

※ 분포도(Selectivity)와 카디널리티(Cardinality)

카디널리티 : 특정 데이터 집합의 유니크한 값의 개수

분포도 : 카디널리티 / 전체 레코드 수

(따라서 분포도는 0~1 사이 값을 가지며 1일 때 모든 값이 유니크하다.)

 

컬럼 선택 조건

1. WHERE절에서 AND 조건으로 자주 결합되어 사용되면서 각각의 분포도보다 두 개 이상의 컬럼이 결합될 때 분포도가 좋아지는 컬럼들

2. 다른 테이블과 조인의 연결고리로 자주 사용되는 컬럼들

3. order by 에서 자주 사용되는 컬럼들

4. 하나 이상의 키 컬럼 조건으로 같은 테이블의 컬럼들이 자주 조회될 때

 

컬럼 순서 결정

컬럼의 순서를 잘못 배열하면 결합 인덱스의 발동 확률이 매우 낮아진다. WHERE 절에 결합 인덱스의 첫 번째 컬럼을 조건에 사용했다면 결합 인덱스가 발동되나, 결합 인덱스의 두번째 컬럼만을 조건으로 사용했다면 인덱스를 사용하지 못한다.

쿼리문 작성 시 결합 인덱스를 사용하고자 한다면 결합 인덱스의 컬럼 중 선행하는 컬럼부터 조건에 지정하여야 한다. 조건은 컬럼 전체를 순서대로 사용할 수도 있고, 선행하는 일부 컬럼을 순서대로 사용할 수 있다.

 

결합 인덱스 컬럼의 설정 시 고려사항 우선순위

1. WHERE절 조건에 많이 사용되는 컬럼이 우선

(첫 번째 컬럼을 조건에서 사용하지 않는다면, 그 인덱스는 사용되지 않기 때문. 따라서 많은 쿼리에서 공통적으로 사용된 조건절의 컬럼을 인덱스 선행 컬럼에 주로 사용한다. 다수의 쿼리에서 공통적으로 사용된다는 것은 필수 조건절이라는 의미와 동일하다.)

2. ==나 Equal로 사용되는 컬럼 우선, 그 다음 정렬에 쓰는 컬럼, 그 다음 다중 값 출력 관련 컬럼(<,>, between 등) 순서다.

(컬럼1~5가 있을 때 중간에 컬럼 3이 Equal 조건이 아니라면 후행 컬럼(컬럼4,컬럼5)에서 =을 사용해도 인덱스를 타지 않는다.)

3. 분포도가 좋은 컬럼이 처리 범위를 줄여주므로 우선

4. 자주 이용되는 순서대로 결합 인덱스 컬럼의 순서 결정

 

 

 

 

※ 분포도가 중요한 것은 = 로 조회할 경우이다. 분포도는 항상 연산자보다 후순위로 고려되어야 한다,

참고 : https://dataonair.or.kr/db-tech-reference/d-lounge/expert-column/?mod=document&uid=53233

결합 컬럼 인덱스와 단일 컬럼 인덱스 II

 

 

참고 사이트

https://inpa.tistory.com/entry/MYSQL-%F0%9F%93%9A-%EC%9D%B8%EB%8D%B1%EC%8A%A4index-%ED%95%B5%EC%8B%AC-%EC%84%A4%EA%B3%84-%EC%82%AC%EC%9A%A9-%EB%AC%B8%EB%B2%95-%F0%9F%92%AF-%EC%B4%9D%EC%A0%95%EB%A6%AC#%EB%8B%A8%EC%9D%BC_%EC%9D%B8%EB%8D%B1%EC%8A%A4_/_%EB%8B%A4%EC%A4%91_%EC%BB%AC%EB%9F%BC_%EC%9D%B8%EB%8D%B1%EC%8A%A4_%EC%B0%A8%EC%9D%B4%EC%A0%90

[MYSQL] 📚 인덱스(index) 핵심 설계 & 사용 문법 💯 총정리

https://coding-factory.tistory.com/755

[DB] 데이터베이스 결합 인덱스에 대하여

https://velog.io/@jwpark06/%ED%9A%A8%EA%B3%BC%EC%A0%81%EC%9D%B8-DB-index-%EC%84%A4%EC%A0%95%ED%95%98%EA%B8%B0

효과적인 DB index 설정하기

https://mommoo.tistory.com/109

[데이터베이스] 인덱스(INDEX)

https://jojoldu.tistory.com/243

[mysql] 인덱스 정리 및 팁

https://ssocoit.tistory.com/217

[자료구조] 간단히 알아보는 B-Tree, B+Tree, B*Tree

https://junghn.tistory.com/entry/DB-%ED%81%B4%EB%9F%AC%EC%8A%A4%ED%84%B0-%EC%9D%B8%EB%8D%B1%EC%8A%A4%EC%99%80-%EB%84%8C%ED%81%B4%EB%9F%AC%EC%8A%A4%ED%84%B0-%EC%9D%B8%EB%8D%B1%EC%8A%A4-%EA%B0%9C%EB%85%90-%EC%B4%9D%EC%A0%95%EB%A6%AC

[데이터베이스] 클러스터 인덱스와 넌클러스터 인덱스/ 개념 총정리

https://musclebear.tistory.com/57

[DB/Oracle/MySQL] table (unique) index 생성, 삭제, 수정, 조회 방법

https://m.blog.naver.com/cmw1728/222257396875

카디널리티(Cardinality)와 선택도(Selectivity)

https://hyeyul-k.tistory.com/13

[DB] 결합인덱스 및 컬럼 순서 결정 방법

https://khdscor.tistory.com/51

DB - 결합인덱스 및 컬럼 순서 결정 방법

https://dataonair.or.kr/db-tech-reference/d-lounge/expert-column/?mod=document&uid=53233

결합 컬럼 인덱스와 단일 컬럼 인덱스 II