Database ‐ 인덱스 - dnwls16071/Backend_Summary GitHub Wiki

📚 인덱스 소개 및 필요한 이유

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

풀 테이블 스캔은 O(N)의 시간 복잡도를 가진다. 여기서 N은 레코드 건수를 나타내며, 레코드 수가 2배로 증가하면 스캔 시간도 대략 2배로 증가한다.
데이터 양에 따른 정확한 응답 시간을 예측하는 것은 불가능하다. 왜냐하면 응답 시간은 데이터베이스 서버 사양(CPU, 메모리, 디스크 I/O 성능), 환경 설정, 한 행당 크기, 시스템 현재 부하 등 수많은 변수에 따라 크게 달라지기 때문이다.
풀 테이블 스캔은 매우 비용이 높은 작업이므로 서비스 핵심 기능에서 가급적 풀 테이블 스캔이 발생하지 않도록 설계하는 기능이 필요하다.

설계

인덱스 활용 : WHERE절에 자주 사용되는 검색 조건에 인덱스를 생성해 풀 테이블 스캔을 방지하는 것이 가장 기본적인 해결책
실행 계획 확인 : 쿼리 실행 전 실행 계획을 확인해 의도치 않은 풀 테이블 스캔이 있는지 반드시 점검해야 한다.
작업 시간 분리 : 대용량 데이터에 대한 전체 스캔이 불가피한 통계/배치 작업이라면 서비스 이용자가 적은 새벽 시간에 실행하는 것을 권장한다.

인덱스(Index)

특정 컬럼들의 데이터를 기반으로 생성되는 원본 테이블과는 별개의 특수한 자료 구조이다.
인덱스 내부 데이터는 항상 정렬된 상태를 유지한다.

📚 인덱스와 LIKE 범위 검색

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

SELECT * FROM items WHERE item_name LIKE '게이밍%';

LIKE 절에서 와일드카드(%)가 앞에 오면 인덱스를 타지 않는다.

SELECT * FROM items WHERE item_name LIKE '%게이밍%';

❗LIKE 검색은 %를 마지막에 사용할 때만 인덱스를 활용할 수 있다.

전문 검색, 전체 문자 검색(Full-Text Search)

LIKE '%검색어%' 방식은 성능이 데이터의 양에 반비례한다.
이런 내용 검색 또는 포함 검색 문제를 해결하기 위해 데이터베이스는 전문 검색이라는 특수 기능을 제공한다.
전문 검색 인덱스는 B-Tree 인덱스와는 달리, 텍스트를 단어(토큰) 단위로 나눠 쪼개 인덱싱하는 방식을 말한다. 이를 통해 텍스트 중간에 있는 단어도 매우 빠르게 검색이 가능하다.
LIKE 대신 MATCH ... AGANIST 구문도 활용 가능하다.

📚 인덱스와 정렬

데이터베이스에서 정렬 작업은 생각보다 비용이 많이 드는 무거운 작업 중 하나이다.
왜냐하면 조건에 맞는 데이터를 모두 찾은 후에 그 결과를 서로 비교해 순서에 맞게 다시 정렬하기 때문이다.
ORDER BY가 인덱스를 잘 활용하게끔 한다면 별도의 정렬 과정 없이 이미 정렬된 인덱스를 순서대로 읽기만 하면 되기 때문에 매우 빠르게 동작할 수 있다. 데이터베이스는 이 과정에서 filesort라는 별도의 정렬 작업을 생략할 수 있게 된다.
filesort란, 파일 시스템을 사용하는 것이 아니고 실제로 메모리나 디스크를 사용해 정렬하는 내부 프로세스를 말한다.

가장 이상적인 경우 - 인덱스를 사용해 정렬까지 한 번에 처리할 수 있는 경우

WHERE절의 조건과 ORDER BY절의 정렬 기준이 같아서 인덱스 하나로 검색과 정렬을 모두 해결하는 경우다.
옵티마이저는 인덱스가 이미 WHERE 절에 쓰이는 특정 컬럼 순서로 정렬된다는 사실을 알고 있기 때문에 WHERE 절의 조건에 맞는 데이터를 찾기 위해 인덱스를 스캔하는 것만으로도 자연스럽게 정렬된 결과를 얻을 수 있다. 즉, 별도의 정렬 작업을 할 필요가 없는 것이다.

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

단일 컬럼 인덱스의 경우 filesort 없이 효율적인 처리가 가능하다.
데이터베이스 옵티마이저는 인덱스를 거꾸로 읽는 즉, 역방향 스캔(Backword Index Scan)을 할 수 있기 때문이다.
filesort가 적용된 여부는 EXPLAIN 실행 계획을 조회 후 Extra 필드를 보면 된다.

내림차순 인덱스(Descending Index)

역방향 스캔은 효율적이나 정렬 방향과 일치하는 인덱스를 직접 만들 수 있다.
MySQL 8.0부터는 내림차순 인덱스 생성을 지원한다.

📚 옵티마이저와 인덱스 선택

컬럼에 인덱스를 생성하면 해당 컬럼을 조건으로 사용하는 모든 WHERE 절이 무조건 인덱스를 사용하는 것은 아니다.
데이터베이스의 옵티마이저는 쿼리를 실행하기 전에 여러 실행 가능한 방법을 평가하고, 그 중 가장 비용이 적게 드는 즉, 가장 효율적이라고 판단되는 방법을 선택한다.
이 과정에서 옵티마이저는 인덱스를 사용하는 것이 오히려 비효율적이라고 판단되면 인덱스가 존재하더라도 포기하고 풀 테이블 스캔하는 방법을 선택할 수 있다.

랜덤 I/O vs 순차 I/O

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

📚 커버링 인덱스

쿼리에 필요한 모든 컬럼을 포함하는 인덱스를 말한다.
데이터베이스 옵티마이저는 쿼리를 실행할 때, 특정 인덱스가 SELECT, WHERE, ORDER BY, GROUP BY절에 사용되는 모든 컬럼을 가지고 있다면, 원본 테이블에 전혀 접근하지 않고 오직 인덱스만을 읽어서 쿼리를 처리한다.
이는 디스크 여러 곳을 오가는 랜덤 I/O 작업을 완전히 제거하고 순차 I/O에 가까운 인덱스 스캔만으로 쿼리를 끝낼 수 있음을 의미한다. 이로 인해 성능은 비약적으로 향상된다.

❗컬럼이 여러 개인 복합 인덱스에서 컬럼 순서는 매우 중요하다. WHERE절에서 동등 비교나 범위 검색에 사용되는 컬럼을 가장 앞에 두어야 인덱스를 효율적으로 사용할 수 있다.

CREATE INDEX idx_items_price_name ON items (price, item_name);

장점 분석

압도적인 SELECT 성능 향상 : 테이블 접근을 위한 랜덤 I/O를 제거해 조회 성능을 개선
COUNT 쿼리 최적화 : 크기가 훨씬 작은 인덱스만 스캔해 결과를 빠르게 반환 가능

단점 분석

저장 공간 증가 : 인덱스는 원본 데이터와 별도의 저장 공간을 차지한다. 인덱스에 포함되는 컬럼 수가 많아질수록 인덱스 크기도 커진다.
쓰기 성능 저하 : INSERT, UPDATE, DELETE 작업 시, 테이블 데이터와 인덱스도 함께 수정되기 때문에 쓰기 성능이 저하된다.

❗나열된 장단점을 잘 보고 신중하게 고려 - 조회(읽기)가 매우 빈번하고 쓰기 작업은 상대적으로 적은 테이블에 적용하는 것이 유리

📚 복합 인덱스

인덱스 예시 : item_idx(category, price)
- 먼저 category를 기준으로 정렬한다.
- 같은 category 내에서 price를 기준으로 다시 정렬한다.

❗복합 인덱스 대원칙

인덱스는 순서대로 사용해라.
등호 조건은 앞으로 오고 범위 조건은 뒤로 가도록
정렬(ORDER BY)도 인덱스 순서를 따르도록

❗범위 조건 때문에 인덱스 컬럼을 활용하지 못하면 IN절을 활용해도 된다.

📚 인덱스 설계 가이드라인⭐

인덱스를 어디에 걸지 판단하는 가장 중요한 기준은 바로 카디널리티(Cardinality)
- 카디널리티란, 해당 컬럼에 저장된 값들의 고유성 정도를 나타내는 지표이다.
- 카디널리티가 높다 = 해당 컬럼에 중복되는 값이 거의 없다.
- 카디널리티가 낮다 = 해당 컬럼에 중복되는 값이 많다.

❗인덱스는 카디널리티가 높은 즉, 식별력이 높은 컬럼에 생성할 때 효과적이다.

WHERE절에서 자주 사용되는 컬럼에 인덱스를 거는 것이 좋다.
JOIN에 사용되는 외래 키(FK) 컬럼에는 반드시 인덱스를 생성해야 한다.
ORDER BY절을 사용한 정렬은 데이터 양이 많을 경우 매우 비용이 큰 작업이다. 왜냐하면 데이터베이스는 결과를 반환하기 전에 모든 데이터를 메모리에 올리고 정렬해야 하기 때문이다. ORDER BY에 사용되는 인덱스가 따로 있다면 이미 데이터가 정렬된 상태기 때문에 굳이 정렬할 필요가 없어 인덱스에 있는 순서 그대로를 읽기만 하면 된다.

📚 인덱스 단점과 주의사항

모든 컬럼에 인덱스를 걸면 데이터베이스 성능을 망치는 최악의 선택이 된다.
저장 공간 문제 : 인덱스는 원본 테이블과는 별개로 B-Tree 구조를 가진 물리적인 파일로 디스크에 저장된다. 즉, 인덱스를 생성하면 그만큼의 추가 저장 공간이 필요하다.
쓰기 성능 저하 : 인덱스는 SELECT 속도를 높이는 대가로, INSERT, UPDATE, DELETE 속도를 저하시킨다. 왜냐하면 데이터베이스는 원본 테이블뿐만 아니라 이와 관련된 모든 인덱스를 함께 수정해야 하기 때문이다.

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

WHERE절에서 인덱스가 적용된 컬럼을 함수로 감싸거나 계산을 하는 등의 가공 작업을 거치게 되면 인덱스가 적용되지 않는다.
컬럼 자체를 가공하는 대신 LIKE 연산자를 사용해야 한다. SQL 성능을 높이고자 한다면 인덱스 컬럼은 절대 가공하지 말고 원본 그대로 사용해야 한다.