[MySQL] INDEX

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

예시

  • RANGE
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
);
  • LIST
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 [테이블 명]

links

social