DB 수동 Index

보통 DBMS 에서 알아서 관리 해주는 부분이 있기 때문에, 수동으로 무언가 최적화 작업을 하지 않는 경우가 많다.

(key 나 unique 제약조건이 있는 경우는 자동으로 인덱스를 생성해 준다)

그러나 특정 시점에서 조회해 올때 시간이 너무 걸린다면 (데이터가 많아진 시점) 수동으로 index 생성 하는것을 고려해야 한다.

 

Index를 수동으로 생성 함으로서 자료조회시 빠르게 조회 해 오는것이 특징이다. (보통 인덱스 라고 하면 B tree, B+ Tree 상에서 적용하는것을 의미한다.)

그러나 장점만 있는것은 아니기 때문에 무턱대고 모든 테이블에 Index를 수동으로 생성하거나 관리하게 되면 오히려 성능이 떨어진다. 

 

1. 수동 Index 를 고려해야 하는 시점

• 특정 테이블에서 조회를 할 때 속도가 느려져 서비스에 지장이 생기는 경우. 
  • 해당 테이블에서 특정 컬럼을 조회할때 속도가 느린경우
  • 조건문에 주로 사용되는 컬럼

 

이 경우에 수동으로 Index를 고려하는것이 좋다고 생각된다. 해당 컬럼에 대응하는 Index를 생성하여 조회를 빠르게 하는게 목적이다. 

 

그럼, 해당 컬럼이 어떤 컬럼인지, 또 어떤 테이블을 대상으로 해야 하는지 어떻게 정할까?

MySql 기준으로 보면,

 

1. slow_query_log 를 켜서 쿼리속도가 오래 걸리는 쿼리문을 찾는다

2. 해당 쿼리문에 EXPLAIN 을 붙여 결과를 확인한다. 

3. 주로 where 에 사용되는 조건 컬럼을 인덱싱 한다.

EXPLAIN SELECT * 
FROM 테이블명
WHERE 컬럼명 = '조건';

 

    - type 결과가 ALL 이면, 테이블 전체를 풀 스캔한다는 의미 이므로, 속도가 느리면 index 수동 설정을 고려해야 한다.

      - rows :  쿼리문 조회를 위해 읽어들인 열의 갯수 이다. 

      - key 는 만일 index 가 있다면, 사용중인 이름, 없다면 null 이 뜬다. 

 

인덱스를 설정 한 후 EXPLAIN 쿼리문을 조회해 보면, 풀스캔 대비 성능을 확인할 수 있다.

단, 만일 데이터가 적어서 인덱싱을 하여도 적용이 안되는 경우, 강제로 적용 하는 방법이 있다. 테스트 용도로만 써야한다.
(postgresql의 경우, SET enable_seqscan = off;)

 

인덱스 생성법은, 

CREATE INDEX 인덱스명 ON 테이블명(컬럼명) where 조건문;

//또는,
CREATE INDEX 인덱스명 ON 테이블명(컬럼명A, 컬럼명B) where 조건문;

//또는
CREATE INDEX 인덱스명 ON 테이블명(컬럼명A, 컬럼명B DESC) where 조건문;

 

• 인덱스명은 원하는 이름을 적어주면 되나, 구분이 되도록 테이블명과 컬럼명의 조합으로 하면 좋다.  특정 쿼리문 타게팅을 위해 조건이 필요할 경우에는 조건문을 추가한다.
• 생성 후 적용이 잘 되는지 EXPLAIN 을 통해 확인해야 한다. 
• 만일 2개 이상의 컬럼 조합 인덱스 생성시, 1번째 항목에 대해서만 인덱스가 적용된다. 예를들어 (a, b) 컬럼 조합으로 쿼리문을 작성하면, where a =  조건에는 인덱스가 되나 where b =  의 조건에는 인덱스가 동작하지 않는다.
• order by의 정렬 조건이 많은 select 문을 위해, 인덱스 생성시 미리 정렬 조건을 넣을 수도 있다. (이 경우, desc / asc 상관없이 하나만 있어도 된다. )
• where 조건문을 추가하는것은 복합 인덱싱을 하기 위함이다. (예를들어 deleted = false 인 항목 또한 인덱싱 할때)

 

클러스터 정렬관련

• UUID가 PK인 경우 자동으로 UUID 인덱싱을 생성
• 랜덤값이기 때문에 정렬이 제 멋대로 되어 버려서 인덱싱 성능이 매우 떨어짐. 
• 따라서 이럴 경우, 정렬에 유리한(예 : 생성일) 컬럼 기준으로 index를 추가하고, 해당 조건 기준으로 정렬 할 것을 명시하는 Cluster 적용 필요

CREATE INDEX idx_notifications_created_at
    ON notifications (created_at);
    
CLUSTER notifications USING idx_notifications_created_at;

 

위에 말했듯, 무분별하게 Index를 생성하면 오히려 역효과가 난다. 이유는, Index를 갱신하는데에도 시간이 소요되기 때문이다. 

즉, 쓰기 행동을 할 때에 쓰기 속도가 느려지게 된다. 

 

따라서,

잦은 Insert, Delete 등 테이블 데이터가 변경되는 경우 에는 사용을 하지 말아야 한다.

 

경우에 따라선 둘 다 해당되는 테이블이 있다. 이 경우는 비즈니스 로직상 조회와 쓰기 중 어느것이 더 중요한지 고려 한 후에 결정을 내려야 한다.

 

예를들어 User 테이블의 경우 조회, 쓰기 둘 다 많은 테이블일 가능성이 높은데, 특정 조회 조건에는 Index를 사용하는것이 더 좋을 수 있다.