[MySQL] 인덱스(Index)_기본 ⭐️⭐️⭐️

코린이·2025년 8월 25일
Indexmysql인덱스

MySQL

목록 보기
10/23

✅ 인덱스 사용 이유

인덱스는 DB에서 대용량의 데이터(수만 건 이상의 데이터)를 빠르게 읽기(SELECT) 위해 사용되는 기술이다.

※ 참고
인덱스를 사용하면 테이블의 Full Scan을 막아 읽기(SELECT) 성능을 높여주지만, 그 외 쓰기(INSERT, UPDATE, DELETE) 성능이 낮아진다.

인덱스 사용 없이 테이블을 Full Scan 하는 행위는 높은 비용을 요구하는 작업이다. 때문에 빈번하게 사용되는 쿼리문에서는 Full Scan이 발생하지 않도록 인덱스를 잘 설계해야 한다.

✅ 인덱스 구조

인덱스는 테이블의 특정 컬럼 데이터를 기반으로 생성되는 별도의 자료 구조다.

인덱스의 가장 큰 특징으로는 인덱스 컬럼 데이터(키값)들은 항상 정렬되어 있다는 점이다.

실제 인덱스는 트리 자료 구조(B-Tree 기반)로 되어 있다.

트리 구조 내부는 최상위에 하나의 root node가 있고, 최하위에는 leaf node가 있으며, 그 사이에는 branch node가 있다.

각 노드는 별도의 주소 값(PK, 참조 값 등)을 가지고 있으며, 마지막 리프 노드는 실제 데이터 파일의 레코드 주소 값(PK, 참조 값 등)을 가지고 있다.

✅ 인덱스 생성/확인/삭제

📌 인덱스 생성

# 인덱스 생성
CREATE INDEX <인덱스명> ON <테이블명>(<컬럼1>, <컬럼2>, ...);

📌 인덱스 확인

# 인덱스 확인
SHOW INDEX FROM <테이블명>;

인덱스 조회 쿼리를 실행하면 아래와 같은 테이블 내 인덱스 정보를 확인할 수 있다.

※ 참고
MySQL에서 PK, FK, UNIQUE으로 지정된 컬럼은 인덱스가 자동 생성된다.

mysql> SHOW INDEX FROM ITEMS;
+-------+------------+------------------+--------------+----------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+------------+
| Table | Non_unique | Key_name         | Seq_in_index | Column_name    | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment | Visible | Expression |
+-------+------------+------------------+--------------+----------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+------------+
| items |          0 | PRIMARY          |            1 | item_id        | A         |          25 |     NULL |   NULL |      | BTREE      |         |               | YES     | NULL       |
| items |          1 | fk_items_sellers |            1 | seller_id      | A         |          10 |     NULL |   NULL |      | BTREE      |         |               | YES     | NULL       |
| items |          1 | idx_items_test   |            1 | category       | A         |           5 |     NULL |   NULL |      | BTREE      |         |               | YES     | NULL       |
| items |          1 | idx_items_test   |            2 | is_active      | A         |           9 |     NULL |   NULL |      | BTREE      |         |               | YES     | NULL       |
| items |          1 | idx_items_test   |            3 | stock_quantity | A         |          24 |     NULL |   NULL |      | BTREE      |         |               | YES     | NULL       |
+-------+------------+------------------+--------------+----------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+------------+
  • Non_unique : 인덱스 중복 허용 여부(0 : 중복 불가, 1 : 중복 가능)
  • Key_name : 인덱스 이름
  • Seq_in_index : 해당 인덱스의 컬럼 순번
  • Column_name : 해당 인덱스의 컬럼
  • Collation : 인덱스 정렬 방식(A : 오름차순, D : 내림차순)
  • Cardinality : 컬럼의 고유 값 수치 (해당 값이 높을수록 성능에 유리함)
  • Index_type : 인덱스 자료 구조(MySQL InnoDB에서는 B-TREE를 기본으로 함)

📌 인덱스 삭제

DROP INDEX <인덱스명> ON <테이블명>;

✅ 인덱스 사용 여부 확인

DB에서 쿼리문의 실행은 옵티마이저에 의해 최적의 실행 계획으로 실행된다. 이는 쿼리문의 인덱스 사용 여부 또한 옵티마이저가 결정한다는 의미로 해석할 수 있다.

특정 쿼리문이 정상적으로 인덱스를 사용하는지 확인하기 위해서는 EXPLAIN 명령어를 사용해야 한다. 해당 명령어를 통해 옵티마이저의 실행 계획(실행 결과 X)을 확인 할 수 있다.

인덱스가 아래와 같은 때 옵티마이저의 실행 계획은 다음과 같다.
idx_items_test(category, is_active, stock_quantity)

※ 참고
실행 계획은 예측 정보라는 점을 명심해야 한다. (실제 실행 결과와 상이할 수 있다)

# 인덱스 사용 X (Full Scan)
mysql> EXPLAIN
    -> SELECT * FROM ITEMS WHERE price > 50000;
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------------+
| id | select_type | table | partitions | type | possible_keys | key  | key_len | ref  | rows | filtered | Extra       |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------------+
|  1 | SIMPLE      | ITEMS | NULL       | ALL  | NULL          | NULL | NULL    | NULL |   25 |    33.33 | Using where |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------------+
# 인덱스 사용 O
mysql> EXPLAIN
    -> SELECT * FROM ITEMS WHERE category = '전자기기';
+----+-------------+-------+------------+------+----------------+----------------+---------+-------+------+----------+-------+
| id | select_type | table | partitions | type | possible_keys  | key            | key_len | ref   | rows | filtered | Extra |
+----+-------------+-------+------------+------+----------------+----------------+---------+-------+------+----------+-------+
|  1 | SIMPLE      | ITEMS | NULL       | ref  | idx_items_test | idx_items_test | 402     | const |   10 |   100.00 | NULL  |
+----+-------------+-------+------------+------+----------------+----------------+---------+-------+------+----------+-------+
  • table : 참조 테이블
  • type : 조인/테이블 접근 방식
    • ALL -> Full Scan
    • ref -> = 또는 JOIN에서 인덱스 사용
    • range -> 범위 검색에서 인덱스 사용
  • possible_keys : 인덱스 사용 후보
  • key : 실행 계획에서 최종적으로 선택된 인덱스
  • rows : 옵티마이저가 테이블에서 읽어야 할 예상 레코드 수
  • filtered : 조건(WHERE)을 적용했을 때 남을 것으로 예상되는 레코드의 비율 (%)
  • Extra : 추가 실행 정보
    • Using where -> 추가 조건(WHERE) 작업 필요
    • Using index -> 커버링 인덱스 사용
    • Using index condition -> 인덱스만으로 조건(WHERE)에 만족하는 데이터를 가져옴
    • Using filesort -> 추가 정렬 작업 필요

※ 참고
rows 값이 작을 수록, filtered 값이 클 수록 성능상 유리하다.

📌 인덱스 사용 상황

인덱스는 크게 아래 3가지 상황에서 사용된다.

  • 동등 비교 (=, IN)

  • 범위 비교 (BETWEEN, >, <, LIKE)

    • LIKE 절은 와일드 카드(%)가 검색어 뒤쪽에 위치해야 한다. 이는 인덱스 키값이 정렬되어 저장되기 때문이다.

      와일드 카드(%) 문자가 앞에 있는 경우 정렬된 인덱스 키값과 매칭을 할 수 없어서 Full Scan 방식으로 작업을 처리한다.

      만약 %검색_단어% 처럼 중간에 포함된 검색 단어를 기반으로 조회 성능을 높이고 싶다면, DB에서 제공하는 전문 검색(Full-Text Search) 기능 또는 검색 엔진(Elasticsearch 등)을 별도로 사용해야 한다.

  • 정렬 (ORDER BY)

    • DB에서 정렬은 큰 비용이 드는 작업이다. 이러한 성능 문제는 인덱스를 적절하게 사용하여 정렬 작업(filesort)을 회피할 수 있다.

      ※ 참고 1
      인덱스를 사용하지 않은 쿼리문의 결과는 PK 기준(클러스터링 되어 정렬)으로 결과를 반환 하지만, 인덱스를 사용한 쿼리문의 결과는 정렬된 인덱스 키값을 기준으로 결과를 반환한다.

      ※ 참고 2

      인덱스를 생성할 때 인덱스 키값을 내림차순으로 생성할 수 있다.

      CREATE INDEX <인덱스명> ON <테이블명>(<컬럼1> DESC);

      사실 오름차순으로 생성된 인덱스는 내림차순 정렬 쿼리를 실행 할 때 MySQL이 이를 역방향으로 자동 스캔해 준다.

      또한, 역방향 스캔은 정방향 스캔 보다 성능이 조금 떨어 지지만 큰 차이가 없다.

      ※ 참고 3
      하지만 여러 컬럼에 대해 오름차순과 내림차순이 혼합된 복잡한 쿼리에서 내림차순이 필요한 인덱스 키값을 DESC로 설정하고, 인덱스 정렬 순서에 맞게 인덱스를 생성하면 쿼리의 성능을 극적으로 높일 수 있다.

✅ 옵티마이저와 인덱스

인덱스 사용이 항상 높은 성능을 보이는 건 아니다.

옵티마이저가 쿼리문의 실행 계획을 만들 때 인덱스 사용이 비효율적이라 판단하면, 인덱스 사용을 포기하고 Table Full Scan을 실행 한다.

※ 참고
인덱스 사용 시 많은 랜덤 I/O가 발생하면 옵티마이저는 순차 I/O 방식(Full Scan)으로 데이터를 읽는다.

📌 인덱스 손익분기점

옵티마이저는 인덱스 손익분기점을 기준으로 인덱스 사용 여부를 판단한다.

[손익분기점]
인덱스 사용(인덱스 키값 스캔 + 참조 값 기반으로 데이터 레코드 조회) VS Table Full Scan(테이블의 전체 데이터 레코드 조회)

※ 참고1
일반적으로 테이블 전체 데이터의 20~25% 이상을 조회하는 쿼리문은 Full Scan 방식이 더 효율적일 수 있다.

※ 참고2
여러 인덱스 후보 중 Full Scan이 효율적이라고 판단하면 모든 인덱스 사용을 포기할 수 있다.

※ 참고3
20~25% 기준을 떠나 데이터양이 적으면, 옵티마이저는 Full Scan을 선택할 수 있다.

✅ 커버링 인덱스

쿼리문에서 사용하는 모든 컬럼이 인덱스에 포함되면 이를 커버링 인덱스라 부른다.

일반 인덱스는 참조 값을 기반으로 테이블의 레코드를 조회(랜덤 I/O)한다. 하지만 커버링 인덱스는 인덱스 자료구조 내부에 모든 컬럼 데이터가 존재하기 때문에 참조 값을 기반으로 한 레코드 조회 작업이 필요 없다.

이처럼 테이블 레코드를 조회하는 작업을 생략할 수 있기 때문에 극한으로 효율을 높일 수 있다.

※ 참고
커버링 인덱스는 압도적인 읽기 성능을 자랑하지만, 그만큼 많은 양의 컬럼을 인덱스로 지정해야 하므로 데이터 저장 공간을 기준으로 생각하면 좋은 방법은 아니다.

또한 많은 컬럼을 인덱스로 설정하기 때문에 그만큼 쓰기 성능이 많이 떨어진다.

✅ 복합 인덱스(다중 컬럼 인덱스)

두 개 이상의 컬럼을 조합하여 만든 인덱스를 복합 인덱스라 부른다.
복합 인덱스에서 가장 중요한 부분은 컬럼의 순서다.

📌 인덱스 컬럼 순서 ⭐️

인덱스 생성 시 컬럼의 순서에 따라 성능이 천차만별로 나뉜다. 때문에 복합 인덱스의 컬럼 순서는 정말 중요하다.

아래와 같은 인덱스가 있다고 가정하면 a -> b -> c 순서로 인덱스 키 값을 정렬한다.

즉, 가장 첫 번째 왼쪽 컬럼에 의해 인덱스 키 값의 정렬 구조가 결정된다.

idx_tmp(a, b, c)

📌 인덱스 왼쪽 접두어 규칙 ⭐️⭐️

복합 인덱스의 자료구조는 가장 왼쪽 컬럼을 기준으로 정렬된다. 이러한 이유로 첫 번째 인덱스 키값이 아닌 두 번째부터의 인덱스 키값만으론 인덱스를 사용할 수 없다.

※ 참고
첫 번째 인덱스 키값을 제외한 두 번째 부터의 인덱스 키값은 정렬된 상태라고 볼 수 없어서 인덱스로 사용할 수 없다. (Full Scan 발생)

즉, 첫 번째 인덱스 키값을 무시하면 인덱스 정렬 구조가 깨진다.

하지만 커버링 인덱스의 경우 첫 번째 인덱스 키값이 없어도 부분적으로 인덱스를 사용할 수 있다.

📌 인덱스 설계 대원칙 ⭐️⭐️⭐️

아래 순서를 기반으로 복합인덱스를 사용하면 높은 성능으로 인덱스를 사용할 수 있다.

1. 인덱스 컬럼은 순서대로 사용

첫 번째 인덱스 컬럼을 무시하고 두 번째 인덱스 컬럼부터 사용하면 Full Scan이 발생한다.

모든 인덱스는 첫 번째 컬럼을 기준으로 정렬하기 때문이다.(단, 커버링 인덱스에서는 부분적으로 인덱스를 사용)

2. 동등(=, IN) 조건은 앞으로, 범위(<,>, BETWEEN) 조건은 뒤로

첫 번째 인덱스 컬럼의 범위 조건은 인덱스를 사용하여 데이터를 스캔할 수 있다.

이후 두 번째 컬럼의 동등 비교로 데이터를 스캔할 때는 인덱스 키값이 정렬되어 있지 않기 때문에 두 번째 조건의 인덱스 값을 Full Scan 한다.

※ 참고
범위 조건이 앞에 있다고 해서 인덱스를 못 사용하는 것은 아니다.
하지만 비효율적일 뿐이다.

범위 조건 이후부터는 인덱스 키값의 정렬 구조가 깨지기 때문에 인덱스를 사용할 수 없다.

3. 정렬(ORDER BY) 순서도 인덱스 컬럼 순서대로 사용

복합 인덱스 컬럼은 각 컬럼의 순서대로 정렬되어 있기 때문에 쿼리문의 정렬을 인덱스 순서와 맞게 작성하면 정렬 작업(filesort)을 회피할 수 있다.

✅ 인덱스 컬럼 후보

  • 카디널리티가 높은 컬럼

  • 조건(WHERE)에 자주 사용되는 컬럼

  • JOIN 조건(ON)으로 사용되는 컬럼

※ 참고
MySQL에서 PK, FK, UNIQUE으로 지정된 컬럼은 인덱스가 자동 생성된다.

  • 정렬(ORDER BY)에 자주 사용되는 컬럼

✅ 인덱스 단점

인덱스의 단점으로는 아래와 같다.

  1. 저장 공간
    인덱스도 데이터이기 때문에 각 테이블의 10~20%의 추가 공간이 발생한다.

  2. 쓰기(INSERT, UPDATE, DELETE) 성능이 하락
    인덱스가 있는 테이블은 정렬된 인덱스 구조를 유지하기 위해 쓰기 작업 시 속도가 떨어진다.

    ※ 참고
    인덱스가 있는 테이블에서 UPDATE는 내부적으로 DELETE -> INSERT 과정으로 진행 되기 때문에 가장 비효율적인 작업으로 전락된다.

profile
코린이
다음 포스트

[MySQL] MySQL 옵티마이저

0개의 댓글