[MySQL] 클러스터링, 유니크 인덱스 및 외래키

해시 인덱스

• 동등 비교 검색에는 최적화 but 범위 검색/정렬된 결과를 가져오는데에는 사용 불가
• 주로 메모리 기반 테이블에 구현
• 해시 값을 기반으로 키를 사용하므로, 실제 키 값과는 관계없이 인덱스 크기가 작고 검색이 빠름
  • 키 칼럼의 값이 길어도 해시 인덱스에 저장되는 값은 해시 값이므로 4~8바이트 수준
• trade-off
  • 해시 함수의 결과 값 범위 high -> bucket이 많이 필요 -> 공간 낭비 가능
  • 해시 함수의 결과 값 범위 low -> 해시 충돌 (input은 다르나 해시 값은 같은 경우) 다량 발생 -> 해시 인덱스의 장점 소멸
    • 테이블의 파티셔닝 용도의 경우 해시 함수 결과 값의 범위를 좁게 사용
• 키값 자체가 해시 값으로 저장되기 때문에 범위 검색/원본값 기준 정렬에 해당 인덱스를 사용할 수 없는 것!
  • 동등 비교 조건의 경우만이 해시 인덱스를 이용 가능
  • 다중 칼럼으로 생성된 해시 인덱스에서도 모든 칼럼이 동등 조건이라면 인덱스 사용 가능!
    • 하지만 다중 칼럼 중 일부의 동등비교에는 전혀 사용 불가능

R-Tree 인덱스

• 2차원의 데이터를 인덱싱하고 검색하는 목적의 인덱스 ex) 위치 기반 서비스
• MBR(Minimum Bounding Rectangle)을 기반으로 트리 형태로 분할됨
  • 상위 MBR은 R-Tree의 루트 노드, 각 도형의 객체는 리프 노드에 저장
• MBR 자체의 특성 때문에, Contains(), Intersect() 등 포함 관계를 비교하는 함수 검색에만 인덱스 이용 가능

Fractal-Tree 인덱스

• B-Tree 인덱스의 단점: 인덱스 키 검색 및 변경 중에 디스크의 랜덤 I/O가 상대적으로 많이 필요 (물론 InnoDB의 경우 PK, unique 키 기준으로 클러스터링 인덱스가 되어 있어 비교적 랜덤 I/O가 덜 발생하긴 하지만)
  • 때문에 오랜 시간 동안 데이터의 변경으로 단편화가 발생하고, 때문에 인덱스 효율이 떨어지는 현상: Aging이 많이 발생 (InnoDB에서도 PK 삭제/변경 등이 되면 단편화는 일어나요)
• 따라서 Fractal-Tree는 이를 순차 I/O로 변환하여 처리할 수 있음
  • 그런데 독점적인 특허 문제로 TokuDB에만 적용되어 있음 (InnoDB에 적용되어 있지 않음)
  • 하지만 TokuDB는 동시성 문제가 있다고..
    근데 사실 아직까지도 TokuDB에 대해서 많이 못들어본거 보면 한물 간 인덱스 방법인듯한 느낌이 솔솔..

전문 검색(Full Text Search) 인덱스

• 문서의 내용 전체를 인덱스화해서 특정 키워드가 포함된 문서를 검색 시 사용 -> 일반적인 용도의 B-Tree 인덱스 사용 불가
• 사용자가 검색하게 될 키워드를 분석해내어, 해당 키워드들로 인덱스를 구축하는 방식
• 구분자(Stopword) 기법
  • 전문의 내용을 공백/탭/마침표와 같은 문장 기호 또는 사용자가 정의한 문자열을 기반으로 분리하여 키워드를 분석
  • MySQL의 내장 full Text Search 엔진은 해당 방식으로만 인덱싱함
  • 인덱싱 기준을 이와 같이 정하고, B-Tree 방식 그대로 사용
  • 구분자 방식에서는 추출된 키워드의 일부 검색이 불가능 (반드시 구분자로 구분된 아이들만 검색 가능)
• N-Gram 기법
  • 본문을 무조건적으로 n글자씩 잘라서 인덱싱
  • 최소 키워드에 대한 키를 관리하는 프론트엔드 인덱스, 그보다 넓은 단위의 키워드에 대한 키를 관리하는 백엔드 인덱스 이렇게 2개로 구성됨
  • ex) 2-Gram이라고 하면 백엔드 인덱스는 4글자 단위로 나눠서 두고, 프론트엔드 인덱스는 4글자 단위로 나눠진 백엔드 인덱스를 2글자 단위로 나눠서 인덱스를 정하는 방식
    -> 입력된 검색어를 동일하기 n바이트 단위로 잘라서 프론트엔드 인덱스 검색 후, 백엔드 인덱스의 최종 검증을 거쳐 return하는 방식
• N-Gram 방식이 인덱스의 크기가 stopword 기법보다 크긴 하지만, stopword의 경우 다른 단어와 연결되어 있는 키워드를 찾을 수 없는 케이스가 생김
• 해당 인덱스를 사용하기 위해서는 MATCH (...) AGAINST (...)와 같은 특정 구문을 반드시 사용해야함

클러스터링 인덱스

• InnoDB, TokuDB 스토리지 엔진에서만 사용

• PK값이 비슷한 레코드끼리 묶어서 저장하는 것

  • PK 값에 의해 레코드의 저장 위치가 결정 -> PK값 변경 시, 레코드의 물리적 저장 위치도 변경
  • 비슷한 값들을 동시에 조회하는 경우가 많다는 점에 착안.
  • PK기반 검색이 빠른 대신 레코드 저장(위치 선정에 CPU 사용) 및 PK 변경(저장 위치 변경해야하므로 삭제 후 재생성 필요)이 상대적으로 slow

• PK가 없는 InnoDB 테이블의 경우

  • not null 옵션의 유니크 인덱스 중에서 첫번째 인덱스를 클러스터 키로 선택
  • 상기 조건 만족되지 않을 시, 자동으로 유니크한 값을 가지도록 증가하는 칼럼을 내부적으로 추가하여 클러스터 키로 선정
    • 해당 경우, 결국 아무런 의미 없는 숫자 값으로 클러스터링이 되는 것으로 우리에게 아무런 혜택도 주지 않음
  • -> 되도록이면 PK를 명시하여 클러스터링 인덱스의 혜택을 받자!

• 보조 인덱스에 미치는 영향

  • 클러스터링 인덱스를 미지원하는 myIsam이나 memory 테이블에서는 PK와 보조인덱스의 구조적 차이가 X
  • 하지만 클러스터링 인덱스를 지원하는 InnoDB 테이블은, 모든 보조 인덱스의 리프노드가 해당 레코드가 저장된 주소가 아닌 PK값을 저장하도록 구성되어 있음
  • 즉 보조 인덱스를 이용하는 경우, 보조 인덱스 B-Tree를 통해 레코드의 PK값을 얻고, 얻은 PK값을 기반으로 주소값을 얻어 빠른속도로 데이터를 가져옴! (클러스터링되어 있으므로)
    • PK로 레코드의 주소를 읽어오는 과정은 매우 빠르게 처리된다고 함 (정확한 과정은 명시가 안되어 있는 것..)

• 장점

  • PK 검색 시 (레인지 검색 포함) 성능 good
  • 모든 보조 인덱스가 PK를 가지고 있어, 인덱스만으로 처리할 수 있는 쿼리(커버링 인덱스) many

• 단점

  • 모든 보조 인덱스가 PK를 가지므로 PK 크기가 크면.. 전체적인 인덱스 크기 big
  • 보조 인덱스로 검색하더라도 결국 PK로 주소 찾는 과정 필요하므로 (매우 빠르다고는 했지만) little slow
  • row insert시 저장 위치 결정으로 처리 성능 little slow
  • PK 변경 시 레코드 delete, insert -> little slow

• PK는 가능하면 AI보다는 업무적인 칼럼으로 생성하기!

  • 왜냐면.. 클러스터링 인덱스는 너무너무 좋은 기능이기 때문이야!! 같이 검색될만한 애들이 비슷한데에 저장되도록.. 그러면 좋겠지!!

• PK 반드시 명시!

  • 어차피 명시 안해도 내부적으로 AI 만들긴 할건데, 그래도 같은 AI면 사용자가 이용 가능한 AI가 낫잖아. AI라도 PK를 명시해라

• PK가 너무 길어질 수 밖에 없고, 보조 인덱스들을 사용할 수 밖에 없는 상황이라면 그때는 AI 칼럼 파서 PK 선정해라.

  • 보조 인덱스들 전부다 PK 포함하기 때문에 긴 PK는 좀 부담될 수 있음

유니크 인덱스

• 인덱스 없이 유니크 제약만 걸 수는 없음
• Null은 특정의 값이 아니므로 2개 이상 저장 가능
• PK는 기본적으로 NON NULL, UNIQUE index
• 사실상 읽기에 있어서 유니크 인덱스와 secondary 인덱스 (인덱스 중 PK나 유니크가 아닌 인덱스들)의 성능상 차이는 거의 없음
• 하지만 쓰기에 있어서, 유니크 인덱스의 키 값을 쓸 때는 중복된 값의 여부를 체크하는 과정이 한단계 더 필요하므로 (심지어 쓰기 가능한지 아닌지 동기로 callback을 줘야하므로 버퍼링도 불가함) secondary index의 쓰기보다 느리다.
• 유니크 인덱스에서 중복 값 체크 시 읽기 잠금, 쓰기 시 쓰기 잠금 사용으로 데드락도 빈번히 발생
  -> 따라서 성능이 좋아질 것으로 생각하고 불필요하게 유니크 인덱스를 생성하는 것은 좋은 선택이 아니다!
  -> 즉, 유일성이 꼭 보장돼야 하는 칼럼에 대해서만 유니크 인덱스를 생성하고, 꼭 필요하지 않은 경우라면 secondary index로 두는 것도 나쁘지 않은 방식

외래키

• InnoDB 스토리지 엔진에서만 지원
• 외래키가 설정되면, 자동으로 연관되는 테이블의 칼럼에 인덱스까지 생성됨
  • 테이블에 쓰기 잠금이 발생하는 경우에만 잠금 대기가 발생한다.
  • ex) id가 2인 부모 테이블에 update가 진행되고 있으면, 자식 테이블에서 외래키를 2로 선정하는 부분에 있어서 대기가 발생
    -> 자식 테이블의 외래키 칼럼의 변경은 부모 테이블의 확인이 필요한데, 이 상황에서 부모 테이블의 해당 레코드가 쓰기 잠금이 걸려 있으면, 해제 시까지 기다려야 한다는 것 (잠금 확장)
  • 단, 외래키가 아닌 칼럼의 변경에서는 잠금 확장이 일어나지 않음!
  • ex) 외래키의 특성(ON DELETE CASCADE) 때문에, 부모 레코드 삭제 시 자식 레코드가 삭제되어야 하는데, 해당 자식 레코드에 쓰기 잠금이 걸려 있으면 풀릴 때까지 대기해야함

MyISQM, InnoDB, Memory 스토리지 엔진 모두 B-Tree를 지원

InnoDB의 경우 인덱스에 대한 통계 정보를 관리하고, 이를 기반으로 쿼리의 실행 계획을 결정 (Cardinality 항목에 의존)

• Cardinality: 해당 인덱스에서의 유일한 값들의 갯수
• 인덱스의 통계 정보가 실제와는 너무 다르게 수집되어 쿼리 플랜이 이상하게 잡힐 때가 있음 -> 통계 정보를 재수집 하도록 ANALYZE 명령 사용 필요