DataBase - Index

이번 블로그에서는 인덱스에 대해서 알아보려고 한다.

 

 

인덱스

- 데이터를 빠르게 찾을 수 있는 하나의 장치

- 인덱스는 보통 'B-트리' 자료 구조로 이루어진다.

 

 

1. B-트리

- 루트 노드, 브랜치 노드, 리프 노드로 구성

 

                     39 83 88

46 53    -   46 53 57 83     -    89 92 100

                       55 57

 

- 만약 57을 찾는다면 이동 경로는 39-83-46-53-57-57 이 된다.

- 루프노드 -> 브랜치 노드 -> 리프 노드 순으로 이동한다.

 

 

 

 

2. 인덱스가 효율적인 이유

- 모든 요소에 접근할 수 있는 균형 잡힌 트리구조와 트리깊이의 대수확정성 때문에

- 대수 확장성 : 트리 깊이가 리프 노드 수에 비해 매우 느리게 성장하는 것을 의미.

 

 

 

 

 

 

3. 인덱스를 만드는 방법

 

MySQL

 

- 클러스터형 인덱스와 세컨더리 인덱스 존재

• 클러스터형 인덱스 : 테이블의 데이터를 정렬한 형태로 저장하는 방식이다. 한 테이블에는 하나의 클러스터형 인덱스만 설정할 수 있으며, 설정시 테이블의 정렬 형태가 해당 클러스터형 인덱스의 열을 기준으로 정렬이된다. 주로, 기본키에 설정을 한다. 변경시 재정렬을 하게되므로 비용 발생한다.
• 세컨더리 인덱스 : 기본키 이외에 열을 인덱스로 설정하는 방식이다. 여러개의 세컨더리 인덱스를 생성가능하며 테이블을 정렬하지는 않는다. 

- primary key 옵션으로 기본키 생성시, 기본키 만들지 않고 unique not null 옵션을 붙이면 클러스터형 인덱스 제작 가능

- create index... 명령어 기반으로 만들면 세컨더리 인덱스 생성 가능

-> 하나의 인덱스만 생성할 것이라면 클러스터형 인덱스 사용

(속성을 하나만 사용해 데이터를 찾는 경우)

-> 세컨더리 인덱스는 보조 인덱스로 여러 개의 필드 값을 기반으로 쿼리를 많이 보낼 때 생성하는 인덱스

(속성을 여러개 사용해 데이터를 찾는 경우 - 여러개의 클러스터 또는 세컨더리 사용하는 복합 인덱스 사용)

 

-> "MySQL은 기본적으로 ID(PRIMARY KEY)를 클러스터형 기본으로 설정"

-> 클러스터형 인덱스 설정을 기본키를 유지하되 기본키에서 다른 칼럼으로 변경 가능

 

 

 

클러스터형 인덱스 기본키에서 다른 칼럼으로 변경 (기본키도 변경)

1. 기본키 삭제

2. 지정하고자 하는 칼럼을 UNIQUE INDEX로 설정

3. 지정하고자 하는 칼럼을 클러스터형 인덱스로 설정

ALTER TABLE member DROP PRIMARY KEY;
CREATE UNIQUE INDEX idx_user_name ON member(user_name);
ALTER TABLE member ADD PRIMARY KEY USING INDEX idx_user_name;

 

 

클러스터형 인덱스 기본키에서 다른 칼럼으로 변경 (기본키도 변경 x)

ALTER TABLE users ADD INDEX idx_clustered (last_name) CLUSTERED;

 

 

세컨더리 인덱스 생성1) 

CREATE INDEX idx_name ON member (user_name);
CREATE INDEX idx_name ON member (user_name, DESC);
CREATE INDEX idx_name ON member (user_name, ASC);

-> 인덱스명, 테이블명, 테이블 칼럼 명

-> ASC, DESC를 user_name 뒤에 삽입해 정렬기준 설정하여 출력시 정렬 가능

 

세컨더리 인덱스 생성2)

INDEX idx_name (user_name)

-> 테이블 생성시 안에 삽입해 설정

 

세컨더리 인덱스 추가)

ALTER TABLE member ADD INDEX idx_name (user_name);

 

세컨더리 인덱스 확인)

SHOW INDEX FROM member;

 

세컨더리 인덱스 삭제)

ALTER TABLE member DROP INDEX idx_name;

 

세컨더리 인덱스 사용)

SELECT * FROM member USE INDEX (idx_name) WHERE user_name = '이름';

 

 

 

Spring - Secondary Indexing

- 엔터티에 인덱스 생성

// 복합 인덱스
@Entity
@Table(name = "users", indexes = {
    @Index(name = "idx_name", columnList = "name"),
    @Index(name = "idx_address", columnList = "address")
})



// 하나의 칼럼으로 만든 세컨더리 인덱스
@Column(nullable = false)
@Index(name = "idx_name")
private String name;

 

- 사용

@Repository
public interface MemberRepository extends JpaRepository<Member, Long> {
    @Query(value = "SELECT * FROM member USE INDEX (idx_name) WHERE user_name = :username", nativeQuery = true)
    List<Member> findByUserNameUsingIndex(@Param("username") String userName);
}

 

- Mysql에서는 추가적으로 세컨더리 인덱스를 설정해 놓으면 자동으로 해당 칼럼을 이용한 검색등의 데이터 조회가 일어날 때 자동으로 세컨더리 인덱스를 적용해서 출력한다.

 

 

 

 

 

MongoDB

 

- 도큐먼트를 만들면 자동으로 ObjectID가 형성되며, 해당 키가 기본키로 설정된다. (클러스터형 인덱스)

- 세컨더리키도 부가적으로 설정해서 기본키와 세컨더리키를 같이 쓰는 복합 인덱스를 설정할 수 있다.

 

 

 

 

 

 

 

4. 인덱스 최적화 기법

1. 인덱스는 비용임을 생각하자.

- 인덱스는 기본적으로 두번 탐색 (인덱스 리스트(DB 목차), 컬렉션(데이터) 순으로 탐색)

- 컬렉션(데이터) 수정시 인덱스리스트도 수정되어야 함.

- B트리 균형 조정 비용 존재

- 데이터 분산 비용도 존재

- 컬렉션으로부터 조회하는 데이터가 많을 수록 비효율적

 

2. 항상 테스팅이 필요

- explain() 함수를 통해 인덱스를 만들고 쿼리를 보내며 걸리는 시간 등을 측정해 최적화를 해야한다.

 

3. 복합 인덱스는 같음, 정렬, 다중 키, 카디널리티 순이다.

- 여러개의 필드로 조회를 할때 복합 인덱스를 생성하는데, 이 인덱스는 생성할 때는 순서가 있고 생성 순서에 따라 인덱스 성능이 달라진다. 같음, 정렬, 다중키, 카디널리티 순으로 복합 인덱스를 생성해야 한다.

 

3-1) 어떠한 값과 같음을 비교하는 쿼리가 있다면 해당 칼럼(필드)을 사용하는 인덱스를 제일 먼저 인덱스로 설정

3-2) 정렬에 쓰는 필드라면 그 다음 인덱스로 설정

3-3) 다중 값을 출력해야 하는 필드, 즉 쿼리 자체가 > , < 등 많은 값을 출력해야 하는 쿼리에 쓰는 필드라면 나중에 인덱스를 설정한다.

3-4) 유니크한 값의 정도를 카디널리티라고 하며 이 카디널리티가 높은 순서를 기반으로 인덱스를 생성해야 한다. 예를 들어, age, email 중 email 칼럼이 우선되어야 한다.

 

 

 

 

(+) 카디널리티

- 해당 열의 고유한 값의 수

- 예를 들어, 국가를 10개로 설정하고 해당 국가를 가진 유저만 생성한다면 카디널리는 10이 된다.

- 카디널리티가 클수록 인덱스 생성후 사용에서 효율성을 가져올 수 있다.

- 반대로, 카디널리티가 작을수록 중복된 값이 많아 인덱스 사용시 비효율적이다.

 

 

 

 

 

 

복합인덱스 예시

// 생성
CREATE INDEX idx_user_info ON member (user_name, user_number, user_id);

- user_name, user_number, user_id 순으로 정렬

- 만약, 위의 예시처럼 작성시 user_name, user_number, user_id 를 모두 사용하여 검색해야 인덱싱이 적용된다.

- 만약, 하나의 칼럼을 기준으로 인덱싱 사용하여 최적화를 원할시 새로운 세컨더리 인덱스를 만드는것이 효율적이다.