[PostgreSQL 공부하기] Index는 어떤 column에 생성해야될까?

Database page:

Database에서 page는 DB가 data를 저장/관리하는 가장 기본적인 단위이다.

쉽게 비유하자면, 

Database	책
Page	Page
Row	Sentence

하지만 (DB마다 조금씩 다르겠지만), row는 page 용량의 반을 넘어갈 수 없다.

https://stackoverflow.com/questions/4401910/mysql-what-is-a-page

 

Clustered Index vs Non-clustered Index:

Clustered Index는 index에 저장되는 순서와 같은 순서로 disk에 저장되는 것이다. 따라서 clustered index는 1종류밖에 없다.

Non-clustered Index는 실제 물리적으로 저장되는 것과는 별도로 다른 리스트가 존재하는 것이다. 이 리스트는 물리적인 주소값을 저장하고 있는 포인터를 가지고 있다.

만약 모든 column을 반환하고 싶다면 주로 clustered index가 더 빠르다. 이유는 Non-clustered Index는 인덱스에서 테이블을 한번 더 찾아가야 되기 때문이다.

https://stackoverflow.com/questions/1251636/what-do-clustered-and-non-clustered-index-actually-mean

 

어떤 Column에 Index를 생성해야할까?

Primary Key를 가지는 새로운 테이블을 생성하게되면 clustered index가 자동으로 primary key의 column에 대해 생성되게 된다. 대부분의 경우에는 괜찮지만 가끔가다 가장 효과적이지 않은 경우도 있다.

 

Clustered Index로 사용되는 column은 unique하고 새로운 entry가 들어갈 때마다 값이 증가되는 column인것이 좋다. 그렇지 않을 경우 새로운 row가 insert될 때마다 index는 새로 정렬되어야 한다. 새로 입력된 row가 있어야될 위치를 찾기위해 data page를 새로 분리하고 위치를 옮기는 작업이 필요하다.

 

- 같은 맥락으로 자주 값이 변경되는 column도 Clustered Index로 사용되면 안된다. 매 업데이트때마다 재정렬을 해야되기 때문이다.

- 큰 데이터를 저장하는 column(BLOB, GUID)를 index를 사용하는 것은 sort할 때 비효율적이다.

 

https://www.sqlshack.com/poor-database-indexing-sql-query-performance-killer-recommendations/