기본 개념

1. 인덱스란 무엇인가?

인덱스는 데이터베이스 엔진이 데이터를 빠르게 검색하기 위해 사용하는 특별한 조회 테이블입니다. 책의 뒤쪽에 있는 색인(index)처럼, 인덱스는 원하는 데이터를 빠르게 찾을 수 있게 도와줍니다. 데이터베이스에서는 인덱스가 테이블의 데이터를 가리키는 포인터 역할을 하여, SELECT 쿼리와 WHERE 절의 성능을 크게 향상시킵니다.

2. 인덱스의 장단점

인덱스는 데이터베이스의 성능을 크게 향상시킬 수 있지만, 몇 가지 단점도 있습니다

• SELECT 쿼리 속도 향상: 인덱스는 SELECT 쿼리와 WHERE 절의 성능을 크게 개선합니다.
• 데이터 수정 속도 저하: 반면, INSERT, UPDATE, DELETE 같은 데이터 수정 작업에서는 인덱스가 업데- 이트되어야 하기 때문에 속도가 느려질 수 있습니다.
• 데이터에는 영향 없음: 인덱스를 생성하거나 삭제해도 실제 데이터에는 영향을 주지 않습니다. 단지 데이터 접근 방식만 바뀔 뿐입니다.

3. 인덱스 생성 기본 구문

CREATE INDEX 구문을 사용하여 테이블에 인덱스를 생성할 수 있습니다. 기본 구문은 다음과 같습니다

CREATE INDEX index_name ON table_name (column_name);

여기서 인덱스의 이름, 테이블, 인덱스를 생성할 열(column) 등을 지정할 수 있습니다.

PostgreSQL에서의 인덱스 타입

PostgreSQL은 다양한 유형의 인덱스를 제공합니다. 각 인덱스는 특정 유형의 쿼리에 적합한 알고리즘을 사용합니다

• B-tree (기본값): 대부분의 쿼리에 적합하며, 특히 등가 비교와 범위 쿼리에 유용합니다.
• Hash: 등가 비교에 최적화되어 있습니다.
• GiST: 전체 텍스트 검색과 같은 고급 검색에 유용합니다.
• SP-GiST: 기하 데이터와 같은 특정 데이터 타입에 적합합니다.
• GIN: 전체 텍스트 검색, 특히 문서 검색에 효과적입니다.

1. 단일 컬럼 인덱스

단일 컬럼 인덱스는 하나의 특정 열에 대해 생성되는 인덱스입니다. 자주 필터링하거나 정렬하는 열에 유용합니다.

장점:

• 특정 열(column)에서의 데이터 조회 속도를 크게 향상시킵니다.
• 인덱스가 적용된 열을 기준으로 한 정렬 작업이 빠르게 수행됩니다.

사용 상황:

• WHERE 절에서 자주 사용되는 특정 열에 대해 쿼리 성능을 향상시키고자 할 때.
• ORDER BY나 GROUP BY와 같이 특정 열을 기준으로 정렬 또는 그룹화 작업을 수행할 때 유용합니다.

CREATE INDEX index_name ON table_name (column_name);

2. 다중 컬럼 인덱스

다중 컬럼 인덱스는 두 개 이상의 열에 대해 생성되는 인덱스입니다. 여러 열을 기준으로 자주 필터링하거나 정렬할 때 유용합니다.

장점:

• 여러 열을 동시에 기준으로 하는 쿼리에서 성능을 최적화할 수 있습니다.
• 다중 조건 검색 시, 별도의 단일 컬럼 인덱스를 사용하는 것보다 효율적입니다.

사용 상황:

• WHERE 절에서 두 개 이상의 열이 자주 함께 사용되는 경우.
• 복합적인 조건을 바탕으로 데이터를 조회하거나 정렬하는 쿼리에서 유용합니다.

CREATE INDEX index_name ON table_name (column1_name, column2_name);

고려 사항: 쿼리에서 여러 열을 기준으로 필터링할 경우, 다중 컬럼 인덱스를 사용하면 성능이 크게 향상될 수 있습니다.

3. 유니크 인덱스

유니크 인덱스는 인덱스가 적용된 열에 중복된 값이 들어가는 것을 방지합니다. 유니크 제약 조건과 유사하지만, 쿼리 성능도 개선됩니다.

장점:

• 중복된 데이터 입력을 방지하여 데이터 무결성을 보장합니다.
• UNIQUE 제약 조건과 함께 데이터베이스의 무결성을 유지하면서 쿼리 성능도 개선합니다.

사용 상황:

• 특정 열의 값이 유일해야 하는 경우 (예: 이메일 주소, 사용자 ID 등).
• 데이터베이스 내에서 중복된 값이 존재해서는 안 되는 상황에서 유용합니다.

CREATE UNIQUE INDEX index_name ON table_name (column_name);

4. 부분 인덱스

부분 인덱스는 테이블의 특정 부분에만 적용되는 인덱스입니다. 조건식을 사용하여 인덱스가 적용될 데이터를 지정할 수 있습니다.

장점:

• 특정 조건에 맞는 데이터만 인덱싱하여, 불필요한 데이터의 인덱싱을 방지합니다.
• 인덱스 크기를 줄여서 저장 공간을 절약하고, 특정 조건에 최적화된 쿼리 성능을 제공합니다.
  사용 상황:
• 특정 조건을 만족하는 데이터만 자주 조회되는 경우.
• 전체 데이터를 인덱싱하기보다는 특정 범위나 상태에 따라 제한된 데이터를 빠르게 조회하고자 할 때 유용합니다.

CREATE INDEX index_name ON table_name (column_name) WHERE condition;

CREATE INDEX active_users_index ON users (email) WHERE status = 'active';

5. 암시적 인덱스

암시적 인덱스는 기본 키나 유니크 제약 조건을 생성할 때 데이터베이스가 자동으로 생성하는 인덱스입니다. 사용자가 명시적으로 생성하지 않아도 됩니다.

장점:

• 데이터베이스가 자동으로 생성하므로 추가적인 작업 없이 데이터 무결성을 보장합니다.
• 기본 키(primary key)나 유니크 제약 조건에서 자동 생성되어, 테이블 생성 시 이미 최적화된 인덱스를 제공합니다.

사용 상황:

• 기본 키나 유니크 제약 조건을 설정하는 경우.
• 별도의 인덱스 설정이 필요 없는 상황에서 데이터 무결성과 성능을 보장합니다

6. 인덱스 목록 조회

PostgreSQL에서 테이블의 모든 인덱스를 조회하려면 \d table_name 명령어를 사용합니다. 이는 인덱스와 테이블의 다른 정보를 보여줍니다.

# \d company
데이터베이스 전체의 인덱스를 조회하려면 다음 명령어를 사용
# \di

7. 인덱스 삭제

더 이상 필요하지 않은 인덱스는 삭제할 수 있습니다. 그러나 인덱스를 삭제하면 그 인덱스가 최적화하던 쿼리 성능이 저하될 수 있으므로 주의해야 합니다.

DROP INDEX index_name;
DROP INDEX salary_index;

8. 인덱스를 피해야 할 때

인덱스는 쿼리 성능을 향상시키지만, 항상 유용한 것은 아닙니다. 다음과 같은 경우에는 인덱스 사용을 재고할 필요가 있습니다

• 작은 테이블: 작은 테이블에서는 인덱스의 오버헤드가 이점을 상쇄할 수 있습니다.
• 빈번한 대량 업데이트: 대량 업데이트나 삽입 작업이 빈번한 경우, 인덱스가 성능 저하를 일으킬 수 있습니다.
• 많은 NULL 값을 포함하는 열: NULL 값이 많은 열은 인덱스를 사용해도 큰 성능 향상을 기대하기 어렵습니다.
• 자주 수정되는 열: 자주 수정되는 열에 인덱스를 적용하면 오히려 성능이 저하될 수 있습니다.

9. 실습: COMPANY 테이블 생성 및 인덱스 적용

이제 실제로 테이블을 생성하고 데이터를 삽입한 후, 다양한 인덱스를 적용해 보겠습니다.

-- 1. COMPANY 테이블 생성
CREATE TABLE COMPANY (
    ID SERIAL PRIMARY KEY,
    NAME TEXT NOT NULL,
    AGE INT NOT NULL,
    ADDRESS CHAR(50),
    SALARY REAL
);

-- 2. 데이터 삽입
INSERT INTO COMPANY (NAME, AGE, ADDRESS, SALARY)
VALUES ('John', 28, 'New York', 45000.00);

INSERT INTO COMPANY (NAME, AGE, ADDRESS, SALARY)
VALUES ('Anna', 24, 'California', 52000.00);

INSERT INTO COMPANY (NAME, AGE, ADDRESS, SALARY)
VALUES ('Mike', 32, 'Texas', 60000.00);

INSERT INTO COMPANY (NAME, AGE, ADDRESS, SALARY)
VALUES ('Sara', 29, 'Florida', 70000.00);

-- 3. SALARY 컬럼에 인덱스 생성
CREATE INDEX salary_index ON COMPANY (SALARY);

-- 4. NAME 컬럼에 유니크 인덱스 생성
CREATE UNIQUE INDEX name_unique_index ON COMPANY (NAME);

-- 5. AGE가 30 이상인 행에만 인덱스 생성 (부분 인덱스)
CREATE INDEX age_above_30_index ON COMPANY (AGE) WHERE AGE >= 30;

-- 6. 인덱스 목록 조회
\d company

-- 7. 인덱스 삭제
DROP INDEX salary_index;

이러한 SQL 코드를 통해 테이블을 생성하고, 데이터를 삽입한 뒤 다양한 인덱스를 적용할 수 있습니다. 각 인덱스는 특정 쿼리의 성능을 최적화하는 데 도움이 됩니다.

결론

이 블로그 포스트에서는 PostgreSQL에서 제공하는 다양한 인덱스의 장점과 사용 상황을 설명했습니다. 올바른 인덱스를 사용하면 데이터베이스 성능을 크게 향상시킬 수 있지만, 잘못 사용하면 성능 저하를 초래할 수 있습니다. 각 인덱스의 특성을 이해하고 상황에 맞게 활용하여 데이터베이스의 최적화를 이루어보세요!