DB Index

Index란?

Index는 RDBS에서 조회할 때 생기는 full scan 문제를 해결하여 성능을 개선하기 위해 고안되었다.

select * from member where userName = '타미';

라는 문이 있을 때, table을 full scan한다.
멤버 row가 1000개라면? 10000개라면? 문제가 심각할 것이다.

배경지식

• 여러분이 만약 mySQL innoDB를 사용하고 있다면, 이미 indexing을 사용하고 있는 중이다.
• innoDB는 pk를 기준으로 indexing한다.
  • 만약 pk가 없다면 사용자에게는 내부적으로 생성한다. 이때, 생성되는 데이터의 크기가 biggint보다 크다고 하니 pk는 만들어주는 게 좋다!
  • clusted index로 만들어준다.

Index 사용하기

1. 기존 테이블에 인덱스 추가

   ALTER TABLE table_name ADD INDEX(keyword(20))

2. 테이블 생성과 동시에 인덱스 추가

   CREATE TABLE table_name (
     keyword varchar(20),
     INDEX(keyword(20))
   )
   ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
   

DB의 인덱스에서 쓰이는 대표적인 알고리즘은 이진트리 , 해시가 있다.
이진트리의 경우 데이터가 미리 정렬되어 있어야 하는데 테이블의 데이터를 언제나 정렬된 상태로 둘수는 없다.
테이블에 인덱스를 작성하면 테이블 데이터와 별개로 인덱스용 데이터가 생성되는데 이때 인덱스들을 이진트리 구조로 작성된다.

Index scan 방식

• index range scan : 어떤 시작점 부터 특정 범위를 스캔하는 방식
• index full scan
• index skip scan
• index fast full scan

어떤 컬럼을 Index로 지정할까?

1. where에서 자주 사용되는 컬럼
2. 카디널리티가 높은 (중복도가 낮은) 컬럼
   중복도 ⬇ 카디널리티 ⬆ : 주민등록번호
   중복도 ⬆ 카디널리티 ⬇ : 성별
   카디널러티
   
   출처: 갓졸두님 블로그
   중복도가 높은 (성별)로 indexing을 하면 원하는 값을 찾기 위해서 또 검색하고 성능이 낮아진다.
3. order by 빈도가 높은 컬럼
   index table은 index를 기준으로 정렬되어있기 때문이다.
4. 여러 개의 컬럼을 중복해서 사용할 수 있다.

• 이때 순서는 중복도가 낮은 컬럼이 앞에 있는 게 좋다.
• (출처) 결합인덱스에서 컬럼의 분별력(중복도)과 선두 위치 여부는 상관이 없습니다. 오히려 분별력이 낮은 컬럼이 선두에 위치하는 것이 index skip scan 측면에선 더 유리합니다.

단점

• 별도의 저장공간이 필요하다.
• insert/update/delete 성능이 안 좋아짐.
  • 데이터가 변경되면, index table을 다시 sorting하기 때문이다.
  • 🤔 index column을 update해도 sorting할까? 당근
• 손익분기점이 존재한다.
  • 분포도가 10%~15% 이상이면 full table scan이 유리하다
    분포도 = 1/컬럼 값의 종류
  • (🙋‍뇌피셜) indexing으로 원하는 데이터를 받아올 수 없다면 또 탐색해야 하고 결국 추가적으로 index table까지 탐색하면서 성능이 안 좋아지는 것 같다.
  • ㄴ요것은 index를 잘못 걸었을 때이고, 그 외에도 I/O 문제가 있다.
    Full Scan을 할 때는 Multiblock I/O로 I/O 횟수가 적은 반면, index를 할 때에는 Singl Block I/O로 데이터가 많아졌을 때 I/O 횟수가 증가하는 문제도 있다.
  • 데이터 양 자체가 너무 많으면 (억 단위) index table 관리 비용 때문에 오히려 full scan이 빠르다.

Clustered, Non Clustered

Clustered

• (예) 책에서 바로 페이지를 아는 것
• 데이터가 물리적으로 정렬되어 저장된다.
• 그래서 논 클러스터드 인덱스보다 빠르다.
• 범위 조회할 때 좋다.

Non Clustered

• (예) 책에서 목차를 보는 것
• 데이터가 물리적으로 정렬되지 않았다.
  • Index 페이지은 정렬된다.
  • 삽입,수정,삭제할 때는 Non-Clustered가 효율적이다.
• 범위 조회(Range Query)를 할 경우 거의 인덱스의 도움을 받을 수 없다.
  (인덱스를 안탄다. Table Scan을 하게 된다.) 왜지?!!

기타

모든 데이터베이스는 해당 쿼리 실행시 인덱스를 사용하는지 안하는지 확인할 수 있는 예약어가 있다. (ex : EXPLAIN)

where 조회 순서와 index 순서를 맞출 필요는 없다.

• 내부적으로 알아서 index 순서에 맞게 재배열해준다.
• 허나 유의미한 성능 이슈 노노

인덱스로 사용된 컬럼값 그대로 사용해야만 인덱스가 사용된다.
where salary * 10 > 150000; (X)
where salary > 150000 / 10; (O)

정확한 타입을 사용

• 컬럼이 문자열인데 숫자로 조회하면 타입이 달라 인덱스가 사용되지 않습니다.