인덱스

인덱스

특정 데이터를 검색하는 쿼리를 실행하면 첫 행부터 마지막 행까지 모든 데이터를 확인하게 된다.
대용량 데이터에서는 효율적인 검색을 위해 인덱스를 사용하기도 한다.

검색 쿼리를 날릴 때 워크벤치에서 결과창 우측의 탭 중 Execution Plan 탭을 확인하면 해당 쿼리가 인덱스를 사용해서 검색되었는지 확인할 수 있다.

장단점

검색 성능 향상으로 시스템 전체의 성능을 향상시키는 장점을 가진다.
하지만 인덱스를 생성하려면 DB에 추가적인 공간이 필요한데, DB의 약 10% 공간을 차지하게 된다.

그러므로 소용량 데이터를 가지는 DB에서는 적합하지 않다.

대용량 데이터를 가지는 DB에 검색이 자주 일어나는 컬럼에 대해서 인덱스를 만드는 것이 좋다.

인덱스 특징과 생성 고려사항

1. 인덱스는 대용량 데이터 테이블에 적합
2. 컬럼 단위 적용. 여러 개 컬럼에 묶어서 적용도 가능
3. WHERE 문에서 자주 사용하는 컬럼에 만드는 것이 좋음
4. 가능한 중복도가 낮은 컬럼에 생성하는 것이 좋음
5. 외래키를 지정한 열에는 자동으로 외래키 인덱스가 생성된다.
6. JOIN이 자주 일어나는 컬럼에 생성하는 것이 좋음
7. INSERT/UPDATE/DELETE가 자주 일어나는 테이블에 인덱스가 많다면, 성능이 떨어질 수 있음.
8. 꼭 필요한 열에만 인덱스를 만들어야함
9. 인덱스는 조회 성능을 높이지만, 추가적인 공간을 차지하며 데이터 변경시에는 성능을 떨어뜨릴 수 있음
10. 클러스터형 인덱스는 테이블 당 하나만 생성 가능
11. 클러스터형 인덱스가 테이블에 아예 없는 것이 좋은 경우도 있다. (7번과 관련된 이유.)
12. 사용하지 않는 인덱스는 제거하는 것이 좋다.

인덱스 종류

1. 클러스터형 인덱스 : 데이터가 특정 컬럼에 맞추어 물리적으로 정렬됨.(오름차순 정렬)
   • Clustered Index. 테이블당 한 개만 생성할 수 있다.
     행 데이터를 인덱스로 지정한 컬럼에 맞추어 자동 정렬한다.
2. 보조 인덱스 : 추가적인 정보를 가지며, 그 정보를 사용해 원하는 데이터에 접근 가능. 정렬되지 않으며, 실제 데이터를 저장하지 않고 저장된 주소(RID)를 참조함.
   • Secondary Index. 테이블당 여러 개를 생성할 수 있다.

자동으로 생성되는 인덱스

PK 클러스터형 인덱스

MySQL에서는 PK 지정 시 이를 자동으로 클러스터형 인덱스로 만든다.
PK는 테이블 내에서 최소성과 유일성을 만족하기 때문에 인덱스를 만들기 적합하다.

SHOW INDEX FROM 테이블명;

 

명령어를 사용해 테이블에 지정된 인덱스를 확인할 수 있다. PK가 지정된 테이블에 대해서 인덱스를 조회하면 Column_name 값으로 PK의 컬럼명이 반환된다.

 

UNIQUE 보조 인덱스

CREATE TABLE tbl2 (
    a INT PRIMARY KEY,
    b INT UNIQUE,
    c INT UNIQUE,
    D INT
 );

 

컬럼에 UNIQUE를 지정하면 해당 컬럼들에 대해서도 자동으로 인덱스가 만들어진다.

다만 앞서 설명했다시피 클러스터형 인덱스는 테이블 당 한 개만 생성이 가능하므로 PK에만 클러스터형 인덱스가 만들어진다. 그리고 나머지 UNIQUE 컬럼들에는 보조 인덱스가 생성된다.

 

PK가 없는 경우 - UNIQUE NOT NULL 클러스터형 인덱스

CREATE TABLE tbl3 (
    a INT UNIQUE NOT NULL,
    b INT UNIQUE,
    c INT UNIQUE,
    D INT
 );

만약 PK가 지정되지 않은 경우에는 UNIQUE NOT NULL로 지정된 컬럼에 대해서 클러스터형 인덱스가 생성된다.

PK, UNIQUE NOT NULL 없는 경우 - 히든 클러스터형 인덱스

PK도, UNIQUE NOT NULL 컬럼도 없는 경우에는 DBMS 내부에서 GEN_CLUST_INDEX 라는 이름의 히든 컬럼(인조키)과 히든 클러스터형 인덱스를 생성한다.

이는 6바이트 필드로, 데이터가 삽입됨에 따라 단조적으로 크기가 증가할 수 있다.
또한 데이터 삽입 순서대로 정렬을 시킨다. 이 히든 클러스터형 인덱스는 SHOW INDEX FROM 테이블명 명령어로 확인이 불가능하다.

PK가 추후에 추가되는 경우의 동작

PK가 테이블 생성 당시에 없고, 추후에 ALTER를 통해 추가되는 경우에 클러스터형 인덱스는 다음과 같이 변경된다.

• UNIQUE NOT NULL : UNIQUE NOT NULL에 대한 클러스터형 인덱스는 보조 인덱스로 변경되고, PK에 대한 클러스터형 인덱스가 새로 생성된다.
• GEN_CLUST_INDEX : GEN_CLUST_INDEX에 대한 클러스터형 인덱스는 삭제되며, PK에 대한 클러스터형 인덱스가 새로 생성된다.

즉, 클러스터형 인덱스 생성에서 PK가 가장 우선순위가 높음.

참고:
https://dev.mysql.com/doc/refman/5.7/en/innodb-index-types.html
https://stackoverflow.com/questions/71846916/if-i-create-a-table-without-a-primary-key-then-add-a-primary-key-is-the-primary

인덱스 직접 생성

CREATE 인덱스 생성

CREATE 저장데이터종류 INDEX 인덱스명
    인덱스타입
    ON 테이블명(KEY_PART, ...)
    인덱스옵션
    [ ALGORITHM_OPTION | LOCK_OPTION ] ...

위 명령으로 보조 인덱스를 직접 생성할 수 있다.

저장 데이터 종류

• UNIQUE : 인덱스는 기본적으로 중복을 허용하므로 중복을 허용하고 싶지 않다면 UNIQUE 인덱스로 생성
• FULLTEXT : 전체 텍스트 인덱스
• SPATIAL : 점, 선, 면과 같은 N차원 공간 데이터와 관련된 인덱스

인덱스 타입

인덱스의 저장 방식을 지정
B-TREE, HASH 중에서 선택이 가능하며 기본적으로는 B-TREE 형식 사용

USING {BTREE | HASH}

KEY_PART

{컬럼명 [(길이)] | (표현식)} [ASC | DESC]

특정 컬럼 혹은 컬럼들에 대해 인덱스를 만들 수 있음.
컬럼마다 정렬 방식을 지정할 수 있으며, 기본적으로 오름차순 정렬

이외에도 다음과 같은 옵션들이 존재하지만 잘 사용하지 않는다.

인덱스옵션

{ KEY_BLOCK_SIZE [=] value
  | index_type
  | WITH PARSER parser_name
  | COMMENT 'string'
  | {VISIBLE | INVISIBLE}
  | ENGINE_ATTRIBUTE [=] 'string'
  | SECONDARY_ENGINE_ATTRIBUTE [=] 'string'
}

ALGORITHM_OPTION

ALGORITHM [=] {DEFAULT | INPLACE | COPY}

LOCK_OPTION

LOCK [=] {DEFAULT | NONE | SHARED | EXCLUSIVE}

테이블 상태 확인

테이블 상태 확인 명령어로 테이블에 적용된 인덱스 길이를 확인할 수 있다.

SHOW TABLE STATUS LIKE '테이블명';

Index_length 컬럼의 값으로 확인 가능.
하지만 새로 인덱스를 만든 경우에 위 명령어로 확인하더라도 값이 0이다.
이러한 경우 다음 명령어로 적용을 시켜줘야 한다.

ANALYZE 인덱스 적용

인덱스를 새로 만드는 경우 적용을 시켜줘야 한다.

ANALYZE TABLE 테이블명;

이제 테이블에 인덱스가 적용되었다.

DROP 인덱스 삭제

DROP INDEX 인덱스명 ON 테이블명

인덱스를 모두 제거할 때는 되도록 보조 인덱스부터 삭제하도록 한다. 만약 클러스터형 인덱스를 먼저 삭제하는 경우 클러스터형 인덱스의 루트 페이지가 사라지므로 이를 참조하는 보조 인덱스의 리프 페이지는 재구성이 이루어진다. (페이지 번호 + #오프셋) 애써서 재구성한 보조 인덱스를 삭제하는 불필요한 작업이 이루어지기 때문에, 인덱스를 모두 제거한다면 보조 인덱스부터 삭제한다.

인덱스 주의사항

데이터 변경 작업이 자주 일어나는 경우에 인덱스를 사용하면 오히려 성능이 나빠질 수 있다. 예시로 이미 인덱스가 존재하는 테이블에 데이터를 삽입하려는 경우, 인덱스 갱신을 위해 오랜 시간이 걸린다.(페이지 분할 관련)

 

또한 데이터가 이미 입력된 테이블에 인덱스를 추가하는 것은 성능면에서 매우 안 좋은 영향을 끼칠 수 있다. 그렇기 때문에 초기 테이블 설계 시 제약조건 설정 및 인덱스 지정을 하는 것이 중요하다.

 

만약 꼭 변경 작업이 필요하다면, 인덱스를 삭제하고 데이터를 삽입한 후 다시 인덱스를 만드는 것이 성능면에서 좋다.

클러스터형 인덱스 - 갱신이 성능에 미치는 영향

인덱스는 보통 B-Tree 구조로 생성된다.

 

B-Tree는 Balanced Tree로 말 그대로 균형을 이루는 트리이다. 트리 내부의 노드들은 페이지 구조를 가지며, 하나의 페이지는 제한된 갯수만큼 데이터를 저장한다.

 

클러스터형 인덱스와 관하여 페이지 내부 데이터들은 순차적으로 정렬이 되어 있다. 그렇기 때문에 모든 인덱스를 순차적으로 조회할 필요 없이 O(logN)의 시간 복잡도로 데이터를 찾을 수 있다.

 

인덱스 갱신은 데이터 삽입이 일어날 때 발생한다. 데이터의 삽입이 일어나는 경우에는 정해진 위치에 데이터를 넣기 위해 페이지 내부의 데이터를 한 칸씩 밀고 데이터를 삽입하게 된다. 페이지는 제한된 크기를 가지기 때문에 페이지가 가득찬 경우에는 페이지를 분할해야 한다.

 

MySQL은 비어있는 페이지를 한 개 확보한 후에, 가득찬 페이지의 데이터를 공평하게 나누게 된다.

 

B-Tree의 균형을 이루기 위하여 리프 노드의 페이지 분할이 일어나면, 중간 노드의 페이지 데이터도 갱신이 된다. 만약 중간 노드의 페이지도 가득찼다면 연쇄적으로 페이지 분할이 발생한다.

 

이처럼 하나의 데이터를 삽입할 때 여러 노드에서 페이지 분할이 발생하게 되며 성능 저하가 발생한다. 또한 이 과정에서 트리의 높이가 증가되는 것도 성능 저하에 영향을 미친다.

클러스터형 인덱스, 보조 인덱스 성능 비교

저장 비교

• 클러스터형 인덱스 : 리프 페이지에 저장되는 데이터는 실제 테이블 데이터
• 보조 인덱스 : 리프 페이지에 저장되는 데이터는 테이블 데이터가 위치하는 주소(RID)

조회 성능

B-Tree 인덱스에서 각 노드는 페이지 구조로 되어있고, 페이지 내부에 데이터들이 저장된다.
클러스터형 인덱스는 테이블 자체의 정렬과 밀접하게 연관이 되어 있으므로 한 페이지의 크기가 보조 인덱스의 페이지보다 크다.

 

페이지 크기가 작을 수록 트리 내부의 여러 페이지를 조회해야 하기 때문에 더 오랜 시간이 걸린다.
그래서 클러스터형 인덱스를 사용하는 데이터 조회가 보조 인덱스를 사용하는 데이터 조회보다 빠르다.

갱신 성능

데이터 삽입 시 클러스터형 인덱스는 순서를 유지해야 하기 때문에 연쇄적인 페이지 분할과 관련하여 성능 문제가 발생했다.

보조 인덱스는 순서를 유지할 필요없이 참조만 하면 되기 때문에 페이지 분할과 관련된 성능 문제가 더 적게 발생한다.

정리

	클러스터형 인덱스	보조 인덱스
검색	빠름. 페이지 크기 크고, 데이터 정렬됨.	느림. 페이지 크기 작고 정렬 x. 실제 데이터 아닌 주소(RID) 참조.
입력/수정/삭제	느림. 정렬을 위한 연쇄적 페이지 분할.	빠름. 정렬x ->상대적으로 페이지 분할이 적게 발생
인덱스 생성	1개만 가능(PK 생성 시 자동 생성)	여러 개 가능. 성능을 위해 꼭 필요한 컬럼에만 생성