인덱스2

옵티마이저와 인덱스 선택

컬럼에 인덱스를 생성하면, 해당 컬럼을 조건으로 사용하는 모든 WHERE 절 성능이 향상될 것이라고 기대하기 쉽다. 하지만 항상 그렇지는 않다. 데이터베이스의 옵티마이저(Optimizer) 는 쿼리를 실행하기 전에 여러 실행 가능한 방법을 평가하고, 그중 가장 비용이 적게 드는, 즉 가장 효율적이라고 판단되는 방법을 선택한다.

이 과정에서 옵티마이저는 인덱스를 사용하는 것이 오히려 비효율적이라고 판단하면, 인덱스가 존재하더라도 과감히 포기하고 테이블 전체를 스캔(Full Table Scan) 하는 방법을 선택할 수 있다.

인덱스 손익분기점

옵티마이저가 인덱스 사용 여부를 결정하는 핵심 기준이 바로 '손익분기점' 이다. 여기서 손익분기점이란, 인덱스를 통해 데이터를 읽는 비용이 테이블 전체를 직접 읽는 비용보다 높아지는 지점을 의미한다.

  • 인덱스를 사용하는 비용 : 인덱스 탐색 비용 + 인덱스에서 찾은 주소로 테이블에 접근하는 비용(랜덤 I/O)

  • 풀 테이블 스캔 비용 : 테이블 전체를 순차적으로 읽는 비용(순차 I/O)

일반적으로 전체 데이터의 약 20~25% 이상을 조회해야 하는 쿼리는 인덱스를 통해 테이블의 각 행에 개별적으로 접근하는 것보다, 차라리 테이블 전체를 순차적으로 스캔하는 것이 더 효율적이라고 알려져 있다. 즉, 조회하려는 데이터의 양이 손익분기점을 넘어가면 옵티마이저는 인덱스 사용을 포기한다.

왜 랜덤 I/O 가 더 느릴까?

랜덤 I/O가 순차 I/O보다 느린 이유는 데이터를 찾는 과정에서 발생하는 시간 때문이다. 이를 HDD, SSD 디스크를 예로 들어 책 읽기에 비유해보자.

  • 순차 I/O (Sequential I/O) 책을 1페이지부터 순서대로 읽기

    • 데이터가 저장된 위치를 한 번 찾으면, 그 이후로는 순서대로 쭉 읽기만 하면 된다.

    • HDD의 경우 디스크의 헤드가 거의 움직이지 않고 연속된 데이터를 읽어오므로 작업이 매우 빠르고 효율적이다.

    • SSD의 경우 "여기서부터 100개 읽어와" 라는 하나의 큰 명령으로 처리할 수 있다.

  • 랜덤 I/O (Random I/O) 책의 여러 페이지를 순서 없이 찾아 읽기

    • 5페이지를 읽은 후 200페이지를 읽고, 다시 45페이지를 읽는 것과 같다.

    • HDD의 경우 읽어야 할 데이터가 디스크의 여러 곳에 흩어져 있어, 데이터를 읽을 때마다 디스크 헤드가 물리적으로 해당 위치까지 이동해야 한다. 이렇게 데이터의 위치를 찾는 데 걸리는 시간(탐색 시간, Seek Time)이 추가되기 때문에 순차 I/O에 비해 느리다.

    • SSD의 경우 찾아야 하는 데이터가 100개라면 "이거 읽어와", "저거 읽어와" 라는 100개의 작은 명령을 각각 처리해야 한다. 작은 명령을 여러 번 처리하는 것은 SSD 컨트롤러에 더 많은 오버헤드(부하)를 준다.

데이터베이스에서 인덱스를 통해 테이블의 여러 행에 접근하는 것은, 인덱스에 저장된 주소에 따라 디스크의 여러 위치를 오가야 하는 랜덤 I/O를 유발할 수 있다. 반면, 테이블 전체를 스캔하는 것은 처음부터 끝까지 데이터를 읽는 순차 I/O에 해당한다.

이 때문에 조회할 데이터가 아주 많을 경우(전체 데이터의 약 20~25% 이상을 조회해야 하는 쿼리), 여러 번의 랜덤 I/O를 수행하는 것보다 한 번의 순차 I/O가 더 빠를 수 있다.

이처럼 인덱스는 만능이 아니다. WHERE 절에 인덱스가 걸린 컬럼을 사용하더라도, 조회하려는 데이터의 범위가 너무 넓어 손익분기점을 넘어가면 옵티마이저는 인덱스를 사용하지 않을 수 있다. 따라서 쿼리 튜닝을 할 때는 EXPLAIN 을 통해 옵티마이저가 실제로 인덱스를 사용하고 있는지 반드시 확인하는 습관을 들여야 한다.

여러 인덱스가 있다면?

선택할 수 있는 인덱스 후보가 여러 개 있다면 옵티마이저는 그 중에서 가장 효율적으로 작동하는 인덱스를 선택한다. 물론 이 경우에도 풀 테이블 스캔이 가장 효율적이라고 판단하면 풀 테이블 스캔을 선택할 수 있다.

데이터가 많이 부족하다면?

데이터 자체가 많이 부족하다면 옵티마이저는 풀 테이블 스캔을 선택할 가능성이 있다.

테이블에 데이터가 몇 건 없다면, 테이블 전체를 순차적으로 읽는 비용이 인덱스를 탐색하고 테이블에 접근하는 비용보다 훨씬 저렴하기 때문이다.

커버링 인덱스

우리는 인덱스를 스캔하고 해당 테이블의 데이터에 접근하는 과정을 랜덤 I/O 로 불렀고, 이 과정이 비용을 발생시킨다고 알고 있다.

그렇다면 이 랜덤 I/O 를 없앨 수 있는 방법은 없을까?

커버링 인덱스란?

커버링 인덱스는 쿼리에 필요한 모든 컬럼을 포함하고 있는 인덱스를 말한다. '커버링'이라는 이름 그대로, 인덱스 하나가 쿼리의 요구사항 전체를 '덮는다'는 의미다.

데이터베이스 옵티마이저는 쿼리를 실행할 때, 만약 특정 인덱스가 SELECT, WHERE, ORDER BY, GROUP BY 절에 사용되는 모든 컬럼을 가지고 있다면, 원본 테이블에 전혀 접근하지 않고 오직 인덱스만을 읽어서 쿼리를 처리한다.

이는 디스크의 여러 곳을 오가는 비싼 랜덤 I/O 작업을 완전히 제거하고, 순차 I/O에 가까운 인덱스 스캔만으로 쿼리를 끝낼 수 있음을 의미한다. 당연히 성능은 비약적으로 향상된다.

커버링 인덱스 적용 전(일반 인덱스 사용)

이처럼 인덱스에 포함되지 않은 컬럼(item_name) 을 조회해야 하므로, 테이블 접근을 피할 수 없다. 참고로 MySQL 의 인덱스는 테이블의 기본 키(PK, item_id) 를 기본으로 한다.

따라서 idx_items_price 인덱스를 사용하는 경우 item_id, price 두 컬럼의 값은 인덱스에서 바로 조회할 수 있다.

커버링 인덱스 적용 - 인덱스 컬럼만 조회하는 경우

결론적으로, 이 실행 계획은 커버링 인덱스를 활용해 테이블 접근을 피했고(Using index), 인덱스 내에서 WHERE 절의 조건으로 필터링(Using where)을 수행한, 매우 효율적인 쿼리임을 보여준다.

커버링 인덱스 적용 - item_name 추가

새로운 idx_items_price_name 인덱스는 price 로 먼저 정렬되고, price 가 같다면 item_name 으로 다시 정렬된 구조를 가진다. 이제 이 인덱스는 쿼리에 필요한 price 와 item_name 정보를 모두 가지고 있다.

커버링 인덱스의 장단점

  • 장점

    • 압도적인 SELECT 성능 향상 : 테이블 접근을 위한 랜덤 I/O 를 제거하여 조회 성능을 극적으로 개선한다.

    • 특히 COUNT 쿼리 최적화 : SELECT COUNT(*) 와 같은 쿼리에서 테이블 전체가 아닌, 크기가 훨씬 작은 인덱스만 스캔하여 결과를 빠르게 반환할 수 있다.

  • 단점

    • 저장 공간 증가 : 인덱스는 원본 데이터와 별도의 저장 공간을 차지한다. 인덱스에 포함되는 컬럼이 많아질수록 인덱스의 크기도 커진다.

    • 쓰기 성능 저하 : INSERT, UPDATE, DELETE 작업 시, 원본 데이터 뿐 아니라 인덱스도 함께 수정해주어야 하기 때문에, 인덱스가 많고 복잡할 수록 성능이 저하된다.

언제 사용해야 할까?

커버링 인덱스는 만능 해결책이 아니며, 읽기 성능과 쓰기 성능 사이의 트레이드오프(trade-off)를 신중하게 고려해야 한다.

  • 조회(읽기)가 매우 빈번하고, 쓰기 작업은 상대적으로 적은 테이블에 적용하는 것이 가장 효과적이다.

  • SELECT 절에서 조회하는 컬럼의 개수가 적을 때 유리하다. SELECT * 처럼 모든 컬럼을 조회하는 쿼리는 커버링 인덱스의 이점을 누리기 어렵다. (모든 컬럼을 포함하는 인덱스를 만들 수는 있지만, 이는 사실상 테이블을 복제하는 것과 같아 매우 비효율적이다.)

  • 성능 저하가 발생하는 특정 쿼리를 튜닝하기 위한 '비장의 무기'로 사용하는 경우가 많다.

복합 인덱스1

실제 쇼핑몰 운영 환경에서는 여러 조건을 조합해서 데이터를 검색하는 경우가 훨씬 더 많다.

이런 다중 조건 쿼리의 성능을 최적화하기 위해서 사용하는 것이 바로 복합 인덱스(Composite Index) 또는 다중 컬럼 인덱스(Multi-column Index) 이다.

하지만 복합 인덱스를 제대로 사용하려면 한 가지 매우 중요한 규칙을 이해해야 한다. 바로 '컬럼의 순서'다. 인덱스를 어떤 컬럼 순서로 만드느냐에 따라 쿼리 성능이 하늘과 땅 차이로 달라질 수 있다.

왜 컬럼의 순서가 중요할까?

복합 인덱스의 동작 원리는 우리가 실생활에서 사용하는 '전화번호부' 나 '국어사전' 과 동일하다.

  • 전화번호부 : '성(Last Name)'으로 먼저 정렬된 후, 같은 성 안에서 '이름(First Name)'으로 다시 정렬된다.

  • 국어사전 : '첫 번째 글자'로 먼저 정렬된 후, 같은 첫 글자로 시작하는 단어들끼리 '두 번째 글자'로 다시 정렬된다.

items 테이블에 (category, price) 순서로 복합 인덱스를 만들었다고 상상해 보자. 이 인덱스는 내부적으로 다음과 같이 정렬된다.

  1. category 를 기준으로 먼저 정렬한다. ('도서', '생활용품', '전자기기', '패션', '헬스/뷰티' 순서)

  2. 같은 category 내에서는 price 를 기준으로 다시 정렬한다.

이 구조를 보면 왜 컬럼 순서가 중요한지 감이 올 것이다.

예를 들면,

  • category 로 검색: 매우 효율적이다. 인덱스의 앞부분만 보고 빠르게 찾을 수 있다. (예: '전자기기' 섹션으로 바

    로 점프)

  • category 와 price 로 검색: 역시 매우 효율적이다. '전자기기' 섹션을 찾은 뒤, 그 안에서 price 순으로 정렬된 데이터를 탐색하면 된다.

    • 전자기기' 안에서는 price 가 항상 정렬된 상태를 유지한다.

    • 각각의 카테고리 안에서는 price 가 항상 정렬된 상태를 유지한다.

    • 1차 정렬의 기준 안에서 2차 정렬은 항상 정렬된 상태를 유지한다.

  • price 만으로 검색: 매우 비효율적이다. 전화번호부에서 성은 모르고 이름 만으로 찾는 것과 같다. price 값은 각 category 섹션마다 흩어져 있기 때문에, 인덱스 전체를 다 훑어봐야 한다. 이런 경우 옵티마이저는 차라리 풀 테이블 스캔을 선택할 수도 있다.

이처럼 복합 인덱스는 첫 번째 컬럼을 기준으로 정렬된 상태에서만 제 역할을 할 수 있다. 이를 인덱스 왼쪽 접두어 규칙(Index Left-Prefix Rule)이라고 한다. 인덱스를 (A, B, C) 순서로 생성했다면, WHERE 조건에 다음과 같이 사용될 때 효율적이다.

복합 인덱스 대원칙

복합 인덱스를 설계하고 사용할 때는 다음 세 가지 대원칙을 반드시 기억하자!

  1. 인덱스는 순서대로 사용하라! (왼쪽 접두어 규칙)

  2. 등호(=) 조건은 앞으로, 범위 조건(<, >)은 뒤로!

  3. 정렬(ORDER BY)도 인덱스 순서를 따르라!

복합 인덱스 준비 과정

복합 인덱스 성공 예제1 : category 사용

복합 인덱스의 첫 번째 컬럼만 WHERE 절에 사용하는 경우다. 이는 인덱스 왼쪽 접두어 규칙을 가장 잘 활용하는 기본적인 예시이다.

"카테고리가 '전자기기' 인 모든 상품을 찾아보자."

복합 인덱스 성공 예제2 : category, price 함께 사용

"카테고리가 '전자기기'이면서, 가격이 정확히 120,000원인 상품을 찾아보자."

복합 인덱스 성공 예제3 : 복합 인덱스와 정렬

가장 주목해야 할 부분은 Extra 컬럼에 Using filesort 가 없다는 점이다! 이것이 어떻게 가능할까?

데이터베이스의 동작 과정을 따라가 보자.

  1. idx_items_category_price 인덱스를 사용해 category 가 '전자기기'인 섹션으로 빠르게 이동한다.

  2. 해당 섹션 내에서, price 가 100000 을 초과하는 첫 번째 데이터를 찾는다.

  3. 그 지점부터 '전자기기' 섹션이 끝날 때까지 인덱스를 순서대로 읽기만 하면 된다.

왜냐하면 인덱스의 '전자기기' 섹션은 이미 price 순서로 완벽하게 정렬되어 있기 때문이다. 따라서 데이터베이스는 별도로 데이터를 모아 다시 정렬할 필요 없이, 인덱스를 읽는 즉시 ORDER BY price 조건을 만족하는 결과를 얻게 된다.

이처럼 WHERE 절의 조건과 ORDER BY 절의 조건이 복합 인덱스의 순서(category → price)와 일치하면, 데이터베이스는 가장 효율적인 방식으로 데이터를 찾고 정렬까지 한 번에 처리한다. filesort 를 피하는 것이야말로 복합 인덱스를 사용하는 핵심적인 이유 중 하나다.

복합 인덱스2

복합 인덱스 대원칙

복합 인덱스를 설계하고 사용할 때는 다음 세 가지 대원칙을 반드시 기억하자!

  1. 인덱스는 순서대로 사용하라! (왼쪽 접두어 규칙)

  2. 등호(=) 조건은 앞으로, 범위 조건(<, >)은 뒤로!

  3. 정렬(ORDER BY)도 인덱스 순서를 따르라!

복합 인덱스 실패 예제1 : 인덱스 순서 무시

이제부터 중요한 문제 상황이다. 복합 인덱스의 첫 번째 컬럼(category)을 건너뛰고, 두 번째 컬럼(price)만으로 데이터를 검색하면 어떻게 될까?

"카테고리와 상관없이 가격이 80,000원인 상품을 찾아보자."

풀테이블 스캔이 발생했다.

왜 이런 결과가 나왔을까? 인덱스 왼쪽 접두어 규칙 때문이다.

  • idx_items_category_price 인덱스는 category 로 먼저 정렬되어 있다.

  • price 가 80000 인 상품은 '전자기기' 카테고리에도 있을 수 있고, 만약 있다면 '패션' 카테고리에도 있을 수 있다.

  • 즉, price 값은 인덱스 전체에 흩어져 있다.

복합 인덱스 실패 예제2 : 범위 조건을 먼저 사용

복합 인덱스 활용에는 한 가지 더 중요한 제약 조건이 있다. 바로 선행 컬럼에 범위 조건(>, <, BETWEEN, LIKE %) 이 사용되면, 그 뒤에 오는 컬럼은 인덱스를 제대로 활용할 수 없다.

"카테고리명이 '패션' 이상인 상품들 중에서, 가격이 정확히 20,000원인 상품을 찾아줘."

실행 계획을 자세히 분석해 보자. type 이 range 이고, key 에 idx_items_category_price 가 사용되었으니 언뜻 보기에는 인덱스를 잘 사용한 것처럼 보인다. 하지만 여기서 주목해야 할 것은 filtered 컬럼의 값인 10.00% 와 Extra 컬럼의 Using index condition 이다.

  1. idx_items_category_price 인덱스를 사용해 category 가 '패션'인 위치를 찾는다. (>= 조건의 시작점)

  2. 거기서부터 인덱스의 끝까지 모든 데이터를 스캔한다. ('패션', '헬스/뷰티' 카테고리에 해당하는 모든 데이터)

  3. 스캔하는 각 레코드마다 price = 20000 조건을 만족하는지 하나하나 검사한다. (인덱스 사용이 아니라 직접 필터링 한다.)

이것이 바로 filtered: 10.00% 의 의미다. 옵티마이저는 category >= '패션' 조건으로 약 6개의 행을 찾을 것으로 예상하고(rows: 6), 그 중에서 price = 20000 조건을 만족하는 데이터는 10% 정도일 것이라고 예측한다.

데이터베이스는 category >= '패션' 범위를 인덱스의 category 컬럼를 통해 스캔하면서 6개의 행을 빠르게 찾았다. 하지만 가져온 각각의 데이터에 대해 price = 20000인지 일일이 확인하는 필터링 추가 작업을 해야만 한다.

이처럼 복합 인덱스에서 앞선 컬럼(`category` )에 범위 조건(>= )이 걸리는 순간, 데이터베이스는 더 이상 뒤따라오는 컬럼(price)의 정렬 순서를 활용할 수 없게 된다. category 가 '패션'일 때의 price 정렬과 category 가 '헬스/뷰티' 일 때의 price 정렬은 둘을 합치는 순간 정렬이 깨지기 때문이다.

이처럼 복합 인덱스에서 어떤 컬럼에 범위 검색을 사용하는 순간, 그 뒤에 오는 컬럼들은 인덱스의 정렬 효과를 제대로 누릴 수 없게 된다.

복합 인덱스3

범위 검색은 마지막에 한 번만 사용!

이러한 제약 때문에, 복합 인덱스를 설계할 때는 다음과 같은 순서를 따르는 것이 매우 중요하다.

"등호(=) 조건을 사용하는 컬럼을 앞에, 범위 조건을 사용하는 컬럼을 뒤에 둔다."

이처럼 가장 변별력 있는 등호(=) 조건을 먼저 처리해서 작업 범위를 최대한 좁히고, 그 다음에 범위 조건을 처리하는 것이 인덱스 설계의 핵심이다.

결론적으로, 복합 인덱스를 설계할 때는 어떤 쿼리가 주로 사용될지 예측하고, 해당 쿼리의 WHERE 절에 맞게 '등호 조건 컬럼 -> 범위 조건 컬럼' 순서로 구성하는 것이 성능 최적화의 지름길이다.

실무 팁 : IN 절 활용하기

범위 조건 때문에 두 번째 인덱스 컬럼을 활용하지 못하는 이 문제는, > 나 < 같은 범위 대신 IN 절을 사용함으로써 해결할 수 있는 경우가 많다.

MySQL 옵티마이저는 IN (...) 을 하나의 큰 범위로 취급하지 않고, 여러 개의 동등 비교(=) 조건의 묶음으로 인식하기 때문이다.

IN 절을 사용했을 때, MySQL 옵티마이저의 동작 방식은 다음과 같이 바뀐다.

  1. 옵티마이저는 WHERE category IN ('패션', '헬스/뷰티') 를 WHERE category = '패션' OR category = '헬스/뷰티' 와 동일하게 인식한다.

  2. 따라서 전체 쿼리는 내부적으로 (category = '패션' AND price = 20000) 또는 (category = '헬

    스/뷰티' AND price = 20000) 를 만족하는 데이터를 찾는 것으로 해석된다.

  3. idx_items_category_price 인덱스를 사용해 ('패션', 20000) 조합을 만족하는 데이터를 찾는다. (첫번째 동등 비교)

  4. 이어서 ('헬스/뷰티', 20000) 조합을 만족하는 데이터를 찾는다. (두 번째 동등 비교)

이렇게 작성한 IN 쿼리는 쉽게 비유하자면 다음과 같이 나누어 실행된다.

핵심은 범위 검색이 동등 비교(= )의 여러 묶음으로 바뀌었다는 점이다.

팁!

물론, IN 절에 들어가는 값의 개수가 너무 많아지면 오히려 성능이 저하될 수도 있으므로, 항상 EXPLAIN 을 통해 실제 실행 계획을 확인하고 결정하는 것이 현명하다.

인덱스 설계 가이드라인

인덱스를 만드는 것보다 더 중요한 것은, 어디에 인덱스를 만들어야 하는지 아는 것이다.

인덱스는 결코 공짜가 아니다. 데이터를 추가(INSERT), 수정(UPDATE), 삭제(DELETE)할 때마다 인덱스도 함께 변경되어야 하므로 쓰기 성능이 저하되고, 별도의 저장 공간도 차지한다. 따라서 우리는 이 비용을 상쇄하고도 남을 만큼의 '검색 성능 향상'이라는 이득을 얻을 수 있는 곳에만 전략적으로 인덱스를 생성해야 한다.

핵심 원칙 : 카디널리티(Cardinality)

해당 컬럼에 저장된 값들의 고유성(uniqueness) 정도를 나타내는 지표다.

  • 카디널리티가 높다 (High Cardinality): 해당 컬럼에 중복되는 값이 거의 없다는 의미다.

    • 예: items 테이블의 item_id, item_name

  • 카디널리티가 낮다 (Low Cardinality): 해당 컬럼의 값이 몇 종류 안되어 중복되는 값이 많다는 의미다.

    • 예: items 테이블의 category (5종류), is_active (2종류)

인덱스는 '찾아보기'다. 찾아보기가 효과적이려면, 특정 키워드를 찾았을 때 검색 범위가 확!!! 줄어들어야 한다.

items 테이블에서 WHERE is_active = TRUE 라는 조건으로 검색한다고 생각해 보자. is_active 컬럼에 인덱스가 있더라도, TRUE 인 데이터가 전체의 80%라면, 데이터베이스는 인덱스를 통해 전체 데이터의 80%를 스캔해야 한다. 이런 경우 데이터베이스 옵티마이저는 "이럴 바엔 그냥 풀 테이블 스캔하는 게 낫겠다"고 판단할 수 있다.

반면 WHERE item_name = '게이밍 노트북' 은 어떤가? 인덱스는 수십만 건의 상품 데이터 중 단 1건으로 검색 범위를 완벽하게 좁혀준다.

핵심 규칙: 인덱스는 카디널리티가 높은, 즉 식별력이 좋은 컬럼에 생성할 때 가장 효율적이다.

인덱스 생성 가이드라인

1. WHERE 절에서 자주 사용되는 컬럼

가장 기본적이고 명백한 가이드라인이다. 인덱스의 존재 이유 자체가 `WHERE` 절의 검색 속도를 높이는 것이기 때문이다. 사용자들이 상품을 검색할 때 items.item_name으로 검색하거나, 특정 카테고리(items.category)를 필터링한다면 이 컬럼들은 인덱스 생성의 우선 후보가 된다.

2. JOIN 의 연결고리가 되는 컬럼 (외래 키)

JOIN 의 성능은 연결고리가 되는 컬럼에 인덱스가 있는지 여부에 따라 극적으로 달라진다. '행복쇼핑' 판매자가 등록한 모든 상품을 조회하는 쿼리를 예로 들어보자.

items.seller_id 에 인덱스가 없을 때

  1. sellers 테이블에서 seller_name 이 '행복쇼핑'인 판매자를 찾는다. (seller_id = 1)

  2. items 테이블의 모든 행을 처음부터 끝까지 스캔하면서, seller_id 가 1 인 상품을 하나씩 찾아낸다.

조인의 논리적인 순서와 실제 순서의 차이

SQL의 논리적인 순서는 조인을 모두 다 한 다음에 WHERE를 실행한다. 하지만 데이터베이스는 최적화를 위해 먼저 데이터를 줄인 다음에 조인한다. 이때 최종 결과는 논리적인 순서와 같음을 보장한다.

items 테이블에 상품이 100만 개 있다면, JOIN 을 위해 100만 번의 비교가 일어나는 끔찍한 일이 벌어진다. 풀 테이블 스캔이 발생하는 것이다.

items.seller_id 에 인덱스가 있을 때

  1. sellers 테이블에서 seller_name 이 '행복쇼핑'인 판매자를 찾는다. (seller_id = 1)

  2. items.seller_id 인덱스를 사용하여 seller_id 가 1 인 상품 데이터의 위치를 곧바로 찾아낸다. 풀 테이블 스캔이 사라지고 몇 번의 탐색만으로 JOIN 이 완료된다.

따라서 JOIN 에 사용되는 외래 키(Foreign Key) 컬럼에는 반드시 인덱스를 생성해야 한다.

3. ORDER BY 절에서 자주 사용되는 컬럼

ORDER BY 를 사용한 정렬은 데이터의 양이 많을 경우 매우 비용이 큰 작업이다. 데이터베이스는 결과를 반환하기 전에 모든 데이터를 메모리에 올리고 정렬해야 하기 때문이다.

만약 ORDER BY 에 사용된 컬럼에 인덱스가 있다면 어떨까? B-Tree 인덱스는 이미 데이터가 정렬된 상태로 저장되어 있다. 데이터베이스는 굳이 데이터를 따로 정렬할 필요 없이, 인덱스에 있는 순서 그대로 데이터를 읽기만 하면 된다. 비용이 큰 정렬 작업(filesort)을 완전히 건너뛸 수 있는 것이다.

인덱스의 단점과 주의사항

지금까지 인덱스의 장점, 즉 검색(SELECT) 속도를 비약적으로 향상시키는 원리에 대해 배웠다. 이쯤 되면 '그럼 모든 컬럼에 인덱스를 걸면 최고 아닌가?' 라는 순수한 생각을 할 수도 있다.

결론부터 말하자면, 그것은 데이터베이스 성능을 망치는 최악의 선택이다.

인덱스는 공짜가 아니다. : 인덱스의 단점

1. 저장 공간(Storage)

인덱스는 원본 테이블과는 별개로, B-Tree 구조를 가진 물리적인 파일로 디스크에 저장된다. 즉, 인덱스를 생성하면 그만큼의 추가 저장 공간이 필요하다.

인덱스는 어떻게 구성하는지에 따라 다르지만, 일반적으로 원본 테이블 크기의 약 10% 내외의 공간을 추가로 차지한다고 알려져 있다. 만약 100GB에 달하는 거대한 items 테이블이 있고, 여기에 5개의 인덱스를 추가로 생성한다면? 인덱스만으로 약 50GB라는 무시할 수 없는 추가 디스크 공간이 필요하게 된다. 인덱스를 무분별하게 생성하면 디스크 사용량이 계속해서 늘어나는 것을 보게 될 것이다.

2. 쓰기 성능(INSERT, UPDATE, DELETE)

이것이 인덱스의 가장 치명적인 단점이자, 반드시 이해해야 할 핵심 트레이드오프다. 인덱스는 SELECT 의 속도를 높이는 대가로, INSERT, UPDATE, DELETE 의 속도를 희생시킨다.

왜 그럴까? 데이터에 변경이 일어날 때마다, 데이터베이스는 원본 테이블뿐만 아니라 이와 관련된 모든 인덱스를 함께 수정해야 하기 때문이다.

실무 가이드 : 균형의 미학

1. 워크로드를 분석하라 : 읽기 vs 쓰기

  • 읽기 중심(Read-heavy) 서비스: 데이터 분석 시스템, 블로그, 뉴스 사이트처럼 데이터 변경보다는 조회가 훨씬 더 빈번한 서비스라면, 다양한 조회 성능을 높이기 위해 인덱스를 비교적 자유롭게 생성해도 좋다. 우리 쇼핑몰의 상품 조회 기능이 대표적이다.

  • 쓰기 중심(Write-heavy) 서비스: 실시간으로 데이터를 기록하는 로깅 시스템, 주식 거래 시스템, 채팅 서비스처럼 INSERT나 UPDATE 가 매우 빈번한 서비스라면, 인덱스 생성에 매우 신중하고 보수적이어야 한다. 모든 인덱스는 쓰기 작업에 오버헤드를 추가하기 때문이다. 꼭 필요한 최소한의 인덱스만 유지해야 한다.

2. 혹시나 해서 인덱스를 만들지 말아라..

사용하지 않는 인덱스는 저장 공간만 차지하고 쓰기 성능만 저하시키는 암적인 존재다. 명확한 목적 없이, "나중에 쓸 것같아서" 라는 이유로 인덱스를 미리 만드는 것은 좋지 않다. 느린 쿼리가 발견되었을 때, 그 쿼리를 개선하기 위한 목적으로 생성해야 한다.

3. 사용하지 않는 인덱스는 주기적으로 정리하라.

대부분의 데이터베이스는 특정 인덱스가 얼마나 사용되었는지 모니터링하는 기능을 제공한다. 몇 달, 혹은 1년 이상 아무도 사용하지 않는 인덱스가 있다면, 과감하게 삭제하여 시스템 자원을 확보하고 쓰기 성능을 높여야 한다.

인덱스는 SELECT 성능을 위한 최고의 무기이지만, 저장 공간과 쓰기 성능이라는 비용을 요구하는 양날의 검과 같다.

인덱스 컬럼은 가공하면 안된다.

WHERE 절에서 인덱스가 적용된 컬럼을 함수로 감싸거나 계산을 하는 등 가공하게 되면 인덱스가 적용되지 않는다. 이는 실무에서 정말 자주 하는 실수이므로 반드시 기억해야 한다.

예를 들어 WHERE 절에서 인덱스가 적용된 컬럼에 SUBSTRING() 같은 함수를 사용하거나 연산을 하면 인덱스가 작동하지 않아 테이블 전체를 스캔하게 되므로 성능이 크게 저하된다.

  • 문제: WHERE SUBSTRING(item_name, 1, 5) = '게이밍' 처럼 인덱스 컬럼(item_name)을 가공하면, 데이터베이스는 정렬된 인덱스를 활용하지 못하고 모든 데이터를 일일이 확인한다.

    • WHERE indexed_column * 10 = 100 이런 경우도 마찬가지로 인덱스를 사용하지 못한다.

  • 원인: 인덱스는 가공되지 않은 원본 값을 기준으로 만들어지기 때문이다.

  • 해결책: 컬럼 자체를 가공하는 대신, LIKE 연산자를 사용하여 WHERE item_name LIKE '게이밍%' 와 같이 조건을 변경해야 인덱스를 효율적으로 사용할 수 있다.

결론: SQL 성능을 높이려면 인덱스 컬럼은 절대 가공하지 말고 원본 상태 그대로 사용해야 한다.

Previous인덱스1Next책

Last updated