Index

DB 인덱스를 효과적으로 설정하는 방법: 고려해야 할 4가지
[데이터베이스] 인덱스란?

• 데이터베이스에는 여러 개의 테이블이 존재하고 그 테이블에는 다양한 수의 데이터가 쌓임
  • 만약 천 만 개의 데이터가 쌓였다고 가정할 때, 여러 조건을 조합해서 데이터를 조회하려면 로딩 시간이 아주 오래 걸림
  • 이 때 인덱스(Index)를 설정하면 속도가 빨라짐

Index란?

• 인덱스는 테이블에서 원하는 데이터를 쉽고 빠르게 찾기 위해 사용
  • 이러한 인덱스는 자주 사용되는 필드 값으로 만들어진 원본 테이블의 사본이라고 생각할 수 있음

• 인덱스는 테이블의 동작 속도를 높여주는 자료 구조
  • 인덱스는 DBMS의 저장 성능을 희생하고 검색 성능을 높이기 위해 만들어진 자료 구조
  • 인덱스로 데이터의 위치를 빠르게 찾아주는 역할을 함
  • 쉬운 예시로 책 뒷 편에 '색인'이 바로 이 인덱스의 역할이라고 보면 됨
    • 색인을 통해 원하는 키워드에 대한 페이지로 바로 이동 가능
  • 책의 색인이 여러 페이지에 기재되어 있는 것처럼 데이터베이스의 인덱스도 데이터베이스 메모리에 일정 공간을 사용해 저장이 되고 데이터를 조회할 때 소모되는 메모리를 효율적으로 사용하게 됨
    • 단, 인덱스가 잘 설정되었을 경우

• 테이블의 컬럼을 색인화(따로 파일로 저장)하여 검색 시 해당 테이블의 레코드를 전체적으로 찾아보는게 아니라 색인화 되어있는 INDEX 파일을 검색하여 검색 속도를 빠르게 함
  • RDBMS에서 사용하는 인덱스는 B-Tree 에서 파생된 B+ Tree 를 사용해서 색인화

• 보통 SELECT 쿼리의 WHERE 절이나 JOIN 예약어를 사용했을 때 인덱스가 사용되며 SELECT 쿼리의 검색 속도를 빠르게 하는데 목적을 두고 있음
  • DELETE, INSERT, UPDATE 쿼리에는 해당 사항이 없으며 인덱스 사용 시 오히려 느려짐

• SQL 서버에서 데이터의 레코드는 내부적으로 아무런 순서 없이 저장됨
  • 이때 데이터 저장영역을 Heap이라고 함
• Heap에서는 인덱스가 없는 테이블의 데이터를 찾을 때 전체 데이터 페이지의 처음 레코드부터 끝 페이지의 마지막 레코드까지 모두 조회하여 검색 조건과 비교
  • 이러한 데이터 검색 방법을 테이블 스캔(Table Scan) 또는 풀 스캔(Full Scan)이라고 함
  • 이럴 경우 양이 많은 테이블에서 일부분의 데이터만 불러 올 때 풀 스캔을 하면 처리 성능이 떨어짐
• 즉, 인덱스는 데이터를 SELECT 할 때 빨리 찾기 위해 사용됨

Tip!

• 아주 두꺼운 책 한 권에 DBMS를 비유했을 때
  • 책의 맨 처음이나 끝에 존재하는 색인 = DBMS의 인덱스
  • 책의 내용 = DBMS의 데이터 파일
  • 책의 색인을 통해 알 수 있는 페이지 번호 = DBMS의 데이터 파일에 저장된 레코드의 주소
  • 책의 색인은 사전 순으로 정렬 = DBMS의 인덱스도 일정 기준으로 정렬 가능

Tip! 추가 내용

• MySQL은 데이터를 검색할 때 첫 번째 필드부터 차례대로 테이블 전체를 검색
  • 따라서 테이블이 크면 클수록 데이터를 탐색하는 시간도 많이 늘어나게 됨
  • 하지만 인덱스를 사용하면 테이블 전체를 읽지 않아도 되므로 검색과 질의에 대한 처리가 빠르게 이루어짐
• 이러한 인덱스는 사용자가 직접 접근할 수는 없으며 검색과 질의에 대한 처리에서만 사용됨
• 이렇게 인덱스가 설정된 필드 값을 포함한 데이터의 삽입, 삭제, 수정 작업이 원본 테이블에서 이루어질 경우, 인덱스도 함께 수정되어야 함
  • 따라서 인덱스가 설정된 테이블의 처리 속도가 느려질 수 있음
• 그러므로 인덱스는 수정보다는 검색이 자주 사용되는 테이블에서 사용하는 것이 더 좋음

Index의 구조와 작동 원리

인덱스의 구조

• 인덱스는 논리적•물리적으로 테이블과 독립적
• 테이블은 컬럼에 데이터가 정렬되지 않고 입력된 순서대로 들어가지만 인덱스는 KEY 컬럼과 ROWID 컬럼 두개로 이루어져 있고 오름차순 또는 내림차순으로 정렬이 가능
  • Key: 인덱스를 생성하라고 지정한 컬럼의 값

• MySQL에서 테이블 생성하면 아래와 같은 3가지 파일이 생성됨
  • FRM : 테이블 구조 저장 파일
  • MYD : 실제 데이터 파일
  • MYI : Index 정보 파일 (Index 사용 시 생성)
• 사용자가 쿼리를 통해 인덱스를 사용하는 칼럼을 검색하게 되면 이때 MYI 파일의 내용을 활용
• 디스크 공간은 보통 테이블을 저장하는 데 필요한 디스크 공간보다 작음
  • 왜냐하면 보통 인덱스는 KEY-ROWID만 가지고 있고 테이블의 세부 항목들은 갖고 있지 않기 때문

인덱스의 작동 원리

# 예시 SQL문

SELECT *
FROM Drink
WHERE drinkno=702;

• 데이터 파일의 블록이 10만개 일 때, 위와 같은 SQL문을 수행하는 과정
  • ① 서버 프로세스가 파싱 과정을 마친 후 DB Buffer Cache에 drinkno가 702인 정보가 있는지 확인
  • ② 정보가 없으면 하드 디스크 파일에서 702 정보를 가진 블록을 복사해서 DB Buffer Cache로 가져온 후 702 정보만 골라내서 사용자에게 보여줌

• 이 때 두 가지 경우로 나뉘어짐
  • 인덱스 없는 경우
    • 702 정보가 어떤 블록에 들어 있는지 모르므로 10만개 전부 DB Buffer Cache로 복사한 후 하나하나 찾음
  • 인덱스 있는 경우
    • WHERE 절의 컬럼이 인덱스가 만들어져 있는지 확인 후, 인덱스에 먼저 가서 702 정보가 어떤 ROWID를 가지고 있는지 확인한 후 해당 ROWID에 있는 블록만 찾아가서 DB Buffer Cache에 복사함

Tip! 추가 내용

DML(Data Manipulation Language)이 일어났을 때의 상황

• INSERT
  • 기존 블록에 여유가 없을 때 새로운 데이터가 입력되면 새로운 블록을 할당 받은 후 Key를 옮기는 작업을 수행
  • Index Split 작업 동안 해당 블록의 Key 값에 대해서 DML이 블로킹됨
    • 대기 이벤트 발생

• DELETE
  • 테이블에서 데이터가 삭제 되는 경우
    • 데이터가 지워지고 다른 데이터가 그 공간 사용 가능
  • 인덱스에서 데이터가 삭제 되는 경우
    • 데이터가 지워지지 않고 '사용 하지 않음' 표시만 해둠
    • 테이블의 데이터 수와 인덱스의 데이터 수가 다를 수 있음

• UPDATE
  • 테이블에서 업데이트가 발생하면 인덱스는 업데이트 할 수 없음
  • 인덱스에서는 삭제가 발생한 후 생성하는 작업 과정을 거침
    • 2배의 작업이 소요됨

Index의 특징

인덱스는 WHERE절에서 효과적

• 인덱스는 SELECT - FROM - WHERE 절 중 WHERE 절에 사용할 컬럼에 대한 효율화라고 볼 수 있음
  • WHERE 절을 사용하지 않고 인덱스가 걸린 컬럼을 조회하는 것은 성능에 아무 영향을 주지 못함
  • 예를 들어, ‘학생’ 테이블에 ‘학번’, ‘이름’, ‘전화번호’가 있다고 가정했을 때
    • 인덱스는 ‘학번’, ‘전화번호’에 걸려 있음
    • 다음 중 인덱스가 영향을 주는 쿼리는 어떤 것일까요?
      • 1번: SELECT '학번' FROM '학생';
      • 2번: SELECT '전화번호' FROM '학생' WHERE '이름' = "김철수";
      • 3번: SELECT * FROM '학생' WHERE '학번' = 1;
    • 정답은 3번
  • WHERE 절에 사용할 때 성능을 향상시킴

인덱스 설정에 따른 효과 차이

• 인덱스는 하나 혹은 여러 개의 컬럼에 대해 설정할 수 있음
  • 단일 인덱스를 여러 개 생성할 수도, 여러 컬럼을 묶어 복합 인덱스를 설정할 수도 있음
  • 그러나 무조건 많이 설정하는게 검색 속도 향상을 높여주지는 않음
  • 인덱스는 데이터베이스 메모리를 사용하여 테이블 형태로 저장되므로 개수와 저장 공간은 비례
• 따라서, 조회 시 자주 사용하고 고유한 값 위주로 인덱스를 설정하는게 좋음

DML(Data Manipulation Language)에 미치는 영향

• SELECT 쿼리에서 성능이 잘 나오지만 INSERT, UPDATE, DELETE 쿼리에서는 때에 따라 다름
  • UPDATE, DELETE는 WHERE 절에 잘 설정된 인덱스로 조건을 붙여주면 조회할 때 성능은 크게 저하되지 않음
    • 단, 업데이트 할 데이터를 찾을 때의 속도가 빨라지는 것이지 업데이트 자체가 빨라지는 것은 아님
  • 반면 INSERT의 경우, 새로운 데이터가 추가되면서 기존에 인덱스 페이지에 저장되어 있던 탐색 위치가 수정되어야 하므로 효율이 좋지 않음
• 즉, 인덱스는 원하는 데이터를 빠르게 찾을 때 효과적

효율적인 Index 설정

• 인덱스는 한 테이블당 보통 3~5개 정도가 적당
  • 물론 테이블의 목적 등에 따라 개수는 달라질 수 있음

• 인덱스는 컬럼을 정해서 설정하는 것이므로 후보 컬럼의 특징을 잘 파악해야 함
• 아래 4가지 기준을 사용하면 효율적으로 인덱스를 설정할 수 있음
  • 카디널리티 (Cardinality)
  • 선택도 (Selectivity)
  • 활용도
  • 중복도

카디널리티 (Cardinality)

• 컬럼에 사용되는 값의 다양성 정도, 즉 중복 수치를 나타내는 지표
  • 중복도가 낮으면 카디널리티가 높음
  • 중복도가 높으면 카디널리티가 낮음
  • 카디널리티는 상대적인 개념
    • 후보 컬럼에 따라 상대적으로 중복 정도가 낮다 혹은 높다로 표현됨

• 카디널리티가 높을수록 인덱스 설정에 좋은 컬럼
  • = 한 컬럼이 갖고 있는 값의 중복 정도가 낮을수록 좋음

• 예를 들어, 10개 rows를 가지는 '학생' 테이블에 '학번'과 '이름' 컬럼이 있다고 했을 때
  • '학번'은 학생마다 부여받으므로 10개 값 모두 고유
    • 중복 정도가 낮으므로 카디널리티가 높음
  • '이름'은 동명이인이 있을 수 있으니 1~10개 사이의 값을 가짐
    • 중복 정도가 '학번'에 비해 높으므로 카디널리티가 낮다고 표현할 수 있음

선택도 (Selectivity)

• 데이터에서 특정 값을 얼마나 잘 선택할 수 있는지에 대한 지표

# 선택도 계산 방법

= 컬럼의 특정 값의 row 수 / 테이블의 총 row 수 * 100
= 컬럼의 값들의 평균 row 수 / 테이블의 총 row 수 * 100

• 선택도가 낮을수록 인덱스 설정에 좋은 컬럼
  • 5-10% 정도가 적당

• 예를 들어, 10개 rows를 가지는 '학생' 테이블에 '학번', '이름', '성별' 컬럼이 있다고 했을 때
  • 학번은 고유하고, 이름은 2명씩 같고, 성별은 남녀 5:5 비율이라는 설정
    • '학번'의 선택도 = 1/10*100 = 10%
      • SELECT COUNT(1) FROM '학생' WHERE '학번' = 1; (모두 고유하므로 특정 값: 1)
    • '이름'의 선택도 = 2/10*100 = 20%
      • SELECT COUNT(1) FROM '학생' WHERE '이름' = "김철수"; (2명씩 같으므로 특정 값: 2)
    • '성별'의 선택도 = 5/10*100 = 50%
      • SELECT COUNT(1) FROM '학생' WHERE '성별' = F; (5명씩 같으므로 특정 값: 5)
• 즉, 선택도는 특정 필드값을 지정했을 때 선택되는 레코드 수를 테이블 전체 레코드 수로 나눈 것

활용도

• 해당 컬럼이 실제 작업에서 얼마나 활용되는지에 대한 값
  • 수동으로 쿼리를 조회할 때와 로직과 서비스에서 쿼리를 보낼 때 WHERE 절에 자주 활용되는지를 판단하면 됨

• 활용도가 높을수록 인덱스 설정에 좋은 컬럼

중복도

• 중복 인덱스 여부에 대한 값
  • 인덱스 성능에 대한 고려 없이 마구잡이로 설정하거나 다른 부서 다른 작업자의 분리된 요청으로
    같은 컬럼에 대해 인덱스가 중복으로 생성된 경우를 볼 수 있음

• 중복도가 없을수록 인덱스 설정에 좋은 컬럼

• 인덱스도 속성을 가지는데 인덱스는 테이블 형태로 생성되므로 속성은 컬럼으로 관리
  • 이 속성이 다를 때, 같은 컬럼에 대해 중복으로 인덱스 설정이 가능
  • 같은 컬럼에 대해 중복 인덱스가 설정되어 있다고 하더라도 SQL 자체 연산이 빠른 쪽으로 데이터를 조회하지만 인덱스도 결국 메모리의 일부이므로 필요 없는 항목은 삭제하는 것을 추천

인덱스 설정 기준 정리

Tip! 추가 내용

인덱스를 사용해야 하는 경우

• 테이블 행의 갯수가 많은 경우 (=데이터가 많은 경우)
• 인덱스를 적용한 컬럼이 where 절에서 많이 사용되는 경우
• JOIN 할 때 사용하는 컬럼 (on 부모테이블.PK = 자식테이블.FK)
• 검색 결과가 원본 테이블 데이터 2-4%에 해당하는 경우
• 해당 컬럼이 null을 포함하는 경우 (색인에 null이 제외)

인덱스를 사용하면 안좋은 경우

• 테이블의 행의 갯수가 적은 경우
• 검색 결과가 원본 테이블의 많은 비중을 차지하는 경우
• 원본 테이블의 삽입, 수정, 삭제가 빈번한 경우

Tip! 추가 내용

인덱스의 관리

• DBMS는 인덱스를 항상 최신의 정렬된 상태로 유지해야 원하는 값을 빠르게 탐색할 수 있음
• 따라서 인덱스가 적용된 컬럼에 INSERT, UPDATE, DELETE가 수행된다면 각각 다음과 같은 연산을 추가로 해 주어야 하며 그에 따른 오버헤드가 발생함
  • INSERT: 새로운 데이터에 대한 인덱스를 추가함
  • DELETE: 삭제하는 데이터의 인덱스를 사용하지 않는다는 작업을 진행함
  • UPDATE: 기존의 인덱스를 사용하지 않음 처리하고, 갱신된 데이터에 대해 인덱스를 추가함

인덱스의 장점과 단점

• 장점
  • 테이블의 기본 키는 자동으로 인덱스가 됨
  • 테이블을 조회하는 속도와 그에 따른 성능을 향상할 수 있음
    • 키 값을 기초로 하여 테이블에서 검색과 정렬 속도를 향상
    • 질의나 보고서에서 그룹화 작업의 속도를 향상
  • 인덱스를 사용하면 테이블 행의 고유성을 강화시킬 수 있음
  • 전반적인 시스템의 부하를 줄일 수 있음

• 단점
  • 인덱스 생성시 .mdb 파일 크기 증가
  • 인덱스를 관리하기 위해 데이터베이스의 약 10%에 해당하는 저장 공간이 추가적으로 필요
  • 인덱스를 관리하기 위해 추가 작업이 필요
  • 인덱스를 잘못 사용할 경우 오히려 성능이 저하되는 역효과가 발생할 수 있음
    • 만약 INSERT, DELETE, UPDATE가 빈번한 속성에 인덱스를 걸게 되면 인덱스의 크기가 비대해져서 성능이 떨어짐
    • 이러한 이유 중 하나는 DELETE와 UPDATE 연산 때문
    • UPDATE와 DELETE는 기존 인덱스를 삭제하지 않고, '사용하지 않음' 처리를 함
    • 만약 어떤 테이블에 UPDATE와 DELETE가 빈번하게 발생한다면 실제 데이터는 10만 건이지만, 인덱스는 100만 건이 넘어가게 되어 SQL문 처리 시 비대해진 인덱스에 의해 오히려 성능이 떨어지게 되는 것
  • 데이터 변경 작업이 자주 일어나는 경우 인덱스를 재작성해야 하므로 성능에 영향을 미침
  • 인덱스를 생성하는데 시간이 많이 소요될 수 있음
  • 한 페이지를 동시에 수정할 수 있는 병행성이 줄어듬

인덱스를 남발하지 말아야 하는 이유

• 데이터베이스 서버에 성능 문제가 발생하면 가장 빨리 생각하는 해결책이 인덱스 추가 생성
  • 문제가 발생할때마다 인덱스를 생성하면서 인덱스가 쌓여가는 것은 하나의 쿼리문을 빠르게는 만들 수 있지만 전체적인 데이터베이스의 성능 부하를 초래
  • 조회 성능을 극대화하려 만든 객체인데 많은 인덱스가 쌓여서 INSERT, DELETE, UPDATE시에 부하가 발생해 전체적인 데이터베이스 성능을 저하
  • 그렇기에 인덱스를 생성하는것 보다는 SQL문을 좀 더 효율적으로 짜는 방향으로 나가야함
• 인덱스 생성은 마지막 수단으로 강구해야 할 문제

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

• 규모가 작지 않은 테이블
• INSERT, UPDATE, DELETE가 자주 발생하지 않는 컬럼
• JOIN이나 WHERE 또는 ORDER BY에 자주 사용되는 컬럼
• 데이터의 중복도가 낮은 컬럼 (카디널리티가 높은 컬럼)
  • 예를 들어 성별이라는 컬럼이 있다고 했을 때 (성별은 남자와 여자만 있다고 가정)
  • 이때 성별에 인덱스를 걸어 봤자 탐색할 수 있는 값이 2개 밖에 없으므로 하나의 성별이 붙은 데이터를 검색하는 데 상황에 따라 전체를 찾아볼 수도 있음
  • 또한, 인덱스는 내부적으로 Key, Value의 트리 형태로 데이터를 저장하는데 Key가 중복되어 여러 개 존재하면 검색할 대상이 증가
  • 이러한 이유로 데이터의 중복도가 낮아서 분포도가 높은 컬럼에 대해 인덱스를 사용해야 함

인덱스를 리빌드하는 이유

• 인덱스 파일은 생성 후 INSERT, UPDATE, DELETE 등을 반복하다보면 성능이 저하됨
  • 생성된 인덱스는 트리구조를 가지는데, INSERT, UPDATE, DELETE 등이 오랫동안 일어나다보면 트리의 한쪽이 무거워져 전체적으로 트리의 깊이가 깊어짐
  • 이러한 현상으로 인해 인덱스의 검색속도가 떨어지므로 주기적으로 리빌딩하는 작업을 거치는 것이 좋음

인덱스의 분류

• 역할별
  • 클러스터 인덱스
    • 테이블의 기본 키(PK)에 대해 적용되는 인덱스
  • 비클러스터 인덱스
    • 테이블의 기본 키 외에 다른 컬럼에 적용된 인덱스
  • 유니크 인덱스
    • 테이블의 기본 키는 아니지만 중복을 허용하지 않는 Unique 속성이 들어간 컬럼에 적용된 인덱스
    • 클러스터 인덱스는 유니크 인덱스 + Not Null 속성을 띄고 있음
    • 유니크 인덱스도 한꺼번에 비클러스터 인덱스로 보기도 함

• 데이터 저장 방식(알고리즘)별
  • B-Tree 인덱스
  • R-Tree 인덱스
  • Hash 인덱스
  • Fractal-Tree 인덱스
  • Merge-Tree 인덱스

• 데이터 중복 허용 여부별
  • 유니크(Unique) 인덱스
  • 논유니크(Non-Unique) 인덱스

• 기능별
  • 전문 검색용 인덱스
  • 공간 검색용 인덱스

B-Tree 알고리즘 사용 이유

• B+ Tree
  • Index에 일반적으로 사용되는 알고리즘은 B+ Tree 알고리즘
  • B+ Tree 인덱스는 컬럼의 값을 변형하지 않고 (값의 앞부분만 잘라서 관리) 원래의 값을 이용해 인덱싱하는 알고리즘

• Hash
  • 컬럼의 값으로 해시 값을 계산해서 인덱싱하는 알고리즘으로 매우 빠른 검색을 지원
  • 하지만 값을 변형해서 인덱싱하므로 특정 문자로 시작하는 값으로 검색을 하는 등 전방 일치와 같이 값의 일부만으로 검색하고자 할 때는 해시 인덱스를 사용할 수 없음
  • 주로 메모리 기반의 데이터베이스에서 많이 사용

• 데이터 접근에 시간 복잡도가 O(1)인 Hash가 아닌 B-Tree를 사용하는 이유는 SELECT 절의 조건에 부등호 연산(>, <)이 포함될 경우 문제가 발생하기 때문
  • Hash는 동등 연산(=)에 특화되어있어 데이터베이스의 자료 구조에 적합하지 않음