SQL 인덱스Explained

31 of 31 rows

개념↕	설명↕	팁↕
인덱스 유형
⤢B-tree	동등성, 범위, 정렬 스캔을 위한 균형 트리. 대부분 데이터베이스의 기본값.	=, <, >, BETWEEN, ORDER BY에 사용. 전문 검색에는 쓰지 않는다.
⤢Clustered	리프 수준이 테이블 자체인 인덱스. 행이 키 순서로 저장된다. 테이블당 하나.	InnoDB는 기본 키로 클러스터링한다. 없는 SQL Server 테이블은 힙이다.
⤢Nonclustered	키와 기본 테이블을 가리키는 포인터를 담는 별도 구조. 테이블당 여러 개 가능.	힙에서는 행 ID를 저장하고, 클러스터형 테이블에서는 클러스터링 키를 저장한다.
⤢Hash	동등성 조회 전용 해시 테이블. 상수 시간의 정확한 일치.	= 연산에만 사용. 범위와 정렬에는 B-tree가 필요하다. SQL Server 메모리 최적화 해시는 행 수에 가까운 버킷 수가 필요하다.
⤢GIN	Generalized Inverted Index. 값을 행에 매핑한다. 배열과 전문 검색용으로 만들어졌다.	jsonb, 배열, tsvector, 트라이그램 검색에 사용. B-tree보다 구축이 느리다.
⤢GiST	Generalized Search Tree. 비스칼라 타입과 겹침 쿼리로 확장 가능.	기하 범위, kNN, 배제 제약에 사용. pg_trgm은 LIKE를 빠르게 한다.
⤢SP-GiST	공간 분할 GiST. 라딕스 또는 쿼드 트리로 데이터를 겹치지 않는 영역으로 나눈다.	점, IP 접두사, 접두사 일치에 사용. 겹침 쿼리에는 부적합. GiST를 쓸 것.
⤢BRIN	Block Range Index. 블록 범위별로 min/max를 저장한다. 공간을 거의 안 쓴다.	크고 물리적으로 정렬된 테이블에 사용. 구축은 저렴, 선택도는 약하다.
⤢Columnstore	행 대신 열 세그먼트로 저장되는 인덱스. 높은 압축률, 배치 실행.	행이 많고 열이 적은 분석 스캔에 사용. 단일 행 조회는 느리다.
⤢Full-text	MySQL과 SQL Server의 전용 역방향 단어 인덱스. 텍스트 열마다 구축된다.	MATCH AGAINST 또는 CONTAINS를 사용. Postgres 전문 검색은 대신 tsvector에 GIN을 쓴다.
⤢Bitmap	고유 값마다 하나의 비트맵을 저장하는 Oracle 인덱스. 각 비트가 한 행을 표시한다.	읽기 중심 웨어하우스의 저카디널리티 열에 사용. 동시 쓰기는 비트맵별로 직렬화된다.
⤢Descending	DESC로 선언된 인덱스 열. 해당 열의 키는 역순으로 저장된다.	(a ASC, b DESC) 같은 혼합 정렬에 필요. 단순 DESC 정렬은 일반 인덱스를 역방향으로 읽어도 된다.
⤢Invisible	옵티마이저가 무시하는 MySQL 8 인덱스. 쓰기 때마다 계속 유지된다.	삭제 전에 인덱스를 숨겨 영향을 시험하라. 재구축 없이 다시 표시할 수 있다.
설계
⤢Composite	여러 열로 구성된 인덱스. 선행 열이 쿼리 필터 순서와 일치해야 한다.	동등성 열을 먼저, 범위 열을 나중에. 단일 인덱스 두 개는 복합 인덱스 하나를 대체할 수 없다.
⤢Partial	WHERE 술어 위의 인덱스. SQL Server는 필터된 인덱스라고 부른다. 일치하는 행만 덮는다.	active='t' 행만 인덱싱하라. 크기와 쓰기 비용이 절반이 된다.
⤢Covering (INCLUDE)	키가 아닌 열을 추가한 B-tree. 인덱스 전용 스캔이 힙 조회를 피한다.	자주 가져오는 열을 INCLUDE로 추가하라. 인덱스의 정렬 가능성을 유지한다.
⤢Unique	중복 값을 금지하는 인덱스. 충돌하는 삽입을 거부한다.	자연 키와 일대일 제약에 사용. UNIQUE NULL은 NULL을 여러 개 허용한다.
⤢Expression	열 자체가 아니라 열의 함수 위에 구축된 인덱스.	lower(email) 또는 date_trunc('day', created_at)을 인덱싱하라. 쿼리는 정확히 같은 표현식을 반복해야 한다.
⤢Opclass	연산자 클래스. 인덱스가 한 열의 타입을 어떻게 비교하고 저장하는지 정의한다.	대소문자 접기 없는 LIKE 'abc%'에는 varchar_pattern_ops를 설정. 기본값은 동등성과 범위에 적합하다.
⤢Index-only scan	인덱스 페이지만으로 쿼리에 답하는 플랜. 힙은 절대 읽히지 않는다.	가져오는 모든 열이 인덱스 또는 INCLUDE 목록에 있어야 한다. Vacuum이 가시성 맵을 최신으로 유지한다.
⤢Foreign keys	외래 키 열은 참조 테이블에 자동 인덱스를 받지 못한다.	조인이나 삭제에 쓰는 FK 열은 모두 인덱싱하라. 없으면 계단식 삭제가 자식 테이블을 스캔한다.
유지 관리
⤢EXPLAIN	플랜 검사 도구. 순차 스캔, 인덱스 선택, 비용 추정치를 보여 준다.	EXPLAIN (ANALYZE, BUFFERS)를 사용하라. 1,000만 행 테이블의 Seq Scan은 인덱스 부재를 뜻한다.
⤢ANALYZE	테이블 열을 표본화해 플래너 통계를 만든다. 히스토그램이 인덱스 선택을 좌우한다.	대량 적재 후 실행하라. 오래된 통계는 인덱스된 열에서 seq scan을 유발한다.
⤢Over-indexing	인덱스마다 쓰기 오버헤드가 는다. 삽입, 갱신, 삭제는 인덱스별로 비용을 치른다.	사용하지 않는 인덱스를 삭제하라. 사용량은 pg_stat_user_indexes로 추적한다.
⤢pg_stat_user_indexes	인덱스별 사용량을 보여 주는 Postgres 뷰. 통계 리셋 이후의 스캔 수와 읽은 튜플 수를 센다.	idx_scan = 0으로 걸러 미사용 인덱스를 찾으라. 카운터는 pg_stat_reset()으로 리셋한다.
⤢CREATE INDEX CONCURRENTLY	읽기와 쓰기를 막지 않고 인덱스를 구축한다. 테이블 스캔 두 번이 필요하다.	트랜잭션 안에서는 실행할 수 없다. 실패한 구축은 INVALID 인덱스를 남기니 삭제하라.
⤢DROP INDEX	인덱스를 제거하고 저장 공간을 해제한다. 기본적으로 배타 잠금을 건다.	트래픽 많은 테이블에서는 DROP INDEX CONCURRENTLY를 사용하라. 제거 전에 사용 통계를 확인하라.
⤢REINDEX	부풀어 오른 인덱스를 재구축한다. 죽은 튜플에서 공간을 회수한다.	운영 환경에서는 REINDEX CONCURRENTLY를 사용하라. 없으면 잠금이 쓰기를 막는다.
⤢Bloat	MVCC 테이블과 그 인덱스에 쌓이는 죽은 튜플과 페이지 간극.	pgstattuple로 측정하라. 자주 Vacuum하고, 죽은 공간이 우세하면 REINDEX하라.
⤢Fillfactor	쓰기 시점에 페이지를 채우는 비율. 여유 공간이 이후 갱신을 흡수한다.	갱신이 잦은 테이블에서는 70-90으로 낮춰라. 꽉 찬 페이지는 페이지 분할을 강제한다.
⤢CLUSTER	테이블을 인덱스 순서로 물리적으로 다시 쓴다. 일회성 작업.	BRIN과 짝지으면 정렬된 블록이 된다. 이후 쓰기에서는 순서가 유지되지 않는다.