인덱스(INDEX) 생성 하는 예

CREATE TABLE users (
  id SERIAL PRIMARY KEY,
  username VARCHAR(50) NOT NULL,
  email VARCHAR(100) NOT NULL,
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

테이블은 다음과 같이 생성되었다고 가정

 

1. 단일 컬럼 인덱스

가장 기본적인 인덱스 생성 방법입니다. 예를 들어, username 컬럼에 인덱스를 생성하여 사용자 이름으로 검색할 때 성능을 향상시킬 수 있습니다.

 

CREATE INDEX idx_users_username ON users (username);

 

 

2. 유니크 인덱스

만약 email 컬럼의 값이 중복되지 않도록 보장하려면, 유니크 인덱스를 생성할 수 있습니다.

 

CREATE UNIQUE INDEX idx_users_email ON users (email);

 

이 인덱스는 중복된 이메일 입력을 방지하는 역할을 하며, 데이터 무결성을 유지할 때 유용합니다.

 

3. 복합 인덱스

여러 컬럼을 함께 사용하여 검색하는 경우, 복합 인덱스를 생성할 수 있습니다. 예를 들어, username과 created_at을 함께 자주 검색한다면:

 

CREATE INDEX idx_users_username_created_at ON users (username, created_at);

 

복합 인덱스는 인덱스 정의 순서에 따라 검색 조건에 활용되므로, 사용 패턴을 고려하여 컬럼 순서를 정하는 것이 중요합니다.

 

4. 인덱스 생성 시 고려사항

• 인덱스 크기 및 유지 비용: 인덱스는 검색 속도를 높여주지만, 데이터가 추가, 수정, 삭제될 때마다 인덱스도 갱신되어야 하므로 추가적인 오버헤드가 발생합니다.
• 쿼리 실행 계획 확인: 생성한 인덱스가 실제 쿼리에 사용되는지 확인하려면 EXPLAIN 명령어를 사용해서 실행 계획을 살펴볼 수 있습니다.
• CONCURRENTLY 옵션: 운영 환경에서 인덱스를 생성할 때는 테이블 락을 피하기 위해 CONCURRENTLY 옵션을 사용할 수 있습니다.

 

CREATE INDEX CONCURRENTLY idx_users_username ON users (username);

이 옵션은 인덱스 생성 도중에도 테이블에 대한 읽기/쓰기가 가능하도록 해줍니다.