고성능을 위한 인덱싱.

• 인덱스는 스토리지 엔진이 행을 빠르게 찾기 위해 사용하는 데이터구조
• 데이터가 커질수록 우수한 성능을 위해 매우 중요
• 잘못된 인덱싱은 성능문제의 주요 원인
• 인덱스 최적화는 쿼리 성능을 향상시키는 가장 강력한 방법

인덱싱 기본

인덱스 유형

• 인덱스는 서버 계층이 아닌 스토리지 엔진 계층에 구현
• 이 책에서는 InnoDB를 기준으로 설명
• B-트리 인덱스
  • 가장 일반적인 인덱스 유형 (리프노드가 연결된 B+ 트리)
  • 대부분의 MySQL 스토리지 엔진이 지원
  • 모든 값이 순서대로 저장, 각 리프는 루트로부터 동일한 거리에 있음
  • B-트리는 인덱싕된 열을 순서대로 저장
    • 범위 검색을 하는데 유용함

      CREATE TABLE People (
        last_name varchar(50) not null,
        fast_name varchar(50) not null,
        dob date not null,
        key(last_name, first_name, dob)
      );
      

• 적응형 해시 인덱스
  • InnoDB 스토리지 엔진에는 적응형 해시 인덱스라는 특별한 기능이 있음
  • InnoDB는 일부 인덱스 값이 매우 자주 액세스되고 있음을 감지하면 B-트리 인덱스 위에서 메모리에 해시 인덱스를 구축
• B트리 인덱스를 사용할 수 있는 쿼리 유형
  • 전체 값 일치
  • 맨 왼쪽 접두사와 일치
  • 열 접두사 일치
  • 값 범위 일치
  • 한 부분을 정확히 일치시키고 다른 부분의 범위와 일치
  • 인덱스 전용 쿼리
• 풀 텍스트 인덱스
  • 값을 인덱스의 값과 직접 비교하는 대신 텍스트에서 키워드를 찾는 특수 유형의 인덱스
  • 불용어, 형태소 분석, 복구형 및 부울 검색과 같은 많은 미묘함이 존재
  • 단순 매칭보다는 검색 엔진이 하는 작업과 유사
  • MATCH AGINST

인덱스 이점

• B-트리 인덱스는 데이터를 정렬된 순서로 저장
  • ORDER BY, GRUOP BY에서 이를 사용가능
• 인덱스는 값의 복사본을 가지고 있으므로 인덱스만으로 일부 쿼리 수행가능
  • 서버가 검사해야하는 데이터의 양을 줄임
  • 서버가 정렬 및 임시 테이블을 생성하지 않도록 도와줌
  • 랜던 I/O를 순차 I/O로 변경

고성능을 위한 인덱싱 전략

• 인덱스를 잘만들고 제대로 사용하는것은 쿼리 성능을 높이는데 필수적인 요소

프리픽스(Prefix) 인덱스 및 인덱스 선택성

• 인덱스 선택성
  • #카디널리티 / #총행수
  • 1/#총행수 ~ 1 사이의 값
  • 높을수록 좋음

ALTER TABLE table_name
ADD KEY (column_name(3))

다중 열 인덱스

• 다중 열 인덱스를 제대로 이해하지 않고 사용하는 경우가 많음
  • 잘못 사용한 예
    • 모든 열을 개별적으로 인덱싱
    • 잘못된 순서로 다중 열 인덱스 구성
• 많은 열에 대한 개별 인덱스는 쿼리 성능 최적화에 도움이 안됨
• 인덱스 병합(index merge) 전략을 사용하면 약간의 대처 가능
  • 세 가지 변형
    • OR의 합집합
    • AND의 교집합
    • 두 조건의 합집합
  • EXPLAIN 예시

좋은 열 순서 선택하기

• 올바른 순서는 인덱스를 사용할 쿼리에 따라 달라짐
• 다중 열 인덱스는 왼쪽열부터 오른쪽의 방향순으로 정렬되어있음
  • 따라서 정방향 또는 역순으로 스캔하여 열 순서와 정확히 일치하는 ORDER BY, GROUP BY, DISTINCT 구성 가능
• 열 순서 선택시 가장 선별적인 열을 먼저 배치해야함

클러스터형 인덱스

• InnoDB의 클러스터형 인덱스는 B-트리 인덱스와 행을 동일한 구조에 함께 저장
• 테이블에 클러스터형 인덱스가 있는경우 행은 인덱스의 리프 페이지에 저장됨
• 클러스터형 인덱스는 동시에
• 데이터 클러스터링의 이점
  • 관련 데이터는 가까이 존재
  • 데이터 액세스 속도가 빠름
  • 커버링 인덱스를 사용하는 쿼리는 리프 노드에 포함된 기본 키 값을 사용할 수 있음
• 데이터 클러스터링의 단점
  • 삽입되는 PK의 순서에 따라 삽입 순서가 크게 달라짐
  • 새로운 행이 삽입되거나 행의 PK가 업데이트되어 행을 이용해야하는 경우 페이지 분할 대상이됨
  • 전체 테이블 스캔이 느릴수 있음
  • 보조 인덱스 리프에는 PK가 있어서 인덱스 크기가 클 수 있음
  • 보조 인덱스 접근시 PK 인덱스 조회가 추가적으로 필요함

커버링 인덱스

• 인덱스는 WHERE뿐만 아니라 쿼리 전체에 대해 설계되어야함
• 쿼리수행을 충족하는데 필요한 데이터를 포함하는 인덱스를 커버링 인덱스(covering index)라고 함
• 데이터 대신 인덱스만 읽을 경우 이점
  • 훨씬 적은 데이터 엑시스
  • 적은 I/O 수행
  • InnoDB의 클러스터형 인덱스 때문에 InnoDB 테이블에 특히 유용
    • 보조 인덱스는 리프 노드에 행의 기본키를 가짐
• EXPLAIN의 Extra에 Using index 확인 가능
• 보조 인덱스의 리프에는 기본키가 있으므로 보조인덱스로 조건을 걸고 기본키를 조회하더라도 커버링 인덱스 동작 만족함

인덱스 스캔을 사용한 정렬

중복과 이중 인덱스

사용하지 않는 인덱스

• 사용하지 않는 인덱스는 부담만 가중, 제거하는것이 좋음
• performance_schema, sys에서 확인가능

SELECT * FROM sys.schema_unused_indexexs;

인덱스와 테이블 유지 관리

• 테이블 손상 찾기 및 복구
• 인덱스 통계 업데이트
• 인덱스 및 데이터 단편화 줄이기

요약

• 대부분 B-트리 인덱스를 사용
• 인덱스 선택, 쿼리 작성시 염두해야할 세 가지 원칙
  • 단일행 액세스는 특히 스핀 기반 스토리지에서 느림
  • 두 가지 이유로 행 범위에 순서대로 액세스하는것이 빠름
    • 순차 I/O는 랜덤 I/O보타 빠름
    • 추가적인 정렬 작업이 필요없음
  • 인덱스를 사용한 액세스가 빠름
• 적절한 인덱스를 만들어야함
• 응답시간 확인하고 응답시간이 너무 오래 걸리거나 서버에 너무 많은 부하를 주는 쿼리를 찾아 스키마, 쿼리, 인덱스 구조 검토 필요
  • 쿼리가 너무 많은 행을 검사
  • 검색 후 정렬이 필요하거나 임시 테이블 사용여부
  • 랜덤 I/O 발생 여부
  • 인덱스에 포함되지 않은 열을 검색하기 위해 테이블 전체 행 조회 발생 여부