chloe

인덱스

들어가며

처음 Schema를 설계하고 인덱스를 잡을 때는 쿼리들을 보고 DBA 분이 조언을 한번 해주셨다. 이후, 요구사항이 추가되면서 사용하는 쿼리들이 늘어나면서, 인덱싱을 다시 고민해보고 개선할 점이 없는지 확인해보기 위해 Real MySQL 책으로 인덱싱 공부를 해보고 개선점을 찾아보려한다.

인덱스란?

  • 칼럼의 값을 주어진 순서로 미리 정렬해서 보관
  • SortedList 자료구조처럼 데이터가 저장될 때 마다 항상 값을 정렬해야하므로 과정이 복잡하고 느리지만, 이미 정렬돼 있어 아주 빨리 원하는 값을 찾아올 수 있다.
  • 데이터 저장 성능을 희생하고 그 대신 데이터 읽기 속도를 높이는 기능이다.

인덱스의 분류

  • 인덱스 역할 별로 구분한다면, 프라이머리 키와 보조 키로 구분할 수 있다.
  • 데이터 저장 방식별로 구분할 경우, 대표적으로 B-Tree 인덱스와 Hash 인덱스로 구분할 수 있다.
  • 데이터 중복 여부로 분류하면 유니크 인덱스와 유니크하지 않은 인덱스로 구분할 수 있다.
    • 유니크 인덱스로 검색하면 항상 1건의 코드만 찾으면 더 찾지 않아도 된다는 것을 옵티마이저에게 알려주는 효과를 낸다.

B-Tree 인덱스

  • 가장 범용적인 목적으로 사용되는 인덱스 알고리즘이다.
  • Binary (X), Balanced (O)
  • 인덱스 구조체 내에서는 항상 정렬된 상태로 유지한다.

구조 및 특성

  • 트리 구조
    • 루트 노드 : 최상위 하나 존재
    • 브랜치 노드: 루트노드도 리프노드도 아닌 중간의 노드
    • 리프 노드 : 실제 데이터 레코드를 찾아가기 위한 주솟값을 가지고 있다.
  • 인덱스의 키 값은 모두 정렬되어 있고, 데이터 파일의 레코드는 정렬되지 않음
  • 레코드는 INSERT 순서대로 저장되는 것은 아니다.
    • 대부분의 RDBMS의 데이터 파일에서 레코드는 특정 기준으로 정렬되지 않고 임의의 순서로 저장된다.
    • 하지만 InnoDB 테이블에서 레코드는 클러스터되어 저장되므로 디폴트 PK 키 순서로 정렬해 저장된다.
  • 레코드가 삭제되어 빈 공간이 생기면 그 다음의 INSERT는 가능한 삭제된 공간을 재활용한다.
  • MyISAM과 InnoDB의 가장 큰 차이는 세컨더리 인덱스를 통해 파일의 레코드를 찾아가는 방법에 있다.
    • MyISAM : 세컨더리 인덱스가 물리적인 주소를 가진다
    • InnoDB : 프라머리키를 주소처럼 사용하기 때문에 논리적인 주소를 가진다
      • 세컨더리 검색에서 데이터 레코드를 읽기 위해서 반드시 프라이머리 키를 저장하는 B-Tree를 다시 한번 검색해야 한다.

B-Tree 인덱스 키 추가, 삭제, 변경, 검색

  • 추가
    • 인덱스 키 추가 작업을 지연시켜 나중에 처리할 수 있다. → 체인지 버퍼
    • 하지만 PK, unique index는 중복 체크가 필요해 즉시 B-Tree에 추가하거나 삭제한다
  • 삭제
    • 삭제 마크만 한다.
    • 그대로 방치되거나 재활용할 수 있다.
  • 변경
    • 인덱스 상의 키 값만 변경하는 불가능하다.
    • 키 값을 삭제한 후, 다시 새로운 키 값을 추가하는 형태로 처리된다.
  • 검색
    • 레코드 잠금이나 넥스트 키락이 검색을 수행한 인덱스를 잠근 후 테이블의 레코드를 잠그는 방식으로 구현되어있다.
    • UPDATE, DELETE를 수행할 때 테이블에 적절히 사용할 수 있는 인덱스가 없으면 불필요하게 많은 레코드를 잠근다.
    • 테이블의 모든 레코드를 잠글 수 있다.

체인지 버퍼 (4.2.10장)

  • InnoDB는 변경해야 할 인덱스 페이지가 버퍼 풀에 있으면 바로 업데이트를 수행하지만
  • 디스크로부터 읽어와서 업데이트해야 한다면 이를 즉시 실행하지 않고
  • 임시 공간에 저장해두고 바로 사용자에게 결과를 반환하는 형태로 성능을 향상시킨다.
  • InnoDB 버퍼 풀로 설정된 메모리 공간의 25%까지 사용할 수 있게 설정되어있다.

B-Tree 인덱스 사용에 영향을 미치는 요소

  • 인덱스를 구성하는 칼럼의 크기
  • B-Tree 깊이
  • 선택도 (기수성)
  • 읽어야하는 레코드의 건수
    • DBMS의 옵티마이저에서 인덱스를 통해 레코드 1건을 읽는 것이 테이블에서 직접 레코드 1건을 읽는 것 보다 4~5배 비용이 더 많이 든다고 예측한다.
    • 인덱스를 통해 읽어야할 레코드건수가 전체 테이블 레코드의 20%~25%를 넘어서면 인덱스를 이용하지 않고 테이블을 모두 직접 읽어서 필요한 레코드만 가려내는 (필터링) 방식으로 처리하는 것이 효율적이다.

인덱스 레인지 스캔

  • 가장 대표적인 방식으로, 다른 접근 방식들 보다 빠르다.
  • 인덱스를 통해 레코드를 한 건만 읽는 경우와 한 건 이상을 읽은 경우를 모두 묶어서 말한다.
  • 검색할 인덱스 범위가 결정됐을 때 사용하는 방식
  • 인덱스 레인지 스캔 순서
    • 시작해야 할 위치를 찾으면 그때부터는 리프 노드의 레코드만 순서대로 읽는다.
    • 리프 노드의 끝까지 읽으면 리프 노드 간의 링크를 이용해 다음 리프 노드를 찾아서 다시 스캔한다.
    • 읽은 인덱스 키와 레코드 주소를 이용해 레코드가 저장된 페이지를 가져오고, 최종 레코드를 읽어온다.
      • 커버링 인덱스는 이 과정이 필요없다.

0인덱스를 이용한 레인지 스캔

1인덱스를 이용한 레인지 스캔

2인덱스 레인지 스캔을 통한 데이터 레코드 읽기

  • 어떤 방식으로 스캔하던, 인덱스를 구성하는 칼럼의 정순 또는 역순으로 정렬된 상태로 레코드를 가져온다.
    • 별도 정렬을 하지 않아도 인덱스 자체의 정렬 특성 때문
  • 리프 노드에 저장된 레코드 주소로 데이터 파일을 읽어오는데, 레코드 한 건 한 건 단위로 랜덤 I/O가 한번 씩 일어난다.
    • 3건의 레코드가 검색 조건에 일치하면, 랜덤 I/O가 최대 3번 필요하다
    • 인덱스를 통해 데이터 레코드를 읽는 작업은 비용이 많이 든다.

인덱스 풀 스캔

  • 인덱스의 처음부터 끝까지 모두 읽는 방식
  • 쿼리의 조건절에 사용된 칼럼이 인덱스의 첫 번째 칼럼이 아닌 경우 사용
    • 인덱스: (A,B,C), 조건절 (B) or (C)
  • 인덱스 레인지 스캔보다는 빠르지 않지만 테이블 풀 스캔보다는 효율적이다.
  • 인덱스에 포함된 칼럼만으로 쿼리를 처리할 수 있는 경우 테이블의 레코드를 읽을 필요가 없다.

3

루스(Loose) 인덱스 스캔

  • 느슨하게 또는 듬성듬성하게 인덱스를 읽는 것
  • 중간에 필요치 않은 인덱스 키 값은 무시하고 다음으로 넘어가는 형태로 처리한다
  • 일반적으로 GROUP BY 또는 집합 함수 MAX, MIN 최적화에 사용된다.
select dept_no, MIN(emp_no)
	from dept_emp
	where dept_no between 'd002' AND 'd004'
	group by dept_no; // index (dept_no, emp_no)
// dept 그룹별로 첫 번째 emp_no만 읽으면 된다.

4

인덱스 스킵 스캔

  • MySQL 8.0 버전 부터는 옵티마이저가 앞의 인덱스 칼럼(A)을 건너뛰어서 뒤의 다른 칼럼(B)으로만으로도 인덱스 검색이 가능하게 해주는 인덱스 스킵 스캔 최적화 기능이 도입되었다.
    • index (A,B)
    • loose index scan과 비슷한 최적화 방식이나 이는 GROUP BY를 처리하기 위해서만 사용가능
  • 쿼리 실행 계획에서 type = ‘range’ 일 때, 인덱스에서 필요한 부분만 꼭 읽었다는 뜻이다.
    • type = ‘index’ : 풀 인덱스 스캔
  • Using index for skip scan은 인덱스 스킵 스캔을 활용했다는 뜻이다.
  • A 칼럼이 조건에 없으면 A 칼럼 조건을 추가해서 쿼리를 다시 실행하는 형태다.
  • 단점
    • 선행 칼럼 (A)의 유니크한 값의 개수가 적어야함
    • 커버링 인덱스여야한다.

    5

다중 칼럼 인덱스

  • 2개 이상의 칼럼으로 구성된 인덱스
  • 인덱스의 두 번째 칼럼은 첫 번째 칼럼에 의존해서 정렬돼 있다.

B-Tree 인덱스의 정렬 및 스캔 방향

  • 인덱스를 설정할 때 설정한 정렬 규칙에 따라서 인덱스의 키 값은 항상 오름차순이거나 내림차순으로 정렬되어 저장된다.
  • 인덱스를 어느 방향으로 읽을지는 쿼리에 따라 옵티마이저가 실시간으로 만들어내는 실행 계획에 따라 결정된다.
  • 인덱스의 정렬
    • MySQL 5.7 버전까지는 칼럼 단위로 정렬 순서를 혼합해서 인덱스를 설정할 수 없었다.
    • MySQL 8.0 버전부터 정렬 순서를 혼합한 인덱스도 생성할 수 있게 됐다.
  • 인덱스 스캔 방향

    • 인덱스 생성 시점에 오름차순 또는 내림차순으로 정렬이 결정되지만 쿼리가 그 인덱스를 사용하는 시점에 인덱스를 읽는 방향에 따라 오름차순 또는 내림차순 정렬 효과를 얻을 수 있다.

        > select * from employees where first_name >= 'Chloe' 
      order by first_name asc limit 4 # Chloe를 찾으면 정순으로 해당 인덱스를 읽어내려간다.
			
  > select * from employees
      order by first_name desc limit 5 
      #first_name에 정의된 인덱스를 역순으로 읽으며 처음 다섯개만 가져온다.

    • 쿼리의 ORDER BY 처리나 MIN, MAX 함수 등의 최적화가 필요한 경우에도 MySQL 옵티마이저는 인덱스의 읽기 방향을 전환해서 사용하도록 실행 계획을 만들어 낸다.

  • 내림차순 인덱스
    • MySQL 8.0 부터 인덱스 자체를 내림차순으로 생성할 수 있게 되었다.
    • 인덱스를 정순으로 읽느냐, 역순으로 읽느냐에 따라 시간이 더 걸릴 수 있다.
      • 페이지 잠금이 인덱스 정순 스캔에 적합하기 때문
      • 페이지 내에서 인덱스 레코드가 단방향으로만 연결된 구조이기 때문
    • 인덱스의 앞쪽만 또는 뒤쪽만 집중적으로 읽어서 인덱스의 특정 페이지 잠금이 병목이 될 것으로 예상된다면 쿼리에서 자주 사용하는 정렬 순서대로 인덱스를 생성하는 것이 잠금 병목 현상을 완화하는데 도움이 된다.

    InnoDB 페이지 내부에서 레코드들이 정렬 순서대로 저장된 것 같지만 InnoDB 페이지는 힙처럼 사용되기 때문에 물리적으로 저장이 순서대로 배치되지는 않는다.

6

B-Tree 인덱스의 정렬 및 스캔 방향

  • 쿼리의 WHERE, GROUP BY, ORDER BY 절이 어떤 경우에 인덱스를 사용할 수 있고 어떤 방식으로 사용할 수 있는지 식별할 수 있어야 한다.
  • 비교 조건의 종류와 효율성
    • 작업의 범위를 결정하는 조건을 작업 범위 결정 조건이라 하고, 비교 작업의 범위를 줄이지 못하고 단순히 거름종이 역할만 하는 조건을 필터링 조건, 체크 조건이라 표현한다.
    • 작업 범위를 결정하는 조건은 많으면 많을수록 쿼리의 처리 성능을 높이지만 체크 조건은 많다고해서 쿼리의 처리 성능을 높이지는 못하고, 오히려 쿼리 실행을 더 느리게 만들 때가 많다.
  • MySQL에서는 NULL 값도 인덱스에 저장된다
    • 하지만 where column is not null 로 비교하는건 작업범위 결정 조건으로 사용할 수 없다.

함수 기반 인덱스

  • 때로는 칼럼의 값을 변형해서 만들어진 값에 대해 인덱스를 구축해야할 때, 함수 기반의 인덱스를 활용하면된다.
  • MySQL 8.0부터 지원하기 시작
  • 구현 방법
    • 가상 칼럼을 이용한 인덱스
    • 함수를 이용한 인덱스
  • 인덱싱 값을 계산하는 과정의 차이만 있을 뿐, 실제 인덱스의 내부적인 구조 및 유지관리 방법은 B-TREE와 동일하다.

가상 칼럼을 이용한 인덱스

  • 가상 칼럼을 추가하고 그 가상 칼럼에 인덱스를 생성할 수 있다.
ALTER TABLE user 
	ADD full_name varchar(30) as (CONCAT(first_name, ' ', last_name)) virtual, 
	ADD index ix_fullname(full_name);
  • 테이블에 새로운 칼럼을 추가하는 것과 같은 효과를 내기 때문에 실제 테이블의 구조가 변경된다는 단점이 있다.

함수를 이용한 인덱스

  • 테이블의 구조를 변경하지 않고, 함수를 직접 사용하는 인덱스를 생성할 수 있게 됐다.
  • 계산된 결과값의 검색을 빠르게 만들어준다.
  • 반드시 조건절에 함수 기반 인덱스에 명시된 표현식이 그대로 사용돼야 한다.
    • MySQL 옵티마이저는 다른 표현식으로 간주해서 함수 기반 인덱스를 사용하지 못한다.

클러스터링 인덱스

  • 테이블의 레코드를 비슷한 것 (PK 기준)끼리 묶어서 저장하는 형태
  • InnoDB 스토리지 엔진에서만 지원하며, 나머지 스토리지 엔진에서는 지원되지 않는다.
  • 클러스터링 테이블은 그 자체가 하나의 거대한 인덱스 구조로 관리되는 것이다.
  • PK를 변경하면 클러스터링 테이블의 데이터 레코드도 이동하면서 페이지가 바뀔 수 있다.
    • InnoDB를 제외한 테이블의 데이터 레코드는 프라이머리 키나 인덱스 키 값이 변경된다고 해서 실제 데이터 레코드의 위치가 변경되지는 않는다.
  • InnoDB가 클러스터링 테이블을 구성하는 키가 되는 우선순위
    1. PK
    2. NOT NULL UNIQUE KEY 중 첫 번째 인덱스
    3. 자동으로 유니크한 키 값을 가지는 칼럼을 내부적으로 추가한 후, 클러스터링 키로 선택
  • 가능하면 프라이머리 키를 명시적으로 생성하자

세컨더리 인덱스에 미치는 영향

  • 클러스터링 키 값이 변경될 때마다 데이터 레코드의 주소가 변경되고 그때마다 해당 테이블의 모든 인덱스에 저장된 주솟값을 변경해야할까?
  • 이런 오버헤드를 줄이기 위해 InnoDB 클러스터링 테이블의 모든 세컨더리 인덱스는 해당 레코드가 저장된 주소가 아니라 프라이머리 키 값을 저장하도록 구현되어 있다.

장단점

  • 장점
    • PK로 검색할 때 처리 성능이 매우 빠름
    • 테이블의 모든 세컨더리 인덱스가 PK를 가지고 있어 인덱스만으로 처리될 수 있는 경우가 많음 (커버링 인덱스)
  • 단점
    • 모든 세컨더리 인덱스가 클러스터링 키를 갖고있어 클러스터링 키 값의 크기가 크면 전체적으로 인덱스 크기가 커진다
    • 세컨더리 인덱스를 통해 검색 시, PK로 한번 더 검색해야하므로 처리 성능이 느림
    • PK를 변경할 때 레코드를 DELETE + INSERT 하는 작업이 필요해서 처리 성능이 느림
  • 일반적인 웹 서비스와 같은 트랜잭션 환경에서 쓰기와 읽기의 비율이 2:8 ~ 1:9 정도로 조금 느린 쓰기를 감수하고 읽기를 빠르게 유지하는 것은 매우 중요하다.
  • 주의사항
    • 클러스터링 인덱스가 클 수록 세컨더리 인덱스도 자동으로 크기가 커진다.
    • PK는 AUTO-INCREMENT 보다는 가능하면 업무적인 칼럼으로 생성하자
    • PK는 반드시 명시할 것
    • PK의 크기가 길어도 세컨더리 인덱스가 필요치 않다면 그대로 PK로 사용하는 것이 좋다.
      • 세컨더리가 필요하고 PK가 길면 auto_increment를 추가하자.

유니크 인덱스

  • 인덱스라기 보다는 제약 조건에 가깝다.
  • MySQL에서는 인덱스없이 유니크 제약만 설정할 방법이 없다.
  • MyISAM이나 MEMORY 테이블에서 PK는 NOT NULL UNIQUE KEY와 같지만, InnoDB의 PK는 클러스터링 키의 역할도 하므로 유니크 인덱스와는 근본적으로 다르다.
  • 성능이 더 좋아질 것으로 생각하고 불필요하게 유니크 인덱스를 생성하는 것은 좋지 않다.

유니크 인덱스와 일반 세컨더리 인덱스

  • 인덱스 구조 상은 아무런 차이점이 없다.
  • 읽기
    • 유니크 인덱스라고 더 빠르지는 않다.
    • 유니크하지 않은 세컨더리 인덱스에서 한 번 더 해야하는 작업은 디스크 읽기가 아니라 CPU에서 칼럼값을 비교하는 작업이기 때문에 성능상 영향이 거의 없다.
    • 읽어야 할 레코드가 많아서 느린 것이지, 인덱스 자체의 특성 때문에 느린 것이 아니다.
    • 읽어야 할 레코드 건수가 같다면 성능상의 차이는 미미하다.
  • 쓰기
    • MySQL는 유니크 인덱스에서 중복된 값을 체크할 때는 읽기 잠금을 사용하고, 쓰기를 할 때는 쓰기 잠금을 사용하면서 데드락이 빈번하게 발생한다.
    • 이때문에 일반 세컨더리 인덱스보다 변경 작업이 더 느리게 작동한다.

외래키

  • MySQL에서 외래키는 InnoDB 스토리지 엔진에서만 생성할 수 있으며, 외래키 제약이 설정되면 자동으로 연관되는 테이블의 칼럼에 인덱스까지 생성된다.
  • 테이블 변경(쓰기 잠금)이 발생하는 경우에만 잠금 경합(잠금 대기)이 발생한다.
  • 외래키와 연관되지 않은 칼럼의 변경은 최대한 잠금 경합을 발생시키지 않는다.
  • DB에서 외래 키를 물리적으로 생성하려면 잠금 경합 까지 고려해 모델링을 진행하는 것이 좋다.

개선점 찾아보기

실제 테이블을 기반으로 쿼리를 날려보고 시간도 측정했는데, 글에서는 Table을 어느정도 추상화해서 말하겠다.

  • Table
    • 상품의 메타 정보와 관련된 Product Table이 있다.
    • 칼럼은 상품 설명, 사진, 판매자 등의 내용이 있다.
  • 기존에 적용된 Index
    • PK : code (auto_increment 아님)
    • Secondary Index : index(updated_at)

원래 is_open(boolean) column이 아예 없었는데, 서비스 오픈 직전에 판매 가능한 상품만 보여주기위해 설계와 1차 구현이 다 끝나고 생겼다.

  1. List 조회

    • 사용하는 쿼리
     select * from product where updated_at >= ? and is_open = true
    • 날짜와 code만 쓰는데 is_open도 인덱스에 추가하면 커버링 인덱스를 적용해도 되지 않을까 생각함

    • select문에 code, updated_at 넣으면 커버링 인덱스가 적용된다 (Using index)

      7

    • 우선 is_open 조건 없이 시간 측정
      • 커버링 인덱스 탈 때 : 50ms 내외
      • 커버링 인덱스 타지 않을 때 : 300ms ~ 600ms
    • 하지만 updated_at 함께 created_at도 로직에서 비교로 쓰여서 커버링 인덱스 적용이 힘들었다.

    • 기존의 non clustered index (updated_at)을 (is_open, updated_at) 으로 수정할까 했으나, 유의미한 차이는 없었다.

      index(updated_at) index (is_open, updated_at)
      233 171
      197 185
      185 178
      207 193
      203 215
      253 211
      224 279
      avg : 214 avg : 204
    • 이 부분에서 이전에는 is_open이 true or false로 카디널리티가 낮아서 차이가 없구나 하면서도 차이가 이정도로 안날 수도 있구나 했었다.
    • 이번에 학습하면서 is_open이 인덱스 스킵 스캔 을 적용하면서 차이가 없을 수도 있겠다

  2. 특정 Product 조회

    • 사용하는 쿼리
     select * from product 
     left outer join description 
     on product.code = description.product_code
     where product.code = ? and product.is_open = true
    • content는 데이터 전체가 거의 필요로 하기 때문에 커버링 인덱스를 검토하기가 어렵다.
    • 하지만 is_open을 복합 인덱스의 첫 번째로 쓴다면 이것도 타고, 앞에 list도 타도록 할 수 있을 것 같다.

    • is_open을 인덱스로 만들고 실행 계획을 확인했을 때, PRIMARY만 탄다

      8

결론적으로는 초기에 설계된 인덱스에서 더 수정할 부분은 찾지 못했다. (처음부터 설계를 잘 한걸로) 이거 말고도 인덱스 컨디션 푸쉬다운도 실행계획을 본게 있는데, 이건 Real MySQL 9장을 읽으면서 또 실제 실행해본걸 공유해보겠다.

Categories:

Updated:

Leave a comment

You may also enjoy

MySQL의 InnoDB가 MVCC를 구현하는 법

DBMS의 격리성 수준은 알고있었으나 내부적으로 어떻게 동작하는지는 알지 못했다. 데드락 문제를 마주하면서 기본은 알고있어야 나중에 트러블 슈팅하기 수월할 것 같아서 MySQL 책을 구매했다.