| 이 글은 MySQL의 InnoDB 스토리지 엔진을 기준으로 작성되었습니다.

데이터베이스 index가 무엇일까? index가 무엇인지, 왜 사용하는지? 어떤 상황에서 사용했을 때 효과적인지 이제부터 알아보고자 한다.

😯 index를 사용하지 않는다면?

학생 테이블이 있다고 생각해보자. 관계형 DB를 기준으로 이름이 짜왕인 학생을 찾으려면

SELECT * FROM student WHERE name = '짜왕' 쿼리를 날릴 것이다.

그럼 만약 학생 테이블의 레코드 개수가 백만개가 넘어간다면? 그 백만개의 레코드를 하나씩 보면서 짜왕을 찾을 것이다.(즉, Full Scan을 한다. 시간 복잡도는 O(N)) 이것이 과연 효율적일까? 만약 레코드의 개수가 100억개가 넘어간다면? 하나씩 보면서 찾는 쿼리가 동시에 여러 사용자들에 의해서 실행된다면? 컴퓨터의 리소스를 많이 소비하게 되면서 버텨내질 못할 것이다.

출처: https://youtu.be/BIlFTFrEFOI?si=jOewqpSWUuRC67-P

이 예시를 보면 FULL SCAN을 통해 찾고 있으며 그다지 효율적이지 않은 프로세스라는 것을 알 수 있다.

하지만 우리가 index를 사용한다면? 훨씬 빠른 성능으로 조건에 해당하는 데이터를 찾을 수 있다. 이때, 이 인덱스가 B- tree 기반의 인덱스라면 시간 복잡도가 O(logN)이 된다.

따라서, 인덱스는 조건을 만족하는 튜플들을 빠르게 찾아내기 위해, 빠르게 정렬(order by)하거나 그룹핑(group by) 하기 위해 관계형 데이터베이스의 검색 성능을 높이기 위해 자주 사용된다.

🗂️ index란

인덱스란 데이터베이스 테이블의 검색 속도를 향상시키기 위한 자료구조이다.

(MySQL InnoDB기준)
특정 컬럼에 대한 인덱스를 생성하면 해당 컬럼의 데이터들은 정렬되어 별도의 메모리 공간에 데이터의 물리적 주소와 함께 저장된다. 인덱스를 사용하면 먼저 인덱스에서 찾고자 하는 데이터가 있는지 확인하고, 있다면 데이터의 물리적 주소로 이동해 데이터를 가져온다. 만약 인덱스에 존재하지 않는다면 DB를 순차 검색하는 방식으로 동작한다. 정렬된 데이터들에서 데이터를 검색하는 것이므로 테이블의 조회 속도를 향상시킬 수 있다. 예를 들어, 이미 정렬이 되어있기 때문에 ORDER BY 같이 부하가 많이 걸리는 작업도 효율적으로 처리할 수 있고 처음과 끝을 가져오는 MIN, MAX도 빨리 구할 수 있다.

출처: https://youtu.be/iNvYsGKelYs?si=2B2EMtvsfOhyvc3j


하지만, 인덱스가 적용된 컬럼에 수정/삭제 등의 작업이 들어가면 원본 테이블 뿐만 아니라 연결되어 있는 인덱스에도 똑같이 반영하고 정렬을 해줘야 해서 오버헤드가 증가한다는 단점이 있다. 그러므로 INSERT, DELETE, UPDATE 같은 DML 작업이 많이 발생하는 컬럼에 인덱스를 사용하면 많은 데이터가 인덱스에 저장될 수 있으므로, 성능이 오히려 저하될 수 있다.

또한, 인덱스를 저장하기 위해서는 DB의 약 10%에 해당하는 저장공간을 사용해야 한다고 한다. 인덱스를 만들때마다 용량을 차지하는 것이므로 검색 작업이 필요없는 컬럼들은 굳이 인덱스를 만들어 놓을 필요는 없다.

참고로, 기본키는 index를 생성할 필요 없다. 자동 생성된다.

위 같은 테이블을 만들고 나서 이 테이블에 생성된 인덱스를 보기 위해 show index from tasks; 를 입력했더니 아래와 같은 결과가 나왔다. 참고로, 아이디는 auto increment로 설정했다. 나는 어떤 인덱스도 만들지 않았지만 id 컬럼에 대한 PRIMARY KEY 인덱스(클러스터드 인덱스)와 외래 키로 인한 id 컬럼에 대한 인덱스(columns 테이블과의 관계)가 자동으로 생성된 것을 볼 수 있다. (MySQL 기준)

📚 인덱스의 유형 및 예시

인덱스는 구현 방식에 여러 종류가 있다. 그 중 중요하다고 생각하는 유형들을 정리해보았다.

예시는 Claude의 힘을 빌렸다.

B-트리 인덱스 (B-tree Index)

• 균형 잡힌 트리 구조로, 대부분의 RDBMS에서 기본적으로 사용된다.
• 범위 검색에 효율적이다.

Ex) MySQL

CREATE TABLE students (
    id INT PRIMARY KEY,
    name VARCHAR(50),
    email VARCHAR(100)
);

CREATE INDEX idx_student_name ON students(name);

해시 인덱스 (Hash Index)

• hash table을 사용하여 매우 빠른 검색이 가능하다.
• 동등 비교(=)에만 최적화되어 있고, 범위 검색이 불가능하다.
• 데이터가 정렬되지 않는다!

Ex) MySQL - MEMORY 엔진

CREATE TABLE student_session (
    student_id INT,
    session_token VARCHAR(100),
    INDEX USING HASH (student_id)
) ENGINE = MEMORY;

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

• 테이블 데이터를 인덱스 순서대로 물리적으로 정렬한다.
• 테이블당 하나만 생성 가능하다.
• 범위 검색에 매우 효율적이다.

Ex) SQL Server

CREATE TABLE student_grades (
    student_id INT PRIMARY KEY CLUSTERED,
    course_id INT,
    grade DECIMAL(3,2)
);

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

• 데이터와 별도로 저장된 인덱스 구조이다.
• 테이블당 여러 개 생성이 가능하다.

Ex) SQL Server

CREATE TABLE students (
    student_id INT PRIMARY KEY,
    name VARCHAR(100),
    email VARCHAR(100)
);

CREATE NONCLUSTERED INDEX idx_student_email ON students(email);

커버링 인덱스 (Covering Index)

• 쿼리에 필요한 모든 컬럼을 포함하는 인덱스이다.
• 더 많은 정보를 저장해야 하므로 일반 인덱스보다 크기가 크다.
• 디스크 읽기 작업이 줄어 성능이 향상된다는 장점이 있다.

Ex) MySQL

CREATE TABLE students (
    id INT PRIMARY KEY,
    name VARCHAR(100),
    grade VARCHAR(2)
);

CREATE INDEX idx_name_grade ON students(name, grade);

역인덱스 (Inverted Index)

• 문서의 내용을 키워드로 인덱싱한다.
• 전문 검색에 매우 효율적이다.(검색 엔진에서 많이 사용된다.)
• 대량의 텍스트 데이터 처리에 적합하다.

Ex) Elasticsearch

PUT /student_essays
{
  "mappings": {
    "properties": {
      "title": { "type": "text" },
      "content": { "type": "text" }
    }
  }
}

Function-based 인덱스 (함수 기반 인덱스)

• 컬럼 값에 함수나 표현식을 적용한 결과를 인덱싱한다.
• 복잡한 WHERE 절 조건에 좋다.

Ex) Oracle

CREATE TABLE students (
    id NUMBER PRIMARY KEY,
    first_name VARCHAR2(50),
    last_name VARCHAR2(50)
);

CREATE INDEX idx_full_name ON students(LOWER(first_name || ' ' || last_name));

💁🏻‍♀️ 인덱스를 사용하기 좋은 컬럼은?

1. 데이터 변경이 드문 컬럼. DML 작업이 자주 발생하지 않는 컬럼
2. JOIN, WHERE, ORDER BY 같이 쿼리 성능에 중요한 영향을 미치는 컬럼
ORDER BY 절에 사용되는 열은 데이터 페이지가 자동 정렬됐기 때문에 클러스터형 인덱스가 유리하다.

3. 데이터의 중복도가 낮은, 카디널리티가 높은 컬럼
예를 들어, 대학생 데이터베이스에서 '학년' 컬럼에 인덱스를 생성한다고 가정해보자. 학년은 보통 1학년부터 4학년까지로 제한되므로 이는 매우 낮은 카디널리티를 가질 것이다. 따라서 3학년 학생을 찾는 쿼리는 전체 데이터의 약 25%이상을 검색해야 할 수 있고 이는 인덱스의 효과를 크게 볼 수 없을 것이다.

4. 선택도(Selectivity)가 높은, 즉 쿼리 결과가 전체 데이터의 10-15% 이하를 반환하는 컬럼

🛠️ 인덱스를 어떻게 만들어야 효과적일까?

WHERE A = 20 AND b = 50 같은 조건인 쿼리에서 A만 Index가 생성되어있다면 결국 B가 만족하는지 찾기 위해 A를 만족하는 결과에서 Full Scan을 해야 한다.

따라서, 이 쿼리가 효과적이려면 A와 B를 묶은 인덱스가 필요하다! 이렇게 2개의 컬럼을 같이 묶은 인덱스를 multicolumn index, composite index 라고 부른다. 하나의 컬럼만을 기준으로 생성된 인덱스는 단일 컬럼 인덱스, Single-Column Index라고 부른다.

이때, 재밌는 점은 (A, B)와 (B, A)는 서로 다른 인덱스라는 것이다! 🫢 인덱스가 ⭐️정렬된⭐️ 목록이라는 것을 다시 떠올려보자. A와 B를 묶어서 인덱스로 만들때 어떤 기준으로 정렬을 할까? 바로 왼쪽부터이다. 따라서 (A, B) 는 A를 기준으로 , (B, A) 는 B를 기준으로 정렬한다. 따라서 어떤 순서로 배치시키냐에 따라서 속도가 정말 차이가 많이 날 수 있다. 이런 다중 컬럼을 인덱싱할 때에는 카디널리티가 높은 컬럼에서 낮은 컬럼 순으로 인덱싱해야 좋은 효과를 볼 수 있다.

만약 (A, B) 인덱스가 생성되어 있는 상태에서 WHERE B = 20 조건인 쿼리를 실행하면 이 인덱스를 사용할까? 대부분 아니다. 😅 해당 인덱스는 A를 기준으로 정렬되어 있는 상태이고 B는 정렬되어있지 않다. 따라서 이 인덱스를 거의 안쓰거나 결국 Full Scan을 해야 한다.

또한, 우리가 명시하지 않으면 주로 DBMS의 optimizer(SQL을 수행할 때 가장 효율적인 방법을 찾아 최적의 처리 경로를 생성해 주는 DBMS의 핵심 엔진)가 실행 시 사용 가능한 인덱스 중 가장 효율적인 것을 선택하는데, 특정 인덱스를 사용하도록 명시하고 싶다면 MySQL 기준 아래와 같이 인덱스를 지정해주면 된다. 그런데 이렇게 지정해도 optimizer가 더 효율적인게 있다고 생각하면 그것을 쓴다고? 한다. 이건 강의에서 들은 내용인데, 이럴거면 강제 옵션이 왜 있는지 의문이라 이 부분에 대해서는 찾아봐야겠다.

• 강제로 지정하기
  SELECT * FROM player FORCE INDEX (backnumber_idx) WHERE backnumber = 7;
  

• 권장하는 느낌으로 지정하기
  SELECT * FROM player USE INDEX (backnumber_idx) WHERE backnumber = 7;
  

🍎 퀴즈

각 쿼리문은 어떤 인덱스를 사용할까요? index 순서대로 1 2 3 중에 고르시오.

출처: https://youtu.be/IMDH4iAQ6zM?si=RSs2N0hGzIkkVhkG

• 문제 1 정답
  3 (team_id를 기준으로 정렬되어 있기 때문에)
  
• 문제 2 정답
  3
  
• 문제 3 정답
  만약 사용한다면 3인데, 3을 사용한다고 해도 team_id를 기준으로 정렬되어 있기 때문에 성능이 정말 안좋을 것이다.
  
• 문제 4 정답
  만약 사용한다면 3이다. 하지만 이 조건은 OR 이기 때문에, team_id에 관해서만 3을 사용하겠지만 backnumber 조건을 만족시키기 위해서는 결국 full scan을 해야할 수 있다.
  

🔍 index를 만들고 정말 빠른지 검증해보자!

EXPLAIN 키워드를 사용하여 해당 쿼리에 대한 실행 정보를 알아보자.
실습에 앞서, 인덱스의 효과를 느끼기 위해 500만건의 데이터를 미리 넣어두었다. 😉

단일 인덱스

• 지정 검색

  SELECT * FROM User WHERE name = '최하은'; 을 실행했을 때의 결과다. 1.89초가 걸린 것을 볼 수 있다.

  

  EXPLAIN 을 통해 자세히 보니 type이 ALL이고 possible_keys, key, ref가 모두 NULL인 것을 볼 수 있다.
  
  

  이제, 인덱스를 만들어보자. 실습을 위해 각 컬럼마다 인덱스를 만들어줬다.
  

  인덱스를 사용하니 똑같은 쿼리를 실행했을때 1.89 ➡️ 0.27초로 줄어든 것을 볼 수 있다! 🫢
  

  EXPLAIN 을 통해 자세히 보니 type이 ref고 possible_keys, key, ref가 아까와는 달리 NULL이 아니다! 특히 possible_keys, key에는 아까 생성한 index 이름이 들어가있다. 이것들이 무엇을 의미하는 걸까?
  

type은 조인 유형이다. ref는 인덱스를 사용한 동등 비교로 접근함을 의미한다.
possible_keys는 사용 가능한 인덱스이다. 'idx_name'이 사용 가능했다고 보여주고 있다.
key는 실제 사용된 인덱스로, 현재 쿼리에서는 'idx_name'가 쓰였다. 이전에 생성한 인덱스가 잘 쓰이고 있는 것이다.
ref는 인덱스와 비교되는 열이나 상수이다. 즉, 인덱스를 어떤 값과 비교하여 검색하는지를 의미한다. 'const'는 상수 값과 비교됨을 의미한다.

위에서 계속해서 말했던 인덱스는 정렬되어 있다.(대부분)를 떠올려보자! 이 특징은 특히 ORDER BY 같은 조건에 더욱 효과적이라고 했던 만큼, 정말 그런지 직접 해보자.

• 정렬
  SELECT * FROM User ORDER BY money DESC LIMIT 100;
  3.55초가 걸리던 조회가 0초 대로 줄어든 것을 볼 수 있다! 역시 정렬에 더 강력한 기능을 보여주는 것 같다.
  이때 type이 아까 지정 검색 경우와는 다르다. ref에서 index로 변경되었는데, 이는 인덱스 전체를 스캔한다는 의미이다. 그리고 인덱스를 생성했는데도 possible_keys도 NULL인 것을 볼 수 있는데, 특정 WHERE 조건이 없어 사용 가능한 인덱스가 지정되지 않은 것이다.
  

복합 인덱스

2개 이상의 컬럼을 묶어서 인덱스를 생성해보자. 우선 아래 쿼리를 실행하였고, 2.20초가 걸렸다.

SELECT * FROM User WHERE money BETWEEN 1000000 AND 2000000 AND start_date >= '2020-01-01';

INDEX idx_money_start_date ON User(money, start_date);

그리고 위 명령어를 통해 money와 start_date를 순서대로 한 인덱스를 생성하고 똑같은 쿼리를 실행했는데,,,,

더,,, 오래 걸린다?? 😯 일부러 효과를 보기 위해 컬럼 순서도 쿼리랑 똑같이 배치했는데 더 오래걸려서 당황스러웠다.

EXPLAIN을 통해 살펴봐도 인덱스를 잘 쓰고 있는 것을 볼 수 있는데,, 무엇이 문제일까? 싶어서 생각하다 머리속에 스치는 것이 있었다. 바로 SELECT * 로 모든 컬럼을 요청하고 있다는 것이었다.
정말 SELECT * 때문인지 궁금하여 이번에는 모든 컬럼을 요청하지 않고 커버링 인덱스(Covering Index)처럼 사용되도록 id, money, start_date를 요청했다.

1.92초에서 0.04초로 줄어들면서 인덱스가 효과적으로 사용된 것을 느낄 수 있었다!

인덱스에는 필요한 컬럼만 있고 나머지 컬럼은 테이블의 실제 데이터 페이지에 별도로 저장되어 있다. 즉, SELECT *는 모든 컬럼을 다 읽어야 하므로, 인덱스를 통해 조건에 맞는 row 위치(레코드 주소)를 찾으면 그 다음에 테이블에서 실제 데이터를 랜덤 I/O로 다 읽어와야 하는 것이다. 이 작업 때문에 원하는만큼 빠르지 않은 것이다.

실습을 하면서 느낀 것은 내가 생각한 인덱스의 동작이 예상과는 다른 경우가 많았다는 것이다. 또한, 복합 인덱스에서 배치 순서 뿐만 아니라 어떤 컬럼을 요청하느냐에 따라서 적절한 인덱스를 생성하는 것이 아주 중요하겠다는 생각이 들었다.

🍀 정리

출처: https://youtu.be/NZgfYbAmge8?si=O7hFw5B7PwdlWiCg

인덱스는 만능이 아니다.

검색 성능을 높이기 위해서는 인덱스를 사용할 수 있다. 하지만, Index를 아무렇게나 만든다고 성능이 향상되지 않는다. 어떻게 배치해야 효과적일지 고민하고, 쿼리에 따라 적절하게 컬럼 순서를 고려하여 생성해야 한다. 검색 속도가 빠르다고 무분별하게 만들면 안된다!

이미 데이터가 많이 들어있는 테이블에 인덱스를 생성하는 경우 시간이 몇 분 이상 소요될 수 있고 DB 성능에 안좋은 영향을 줄 수 있다. 만약 실제 운영중인 서버에서 수천만건의 데이터에 대한 인덱스를 만든다면..? 최악의 경우 서비스가 잠깐 멈출 수 있다. 😇 

인덱스를 만든다고 무조건 빨라지는 것도 아니다. table에 데이터가 조금 있을 때, 조회하려는 데이터가 테이블의 상당 부분을 차지할 때는 오히려 full scan이 더 빠를 수 있다. 테이블당 4~5개 권장이라고 한다.

하지만, 잘 생성하고 잘만 쓰면 인덱스은 매우 효과적이다. 인덱스가 정렬되어 있다는 특징은 검색 속도를 향상시킬 뿐만 아니라 시스템 성능도 향상시킬 수 있다. 또한, 조인 조건에 쓰이는 컬럼에 인덱스가 있으면 매칭되는 데이터를 훨씬 빨리 찾을 수 있어서 전체적인 쿼리 속도가 올라갈 수 있다.

참고 자료

index가 뭔지 설명해보세요 (개발면접시간)

[MySQL] B-Tree 인덱스

SQL indexing best practices | How to make your database FASTER!

DB 인덱스(DB index) !! 핵심만 모아서 설명합니다 !! (31분이 아깝지 않을 겁니다)

[DB] 인덱스(Index)로 DB 검색 속도를 높여보자

SQL Indexes - Definition, Examples, and Tips

데이터베이스 인덱스(Index) 3분 요약 정리

[MYSQL] 📚 인덱스(index) 핵심 설계 & 사용 문법 💯 총정리