인덱스 뿌시기

텐저린티·2023년 10월 11일
db알쓸신잡
0

알쓸신잡

목록 보기
7/10

💡 인덱스는 검색을 위해 나머지를 희생하는 것

어디선 쓰지마라, 어디선 왜 안 썼냐.

이제 인덱스 고민은 그만!

MySQL의 인덱스를 바탕으로 알고리즘과 인덱스 활용을 알아보며, 코드에 녹여봅시다.

랜덤 I/O와 순차 I/O

인덱스가 검색에 좋은 이유를 알아보려면 우선 I/O 방식에 대해서 간단히 알아봐야 합니다.

결론부터 말씀드리면, I/O 가 필요할 때 랜덤 I/O 를 지향하고 순차 I/O 를 지양해야 합니다.

순차 I/O가 랜덤 I/O 보다 전체 처리량이 높을 수밖에 없기 때문입니다.

이유는 간단합니다. 디스크 헤드가 움직이는 횟수가 적기 때문입니다.

우리가 쿼리에 대해서 신경쓰고 튜닝으로 성능을 개선하려는 노력 모두가 결국 I/O 횟수 자체를 줄이는 목적에 있습니다.

쿼리를 처리하는 데 꼭 필요한 데이터만 읽도록 하는 방법에 인덱스가 있습니다.

인덱스란?

인덱스를 색인으로 자주 표현합니다.

맞습니다. 색인으로 우리는 원하는 정보를 쉽게 찾을 수 있습니다. 인덱스도 마찬가지입니다.

위 그림처럼 컬럼의 값과 해당 레코드가 저장된 주소를 키와 값의 쌍으로 삼아 인덱스를 만듭니다.

우리는 인덱스에 저장된 컬럼의 값과 레코드 저장 주소를 이용해서 데이터를 빠르게 접근할 수 있습니다.

하지만, 색인에 내용이 중구남방으로 되어있다면 어떻게 될까요?

가나다 순서도 아니고, 페이지 순서도 아닌 그냥 무의미한 상태로 나열된다면 쉽게 찾을 수 있을까요?

아니죠. 쉽게 찾을 수 없습니다. 색인은 대체로 가나다 순으로 정렬되어 있기 때문에 우리가 원하는 키워드를 금방 찾을 수 있게 됩니다. 만약에 찾고자 하는 글자의 첫 글자가 ㄷ으로 시작한다면, 우리는 ㄱ, ㄴ 에 대한 색인은 넘기고 ㄷ 부터 찾을 겁니다. 탐색 속도가 빨라지죠.

인덱스도 마찬가집니다. 인덱스에서 정렬은 아주 중요한 개념입니다.

인덱스를 활용하고, 인덱스가 동작하는 모든 근간에 정렬이 있습니다.

정렬이 주는 장단점

컬럼 값을 기준으로 인덱스는 정렬되어 있습니다. 이에 따른 장단점을 알아봅시다.

장점단점
이분탐색 같이 정렬을 활용한 탐색을 활용해 검색 성능 끌올 가능값 저장, (삭제, 수정)마다 정렬 상태를 유지하도록 처리해야 함
이미 정렬되어 있기 때문에 group by 나 집약함수에 필요한 정렬 작업 생략

장단점에서도 알아볼 수 있듯이 인덱스는 검색을 위해 나머지를 희생하는 기능입니다.

인덱스의 양날의 검도 여기에서 시작됩니다.

진짜 도움이 될까?

눈으로 보지 않으면 이해 안 됩니다. 그래서 코드를 가져왔습니다.

환경은 다음과 같습니다.

  • Spring Boot / Java / MySQL / JPA / Lombok
  • 십 만 개 레코드 추가

인덱스를 사용하지 않는 코드입니다.

@Getter
@NoArgsConstructor(access = PROTECTED)
@Entity(name = "items")
public class Item {

    @Id
    @GeneratedValue(strategy = GenerationType.IDENTITY)
    private Long id;

    @Column(name = "name", nullable = false)
    private String name;

    @Column(name = "price", nullable = false)
    private long price;

    @Column(name = "quantity", nullable = false)
    private int quantity;

    public Item(String name, long price, int quantity) {
        this.name = name;
        this.price = price;
        this.quantity = quantity;
    }
}

public interface ItemRepository extends JpaRepository<Item, Long> {

    List<Item> findByNameAndPriceGreaterThan(String name, long price);

}

인덱스를 사용하는 코드입니다.

@Getter
@NoArgsConstructor(access = PROTECTED)
@Entity
@Table(
		name = "items_index", 
		indexes = @Index(name = "ix_name_price", columnList = "name, price")) // 인덱스
public class IndexItem {

    @Id
    @GeneratedValue(strategy = GenerationType.IDENTITY)
    private Long id;

    @Column(name = "name", nullable = false)
    private String name;

    @Column(name = "price", nullable = false)
    private long price;

    @Column(name = "quantity", nullable = false)
    private int quantity;

    public IndexItem(String name, long price, int quantity) {
        this.name = name;
        this.price = price;
        this.quantity = quantity;
    }
}

public interface IndexItemRepository extends JpaRepository<IndexItem, Long> {

		// 인덱스 비교 효과를 크게 만들기 위해 커버링 인덱스로 구현
    List<ItemInfo> findByNameAndPriceGreaterThan(String name, long price);

}

조회 성능은 당연히 인덱스를 사용한 쪽이 좋습니다.

`인덱스 사용 안 한 쿼리` 결과 `인덱스 사용한 쿼리` 결과
인덱스사용X 소모 시간 = `136 ms` 인덱스사용O 소모 시간 = `74 ms`

이제 인덱스가 실효성이 있다는 것을 확인했으니, 자세히 톺아보러 갑시다.

인덱스 알고리즘

그 유명한 B-tree 알고리즘입니다.

결론부터 말하자면, MySQL을 포함한 상용 DBMS에서는 범용성을 이유로 B+tree 인덱스 알고리즘을 주로 사용합니다.

B-tree 알고리즘, B+tree 알고리즘

DB, 파일 시스템에서 주로 사용하는 두 알고리즘은 이름에서 알 수 있듯이 그 기조가 같습니다. 차이점은 말미에 다루겠습니다.

구조

루트, 브랜치 노드에는 인덱스에 저장된 컬럼 값과 자식 노드의 페이지 주소가 저장됩니다.

리프 노드에는 데이터 파일에 저장된 레코드 주소를 갖습니다.

데이터 파일에 랜덤 I/O 하는 것은 MyISAM 엔진일 때의 그림입니다.

InnoDB에서는 B-tree 와 같은 형태의 트리를 통해 실제 레코드에 접근하게 됩니다. 그림에서는 생략했습니다.

균형

Binary 아닙니다. Balanced 트리입니다.

이름대로 균형이 잘 잡혀 있는 트리입니다. 편향트리가 될 가능성이 있는 이진트리와는 다르게 항상 자식노드가 균형있게 존재합니다. 루트 노드와 리프 노드의 거리 차이를 일정하게 유지한다는 의미입니다.

B-tree 알고리즘의 강점은 이 균형에서 나옵니다. 데이터량이 증가하더라도 루트와 리프 사이 거리가 일정하기 때문에 검색 성능이 급격히 악화되지 않습니다. 이를 ‘범용성 있다’고 표현하며, MySQL 을 포함한 대부분의 DBMS 인덱스 알고리즘으로 채택한 이유입니다.

정렬

B-tree 는 항상 정렬된 상태를 유지합니다.

정렬로 인해 아래의 장점을 갖게 됩니다.

  1. 이분탐색으로 검색 효율 향상 가능
  2. GROUP BY 절이나 집약함수 사용 시 정렬 생략 가능
  3. 등호, 부등호 모두 검색 조건에 활용 가능

동작

  • 인덱스 키 삽입
    • 키 값 이용해 정렬 상태를 유지 가능한 위치 찾아 인덱스 삽입
  • 인덱스 키 삭제
    • 해당 키 값 저장된 리프 노드 찾아 삭제 마크
  • 인덱스 키 변경
    • 인덱스 키 값 따라 저장 위치 결정
    • 따라서 먼저 키 값을 삭제 + 새로운 키 값 추가로 동작
  • 인덱스 키 검색
    • select 뿐만 아니라 update, delete 에서도 검색 쓰임
    • 함수/연산자/전방일치/완전일치 아닌 검색에는 인덱스 키 검색 불가

B-tree 와 B+tree 차이점

사실 상용 DBMS에서는 B-tree 보다 B+tree 알고리즘을 채택해서 사용하고 있습니다.

검색 효율이 더 좋기 때문입니다.

B-treeB+tree
내부 구조내부/리프 노드 모두 데이터 저장내부 노드는 키 값만 저장
리프 노드에는 실제 데이터 저장
탐색 경로탐색 경로에 내부/리프 노드 모두 포함
탐색에 더 많은 I/O 유발탐색 경로에 내부 노드만 포함
키 중복중복 키 허용중복 키 불허
범위 탐색범위 검색 복잡범위 검색 용이

BitMap 알고리즘

  • 데이터를 비트 플래그로 변환해서 저장하는 인덱스
  • 카디널리티(필드값의 분포도) 낮은 필드에서 효율적
  • 큰 갱신 오버헤드

Hash 알고리즘

  • 키를 해시 분산
  • 등가 검색에서 높은 효율
  • 일부/범위 검색 불가
  • 메모리 기반 데이터베이스에서 사용

인덱스 동작 방식

우리가 사용하는 MySQL에서는 B+tree 기반 인덱스 알고리즘을 사용합니다.

이제부터 B+tree 기반 인덱스를 활용해서 실제 데이터를 얻는 여러 방법에 대해서 알아봅시다.

인덱스 레인지 스캔

인덱스를 활용한 검색 중 가장 빠른 방법입니다.

검색해야 할 인덱스 범위가 정해졌을 때 옵티마이저가 인덱스 레인지 스캔으로 실행계획을 세워 실행합니다.

정렬이 되어 있기 때문에 범위 탐색이 가능한 것입니다.

아래 파란 선을 보시면 알 수 있듯이 리프 노드(페이지)의 끝까지 스캔했다면, 노드 간 링크를 이용해 다음 리프 노드를 찾아서 스캔을 이어갑니다. 리프 노드 사이의 노란선이 링크입니다.

동작

  1. 인덱스 탐색 : 인덱스에서 조건을 만족하는 값이 저장된 위치 찾기
  2. 인덱스 스캔 : 탐색된 위치부터 필요한 만큼 인덱스를 차례대로 쭉 읽기
  3. 레코드 읽기 : 인덱스 키와 레코드 주소 이용해 레코드 저장된 페이지 가져와 레코드 읽어오기
SELECT * FROM things
WHERE name BETWEEN 'Apple' AND 'Tomato';

인덱스 풀 스캔

인덱스의 처음부터 끝까지 모두 읽는 방식입니다. 당연히 위의 레인지 스캔 방식보다는 성능이 안 좋을 수밖에 없습니다.

인덱스를 (a, b, c) 컬럼으로 구성했지만, 조건절에서 a 없이 b, c로만 검색한 경우에 인덱스 풀 스캔하게 됩니다.

추가로, 쿼리가 인덱스에 명시된 컬럼만으로 조건을 처리할 수 있는 커버링 인덱스여야 합니다. 만약 인덱스 컬럼이 아닌 컬럼이 하나라도 쿼리에 포함되면 옵티마이저는 인덱스 풀 스캔으로 실행계획을 세우지 않습니다.

동작

-- price, name 컬럼으로 인덱스 생성
ALTER TABLE things ADD INDEX ix_price_name (price, name);

-- name 컬럼으로만 쿼리
SELECT * FROM things WHERE name > 'Gold';

아래 모든 노드에 price 컬럼도 있어야 합니다. 편의상 제거했습니다.

where 절에 사용하지 않는 선행 인덱스 컬럼이 있는 경우 인덱스 풀 스캔을 한다는 동작에 집중해주세요.

루스 인덱스 스캔

중간에 필요하지 않은 인덱스 키 값은 skip 하고 다음으로 넘어가는 형태의 인덱스 스캔입니다.

skip 을 제외하면 인덱스 레인지 스캔과 비슷합니다.

GROUP BY 또는 집합함수 중 MAX, MIN 함수를 최적화할 때 사용합니다.

동작

ALTER TABLE members
	ADD INDEX ix_gender_age (성별, 나이);

SELECT 성별, MIN(나이) FROM members
GROUP BY 성별;
  • 중간에 필요 없는 데이터는 skip

인덱스 스킵 스캔

인덱스를 제대로 활용하려면 컬럼의 순서가 중요합니다.

만약에 인덱스를 활용하는 where 절에서 인덱스 컬럼 순서 지키지 않는 조건 검색을 하면, 인덱스를 활용하지 않고 테이블 풀 스캔을 하게 됩니다. 선행 컬럼의 정렬이 후행 컬럼의 정렬에 영향을 주기 때문입니다.

인덱스 스킵 스캔은 MySQL 8.0 버전에 추가된 내용이며, 사용에 조건이 붙습니다.

  1. where 조건절에 조건이 없는 인덱스의 선행 컬럼의 유니크한 값의 개수가 적어야 함
  2. 커버링 인덱스여야 함(쿼리가 인덱스에 존재하는 컬럼만으로 처리 가능해야 함)

동작

ALTER TABLE members
	ADD INDEX ix_gender_age (성별, 나이);

-- 인덱스 사용 불가 쿼리
SELECT * FROM members
WHERE 나이 >= 30;

-- 인덱스 사용 가능 쿼리
-- 인덱스 스킵 스캔으로 동작하는 경우
SELECT * FROM
WHERE 성별 = 'M' AND 나이 >= 30;
SELECT * FROM
WHERE 성별 = 'F' AND 나이 >= 30;
  1. 조건절에 사용되지 않은 선행 인덱스 컬럼에서 유니크한 값을 모두 조회
  2. 주어진 쿼리에 해당 컬럼의 조건 추가해서 쿼리를 재실행

어떤 컬럼을 인덱스로 써야할까?

인덱스로 사용할 컬럼은 신중하게 결정해야 합니다.

자주 사용되는 쿼리가 무엇인지, 서비스에서 어떻게 사용되는지 복합적으로 판단해야 합니다.

컬럼 순서도 중요합니다. 위의 인덱스 풀 스캔처럼 컬럼 순서가 바뀌었을 뿐인데도 스캔하는 방식에서 차이가 납니다.

이번 절에서는 인덱스의 컬럼으로 적절한 특성과 적절하지 않는 상황에 대해서 알아보겠습니다.

인덱스에 적합한 컬럼 판단 척도

카디널리티

값의 균형 (분포)를 말하는 지표입니다.

카디널리티가 높을수록 인덱스로 쓰지 적합한 컬럼입니다.

유니크 키로 설정한 컬럼이 주로 카디널리티가 높습니다.

  • 높은 카디널리티
    • 모든 레코드에 다른 값이 들어있는 필드
    • 유일 키 필드
  • 낮은 카디널리티
    • 모든 레코드에 같은 값이 들어있는 필드

인덱스 키 값 크기

인덱스로 쓰이는 컬럼의 크기가 작아야 한다는 의미입니다.

인덱스는 페이지 단위로 관리됩니다. 이전까지 그림에서 볼 수 있듯이, 인덱스 각 노드는 페이지로 구성되어 있습니다. 따라서 각 노드를 탐색할 때마다 페이지 입출력이 발생합니다.

인덱스를 구성하는 키 값의 크기가 커졌을때 (인덱스 컬럼 크기가 커졌을때) 어떤 결과로 이어지는지 알아보겠습니다.

  1. 인덱스 키 값 크기 커짐
  2. 가질 수 있는 자식 노드 개수 줄어듬
    → 정해진 페이지 크기에서 인덱스 키 값 크기가 커지므로, 자식 노드 포인터 공간은 줄어듭니다.
  3. 자식 노드 개수가 늘어나면서 트리 깊이 깊어짐
  4. I/O 작업 늘어남
  5. 느려짐 + 메모리에 캐시 가능한 레코드 수 줄어듬

인덱스 키 값이 커지면 큰일이라도 일어날 것 처럼 호들갑을 떨었지만, 사실 크게 상관은 없습니다.

B-tree 구조 자체가 수용력이 강한 자료구조이기 때문에 대용량의 데이터를 처리한다고 해도 B-tree의 최대 깊이는 5를 넘지 않는다고 합니다.

인덱스에 사용할 컬럼을 딱 필요한 만큼만 추출하여 생성한다 라는 마음으로 사용해주면 됩니다.

선택률 (기수성)

테이블 전체에서 인덱스를 통해 몇 개의 레코드가 선택되는지에 대한 지표입니다.

유니크한 값이 많을수록 선택률이 높아질 가능성이 높습니다.

선택률이 적을수록 검색 대상이 줄어들어 인덱스 성능이 올라가게 됩니다.

선택률은 카디널리티와 다르게, 쿼리나 데이터에 따라 변동이 심하기 때문에 SQL 문이 어떻게 활용되는지를 잘 파악해야 합니다.

예를 들어볼까요?

💡 아래 테이블의 만 개의 레코드에서 남자 레코드는 9,900 개, 여자 레코드는 100 개라고 가정해봅시다. 
CREATE TABLE members (
	gender VARCHAR(1),
  name VARCHAR(50)
);

CREATE INDEX ix_gender ON members (gender);

-- 남자 선택률 = 9,900 / 10,000 = 0.99
SELECT * FROM members WHERE gender = 'M';

-- 여자 선택률 = 100 / 10,000 = 0.01
SELECT * FROM members WHERE gender = 'F';

여자 레코드를 조회하는 경우에 선택률은 0.01로 매우 좋습니다. 인덱스 컬럼으로 아주 적절한 컬럼입니다.

하지만 남자 레코드에서는 그렇지 않습니다. 따라서, gender 컬럼은 인덱스 컬럼으로 부적절한 컬럼이 됩니다.

다만, 선택률이 높더라도 인덱스로 활용되는 경우가 있습니다. 집약함수나 GROUP BY 에서 필요한 정렬을 생략하고 싶을 때입니다.

읽어야 하는 레코드 건수

읽어야 하는 레코드 건수는 선택률과 조금 다른 개념입니다.

선택률은 쿼리를 통해 조회되는 최종 데이터와 모든 데이터의 관계입니다. 필터링 하기 전에 읽어야 하는 레코드와는 다른 개념이란걸 아시겠죠?

일반적으로 옵티마이저는 쿼리 비용을 이렇게 판단한다고 합니다.

인덱스 활용 조회 1건 비용 = 레코드 직접 조회 1건 비용 * 4(or 5)

인덱스 활용한 조회가 비용이 큰 건 어쩔 수 없습니다.

B-tree 구조에서 보았듯이 루트 노드에서 리프 노드로 탐색이 필요하며, 리프 노드에서 찾은 레코드 주소를 이용해 데이터 파일에 랜덤 I/O 를 수행해야 하기 때문입니다.

때문에 인덱스로 읽어야 할 레코드 건수가 전체 테이블의 20~25% 미만일 때 인덱스를 활용해야 옵티마이저가 인덱스를 활용한 실행계획을 세워줄 겁니다.

클러스터링 팩터

353
247
132
111
233
233

저장소에 같은 값이 어느정도 물리적으로 뭉쳐 존재하는지 여부입니다. 공간지역성과 같은 의미입니다.

  • 높은 클러스터링 팩터
    • 같은 값이 분산되어 퍼져 있음
  • 낮은 클러스터링 팩터
    • 같은 값이 일정 구역에 몰려 있음
    • 인덱스 스캔에 유리
    • 접근할 데이터양이 적어지기 때문

인덱스 사용 못하는 경우

옵티마이저는 아래 상황에 대해서 인덱스가 있음에도 인덱스를 활용할 수 없습니다.

이를 지향하는 것이 인덱스 활용의 시작입니다.

중간 일치, 후방 일치의 LIKE 연산자

  • LIKE 연산자 사용하는 경우
    • 전방 일치 (’대공원%’) 에는 인덱스 사용 가능
    • 중간 일치 (’%대공원%’), 후방 일치 (’%대공원’) 에는 인덱스 사용 불가

색인(인덱스) 필드로 연산하는 경우

select * from some_table
where col_1 * 1.1 > 100; -- 인덱스 필드에 연산을 사용해서 인덱스 검색 불가

where col_1 > 100/1.1;   -- 인덱스 필드에 연산 사용 안 했으므로 인덱스 검색 가능

색인 필드에 함수를 사용하는 경우

인덱스 내부에 존재하는 값은 col_1 이지 length(col_1) 이 아니기 때문입니다.

select * from some_table
where length(col_1) = 10;

IS NULL 을 사용하는 경우

인덱스 필드 데이터는 null 이 없기 때문입니다. 대부분의 DBMS에 해당하는 내용입니다.

MySQL 에서는 인덱스에 Null 을 허용하기 때문에 해당되지 않는 내용입니다.

select * from some_table
where col_1 is null;

부정형을 사용하는 경우

  • <>, !=, NOT IN, NOT BETWEEN, IS NOT NULL
select * from some_table
where col_1 <> 100;

Not-deterministic 속성 스토어드 함수가 비교조건에 사용된 경우

select * from some_table
where col_1 = deterministic_function()

데이터 타입이 서로 다른 비교 (인덱스 컬럼의 타입을 변환해야 비교 가능한 경우)

select * from some_table
where char_col = 10

문자열 데이터 타입의 콜레이션이 다른 경우

select * from some_table
where utf8_bin_char_col = euckr_bin_char_col

인덱스 사용해도 별 의미 없는 경우

마찬가지로 인덱스는 존재하지만, 인덱스를 활용하지 못하는 경우입니다.

위의 사용할 수 없는 경우와는 다르게, 인덱스를 사용할 수는 있지만 옵티마이저가 인덱스를 활용하는 것이 오히려 손해라고 판단하여 테이블 풀 스캔으로 실행계획을 세우는 케이스입니다.

압축 조건이 존재하지 않는 경우

  • where 절 없는 쿼리
  • 인덱스로 사용할 필드 자체가 없는 것

레코드 제대로 압축 못하는 경우

  • where 절 존재하지만, 딱히 압축되지 않는 경우
  • 선택률이 높은 경우
  • _flg 혹은 _status 같은 플래그 컬럼은 중복된 값이 있을 가능성이 높으므로 적절하지 않음
  • 입력 매개변수에 따라 선택률이 변동하는 경우 인덱스가 실제로 효용이 있는지 판단하기 어려움
    • start_date, end_date 사이 레코드 검색 SQL
    • shop_id 기준 레코드 검색 SQL

결론

한 번의 선택으로 레코드가 조금만 선택되도록 하는 필드가 좋은 인덱스 필드 후보입니다.

작업 범위 결정하는 조건은 많을수록 쿼리 처리 성능 좋아짐, 체크 조건은 많다고 해도 별 의미 없음

좋은 인덱스를 만드는 컬럼 기준입니다.

1. 높은 카디널리티 필드
2. 낮은 선택률 필드 (5~10% 정도)
3. 낮은 클러스터링 팩터
4. 읽어야 하는 레코드 건수가 적은 경우
5. 인덱스 키 값이 작아지도록 인덱스 컬럼 설정
    - 컬럼 순서도 중요!

클러스터링 인덱스 = 클러스터링 테이블

MySQL 5.7 이상 버전을 사용하신다면, 기본적으로 InnoDB를 사용하게 됩니다.

InnoDB에서는 항상 클러스터링 인덱스로 테이블을 저장합니다. 따라서 InnoDB의 테이블은 사실상 하나의 인덱스와 다름 없습니다.

클러스터링 인덱스에서는 프라이머리 키에 대해서만 인덱스를 적용합니다. 테이블 전체가 프라이머리 키로 정렬을 수행한 인덱스인거죠. 우리가 테이블을 만들때 PK 를 지정해주는 이유이기도 합니다.

프라이머리 키 값을 기준으로 정렬하기 때문에 비슷한 레코드끼리 묶일 가능성이 높아지고, 이를 이용해서 키 기반 검색을 엄청 좋게 개선할 수 있게 되었습니다.

하지만 PK 에 의해 레코드 저장 위치가 결정되므로 레코드 추가나, PK 값 변경에는 느린 단점이 있습니다.

특징으로 B-tree 와 다르게 리프 노드에 레코드의 모든 컬럼이 함께 저장되어 있습니다. 앞서 InnoDB에서 B-tree와 유사한 트리에서 실제 레코드 조회가 가능하다고 했던 내용입니다.

Primary Key & Secondary Key

MySQL 에서 인덱스는 키(Key) 와 동의어라고 합니다.

  • Primary Key
    • 레코드를 대표하는 컬럼 값으로 만들어진 인덱스
    • 식별자
    • null 값 허용 X, 중복 허용 X
  • Secondary Key
    • 프라이머리 키 제외한 인덱스

PK 를 반드시 설정해야 하는 이유

다음 우선순위에 따라 클러스터링 인덱스의 PK 가 결정됩니다.

  1. 기본적으로 PK 를 클러스터링 키로 선택
  2. NOT NULL + UNIQUE 인덱스 중 첫 번째 인덱스를 클러스터링 키로 선택
  3. 자동으로 유니크한 값을 가지도록 증가되는 컬럼을 내부적으로 추가한 후, 클러스터링 키로 선택

PK 없이 테이블을 생성하면 좋을 게 없습니다. 자동으로 추가되는 키는 의미도 없고, 활용할 수도 없기 때문입니다.

장단점

장점단점
검색 성능 좋음 (범위 검색에서 폼 미친 성능)클러스터링 키 값 크기가 커지면 인덱스 전체 크기도 함께 커짐
커버링 인덱스일 가능성 높음PK에 의해 레코드 저장 위치가 결정되므로 삽입 성능 낮음
PK 변경 시 삭제 + 삽입 과정이 필요

주의점

  • 프라이머리 키를 작게 설정해야한다
  • 프라이머리 키는 반드시 명시할 것
  • auto-increment 컬럼을 인조 식별자로 사용할 경우
    • 다른 인덱스도 필요하고, 프라이머리 키 크기도 길다면 고려할 만 함
    • 조회보다는 insert 위주의 테이블들은 인조식별자를 사용하는 것이 좋음
    • 하지만 비즈니스 요구사항이 언제든지 변할 수 있으므로, 조회 성능이 절실한 것이 아니라면 인조 식별자를 사용하도록 권장

결론

인덱스를 잘 쓰는 건 서비스에 대한 이해가 가장 필요한 것 같습니다.

조회 성능이 부족한 기능이 무엇인지, 어느정도의 성능이 필요한지를 판단하고

인덱스를 이용해 개선 가능한지 여부를 파악해야 합니다.

옵티마이저가 의도한 대로 실행계획을 세워줄 수 있도록 인덱스에 대해서 좀 더 알아보게 되었으면 좋겠습니다.

profile
텐저린티
개발하고 말테야
이전 포스트

좋은테스트, FIRST 속성

다음 포스트

MVCC

0개의 댓글