[데이터베이스] 인덱스(INDEX)

 

 

데이터베이스 인덱스(INDEX)

---

안녕하세요? 장장스입니다.

오늘은 데이터베이스 인덱스에 대해 알아보겠습니다!

 

 

인덱스(INDEX) ?

---

 우리는 교과서와 같은 책의 맨 뒤를 보면 색인(INDEX)을 볼 수가 있었습니다. ㄱ-ㅎ 순서로 잘 정리된 인덱스 목록을 통해 우리가 찾고자 하는 내용의 페이지를 빠르게 찾을 수가 있었습니다.

 

데이터베이스의 인덱스는 무엇일까요?

 데이터베이스의 인덱스는 테이블의 검색 속도를 향상시키기 위한 동작의 속도를 높여주는 자료 구조를 일컫습니다.

 

 

장점과 단점

---

인덱스를 사용할 때의 보편적인 장단점을 살펴보면 아래와 같습니다. 그러나 예외적인 상황도 많이 있으니 인덱스 사용에 신중해야 합니다!

 

•  장점
  • 검색(SELECT) 속도가 향상 될 수 있다.(단, 항상 그런 것은 아니다.)
  • 그 결과 검색쿼리의 부하가 줄어들어 시스템의 전체적인 성능이 향상된다.
• 단점
  • 인덱스는 하나의 오브젝트로 약 10%의 추가적인 데이터베이스 공간을 차지한다.
  • 처음 인덱스를 생성하는데 시간이 많이 소요될 수 있다.
  • INSERT, UPDATE, DELETE 쿼리가 빈번하게 발생하는 경우 오히려 성능이 저하 될 수 있다.

 

 

클러스터드 인덱스(Clustered Index)

---

 클러스터드 인덱스는 데이터를 물리적으로 재배열 하는 인덱스를 말합니다. 데이터를 인덱스 기준으로 저장합니다. 예를 들어 맛있는 치킨을 맛있는 순서대로 저장한다고 해보겠습니다.

참, 설명을 위한예시 데이터 입니다. 치킨은 다 맛있어요 :) 

 

 그런데 새로운 맛있는 치킨이 등장하였고 순서를 재조정하게 되었습니다.

 

 

위의 사진을 보면 새롭게 등장한 60계 치킨이 4등이 되었고,  4~7등을 차지한 치킨들이 밀려서 인덱스가 하나씩 증가하게 되는 것을 볼 수가 있습니다!

 

클러스터드 인덱스의 특징입니다!

 

• 행을 물리적으로 재배열 한다.
• 인덱스 페이지 용량이 작다. (데이터가 이미 정렬되어 있어서 인덱스 테이블이 필요 X)
• 데이터베이스의 30% 이내 사용을 권장한다.
• 데이터를 정렬하여 사용하기 때문에 범위(range) 검색에 유리하다.

 

넌클러스터드 인덱스(Non-Clustered Index)

---

넌클러스터드 인덱스는 별도의 공간에 테이블을 생성하는 인덱스입니다. 

 

넌클러스터드 인덱스의 특징입니다!

• 인덱스를 저장할 별도의 테이블(오브젝트)이 필요하다.
• 별도의 인덱스를 저장하기 때문에 약10% 정도의 저장공간이 추가적으로 필요하다.
• 검색(SELECT) 속도는 느리지만 INSERT, UPDATE, DELETE가 더 빠르다. (인덱스는 정렬되서 저장된다)
• 인덱스 테이블을 너무 많이 생성할 경우 시스템 성능을 떨어뜨리는 결과를 가져온다.
• 최대 249개의 인덱스 테이블 생성이 가능하다. 

 

어떤 컬럼을 인덱스를 사용하면 좋을까?

---

1. WHERE절에 자주 등장하는 컬럼

  조건 검색시 WHERE 절에 해당하는 칼럼을 찾는 작업이 이루어진다. 인덱스로 더 빠르게 찾을 수 있다!

 

2. ORDER BY절에 자주 등장하는 컬럼

  인덱스는 순서대로 정렬(sorting)되어 있기 때문에 ORDER BY 쿼리에 효과적이다!

 

3. SELECT절에 무리지어 등장하는 컬럼들

  인덱스는 여러 칼럼을 조합해서 결합 인덱스로 구성할 수 있다. 그럴 경우 SELECT 절에 묶음(?)으로 등장하는 컬럼들을 인덱스로 구성하면 검색 속도를 높일 수 있다!

 

결합 인덱스 구성시 주의!
성별과 같이 구분이 모호한 컬럼보다는 아이디 같은 분별력이 높은 컬럼이 앞에 오는게 효율적
WHERE 절의 equal에 많이 쓰이는 컬럼이 앞에 오는게 효율적

 

 

 

쿼리를 작성시 주의할 점

---

인덱스를 생성해놓고 쿼리를 잘못 작성해서 오히려 성능이 더 떨어지는 경우가 있다. 쿼리 작성시 다음과 같이 작성하지 않도록 주의하자

 

1. 인덱스 컬럼을 가공

Bad
WHERE SUBSTR(ORDER_NO, 1, 4) = '2021'

Good
WHERE ORDER_NO LIKE '2019%'

 

2. 인덱스 컬럼의 묵시적 형변환

Bad
WHERE REG_DATE = '20210712'

Good
WHERE REG_DATE = TO_DATE('20210712', 'YYYYMMDD')

 

3. 인덱스 컬럼 부정형 비교

Bad
WHERE MEM_TYPE != '10'

Good
WHERE MEM_TYPE IN ('20', 30)

 

4. LIKE 연산자 사용 시 %가 앞에 위치

Bad
WHERE ORDER_NO LIKE '%2021'

Good
WHERE ORDER_NO LIKE '2021%'

 

5. OR조건 사용

Good
UNION ALL로 대체

 

 

 

인덱스 손익분기점

---

 인덱스를 사용하면 무조건 좋을까? 그렇지 않다. 대량의 데이터를 다룰 때는 인덱스는 오히려 성능을 저하시킨다. 

 

 검색시 인덱스를 스캔 후 테이블 레코드를 랜덤액세스 하게 되는데, 대량의 데이터를 조회하게 되는 경우  랜덤엑세스가 부하를 일으킨다.

 

 데이터가 적다면 인덱스에 적혀있는 주소에 가서 데이터를 가져오면 되기 때문에 효율적으로 데이터를 찾을 수 있다. 하지만 대량의 데이터를 조회하게 되면 반복적인 랜덤엑세스가 발생하여 성능이 저하되게 된다. 이럴 경우 오히려 Full Scan이 더 효율이 높아지게 된다. 이러한 지점을 인덱스 손익분기점이라 한다.

 

 

 

위키피디아

 

보통 손익분기점은 가지고 있는 전체 데이터의 5~ 20프로의 데이터를 검색 할 때 가장 효율적이며 이를 넘어가면 풀스캔 하는게 더 빠르다.

 

(모르면 옵티마이저가 알아서 해준다 ㅎㅎ)

 

 

 

인덱스 스캔 방식

 

---

• index rage scan
• index full scan
• index skip scan
• index fast full scan

 

 

 

References

---

• https://www.youtube.com/watch?v=NkZ6r6z2pBg&t=19s
• https://ko.wikipedia.org/wiki/%EC%9D%B8%EB%8D%B1%EC%8A%A4_(%EB%8D%B0%EC%9D%B4%ED%84%B0%EB%B2%A0%EC%9D%B4%EC%8A%A4)
• https://www.youtube.com/watch?v=uO8tL0okg7Q

 

 

---

잘못된 코드나 내용이 있다면 댓글을 남겨주세요. 즉시 수정하도록 하겠습니다! :)