RDB에서 인덱스를 사용하지 않는 케이스들

# 테이블 스캔

• 테이블 전체 레코드를 반환하는 쿼리는 당연하게도 인덱스를 사용하지 않는다.

SELECT * FROM articles

 

# 복합 인덱스

• deleted_at, user_id 순으로 복합인덱스가 생성되었다.

CREATE INDEX index_articles_on_deleted_at_and_user_id ON public.articles USING btree (deleted_at, user_id)

• 생성한 인덱스 순서에 맞게 쿼리를 작성하여야 한다.

# 인덱스와 동일한 순서
SELECT * FROM articles WHERE deleted_at IS NULL and user_id IN (1,100,2)

# 인덱스와 다른 순서
SELECT * FROM articles WHERE user_id IN (1,100,2) AND deleted_at IS NULL

• 쿼리 플래너에서 조건 순서를 자동으로 최적화 하는 경우도 있긴하지만, 기본적으로 인덱스 사용을 보장받으려면 순서에 맞게 조건을 작성하여야한다.

 

# IS NULL

SELECT * FROM articles WHERE deleted_at IS NULL

• 기본인덱스를 사용하는 경우도 있다.
• IS NULL 조건이 다른 복잡한 조건과 결합된 경우 인덱스를 사용하지 않을 수 있다.
• IS NULL 조회에 좀 더 효율적인 인덱스를 생성할 수도있다.

CREATE INDEX index_users_on_deleted_at_is_null ON articles(deleted_at) WHERE deleted_at IS NULL;

 

# 부정 연산

SELECT * FROM articles WHERE category_id != 1
SELECT * FROM articles WHERE category_id NOT IN (1,2)

• 부정 조건은 특정 값의 범위를 명확히 정의하지 못한다.
• 조건을 제외한 모든 값을 반환하여야 하기 때문에 풀스캔이 유리한 상황이 많다.
• 인덱스는 일반적으로 정렬된 순서로 값을 저장한다. 특정 값을 제외한 나머지 값을 찾는 부정 조건은 이러한 정렬된 구조를 효과적으로 활용하기는 어렵다.

# blacklist 방식에서 whitelist 방식으로 변경한다
SELECT * FROM articles WHERE category_id = 1 OR category_id = 2
SELECT * FROM articles WHERE category_id IN (3,4,5)

• 서로 참조하는 두 테이블에서 not in 연산이 이루어질때 left outer join으로 대체가 가능하다.
• 통계 정보에 따라 수행시간 차이가 있다.
  • 조인 키 분포가 고르지 않은 경우
  • 조인 키 인덱스 유무

# NOT IN 직접 사용
SELECT * FROM articles WHERE user_id NOT IN (1,2,3)

# LEFT OUTER JOIN 활용
SELECT * FROM articles a LEFT OUTER JOIN 
(SELECT id from users WHERE id IN (1,2,3)) as u ON a.user_id=u.id WHERE u.id IS NULL

 

# 와일드카드 사용

• 와일드카드를 문자열의 앞과 뒤에 모두 사용하면 인덱스를 사용하지 않을 수 있다.

SELECT * FROM articles WHERE title LIKE '%title' # 인덱스 X
SELECT * FROM articles WHERE title LIKE '%title%' # 인덱스 X

SELECT * FROM articles WHERE title LIKE 'title%' # 인덱스 O

• 위에서도 언급했지만 인덱스는 일반적으로 정렬된 순서로 값을 저장한다.
• %가 뒤에 있을 경우는 문자열 정렬 순서를 그대로 이용할 수 있어서 인덱스를 사용하지만, 반대의 경우는 정렬 순서를 활용하지 못해 인덱스를 사용하지 않는 경우가 발생할 수 있다.

 

# 함수나 연산자 사용

• 함수나 연산자를 사용하면 검색 대상 데이터에 대한 변환을 수행하므로, 인덱스의 순서와 일치하지 않을 수 있다.

SELECT * FROM articles WHERE UPPER(title) = 'TITLE'; # 인덱스 X
SELECT * FROM articles WHERE view_count + 1000 > 3000 # 인덱스 X

# 인덱스 O
SELECT * FROM articles WHERE view_Count > 2000

# 인덱스 O
# 하지만 아래와 같이 해결하려는 경우 title 값이 오로지 소문자임이 보장되어야만 한다.
# TitLe, titLe과 같이 대소문자 구분이 안된 케이스에서는 동일한 결과값이 보장이 되지 않는다.
SELECT * FROM articles WHERE title = 'title'

• UPPER 함수 기반의 인덱스를 생성하는 것도 해결 방법이 될 수 있다.

CREATE INDEX idx_upper_title ON articles (UPPER(title));
CREATE INDEX idx_year_created_at ON articles ((EXTRACT(YEAR FROM created_at)));

 

 

작성된 예시들은 모두 PostgreSQL 기준으로 작성되었습니다.