[Postgresql] 인덱스(INDEX) 개념 및 효과적인 인덱스 설계 방법 정리

🚀  들어가며...

• 실무에서 너무나도 많이 쓰이는 인덱스에 대해 알아보고, 효과적인 인덱스 설계 방법에는 어떠한 것들이 있는지 정리해보았습니다!

 

📑 내용

인덱스의 개념

인덱스는 테이블의 동작속도(조회)를 높여주는 자료구조입니다. 인덱스로 데이터의 위치를 빠르게 찾아주는 역할이고

대표적인 예로는 책 뒷편에 '색인'이 인덱스의 역할과 동일하다고 볼 수 있습니다.

 

인덱스가 설정되지 않았다면 Table Full Scan이 일어나 성능이 저하되거나 치명적인 장애가 발생합니다.

하지만, 인덱스의 단점도 존재하는데, 조회속도는 빨라지지만 UPDATE, INSERT, DELETE의 속도는 저하된다는 단점이 있습니다. (Table의 index 색인 정보를 갱신하는 추가적인 비용 소요)

때문에 효율적인 인덱스 설계로 단점을 최대한 보완하는 방법을 생각해보아야 합니다.

-> 최근에 저는 POSTGRESQL에서 제공하는 BRIN INDEX(Block Range Index)를 알게되었는데 B-TREE 인덱스보다 쿼리 퍼포먼스가 더 좋다고 합니다. 추후 BRIN INDEX관련하여 블로그 글 작성 예정입니다!

 

인덱스의 특징

인덱스는 하나 혹은 여러 개의 컬럼에 대해 설정할 수 있습니다. (단일 여러개 또는 여러컬럼을 묶어 복합인덱스)

WHERE절을 사용하지 않고 인덱스가 걸린 컬럼을 조회하는 것은 성능에 아무런 영향이 없습니다.

 

ORDER BY 와 GROUP BY에 대한 INDEX

INDEX는 ORDER BY와 GROUP BY에도 영향을 끼치는데 다음과 같은 경우에는 INDEX를 타지 않습니다.

• ORDER BY 인덱스컬럼1, 컬럼2 : 복수의 키에 대해서 ORDER BY를 사용한 경우
• WHERE 컬럼1='값' ORDER BY 인덱스 컬럼 : 연속하지 않은 컬럼에 대해 ORDER BY를 실행한 경우
• ORDER BY 인덱스컬럼1 DESC, 인덱스컬럼2 ASC : DESC와 ASC를 혼합해서 사용한 경우
• GROUP BY 컬럼1 ORDER BY 컬럼2 : GROUP BY와 ORDER BY의 컬럼이 다른 경우
• ORDER BY ABS(컬럼) : ORDER BY 절에 다른 표현을 사용한 경우

 

다중 컬럼 인덱스

다중 컬럼 인덱스는 두개 이상의 필드를 조합해서 생성한 INDEX입니다. 1번째 조건과 이를 만족하는 2번째 조건을 함께 INDEX해서 사용합니다. (MySQL은 INDEX에 최대 15개 컬럼으로 구성 가능)

다중 컬럼 인덱스는 단일 컬럼 인덱스 보다 더 비효율적으로 INDEX/UPDATE/DELETE를 수행하기 때문에 신중해야합니다.

때문에 가급적 UPDATE가 안되는 값을 선정해야 합니다.

 

단일인덱스와 다중 컬럼 인덱스의 차이점

Table1(단일 인덱스)

CREATE TABLE table1(
    uid INT(11) NOT NULL auto_increment,
    id VARCHAR(20) NOT NULL,
    name VARCHAR(50) NOT NULL,
    address VARCHAR(100) NOT NULL,
    PRIMARY KEY('uid'),
    key idx_name(name),
    key idx_address(address)
)

Table2(다중 컬럼 인덱스)

CREATE TABLE table2(
    uid INT(11) NOT NULL auto_increment,
    id VARCHAR(20) NOT NULL,
    name VARCHAR(50) NOT NULL,
    address VARCHAR(100) NOT NULL,
    PRIMARY KEY('uid'),
    key idx_name(name, address)    
)

QUREY문

SELECT * FROM table1 WHERE name='홍길동' AND address='경기도';

table1의 경우에 각각 컬럼(name),(address)에 INDEX가 걸려있기 때문에 MySQL은 name컬럼과 address컬럼을 보고 둘 중에 어떤 컬럼의 수가 더 빠르게 검색되는지 판단 후 빠른쪽을 먼저 검색하고 그 다음 다른 컬럼을 검색하게 됩니다.

table2의 경우 바로 원하는 값을 찾는데 그 이유는 INDEX를 저장할 때 name과 address를 같이 저장하기 때문입니다. 즉, name과 address의 값을 같이 색인하고 검색에서도 '홍길동경기도'로 검색을 시도하게 됩니다. 이렇게 사용할 경우 table1보다 table2의 경우가 더 빠른 검색을 할 수 있습니다.

 

그렇지만 다중 컬럼 인덱스를 아래와 같이 사용하면 INDEX를 타지 않습니다.

SELECT * FROM table2 WHERE address='경기도';

이 경우에는 다중 컬럼 인덱스로 설정되어 있던 name이 함께 검색이 되지 않으므로 INDEX의 효과를 볼 수가 없습니다.

하지만 조건값을 name='홍길동'으로 준다면 B*Tree 자료구조 탐색으로 인해 name컬럼은 인덱스가 적용됩니다. 

예를들어 key idx_name(name, address, age) 일때 WHERE name = ? AND address = ? 는 인덱스가 적용되지만 WHERE name = ? AND age = ? 에서 age 컬럼은 인덱스 적용이 되지 않습니다.

다중 컬럼 인덱스를 사용할 때는 INDEX로 설정해준 제일 왼쪽컬럼이 WHERE절에 사용되어야 합니다. (중요)

 

설계방법

• 무조건 많이 설정하지 않습니다. (한 테이블당 3~5개가 적당 목적에 따라 상이)
• 조회시 자주 사용하는 컬럼
• 고유한 값 위주로 설계
• 카디널리티가 높을 수록 좋습니다 (= 한 컬럼이 갖고 있는 중복의 정도가 낮을 수록 좋습니다.)
• INDEX 키의 크기는 되도록 작게 설계
• PK, JOIN의 연결고리가 되는 컬럼
• 단일 인덱스 여러 개 보다 다중 컬럼 INDEX 생성 고려
• UPDATE가 빈번하지 않은 컬럼
• JOIN시 자주 사용하는 컬럼
• INDEX를 생성할 때 가장 효율적인 자료형은 정수형 자료(가변적 데이터는 비효율적)

 

인덱스 문법

인덱스 생성

-- 단일 인덱스
CREATE INDEX 인덱스이름 ON 테이블이름(필드이름1)

-- 다중 컬럼 인덱스
CREATE INDEX 인덱스이름 ON 테이블이름(필드이름1, 필드이름2, ...)

 

인덱스 조회

SHOW INDEX FROM 테이블이름

 

UNUQUE 인덱스 생성(중복 값을 허용하지 않는 인덱스)

-- 단일 인덱스
CREATE UNIQUE INDEX 인덱스 이름 ON 테이블이름(필드이름1)
-- 다중 컬럼 인덱스
CREATE UNIQUE INDEX 인덱스 이름 ON 테이블이름(필드이름1, 필드이름2, ...)

 

인덱스 정렬(인덱스 생성 시점에 필드의 정렬방식 설정)

CREATE INDEX 인덱스이름 ON 테이블이름 (필드이름 DESC)
CREATE INDEX 인덱스이름 ON 테이블이름 (필드이름 ASC)

 

인덱스 삭제

ALTER TABLE 테이블이름 DROP INDEX 인덱스이름;

 

인덱스 추가

ALTER TABLE 테이블이름 ADD (UNIQUE)INDEX 인덱스이름(컬럼명1, 컬럼명2...);

 

🙋🏻‍♂️ 후기

다음편에는 POSTGRESQL 9.5버전 이후로 제공하는 BRIN INDEX에 대해 공부한 후 블로그 글을 통해 내용을 공유하도록 하겠습니다. 인덱스를 잘 모르고 사용한다면 인덱스를 걸어도 사용이 안될 수 있으니 이 점 유의하여 사용하여야 합니다.

혹시 실무에서 인덱스를 사용하시다가 이슈사항이 발생하여 트러블슈팅을 해보신 경험이 있다면 서로 공유하면 좋을것 같습니다.

오늘도 글 읽어주셔서 감사합니다~!