Index
- 추가적인 쓰기 작업과 저장 공간을 활용하여 데이터베이스 테이블의 검색 속도를 향상 시키기 위한 자료구조
용어
- 테이블 스캔: 테이블 전체를 읽는 것
- 인덱스 스캔: 인덱스 영역만 읽는 것
- INDEX RANGE SCAN
- 인덱스를 정상적으로 사용하는것.
- 리프블록에서 스캔시작점을 찾아 거기서 부터 일부만 스캔
- INDEX FULL SCAN
- 인덱스 컬럼을 가공한 경우
- 스캔 시작점을 찾을 수 없기에 리프블록 전체를 스캔해야 한다.
- Cardinality: 데이터 중복도(간단히 말해서)
- 주민등록번호와 같이 중복되지 않는 경우 카디널리티가 가장 높다
- 성별과 같이 남/녀로 나눠져 데이터가 중복될 경우 카디널리티가 낮다
- 인덱스를 만들 때는 카디널리티가 높은 컬럼에 건다
인덱스의 종류
Clustered Index
- 물리적으로 테이블의 데이터를 재배열
- 데이터가 테이블에 삽입되는 순서에 상관없이 인덱스로 생성되어 있는 컬럼을 기준으로 정렬되어 삽입된다.
- 인덱스 페이지를 키값과 데이터 페이지의 번호로 구성하고, 검색하고자하는 데이터의 키 값으로 페이지 번호를 알아내어 데이터를 찾는다.
Non-Clustered Index
- 물리적으로 데이터를 배열하지 않은 상태로 데이터 페이지가 구성된다.
- 인덱스 페이지는 키값(정렬하여 인덱스 페이지 구성)과 RID로 구성된다
- RID는 '파일그룹번호-데이터페이지번호-데이터페이지오프셋'으로 구성되는 포인팅 정보이다. ex) 1-3-1, 1번 파일그룹의 3번 데이터 페이지의 1번째 데이터가 된다.
- 이렇게 중간 레벨 인덱스 페이지들을 생성하고, 이 인덱스 페이지를 찾기위한 루트 레벨 인덱스 페이지를 생성한다.
- 검색하고자하는 데이터의 키 값을 루트 레벨 인덱스 페이지에서 비교하여 중간 레벨 인덱스 페이지 번호를 찾고, 중간 레벨 인덱스 페이지에서 RID 정보로 실제 데이터의 위치로 이동한다.
B-TREE 인덱스
- 일반적으로 가장 많이 사용됨
- 데이터의 값의 종류가 많고 동일한 데이터가 적을 경우 주로 사용
- UNIQUE 인덱스
- 인덱스르 만드는 KEY 값에 중복되는 값이 없는 것
- 속도가 빠르다
- NON-UNIQUE 인덱스
BITMAP 인덱스
- 데이터 값의 종류가 적고 동일한 데이터가 많을 경우 주로 사용(ex.성별)
주의점
- DML이 자주 발생하는 테이블은 인덱스를 최소한으로 해야함
- 클러스터(Clustered) 인덱스는 데이터 파일과 직접 연관.
- 데이터 크기가 너무 크면 페이지 분할이 빈번하여 쓰기 성능 절하.
- 인덱스는 카디널리티(Cardinality)가 높을수록 유리.
- 클러스터 인덱스는 읽기 성능은 보조 인덱스보다 빠르지만 쓰기는 느림.
- 페이지 분할은 시스템 부담! (Clustered Index가 없는게?!)
- 다중 컬럼 인덱스는 순서를 고려해서.
- 인덱스는 꼭 필요한 것만.
- 전체 테이블의 10~15% 이상을 읽을 경우 보조 인덱스 사용 안함!
INSERT
- INDEX Split 현상
- 인덱스의 블록들이 하나에서 두 개로 나누어 지는 것
- 새로운 데이터 추가 시 하나의 블록에 저장되어 있던 데이터들에 빈자리가 없을 경우 2개의 블록으로 나눔
- 이 경우 인덱스의 갯수만큼 일어나므로 조심
- Index Split 이 완료되기 전까지 다음 작업이 안되므로 속도저하가 일어남
DELETE
- delete 작업 수행시 테이블에서는 데이터가 사라지지만 index의 경우 delete 되자 않고 해당 데이터를 사용하지 않는 표시만 해둠
- 이 경우 인덱스를 사용함에도 쿼리의 수행속도가 저하됨
- Index Rebuild를 해줘야함
- ANALYZE INDEX 인덱스명 VALIDATE STRUCTURE로 상태조회
- ALTER INDEX 인덱스명 REBUILD로 리빌드
UPDATE
- 인덱스에는 update라는 개념이 없다
- 인덱스 상에서 delete와 insert 작업이 발생
- 인덱스에 큰 부하를 준다.
Sargable(Search ARGument ABLE) Query
- where, order by, group by 등에는 가능한 index가 걸린 컬럼 사용.
- where 절에 함수, 연산, Like(시작 부분 %)문은 사거블하지 않다!
- SELECT * FROM member WHERE member_id / 5 = 1 (X)
- SELECT * FROM member WHERE member_id = 5 / 1 (O)
- SELECT * FROM member WHERE MOD(member_id, 10) = 0 (X)
- between, like, 대소비교(>, < 등)는 범위가 크면 사거블하지 않다.
- or 연산자는 필터링의 반대 개념(로우수를 늘려가는)이므로 사거블이 아니다.
- offset이 길어지면 사거블하지 않는다.
- SELECT * FROM member LIMIT 10 offset 200 (X)
- SELECT * FROM member LIMIT 200, 10 (X)
- 건너뛰는 것들이 많기 때문에 결국 앞을 다 읽어야함
- 범위 보다는 in 절을 사용하는 게 좋고, in 보다는 exists가 더 좋다.
- 꼭 필요한 경우가 아니라면 서브 쿼리보다는 조인(Join)을 사용하자.
Fulltext Index
- SELECT * FROM [테이블 명] WHERE MATCH([컬럼명1], [컬럼명2]...) AGAINST('[키워드1, 키워드2, -제외키워드, +필수키워드, ...]' [IN BOOLEAN MODE])
- chat, varchar, text 타입도 가능
- 여러 컬럼을 동시에 인덱스 생성 가능
- 불용어는 기본적으로 영어만 들어가 있음
- SELECT * FROM information_schema.innodb_ft_default_stopword 로 확인가능
- a about, com, from how... 등
- 한국어 불용어를 만들기 위해서는 여러 설정 필요
- 필요할 때 찾아보자
Partition
- 8,192개까지 가능
- FK 지정 불가
- PK를 지정할 경우 PK가 Partition Key
- ex) Partition by RANGE(컬럼)
Partition 타입
- 중요도 MAX
- Range Partitioning
- List Column Partitioning
- 중요도 MIN
- 어느 파티션에 어느 데이터가 들어갔는지 알 수 없기 때문에 잘 사용안함
- Hash Partitioning: 해싱 알고리즘 사용
- Key Partitioning: 컬럼을 안넣어도 됨
- Subpartitioning
- RANGE 와 LIST 으로 파티션을 해야 서브 파티션을 만들수 있으며, 서브 파티션은 반드시 Hash 나 Key 파티션으로 생성 해야함
- ...etc
예시
create table PartiRangeTest (
studentno varchar(7) not null,
enteryear smallint not null,
studentname varchar(31) not null
);
Partition by RANGE(enteryear) (
partition p1 values less than(2000),
partition p2 values less than(2010),
partition p3 values less than MAXVALUE
);
create table PartiListTest (
studentno varchar(7) not null,
dept varchar(31) not null
);
partition by LIST COLUMNS (dept) (
partition p1 values IN ('컴공', '산공'),
partition p2 values IN ('사학', '철학')
);
-- 파티션 변경
Alter table PartiRangeTest
REORGANIZE Partition p1, p2 INTO (
partition p1_2 values less than(2010)
);
-- 파티션 삭제
Alter table PartiRangeTest DROP Partition p1_2;
인덱스 관련 Statements
- OPTIMIZE TABLE [테이블 명]
- ANALYZE TABLE [테이블 명]
- SHOW TABLE STATUS [WHERE 조건]
- SHOW INDEX FROM [테이블 명]