인덱스(Index)

인덱스(Index)란

인덱스란 추가적인 쓰기 작업과 저장 공간을 활용하여 데이터베이스 테이블의 검색 속도를 향상시키기 위한 자료구조이다.

만약 우리가 책에서 원하는 내용을 찾는다고 하면, 책의 모든 페이지를 찾아 보는것은 오랜 시간이 걸린다. 그렇기 때문에 책의 저자들은 책의 맨 앞 또는 맨 뒤에 색인을 추가하는데, 데이터베이스의 index는 책의 색인과 같다.

데이터베이스에서도 테이블의 모든 데이터를 검색하면 시간이 오래 걸리기 때문에 데이터와 데이터의 위치를 포함한 자료구조를 생성하여 빠르게 조회할 수 있도록 돕고 있다.

인덱스를 활용하면, 데이터를 조회하는 SELECT 외에도 UPDATE나 DELETE의 성능이 함께 향상된다. 그러한 이유는 해당 연산을 수행하려면 해당 대상을 조회해야만 작업을 할 수 있기 때문이다.

// Mang이라는 이름을 업데이트 해주기 위해서는 Mang을 조회해야 한다.
UPDATE USER SET NAME = 'MangKyu' WHERE NAME = 'Mang';

만약 index를 사용하지 않은 컬럼을 조회해야 하는 상황이라면 전체를 탐색하는 Full Scan을 수행해야 한다. Full Scan은 전체를 비교하여 탐색하기 때문에 처리 속도가 떨어진다.

ex)

• 인덱스 없음: Full Table Scan, 2.5초
• 인덱스 있음: Index Scan, 0.01초

인덱스(index)의 관리

DBMS는 index를 항상 최신의 정렬된 상태로 유지해야 원하는 값을 빠르게 탐색할 수 있다. 그렇기 때문에 인덱스가 적용된 컬럼에 INSERT, UPDATE, DELETE가 수행된다면 각각 다음과 같은 연산을 추가적으로 해주어야 하며 그에 따른 오버헤드가 발생한다.

• INSERT: 새로운 데이터에 대한 인덱스를 추가함

  INSERT INTO users (id, name, email)
  VALUES (1, 'Alice', 'alice@example.com');

• DELETE: 삭제하는 데이터의 인덱스를 사용하지 않는다는 작업을 진행함

  DELETE FROM users
  WHERE id = 2;

• UPDATE: 기존의 인덱스를 사용하지 않음 처리하고, 갱신된 데이터에 대해 인덱스를 추가함

  UPDATE users
  SET name = 'Robert'
  WHERE id = 2;

Ex)

-- 인덱스 생성
CREATE INDEX idx_name ON users(name);
CREATE INDEX idx_name_email ON users(name, email);  -- 복합 인덱스

-- 인덱스 조회
SHOW INDEX FROM users;

-- 인덱스 삭제
DROP INDEX idx_name ON users;

-- 실행 계획으로 인덱스 사용 여부 확인
EXPLAIN SELECT * FROM users WHERE name = '홍길동';

인덱스(index)의 장점과 단점

장점

• 테이블을 조회하는 속도와 그에 따른 성능을 향상시킬 수 있다.
• 전반적인 시스템의 부하를 줄일 수 있다.

단점

• 인덱스를 관리하기 위해 DB의 약 10%에 해당하는 저장공간이 필요하다.
• 인덱스를 관리하기 위해 추가 작업이 필요하다.
• 인덱스를 잘못 사용할 경우 오히려 성능이 저하되는 역효과가 발생할 수 있다.

만약 CREATE, DELETE, UPDATE가 빈번한 속성에 인덱스를 걸게 되면 인덱스의 크기가 비대해져서 성능이 오히려 저하되는 역효과가 발생할 수 있다. 그러한 이유 중 하나는 DELETE와 UPDATE 연산 때문이다.

앞에서 설명한대로, UPDATE와 DELETE는 기존의 인덱스를 삭제하지 않고 '사용하지 않음' 처리를 해준다고 하였다. 만약 어떤 테이블에 UPDATE와 DELETE가 빈번하게 발생된다면 실제 데이터는 10만건이지만 인덱스는 훨씬 많이 존재하게 되어, SQL문 처리 시 비대해진 인덱스에 의해 오히려 성능이 떨어지게 될 것이다. 

인덱스(index)를 사용하면 좋은 경우

• 규모가 작지 않은 테이블

• INSERT, UPDATE, DELETE가 자주 발생하지 않는 컬럼

• JOIN이나 WHERE 또는 ORDER BY에 자주 사용되는 컬럼

• 데이터의 중복도가 낮은 컬럼

인덱스를 사용하는 것 만큼이나 생성된 인덱스를 관리해주는 것도 중요하다. 그러므로 사용되지 않는 인덱스는 바로 제거를 해주어야 한다.

인덱스 컬럼 순서의 중요성

인덱스는 단순히 존재하는 것만으로 성능을 보장하지 않습니다.
특히 복합 인덱스(Composite Index)의 경우, 컬럼의 순서에 따라 인덱스가 사용될 수 있는지 여부가 결정됩니다.

CREATE INDEX idx_name_email ON users(name, email);

위와 같은 인덱스가 있을 때, 다음과 같이 동작한다.

인덱스가 사용되는 경우

-- 첫 번째 컬럼(name)만 사용하는 경우
SELECT * FROM users WHERE name = '홍길동';

-- 첫 번째와 두 번째 컬럼을 모두 사용하는 경우
SELECT * FROM users WHERE name = '홍길동' AND email = 'hong@example.com';

인덱스가 사용되지 않는 경우

-- 두 번째 컬럼(email)만 조건에 사용하는 경우
SELECT * FROM users WHERE email = 'hong@example.com';

복합 인덱스는 선두 컬럼부터 차례로 사용되어야만 인덱스로 활용된다.
즉, (name, email) 인덱스는 name이 포함되어 있어야만 효력을 발휘하며, email 단독으로는 해당 인덱스를 사용할 수 없다.

이를 Left-most Prefix Rule(최좌측 접두사 규칙)이라고 한다.