S)DB_07_인덱스

도서관을 떠올려보자

도서관엔 수많은 책들이 잘 정리되어 있다. 나는 요시나가 요시마사가 쓴 "괴델 불완전성 정리"라는 책을 찾아 읽고 싶다.

 

1. 인덱스의 필요성

   도서관은 책으로 가득 차 있다. 만약 책의 위치를 나타내는 색인(인덱스)가 없다면, 우리는 원하는 책을 찾기 위해 모든 책을 차례로 살펴봐야 한다. "괴델 불완전성 정리"를 찾기도 전에 도서관은 문을 닫고 말 것이다.

 

2. 인덱스의 역할

   그럼 각 책의 위치를 알려주는 색인을 제공한다고 해보자. 이 색인은 각 제목의 위치를 정리해 놓은 것이다. 

우리가 원하는 책을 찾을 때, 이제 색인을 참조하여 어느 섹션에 가야 할지 알 수 있다. 

이렇게 색인을 통해 우리는 시간을 절약하고 효율적으로 책을 찾을 수 있다. 

 

3. 인덱스 설정 기준

   이렇게 편리한 색인을 만들 때는 몇 가지 요소를 고려해야 한다. 

- 항목들이 서로 중복되지 않아야 한다. 

- 각 항목이 책의 위치를 정확하게 가리켜야 한다.

- 가능한 많은 사람들이 이용할 수 있도록 다양한 검색 조건을 고려하여 설계되어야 한다. 

 

 

이제 인덱스가 무엇인지 대충은 알 것만 같으니 더 자세하게 알아봅시다~^^

 

 

---

 

 

인덱스는

"원하는 부분을 쉽고 빠르게 찾아서 전달해주는 역할 

& 정보 검색에 있어 성능을 최적화시켜줄 수 있는 유용한 도구"

 

 

데이터를 빠르게 조회하기 위해서 필요하다. 

데이터베이스에서 인덱스를 사용하면 조건에 맞는 튜플을 더 빠르게 찾을 수 있다. 
          (그리고 더 빠르게 정렬(ORDER BY)하거나 그룹핑(GROUP BY) 할 수 있다 !!)

데이터베이스가 대량의 데이터를 가지고 있을 때 특히 중요하다. 

 

ex) 특정 테이블에 인덱스가 없다면 조건에 해당하는 데이터를 찾기 위해 전체 데이터를 순차적으로 조회해야 한다. 

(이를 "Full Scan"이라고 한다.)

하지만 인덱스를 사용하면 Full Scan을 피하고 조건에 맞는 데이터를 더 빠르게 찾을 수 있다. 

 

 

 

 

 

인덱스는 주로 B-tree & B+tree 같은 자료구조를 사용해 구현된다. 

이들은 탐색 속도를 빠르게 하면서도 공간을 효율적으로 사용하게 할 수 있는 장점이 있다. 

또 인덱스를 사용하면 정렬이나 그룹화된 데이터를 더 빠르게 처리할 수도 있다. 

 

 

 

하지만 인덱스를 생성하는 것은 항상 좋은 것만은 아니다.

 

 

 

 

 

 

 

인덱스의 관리

인덱스는 항상 최신 데이터를 정렬된 상태로 유지해야 원하는 값을 빠르게 탐색할 수 있다.

따라서 인덱스가 적용된 컬럼에 INSERT, UPDATE, DELETE가 수행된다면

다음과 같은 연산을 추가적으로 해주어야 하며, 그에 따른 오버헤드가 발생한다. 

 

- INSERT 

     : 새로운 행이 추가될 때 인덱스도 함께 없데이트 되어야 한다. 

     이는 새로운 데이터를 인덱스에 추가하는 작업으로, 삽입 연산의 속도를 떨어뜨릴 수 있다.

 

- UPDATE 

     : 인덱스를 가진 열의 값을 변경할 때, 해당 값을 참조하는 모든 인덱스를 업데이트해야 한다. 

     이는 인덱스 효율성을 유지하기 위한 추가 작업으로, 업데이터 연산이 느려질 수 있다. 

 

- DELETE

     : 행이 삭제될 때 인덱스에서도 해당 값이 삭제되어야 한다. 

     이는 삭제된 데이터를 인덱스에서 제거하는 추가 작업으로, 삭제 연산의 속도를 저하시킬 수 있다. 

 

 

 

인덱스를 생성하면 데이터의 수정, 삽입, 삭제 작업이 느려질 수도 있고

불필요한 인덱스를 생성하면 오히려 성능을 저하시킬 수 있다. 

따라서 인덱스를 생성할 때는 카디널리티, 선택도, 활용도, 수정 빈도 등을 고려해야 한다. 

[카디널리티, 선택도, 활용도, 수정 빈도]

카디널리티(Cardinality) : 특정 특성(feature)이나 속성(attribute)이 가질 수 있는 고유한 값의 수 

ex) 이름은 각 사람마다 고유하고 사람마다 다른 이름을 가질 수 있다. (이 때 카디널리티는 그룹에 속한 사람의 수와 동일하다. )

"민수", "민지"와 같은 흔한 이름은 많은 사람들이 가지고 있다. 

이러한 이름의 카디널리티는 낮다.

하지만 "재원", "혜리", "강현" 과 같이 드물게 나오는 이름을 가진 사람의 수는 적다. 이러한 이름의 카디널리티는 높다. 

따라서 이름의 카디널리티가 낮으면 많은 사람들이 동일한 이름을 가지고 있으며, 높으면 각각의 이름이 보다 고유하다고 볼 수 있다. 

 

데이터베이스에서도 마찬가지로 카디널리티가 높은 열은 데이터의 분산이 크고, 각 값이 고유하며, 카디널리티가 낮은 열은 값의 중복이 많고 분산이 낮다고 볼 수 있다. 

따라서 인덱스를 설정할 때 카디널리티가 높을수록 해당 열의 값을 식별하는데 더욱 효과적이다. 

 

 

 

선택도(Selectivity) : 특정 조건을 만족하는 데이터의 비율

ex) 한 텃밭에서 직접 심어둔 옥수수를 찾는다고 상상해보자. 만약 전체 텃밭의 대부분을 옥수수가 차지한다면, 그 옥수수를 찾는 것은 쉽지 않을 것이다. 하지만 옥수수가 거의 심어져있지 않다면, 내가 심은 옥수수를 빠르게 찾을 수 있다.

 

선택도가 높을 수록 인덱스를 사용하여 원하는 데이터를 찾는 것이 더욱 효율적이다.

 

 

 

활용도(Utilization) : 얼마나 인덱스를 자주 사용하는가? 

ex) 어떤 책에서 특정 단어의 페이지 번호를 찾는 것을 떠올려보자. 만약 그 단어가 자주 등장하는 경우에는 책을 더 자주 펼쳐서 찾을 것이다. 반면에 그 단어가 드물게 나오는 경우에는 페이지 번호를 빨리 찾을 수 있다. 

 

인덱스의 활용도가 높을 수록 해당 인덱스를 사용하여 데이터를 찾는 것이 효율적이다.

 

 

 

수정 빈도(Modification Frequency) : 데이터의 수정이 얼마나 자주 발생했는가? 

ex) 미술 갤러리에서 전시품의 위치를 변경하는 작업을 떠올려보자. 만약 전시품의 위치가 자주 변경된다면, 갤러리 지도를 자주 수정해야 한다. 하지만 전시품 위치가 고정되어 거의 변경되지 않는다면, 지도를 자주 수정할 필요가 없을 것이다.

이와 유사하게,

 

데이터의 수정 빈도가 낮을수록 해당 열에 대한 인덱스를 유지하는 데 더욱 효율적이다.

인덱스를 사용하는 것 만큼이나 생성된 인덱스를 관리해주는 것도 중요하다. 그러므로 사용하지 않는 인덱스는 바로 제거를 해주어야 한다. 

 

 

 

 

 

 

 

 

인덱스의 장점

- 검색 속도 향상 

     : 데이터 검색 시 인덱스를 사용하면 일반적으로 빠르게 원하는 데이터를 찾을 수 있다. 

 

- 중복 데이터 방지, 특정 컬럼 유일성(Unique) 보장 가능

     : 무결성 유지에 도움 

 

- 정렬 및 그룹화에 효율적 

     : 인덱스를 사용하면 ORDER BY나 GRUOP BY와 같은 정렬 및 그룹화 작업이 빨라진다. 

 

[중복 데이터 방지, 유일성 보장]

- 중복 데이터 방지

: 인덱스를 특정 열에 설정하면 해당 열에 중복된 값을 가지는 행의 삽입을 방지할 수 있다. 

ex) 사용자의 이메일 주소를 저장하는 열에 유니크한 인덱스를 설정하면,

같은 이메일 주소를 가진 사용자를 중복해서 저장하는 것을 방지할 수 있다. 

 

이렇게 중복된 데이터를 방지하면 데이터의 일관성이 유지되고, 데이터베이스의 정확성이 향상된다. 

 

 

 

 

- 유일성 보장

: 특정 열에 유니크한 인덱스를 설정하면 해당 열의 각 값이 유일하다는 것을 보장할 수 있다. 

ex) 사용자의 주민등록번호나 고유한 식별자를 저장하는 열에 유니크한 인덱스를 설정하면 각 사용자의 정보가 중복되지 않고 유일하게 식별된다.

 

이렇게 유일성을 보장하면 데이터베이스의 신뢰성이 확보되며,

잘못된 데이터가 삽입되는 것을 방지할 수 있다. 

 

 

 

 

인덱스의 단점 

- 추가 저장 공간 필요

     : 인덱스를 생성하면 추가적인 저장 공간이 필요하며, 테이블에 대한 변경 작업이 느려질 수 있다. 

 

- 데이터 변경 시 성능 저하 

     : 인덱스를 관리하기 위해 데이터를 추가, 수정, 삭제할 때 추가 작업이 필요하며, 이로 인해 데이터 변경 작업의 성능이 저하될 수 있다.

 

-  병행성 감소

     : 한 페이지를 동시에 수정할 수 있는 병행성이 줄어든다. 

 

- 인덱스 생성 시간이 오래 걸릴 수 있다.

     

 

만약 데이터 조작이 빈번한 속성에 인덱스를 걸게 되면 

인덱스의 크기가 비대해져서 성능이 오히려 저하되는 역효과가 발생할 수 있다. 

UPDATE와 DELETE는 기존의 인덱스를 삭제하지 않고 '사용하지 않음'처리를 하는데, 

만약 어떤 테이블에 UPDATE와 DELETE가 빈번하게 발생된다면

실제 데이터보다 인덱스는 훨씬 더 많이 존재하게 된다. 

SQL문 처리 시 비대해진 인덱스에 의해 오히려 성능이 떨어진다. 

 

 

적절하게 활용하지 않으면 오히려 데이터베이스 성능 저하되거나 저장 공간 낭비될 수 있다. 

따라서 인덱스를 적절히 선택하고 생성하는 것이 중요하다.

 

 

 

 

인덱스를 언제 사용해야할까? 

 

□ 사용해야 하는 경우

 

     - 자주 검색되는 열 (데이터 조작 자주 발생)

          : 데이터를 자주 검색하는 열에 인덱스를 생성하면 검색 속도를 향상시킬 수 있다.

 

     - 정렬이나 그룹화가 필요한 경우 (데이터 중복도 낮은 컬럼)

          : ORDER BY나 GRUOP BY와 같은 정렬 및 그룹화 작업에 사용될 열에 인덱스를 생성하면 작업이 더 빨라진다.

 

     - 조인 연산 수행하는 경우 (join, where)

          : 조인 연산 수행하는 경우 인덱스 사용해 연산 속도 향상시킬 수 있다. 

           인덱스 생성하여 조인 대상 테이블의 데이터를 빠르게 검색하는 것이 좋다. 

 

     - 유니크한 값을 가지고 오는 경우

          : 인덱스는 유니크한 값을 가지고 있는 필드에 대해 중복되지 않는 값을 빠르게 검색할 수 있다. 

           이러한 경우 인덱스를 사용하여 검색 속도 빠르게 할 수 있다. 

 

   

 

□ 사용하지 말아야 하는 경우 

 

     - 테이블 크기가 작을 때

          : 작은 테이블에서는 인덱스를 사용하는 것이 성능에 미치는 영향이 미비할 수 있다.

 

 

 

     - 자주 변경되는 열

          : 자주 변경되는 일에 인덱스를 생성하면 삽입,삭제,업데이트 작업의 성능이 저하될 수 있다. 

 

 

 

 

 

인덱스의 동작 순서

- Index Table에서 WHERE에 포함된 값을 찾는다.

- 해당 값의 pk 가져온다.

- 가져온 Pk 값으로 원본 테이블에서 값을 조회한다.

 

 

 

 

 

 

인덱스의 종류

□ 열에 따른 분류

     - 단일 열 인덱스 (Single Column Index)

          : 특정 열(속성)에 대한 인덱스로 하나의 열만을 기반으로 한다. 

 

     - 복합 열 인덱스 (Composite Index, =Multi Column Index)

          : 두 개 이상의 열을 함께 인덱스로 설정한 것으로, 여러 열의 조합에 대해 인덱스를 생성한다. 
          데이터베이스에서 여러 열을 조건으로 사용하는 쿼리의 성능을 향상시키는데 도움이 된다.

 

          > Covering Index, 컬럼의 순서에 따라 정렬된다.
               인덱스를 검색한 이후 물리적인 데이터 블록을 읽을 필요가 없다. 

           ㄴ>  WHERE 절에서 AND 연산자에 의해 자주 같이 질의 되는 컬럼일 경우 !

          

 

 

 

 

□ 키에 따른 분류

     - 기본 인덱스 (Primary Index)

          : 기본키를 포함하는 인덱스 (키의 순서가 레코드의 순서를 결정 지음)

 

     - 보조 인덱스 (Secondary Index) 

          : 기본 인덱스 이외의 인덱스 (키의 순서가 레코드 순서를 의미하지는 않음)

           다른 인덱스를 돕는 보조 인덱스. 레코드가 어디 위치한지만 알려주는 역할

           주키가 아니라 보조키 이용하여 추가적인 방법으로 원하는 값을 가져올 수 있다. 

 

 

□ 파일 조직에 따른 분류

     - 집중 인덱스 (Clustered Index)

          : 데이터 레코드의 물리적 순서가 그 파일에 대한 인덱스 엔트리 순서와 동일하게(유사하게) 유지되도록 구성한 인덱스

          탐색 키 값에 따라 정렬된 데이터 파일에 대해 정의한다. 

          많은 레코드가 ordering field에 대한 공통된 값을 가질 경우 사용할 수 있다.

 

     - 비집중 인덱스 (Unclustered Index) 

          : 집중 형태가 아닌 인덱스

 

 집중 인덱스 (Clustered Index)

: 한 방(테이블) 안에 책들을 주제(인덱스 키)에 따라 정렬된 순서로 놓은 도서관이다.

ex) 과학, 역사, 문학 등 주제에 따라 정리되어 있다. 

사용자가 특정 주제의 책을 찾을 때 주제(인덱스 키)에 따라 정렬된 방(테이블)을 찾아가면

해당 주제에 관련된 책을 빠르게 찾을 수 있다.

 

특정 주제(인덱스 키)에 따라 정렬된 방(테이블)이 유사한 순서로 배치되기 때문에,

특정 주제의 책을 찾는 것이 효율적이다.

 

 

비집중 인덱스 (Unclustered Index) 

: 주제와 상관없이 임의로 정렬 해놓은 도서관이다. 

과학, 문학 책이 섞여있다.

도서관 사용자가 특정 주제의 책을 찾을 때 

 

모든 방을 돌아다니며 검색해야 하므로 시간이 더 걸릴 수 있다.

 

 

□ 데이터 범위에 따른 분류

     - 밀집 인덱스 (Dense Index)

          : 데이터 레코드 각각에 대해 하나의 인덱스 엔트리가 만들어진 인덱스

           모든 key value에 대해 index entry를 준다. 모든 레코드에 대해 색인을 만든다. 

 

 

     - 희소 인덱스 (Sparse Index) 

          : 레코드 그룹 또는 데이터 블록에 대해 하나의 엔트리가 만들어지는 인덱스

           몇몇 값에 대해서만 entry를 만는다. 대부분 기본적으로 희소 인덱스를 사용한다. Primary Index도 Sparse Index이다.

 

 

 

 

 

 

 

인덱스의 자동 생성

일부 데이터베이스 시스템은 인덱스를 자동으로 생성하거나 추천하는 기능을 제공한다. 

이러한 자동 인덱스 생성 기능을 사용해 성능을 향상시킬 수 있다. 

 

 

인덱스의 힌트 

데이터베이스 시스템에서는 쿼리 실행 시 특정 인덱스를 사용하도록 지시하는 힌트를 제공할 수 있다. 

이를 통해 개발자는 쿼리의 실행 계획을 조정하여 최적의 성능을 얻을 수 있다.

 

https://brunch.co.kr/@skeks463/25   (인덱스의 종류도 다양하고 작동 방식이 다르기 때문에 ... 내용이 너무 방대....)

https://mangkyu.tistory.com/96

https://github.com/devSquad-study/2023-CS-Study/blob/main/DB/db_index.md

 

 

 

 

 

 

 

그럼 이런 인덱스를 사용할 때는 어떤 자료 구조를 사용할까? 

 

S)DB_07_B tree, B+tree