[MySQL] INDEX 를 알아보자 (1)

인덱스를 사용했을 때

어떤 데이터가 테이블에서 저장되거나 삭제될 때 성능을 희생하지만 데이터를 조회할 때 시간을 단축시킬 수 있게 해준다.
하지만 무자비하게 인덱스를 집어넣으면 데이터 저장 성능이 상당히 떨어지는 역효과가 날 수 있다. 데이터를 관리하는 방식에 따라 여러가지의 인덱스로 나눠질 수 있습니다.

역할 별로 인덱스를 나눈다면 id 값으로 자주 사용하는 키인 Primary Key(기본키) 와 Secondary Key(보조 키) 로 구분할 수 있다.

데이터 저장 방식으로 구분할 경우 대표적으로 B-tree(Balance-tree) 와 Hash 로 구분할 수 있다.

• B-tree 는 컬럼의 값을 변형하지 않고 원래 값을 이용하여 인덱싱을 진행한다.
• Hash 는 컬럼의 값으로 해시값을 계산하여 인덱싱을 진행한다. 속도가 매우 빠르지만 값을 변형하여 인덱싱을 진행하기에 값의 일부 검색, 범위 검색을 진행하는 경우 인덱스를 사용할 수 없다. 주로 메모리 DB에서 많이 사용한다고 한다.

B-Tree Index

DB의 인덱싱 알고리즘 중에서 가장 일반적으로 사용되는 인덱싱 알고리즘이라고 한다. B-Tree 는 이름과 같이 트리 구조로 되어 있는데, 가장 위에 위치하는 노드가 Root node, 가장 밑에 자식 없이 붙어 있는 노드가 Leaf node, 가장 위도 아래도 아닌 노드가 Branch node 이다. DB 에서 인덱스와 실제 데이터가 저장된 데이터는 따로 분리되는데, 리프 노드는 항상 실제 데이터의 주소값을 가지고 있다.

B-Tree 의 Index 키 추가 및 삭제

테이블에 레코드를 변경하는 경우 인덱스 키 추가 혹은 삭제 작업이 발생한다.

• [저장] 새로운 값이 B-Tree 인덱스에 저장될 때 테이블의 스토리지 엔진에 따라 새로운 값이 즉시 인덱스에 저장될 수 있고 그렇지 않을 수도 있다. 새로운 값이 저장될 때 저장될 키 값을 이용하여 적절한 위치를 검색해야 한다. 위치가 결정되면 리프 노드에 저장될 데이터의 키 값과 주소를 저장한다. 리프 노드에 공간이 없는 경우 리프 노드를 분리시켜야 하는데 이 경우 브랜치 노드까지 영향이 가기 때문에 범위가 넓어진다. 이러한 작업 때문에 B-Tree 가 쓰기 작업에 비용이 많이 드는 것이라고 한다. MyISAM 혹은 MEMORY 엔진을 사용하는 테이블에서는 INSERT 실행 후 즉시 반영하지만 InnoDB는 필요한 경우 이 작업을 지연시키는 기능이 있다.(체인지 버퍼) 하지만 중복 체크가 필요한 값이 있는 경우 (PK, Unique) 즉시 반영한다고 한다.
• [삭제] 해당 키 값이 저장된 리프 노드를 찾아서 삭제만 하면 작업이 종료된다. 이 작업 또한 InnoDB 에서는 체인지 버퍼가 적용되어 지연될 수 있다고 한다.
• [변경] 인덱스 키 값이 변경되는 경우 그 에 따라 저장될 리프 노드의 위치도 변경되어야 하기 때문에 변경 작업이 일어나면 먼저 해당 키를 삭제한 후 다시 새로운 키 값을 추가하는 식으로 진행되고 InnoDB 에서는 마찬가지로 지연될 수 있다고 한다.
• [검색] 데이터가 변경될 때의 비용을 감수하면서까지 인덱싱을 하는 이유는 검색때문이다. 인덱스를 검색하는 작업은 루트 노드부터 브랜치 노드를 거쳐 리프 노드에까지 이동하면서 비교 작업을 수행하는데 이 과정을 트리 탐색이라고 한다. 트리 탐색은 SELECT 구문에서만 발생하는 것이 아닌 UPDATE, DELETE 구문에서도 사용된다. B-Tree 를 이용한 검색은 100% 일치 (=) 혹은 값의 앞 부분만 일치하는 경우에 사용할 수 있다. 또한 키 값이 변경된 상태로 비교하는 경우 빠른 검색 기능을 사용할 수 없는데, 변경된 키 값의 경우 이미 인덱스에 존재하는 값이 아니기 때문이다. 함수 혹은 연산으로 인해 변경된 값도 마찬가지이다.

B-Tree Index 사용에 영향을 미치는 요소

인덱스를 구성하는 컬럼의 크기와 레코드의 건 수 그리고 유니크한 인덱스 키 값의 의해 영향을 받는다.

인덱스 키 값의 크기

• InnoDB 엔진은 디스크에 데이터를 저장할 때 가장 작은 단위를 페이지(page) 혹은 블록(block) 이라고 하고 디스크의 모든 I/O 작업의 최소 작업 단위가 된다.
• MySQL의 B-Tree는 자식 노드의 개수가 가변적인 구조인데 인덱스 페이지의 크기에 따라 결정된다. 5.7 버전 이상부터 innodb_page_size 시스템 변수를 이용하여 4KB ~ 64KB 까지 설정할 수 있는데 기본 값은 16KB 이다.
• 인덱스 키 값의 크기가 커지면 커질 수록 하나의 페이지 안쪽에 저장할 수 있는 키 값이 줄어들게 되고 이는 인덱스를 이용한 검색을 할 때 페이지를 다음 페이지까지 스캔을 해야하는 상황이 발생하고 그만큼 검색이 느려진다.
• 전체적인 인덱스의 크기가 커지면 인덱스를 캐싱하는 영역이 좁아져 캐시할 수 있는 데이터가 줄어들게 된다.

B-Tree 깊이

• 깊이는 상당히 중요하지만 직접 제어할 수 있는 방법은 없다.
• 깊이는 데이터를 검색할 때 몇 번이나 랜덤하게 디스크를 읽어야 하는지와 직결된다.
• 인덱스의 키 값의 크기가 커지면 하나의 페이지에 담을 수 있는 개수가 줄어들고 이는 노드가 더 붙어서 깊어지는 결과로 이어진다.

선택도(기수성)

• 선택도(Selectivity) 와 기수성(Cardinality) 는 거의 같은 의미로 사용된다.
• 모든 인덱스 중 유니크 값의 숫자를 의미한다. 전체 인덱스 키 값은 100개인데 그 중 유니크한 값이 10개라면 기수성은 10이 된다.
• 인덱스는 선택도가 높을수록 검색할 대상이 줄어들어서 그만큼 속도가 빨라진다.
• 밑의 DDL 문으로 생성되는 테이블에 1만개의 Row 를 집어넣고 케이스 A, B로 구분하여 알아보자. name은 중복이 없다는 가정 하에 케이스 A는 scale 컬럼의 유니크한 값이 10개이고 케이스 B는 1000개이다.

CREATE TABLE gunpla(
    scale VARCHAR(25),
    name VARCHAR(50),
    INDEX idx_scale (scale)
);

SELECT *
FROM gunpla
WHERE scale = '1/144' AND name = 'RX-78-2'

• 케이스 A의 경우 유니크 값이 10개이므로 10개 등급의 프라모델들의 이름이 저장 돼 있다. MySQL 서버는 인덱스된 컬럼에 대해서는 전체 건수 혹은 유니크 값의 개수를 가지고 있는데 여기서 전체 건수를 유니크한 값으로 나눠보면 하나의 키 값으로 검색했을 때 몇 개가 일치할 지 알 수 있다. scale이 1/144 조건으로 검색을 하면 1,000건 (10,000/10) 이 일치하는 것을 예상할 수 있고, 그 1,000건 중 에서 name이 RX-78-2 인 프라모델은 1개 이기 때문에 나머지 999건은 불필요하게 읽은 것으로 판단할 수 있다.
• 케이스 B의 경우는 유니크 값이 1,000개니까 10건이 일치하고, 10건 중 name이 RX-78-2 인 프라모델은 1개 이기 때문에 9건만 불필요하게 읽힌 것으로 판단할 수 있다.

읽어야 하는 레코드의 건수

• 인덱스를 통해 테이블의 레코드를 읽는 것은 인덱스를 통하지 않고 읽는 것 보다 더 많은 비용이 드는 작업이다.
• 데이터 100만 건이 저장돼 있는 경우 전체 테이블을 다 읽어서 필요 없는 건을 버릴지, 인덱스로 처음 부터 필요한 것만 가져올 지 판단해야 한다.
• 판단하기 위해서 "손익분기점" 을 파악해야 하는데, 일반적인 DBMS의 옵티마이저는 인덱스를 통해 레코드 1건을 읽는 것이 테이블에서 직접 읽는 것보다 4~5배 정도 비용이 많이 드는 것으로 파악되고 있다.
• 인덱스를 통해 읽어야 하는 건수가 25% 를 넘어가면 그냥 테이블을 전부 읽어서 필요한 레코드만 필터링하는 쪽이 효율적일 수 있다.