인덱스

인덱스

• 데이터 검색 성능을 향상시키기 위해 사용되는 데이터 구조

  • 빠른 정렬
  • 빠릅 그룹화

• 책의 색인과 유사하게 작동

• 특정 키 값 기준으로 데이터 위치 빠른 검색 가능

• 인덱스 오버헤드: 데이터 변경 시 인덱스도 갱신해야 함

• 인덱스 생성

  • CREATE INDEX idx_lastname ON employees (last_name);
    CREATE INDEX idx_department ON employees (department);

• 인덱스 활용 검색

  • 조건이 하나일 때는 해당 인덱스 사용하여 빠르게 검색
  • 두 개 이상일 경우 다중 열 인덱스가 적합
    • 다중 열 인덱스의 첫 번째 요소인 경우 하나의 조건도 사용 가능
  • 중복된 값이 있는 경우 일반적으로 DFS

  • SELECT * FROM employees WHERE last_name = 'Doe';

• 인덱스 삭제

  • DROP INDEX idx_lastname ON employees;
    DROP INDEX idx_department ON employees;

• 인덱스 확인

  • SHOW INDEX FROM table_name

• 쿼리문이 사용하는 인덱스 우선순위

  • optimizer가 인덱스를 선택

  • EXPLAIN 키워드로 선택된 인덱스 확인 가능

    • EXPLAIN SELECT * FROM employees WHERE email = 'john.doe@example.com';

  • USE INDEX

    • SELECT * FROM employees USE INDEX (idx_email) WHERE email = 'john.doe@example.com';

  • FORCE INDEX

    • SELECT * FROM employees FORCE INDEX (idx_email) WHERE email = 'john.doe@example.com';

• 다중 열 인덱스(Multicolumn index, Composite index)

  • 두 개 이상 열을 결합한 인덱스
  • 쿼리의 WHERE 절에 여러 열이 동시에 사용될 때 효율적

• 기본 키 생성 시 인덱스 자동 생성

• Unique 인덱스

  • 중복되지 않는 값을 갖는 열에 대해 생성되는 인덱스
  • 각 행의 고유한 값 보장

  • CREATE UNIQUE INDEX idx_email ON employees (email);

• Covering 인덱스

  • SELECT 와 WHERE 절에 사용된 모든 열을 포함하는 인덱스
  • 데이터 참조 없이 인덱스만으로 결과 반환 가능

• Hash 인덱스

  • 해쉬 함수 사용
  • 일반적으로 빠름
  • 범위 검색이나 정렬 작업에 부적합

• Full Scan이 더 좋은 경우

  • 대부분의 데이터 검색
  • 정렬 작업이 불필요

B-Tree

• 노드에는 하나뿐 아니라 2개 이상의 데이터 입력 가능, 정렬된 상태
• 최소 자식 수 : $2\div m$
• 최대 자식 수(차수) : $m$
• 노드의 키가 k개라면 자식 노드의 개수는 k+1개
• 키의 왼쪽은 작은 값 오른쪽은 큰 값
• 하나의 노드 내 데이터 수: $2\div m$ ~ $m-1$개
• 모든 리프 노드는 같은 레벨에 존재

인덱스 선정 시

카디날리티

• 특정 데이터 집합의 유니크한 값의 개수
  • 예) 성별 2

선택도

• 5% ~ 10% 적당
• 특정 칼럼의 row 수 / 총 row 수
  • 중복되는 값이 낮을 수록 적합

활용도

• where절이나 join의 on 절 같은 곳에 잘 활용이 되는지?
• 동등 비교인 경우 특히 빠르다.

PK와 UK 차이

• PK는 NULL이 불가능.
• PK로 클러스터 인덱스(PK에 따라 정렬하여 저장) 생성
• PK는 하나의 테이블에 하나만 존재, UK는 여러 개 가능

커버링 인덱스

• 쿼리를 충족시키는데 필요한 모든 데이터를 갖고 있는 인덱스
• 실행 계획 Extra에 using index로 표기
• 데이터 페이지를 찾는 과정이 필요 없다.
• 비 클러스티 키는 pk로 검색을 하지 않아도 되서 빠르다.
  • 클러스터 키 역시 빠르다.

클러스터 키와 비 클러스터 키

• 클러스터 키: 인덱스 페이지 자체가 데이터 페이지
  • 리프 페이지가 데이터 페이지 (리프 페이지 순차적으로 이중 연결)
  • 데이터 페이지 정렬
• 비 클러스터 키: 데이터 페이지와 별도의 위치에 인덱스 구성
  • 인덱스를 구성하는 값과 데이터에 해당하는 pk 값 저장