DB 인덱스를 효과적으로 설정하는 방법 - 고려해야 할 4가지

DB에는 여러 개의 테이블이 존재하고, 그 테이블에는 다양한 수의 데이터가 쌓입니다.
만약 천 만 개의 데이터가 쌓였다고 가정할 때, 여러 조건을 조합해서 데이터를 조회하려면 로딩 시간이 아주 오래 걸리겠지요?
이 때 인덱스(index)를 설정하면 속도가 빨라집니다.

인덱스의 개념

“‘찾아보기’로 일컬어지는 색인(索引)은 책에서 중요한 단어나 항목, 고유명사 등을 쉽게 찾을 수 있도록, 그것들을 일정 순서에 따라 배열한 목록이다.” - [표정훈의 호모부커스]<83>색인

인덱스는 테이블의 동작 속도를 높여주는 자료 구조입니다.
인덱스로 데이터의 위치를 빠르게 찾아주는 역할을 합니다.

쉬운 예시로, 책 뒷 편에 ‘색인’이 바로 이 인덱스의 역할이라고 보면 됩니다.
색인을 통해 원하는 키워드에 대한 페이지로 바로 이동할 수 있지요.

책의 색인이 여러 페이지에 기재되어 있는 것처럼
DB의 인덱스도 데이터베이스 메모리에 일정 공간을 사용해 저장이 되고,
데이터를 조회할 때 소모되는 메모리를 효율적으로 사용하게 합니다.

단, 인덱스가 잘 설정되었을 경우에요!

인덱스의 특징

인덱스는 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의 경우, 새로운 데이터가 추가되면서 → 기존에 인덱스 페이지에 저장되어 있던 탐색 위치가 수정되어야 하므로 효율이 좋지 않습니다.

즉, 인덱스는 원하는 데이터를 빠르게 찾을 때 빛을 발합니다.

그럼 어떤 컬럼에 인덱스를 설정하는게 좋을까?

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

인덱스는 컬럼을 정해서 설정하는 것이므로 후보 컬럼의 특징을 잘 파악해야 합니다.
아래 4가지 기준을 사용하면 효율적으로 인덱스를 설정할 수 있습니다.

• 카디널리티 (Cardinality)
• 선택도 (Selectivity)
• 활용도
• 중복도

카디널리티 (Cardinality)

✔️ 카디널리티가 높을 수록 인덱스 설정에 좋은 컬럼입니다.
= 한 컬럼이 갖고 있는 값의 중복 정도가 낮을 수록 좋습니다.

컬럼에 사용되는 값의 다양성 정도, 즉 중복 수치를 나타내는 지표입니다.
후보 컬럼에 따라 상대적으로 중복 정도가 낮다, 혹은 높다로 표현됩니다.

예를 들어, 10개 rows를 가지는 ‘학생’ 테이블에 ‘학번’과 ‘이름’ 컬럼이 있다고 해봅시다.

• ‘학번’은 학생마다 부여 받으므로 10개 값 모두 고유합니다.
  • 중복 정도가 낮으므로 카디널리티가 높습니다.
• ‘이름’은 동명이인이 있을 수 있으니 1~10개 사이의 값을 가집니다.
  • 중복 정도가 ‘학번’에 비해 높으므로 카디널리티가 낮다고 표현할 수 있습니다.

선택도 (Selectivity)

✔️ 선택도가 낮을 수록 인덱스 설정에 좋은 컬럼입니다.
5~10% 정도가 적당합니다.

데이터에서 특정 값을 얼마나 잘 선택할 수 있는지에 대한 지표입니다.
선택도는 아래와 같이 계산합니다.

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

예를 들어, 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 절에 자주 활용되는지를 판단하면 됩니다.

중복도

✔️ 중복도가 없을 수록 인덱스 설정에 좋은 컬럼입니다.

중복 인덱스 여부에 대한 값입니다.

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

인덱스도 속성을 가집니다.
인덱스는 테이블 형태로 생성되므로, 속성을 컬럼으로 관리합니다.

(참고) 주요 인덱스 컬럼
- Table: The name of the table.
- Non_unique: 0 if the index cannot contain duplicates, 1 if it can.
- Key_name: The name of the index. If the index is the primary key, the name is always PRIMARY.
- Seq_in_index: The column sequence number in the index, starting with 1.
- Column_name: The column name.
- Collation: How the column is sorted in the index. This can have values A (ascending) or NULL (not sorted).
- Cardinality: An estimate of the number of unique values in the index.
- Index_type: The index method used (BTREE, FULLTEXT, HASH, RTREE)

출처: https://www.fun-coding.org/mysql_advanced5.html

이 속성이 다를 때 같은 컬럼에 대해 중복으로 인덱스 설정이 가능합니다.
같은 컬럼에 대해 중복 인덱스가 설정되어 있다고 하더라도 SQL 자체 연산이 빠른 쪽으로 데이터를 조회합니다만,
인덱스도 결국 메모리의 일부이므로 필요 없는 항목은 삭제하는게 좋겠지요?

인덱스 설정 기준 - 정리!

위 기준을 활용해서 효율적인 인덱스를 설정해보세요! 😊