postgreSQL INDEX

유튜브 강의영상들, ai를 참조해 작성한 게시글입니다.

---

INDEX 

만약 1000만명의 유저가 가입 순서에 따라 나열되어있다고 생각해보자.

user_id [pk]	nick_name [varchar(20)]	email [varchar(50)]	created_at [timestamp]
1	jo	asd@asd.asd	...
2	woang	qwe@qwe.qwe	...
~	~	~	~
1000000	Kim	qaz@qaz.qaz	...

각자의 닉네임이 모두 다른데, 그중에서 Kim을 찾으려고 한다면 1000만개의 닉네임을 선형적으로 탐색하기 때문에 어마어마한 시간이 걸릴것이다. 그래서 사용하는 방법이 nick_name 컬럼에 대한 인덱스를 생성하는 것이다. (보통 처음부터 만들어놓고 시작하겠지만)

 

인덱스를 만들면 postgresql은 캐릭터 테이블을 전부 스캔해 각 행의 nick_name 값을 추출해 1000만개의 (nick_name, CTID) 쌍을 만든다. CTID는 실제 물리적 행 위치. 그리고 1000만개의 닉네임을 B-tree를 사용해 정렬한다. 메모리가 부족하면 디스크 정렬까지 간다고 함. 정렬된 데이터를 여러 페이지로 나눠 저장, 리프 노드 구성, 상위 노드 생성, 루트 생성한다.

postgresql의 기본 인덱스 타입은 B-Tree

이렇게 만들어진 인덱스 파일은 테이블과 별도의 파일로 관리되고 추가적으로 공간을 잡아먹는다. 대신 테이블에서 nick_name 을 검색 시 더이상 테이블에서 선형탐색을 하지 않고, 정렬된 인덱스에서 빠르게 검색한 뒤 CTID로 실제 행의 위치를 알아내기 때문에 매우 빠르다.

 

값을 넣을때마다 인덱스 수정해줘야하기 때문에 하나의 테이블에 인덱스가 많이 설정되어있다면 공간도 많이 먹고 insert 가 상대적으로 오래걸릴 수 있다. 하지만 select가 매우 빨라지기 때문에 트레이드 오프가 있다.

INDEX를 만들기 좋은 컬럼

• WHERE 을 자주 사용할 컬럼
• JOIN 을 자주 사용할 컬럼
• ORDER BY 를 자주  사용할 컬럼
• 범위 검색이 많은 컬럼
• 숫자 - 비교와 정렬이 빠르고 저장공간이 작음.
• 날짜 - 내부적으로 숫자처럼 저장되고 보통 순차적으로 증가함
• email - 일치 검색이 매우 유용함

INDEX를 만들기 안좋은 컬럼

• 성별, is_active같이 중복이 많은 값 - 선택도가 떨어지면 인덱스 써도 이득이 없다.
• 자주 업데이트 되는 컬럼 - 인덱스는 쓰기성능이 안좋기 때문
• 인덱스가 필요 없을 정도로 작은 테이블
• 검색을 거의 안하는 컬럼

사용로그나 결제정보같이 인덱스를 만들기 안좋아도 필수적으로 사용해야 하는 경우는 최소한의 인덱스만 사용하는 것이 좋다.

postgreSQL에서 INDEX 만들기

인덱스 생성

CREATE INDEX new_index_name ON table_name (column_name); -- 일반 인덱스
CREATE UNIQUE INDEX new_index_name ON table_name (column_name); -- 유니크 인덱스
CREATE INDEX new_index_name ON table_name (column1, column2); -- 복합 인덱스

-- 정렬 방향도 지정 가능
CREATE INDEX new_index_name ON table_name (column1 ASC, column2 DESC); -- 복합 인덱스

유니크 인덱스?

중복값 허용하지 않는 인덱스

테이블 생성 시 컬럼이 중복 허용 상태였더라도, 이후에 UNIQUE 인덱스를 생성하면 그 시점부터 중복이 금지된다. 단, 이미 중복 데이터가 존재하면 UNIQUE 인덱스 생성은 실패한다.

복합인덱스?

두 값을 가지고 정렬. 만약 (nickname, age) 로 설정한다면 nickname먼저 정렬하고 nickname이 같으면 age로 정렬한다.

복합인덱스를 탈 때는 아래와 같이 왼쪽컬럼부터 사용해야 한다. (Leftmost Prefix Rule)

WHERE nickname = 'Kim';
WHERE nickname = 'Kim' AND age = 20;
WHERE nickname = 'Kim' AND age > 18;
WHERE age = 20; -- 잘 못 쓰는 경우

 

인덱스 제거

DROP INDEX index_name;

 

그 외 INDEX 관련 정보

• primary key, unique 컬럼은 자동으로 인덱스 만들어진다.
• foreign key는 자동생성x - 외래키는 값이 부모테이블에 존재하는지 검증을 위한거지 빠른검색용이 아니기 때문
• 쿼리문이 실제로 어떤 INDEX를 사용하는지는 EXPLAIN으로 확인할 수 있다.

테이블의 인덱스 정보 확인하는 SQL

SELECT * FROM pg_indexes WHERE tablename = '테이블 이름';

 

 

추가

부분 인덱스

특정 조건에 맞는 데이터만 인덱싱하여, 불필요한 데이터의 인덱싱을 방지

CREATE INDEX index_name ON table_name (column_name) WHERE condition;

CREATE INDEX active_users_index ON users (email) WHERE status = 'active';

 

인덱스 힌트

SQL을 실행할 때 어떤 인덱스를 사용할지는 db의 옵티마이저가 결정한다. mysql에서는 사용자가 인덱스를 선택할 수 있는 인덱스 힌트 기능을 제공하지만 postgresql에서는 제공하지 않는다.

 

커버링 인덱스

조회하려는 데이터를 index가 모두 커버할 때 조회 성능이 더 빠르다.

예를 들어 (nickname, created_at) 튜플을 복합인덱스로 설정했는데 내가 nickname과 created_at을 찾는다면 굳이 실제 table까지 가지 않아도 index안에서 필요한 데이터를 가져올 수 있어서 성능이 더 빠른 것.

 

해시 인덱스

조회 시간복잡도 1

rehashing 부담 있음 - array로 만들어져있는데 꽉차면 새 공간 구해서 copy하니까 이럴 때 성능 부담.

equality ( = ) 비교만 가능. range 비교 불가능

복합인덱스 불가

CREATE INDEX idx_user_id_hash 
ON users USING hash (user_id);