개발기술/데이터베이스

데이터베이스 인덱스 종류와 전략 (MYSQL InnoDB)

bsh6226 2024. 12. 27. 10:54

인덱스 개념과 분류

인덱스 개념 및 목적

  • 인덱스의 목적: 데이터베이스 테이블의 모든 데이터를 검색하지 않고, 빠르게 원하는 데이터를 탐색하기 위해 사용됩니다.
  • 인덱스란: 레코드의 특정 칼럼 값과 레코드의 저장 위치를 키-값 쌍으로 저장하며, 빠른 탐색을 위해서 칼럼 값을 항상 정렬된 상태로 유지합니다.
  • 인덱스 사용 결과:
    • 데이터를 저장할 때, 인덱스를 유지하기 위한 추가적인 저장과 정렬 작업이 필요하여 **저장 성능(Insert, Update, Delete)**이 저하됩니다.
    • 그러나 읽기 작업 시, 이미 정렬된 데이터를 기반으로 탐색하므로 조회 성능이 크게 향상됩니다.
     
  • 데이터 저장과 읽기의 비율: 일반적으로 저장 작업과 읽기 작업의 비율이 2:8 정도로, 저장 성능을 어느 정도 희생할지 고려하여 인덱스 전략을 설계해야 합니다.

인덱스 역할에 따른 분류

  1. 프라이머리 키(Primary Key):
    • 레코드를 식별하는 대표 칼럼 값으로, 레코드 생성 시 필수적으로 생성됩니다.
    • 특징: 중복과 NULL 값을 허용하지 않습니다.
  2. 세컨더리 인덱스(Secondary Index):
    • 프라이머리 키를 제외한 나머지 모든 인덱스입니다.
    • 선택적으로 생성하며, 데이터 조회 성능을 높이는 데 사용됩니다.

인덱스 개수에 따른 분류

  1.  단일값 인덱스(Single-Value Index):
    • 하나의 데이터 레코드가 하나의 키값만 가지는 인덱스.
  2. 멀티밸류 인덱스 : 하나의 데이터 레코드가 여러 개의 키값을 가질 수 있는 형태의 인덱스 첫 번째 칼럼을 기준으로 정렬한 후, 동일한 값이 있을 경우 두 번째 칼럼으로 정렬합니다.
    • 첫번째 인덱스 기준으로 정렬 후 동일한 인덱스인 경우 두번째 인덱스로 정렬하므로 조건 검색시 첫번째 인덱스 조회가 먼저 적용되도록 하는게 필요

인덱스 중복 허용 여부에 따른 분류

  1. 유니크 인덱스(Unique Index):
    • 데이터의 중복을 허용하지 않음.
    • 특정 칼럼 값이 고유해야 하는 경우에 사용됩니다.
  2. 비유니크 인덱스(Non-Unique Index):
    • 데이터의 중복을 허용함.
    • 조회 시 일치하는 값이 다수일 경우, 모든 일치하는 레코드를 탐색해야 하므로 유니크 인덱스보다 조회 비용이 높을 수 있습니다.

인덱스 저장관련 알고리즘

Binary Search Tree

  • 각 노드 별로 최대 하나의 키 값과 두개의 자식노드를 갖을 수 있다, 
  • 모든 노드의 왼쪽 서브트리는 해당 노드의 값보다 작은 값만 가지고 모든 노드의 오른쪽 서브트리는 해당 노드값의 큰 값만 가진다.

B-Tree

  • Binary Tree를 일반화한 트리로 자녀노드가 M개인  Multiway SearchTree의 일종이다
  • 각 노드 별로 최대 m-1개의 키 값과 m개의 자식을 갖을 수 있다.
  • 모든 노드의 왼쪽 서브트리는 해당 노드의 값보다 작은 값만 가지고 노드의 오른쪽 서브트리는 해당 노드값의 큰 값만 가진다.
  • 자녀노드의 개수를 원하는 만큼 결정해서 사용할 수 있다

 

 

 

B-Tree가 DB에 사용되는 이유

  • DB에서 가장 일반적으로 사용되는 인덱스 구조
  • 시간복잡도 측면에서 이진트리에 우세
    • 이진트리 : n번 조회시 최대 2^n개의 데이터검색가능
    • m-way트리 : n번 조회로 최대 m^n번개의 데이터 검색가능 (M이 410일때 3번의 조회로 약 6천만 데이터 조회가능)
  • 범위 조회에 효율적임

 

R-Tree 인덱스:

  • 공간 데이터(지리 정보 등)를 효율적으로 처리하기 위해 설계된 인덱스 구조입니다.
  • 다차원 데이터를 처리할 때 사용되며, 공간 범위 쿼리와 같은 작업에 적합합니다.

 

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

1. 정의

  • 클러스터드 인덱스(Clustered Index)는 데이터의 저장 방식으로, 프라이머리 키(primary key)의 인덱스 구조의 리프 노드(leaf node)에 데이터의 주소가 아닌 데이터 자체가 저장되는 방식을 말합니다.

2. 특징

  1. 데이터 정렬
    • 데이터는 프라이머리키 값을 기준으로 정렬되고, 프라이머리 키 값이 비슷한 레코드끼리 묶여 저장됩니다.
  2. 물리적 저장 위치
    • 프라이머리 키에 따라 데이터의 물리적 주소가 결정됩니다.
  3. InnoDB와 다른 엔진의 차이
    • InnoDB에서는 모든 테이블이 기본적으로 프라이머리 키를 기준으로 레코드가 클러스터링되어 있습니다.
    • 다른 스토리지 엔진(예: MyISAM)에서는 프라이머리 키와 세컨더리 키의 구조적 차이가 없으며, 데이터와 인덱스가 분리되어 저장됩니다.
  4. 함의
    • 클러스터링 인덱스 하에서는 프라이머리 키로 검색할때 처리 성능이 매우 빠름, 세컨더리 인덱스로 조회할 경우 트리검색을 세컨더리 인덱스 조회, 프라이머리키 인덱스 조회 총 2회 진행해야함.
    • 프라이머리 키 값은 모든 인덱스가 pointer로 사용하고 있기에 프라이 크기가 클수록 전체적으로 데이터 저장량이 인덱스의 배수로 커짐

 

 

인덱스 설정 예시

데이터 예시: yyyymmdd / hhmmss / camera_no / 상태 값들...
  • camera_no: 약 300개 (수백~수천 개 예상)
  • yyyymmdd: 날짜, 10년간 수집된다고 가정 (3,650일)
  • hhmmss: 15초 단위 수집 → 하루 86,400 / 15 = 약 5,760건
  • 총 데이터량: 하루 300개 × 5,760건 = 172만 건/일 → 연간 약 6억 건


인덱스 전략

✅ 인덱스 설계의 3대 기준

기준 설명 영향
1. 선택도 (Selectivity / Cardinality) 조건 하나로 데이터를 얼마나 좁힐 수 있느냐 조건이 희귀할수록 인덱스 효율이 높아짐
2. 쿼리에서의 빈도
(Query Usage Frequency)		 어떤 조건이 자주 쓰이느냐 자주 쓰이는 조건을 인덱스 선두에 둬야 효과 큼
3. 데이터 정렬/저장 구조
(Clustering Effect)		 물리적으로 데이터가 연속되게 읽히느냐 랜덤 I/O보다 순차 I/O가 빠르므로 큰 차이 발생

칼럼별 인덱싱 비교기준

기준 camera_no yyyymmdd hhmmss
① 선택도 (비율)
값이 작을수록 좋음		 0.33 % (1/300) 0.027 % (1/3650) 0.017 % (1/5760)
② 쿼리 사용 빈도 매우 높음 – 모든 조회에서 사용 높음 – 일자별 조회 빈번 중간~높음 – 주로 범위 조건
③ 클러스터링 효과
(물리적 연속성 기대)		 높음 – camera별로 INSERT 순서가 뭉칠 가능성 중간 – 날짜 순 삽입이면 약간 효과 낮음 – 시간 값은 전 카메라에 산재

 

  • DB 구현방식에 따라 다르겠지만 일반적인 관점에서 cameraNo가 합리적임
    • 선택도가 나쁘지 않음, 쿼리사용빈도는 훨씬 높을 것으로 예상됨, 클러스터링 효과가 높을 것으로 예상됨
    • hhmmss는 조건 선택도는 높지만 물리적으로 퍼져 있어서 랜덤 페이지 접근이 많음 반면 camera_no는 물리적으로 뭉쳐 저장돼서 I/O 패턴이 순차적이고 효율적
    • 또한 쿼리에서 조건 빈도가  camera_no는 쿼리에서 50배 더 자주 사용된다면,
      전체 성능 최적화 측면에서는 camera_no 인덱스를 쓰는 것이 더 유리하다.

\

복합인덱스

여러 컬럼을 조합하여 생성하는 인덱스를 의미합니다. 단일 인덱스가 하나의 컬럼에 대해서만 생성되는 것과 달리, 복합 인덱스는 여러 컬럼을 함께 사용하여 데이터 검색 효율을 높입니다. 복잡한 WHERE 조건이나 ORDER BY에 대해 한 번의 인덱스 탐색으로 해결할 수 있음.

 

 

예시 쿼리

데이터 예시: yyyymmdd / hhmmss / camera_no / 상태 값들...
  • camera_no: 약 300개 (수백~수천 개 예상)
  • yyyymmdd: 날짜, 10년간 수집된다고 가정 (3,650일)
  • hhmmss: 15초 단위 수집 → 하루 86,400 / 15 = 약 5,760건
  • 총 데이터량: 하루 300개 × 5,760건 = 172만 건/일 → 연간 약 6억 건
SELECT *
FROM logs
WHERE camera_no = 313
AND yyyymmdd = 20250615
AND hhmmss BETWEEN 120000 AND 121500;   -- 6 time-slots (≈90 s)

1. 단일 인덱스의 한계

단일 인덱스만 있는 경우

  • camera_no에 인덱스가 있음, camera_no = 303 조건만 인덱스로 탐색시 2.1 million (365 × 5 760) row로 대상이 좁혀짐.
  • yyyymmdd = '20250616'와 hhmmss between 120000 and 121500; 이라는 조건은 카메라 번호로 필터링된 결과 칼럼에 대해서 2백만번 랜덤 I/O로 접근 후에 개별 필터링  (SSD 환경에서도 약 6분 이상 걸릴 수 있는 작업)

 

2. 복합 인덱스 (camera_no, yyyymmdd, hhmmss) 도입

얼마나 많은 row를 건드리는지

  1. camera_no = 313 → 1/300 of table → 2.1 M →
  2. yyyymmdd = 20250615 → 1/365 of those → 5 760 →
  3. hhmmss BETWEEN … → 6/5 760 of those → ≈ 6 rows

이 경우 B-Tree 인덱스의 leaf node 6개만 스캔하게 됨

 

3. 복합 인덱스 생성 시 고려 사항:

  • 컬럼 순서: 복합 인덱스룰 정의할 때 컬럼의 순서는 중요합니다.  복합 인덱스에서 가장 왼쪽 컬럼부터 순서대로 조건이 들어와야 인덱스가 효율적으로 사용됩니다.
    • 인덱스 순서가 (hhmmss, yyyymmdd, camera_no) 인경우  쿼리에서는 camera_no부터 찾고 싶은데, 인덱스 구조가 hhmmss부터 되어 있으니 사용할 수 없음
  • 컬럼 선택: 복합 인덱스에 포함될 컬럼을 신중하게 선택해야 합니다. 너무 많은 컬럼을 포함하면 인덱스 크기가 커져서 오히려 성능 저하를 유발할 수 있습니다.
  • 인덱스가 많으면 많을수록 조회에도  좋은것은 아니다 
    • DB는 쿼리를 실행하기 전에 어떤 인덱스를 사용할지 결정하는 비용을 발생시킵니다.  인덱스가 많아지면, DB 엔진은 가능한 인덱스들 중 최적의 인덱스를 골라야 하며, 이 과정에서 쿼리 플래너가 더 많은 경로를 탐색해야 합니다.
  • 데이터의 유지방식을 고려해야함
    • Insert 시 인덱스 정렬 재배치가 거의 없음. 일반적으로 B-Tree 인덱스는 정렬된 구조를 유지하기 위해 insert 시 적절한 위치를 찾아 데이터를 끼워 넣는데 하지만 **append 구조 (예: 시간순 로그)**에서는 항상 인덱스의 가장 마지막 위치에 추가됨
    • 인덱스의 유지 비용은 주로 update나 delete 작업에서 큽니다. update/ delete 는 인덱스 값을 수정해야 하므로, 인덱스 재구성 비용이 큽니다.

 

쿼리전략설정

  • 주로 사용하는 쿼리 파악
    • CUD관점에서 단순하게 데이터 append가 일어남
    • 조회 관점에서는 cameraNo, cameraNo +yyyymmdd + hhmmss, cameraNo + hhmmss, cameraNo + yyyymmdd 세가지 패턴으로 이루어짐
  • 쿼리설정 
    • 복합인덱스 1. camerano + yyyymmdd +hhmmss  2. camerano + hhmmss 선정
      • cameraNo + yyyymmdd, cameraNo, cameraNo + yyyymmdd + hhmmss는 1번 인덱스로 사용할수 있음 ( 단 쿼리순서 주의)
      • cameraNo + hhmmss는 1번 인덱스로 사용불가하여 별도 인덱스 필요
      • CUD관점에서 append만 하는 구조이므로 ,인덱스 2개 유지에 따른 부하가 적다.