인덱스1

인덱스가 필요한 이유

우리 쇼핑몰이 대성공을 이루어서 판매하는 상품의 개수가 25개가 아니라, 50만개, 100만개로 늘어난다면 아래 쿼리의 실행 속도가 느려지기 시작한다. (몇분이 걸릴수도 있다..)

select * from items where item_name = '게이밍 노트북';

물론 데이터가 아주 많아야 이렇게 느려진다.

느린 검색의 원인 : 풀 테이블 스캔(Full Table Scan)

인덱스가 없는 테이블에서 특정 데이터를 찾는 과정은, 비유하자면 100만 페이지짜리 거대한 책에서 특정 단어 하나를 찾기 위해, 책의 첫 페이지부터 마지막 페이지까지 한 장 한장 넘겨보는 것과 같다.

데이터베이스는 item_name 컬럼에 '게이밍 노트북' 이라는 값이 어디에 있는지 알 수 있는 아무런 힌트가 없다. 그래서 데이터베이스는 가장 무식하고 정직하게, items 테이블 전체를 디스크에서 메모리로 읽어 들인 후, 첫 번째 행부터 마지막 100만 번째 행까지 하나씩 차례대로 item_name 컬럼의 값을 비교하는 것이다.

이러한 작업 방식을 풀 테이블 스캔 이라고 부른다.

  • 최선의 경우 : 우리가 찾는 데이터가 우연히 첫 번째 행에 있다면 한 번의 비교만으로 끝난다.

  • 최악의 경우 : 우리가 찾는 데이터가 맨 마지막 행에 있거나, 아예 존재하지 않는다면 100만 번의 비교를 모두 수행해야만 알 수 있다.

풀 테이블 스캔은 빅오 표기법으로 O(N) 의 시간복잡도를 갖는다. 이말은 레코드 수 많큼 스캔 시간도 증가한다는 의미이다.

이렇듯 풀 테이블 스캔에서 데이터가 많아질수록 검색 시간이 정비례해서 늘어나는 것은 당연한 결과이다. 이것이 바로 우리의 서비스가 느려지는 근본적인 원인이다.

실무 이야기1

오늘날의 사용자들은 단 몇 초의 로딩 시간도 기다리지 않는다.

페이지 로딩 속도에 관한 연구

페이지 로딩 속도에 관한 다양한 논문들이 있는데 대략 정리하면 다음과 같다.

  • 모바일 페이지 로딩 속도가 1초에서 3초로 늘어나면 이탈률이 32% 증가한다.

  • 모바일 페이지 로딩 속도가 1초에서 5초로 늘어나면 이탈률이 90% 이상 증가한다.

정리하면 웹 서비스는 최소한 3초 이내, 이상적으로는 1~2초 이내의 빠른 로딩 속도를 목표로 해야 한다.

일반적인 서비스를 생각할때, 하나의 화면에 단순히 items 테이블 하나만 조회하지 않는다. 보통 한 화면을 보여주기 위해서는 다양한 데이터 조회가 필요하다.

이 테이블들이 모두 풀 테이블 스캔이라면 데이터의 양이 적을 때는 문제가 없어 보이겠지만, 데이터가 점점 증가하기 시작하는 순간 서비스도 점점 느려질 것이다.

실무 이야기2

풀테이블 스캔은 매우 비용이 높은 작업이므로, 서비스의 핵심 기능에서 가급적 풀테이블 스캔이 발생하지 않도록 설계하는 것이 필수적이다.

  • 인덱스 활용 : WHERE 절에 자주 사용되는 조건 컬럼에는 인덱스를 생성해 풀테이블 스캔을 방지해야 한다.

  • 실행 계획 확인 : 쿼리 실행 전 실행 계획을 확인하여 의도하지 않은 풀테이블 스캔이 발생하는지 반드시 점검해야 한다.

  • 작업 시간 분리 : 대용량 데이터에 대한 전체 스캔이 불가피한 통계/배치 작업이라면, 서비스 이용자가 적은 새벽 시간에 실행하는 것을 권장한다.

인덱스 소개

책에서 특정 단어를 찾기 위해서 우리는 책의 맨 뒤에 있는 '찾아보기(색인)' 페이지를 확인한다.

색인은 페이지는 어떻게 구성되어 있는가?

  • 책의 중요한 키워드들이 정렬된 순서대로 정리되어 있다.

  • 각 키워드 옆에는 그 키워드가 등장하는 페이지 번호가 있다.

여기서 중요한 점은 중요한 키워드들이 정렬된 순서로 정리되어 있기에 우리가 빠르게 찾을 수 있는 것이다.

데이터베이스의 인덱스는 책의 색인과 동일한 역할을 한다.

인덱스트는 특정 컬럼들의 데이터를 기반으로 생성되는, 원본 테이블과 별개의 특수한 자료 구조이다.

  • 인덱스는 지정된 컬럼(예: item_name)의 값과, 해당 값을 가진 실제 데이터 행의 위치(예: 주소값, 포인터, PK 값 등)를 한 쌍으로 저장한다.

  • 가장 중요한 것은, 인덱스 내부의 데이터는 항상 정렬된 상태를 유지한다는 점이다.

item_name 기반으로 인덱스를 만들면 다음과 같은 구조를 가진다.

  • item_name 이 가나다 순서대로 정렬된 것을 확인할 수 있다.

  • 이렇게 정렬된 상태면 사람도 원하는 데이터를 빨리 찾을 수 있듯이 데이터베이스도 원하는 데이터를 빨리 찾을 수 있다.

  • 원본 데이터의 위치에는 해당 값을 가진 실제 데이터 행의 위치(예: 주소값, 포인터, PK 값 등)를 저장한다. 이 값을 통해 원본 데이터에 빠르게 접근할 수 있다.

item_name 컬럼에 인덱스가 생성된 후, 다시 검색 쿼리를 실행하면 완전히 다르게 동작한다.

  1. items 테이블 전체를 순서대로 스캔하는 대신, 먼저 item_name 기반의 인덱스를 찾아간다.

  2. 인덱스는 정렬되어 있으므로, '게이밍 노트북' 이라는 겂을 아주 빠르게 찾아낸다.

  3. 찾아낸 인덱스 항목에서 실제 데이터 행의 위치를 확인한다.

  4. 그 위치를 이용해서 원본 테이블의 해당 위치로 점프해서, 단번에 원하는 데이터를 가져온다.

인덱스 덕분에 100만번의 비교 작업이 단 몇번의 작업으로 줄어들게 된다. 데이터가 1억 건으로 늘어나도 검색 속도는 거의 차이가 나지 않을 정도로 비약적인 성능 향상이 일어난다.

이것이 바로 우리가 인덱스를 사용해야 하는 이유다. 인덱스는 느려터진 데이터베이스에 날개를 달아주는, 성능 최적화의 가장 기본적이고 핵심적인 기술이다.

실제 인덱스의 구현

실제 물리적인 인덱스는 그 종류에 따라 구현이 다 다르다.

예를 들어, MySQL 의 경우 클러스터 인덱스(Clustered Index) 와 보조 인덱스(Secondary Index) 라는 2가지 종류의 인덱스를 제공한다.

클러스터 인덱스는 기본 키를 기반으로 만드는 인덱스이다. 클러스터 인덱스는 원본 데이터 자체를 인덱스에 함께 보관해서 원본 데이터를 매우 빠르게 찾을 수 있다.

보조 인덱스는 원본 데이터의 기본 키 값을 함께 보관한다. 그리고 이 기본키 값으로 클러스터 인덱스를 통해 원하는 데이터를 조회한다.

이처럼 인덱스의 종류에 따라 내부 동작 방식은 다양하지만, 핵심적인 목적은 변함없이 데이터 검색 속도를 향상시키고 쿼리 성능을 최적화하는 것이다.

SHOW INDEX : 테이블의 인덱스 정보 확인하기

SHOW INDEX 명령어를 사용하면 테이블에 걸려있는 모든 인덱스의 정보를 한눈에 볼 수 있다.

결과를 자세히 살펴보자.

  • Key_name : 인덱스의 이름이다. idx_items_item_name 은 직접 만든 인덱스이다.

  • Column_name : 해당 인덱스가 어떤 컬럼을 기반으로 만들어졌는지 보여준다.

  • PRIMARY 와 seller_id : 여기서 흥미로운 점은, 우리는 item_name 컬럼에 대한 인덱스만 만들었는데, PRIMARY 와 fk_items_sellers 라는 인덱스가 이미 존재한다는 것이다.

    • MySQL에서는 PRIMARY KEY (기본 키)나 FOREIGN KEY (외래 키) 제약조건을 설정하면, 해당 컬럼에 대해 자동으로 인덱스를 생성한다. item_id 는 기본 키이므로 PRIMARY 인덱스가, seller_id 는 외래키이므로 fk_items_sellers 인덱스가 이미 존재했던 것이다. 매우 중요한 사실이니 꼭 기억해 두자!

      (UNIQUE 제약조건도 마찬가지로 인덱스가 생성된다.)

    • UNIQUE 조건도 생각해보면, 데이터를 넣을 때마다 같은 데이터가 있는지 비교해야 하는데, 데이터의 수가 일정 수를 넘어가면 조회하는데 비용이 클 것이기 때문에, 인덱스로 사용하는 것이 합리적이다.

  • Non_unique: 1 이면 중복 값을 허용하는 인덱스, 0 이면 중복을 허용하지 않는 고유 인덱스(UNIQUE 또는 PRIMARY KEY)라는 의미다. idx_items_item_name 은 1 이므로, 상품명이 같은 다른 상품이 등록될 수 있다는 것을 알 수 있다.

  • Cardinality: 인덱스에 저장된 유니크한 값의 개수에 대한 추정치다. 이 값이 높을수록 중복도가 낮다는 의미이며, 인덱스의 성능이 좋다고 판단할 수 있다. (Cardinality는 뒤에서 설명한다.)

인덱스가 정말 사용되는지 확인하는 법(EXPLAIN)

데이터베이스에는 쿼리를 어떤 방식으로 최적화해서 실행할지 계획하는 기능이 있는데, 이것을 쿼리 옵티마이저(최적화기)라 한다.

인덱스를 만들었다고 해서, 데이터베이스가 모든 SELECT 문에 항상 그 인덱스를 사용하는 것은 아니다. 데이터의 분포나 쿼리의 형태에 따라, 데이터베이스 옵티마이저는 인덱스를 사용하는 것보다 풀 테이블 스캔이 더 빠르겠다고 판단할 수도 있다. 추가로 사용할 수 있는 인덱스가 여러개 있다면 어떤 인덱스를 사용할지도 선택한다.

우리가 만든 인덱스가 실제로 쿼리에 사용되는지 확인하려면 EXPLAIN 이라는 명령어를 쿼리문 앞에 붙여보면 된다.

  • type: ALL: 가장 중요하게 봐야 할 부분이다. type 은 데이터베이스가 테이블에 어떻게 접근할지를 나타낸다.

    ALL 은 풀 테이블 스캔(Full Table Scan)을 의미한다. 즉, items 테이블의 처음부터 끝까지 모든 데이터를 하나씩 다 읽어서 조건에 맞는 데이터를 찾는다는 뜻이다. 지금은 데이터가 25개뿐이라 문제가 없지만, 실무에서처럼 데이터가 수백만 건에 달한다면 상상만 해도 끔찍한 성능 저하를 일으키는 주범이 된다.

    • ALL 이라고 표시되면 풀 테이블 스캔을 의미한다. 즉, 테이블의 모든 행을 처음부터 끝까지 다 읽었다는 뜻이다.

    • 인덱스를 제대로 사용했다면 ref , range 등 다른 값이 표시된다.

      • ref 는 = 조건이나 JOIN 에서 인덱스를 사용했다는 의미다.

      • range 는 범위 검색(BETWEEN, >, < , >= 등)에서 인덱스를 사용했다는 의미다.

  • key: NULL: key 는 쿼리를 실행할 때 사용한 인덱스를 보여준다. 이 값이 NULL 이라는 것은 어떤 인덱스도 사용하지 못했다는 것을 명확하게 알려준다. item_name 컬럼으로 데이터를 찾고 있지만, 해당 컬럼에 인덱스가 없기 때문에 당연한 결과다.

  • rows: 25: 옵티마이저가 쿼리를 처리하기 위해 탐색할 것으로 예측하는 행의 수다. 현재 items 테이블의 전체 데이터가 25개이므로, 결국 테이블 전체를 다 훑어보겠다고 말하는 것과 같다. type 이 ALL 이니 당연히 전체 행의 개수가 표시된다. 이 값이 작을 수록 효율적인 쿼리라 할 수 있다. (실제 실행하는게 아니라 데이터베이스 나름의 통계 데이터를 기반으로 하는 예측 정보다. 따라서 정확하게 맞는 것은 아니다.)

  • filtered: 10.00: 테이블에서 읽어온 행들 중에서 WHERE 조건으로 필터링되고 난 후, 최종적으로 남을 것으로 예측되는 행의 비율이다. 여기서는 25개의 행을 모두 읽은 후, 그중 10%인 2.5개 정도의 행이 item_name = 게이밍 노트북 조건을 만족할 것이라고 예측하고 있다. (실제 실행하는게 아니라 데이터베이스 나름의 통계 데이터를 기반으로 하는 예측 정보다. 따라서 정확하게 맞는 것은 아니다.)

  • Extra: Using where : 데이터를 가져온 후에 WHERE 절의 조건(item_name = '게이밍 노트북' ) 을 사용해 필터링 작업을 수행했다는 의미다. 만약 인덱스를 효율적으로 사용했다면, 처음부터 조건에 맞는 데이터만 골라서 가져왔을 것이다. 하지만 인덱스가 없으니 일단 모든 데이터를 다 가져와서, 그 후에 조건에 맞는지 일일이 비교하는 비효율적인 방식으로 일하고 있음을 보여준다.

인덱스의 동등 비교

인덱스와 동등 비교 시작

  • type: ref: 이전의 ALL 과 비교했을 때 가장 극적인 변화다. type 이 ref 라는 것은, 인덱스를 사용해 동등 비교(=

    ) 조건으로 데이터를 찾았다는 의미다. ref 는 reference(참조)의 약자로, 인덱스를 통해 조건에 맞는 데이터를 매우 효율적으로 참조해서 가져왔다는 뜻이다. 풀 테이블 스캔(ALL)과는 비교할 수 없을 정도로 빠른 접근 방식이다.

  • possible_keys : 현재 쿼리에서 사용 가능한 인덱스의 후보이다. 지금은 idx_items_item_name 하나만 있지만, 현재 쿼리에서 사용가능한 인덱스를 모두 보여준다. 이 후보들 중에 선택되어 사용될 인덱스가 다음 key 항목에 나타난다.

  • key: idx_items_item_name : 이전에는 NULL 이었던 이 값에 우리가 방금 생성한 인덱스의 이름(idx_items_item_name)이 명확하게 표시된다. 이것은 옵티마이저가 이 쿼리를 실행하는 데 idx_items_item_name 인덱스를 사용했음을 보여주는 직접적인 증거다.

  • filtered: 100.00: 인덱스를 통해서 찾은 1개의 행을 100% 선택한다는 뜻이다.

  • rows: 1: 이 또한 엄청난 변화다. 인덱스가 없을 때는 테이블 전체 행의 수인 25 를 스캔할 것으로 예측했지만, 이제는 단 1 개의 행만 읽으면 된다고 예측한다. 마치 책의 맨 뒤에 있는 찾아보기를 통해 '게이밍 노트북'이라는 단어가 있는 페이지를 바로 찾아가는 것과 같다. 테이블 전체를 뒤지는 것이 아니라, 인덱스를 통해 필요한 데이터의 위치를 정확히 찾아가기 때문에 탐색하는 행의 수가 극적으로 줄어든다.

  • Extra: NULL: 이전에 표시되었던 Using where 가 사라졌다. 이는 인덱스 단계에서 이미 모든 검색 조건이 충족되었기 때문에, 데이터를 가져온 후 별도의 필터링 작업이 필요 없었다는 것을 의미한다. 그만큼 작업이 더 단순하고 효율적으로 처리된 것이다.

인덱스와 범위 검색

앞에서 type 이 range 일 때 범위 검색(BETWEEN, >, < , LIKE 등)에 인덱스가 사용된다고 설명했다. 이번에는 items 테이블의 price 컬럼을 사용하여 범위 검색(BETWEEN)에 인덱스가 어떻게 사용되는지 EXPLAIN 으로 확인해 보자.

  • ...

  • Extra: Using index condition : 이 부분도 중요한 최적화 정보다. 인덱스 정보만으로 WHERE 조건절을 최대한 필터링한 후, 조건을 만족하는 데이터의 전체 행만 가져왔다는 뜻이다.

여기서 주목할 점은 결과가 price 순으로 정렬되었다는 것이다. 가장 오른쪽의 price 컬럼을 확인해보자.

인덱스가 없을 때는 item_id 순서(테이블에 데이터가 물리적으로 저장된 순서) 로 결과가 나왔지만, idx_items_price 인덱스를 사용한 후에는 인덱스 키인 price 를 기준으로 정렬된 결과가 나왔다.

이는 데이터베이스가 idx_items_price 인덱스를 price 순서대로 스캔하면서 조건에 맞는 item_id 를 찾고, 그 item_id 를 사용해 원본 테이블에서 데이터를 가져왔기 때문이다. 이처럼 인덱스를 사용하면 쿼리 결과의 정렬 순서가 달라질 수 있다는 점을 알아두는 것이 중요하다.

인덱스와 LIKE 범위 검색

LIKE 절에서 인덱스를 사용하려면, 와일드카드(% )가 검색어의 뒤쪽에 위치해야 한다.

  • WHERE item_name LIKE '게이밍%' : 인덱스 사용 가능 (O)

  • WHERE item_name LIKE '%게이밍' : 인덱스 사용 불가 (X)

  • WHERE item_name LIKE '%게이밍%' : 인덱스 사용 불가 (X)

% 가 앞에 있으면 시작점이 불분명해져 정렬된 인덱스를 활용할 수 없기 때문이다.

인덱스와 정렬

데이터베이스에서 정렬(ORDER BY) 작업은 생각보다 비용이 많이 드는 무거운 작업 중 하나다. 왜냐하면 조건에 맞는 데이터를 모두 찾은 후에, 그 결과를 서로 비교하면서 순서에 맞게 다시 정렬해야 하기 때문이다. 찾은 데이터가 수십, 수백만 건이라면 이 데이터를 정렬 알고리즘을 사용해서 정렬해야 하는데, 이 정렬 과정에서 엄청난 부하가 발생할 수 있다.

하지만 우리에게는 인덱스가 있다. 인덱스는 이미 데이터가 특정 순서로 정렬된 자료구조다. 그렇다면 이 정렬된 인덱스를 활용해서 ORDER BY 작업의 성능을 획기적으로 개선할 수 있지 않을까?

ORDER BY 가 인덱스를 잘 활용하면, 별도의 정렬 과정 없이 이미 정렬된 인덱스를 순서대로 읽기만 하면 되므로 매우 빠르게 동작한다. 데이터베이스는 이 과정에서 filesort 라는 별도의 정렬 작업을 생략할 수 있게 된다.

  • 여기서 filesort 라는 이름만 보고 파일 시스템을 사용한다고 오해하면 안 된다.

  • 실제로는 메모리나 디스크를 사용해 정렬하는 내부 프로세스를 의미한다. 우리의 목표는 바로 이 비효율적인 filesort 를 피하는 것이다.

인덱스를 활용하지 않는 다음 쿼리를 보면 filesort 를 확인할 수 있다.

인덱스를 사용해 정렬까지 한 번에 처리하는 경우

가장 이상적인 상황은 WHERE 절의 조건과 ORDER BY 절의 정렬 기준이 같아서, 인덱스 하나로 검색과 정렬을 모두 해결하는 경우다.

앞서 price로 범위 검색을 했던 쿼리에 ORDER BY price 를 추가해서 실행 계획을 확인해 보자. 우리는 이미 price 컬럼에 idx_items_price 인덱스를 만들어 두었다.

데이터베이스 옵티마이저는 idx_items_price 인덱스가 이미 price 순서로 정렬되어 있다는 사실을 알고 있다. 따라서 WHERE 조건에 맞는 데이터를 찾기 위해 인덱스를 스캔하는 것만으로도 자연스럽게 price 순서로 정렬된 결과를 얻을 수 있다. 즉, 별도의 정렬 작업을 할 필요가 전혀 없는 것이다.

ORDER BY price 가 없더라도 인덱스에서 이미 price 순서대로 정렬이 되어 있기 때문에, 결과는 동일하게 나온다.

이것이 인덱스를 활용한 ORDER BY 최적화의 핵심이다. WHERE 절과 ORDER BY 절이 동일한 인덱스를 효율적으로 사용할 수 있다면, 데이터베이스는 정렬을 생략하고, 가장 빠른 방식으로 쿼리를 처리한다.

인덱스를 역방향으로 조회하는 경우

ORDER BY 를 사용할 때 항상 오름차순(ASC)으로만 정렬하는 것은 아니다. 쇼핑몰에서는 최신 상품이나 가격이 높은 상품을 먼저 보여주는 것처럼, 내림차순(DESC) 정렬도 매우 흔하게 사용된다.

그렇다면 ORDER BY price DESC 처럼 내림차순 정렬을 사용하면 filesort 가 발생할까?

결론을 말하면, 단일 컬럼 인덱스에서는 filesort 없이 효율적인 처리가 가능하다. 데이터베이스 옵티마이저는 인덱스를 거꾸로 읽는, 즉 역방향 스캔(Backward Index Scan)을 할 수 있기 때문이다.

  • 트리 자료 구조의 특징 때문에 가능하다.

이처럼 인덱스를 역방향으로 스캔하는 것만으로도 filesort 를 피할 수 있으므로 매우 효율적인 방식이다.

내림차순 인덱스

역방향 스캔은 효율적이지만, 여기서 한 걸음 더 나아가 정렬 방향과 일치하는 인덱스를 직접 만들어 줄 수도 있다. MySQL 8.0 버전부터는 내림차순 인덱스(Descending Index) 생성을 정식으로 지원한다.

내림차순 인덱스는 데이터 자체를 처음부터 내림차순으로 정렬하여 저장하는 인덱스다.

key 컬럼에서 우리가 새로 만든 idx_items_price_desc 인덱스가 사용된 것을 볼 수 있다. 가장 주목할 점은 Extra 컬럼에서 Backward index scan 이 사라지고 Using index condition 만 남았다는 것이다.

이는 옵티마이저가 더 이상 인덱스를 '거꾸로' 읽는 수고를 할 필요가 없어졌음을 의미한다. 쿼리가 요구하는 정렬 순서(DESC)와 인덱스의 정렬 순서(DESC)가 완벽하게 일치하므로, 인덱스를 자연스러운 순서(정방향)로 스캔하기만 하면 된다. 이것이 ORDER BY 절을 최적화하는 가장 이상적인 방법이다.

단일 컬럼 인덱스에서는 역방향 스캔과 내림차순 인덱스 간의 성능 차이가 크지 않을 수 있다. 하지만 ORDER BY category ASC, registered_date DESC 처럼 여러 컬럼에 대해 서로 다른 정렬 순서(오름차순과 내림차순의 혼합)가 필요한 복잡한 쿼리에서는 내림차순 인덱스의 진가가 발휘된다.

이런 경우, 정렬 순서에 맞춰 정확하게 생성된 다중 컬럼 인덱스(복합 인덱스)는 쿼리 성능을 극적으로 향상시킬 수 있다. 다중 컬럼 인덱스(복합 인덱스)는 뒤에서 알아본다.

PreviousViewNext인덱스2

Last updated