인덱스

인덱스

인덱스는 데이터베이스 쿼리의 성능을 언급하면서 빼놓을 수 없는 부분이다. MySQL 8.0 버전까지 업그레이드되어 오면서 다른 상용 RDBMS에서 제공하느 많은 기능을 지원하게 됐으며, 기존의 MyISAM 스토리지 엔진에서만 제공하던 전문 검색이나 위치 기반 검색 기능도 모두 InnoDB 스토리지 엔진에서 사용할 수 있게 개선되었다. 아무리 MySQL의 옵티마이저가 발전하고 성능이 개선됐다고 해도 여전히 관리자의 역할은 매우 중요하다.

 

디스크 읽기 방식

컴퓨터의 CPU나 메모리처럼 전기적 특성을 띤 장치의 성능은 짧은 시간 동안 매우 빠른 속도로 발전했지만 디스크 같은 기계식 장치의 성능은 상당히 제한적으로 발전했다. 여전히 데이터 저장 매체는 컴퓨터에서 가장 느린 부분이기 때문에 데이터베이스나 쿼리 튜닝에 있어 어떻게 디스크 I/O를 줄이느냐가 관건이다.

 

하드 디스크 드라이브(HDD)와 솔리드 스테이트 드라이브(SSD)

출처: https://www.admin-magazine.com/layout/set/print/HPC/Articles/Linux-Writecache

데이터베이스 서버에서는 항상 디스크 장치가 병목이 된다. 기계식 하드 디스크 드라이브를 대체하기 위해 전자식 저장 매체인 SSD(Solid State Drive)가 등장하였고, SSD도 기존 HDD와 같은 인터페이스(SATA나 SAS)를 지원하므로 내장 디스크나 DAS 또는 SAN에 그대로 사용할 수 있다. SSD는 기존 HDD에서 데이터 저장용 플래터(원판)을 제거하고 대신 플래시 메모리를 장착하여 원판을 기계적으로 회전시킬 필요없이 아주 빨리 데이터를 읽고 쓸 수 있다. 따라서 컴퓨터의 메모리(D-Ram)보다는 느리지만 기계식 HDD보다는 훨씬 빠르다.

 

Random(랜덤) I/O와 Sequential(순차) I/O

랜덤 I/O라는 표현은 HDD의 플래터(원판)를 돌려서 읽어야 할 데이터가 저장된 위치로 디스크 헤더를 이동시킨 다음 데이터를 읽는 것을 의미하는데, 순차 I/O 또한 이 작업 과정은 같다. 하지만 순차 I/O는 3개의 페이지(3 * 16KB)를 디스크에 기록하기 위해 1번 시스템 콜을 요청하지만, 랜덤 I/O는 3번 시스템 콜을 요청한다. 즉, 디스크에 기록해야 할 위치를 찾기 위해 순차 I/O는 디스크의 헤더를 1번 움직였고, 랜덤 I/O는 디스크 헤드를 3번 움직인다. 디스크에 데이터를 쓰고 읽는 데 걸리는 시간은 디스크 헤더를 움직여서 읽고 쓸 위치로 옮기는 단계에서 결정되기 때문에 순차 I/O는 랜덤 I/O보다 거의 3배 정도 빠르다고 볼 수 있다.

사실 쿼리를 튜닝해서 랜덤 I/O를 순차 I/O로 바꿔서 실행할 방법은 그다지 많지 않다. 일반적으로 쿼리를 튜닝하는 것은 랜덤 I/O 자체를 줄여주는 것이 목적이라고 할 수 있다. 여기서 랜덤 I/O를 줄인다는 것은 쿼리를 처리하는 데 꼭 필요한 데이터만 읽도록 쿼리를 개선하는 것을 의미한다.

 

인덱스 레인지 스캔은 데이터를 읽기 위해 주로 랜덤 I/O를 사용하며, 풀 테이블 스캔은 순차 I/O를 사용한다. 그래서 큰 테이블의 레코드 대부분을 읽는 작업에서는 인덱스를 사용하지 않고 풀 테이블 스캔을 사용하도록 유도할 때도 있다. 이는 순차 I/O가 랜덤 I/O보다 훨씬 빨리 많은 레코드를 읽어올 수 있기 때문인데, 이런 형태를 OLTP(On-Line Transaction Processing) 성격의 웹 서비스보다는 데이터 웨어하우스나 통계 작업에서 자주 사용된다.

 

인덱스란?

인덱스는 데이터베이스 테이블 내의 레코드를 검색, 정렬 및 필터링하는데 사용되는 데이터 구조다. 데이터베이스 테이블의 모든 데이터를 검색해서 원하는 결과를 가져오려면 시간이 오래 걸리기 때문에 컬럼(또는 컬럼들)의 값과 해당 레코드가 저장된 주소를 키와 값의 쌍(key-value)으로 삼아 주어진 순서로 미리 정렬해서 보관하는 인덱스를 만들면 데이터 접근하는데 성능을 향상시킬 수 있다. 

 

DBMS에서 인덱스는 항상 값을 정렬해야 하므로 데이터의 저장(INSERT, UPDATE, DELETE) 성능을 희생하고 그 대신 데이터의 읽기 속도를 높이는 기능이다. 따라서 테이블의 인덱스를 하나 더 추가할지 말지는 데이터의 저장 속도를 어디까지 희생할 수 있는지, 읽기 속도를 얼마나 더 빠르게 만들어야 하느냐에 따라 결정해야 한다. SELECT 쿼리 문장의 WHERE 조건절에 사용되는 컬럼이라고 해서 전부 인덱스로 생성하면 데이터 저장 성능이 떨어지고 인덱스의 크기가 비대해져 오히려 역효과만 불러올 수 있다.

 

역할별로 구분한 인덱스

인덱스를 역할별로 구분해 본다면 프라이머리 키(Primary key)와 보조 키(세컨더리 인덱스, Secondary key)로 구분할 수 있다.

• 프라이머리 키는 그 레코드를 대표하는 컬럼의 값으로 만들어진 인덱스를 의미한다. 이 컬럼은 테이블에서 해당 레코드를 식별할 수 있는 기준값이 되기 때문에 이를 식별자라 부르고 NULL 값을 허용하지 않으며 중복을 허용하지 않는 것이 특징이다.
• 프라이머리 키를 제외한 나머지 모든 인덱스는 세컨더리 인덱스로 분류한다.

 

데이터 저장방식(알고리즘)별로 구분한 인덱스

데이터 저장방식(알고리즘)별로 구분할 경우 상당히 많은 분류가 가능하지만 대표적으로 B-Tree 인덱스와 Hash 인덱스로 구분할 수 있다.

• B-Tree 알고리즘은 가장 일반적으로 사용되는 인덱스 알고리즘으로서, 컬럼의 값을 변형하지 않고 원래의 값을 이용해 인덱싱하는 알고리즘이다. MySQL 서버에서는 위치 기반 검색을 지원하기 위한 R-Tree 인덱스 알고리즘도 있지만, 결국 R-Tree 인덱스는 B-Tree의 응용 알고리즘으로 볼 수 있다.
• Hash 인덱스 알고리즘은 컬럼의 값을 해시값을 계산해서 인덱싱하는 알고리즘으로, 매우 빠른 검색을 지원한다. 하지만 값을 변형해서 인덱싱하므로 전방(Prefix) 일치와 같이 값의 일부만 검색하거나 범위를 검색할 때는 해시 인덱스를 사용할 수 없다. Hash 인덱스는 주로 메모리 기반의 데이터베이스에서 많이 사용한다.

 

데이터 중복 허용 여부로 분류하면 유니크 인덱스(Unique)와 유니크하지 않은 인덱스(Non-Unique)로 구분할 수 있으며, 이는 실제 DBMS의 쿼리를 실행해야 하는 옵티마이저에게는 상당히 중요한 문제가 된다. 유니크 인덱스에 대해 동등 조건(Equal, =)으로 검색한다는 것은 항상 1건의 레코드만 찾으면 더 찾지 않아도 된다는 것을 옵티마이저에게 알려주는 효과를 낸다. 그뿐만 아니라 유니크 인덱스로 인한 MySQL의 처리 방식의 변화나 차이점이 상당히 많다.

 

B-Tree 인덱스

B-Tree(Balanced Tree)는 (값의 앞부분만 잘라서 관리하기는 하지만) 컬럼의 원래 값을 변형시키지 않고 인덱스 구조체 내에서 항상 정렬된 상태로 유지한다. 전문 검색과 같은 특수한 요건이 아닌 경우, 대부분 인덱스는 거의 B-Tree를 사용할 정도로 일반적인 용도에 적합한 알고리즘이다.

 

구조 및 특성

B-Tree의 인덱스 구조와 테이블 데이터 레코드(InnoDB)

 

B-Tree는 트리 구조의 최상위에 하나의 루트 노드(Root node)가 존재하고 가장 하위에 있는 노드를 리프 노드(Leaf node)라 하고, 트리 구조에서 루트 노드가 아니고 리프 노드도 아닌 중간의 노드를 브랜치 노드(Branch node)라고 한다. 데이터베이스에서 인덱스와 실제 데이터가 저장된 데이터는 따로 관리되는데, 일반적으로 인덱스의 리프 노드는 항상 실제 데이터 레코드를 찾아가기 위한 주솟값을 가지고 있다.

하지만 InnoDB 테이블에서 인덱스를 통해 레코드를 읽을 때는 위 그림처럼 바로 데이터 파일을 바로 찾아가지 못한다. 인덱스에 저장돼 있는 PK 값을 이용해 PK 키 인덱스를 한 번 더 검색한 후, 프라이머리 키 인덱스의 리프 페이지에 저장되어 있는 레코드를 읽는다. 즉, InnoDB 스토리지 엔진에서는 모든 세컨더리 인덱스 검색에서 데이터 레코드를 읽기 위해서는 반드시 프라이머리 키를 저장하고 있는 B-Tree를 다시 한번 검색해야 한다. InnoDB 스토리지 엔진을 사용하는 테이블은 성능이 떨어질 것처럼 보이지만 레코드를 찾는 속도가 훨씬 빠르다는 장점이 있다. 이에 대해서는 뒤에 클러스터링 인덱스에서 자세히 살펴보자.

 

B-Tree 인덱스 키 추가 및 삭제

테이블의 레코드를 저장하거나 변경하는 경우 인덱스 키 추가나 삭제 작업이 발생한다. 인덱스 키 추가나 삭제가 어떻게 처리되는지 알아두면 쿼리의 성능을 예측할 수 있을 것이다.

 

인덱스 키 추가

새로운 키 값이 B-Tree에 저장될 때 테이블의 스토리지 엔진에 따라 새로운 키 값이 즉시 인덱스에 저장될 수도 있고 그렇지 않을 수도 있다. B-Tree에 저장될 때는 저장될 키 값을 이용해 B-Tree상의 적절한 위치를 검색해야 한다. 저장될 위치가 결정되면 레코드의 키 값과 대상 레코드의 주소 정보를 B-Tree의 리프 노드에 저장한다. 리프 노드가 꽉 차서 더는 저장할 수 없을 때는 리프 노드가 분리되어야 하는데, 이는 상위 브랜치 노드까지 처리의 범위가 넒어진다. 또한, 인덱스 추가로 INSERT나 UPDATE 명령에 대한 비용의 대부분이 메모리와 CPU에서 처리하는 시간이 아니라 디스크로부터 인덱스 페이지를 읽고 쓰기를 해야 해서 B-Tree는 상대적으로 쓰기 작업(새로운 키를 추가하는 작업)에 비용이 많이 드는 것으로 알려졌다.

 

MyISAM이나 MEMORY 스토리지 엔진을 사용하는 테이블에서는 INSERT 문장이 실행되면 즉시 새로운 키 값을 B-Tree 인덱스에 변경하고, InnoDB 스토리지 엔진은 이 작업을 조금 더 지능적으로 처리하는데, 필요하다면 인덱스 키 추가 작업을 지연시켜 나중에 처리할 수 있다. 하지만 프라이머리 키나 유니크 인덱스의 경우 중복 체크가 필요하기 때문에 즉시 B-Tree에 추가하거나 삭제한다.

 

인덱스 키 삭제

B-Tree의 키 값이 삭제되는 경우는 상당히 간단하다. 해당 키 값이 저장된 B-Tree의 리프 노드를 찾아서 그냥 삭제 마크만 하면 작업이 완료된다. 이렇게 삭제 마킹된 인덱스 키 공간은 계속 그대로 방치하거나 재활용할 수 있다. 인덱스 키 삭제로 인한 마킹 작업 또한 디스크 I/O가 필요한 작업이지만 InnoDB 스토리지 엔진에서는 이 작업 또한 버퍼링되어 지연 처리될 수 있다. 처리가 지연된 인덱스 키 삭제 또한 사용자에게는 특별한 악영향 없이 MySQL 서버가 내부적으로 처리하므로 특별히 걱정할 것은 없다.

 

인덱스 키 변경

인덱스의 키 값은 그 값에 따라 저장될 리프 노드의 위치가 결정되므로 B-Tree의 키 값이 변경되는 경우에는 단순히 인덱스상의 키 값만 변경하는 것은 불가능하다. B-Tree의 키 값 변경 작업은 먼저 키 값을 삭제한 후, 다시 새로운 키 값을 추가하는 형태로 처리된다. B-Tree 인덱스 키 값의 삭제와 추가 작업은 위 설명한 절차대로 처리되므로 InnoDB 스토리지 엔진을 사용하는 테이블에 대해서는 이 작업 모두 체인지 버퍼를 활용해 지연 처리될 수 있다.

 

인덱스 키 검색

INSERT, UPDATE, DELETE 작업을 할 때는 인덱스 관리에 따르는 추가 비용을 감당하면서 인덱스를 구축하는 이유는 빠른 검색을 위해서이다. 인덱스 트리 탐색은 SELECT에서만 사용하는 것이 아니라 UPDATE나 DELETE를 처리하기 위해 항상 해당 레코드를 먼저 검색해야 할 경우에도 사용된다. B-Tree 인덱스를 이용한 검색은 100% 일치 또는 값의 앞 부분(Left-most part)만 일치하는 경우에 사용할 수 있다. 인덱스를 구성하는 키 값의 뒷부분만 검색하는 용도로는 인덱스를 사용할 수 없고, 함수나 연산을 수행한 결과로 정렬한다거나 검색하는 작업은 B-Tree의 장점을 이용할 수 없으므로 주의해야 한다.

 

InnoDB 테이블에서 지원하는 레코드 잠금이나 넥스트 키락(갭락)이 검색을 수행한 인덱스를 잠근 후 테이블의 레코드를 잠그는 방식으로 구현되어 있다. 따라서 UPDATE나 DELETE 문장이 실행될 때 테이블에 적절히 사용할 수 있는 인덱스가 없으면 불필요하게 많은 레코드를 잠그고 심한 경우 모든 레코드를 잠글 수도 있다. InnoDB 스토리지 엔진에서는 그만큼 인덱스의 설계가 중요하고 많은 부분에 영향을 미친다.

 

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

B-Tree 인덱스는 인덱스를 구성하는 컬럼의 크기나 레코드의 건수, 그리고 유니크한 인덱스 키 값의 개수 등에 의해 검색이나 변경 작업의 성능이 영향을 받는다.

 

인덱스 키 값의 크기

InnoDB 스토리지 엔진은 디스크에 데이터를 저장하는 가장 기본 단위를 페이지(Page) 또는 블록(Block)이라고 하면, 디스크의 모든 읽기 및 쓰기 작업의 최소 작업 단위가 된다. 또한 페이지는 InnoDB 스토리지 엔진의 버퍼 풀에서 데이터를 버퍼링하는 기본 단위이기도 하다.

 

인덱스 페이지의 구성

일반적으로 DBMS의 B-Tree는 자식 노드의 개수가 가변적인 구조로 인덱스의 페이지 크기와 키 값의 크기에 따라 결정된다. InnoDB 스토리지 엔진의 페이지 크기를 innodb_page_size 시스템 변수를 이용해 4KB ~ 64KB 사이의 값을 선택할 수 있지만 기본값은 16KB다.  인덱스가 16바이트라 가정하면 위 그림처럼 인덱스 페이지가 구성된다. 자식 노드 주소라는 것은 대량 6바이트에서 12바이트까지 다양한 크기의 값을 가질 수 있으며 평균적으로 12바이트로 구성된다고 가정하면 16*1024/(16+12) = 585개의 자식 노드를 가질 수 있다. SELECT 쿼리가 300개를 읽어야 한다면 최소한 2번 이상 디스크를 읽어야 한다. 결국 인덱스를 구성하는 키 값의 크기가 커지면 디스크로부터 읽어야 하는 횟수가 늘어나고, 그만큼 느려진다는 것을 의미한다.

 

B-Tree 깊이

B-Tree 인덱스의 깊이(Depth)는 상당히 중요하지만 직접 제어할 방법은 없다. B-Tree의 깊이는 MySQL에서 값을 검색할 때 몇 번이나 랜덤하게 디스크를 읽어야 하는지와 직결되는 문제로 결론적으로 인덱스 키 값의 크기가 커지면 커질수록 하나의 인덱스 페이지가 담을 수 있는 인덱스 키 값의 개수가 적어진다고 위에서 언급했다.

예를 들어 인덱스 키 값이 16바이트인 경우에는 최대 2억(585 * 585 * 585)개 정도의 키 값을 담을 수 있지만, 키 값이 32바이트로 늘어난다면 5천만(372* 372* 372)개로 줄어들기 때문에 같은 레코드 건수라 하더라도 B-Tree의 깊이가 깊어져 디스크 읽기가 더 많이 필요하게 된다. 따라서 인덱스 키 값의 크기는 가능하면 작게 만드는 것이 좋다.

 

선택성(기수성)

인덱스에서 선택도(Selectivity) 또는 기수성(Cardinality)은 거의 같은 의미로 사요오디며, 모든 인덱스 키 값 가운데 유니크한 값의 수를 의미한다. 전체 인덱스 키 값은 100개인데, 그 중에서 유니크한 값의 수는 10개라면 기수성은 10이다. 인덱스 키 값 가운데 중복된 값이 많아지면 많아질수록 기수성은 낮아지고 동시에 선택도 또한 떨어진다. 인덱스는 선택도가 높을수록 검색 대상이 줄어들기 때문에 그만큼 빠

르게 처리된다.

 

SELECT * FROM tb_test WHERE country = 'KOREA' AND city = 'SEOUL';

인덱스에서 유니크한 값의 개수는 인덱스나 쿼리의 효율성에 큰 영향을 미친다. tb_test 테이블의 전체 레코드 건수는 1만 건이며, country 컬럼으로만 인덱스가 생성된 상태에서 아래의 두 케이스를 살펴보자.

• county 컬럼의 유니크 값이 10개일 때
  country 컬럼의 유니크 값이 10개이므로 tb_city 테이블에는 10개 국가(country)의 도시(city) 정보가 저장되어 있는 것이다. MySQL 서버는 인덱스된 컬럼(country)에 대해서는 전체 레코드의 건수나 유니크한 값의 개수등에 대한 통계 정보를 가지고 있다. 여기서 전체 레코드 건수를 유니크한 값의 개수로 나눠보면 하나의 키 값으로 검색했을 때 대략 몇 건의 레코드가 일치할지 예측할 수 있게 된다. 즉, 이 케이스의 tb_city 테이블에서는 country='KOREA'라는 조건으로 인덱스를 검색하면 1000건(10,000/10)를 조회하는 것을 알 수 있다. 그런데 인덱스를 통해 검색된 1000건 가운데 city='SEOUL'인 레코드는 1건이므로 999건은 불필요하게 읽은 것으로 볼 수 있다.
• county 컬럼의 유니크 값이 1,000개일 때
  country 컬럼의 유니크 값이 1,000개이므로 country='KOREA'라는 조건으로 인덱스를 검색하면 10건(10,000/1,000)이 일치할 것이며, 그 10건 중에서 city='SEOUL'인 레코드는 1건이므로 9건만 불필요하게 읽은 것이다.

 

위 두 케이스의 테이블에서 똑같은 쿼리를 실행해 똑같은 결과를 받았지만, 사실 두 쿼리가 처리되기 위해 MySQL 서버가 수행한 작업 내용은 매우 크다는 것을 알 수 있다. 이처럼 인덱스에서 유니크한 값의 개수는 인덱스나 쿼리의 효율성에 큰 영향을 미친다.

 

읽어야 하는 레코드의 건수

인덱스를 통해 테이블의 레코드를 읽는 것은 인덱스를 거치지 않고 바로 테이블의 레코드를 읽는 것보다 높은 비용이 드는 작업이다. 테이블에 레코드가 100만건 저장되어 있을 때 그 중 50만건을 읽어야 하는 쿼리가 있다고 가정하면 이 작업을 전체 테이블을 모두 읽어서 필요없는 50만 건을 버리는 것이 효율적일지, 인덱스를 통해 필요한 50만 건만 읽어 오는 것이 효율적일지 판단해야 한다.

일반적인 DBMS의 옵티마이저에서는 인덱스를 통해 레코드 1건을 읽는 것이 테이블에서 직접 레코드 1건을 읽는 것보다 4~5배 정도 비용이 더 많이 드는 작업인 것으로 예측한다. 즉, 인덱스를 통해 읽어야 할 레코드의 건수(물론 옵티마이저가 판단한 예상 건수)가 전체 테이블 레코드의 20~25%를 넘어서면 인덱스를 이용하지 않고 테이블을 모두 직접 읽어서 필요한 레코드만 가려내는(필터링) 방식으로 처리하는 것이 효율적이다.

 

B-Tree 인덱스를 통한 데이터 읽기

어떤 경우에 인덱스를 사용하게 유도할지, 또는 사용하지 못하게 할지 판단하려면 MySQL 스토리지 엔진이 어떻게 인덱스를 이용(경유)해서 실제 레코드를 읽어내는지 알아야 한다.

 

인덱스 레인지 스캔

인덱스 레인지 스캔은 인덱스의 접근 방법 가운데 가장 대표적인 접근 방식으로, 뒤에서 설명할 나머지 두 가지 접근 방식보다는 빠른 방법이다.

 

SELECT * FROM employees WHERE first_name BETWEEN 'Ebbe' AND 'Gad';

인덱스 레인지 스캔은 검색해야 할 인덱스의 범위가 결정됐을 때 사용하는 방식이다. 검색하려는 값의 수나 검색 결과 레코드 건수와 관계없이 레인지 스캔이라고 표현한다.

1. 인덱스에서 조건을 만족하는 값이 저장된 위치를 찾는다. 이 과정을 인덱스 탐색(Index seek)이라고 한다.
2. 1번에서 탐색된 위치부터 필요한 만큼 인덱스를 차례대로 쭉 읽는다. 이 과정을 인덱스 스캔(Index scan)이라고 한다.(1번과 2번 합쳐서 인덱스 스캔으로 통칭하기도 한다.)
3. 2번에서 읽어들인 인덱스 키와 레코드 주소를 이용해 레코드가 저장된 페이지를 가져오고, 최종 레코드를 읽어온다.

 

인덱스 풀 스캔

인덱스 레인지 스캔과는 달리 인덱스의 처음부터 끝까지 모두 읽는 방식을 인덱스 풀 스캔이라고 한다. 대표적으로 쿼리의 조건절에 사용된 컬럼이 인덱스의 첫 번째 컬럼이 아닌 경우 인덱스 풀 스캔 방식이 사용된다. 쿼리가 인덱스에 명시된 컬럼만으로 조건을 처리할 수 있는 경우 주로 이 방식으로 사용되며, 일반적으로 인덱스의 크기는 테이블의 크기보다 작으므로 직접 테이블을 처음부터 끝까지 읽는 것보다는 인덱스만 읽는 것이 적은 디스크 I/O로 쿼리를 처리하기 때문에 효율적이다. 즉, 이 방식은 인덱스 레인지 스캔보다는 빠르지 않지만 테이블의 레코드를 읽을 필요가 없기 때문에 테이블 풀 스캔보다는 효율적이다. 

 

루스 인덱스 스캔

루스 인덱스 스캔이란 말 그대로 느슨하게 또는 듬성듬성하게 인덱스를 읽는 것을 의미한다. 위 인덱스 레인지 스캔과 인덱스 풀 스캔은 상반된 의미에서 타이트 인덱스 스캔으로 분류한다. 루스 인덱스 스캔은 인덱스 레인지 스캔과 비슷하게 작동하지만 중간에 필요치 않은 인덱스 키 값은 무시(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_no, emp_no) 조합으로 정렬되어 있다고 가정하면 dept_no 그룹별로 첫 번째 레코드의 emp_no 값만 읽으면 된다. 즉, 인덱스에서 WHERE 조건을 만족하는 범위 전체를 다스캔할 필요가 없다는 것을 옵티마이저는 알고 있기 때문에 조건에 만족하지 않는 레코드는 무시하고 다음 레코드로 이동한다.

 

인덱스 스킵 스캔

mysql > ALTER TABEL employees ADD INDEX ix_gender_birthdate (gender, birth_date);

-- // 인덱스를 사용하지 못하는 쿼리
mysql> SELECT * FROM employees WHERE birth_date >= '1965-02-01';

-- // 인덱스를 사용할 수 있는 쿼리
mysql> SELECT * FROM employees WHERE gender = 'M' AND birth_date >= '1965-02-01';

인덱스를 사용하지 못하는 쿼리는 MySQL 8.0 버전 이전에는 위에서 봤던 풀 인덱스 스캔을 사용하기 때문에 인덱스를 비효율적으로 사용한다. 하지만 MySQL 8.0 버전에 도입된 인덱스 스킵 스캔은 WHERE 조건절의 검색을 위해 사용 가능하도록 용도가 훨씬 넓어진 것이다.  인덱스 스킵 스캔을 사용하면 MySQL 옵티마이저는 아래처럼 우선 gender 컬럼에서 유니크한 값을 모두 조회해서 주어진 쿼리에 gender 컬럼의 조건을 추가해서 쿼리를 다시 실행하는 형태로 처리한다.

• SELECT gender, birth_date WHERE gender = 'M' AND birth_date >= '1965-02-01';
• SELECT gender, birth_date WHERE gender = 'F' AND birth_date >= '1965-02-01';

 

인덱스 스킵 스캔은 MySQL 8.0 버전에 새로 도입된 기능이어서 아직 다음과 같은 단점이 있다.

• WHERE 조건절에 조건이 없는 인덱스의 선행 컬럼의 유니크한 값의 개수가 적어야 함
• 쿼리가 인덱스에 존재하는 컬럼만으로 처리 가능해야 함(커버링 인덱스)

 

첫 번째 조건은 쿼리 실행 계획의 비용과 관련된 부분인데, 만약 유니크한 값의 개수가 매우 많다면 MySQL 옵티마이저는 인덱스에서 스캔해야 할 시작 지점을 검색하는 작업이 많이 필요해진다. 그래서 쿼리의 처리 성능이 오히려 더 느려질 수 있다. 

 

다중 컬럼(Multi-column) 인덱스

지금까지 살펴본 인덱스들은 모두 1개의 컬럼만 포함된 인덱스다. 하지만 실제 서비스용 데이터베이스에서는 2개 이상의 컬럼을 포함하는 인덱스가 더 많이 사용된다. 두 개 이상의 컬럼으로 구성된 인덱스를 다중 컬럼 인덱스(또는 복합 컬럼 인덱스)라고 하며, 또한 2개 이상의 컬럼이 연결됐다고 해서 Concatenated Index라고도 한다. 

 

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

인덱스를 생성할 때 설정한 정렬 규칙에 따라서 인덱스의 키 값은 항상 오름차순이거나 내림차순으로 정렬되어 저장된다. 인덱스를 어느 방향으로 읽을지는 쿼리에 따라 옵티마이저가 실시간으로 만들어내는 실행 계획에 따라 결정된다.

 

인덱스의 정렬

일반적인 상용 DBMS에서는 인덱스를 생성하는 시점에 인덱스를 구성하는 각 컬럼의 정렬을 오름차순 또는 내림차순으로 설정할 수 있다. 그리고 MySQL 8.0 버전부터는 다음과 같은 형태의 정렬순서를 혼합한 인덱스도 생성할 수 있게 되었다.

• CREATE INDEX ix_teamname_userscore ON employees (team_name ASC, user_score DESC);

 

인덱스 스캔 방향

SELECT * FROM employees ORDER BY first_name DESC LIMIT 1;

first_name 컬럼에 대한 인덱스가 포함된 employees 테이블에 대해 위 쿼리를 실행하는 과정을 한 번 살펴보자. MySQL은 이 쿼리를 실행하기 위해 인덱스를 처음부터 오름차순으로 끝까지 읽어 first_name이 가장 큰 값 하나를 가져오는 것일까? 그렇지 않다. 인덱스는 항상 오름차순으로만 정렬되어 있지만 인덱스를 최솟값부터 읽으면 오름차순으로 값을 가져올 수 있고, 최댓값부터 거꾸로 읽으면 내림차순으로 값을 가져올 수 있다는 것을 MySQL 옵티마이저는 이미 알고 있다. 그래서 위 쿼리는 인덱스를 사용하는 시점에 읽는 방향이 결정되어 인덱스를 역순으로 접근해 첫 번째 레코드만 읽으면 된다.

 

내림차순 인덱스

CREATE INDEX ix_teamname_userscore ON employees (team_name ASC, user_score DESC);

위처럼 2개 이상의 컬럼으로 구성된 복합 인덱스에서 각각의 컬럼이 내림차순과 오름차순이 혼합된 경우에는 MySQL 8.0의 내림차순 인덱스로만 해결할 수 있다. first_name 컬럼을 역순으로 정렬하는 요건만 있다면 다음 2개 인덱스 중에서 어떤 것을 선택하는 것이 좋을까? 아니면 두 인덱스 모두 동일한 성능을 보일까?

• CREATE INDEX ix_firstname_asc ON employees (first_name ASC);
• CREATE INDEX ix_firstname_asc ON employees (first_name DESC);

 

이에 대해 알게 위해서는 아래 인덱스 정순과 역순 스캔에 대한 내용을 알 필요가 있다.

• 오름차순 인덱스(Ascending Index): 작은 값의 인덱스 키가 B-Tree의 왼쪽으로 정렬된 인덱스
• 내림차순 인덱스(Descending Index): 큰 값의 인덱스 키가 B-Tree의 왼쪽으로 정렬된 인덱스
• 인덱스 정순 스캔(Forward Index Scan): 인덱스 키의 크고 작음에 관계없이 인덱스 리프 노드의 왼쪽 페이지부터 오른쪽으로 스캔
• 인덱스 역순 스캔(Backward Index Scan): 인덱스 키의 크고 작음에 관계없이 인덱스 리프 노드의 오른쪽 페이지부터 왼쪽으로 스캔

내림차순 인덱스의 필요성에 대해 간단한 테스트 결과를 살펴보면서 알아보자.

 

SELECT COUNT(*) FROM t1; -- 31547392

SELECT * FROM t1 ORDER BY tid ASC LIMIT 31547391, 1; -- 6.345s
SELECT * FROM t1 ORDER BY tid DESC LIMIT 31547391, 1; -- 7.423s

위 두 쿼리는 테이블의 프라이머리 키를 정순 또는 역순으로 스캔하면서 마지막 레코드 1건만 반환한다. 첫 번째 쿼리는 tid 컬럼의 값이 가장 큰 레코드 1건을, 그리고 두 번쨰 쿼리는 tid 컬럼의 값이 가장 작은 레코드 1건을 반환한다. 하지만 LIMIT ... OFFSET ... 부분의 쿼리로 인해 실제 MySQL 서버는 테이블의 모든 레코드를 스캔해야 한다.

31,547,392건을 스캔하는데 두 쿼리는 1.1초 정도가 차이지만 비율로 따져보면 16.9% 더 시간이 걸리는 것을 확인할 수 있다. InnoDB 내부적으로 인덱스 역순 스캔이 인덱스 정순 스캔에 비해 느릴 수 밖에 없는 다음의 두 가지 이유가 있다.

• 페이지 잠금이 인덱스 정순 스캔(Forward index scan)에 적합한 구조
• 페이지 내에서 인덱스 레코드가 단방향으로만 연결된 구조

 

SELECT * FROM tab WHERE userid=? ORDER BY score DESC LIMIT 10;

오름차순 인덱스: INDEX (userid ASC, score ASC)
내림차순 인덱스: INDEX (userid DESC, score DESC)

이제 서비스 요건에 맞게 어떤 정렬 순서의 인덱스를 선택해야 할지 살펴보면 ORDER BY ... DESC하는 쿼리가 소량의 레코드에 드물게 실행되는 경우라면 내림차순 인덱스를 굳이 고려할 필요는 없어 보이지만 많은 레코드를 조회하면서 빈번하게 실행된다면 오름차순 인덱스보다는 내림차순 인덱스가 더 효율적이라고 볼 수 있다.

 

B-Tree 인덱스의 가용성과 효율성

쿼리의 WHERE 조건이나 GROUP BY, 또는 ORDER BY 절이 어떤 경우에 인덱스를 사용할 수 있고 어떤 방식으로 사용할 수 있는지 식별할 수 있어야 한다. 그래야만 쿼리의 조건을 최적화하거나, 역으로 쿼리에 맞게 인덱스를 최적으로 생성할 수 있다.

 

가용성과 효율성 판단

기본적으로 B-Tree 인덱스의 특성상 다음 조건에서는 사용할 수 없다. 여기서 사용할 수 없다는 것은 작업 범위 결정 조건(작업의 범위를 결정하는 조건)으로 사용할 수 없다는 것을 의미하며, 경우에 따라서는 체크 조건으로 인덱스를 사용할 수는 있다.

• NOT-EQUAL로 비교된 경우("<>", "NOT IN", "NOT BETWEEN", "IS NOT NULL")
• LIKE "%??"(앞부분이 아닌 뒷부분 일치) 형태로 문자열 패턴이 비교된 경우
• 스토어드 함수나 다른 연산자로 인덱스 컬럼이 변형된 후 비교된 경우
  • WHERE SUBSTRING(column, 1, 1) = 'X'
  • WHERE DAYOFMONTH(column) = 1
• NOT-DETERMINISTIC 속성의 스토어드 함수가 비교 조건에 사용된 경우
  
  •  WHERE column = deterministic_function()
• 데이터 타입이 서로 다른 비교(인덱스 컬럼의 타입을 변환해야 비교가 가능한 경우)
  • WHERE char_column = 10
• 문자열 데이터 타입의 콜레이션이 다른 경우
  • WHERE utf8_bin_char_column = euckr_bin_char_column

 

다른 일반적인 DBMS에서는 NULL 값이 인덱스에 저장되지 않지만 MySQL에서는 NULL 값도 인덱스에 저장된다. 따라서  ... WHERE column IS NULL ... 조건도 작업 범위 결정 조건으로 인덱스를 사용한다.

 

INDEX ix_test (column_1, column_2, column_3, .., column_n)

다중 컬럼으로 만들어진 인덱스는 어떤 조건에서 사용될 수 있고, 어떤 경우에 절대 사용할 수 없는지 위 인덱스를 통해 살펴보자.

• 작업 범위 결정 조건으로 인덱스를 사용하지 못하는 경우
    - column_1 컬럼에 대한 조건이 없는 경우
    - column_1 컬럼의 비교 조건이 위의 인덱스 사용 불가 조건 중 하나인 경우
• 작업 범위 결정 조건으로 인덱스를 사용하는 경우(i는 2보다 크고 n보다 작은 임의의 값을 의미)
    - column_1 ~ column_(i-1) 컬럼까지 동등 비교 형태("=" 또는 "IN")로 비교
    - column_i 컬럼에 대해 다음 연산자 중 하나로 비교
        - 동등 비교("=" 또는 "IN")
        - 크가 작다 형태(">" 또는 "<")
        - LIKE로 좌측 일치 패턴(LIKE '승환%')

위의 두 가지 조건을 모두 만족하는 쿼리는 column_1부터 column_i까지는 작업 범위 결정 조건으로 사용되고, column_(i+1)부터 column_n까지의 조건은 체크 조건으로 사용된다. 인덱스를 사용하는 경우와 그렇지 않은 상황에 해당하는 쿼리의 조건 몇 가지를 예제로 살펴보자.

 

-- // 다음 쿼리는 인덱스를 사용할 수 없음
mysql> .. WHERE column_1 <> 2

-- // 다음 쿼리는 column_1과 column_2까지 범위 결정 조건으로 사용됨
mysql> .. WHERE column_1 =1 AND column_2 > 10

-- // 다음 쿼리는 column_1, column_2, column_3까지 범위 결정 조건으로 사용됨
mysql> .. WHERE column_1 IN (1, 2) AND column_2 = 2 AND column_3 <= 10

-- // 다음 쿼리는 column_1, column_2, column_3까지 범위 결정 조건으로, column_4는 체크 조건으로 사용됨
mysql> .. WHERE column_1 IN (1, 2) AND column_2 = 2 AND column_3 IN (10, 20, 30) AND column_4 <> 100

-- // 다음 쿼리는 column_1, column_2, column_3, column_4까지 범위 결정 조건으로 사용됨
-- // 좌측 패턴 일치 LIKE 비교는 크다 또는 작다 비교와 동급으로 생각하면 됨
mysql> .. WHERE column_1 = 1 AND column_2 = (2, 4) AND column_3 = 30 AND column_4 LIKE '김승%'

-- // 다음 쿼리는 column_1, column_2, column_3, column_4, column_5까지 모두 범위 결정 조건으로 사용됨
mysql> .. WHERE column_1 = 1 AND column_2 = (2, 4) AND column_3 = 30
                              AND column_4 LIKE '김승환' AND column_5 = '서울'

 

 

R-Tree 인덱스

공간 인덱스는 R-Tree 인덱스 알고리즘을 이용해 2차원의 데이터를 인덱싱하고 검색하는 목적의 인덱스다. 기본적인 내부 메커니즘은 B-Tree와 흡사하다. B-Tree는 인덱스를 구성하는 컬럼의 값이 1차원의 스칼라 값인 반면, R-Tree 인덱스는 2차원의 공간 개념 값이라는 것이다.

위치 기반의 서비스를 구현하는 방법은 여러 가지가 있겠지만 MySQL의 공간 확장(Spatial Extension)을 이용하면 간단하게 이러한 기능을 구현할 수 있다. MySQL의 공간 확장에는 다음과 같이 크게 세 가지 기능이 포함되어 있다.

• 공간 데이터를 저장할 수 있는 데이터 타입
• 공간 데이터의 검색을 위한 공간 인덱스(R-Tree 알고리즘)
• 공간 데이터의 연산 함수(거리 또는 포함 관계의 처리)

 

전문 검색 인덱스

위 인덱스 알고리즘은 일반적으로 크지 않은 데이터 또는 이미 키워드화한 작은 값에 대한 인덱싱 알고리즘이었다. 대표적으로 MySQL의 B-Tree 인덱스는 실제 컬럼의 값이 1MB이더라도 1MB 전체의 값을 인덱스 키로 사용하는 것이 아니라 1,000바이트(MyISAM) 또는 3072바이트(InnoDB)까지만 잘라서 인덱스 키로 사용한다. 또한 B-Tree 인덱스의 특성에서도 알아봤듯이 전체 일치 또는 좌측 일부 일치와 같은 검색만 가능하다.

문서의 내용 전체를 인덱스화해서 특정 키워드가 포함된 문서를 검색하는 전문(Full Text) 검색에는 InnoDB나 MyISAM 스토리지 엔진에서 제공하는 일반적인 용도의 B-Tree 인덱스를 사용할 수 없다. 문서 전체에 대한 분석과 검색을 위한 이러한 인덱싱 알고리즘을 전문 검색(Full Text search) 인덱스라고 하는데, 전문 검색 인덱스는 일반화된 기능의 명칭이지 전문 검색 알고리즘의 이름을 지칭하는 것은 아니다.

 

어근 분석 알고리즘

MySQL 서버의 전문 검색 인덱스는 다음과 같은 두 가지 중요한 과정을 거쳐서 색인 작업이 수행된다.

• 불용어(Stop Word) 처리
• 어근 분석(Stemming)

불용어 처리는 검색에서 별 가치가 없는 단어를 모두 필터링해서 제거하는 작업을 의미한다. 불용어의 개수는 많지 않기 때문에 알고리즘을 구현한 코드에 모두 상수로 정의해서 사용하는 경우가 많고, 유연성을 위해 불용어 자체를 데이터베이스화해서 사용자가 추가하거나 삭제할 수 있게 구현하는 경우도 있다.

어근 분석은 검색어로 선정된 단어의 뿌리인 원형을 찾는 작업으로 MySQL 서버에서는 오픈소스 형태소 분석 라이브러리인 MeCab을 플러그인 형태로 사용할 수 있게 지원한다.

 

n-gram 알고리즘

MeCab을 위한 형태소 분석은 매우 전문적인 전문 검색 알고리즘이어서 만족할 만한 결과를 내기 우해서는 많은 노력과 시간을 필요로 한다. 그래서 이런 단점을 보완하기 위한 방법으로 n-gram 알고리즘이 도입되었고, 이는 단순히 키워드를 검색해내기 위한 인덱싱 알고리즘이라고 할 수 있다.

 

함수 기반 인덱스

일반적인 인덱스는 컬럼의 값 일부(컬럼의 값 앞부분) 또는 전체에 대해서만 인덱싱 생성이 허용된다. 하지만 때로는 컬럼의 값을 변형해서 만들어진 값에 대해 인덱스를 구축해야 할 때도 있는데, 이러한 경우 함수 기반의 인덱스를 활용하면 된다. 다음 두 가지 방법으로 구분할 수 있다.

• 가상 컬럼을 이용한 인덱스
• 함수를 이용한 인덱스

 

가상 컬럼을 이용한 인덱스

mysql> CREATE TABLE user (
                 user_id BIGINT,
                 first_name VARCHAR(10),
                 last_name VARCHAR(10),
                 PRIMARY KEY (user_id)
             );

mysql> ALTER TABLE user
                 ADD full_name VARCHAR(30) AS (CONCAT(first_name, ' ', last_name)) VIRTUAL,
                 ADD INDEX ix_fullname (full_name);

mysql> SELECT * FROM user WHERE full_name = 'Matt Lee';

first_name과 last_name을 합쳐서 검색해야 하는 요건이 생겼다면 이전 버전의 MySQL 서버에서는 full_name이라는 컬럼을 추가하고 모든 레코드에 대해 full_name을 업데이트하는 작업을 거쳐야 했다. 하지만 MySQL 8.0 버전부터는 위처럼 가상 컬럼을 추가하고 그 가상 컬럼에 인덱스를 생성할 수 있게 되었다. 가상 컬럼은 테이블에 새로운 컬럼을 추가하는 것과 같은 효과를 내기 대문에 실제 테이블의 구조가 변경된다는 단점이 있다.

 

함수를 이용한 인덱스

mysql> CREATE TABLE user (
                 user_id BIGINT,
                 first_name VARCHAR(10),
                 last_name VARCHAR(10),
                 PRIMARY KEY (user_id),
                 INDEX ix_fullname ((CONCAT(first_name, ' ', last_name)))
             );

mysql> SELECT * FROM user WHERE CONCAT(firstname, ' ', last_name) = 'Matt Lee';

함수를 이용한 인덱스는 MySQL 8.0 버전부터 테이블의 구조를 변경하지 않고, 함수를 직접 사용하는 인덱스를 생성하여 계상된 결괏값의 검색을 빠르게 만들어준다. 함수 기반 인덱스를 제대로 활용하려면 반드시 조건절에 함수 기반 인덱스에 명시된 표현식이 그대로 사용되어야 한다.

 

멀티 밸류 인덱스

멀티 밸류(Multi-Value) 인덱스는 하나의 데이터 레코드가 여러 개의 키 값을 가질 수 있는 형태의 인덱스다. 일반적인 RDBMS를 기준으로 생각하면 이러한 인덱스는 정규화에 위배되는 형태다. 하지만 최근 RDBMS들이 JSON 데이터 타입을 지원하기 시작하면서 JSON의 배열 타입의 필드에 저장된 원소(Element)들에 대한 인덱스 요건이 발생한 것이다.

mysql> CREATE TABLE user (
                 user_id BIGINT AUTO_INCREMENT PRIMARY KEY,
                 first_name VARCHAR(10),
                 last_name VARCHAR(10),
                 credit_info JSON,
                 INDEX mx_creditscores ( (CAST(credit_info->'$.credit_scores' AS UNSIGNED ARRAY)) )
             );

mysql > INSERT INTO user VALUES (1, 'Matt', 'Lee', {"credit_scores":[360, 353, 351]}');


mysql> SELECT * FROM user WHERE 360 MEMBER OF (credit_info->'$.credit_scores');

위처럼 신용 정보 점수를 배열로 JSON 타입 컬럼에 저장하는 테이블을 가정해보자. 멀티 밸류 인덱스를 활용하기 위해서는 일반적인 조건 방식으로 사용하면 안 되고, 반드시 다음 함수들을 이용해서 검색해야 옵티마이저가 인덱스를 활용한 실행 계획을 수립한다. 

• MEMBER OF()
• JSON_CONTAINS()
• JSON_OVERLAPS()

 

클러스터링 인덱스

클러스터링이란 여러 개를 하나로 묶는다는 의미로 주로 사용되는데, MySQL 서버에서 클러스터링은 주로 비슷한 값들을 동시에 조회하는 경우가 많다는 점을 착안하여 테이블의 레코드를 비슷한 것(프라이머리 키를 기준으로)들끼리 묶어서 저장하는 형태로 구현된다. MySQL에서 클러스터링 인덱스는 InnoDB 스토리지 엔진만 지원한다.

 

클러스터링 인덱스

클러스터링 인덱스는 테이블의 프라이머리 키에 대해서만 적용되는 내용이다. 즉, 프라이머리 키 값이 비슷한 레코드끼리 묶어서 저장하는 것을 클러스터링 인덱스라고 표현한다. 여기서 중요한 것은 프라이머리 키 값에 의해 레코드의 저장 위치가 결정되기 때문에 프라이머리 키 값이 변경되면 레코드의 물리적인 저장 위치도 바뀐다. 따라서 프라이머리 키 값으로 클러스터링된 테이블은 프라이머리 키 값 자체에 대한 의존도가 상당히 크기 때문에 신중히 프라이머리 키를 결정해야 한다.

 

그러면 프라이머리 키가 없는 InnoDB 테이블은 어떻게 클러스터링 테이블로 구성될까? 프라이머리 키가 없는 경우에는 InnoDB 스토리지 엔진이 다음 우선순위대로 프라이머리 키를 대체할 컬럼을 선택한다.

1. 프라이머리 키가 있으면 기본적으로 프라이머리 키를 클러스터링 키로 선택
2. NOT NULL 옵션의 유니크 인덱스(UNIQUE INDEX) 중에서 첫 번째 인덱스를 클러스터링 키로 선택
3. 자동으로 유니크한 값을 가지도록 증가되는 컬럼을 내부적으로 추가한 후, 클러스터링 키로 선택

 

InnoDB 스토리지 엔진이 적절한 클러스터링 키 후보를 찾지 못한 내부적으로 레코드의 일련번호 컬럼을 생성하지만 쿼리 문장에 명시적으로 사용할 수는 없다. 또한 아무 의미 없는 숫자 값으로 클러스터링된 것이며, 이것은 우리에게 아무런 혜택을 주지 않는다.

 

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

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

 

mysql> CREATE TABLE employees (
                 emp_no INT NOT NULL,
                 first_name VARCHAR(20) NOT NULL,
                 PRIMARY KEY (emp_no),
                 INDEX ix_firstname (first_name)
             );

mysql > SELECT * FROM employees WHERE first_name = 'Aamer';

 

employees 테이블에서 first_name 컬럼으로 검색하는 경우 프라이머리 키로 클러스터링된 InnoDB와 그렇지 않은 MyISAM에서 어떤 차이가 있는지 살펴보자.

• MyISAM: ix_fistname 인덱스를 검색해서 레코드의 주소를 확인한 후, 레코드의 주소를 이용해 최종 레코드를 가져옴
• InnoDB: ix_firstname 인덱스를 검색해 레코드의 프라이머리 키 값을 확인한 후, 프라이머리 키 인덱스를 검색해서 최종 레코드를 가져옴

InnoDB가 MyISAM보다 조금 더 복잡하게 처리되지만 InnoDB 테이블에서 프라이머리 키(클러스터링 인덱스)는 더 큰 장점을 제공하기 때문에 성능 저하에 대해 너무 걱정하지 않아도 된다.

 

클러스터링 인덱스의 장점과 단점

• 장점
  • 프라이머리 키(클러스터링 키)로 검색할 때 처리 성능이 매우 빠름(특히, 프라이머리 키를 범위 검색하는 경우 매우 빠름)
  • 테이블의 모든 세컨더리 인덱스가 프라이머리 키를 가지고 있기 때문에 인덱스만으로 처리될 수 있는 경우가 많음(이를 커버링 인덱스라고 한다.)
• 단점
  • 테이블의 모든 세컨더리 인덱스가 클러스터링 키를 갖고 있기 때문에 클러스터링 키 값의 크기가 클 경우 전체적으로 인덱스의 크기가 커짐
  • 세컨더리 인덱스를 통해 검색할 때 프라이머리 키로 다시 한번 검색해야 하므로 처리 성능이 느림
  • INSERT할 때 프라이머리 키에 의해 레코드의 저장 위치가 결정되기 때문에 처리 성능이 느림
  • 프라이머리 키를 변경할 때 레코드를 DELETE하고 INSERT하는 작업이 필요하기 때문에 처리 성능이 느림

대부분 클러스터링 인덱스의 장점은 빠른 읽기(SELECT)이며, 단점은 느린 쓰기(INSERT, UPDATE, DELETE)라는 것을 알 수 있다. 일반적으로 웹 서비스와 같은 온라인 트랜잭션 환경(OLTP, On-Line Transaction Processing)에서는 쓰기와 읽기의 비율이 2:8 또는 1:9 정도이기 때문에 조금 느린 쓰기를 감수하고 읽기를 빠르게 유지하는 것이 매우 중요하다.

 

클러스터링 테이블 사용시 주의사항

MyISAM과 같이 클러스터링되지 않은 테이블에 비해 InnoDB 테이블(클러스터링 테이블)에서는 조금 더 주의할 사항이 있다.

 

클러스터링 인덱스 키의 크기

클러스터링 테이블의 경우 모든 세컨더리 인덱스가 프라이머리 키(클러스터링 키) 값을 포함한다. 그래서 프라이머리 키의 크기가 커지면 세컨더리 인덱스도 자동으로 크기가 커진다. 일반적으로 테이블에 세컨더리 인덱스가 4~5개 정도 생성된다는 것을 고려하면 세컨더리 인덱스 크기는 급격히 증가한다. 

 

PK 크기	레코드당 증가하는 인덱스 크기		100만 건 레코드 저장 시 증가하는 인덱스 크기
10바이트	10바이트 * 5 = 50바이트		50바이트 * 1,000,000 = 47MB
50바이트	50바이트 * 5 = 250바이트		250바이트 * 1,000,000 = 238MB

5개의 세컨더리 인덱스를 가지는 테이블의 프라이머리 키가 10바이트인 경우와 50바이트인 경우를 한 번 비교하면 레코드 한 건일 때는 50바이트이지만 100만 건만 되어도 인덱스의 크기가 거의 190MB(238MB - 47MB)나 증가한다. 1,000만 건이면 1.9GB가 증가한다. 또한 인덱스가 커질수록 같은 성능을 내기 위해 그만큼의 메모리가 더 필요해지므로 InnoDB 테이블의 프라이머리 키는 신중하게 선택해야 한다.

 

프라이머리 키는 AUTO-INCREMENT보다는 업무적인 컬럼으로 생성(가능한 경우)

InnoDB의 프라이머리 키는 클러스터링 키로 사용되며, 이 값에 의해 레코드의 위치가 결정된다. 즉, 프라이머리 키로 검색하는 경우 클러스터링되지 않은 테이블에 비해 매우 빠르게 처리될 수 있기 때문에 그 컬럼의 크기가 크더라도 업무적으로 해당 레코드를 대표할 수 있다면 그 컬럼을 프라이머리 키로 설정하는 것이 좋다.

 

프라이머리 키는 반드시 명시할 것

InnoDB 테이블에서 프라이머리 키를 정의하지 않으면 InnoDB 스토리지 엔진이 내부적으로 일련번호 컬럼을 추가한다. 하지만 이렇게 자동으로 추가된 컬럼은 사용자가 전혀 접근(사용)할 수 없기 때문에 AUTO_INCREMENT 컬럼을 생성하고 프라이머리 키로 설정하는 것이 좋다. 또한 ROW 기반의 복제나 InnoDB Cluster에서는 모든 테이블이 프라이머리 키를 가져야만 정상적인 복제 성능을 보장하기도 하므로 프라이머리 키는 꼭 생성해야 한다.

 

AUTO-INCREMENT 컬럼을 인조 식별자로 사용할 경우

로그 테이블과 같이 조회보다는 INSERT 위주의 테이블들은 AUTO_INCREMENT를 이용한 인조 식별자를 프라이머리 키로 설정하는 것이 성능 향상에 도움이 된다.

 

유니크 인덱스

유니크는 인덱스라기보다는 제약 조건에 가깝다고 볼 수 있다. 하지만 MySQL에서는 인덱스 없이 유니크 제약만 설정할 방법이 없다. 유니크 인덱스에서 NULL도 저장될 수 있는데, NULL은 특정 값이 아니므로 2개 이상 저장될 수 있다.

 

인덱스 읽기

많은 사람들이 유니크 인덱스가 빠르다고 생각하지만 사실이 아니다. 유니크 인덱스는 1건만 읽으면 되지만 유니크하지 않은 세컨더리 인덱스에서는 레코드를 한 건 더 읽어야하므로 느리다고 하지만 한 번 더 해야 하는 작업은 디스크 읽기가 아니라 CPU에서 컬럼값을 비교하는 작업이기 때문에 이는 성능상 영향이 거의 없다고 볼 수 있다. 유니크하지 않은 세컨더리 인덱스는 중복된 값이 허용되므로 읽어야 할 레코드가 많아서 느린 것이지, 인덱스 자체의 특성 때문에 느린 것이 아니다. 즉, 하나의 값을 검색하는 경우, 유니크 인덱스와 일반 세컨더리 인덱스는 사용되는 실행 계획이 다르다. 하지만 이는 인덱스의 성격이 유니크한지 아닌지에 따른 차이일 뿐 큰 차이는 없다. 

 

인덱스 쓰기

새로운 레코드가 INSERT되거나 인덱스 컬럼의 값이 변경되는 경우에는 인덱스 쓰기 작업이 필요하다. 그리고 유니크 인덱스의 키 값을 쓸 때는 중복된 값이 있는지 없는지 체크하는 과정이 한 단계 더 필요하다. InnoDB 스토리지 엔진에는 인덱스 키의 저장을 버퍼링하기 위해 체인지 버퍼(Change Buffer)가 사용된다. 그래서 인덱스의 저장이나 변경 작업이 상당히 빨리 처리되지만, 유니크 인덱스는 반드시 중복 체크를 해야 하므로 작업 자체를 버퍼링하지 못한다. 이 때문에 유니크 인덱스는 일반 세컨더리 인덱스보다 변경 작업이 더 느리게 작동한다.

또한, MySQL에서는 유니크 인덱스에서 중복된 값을 체크할 때는 읽기 잠금을 사용하고, 쓰기를 할 때는 쓰기 잠금을 사용하는데 이 과정에서 데드락이 아주 빈번히 발생한다.

 

외래키

MySQL에서 외래키는 InnoDB 스토리지 엔진에서만 생성할 수 있으며, 외래키 제약이 설정되면 자동으로 연관되는 테이블의 컬럼에 인덱스까지 생성된다. InnoDB의 외래키 관리에는 중요한 두 가지 특징이 있다.

• 테이블의 변경(쓰기 잠금)이 발생하는 경우에만 잠금 경합(잠금 대기)이 발생한다.
• 외래키와 연관되지 않은 컬럼의 변경은 최대한 잠금 경합(잠금 대기)을 발생시키지 않는다.

 

mysql> CREATE TABLE tb_parent (
                 id INT NOT NULL,
                 fd VARCHAR(100) NOT NULL,
                 PRIMARY KEY (id),
             ) ENGINE=InnoDB;


mysql> CREATE TABLE tb_child (
                 id INT NOT NULL,
                 pid INT DEFAULT NULL, -- // parent.id 컬럼 참조
                 fd VARCHAR(100) NOT NULL,
                 PRIMARY KEY (id),
                 KEY ix_parentid (pid),
                 CONSTRAINT child_ibfk_1 FOREIGN KEY (pid) REFERENCES tb_parent (id) ON DELETE CASCADE
             ) ENGINE=InnoDB;

mysql> INSERT INTO tb_parent VALUES (1, 'parent-1'), (2, 'parent-2');
mysql> INSERT INTO tb_child VALUES (100, 1, 'child-100');

위와 같은 테이블에서 언제 자식 테이블의 변경이 잠금 대기를 하고, 언제 부모 테이블의 변경이 잠금 대기를 하는지 살펴보자.

 

자식 테이블의 변경이 대기하는 경우

작업 번호	커넥션-1			커넥션-2
1	BEGIN;
2	UPDATE tb_parent SET fd='changed-2' WHERE id=2;
3				BEGIN;
4				UPDATE tb_child SET pid=2 WHERE id=100;
5	ROLLBACK;
6				Query OK, 1 row affected (3.04 sec)

이 작업에서는 1번 커넥션에서 먼저 트랜잭션을 시작하고 부모(tb_parent) 테이블에서 id가 2인 레코드에 UPDATE를 실행한다. 이 과정에서 1번 커넥션이 tb_parent 테이블에서 id가 2인 레코드에 대해 쓰기 잠금을 획득한다.

그리고 2번 커넥션에서 자식 테이블(tb_child)의 외래키 컬럼(부모의 키를 참조하는 컬럼)인 pid를 2로 변경하려고 하지만 부모 테이블의 변경 작업이 완료될 때까지 대기하고 1번 커넥션에서 ROLLBACK이나 COMMIT으로 트랜잭션을 종료하면 2번 커넥션의 대기 중이던 작업이 즉시 처리되는 것을 확인할 수 있다.

이것이 InnoDB의 외래키 관리의 첫 번째 특징에 해당한다. 자식 테이블의 외래키가 아닌 컬럼의 변경은 외래키로 인한 잠금 확장이 발생하지 않는데 이는 InnoDB의 외래키의 두 번째 특징에 해당한다.

 

부모 테이블의 변경이 대기하는 경우

작업 번호	커넥션-1			커넥션-2
1	BEGIN;
2	UPDATE tb_child SET fd='changed-100' WHERE id=100;
3				BEGIN;
4				DELETE FROM tb_parent WHERE id=1;
5	ROLLBACK;
6				Query OK, 1 row affected (6.09 sec)

변경하는 테이블의 순서만 변경한 예제이다. 첫 번째 커넥션에서 부모 키 "1"을 참조하는 자식 테이블의 레코드를 변경하면 tb_child 테이블의 레코드에 대해 쓰기 잠금을 획득한다. 이 상태에서 2번 커넥션이 tb_parent 테이블에서 id가 1인 레코드를 삭제하는 경우 tb_child 테이블의 레코드에 대한 쓰기 잠금이 해제될 때까지 기다려야 한다. 이는 자식 테이블(tb_chidl)이 생성될 때 정의된 외래키의 특성(ON DELETE CASCADE) 때문에 부모 레코드가 삭제되면 자식 레코드도 동시에 삭제되는 식으로 작동하기 때문이다.

 

이렇게 데이터베이스에서 외래키를 물리적으로 생성하려면 잠금 경합까지 고려해 모델링을 진행하는 것이 좋다. 물리적인 외래키의 고려사항은 연관 테이블에 읽기 잠금을 걸어야 한다는 것이다. 이렇게 잠금이 다른 테이블로 확장되면 그만큼 전체적으로 쿼리의 동시 처리에 영향을 미칠 수 있다.

 

 

 

출처

Real MySQL 8.0 1권 : 개발자와 DBA를 위한 MySQL 실전 가이드

[MySQL] B-Tree로 인덱스(Index)에 대해 쉽고 완벽하게 이해하기