[MySQL] Covering Index (커버링 인덱스)와 색인(Index)과 역색인(Inverted Index)

💡 July님의 200억건의 데이터를 MySQL로 마이그레이션 할 때 고려했던 개념과 튜닝 방법 강의를 듣고 정리한 내용입니다.

 

목차

Covering Index

PK 아닌 컬럼에 대해 인덱스를 설정(Non-Clustered Index)하면 컬럼 값들은 디스크의 별도에 B-Tree 구조에 저장된다. 즉 인덱스를 구성하는 B-Tree의 노드에는 인덱싱된 컬럼 값의 복사본과 Clusterd Index 행에 대한 포인터가 포함된다.

이때 실제 테이블에서 데이터를 조회하지 않고 인덱스에 있는 컬럼 값들만 사용하여 쿼리의 결과 데이터를 가져올 수 있는 인덱스를 커버링 인덱스(Covering Index)라고 한다.

 

Covering Index로 데이터를 조회하면 실제 테이블에 접근하지 않으므로 I/O가 줄어들고 탐색 성능이 개선된다. 따라서 최대한 Covering Index를 사용하도록 인덱스를 구성하고 쿼리를 작성해야한다. 이 때 B-Tree 만으로 필요한 값을 찾을 수 없다면 실제 테이블을 조회한다.(Full Scan 발생)

Clusterd Index

PK를 구성하는 인덱스, 자동으로 생성되며 순서에 따라 물리적으로 정렬되어 있다. (따라서 PK에는 별도로 인덱스를 걸지 않는다.)

만약 PK가 없는 테이블이라면 Unique Key, Unique Key도 없는 테이블이라면 별도의 ROW ID를 기준으로 구성된다.

Non-Clusterd Index

PK가 아닌 컬럼을 대상으로 구성되는 인덱스, 개발자 혹은 DBA가 생성해주며 별도의 공간에 정렬되어 저장된다.

Non-Clusterd Index만으로 데이터를 조회할 수 없는 경우 실제 테이블을 조회한다. (Full Scan 발생)

MySQL 쿼리 동작 방식

쿼리를 실행하면 MySQL Optimizer는 쿼리를 분석하고, 적합한 인덱스를 찾고 판단한다. 이 과정을 쿼리 실행 전력(Explain)을 수립한다고 한다.

사용할 인덱스를 정하면 해당 인덱스를 통해 컬럼 값과 위치를 찾는다. 만약 Optimizer가 실행 전략을 수립하는데 적절한 인덱스를 찾지 못하면 테이블을 직접 조회한다.

Covering Index 예시

예를 들어 다음과 같이 employees 테이블이 있다고 가정하자

CREATE TABLE employees (
    employee_id INT PRIMARY KEY,
    first_name VARCHAR(50),
    last_name VARCHAR(50),
    department_id INT,
    salary DECIMAL(10, 2)
);

 

위 employees 테이블에 대해 first_name과 last_name을 포함하는 idx_employee_name 인덱스를 구성하자.

CREATE INDEX idx_employee_name ON employees (first_name, last_name);

 

이 상황에서 다음 쿼리는 SELECT 절에 나오는 컬럼들이 모두 idx_employee_name 인덱스에 포함되어 있어 인덱스만으로 해결할 수 있다. 따라서 이 경우 별도의 테이블 없이 인덱스만으로 결과를 반환할 수 있으므로 커버링 인덱스에 해당한다.

SELECT first_name, last_name FROM employees WHERE first_name = 'John';

 

반면 다음 쿼리는 SELECT 절에 포함된 salary 컬럼이 인덱스에 포함되어 있지 않으므로, 인덱스를 사용해 first_name과 last_name을 조회한 후, 추가로 테이블에 접근하여 salary 값을 읽어야한다. 즉 테이블 풀 스캔이 발생했으며, 이는 커버링 인덱스가 아닌 상황이다.

 

혹은 다음과 같이 WHERE 조건에 인덱스가 아닌 컬럼이 포함되는 경우에도 테이블 전체를 스캔하여 조건을 만족하는 행을 찾아야 한다. 이 경우 또한 테이블 풀 스캔이 발생했으며, 커버링 인덱스가 아닌 상황이다.

SELECT first_name, last_name FROM employees WHERE salary > 50000;

실무에서는?

실무에서는 아무리 처음에 테이블 설계가 완벽했고 정규화도 잘 되어있다고 하더라도 모든 쿼리에서 Covering Index를 사용하도록 쿼리를 작성하기는 어렵다. 추가적인 기능과 정책이 발전되면서 서버 개발자는 어쩔 수 없이 데이터를 추가하고 테이블을 수정해야한다.

즉 테이블 구조가 망가지고 처음 의도처럼 인덱스를 효율적으로 사용할 수 없다. 실제로는 운영하면서 인덱스를 추가하는 과정도 많이 발생한다.

색인(Index)과 역색인(Inverted Index)

인덱스를 구성하는 컬럼 값들을 B-Tree 구조에 미리 저장해두고 쿼리를 실행할때 이 미리 저장된 값을 사용하는 것을 색인(Index)이라고 한다. 쉽게 말해 책의 목차(Index)를 생각하면 이해하기 쉽다. RDB에서 데이터를 저장하는 구조가 이 방식을 따른다.

 

반면 단어별(텍스트)로 저장된 위치를 저장하는 방식을 역색인(Inverted Index)라고 한다. 예를 들어 책에서 특정 키워드가 등장하는 페이지를 저장해둔다고 생각하면 이해하기 쉽다.

Elastic Search에서 이러한 구조를 사용한다. 단어별로 위치를 저장한다는 특징으로 Elastic Search는 텍스트 검색에서 좋은 성능을 발휘한다.