B-Tree index 사용에 영향을 미치는 요소
B-Tree 인덱스는 인덱스를 구성하는 컬럼의 크기, 레코드 개수, 유니크 인덱스 키 값의 개수 등에 의해 검색이나 변경 작업 성능에 영향을 받는다.
인덱스 키 값의 크기
InnoDB 엔진은 디스크에 데이터를 저장하는 기본 단위를 페이지(Page) 또는 블록(Block)이라고 하며, 디스크의 모든 읽기/쓰기 작업의 최소 작업 단위가 된다. 인덱스도 페이지 단위로 관리되며, 루트, 브랜치, 리프 노드를 구분하는 기준이 페이지 단위이다.
일반적으로 DBMS의 B-Tree는 자식 노드의 개수가 가변적이다. MySQL은 인덱스 페이지의 크기와 키 값의 크기에 따라 자식 노드의 개수가 결정된다.
MySQL 5.7 버전부터 innodb_page_size 시스템 변수를 이용해서 4KB ~ 64KB 사이의 페이지 크기를 지정할 수 있지만 기본값은 16KB이다. (1KB = 1024bytes)
인덱스 키가 16bytes라고 가정하면 그림과 같이 인덱스 페이지가 구성된다.
자식 노드 주소는 페이지 종류별로 약 6bytes ~ 12bytes까지 크기의 값을 가질 수 있다.
위와 같은 경우는 하나의 인덱스 페이지(16KB)에 16 * 1024 / (16 + 12) = 585개의 키를 저장할 수 있다.
인덱스 키 사이즈가 커지면 하나의 인덱스 페이지에 저장할 수 있는 키의 개수가 줄어든다.
만약 select 쿼리로 500개의 레코드를 읽어야 하는데, 키 사이즈가 16bytes인 경우 인덱스 페이지 한 번으로 해결이 가능하다. 하지만 32bytes라면 최소 2번 이상 디스크로부터 읽어야 한다.
따라서 인덱스 키 값의 사이즈가 커질수록 디스크의 읽는 횟수가 많아지고 이는 disk I/O가 더 많이 발생하므로 그만큼 쿼리 수행 시간이 느려진다.
또, 인덱스 키 사이즈가 커지면 메모리에 캐시해 둘 수 있는 레코드 수가 줄어들기 때문에 메모리 효율성 저하로 이어진다.
선택도(기수셩 = 카디널리티)
인덱스에서 선택도(Selectivity) 또는 기수성(카디널리티)은 같은 의미로 사용되며, 모든 인덱스의 키 값 중 유니크한 값의 수를 의미한다.
country 컬럼과 city 컬럼이 포함된 tb_test 테이블이 있다. 테이블의 전체 레코드 건수는 1만이고, country 컬럼만 인덱스가 생성된 상태이다
select * from tb_test where country=’korea’ and city=’seoul’;
케이스 A: country 컬럼의 유니크 값의 개수 10개
케이스 B: country 컬럼의 유니크 값의 개수 1000개
인덱스 선택성 계산: 선택성 = 유니크한 값의 수 / 전체 레코드 수
MySQL에서 인덱스 통계 정보(유니크한 값의 개수)가 관리되기 때문에 city 칼럼의 기수성은 작업 범위에 아무런 영향을 미치지 못한다. 위 쿼리를 실행하면 A의 경우 평균 1000건, B의 경우 평균 10건 조회될 수 있다.
A, B 케이스 모두 실제 모든 조건을 만족하는 레코드가 단 1건일 떄, A 케이스는 1건의 레코드를 위해 쓸모없는 999건의 레코드를 더 읽은 것이고, B 케이스는 9건만 더 읽은 것이다.
두 케이스는 똑같은 쿼리를 실행했을 때 같은 결과를 받지만, 인덱스의 선택성(카디널리티)가 높을수록 쿼리 성능이 향상된다.
읽어야 하는 레코드 건수
인덱스를 통해 테이블의 레코드를 읽는 것이 그렇지 않은 것보다 더 높은 비용이 드는 작업이다.
테이블 레코드가 100만 건이 있을 때, 50만 건을 읽어야 하는 쿼리가 있다. 여기서 우리는 테이블을 모두 스캔하여 50만 건을 가져올지, 인덱스를 통해 필요한 50만 건만 가져올지에 대한 효율성을 판단해야 한다.
일반적인 DBMS의 옵티마이저에서는 인덱스를 통해 레코드 1건을 읽는 것이 테이블에서 읽는 것보다 4~5배 정도 더 비용이 발생한다고 예측한다.
즉, 인덱스를 통해 읽어야 할 레코드의 건수가 전체 테이블 레코드의 20~25%를 넘어서면 인덱스를 사용하지 않고 테이블을 모두 직접 읽어서 필요한 레코드만 필터링하는 것이 효율적이라고 소개한다.
100만 건의 레코드에서 50만 건을 읽어야 하는 작업은 인덱스의 손익 분기점인 20~25%보다 훨씬 크기 때문에 MySQL 옵티마이저는 인덱스를 이용하지 않고 테이블을 처음부터 끝까지 읽어서 처리할 것이다.
이렇게 많은 레코드(전체 레코드의 20~25% 이상)를 읽을 때는 강제로 인덱스를 사용하도록 힌트를 추가해도 성능상 얻을 수 있는 이점이 없다. 물론 이러한 작업은 MySQL 옵티마이저가 기본적으로 힌트를 무시하고 테이블을 직접 읽는 방식으로 처리하겠지만 이러한 내용은 숙지하고 있어서 할 사항이다.
힌트(Hint): MySQL 옵티마이저에게 쿼리 실행 계획에 대한 추가적인 정보를 제공하는 지시문이다.
USER INDEX: 특정 인덱스를 사용하도록 권장한다.
FORCE INDEX: USER INDEX 보다 강력하게 특정 인덱스 사용을 지시한다.
IGNORE INDEX: 특정 인덱스 사용을 금지한다.
Last updated