대용량 데이터에서 페이징 처리는 데이터 양에 따라 시간이 오래 걸릴 수 있다. 이를 해결하는 방법 중 하나는 데이터베이스 샤딩(Sharding)과 분산 처리이지만, 우선적으로 인덱스를 잘 이해하고 쿼리를 최적화하는 방법이 중요하다.
1. 클러스터(Clustered) 인덱스
1-1) 특징 / 장단점
- 특징:
- 클러스터드 인덱스는 데이터가 물리적으로 저장되는 순서를 결정한다.
- 테이블의 기본 키(primary key) 또는 유니크 키로 생성.
- 클러스터드 인덱스를 사용할 경우, 해당 인덱스에 의해 데이터를 물리적으로 정렬하여 저장.
- 장점:
- 기본 키(primary key)나 유니크한 컬럼을 사용하여 데이터를 조회할 때 빠른 검색 성능을 제공.
- 단점:
- 한 테이블에는 하나의 클러스터드 인덱스만 있을 수 있다.
- 클러스터드 인덱스를 자주 수정하는 경우, 데이터의 물리적 위치를 다시 정렬해야 하므로 성능에 영향을 미칠 수 있다.
1-2) 사용
기본 키로 설정하면 자동으로 클러스터 인덱스가 설정됩니다. 아래 예시는 ARTICLE_ID를 클러스터 인덱스로 설정한 예시이다.
CREATE TABLE ARTICLE (
ARTICLE_ID BIGINT NOT NULL PRIMARY KEY,
TITLE VARCHAR(100) NOT NULL,
CONTENT VARCHAR(3000) NOT NULL,
BOARD_ID BIGINT NOT NULL,
WRITER_ID BIGINT NOT NULL,
CREATE_AT DATETIME NOT NULL,
MODIFIED_AT DATETIME NOT NULL
);
2. 세컨더리(Secondary) 인덱스
2-1) 특징 / 장단점
- 특징:
- 세컨더리 인덱스는 클러스터드 인덱스가 아닌 다른 컬럼에 대해 생성.
- 이 인덱스는 테이블의 데이터를 정렬하지 않으며, 대신 인덱스 페이지에 값과 해당 기본 키를 저장.
- 장점:
- 기본 키 외에도 자주 검색되는 컬럼에 대해 인덱스를 생성하여 검색 성능을 향상시킬 수 있다.
- 단점:
- 두 번의 조회가 필요하다. 첫 번째로 세컨더리 인덱스를 통해 기본 키를 얻고, 두 번째로 기본 키를 사용하여 클러스터드 인덱스에서 실제 데이터를 조회해야 한다.
2-2) 사용
BOARD_ID와 ARTICLE_ID로 세컨더리 인덱스를 생성합니다. ARTICLE_ID는 시간 순서에 따라 생성되는 컬럼이기 때문에, BOARD_ID와 ARTICLE_ID를 조합하여 정렬된 데이터가 만들어집니다.
💡 CREATE_AT컬럼이 아닌 ARTICLE_ID가 시간 순서라고 말한 이유는 ARTICLE_ID 컬럼은 데이터 분산 환경을 고려해 시퀀스를 할당하는 SnowFlake 기법으로 시간 순서에 따라 만들어지는 컬럼이기 때문이다.
-- 세컨더리 인덱스 생성
CREATE INDEX IDX_BOARD_ID_ARTICLE_ID ON ARTICLE(BOARD_ID ASC, ARTICLE_ID DESC);
반응형
3. 커버링(Covering) 인덱스
3-1) 특징 / 장단점
- 특징:
- 커버링 인덱스는 쿼리에서 필요한 모든 컬럼을 인덱스에 포함시킨 인덱스입니다.
- 즉, 해당 인덱스만으로 쿼리에서 요청된 데이터를 모든 컬럼을 포함해 반환할 수 있습니다.
- 쿼리 성능을 최적화하기 위해, 자주 사용되는 컬럼들을 인덱스에 포함시켜서 디스크 I/O를 줄일 수 있습니다.
- 장점:
- 쿼리 성능을 최적화하며, 디스크 접근 횟수를 줄이는 효과가 있습니다. 데이터베이스는 인덱스에서 바로 필요한 데이터를 얻을 수 있습니다.
- 단점:
- 인덱스 크기가 커질 수 있기 때문에 메모리 사용과 디스크 공간에 영향을 줄 수 있습니다.
3-2) 사용
세컨더리 인덱스로 설정된 값을 조회할 때, Using Index 방식으로 인덱스의 데이터만으로 조회가 가능하다. 클러스터 인덱스를 거치지 않기 때문에 빠르게 조회할 수 있다.
SELECT BOARD_ID, ARTICLE_ID FROM ARTICLE
WHERE BOARD_ID = 1
ORDER BY ARTICLE_ID DESC
LIMIT 30 OFFSET 159999;
4. 응용 : 빅데이터 조회
커버링 인덱스로 빠르게 조회한 30건의 데이터에 대해서만 ARTICLE_ID를 조인하여 데이터를 추출하면 빠르게 조회된다. 이렇게 함으로써 디스크 접근 횟수를 줄이고 성능을 최적화할 수 있다.
SELECT * FROM (
SELECT ARTICLE_ID FROM ARTICLE
WHERE BOARD_ID = 1
ORDER BY ARTICLE_ID DESC
LIMIT 30 OFFSET 159999
) T LEFT JOIN ARTICLE ON T.ARTICLE_ID = ARTICLE.ARTICLE_ID;
반응형