Index

안돌님의 Index 영상을 정리한 글입니다!

www.youtube.com/watch?v=NkZ6r6z2pBg&list=PLgXGHBqgT2TvpJ_p9L_yZKPifgdBOzdVH&index=77&t=167s

 

우아한 형제들의 이동욱 개발자님의 Index 정리글도 함께 정리했습니다!!

jojoldu.tistory.com/243

 

---

인덱스란?

(검색을 위해) 임의의 규칙대로 부여된, 임의의 대상을 가리키는 무언가.

ex) 2학년 3반 21번, 920000-1xxxxxx, 주소

데이터베이스에서 인덱스가 왜 필요할까?

 

- 데이터베이스는 내가 원하는 데이터를 어떻게 찾아오는걸까?

- 왜 데이터가 많아질수록 점점 느려질까?

- 왜 조인만 수행하면 느릴까?

- 왜 쿼리가 느릴까?

 

---

그래서 데이터베이스의 인덱스는?

 

지정한 컬럼들을 기준으로 메모리 영역에 일종의 목차를 생성하는 것
insert, update, delete 의 성능을 희생하고 대신 select (Query)의 성능을 향상시킵니다.
-> update, delete 하는 행위가 느린것이지 ,update,delete를 하기 위해 해당 데이터를 조회하는 것은 인덱스가 있으면 빠르게 조회 가능합니다. 인덱스가 없는 컬럼을 조건으로 update, delete를 하게되면 굉장히 느리기 때문에
많은 양의 데이터를 삭제 해야하는 상황에선 인덱스로 지정된 컬럼을 기준으로 진행하는 것을 추천드립니다.

---

Clustered vs Non-Clustered

용어 정의

 

Cluster : 군집

 

Clustered : 군집화

 

Clustered Index : 군집화된 인덱스

 

뭐가 군집 되있는가? 인덱스와 데이터가 군집

 

Clustered Index 

 

Primary Key와 유사합니다.(거의 똑같다고 봐도 될정도)

인덱스의 해당 키 값을 기준으로 테이블이나 뷰의 데이터 행을 정렬하고 저장합니다.

(테이블당 Clustered Index는 하나만 존재가능)

 

범위 검색에 있어서 뛰어난 성능을 보여줍니다.

 

예를들어 영어사전처럼 내용 자체가 순서대로 정렬이 되어 있어서, 

인덱스 자체가 데이터 내용을 가리키는 경우

 

1 -> 박씨, park92
2 -> 김씨, kim94
3 -> 이씨, lee22
4 -> 홍씨, hong11
5 -> 남씨, nam72

왼쪽의 숫자가 인덱스의 키값 오른쪽이 데이터

 

삽입, 삭제 시 성능이 매우 떨어짐

업무시간에 클러스터 인덱스에 삽입, 삭제시 성능상의 문제가 발생할 수 있음

 

Non Clustered Index

 

• 인덱스가 데이터가 있는 주소값을 가리킵니다. 일반적인 책의 마지막 부분에 있는 "찾아보기"와 유사합니다.
• 한 테이블에 여러개의 인덱스를 가질 수 있습니다.
• INSERT시 반드시 추가 작업이 필요
  • 순서를 바꾸는 작업을 없지만 어떤 행이 삽입될때 그 행에 대한 인덱스를 생성해서 넣어줘야함 

• 추가 저장공간이 필요 (약 10%)
• 카디널리티(Cardinality) : 해당 컬럼의 중복도를 나타내는 수치, 인덱스의 성능을 예측할 수 있는 항목입니다.
  • 카디널리티가 높을수록 중복된 데이터가 적다는 뜻으로 인덱스로 지정하기 좋은 컬럼입니다.
  • ex) 주민등록번호는 카디널리티가 높음 / 성별(남자 or 여자)는 카디널리티가 매우 낮음

 

 

---

B-tree

B-tree 인덱스 구조(출처 : https://jojoldu.tistory.com/243)

B-tree 인덱스 구조

 

• 인덱스 탐색은 Root->Branch->Leaf->디스크 저장소 순으로 진행됩니다.
  • 예를들어 Branch(페이지 번호2)의 첫번째 행은 dept_no가 d001이면서 emp_no가 10017 ~ 10024까지인 Leaf의 부모로 있습니다.
  • 즉, dept_no=d001 and emp_no10018로 조회하면 페이지 번호4인 Leaf에서 데이터 파일의 주소를 불러와 반환하는 과정을 하게 됩니다.
• 인덱스의 두번째 컬럼은 첫 번째 컬럼에 의존해서 정렬되어 있습니다.
  • 즉, 두번째 컬럼의 정렬은 첫번째 컬럼이 똑같은 열에서만 의미가 있습니다.
  • 만약 3번째, 4번째 인덱스 컬럼도 존재한다면 두번째 컬럼과 마찬가지로 3번째 컬럼은 2번째 컬럼에 의존하고 4번째 컬럼은 3번째 컬럼에 의존하는 관계가 됩니다.
• 디스크에서 읽는것은 메모리에서 읽는것보다 성능이 떨어집니다.
  • 결국 인덱스의 성능을 향상시킨다는 것은 디스크 저장소에 얼마나 덜 접근하게 만드느냐 , 인덱스 Root에서 leaf까지 오고가는 횟수를 얼마나 줄이느냐에 달려있습니다.
• 인덱스의 개수는 3 ~ 4개가 적당합니다.
  • 너무 많은 인덱스는 새로운 Row가 등록 될때마다 인덱스를 추가 등록해야하고, 수정/삭제 시 마다 인덱스 수정이 필요하여 성능상 이슈가 있습니다.
  • 인덱스 역시 저장 공간을 차지합니다. 많은 인덱스는 그만큼 많은 공간을 차지할 수 있습니다.
  • 특히 많은 인덱스들로 인해 옵티마이저가 잘못된 인덱스를 선택할 확률이 높습니다.

옵티마이저란? SQL을 가장 빠르고 효율적으로 수행할 최적(최저비용)의 처리경로를 생성해주는 DBMS 내부의 핵심 엔진입니다.

 

---

인덱스 키 값의 크기

 

InnoDB(MySQL)은 디스크에 데이터를 저장하는 가장 기본 단위를 페이지라고 하며, 인덱스 역시 페이지 단위로 관리됩니다.

(Btree의 Root, Branch, Leaf 또한 페이지 단위 입니다. 위 B-tree 그림 참고)

 

만약 본인이 설정한 인덱스 키의 크기가 16Byte라고 하고,

자식노드(Branch,Leaf)의 주소가 담긴 크기가 12Byte 정도로 잡으면, 16*1024 / (16+12) = 585 로 인해 하나의 페이지는 585개가 저장될 수 있습니다.

 

여기서 인덱스 키 값의 크기가 32Byte로 커지면

16*1024 / (32+12) = 372로 되어 372개만 한 페이지에 저장할 수 있게됩니다.

 

조회 결과로 500개의 row를 읽을 때 인덱스 키의 크기가 16byte일때는 1개의 페이지에서 다 조회가 되지만,

32byte일때는 2개의 페이지를 읽어야 하므로 이는 성능 저하가 발생하게 됩니다.

 

인덱스의 키는 길면 길수록 성능상 이슈가 있습니다.

 

---

인덱스 컬럼을 정하는 기준

1개의 컬럼만 인덱스를 걸어야 한다면

-> 인덱스 컬럼은 카디널리티(Cardinality)가 가장 높은 것을 잡아야 합니다.

 

카디널리티(Cardinality)란 해당 컬럼의 중복된 수치를 나타냅니다.

예를 들어 성별, 학년 등은 카디널리티가 낮다고 얘기합니다. (성별은 남자 아니면 여자 2개의 그룹, 학년도 그룹이 적음)

반대로 주민등록번호, 계좌번호 등은 카디널리티가 높다고 말합니다. (주민등록번호는 개인마다 고유의 값을 가짐)

 

인덱스로 최대의 효율을 뽑아내려면, 해당 인덱스로 많은 부분을 걸러내야 하기 때문입니다. (마치 소거법)

만약 성별을 인덱스로 잡는다면, 남/녀 중 하나를 선택하기 때문에 인덱스를 통해 50%밖에 걸러내지 못합니다.

 

하지만 주민등록번호나 계좌번호 같은 경우에는 인덱스를 통해 데이터의 대부분을 걸러내기 때문에 빠르게 검색이 가능합니다.

 

---

여러 컬럼으로 인덱스 구성시

 

여러 컬럼으로 인덱스를 잡는다면 어떤 순서로 인덱스를 구성해야 할까요?

 

카디널리티가 낮은 -> 높은 순서 ?

카디널리티가 높은 -> 낮은 순서 ?

 

여러 컬럼으로 인덱스를 잡는다면 카디널리티가 높은 순에서 낮은 순으로 구성하는것이 더 성능이 뛰어납니다.

 

예를들어 컬럼이름과 각각의 카디널리티가 다음과 같이 있습니다.

 

컬럼이름	카디널리티
group_no	8000
from_date	2500
is_bonus	1

 

is_bonus -> from_date -> group_no 순으로 카디널리티가 낮은 순에서 높은 순으로(중복도가 높은 순에서 낮은 순으로)

group_no -> from_date -> is_bonus 순으로 카디널리티가 높은 순에서 낮은 순으로(중복도가 낮은 순에서 높은 순으로)

 

두 가지 순서로 쿼리를 실행하면 두번째. 즉, 카디널리티가 높은 순에서 낮은 순으로 구성하는 것이 성능이 더 좋습니다.(group_no -> from_date -> is_bonus의 순서)

 

---

여러 컬럼으로 인덱스 시 조건 누락

인덱스의 컬럼을 모두 사용해야만 인덱스가 사용되는 것은 아닙니다.

그렇다면 인덱스 컬럼중 어떤것들이 누락되어도 되고, 누락되면 안되는 것은 어떤 것일까요?

 

group_no -> from_date -> is_bonus의 순서로 인덱스가 있다고 가정합니다.

 

from_date를 제외한 group_no와 is_bonus를 사용해서 조회 쿼리를 실행해보면 정상적으로 인덱스를 사용합니다.

하지만 group_no을 제외하고 from_date와 is_bonus를 사용해서 조회 쿼리를 실행하면 전혀 인덱스를 사용하지 못합니다.

 

조회 쿼리 사용시 인덱스를 사용하려면 최소한 첫번째 인덱스 조건은 조회 조건에 포함되어야만 합니다.

첫번째 인덱스 컬럼이 조회쿼리에 없으면 인덱스를 타지 않는다는 점을 기억하시면 됩니다.

 

---

인덱스 조회시 주의 사항

between, like, <, > 등 범위 조건은 해당 컬럼의 인덱스를 타지만, 그 뒤 인덱스 컬럼들은 인덱스가 사용되지 않습니다.

• 즉, group_no, from_date, is_bonus로 쿼리가 잡혀있을 때
• where group_no=XX and from_date=YY and is_bonus=ZZ 등으로 잡으면 is_bonus는 인덱스가 사용되지 않습니다.
• 범위 조건으로 사용하면 안된다고 생각하면 됩니다.

 

반대로 =, in 은 다음 컬럼에도 인덱스를 사용합니다.

• in은 결국 =를 여러번 실행시킨 것입니다.
• 단, in은 인자값으로 상수가 포함되면 문제없지만, 서브쿼리를 넣게되면 성능상 이슈가 발생합니다.
• in의 인자로 서브쿼리가 들어가면 서브쿼리의 외부가 먼저 실행되고, in은 체크조건으로 실행되기 때문입니다.

 

AND 연산자는 각 조건들이 읽어와야할 ROW수를 줄이는 역할을 하지만 or연산자는 비교해야할 ROW가 더 늘어나기 때문에 풀 테이블 스캔이 발생할 확률이 높습니다.

• where 나 or 을 사용할 때는 주의가 필요합니다.

 

인덱스로 사용된 컬럼값을 그대로 사용해야만 인덱스가 사용됩니다.

• 인덱스는 가공된 데이터를 저장하고 있지 않습니다.
• where salary * 10 > 150000; 는 인덱스를 못타지만, where salary > 150000 / 10; 은 인덱스를 사용합니다.
• 컬럼이 문자열인데 숫자로 조회하면 타입이 달라 인덱스가 사용되지 않습니다. 정확한 타입을 사용해야만 합니다.

 

null 값의 경우 is null 조건으로 인덱스 레인지 스캔 가능

 

---

인덱스 컬럼 순서와 조회 컬럼 순서

 

최근엔 이전과 같이 꼭 인덱스 순서와 조회 순서를 지킬 필요는 없어졌습니다.

 

group_no -> from_date -> is_bonus 순으로 인덱스를 생성했을 때

조회 쿼리의 순서도 꼭 where group_no=XX and from_date=YY and is_bonus=ZZ 을 따라갈 필요가 없습니다.

 

where where is_bonus=XX and from_date=YY and group_no=ZZ 로 하더라도

옵티마이저가 조회 조건의 컬럼을 인덱스 컬럼 순서에 맞춰 재배열하는 과정이 추가해줍니다.

 

하지만 옵티마이저가 재배열하는 과정또한 성능에 아주 조금의 영향을 주기 때문에

이왕이면 순서도 맞춰주는것이 좋습니다

---

참고문헌

- jojoldu.tistory.com/243

- www.youtube.com/watch?v=NkZ6r6z2pBg&list=PLgXGHBqgT2TvpJ_p9L_yZKPifgdBOzdVH&index=77&t=167s

- docs.microsoft.com/ko-kr/sql/relational-databases/indexes/clustered-and-nonclustered-indexes-described?view=sql-server-ver15

-

-