Index와 Query문 조건의 관계

"분명 Index를 생성했음에도 불구하고 왜 인덱싱이 되지 않는걸까?"
Index는 어떻게 생성해야 할까?

 

 

"인덱스를 생성해도 인덱싱이 안되는 경우가 존재할까요?"

네, 존재합니다. 제가 직접 겪은 문제입니다.

먼저, 인덱스를 왜 사용하는지에 대해 간략히 짚고 넘어간 후, 왜 인덱스를 생성했음에도 인덱싱이 안됬는지에 대해 알아보겠습니다.

 

인덱스

인덱스는 데이터베이스에서 검색 기능을 향상시키기 위해 사용되는 데이터 구조를 말합니다. 테이블의 데이터에 빠르게 접근하도록 돕는 일종의 색인 역할을 합니다. 쉽게 말해, 책의 목차를 생각하면 됩니다. 인덱스를 사용하지 않는 경우에는 찾고자하는 데이터가 존재하는 테이블을 Full Scan하여 조회에 더 많은 시간이 걸리게 됩니다.

 

 

예시 코드

api를 호출하게되면, 이 코드는 다음과 같은 sql query문을 날리게 됩니다.  

꽤나 긴 쿼리문이 DB로 flush()되게 됩니다.

select
        pe1_0.id,
        pe1_0.content,
        ue1_0.id,
        ue1_0.name,
        ue1_0.profile_image_url,
        pe1_0.reg_date,
        pe1_0.mod_date,
        pe1_0.comment_count,
        pe1_0.like_count,
        (le1_0.target_id is not null 
        and le1_0.target_type is not null 
        and le1_0.user_id is not null) 
    from
        community_user_post_queue upqe1_0 
    join
        community_post pe1_0 
            on upqe1_0.post_id=pe1_0.id 
    join
        community_user ue1_0 
            on upqe1_0.author_id=ue1_0.id 
    left join
        community_like le1_0 
            on pe1_0.id=le1_0.target_id 
            and le1_0.target_type='POST' 
            and le1_0.user_id=1
    where
        upqe1_0.user_id=1
    order by
        upqe1_0.post_id desc 
    limit
        20

 

"그렇다면, 현재 이 쿼리문은 어떤 전략을 통해 데이터를 찾게되는 걸까요?"

explain 명령어를 사용해서 확인해보도록 하겠습니다. 결과는 다음과 같습니다.

 

표에서 type의 값이 ALL임을 확인할 수 있습니다. 여기서, ALL은 테이블을 Full Scan했다는 의미를 나타냅니다.

인덱스를 생성해주지 않았으니, 테이블을 Full Scan해주는 것은 당연합니다.

 

이제, Full Scan하는 것을 막기 위해서 인덱스를 생성해주려고 합니다.

제가 생성해준 쿼리문은 다음과 같습니다. 

CREATE INDEX `idx_community_user_post_queue_post_id_user_id`  ON `communityfeed`.`community_user_post_queue` (post_id, user_id)

쿼리문을 보면 알 수 있듯이 post_id와 user_id를 기준으로 인덱싱을 해주었습니다. 

이제 인덱싱을 해주었으니, 데이터를 찾는 전략이 ALL이 아닌 인덱스의 사용을 의미하는 ref로 변경되어야 합니다.

 

다시한번, explain 명령어를 사용해서 확인해보겠습니다. 결과는 다음과 같습니다.

 

분명히, 인덱스를 생성했음에도 불구하고 ALL문자가 보입니다. ALL문자는 인덱스가 적용되지 않음을 의미합니다.

"왜 적용이 안됬을까요?"

 

결론부터 말하자면, 인덱스는 쿼리문에 나오는 조건의 순서와 밀접한 관련이 있습니다.

예시 쿼리문의 조건을 나타내는 부분은 다음과 같습니다.

 where
        upqe1_0.user_id=1
    order by
        upqe1_0.post_id desc

 

쿼리 문에서는 user_id가 1번인 조건을 먼저 처리한 후, post_id를 내림차순하고 있습니다.

 

인덱스가 적용이 안된 근본적인 원인은 쿼리문의 인덱스를 만들어 줄때, 조건 순서와 반대로 (post_id, user_id)로 만들어주었기 때문입니다. 정상적인 인덱스를 생성하려면 (post_id, user_id)가 아닌 (user_id, post_id) 순서대로 만들어야 합니다.  

 

기존에 생성했던 index를 삭제하고, (user_id, post_id) 순서대로 새로 인덱스를 생성하고 조회 전략을 보도록 하겠습니다.

인덱스를 생성하는 쿼리문은 다음과 같습니다.

CREATE INDEX `idx_community_user_post_queue_post_id_user_id`  ON `communityfeed`.`community_user_post_queue` (user_id,post_id)

 

기존 코드는 같기에 맨 마지막 부분인 (post_id, user_id)를 (user_id, post_id)로 변경해주었습니다.

 

변경 후 결과

type컬럼을 보면 ALL에서 ref로 변경된걸 확인할 수 있습니다. 인덱스가 잘 적용이 되었습니다.

 

 

"인덱스와 쿼리문의 조건의 순서가 중요한건 알겠는데, 조건의 순서와 인덱스가 무슨 상관이 있는건데?" 라고 생각할 수 있습니다.

이 질문에 대한 답은 인덱스의 작동 원리에 있습니다.

 

인덱스 작동 원리

 

데이터베이스의 인덱스는 B-Tree 구조를 기반으로 동작하며, 쿼리의 검색 조건에 따라 효율적으로 데이터를 탐색합니다.

B-Tree 기반 인덱스는 왼쪽에서 오른쪽으로 순차적으로 컬럼을 비교하며 검색을 수행합니다.

(A, B, C) 순서로 정의된 인덱스는 A → B → C 순서로 검색을 수행합니다.

 

좀 더 쿼리와 밀접하게 접근해 보면 다음과 같습니다.

(1) WHERE A = ? AND B = ? AND C = ? → 모든 컬럼이 인덱스를 효율적으로 사용합니다.

(2) WHERE A = ? AND B = ? → 인덱스를 부분적으로 사용합니다.

(3) WHERE B = ? AND C = ? → A 컬럼을 건너뛰면 인덱스를 사용할 수 없습니다. 

 

정리하면, 컬럼 순서가 쿼리 조건과 맞지 않으면, DB는 인덱스를 무시하고 Full Table Scan을 통해서 조회 요청이 들어온 데이터를 찾게 됩니다.  

 

"그렇다면, 이전에 생성한 인덱스 코드는 (1), (2), (3)번 중에 어떤 경우에 해당할까요?"

CREATE INDEX `idx_community_user_post_queue_post_id_user_id`  ON `communityfeed`.`community_user_post_queue` (post_id, user_id)

 

(3)번의 경우에 해당이 됩니다. 이전 쿼리문에서는 (post_id,user_id) 순서대로 인덱스를 생성해주었습니다. 

그러나, 쿼리문은 WHERE user_id = ? OrderBy post_id 순서로 작성되어 있으니 컬럼을 건너뛰어 인덱스를 사용할 수 없는 상황이 됩니다. 

 

 

정리

인덱스를 생성할 땐, 반드시 쿼리문에 사용된 조건의 순서를 따라서 생성해야 합니다.

그렇지 않을 경우, 인덱스는 의미가 없어지게 되고, 데이터를 조회하면 Full Scan을 사용하게 됩니다.