-
Real MySQL 8.0 2 : 인덱스책책책 책을 읽읍시다 2023. 4. 29. 00:23
인덱스 기본
인덱스를 역할별로 구분해 본다면 프라이머리 키(Primary Key)와 보조 키(세컨더리 인덱스, Secondary key)로 구분할 수 있다.
- 프라이머리 키는 이미 잘 아는 것처럼 그 레코드를 대표하는 컬럼의 값으로 만들어진 인덱스를 의미한다. 이 컬럼(때로는 컬럼의 조합)은 테이블에서 해당 레코드를 식별할 수 있는 기준값이 되기 때문에 우리는 이를 식별자라고도 부른다. 프라이머리 키는 NULL 값을 허용하지 않으며 중복을 허용하지 않는 것이 특징이다.
- 프라이머리 키를 제외한 나머지 모든 인덱스는 세컨더리 인덱스(Secondary Index)로 분류한다. 유니크 인덱스는 프라이머리 키와 성격이 비슷하고 프라이머리 키를 대체해서 사용할 수도 있다고 해서 대체 키라고도 하는데, 별도로 분류하기도 하고 그냥 세컨더리 인덱스로 분류하기도 한다.
데이터 저장 방식(알고리즘)별로 구분할 경우 사실 상당히 많은 분류가 가능하겠지만 대표적으로 B-Tree 인덱스와 Hash 인덱스로 구분할 수 있다. 그리고 최근에는 Fractal-Tree 인덱스나 로그 기반의 Merge-Tree 인덱스와 같은 알고리즘을 사용하는 DBMS도 개발되고 있다. 물론 이 외에도 수많은 알고리즘이 있지만 대표적으로 시중의 RDBMS에서 많이 사용하는 알고리즘은 이 정도일 것이다.
- B-Tree 알고리즘은 가장 일반적으로 사용되는 인덱스 알고리즘으로서, 상당히 오래전에 도입된 알고리즘이며 그만큼 성숙해진 상태다. B-Tree 인덱스는 컬럼의 값을 변형하지 않고 원래의 값을 이용해 인덱싱하는 알고리즘이다. MySQL 서버에서는 위치 기반 검색을 지원하기 위한 R-Tree 인덱스 알고리즘도 있지만, 결국 R-Tree 인덱스는 B-Tree의 응용 알고리즘으로 볼 수 있다.
- Hash 인덱스 알고리즘은 컬럼의 값으로 해시값을 계산해서 인덱싱하는 알고리즘으로, 매우 빠른 검색을 지원한다. 하지만 값을 변형해서 인덱싱하므로 전방(Prefix) 일치와 같이 값의 일부만 검색하거나 범위를 검색할 때는 해시 인덱스를 사용할 수 없다. Hash 인덱스는 주로 메모리 기반의 데이터베이스에서 많이 사용한다.
데이터의 중복 허용 여부로 분류하면 유니크 인덱스(Unique)와 유니크하지 않은 인덱스(Non-Unique)로 구분할 수 있다. 인덱스가 유니크한지 아닌지는 단순히 같은 값이 1개만 존재하는지 1개 이상 존재할 수 있는지를 의미하지만, 실제 DBMS의 쿼리를 실행해야 하는 옵티마이저에게는 상당히 중요한 문제가 된다. 유니크 인덱스에 대해 동등 조건(Equal, =)으로 검색한다는 것은 항상 1건의 레코드만 찾으면 더 찾지 않아도 된다는 것을 옵티마이저에게 알려주는 효과를 낸다. 그뿐만 아니라 유니크 인덱스로 인한 MySQL의 처리 방식의 변화나 차이점이 상당히 많다.
B-Tree
인덱스 키 값의 크기
InnoDB 스토리지 엔진은 디스크에 데이터를 저장하는 가장 기본 단위를 페이지(Page) 또는 블록(Block)이라고 하며, 디스크의 모든 읽기 및 쓰기 작업의 최소 작업 단위가 된다. 또한 페이지는 InnoDB 스토리지 엔진의 버퍼 풀에서 데이터를 버퍼링하는 기본 단위이기도 하다. 인덱스도 결국 페이지 단위로 관리되며, 루트와 브랜치, 그리고 리프 노드를 구분한 기준이 바로 페이지 단위다.
이진(Binary) 트리는 각 노드가 자식 노드를 2개만 가지는데, DBMS의 B-Tree가 이진 트리라면 인덱스 검색이 상당히 비효율 적이다. B-Tree의 B는 Balanced를 뜻하고, 일반적으로 B-Tree는 자식 노드의 개수가 가변적인 구조다. MySQL의 B-Tree 자식 노드는 인덱스의 페이지 크기와 값 크기에 따라 결정된다. MySQL 5.7 버전부터는 InnoDB 스토리지 엔진의 페이지 크기를 innodb_page_size 시스템 변수를 이용해 4KB ~ 64KB 사이의 값을 선택할 수 있지만 기본값은 16KB다. 인덱스의 키가 16바이트라고 가정하면 다음 그림과 같이 인덱스 페이지가 구성될 것이다. 그림에서 자식 노드 주소라는 것은 여러 가지 복합적인 정보가 담긴 영역이며, 페이지의 종류별로 대략 6바이트에서 12바이트까지 다양한 크기의 값을 가질 수 있다. 편의상 자식 노드 주소 영역이 평균적으로 12바이트로 구성된다고 가정하자.
인덱스 페이지의 구성 그림의 경우 하나의 인덱스 페이지(16KB)에 몇 개의 키를 저장할 수 있을까? 계산해 보면 16*1024(16+12) = 585개 저장할 수 있다. 최종적으로 이 경우는 자식 노드를 585개를 가질 수 있는 B-Tree가 되는 것이다. 그러면 인덱스 키 값이 커지면 어떤 현상이 발생할까? 이 경우에서 키 값의 크기가 두 배인 32바이트로 늘어났다고 가정하면 한 페이지에 인덱스 키를 16*1024(32+12) = 372개 저장할 수 있다. 우리의 SELECT 쿼리가 레코드 500개를 읽어야 한다면 전자는 인덱스 페이지 한번으로 해결될 수도 있지만, 후자는 최소한 2번 이상 디스크로부터 읽어야 한다. 결국 인덱스를 구성하는 키 값의 크기가 커지면 디스크로부터 읽어야 하는 횟수가 늘어나고, 그만큼 느려진다는 것을 의미한다.
또한 인덱스 키 값의 길이가 길어진다는 것은 전체적인 인덱스의 크기가 커진다는 것을 의미한다. 하지만 인덱스를 캐시해 두는 InnoDB의 버퍼 풀이나 MyISAM의 키 캐시 영역은 크기가 제한적이기 때문에 하나의 레코드를 위한 인덱스 크기가 커지면 커질수록 메모리에 캐시해 둘 수 있는 레코드 수는 줄어든다. 그렇게 되면 자연히 메모리의 효율이 떨어지는 결과를 가져온다.
B-Tree 깊이
B-Tree 인덱스의 깊이(Depth)는 상당히 중요하지만 직접 제어할 방법은 없다. 대신 인덱스 키 값의 평균 크기가 늘어나면 어떤 현상이 추가로 더 발생하는지 관점에서 보자. 위 예제로 다시 돌아가 인덱스의 B-Tree 깊이가 3인 경우 최대 몇 개의 키 값을 가질 수 있는지 한 번 비교해보자. 키 값이 16바이트인 경우에는 최대 2억(585*585*585)개 정도의 키 값을 담을 수 있지만, 키 값이 32바이트로 늘어나면 5천만(372*372*372)개로 줄어든다. B-Tree의 깊이는 MySQL에서 값을 검색할 때 몇 번이나 랜덤하게 디스크를 읽어야 하는지와 직결되는 문제다. 결론적으로 인덱스 키 값의 크기가 커지면 커질수록 하나의 인덱스 페이지가 담을 수 있는 인덱스 키 값의 개수가 적어지고, 그 때문에 같은 레코드 건수라 하더라도 B-Tree의 깊이(Depth)가 깊어져서 디스크 읽기가 더 많이 필요하게 된다는 것을 의미한다.
여기서 언급한 내용은 사실 인덱스 키 값의 크기는 가능하면 작게 만드는 것이 좋다는 것을 강조하기 위함이고, 실제로는 아무리 대용량 데이터베이스라도 B-Tree의 깊이가 5단계 이상까지 깊어지는 경우는 흔치 않다.
선택도(기수성)
인덱스에서 선택도(Selectivity) 또는 기수성(Cardinality)은 거의 같은 의미로 사용되며, 모든 인덱스 키 값 가운데 유니크한 값의 수를 의미한다. 전체 인덱스 키 값으 100개인데, 그중에서 유니크한 값의 수는 10개라면 기수성은 10이다. 인덱스 키 값 가운데 중복된 값이 많아지면 많아질수록 기수성은 낮아지고 동시에 선택도 또한 떨어진다. 인덱스는 선택도가 높을수록 검색 대상이 줄어들기 때문에 그만큼 빠르게 처리된다.
선택도가 좋지 않다고 하더라도 정렬이나 그루핑과 같은 작업을 위해 인덱스를 만드는 것이 훨씬 나은 경우도 많다. 인덱스가 항상 검색에만 사용되는 것은 아니므로 여러 가지 용도를 고려해 적절히 인덱스를 설계할 필요가 있다.
country라는 컬럼과 city라는 컬럼이 포함된 tb_test라는 테이블이 있다고 하자. tb_test 테이블의 전체 레코드 건수는 1만 건이며, country 컬럼으로만 인덱스가 생성된 상태에서 아래의 두 경우를 살펴보자.
- 케이스 A : country 컬럼의 유니크한 값의 개수가 10개
- 케이스 B : country 컬럼의 유니크한 값의 개수가 1,000개
SELECT * FROM tb_test WHERE country = 'KOREA' AND city = 'SEOUL';MySQL에서는 인덱스의 통계 정보(유니크한 값의 개수)가 관리되기 때문에 city 컬럼의 기수성은 작업 범위에 아무런 영향을 미치지 못한다. 위의 쿼리를 실행하면 A 케이스의 경우네는 평균 1,000건, B 케이스의 경우에는 평균 10건이 조회될 수 있다는 것을 인덱스 통계 정보(유니크한 값의 개수)로 예측할 수 있다. A 케이스와 B 케이스 모두 실제 모든 조건을 만족하는 레코드는 단 1건만 있었다면 A 케이스의 인덱스는 적합하지 않은 것이라고 볼 수 있다. A 케이스는 1건의 레코드를 위해 쓸모없는 999건의 레코드를 덜 읽은 것이지만, B 케이스는 9건만 더 읽은 것이다. 그래서 A 케이스의 경우 country 컬럼에 생성된 인덱스는 비효율적이다. 물론 필요한 만큼의 레코드만 정확하게 읽을 수 있다면 최상이겠지만 현실적으로 모든 조건을 만족하게 인덱스를 생성한다는 것은 불가능하므로 이 정도의 낭비는 무시할 수 있다.
각 국가의 도시를 저장하는 tb_city라는 테이블을 예로 들어보겠다. tb_city 테이블은 1만 건의 레코드를 가지고 있는데, country 컬럼에만 인덱스가 준비돼 있다. tb_city 테이블에는 국가와 도시가 중복해서 저장돼 있지 않다고 가정하자.
CREATE TABLE tb_city( country VARCHAR(10), city VARCHAR(10), INDEX ix_country (country) );tb_city 테이블에 아래와 같은 쿼리를 한번 실행해보자. 이때 tb_city 테이블의 데이터 특성을 두 가지로 나눠서 내부적인 쿼리나 인덱스의 효율성을 살펴보자.
SELECT * FROM tb_test WHERE country = 'KOREA' AND city = 'SEOUL';country 컬럼의 유니크 값이 10개일 때
tb_city 테이블에는 10개 국가(country)의 도시(city) 정보가 저장돼 있는 것이다. MySQL 서버는 인덱스된 컬럼(country)에 대해서는 전체 레코드의 건수나 유니크한 값의 개수 등에 대한 통계 정보를 가지고 있다. 여기서 전체 레코드 건수를 유니크한 값의 개수로 나눠보면 하나의 키 값으로 검색했을 때 대략 몇 건의 레코드가 일치할지 예측할 수 있게 된다. 즉, 이 케이스의 tb_city 테이블에서는 country='KOREA'라는 조건으로 인덱스를 검색하면 1000건(10,000/10)이 일치하리라는 것을 예상할 수 있다. 그런데 인덱스를 통해 검색된 1000건 가운데 city='SEOUL'인 레코드는 1건이므로 999건은 불필요하게 읽은 것으로 볼 수 있다.
country 컬럼의 유니크 값이 1000개일 때
tb_city 테이블에는 1000개 국가(country)의 도시(city) 정보가 저장돼 있는 것이다. 이 케이스에서도 전체 레코드 건수를 국가 컬럼의 유니크 값 개수로 나눠보면 대략 한 국가당 대략 10개 정도의 도시 정보가 저장돼 있으리라는 것을 예측할 수 있다. 그래서 이 케이스에서는 tb_city 테이블에서 country='KOREA'라는 조건으로 인덱스를 검색하면 10건(10,000/1,000)이 일치할 것이며, 그 10건 중에서 city='SEOUL'인 레코드는 1건이므로 9건만 불필요하게 읽은 것이다.
위 두 케이스의 테이블에서 똑같은 쿼리를 실행해 똑같은 결과를 받았지만, 사실 두 쿼리가 처리되기 위해 MySQL 서버가 수행한 작업 내용은 매우 크다는 것을 알 수 있다. 이처럼 인덱스에서 유니크한 값의 개수는 인덱스나 쿼리의 효율성에 큰 영향을 미친다.
읽어야 하는 레코드의 건수
인덱스를 통해 테이블의 레코드를 읽는 것은 인덱스를 거치지 않고 바로 테이블의 레코드를 읽는 것보다 높은 비용이 드는 작업이다. 테이블에 레코드가 100만 건이 저장돼 있는데, 그중에서 50만 건을 읽어야 하는 쿼리가 있다고 가정해보자. 이 작업은 전체 테이블을 모두 읽어서 필요 없는 50만 건을 버리는 것이 효율적일지, 인덱스를 통해 필요한 50만 건만 읽어 오는 것이 효율적일지 판단해야 한다.
인덱스를 이용한 읽기의 손익 분기점이 얼마인지 판단할 필요가 있는데, 일반적인 DBMS의 옵티마이저에서는 인덱스를 통해 레코드 1건을 읽는 것이 테이블에서 직접 레코드 1건을 읽는 것보다 4~5배 정도 비용이 더 많이 드는 작업인 것으로 예측한다. 즉, 인덱스를 통해 읽어야 할 레코드의 건수(물론 옵티마이저가 판단한 예상 건수)가 전체 테이블 레코드의 20~25%를 넘어서면 인덱스를 이용하지 않고 테이블을 모두 직접 읽어서 필요한 레코드만 가려내는(필터링) 방식으로 처리하는 것이 효율 적이다.
전체 100만 건의 레코드 가운데 50만 건을 읽어야 하는 작업은 인덱스의 손익 분기점인 20~25%보다 훨씬 크기 때문에 MySQL 옵티마이저는 인덱스를 이용하지 않고 직접 테이블을 처음부터 끝까지 읽어서 처리할 것이다. 이렇게 많은 레코드(전체 레코드의 20~25% 이상)를 읽을 때는 강제로 인덱스를 사용하도록 힌트를 추가해도 성능상 얻을 수 있는 이점이 없다. 물론 이러한 작업은 MySQL의 옵티마이저가 기본적으로 힌트를 무시하고 테이블을 직접 읽는 방식으로 처리하겠지만 기본으로 알고 있어야 할 사항이다.
인덱스 레인지 스캔
인덱스의 접근 방법 가운데 가장 대표적인 접근 방식으로, 뒤에서 설명할 나머지 두 가지 접근 방식보다는 빠른 방법이다. 인덱스를 통해 레코드를 한 건만 읽는 경우와 한 건 이상을 읽는 경우를 각각 다른 이름으로 구분하지만, 모두 묶어 "인덱스 레인지 스캔"이라고 접근해보자. 아래 쿼리를 예제로 살펴보자.
SELECT * FROM employees WHERE first_name BETWEEN 'Ebbe' AND 'Gad'
인덱스를 이용한 레인지 스캔 인덱스 레인지 스캔은 검색해야 할 인덱스의 범위가 결정됐을 때 사용하는 방식이다. 검색하려는 값의 수나 검색 결과 레코드 건수와 관계없이 레인지 스캔이라고 표현한다. 그림의 화살표에서도 알 수 있듯이 루트 노드에서부터 비교를 시작해 브랜치 노드를 거치고 최종적으로 리프 노드까지 찾아 들어가야만 비로소 필요한 레코드의 시작 지점을 찾을 수 있다. 일단 시작해야 할 위치를 찾으면 그때부터는 리프 노드의 레코드만 순서대로 읽으면 된다 .이처럼 차례대로 쭉 읽는 것을 스캔이라고 표현한다. 만약 스캔하다가 리프 노드의 끝까지 읽으면 리프 노드 간의 링크를 이용해 다음 리프 노드를 찾아서 다시 스캔한다. 그리고 최종적으로 스캔을 멈춰야 할 위치에 다다르면 지금까지 읽은 레코드를 사용자에게 반환하고 쿼리를 끝낸다. 그림에서 두꺼운 선은 스캔해야 할 위치 검색을 비교 작업을 의마하며, 두꺼운 화살표가 지나가는 리프노드의 레코드 구간은 실제 스캔하는 범위를 표현한다.
그림은 실제 인덱스만을 읽는 경우를 보여준다. 하지만 B-Tree 인덱스의 리프 노드를 스캔하면서 실제 데이터 파일의 레코드를 읽어 와야 하는 경우도 많은데, 이 과정을 좀 더 자세히 살펴보자.
인덱스 레인지 스캔을 통한 데이터 레코드 읽기 B-Tree 인덱스에서 루트와 브랜치 노드를 이용해 스캔 시작 위치를 검색하고, 그 지점부터 필요한 방향(오름차순 또는 내림차순)으로 인덱스를 읽어 나가는 과정을 그림에서 확인할 수 있다. 중요한 것은 어떤 방식으로 스캔하든 관계없이, 해당 인덱스를 구성하는 컬럼의 정순 또는 역순으로 정렬된 상태로 레코드를 가져온다는 것이다. 이는 별도의 정렬 과정이 수반되는 것이 아니라 인덱스 자체의 정렬 특성 때문에 자동으로 그렇게 된다.
그림에서 또 한 가지 중요한 것은 인덱스의 리프 노드에서 검색 조건에 일치하는 건들은 데이터 파일에서 레코드를 읽어오는 과정이 필요하다는 것이다. 이때 리프 노드에 저장된 레코드 주소로 데이터 파일의 레코드를 읽어오는데, 레코드 한 건 한 건 단위로 랜덤 I/O가 한 번씩 일어난다. 그림에서 처럼 3건의 레코드가 검색 조건에 일치했다고 가정하면, 데이터 레코드를 읽기 위해 랜덤 I/O가 최대 3번 필요한 것이다. 그래서 인덱스를 통해 데이터 레코드를 읽는 작업은 비용이 많이 드는 작업으로 분류된다. 그리고 인덱스를 통해 읽어야 할 데이터 레코드가 20~25%를 넘으면 인덱스를 통한 읽기보다 테이블의 데이터를 직접 읽는 것이 더 효율적인 처리 방식이 된다.
인덱스 레인지 스캔은 다음과 같이 크게 3단계를 거친다는 점을 살펴봤다.
- 인덱스에서 조건을 만족하는 값이 저장된 위치를 찾는다. 이 과정을 인덱스 탐색(Index seek)이라고 한다.
- 1번에서 탐색된 위치부터 필요한 만큼 인덱스를 차례대로 쭉 읽는다. 이 과정을 인덱스 스캔(Index scan)이라고 한다.(1번과 2번 합쳐서 인덱스 스캔으로 통칭하기도 한다.)
- 2번에서 읽어 들인 인덱스 키와 레코드 주소를 이용해 레코드가 저장된 페이지를 가져오고 , 최종 레코드를 읽어 온다.
쿼리가 필요로 하는 데이터에 따라 3번 과정은 필요하지 않을 수도 있는데, 이를 커버링 인덱스라고 한다. 커버링 인덱스로 처리되는 쿼리는 디스크의 레코드를 읽지 않아도 되기 때문에 랜덤 읽기가 상당히 줄어들고 성능은 그만큼 빨라진다.
인덱스 풀 스캔
인덱스 레인지 스캔과 마찬가지로 인덱스를 사용하지만 인덱스 레인지 스캔과는 달리 인덱스의 처음부터 끝까지 모두 읽는 방식을 인덱스 풀 스캔이라고 한다. 대표적으로 쿼리의 조건절에 사용된 컬럼이 인덱스의 첫 번째 컬럼이 아닌 경우 인덱스 풀 스캔 방식이 사용된다. 예를 들어, 인덱스는 (A, B, C) 컬럼의 순서로 만들어져 있지만 쿼리의 조건절은 B컬럼이나 C컬럼으로 검색하는 경우다.
일반적으로 인덱스의 크기는 테이블의 크기보다 작으므로 직접 테이블을 처음부터 끝까지 읽는 것보다는 인덱스만 읽는 것이 효율적이다. 쿼리가 인덱스에 명시된 컬럼만으로 조건을 처리할 수 있는 경우 주로 이 방식이 사용된다. 인덱스뿐만 아니라 데이터 레코드까지 모두 읽어야 한다면 절대 이 방식으로 처리되지 않는다. 간단하게 그림으로 인덱스 풀 스캔의 처리 방식을 살펴보자.
인덱스 풀 스캔 그림에서 인덱스 풀 스캔의 예를 살펴볼 수 있다. 먼저 인덱스 리프 노드의 제일 앞 또는 제일 뒤로 이동한 후, 인덱스의 리프 노드를 연결하는 링크드 리스트(Linked list, 리프 노드 사이를 연결하는 세로로 그려진 두 쌍의 화살표)를 따라서 처음부터 끝까지 스캔하는 방식을 인덱스 풀 스캔이라고 한다. 이 방식은 인덱스 레인지 스캔보다는 빠르지 않지만 테이블 풀 스캔보다는 효율적이다. 앞에서도 언급했듯이 인덱스에 포함된 컬럼만으로 쿼리를 처리할 수 있는 경우 테이블의 레코드를 읽을 필요가 없기 때문이다. 인덱스의 전체 크기는 테이블 자체의 크기보다는 훨씬 작으므로 인덱스 풀 스캔은 테이블 전체를 읽는 것보다는 적은 디스크 I/O로 쿼리를 처리할 수 있다.
루스 인덱스 스캔
많은 사용자에게 루스(Loose) 인덱스 라는 단어는 상당히 생소할 것이다. 오라클과 같은 DBMS의 "인덱스 스킵 스캔"이라고 하는 기능과 작동 방식은 비슷하지만 MySQL에서는 이를 "루스 인덱스 스캔"이라고 한다. MySQL 5.7 버전까지는 MySQL의 루스 인덱스 스캔 기능이 많이 제한적이었지만, MySQL 8.0 버전부터는 다른 상용 DBMS에서 지원하는 인덱스 스킵 스캔과 같은 최적화를 조금씩 지원하기 시작했다. 앞에서 소개한 두 가지 접근 방법("인덱스 레인지 스캔"과 "인덱스 풀 스캔")은 "루스 인덱스 스캔"과는 상반된 의미에서 "타이트(Tight) 인덱스 스캔"으로 분류한다. 루스 인덱스 스캔이란 말 그대로 느슨하게 또는 듬성듬성하게 인덱스를 읽는 것을 의미한다.
루스 인덱스 스캔(dept_name과 first_name 컬럼은 참조용으로 표시됨) 루스 인덱스 스캔은 인덱스 레인지 스캔과 비슷하게 작동하지만 중간에 필요치 않은 인덱스 키 값은 무시(SKIP)하고 다음으로 넘어가는 형태로 처리한다. 일반적으로 GROUP BY 또는 집합 함수 가운데 MAX() 또는 MIN() 함수에 대해 최적화를 하는 경우에 사용된다.
SELECT dept_no, MIN(emp_no) FROM dept_emp WHERE dept_no BETWEEN 'd002' AND 'd004' GROUP BY dept_no;이 쿼리에서 사용된 dept_emp 테이블은 dept_no와 emp_no라는 두 개의 컬럼으로 인덱스가 생성돼 있다. 또한 이 인덱스는 (dept_no, emp_no) 조합으로 정렬까지 돼 있어서 그림에서와 같이 dept_no 그룹별로 첫 번째 레코드의 empt_no 값만 읽으면 된다. 즉 인덱스에서 WHERE 조건을 만족하는 범위 전체를 다 스캔할 필요가 없다는 것을 옵티마이저는 알고 있기 때문에 조건에 만족하지 않는 레코드는 무시하고 다음 레코드로 이동한다. 그림을 보면 인덱스 리프 노드를 스캔하면서 불필요한 부분은 그냥 무시하고 필요한 부분(회색 바탕 색깔의 레코드)만 읽었음을 알 수 있다.
B-Tree 인덱스의 가용성과 효율성
비교 조건의 종류와 효율성
다중 컬럼 인덱스에서 각 컬럼의 순서와 그 컬럼에 사용된 조건이 동등 비교("=")인지 아니면 크다(">") 또는 작다("<") 같은 범위 조건인지에 따라 각 인덱스 컬럼의 활용 형태가 달라지며, 그 효율 또한 달라진다. 다음 예제를 한번 살펴보자.
SELECT * FROM dept_emp WHERE dept_no = 'd002' AND ep_no >= 10114;이 쿼리를 위해 dept_emp 테이블에 각각 컬럼의 순서만 다른 두 가지 케이스로 인덱스를 생성했다고 가정하자. 위의 쿼리가 처리되는 동안 각 인덱스에 어떤 차이가 있었는지 살펴보자.
- 케이스 A : INDEX (dept_no, emp_no)
- 케이스 B : INDEX (emp_no, dept_no)
케이스 A 인덱스는 "dept_no = 'd002' AND emp_no >= 10144"인 레코드를 찾고, 그 이후에는 dept_no가 'd002'가 아닐 때까지 인덱스를 그냥 쭉 읽기만 하면 된다. 이 경우에는 읽은 레코드가 모두 사용자가 원하는 결과임을 알 수 있다. 즉, 조건을 만족하는 레코드가 5건이라고 할 때, 5건의 레코드를 찾는 데 꼭 필요한 5번의 비교 작업만 수행한 것이므로 상당히 효율적으로 인덱스를 이용한 것이다. 하지만 케이스 B 인덱스는 우선"emp_no >= 10144 AND dept_no = 'd002'"인 레코드를 찾고, 그 이후 모든 레코드에 대해 dept_no가 'd002'인지 비교하는 과정을 거쳐야 한다. 그림은 두 인덱스의 검색 과정을 보여준다.
인덱스의 컬럼 순서로 인한 쿼리 실행 내역의 차이 이처럼 인덱스를 통해 읽은 레코드가 나머지 조건에 맞는지 비교하면서 취사선택하는 작업을 '필터링'이라고도 한다. 케이스 B 인덱스에서는 최종적으로 dept_no='d002' 조건을 만족(필터링)하는 레코드 5건을 가져온다. 즉, 이 경우에는 5건의 레코드를 찾기 위해 7번의 비교 과정을 거친 것이다. 왜 이런 현상이 발생했을까? 그 이유는 인덱스의 N번째 키 값은 N-1번째 키 값에 대해서 다시 정렬되기 때문이다. 케이스 A 인덱스에서 2번째 컬럼인 emp_no는 비교 작업의 범위를 좁히는 데 도움을 준다. 하지만 케이스 B 인덱스에서 2번째 컬럼인 dept_no는 비교 작업의 범위를 좁히는 데 아무런 도움을 주지 못하고, 단지 쿼리의 조건에 맞는지 검사하는 용도로만 사용된다.
공식적인 명칭은 아니지만 케이스 A 인덱스에서의 두 조건(dept_no='d002'와 emp_no>=10144)과 같이 작업의 범위를 결정하는 조건을 '작업 범위 결정 조건'이라 하고, 케이스 B 인덱스의 dept_no='d002' 조건과 같이 비교 작업의 범위를 줄이지 못하고 단순히 거름종이 역할만 하는 조건을 '필터링 조건' 또는 '체크 조건'이라고 표현한다. 결국, 케이스 A 인덱스에서 dept_no 컬럼과 emp_no 컬럼은 모두 '작업 범위 결정 조건'에 해당하지만, 케이스 B 인덱스에서는 emp_no 컬럼만 '작업 범위 결정 조건'이고, dept_no 컬럼은 '필터링 조건'으로 사용된 것이다. 작업 범위를 결정하는 조건은 많으면 많을수록 쿼리의 처리 성능을 높이지만 체크 조건은 많다고 해서 (최종적으로 가져오는 레코드는 작게 만들지 몰라도) 쿼리의 처리 성능을 높이지는 못한다. 오히려 쿼리 실행을 더 느리게 만들 때가 많다.
인덱스의 가용성
B-Tree 인덱스의 특징은 왼쪽 값에 기준해서(Left-most) 오른쪽 값이 정렬돼 있다는 것이다. 여기서 왼쪽이란 하나의 컬럼 내에서뿐만 아니라 다중 컬럼 인덱스의 컬럼에 대해서도 함께 적용된다.
- 케이스 A : INDEX (first_name)
- 케이스 B : Index (dept_no, emp_no)
왼쪽 값(Left-most)을 기준으로 정렬 그림에서는 인덱스 키 값의 정렬만 표현하지만 사실은 인덱스 키 값의 이런 정렬 특성은 빠른 검색의 전제 조건이다. 즉 하나의 컬럼으로 검색해도 값의 왼쪽 부분이 없으면 인덱스 레인지 스캔 방식의 검색이 불가능하다. 또한 다중 컬럼 인덱스에서도 왼쪽 컬럼의 값을 모르면 인덱스 레인지 스캔을 사용할 수 없다.
케이스 A의 인덱스가 지정된 employees 테이블에 대해 다음과 같은 쿼리가 어떻게 실행되는지 한번 살펴보자.
SELECT * FROM employees WHERE first_name LIKE '%mer';이 쿼리는 인덱스 레인지 스캔 방식으로 인덱스를 이용할 수는 없다. 그 이유는 first_name 컬럼에 저장된 값의 왼쪽부터 한 글자씩 비교해 가면서 일치하는 레코드를 찾아야 하는데, 조건절에 주어진 상숫값('%mer')에는 왼쪽 부분이 고정되지 않았기 때문이다. 따라서 정렬 우선순위가 낮은 뒷부분의 값만으로는 왼쪽 기준(Left-most) 정렬 기반의 인덱스인 B-tree에서는 인덱스의 효과를 얻을 수 없다. 케이스 B의 인덱스가 지정된 dept_emp 테이블에 대해 다음 쿼리가 어떻게 실행되는지 한번 살펴보자.
SELECT * FROM dept_emp WHERE emp_no >= 10144;인덱스가 (dept_no, emp_no) 컬럼 순서대로 생성돼 있다면 인덱스의 선행 컬럼인 dept_no 조건 없이 emp_no 값으로만 검색하면 인덱스를 효율적으로 사용할 수 없다. 케이스 B의 인덱스는 다중 컬럼으로 구성된 인덱스이므로 dept_no 컬럼에 대해 먼저 정렬한 후, 다시 emp_no 컬럼값으로 정렬돼 있기 때문이다. 여기서는 간단히 WHERE 조건절에 대한 내용만 언급했지만 인덱스의 왼쪽 값 기준 규칙은 GROUP BY 절이나 ORDER BY 절에도 똑같이 적용된다.
클러스터링 인덱스
클러스터링이란 여러 개를 하나로 묶는다는 의미로 주로 사용되는데, 지금 설명하고자 하는 인덱스의 클러스터링도 그 의미를 크게 벗어나지 않는다. MySQL 서버에서 클러스터링은 테이블의 레코드를 비슷한 것(프라이머리 키를 기준으로)들끼리 묶어서 저장하는 형태로 구현되는데, 이는 주로 비슷한 값들을 동시에 조회하는 경우가 많다는 점에 착안한 것이다. MySQL에서 클러스터링 인덱스는 InnoDB 스토리지 엔진에서만 지원하며, 나머지 스토리지 엔진에서는 지원되지 않는다.
클러스터링 인덱스는 테이블의 프라이머리 키에 대해서만 적용되는 내용이다. 즉 프라이머리 키 값이 비슷한 레코드끼리 묶어서 저장하는 것을 클러스터링 인덱스라고 표현한다. 여기서 중요한 것은 프라이머리 키 값에 의해 레코드의 저장 위치가 결정된다는 것이다. 또한 프라이머리 키 값이 변경된다면 그 레코드의 물리적인 저장 위치가 바뀌어야 한다는 것을 의미하기도 한다. 프라이머리 키 값으로 클러스터링된 테이블은 프라이머리 키 값 자체에 대한 의존도가 상당히 크기 때문에 신중히 프라이머리 키를 결정해야 한다.
일반적으로 InnoDB와 같이 항상 클러스터링 인덱스로 저장되는 테이블은 프라이머리 키 기반의 검색이 매우 빠르며, 대신 레코드의 저장이나 프라이머리 키의 변경이 상대적으로 느리다.
프라이머리 키가 없는 InnoDB 테이블은 InnoDB 스토리지 엔진이 다음 우선순위대로 프라이머리 키를 대체할 컬럼을 선택한다.
- 프라이머리 키가 있으면 기본적으로 프라이머리 키를 클러스터링 키로 선택
- NOT NULL 옵션의 유니크 인덱스(UNIQUE INDEX) 중에서 첫 번째 인덱스를 클러스터링 키로 선택
- 자동으로 유니크한 값을 가지도록 증가되는 컬럼을 내부적으로 추가한 후, 클러스터링 키로 선택
InnoDB 스토리지 엔진이 적절한 클러스터링 키 후보를 찾지 못하는 경우 InnoDB 스토리지 엔진이 내부적으로 레코드의 일련번호 컬럼을 생성한다. 이렇게 자동으로 추가된 프라이머리 키(일련번호 컬럼)는 사용자에게 노출되지 않으며, 쿼리 문장에 명시적으로 사용할 수 없다. 즉, 프라이머리 키나 유니크 인덱스가 전혀 없는 InnoDB 테이블에서는 아무 의미 없는 숫자 값으로 클러스터링되는 것이며, 이것은 아무런 혜택을 주지 않는다. InnoDB 테이블에서 클러스터링 인덱스는 테이블당 단 하나만 가질 수 있는 엄청난 혜택이므로 가능하다면 프라이머리 키를 명시적으로 생성하자.
'책책책 책을 읽읍시다' 카테고리의 다른 글
카프카 핵심 가이드 3 : 컨슈머 (1) 2023.06.11