디스크 읽기 방식

HDD와 SDD

디스크 헤더를 움직이지 않고 한 번에 많은 데이터를 읽는 순차 I/O에서는 SSD가 하드 디스크 드라이브보다 조금 빠르거나 거의 비슷한 성능을 보이기도 한다.

하지만 SSD의 장점은 기존 하드 디스크 드라이브보다 랜덤 I/O가 훨씬 빠르다는 것이다.

데이터베이스 서버에서 순차 I/O 작업은 그다지 비중이 크지 않고 랜덤 I/O를 통해 작은 데이터를 읽고 쓰는 작업이 대부분이므로 SSD의 장점은 DBMS용 스토리지에 최적이다.

랜덤 I/O와 순차 I/O

순차 I/O는 3개의 페이지를 디스크에 기록하기 위해 1번의 시스템 콜을 요청했지만 랜덤 I/O는 3개의 페이지를 디스크에 기록하기 위해 3번의 시스템 콜을 요청했다.

디스크 성능은 디스크 헤더의 위치 이동 없이 얼마나 많은 데이터를 한 번에 기록하느냐에 의해 결정된다.
그래서 여러 번 쓰기 또는 읽기를 요청하는 랜덤 I/O 작업이 작업 부하가 훨씬 크다.

데이터베이스 대부분의 작업은 이러한 작은 데이터를 빈번히 읽고 쓰기 때문에 MySQL 서버에는 그룹 커밋이나 바이너리 로그 버퍼 또는 InnoDB 로그 버퍼 등의 기능이 내장돼 있다.

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

인덱스 레인지 스캔은 데이터를 읽기 위해 주로 랜덤 I/O를 사용하며 풀 테이블 스캔은 순차 I/O를 사용한다. 그래서 큰 테이블의 레코드 대부분을 읽는 작업에서는 인덱스를 사용하지 않고 풀 테이블 스캔을 사용하도록 유도할 때도 있다. 이는 순차 I/O가 랜덤 I/O보다 훨씬 빨리 많은 레코드를 읽어올 수 있기 때문이다.

랜덤 I/O와 순차 I/O 모두 파일에 쓰기를 실행하면 반드시 동기화가 필요하다. 순차 I/O의 경우에도 이런 파일 동기화 작업이 빈번히 발생한다면 랜덤 I/O와 같이 비효율적인 형태로 처리될 때가 많다. 그래서 기업용 데이터베이스 서버에는 캐시 메모리가 장착된 RAID 컨트롤러를 사용해 빈번한 파일 동기화 작업이 호출되는 순차 I/O를 효율적으로 처리될 수 있게 변환하는 역할을 한다.

인덱스란?

특징

• 칼럼 값과 해당 레코드가 저장된 주소를 key-value 형태로 인덱스로 만들어 둔다.
• 저장되는 컬럼 값을 이용해 항상 정렬된 상태를 유지한다.

장점

• 항상 정렬돼 있어 SELECT 문장을 빠르게 처리할 수 있다.

단점

• 항상 값을 정렬해야 INSERT, UPDATE, DELETE 성능이 느리다.

역할별 인덱스

• Primary Key
  레코드를 대표하는 칼럼의 값으로 만들어진 인덱스를 의미한고 이를 식별자라고도 부른다. 프라이머리키는 NULL 값을 허용하지 않으며 중복을 허용하지 않는다.

• Secondary Index
  프라이머리 키를 제외한 나머지 모든 인덱스는 Secondary Index로 분류한다.

• Unique Index
  Primary key와 성격이 비슷하고 대체해서 사용할 수 있다고 해서 대체 키라고도 하는데 별도로 분류하기도 하고 Secondary Index로 분류하기도 한다.

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

• B-Tree
  가장 일반적으로 사용되는 인덱스 알고리즘으로서 칼럼의 값을 변형하지 않고 원래의 값을 이용해 인덱싱하는 알고리즘이다.

• Hash
  메모리 기반의 데이터베이스에서 많이 사용하는 인덱스로 칼럼의 값으로 해시값을 계산해서 인덱싱하는 알고리즘으로 매우 빠른 검색을 지원한다. 하지만 값을 변형해서 인덱싱하므로 값의 일부만 검색하거나 범위를 검색할 때는 해시 인덱스를 사용할 수 없다.

데이터 중복 허용 여부별 인덱스

• Unique Index
  Unique Index에 대해 동등 조건(Equal, =)으로 검색한다는 것은 항상 1건의 레코드만 찾으면 더 찾지 않아도 된다는 것을 옵티마이저에게 알려주는 효과를 낸다. 그뿐만 아니라 유니크 인덱스로 인한 MySQL의 처리 방식의 변화나 차이점이 상당히 많다.

• Non-Unique Index
  같은 값이 1개 이상 존재하는 인덱스이다.

B-Tree 인덱스

Balanced Tree의 약자로 B-Tree는 칼럼의 원래 값을 변형시키지 않고 인덱스 구조체 내에서는 항상 정렬된 상태로 유지한다.

구조 및 특성

• 트리 구조의 최상위에 하나의 루트 노드(Root node)가 존재하고 그 하위에 자식 노드가 붙어 있는 형태다.
• 트리 구조의 가장 하위의 노드를 리프 노드(Leaf node)라 하고, 루트 노드도 리프 노드도 아닌 중간의 노드를 브랜치 노드(Branch node)라고 한다.
• 데이터베이스에서 인덱스와 실제 데이터가 저장된 데이터는 따로 관리되는데 인덱스 리프 노드도 실제 데이터 레코드를 찾아가기 위한 주솟값을 가지고 있다.
• 인덱스의 키 값은 모두 정렬돼 있지만 데이터 파일의 레코드는 정렬돼 있지 않고 임의의 순서로 저장돼 있다. 레코드가 삭제되어 빈 공간이 생기면 그 다음의 INSERT는 가능한 한 삭제된 공간을 재활용하도록 DBMS가 설계되기 때문에 항상 INSERT된 순서로 저장되는 것은 아니다.
• MyISAM 테이블은 세컨더리 인덱스가 물리적인 주소를 갖고, InnoDB 테이블은 프라이머리 키를 주소처럼 사용하기 때문에 논리적인 주소를 가진다.
• InnoDB 스토리지 엔진에서는 모든 세컨더리 인덱스 검색에서 데이터 레코드를 읽기 위해서는 반드시 프라이머리 키를 저장하고 있는 B-Tree를 다시 한번 검색해야 한다.
  

InnoDB 테이블에서 레코드는 클러스터되어 디스크에 저장되므로 기본적으로 프라이머리 키 순서로 정렬되어 저장된다. InnoDB에서는 Default로 클러스터링 테이블이 생성된다. 클러스터링이란 비슷한 값을 최대한 모아서 저장하는 방식을 의미한다.

B-Tree 인덱스 키

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

인덱스 키 추가

새로운 키 값이 B-Tree에 저장될 때, 저장될 키 값을 이용해 B-Tree 상의 적절한 위치를 검색해야 합니다. 저장될 위치가 결정되면 레코드의 키 값과 대상 레코드의 주소 정보를 B-Tree의 리프 노드에 저장합니다. 리프 노드가 가득 차 더는 저장할 수 없을 때는 리프 노드를 분리해야 하며, 이 처리는 상위 브랜치 노드까지 영향을 미칩니다. 이로 인해 B-Tree는 쓰기 작업에 상대적으로 높은 비용이 든다고 알려져 있습니다.

INSERT나 UPDATE 문장이 인덱스에 어떤 영향을 받는지 확인하려면, 테이블의 칼럼 수, 칼럼의 크기, 인덱스 칼럼의 특성 등을 고려해야 합니다. 이 과정에서 발생하는 비용의 중요한 요소는 인덱스 페이지를 읽고 쓰는 데 필요한 I/O 작업 시간입니다.

MyISAM이나 MEMORY 스토리지 엔진은 INSERT 문장이 실행될 때 새로운 키 값을 B-Tree 인덱스에 즉시 반영합니다. 반면에 InnoDB 스토리지 엔진은 체인지 버퍼를 사용하여 필요한 경우 인덱스 키 추가 작업을 지연시켜 나중에 처리할 수 있습니다. 그러나 프라이머리 키나 유니크 인덱스의 경우 중복 검사가 필요하므로, 이들 인덱스는 즉시 B-Tree에 추가하거나 삭제해야 합니다.

인덱스 키 삭제

B-Tree에서 키 값을 삭제할 때는 해당 키 값이 저장된 리프 노드를 찾아 삭제 마크를 표시하면 작업이 완료됩니다. 삭제 마크가 표시된 인덱스 키 공간은 그대로 두거나 재활용할 수 있습니다. 인덱스 키를 삭제하는 과정에서 마킹 작업이 필요하며, 이 작업 역시 디스크 I/O가 필요합니다.

MyISAM이나 MEMORY 스토리지 엔진에서는 인덱스 키 삭제가 완료되면 쿼리 실행이 종료됩니다. 그러나 InnoDB 스토리지 엔진에서는 이 작업이 버퍼링되어 지연 처리될 수 있습니다.

인덱스 키 변경

인덱스의 키 값은 키 값에 따라 저장될 리프 노드의 위치가 결정되기 때문에, B-Tree의 키 값 변경은 단순히 인덱스 상의 키 값만 변경하는 것이 불가능합니다. 대신, 키 값 변경 작업은 먼저 해당 키 값을 삭제한 다음 새로운 키 값을 추가하는 방식으로 처리됩니다.

키 값을 변경하는 과정은 기존 인덱스 키 값 삭제 후 새로운 인덱스 키 값 추가로 이루어지며, InnoDB 스토리지 엔진을 사용하는 테이블의 경우 이 작업들이 체인지 버퍼를 활용하여 지연 처리될 수도 있습니다.

인덱스 키 검색

인덱스를 구축하고 관리하는 추가 비용을 감수하는 이유는 빠른 검색을 위함입니다. 인덱스 검색 작업은 B-Tree의 루트 노드부터 시작하여 브랜치 노드를 거쳐 최종 리프 노드까지 이동하면서 비교 작업을 수행하는 "트리 탐색" 과정을 거칩니다. 인덱스 트리 탐색은 SELECT뿐만 아니라 UPDATE나 DELETE 처리를 위해 해당 레코드를 검색할 때도 사용됩니다.

B-Tree 인덱스 검색은 값이 완전히 일치하거나 값의 앞부분만 일치하는 경우에 사용할 수 있습니다. 부등호 비교 조건에서도 인덱스를 활용할 수 있지만, 인덱스를 구성하는 키 값의 뒷부분만 검색하는 용도로는 사용할 수 없습니다. 인덱스 키 값이 변형된 후 비교되는 경우, B-Tree의 빠른 검색 기능을 사용할 수 없습니다. 변형된 값은 B-Tree 인덱스에 존재하지 않기 때문입니다. 따라서 함수나 연산을 수행한 결과로 정렬하거나 검색하는 작업은 B-Tree의 장점을 이용할 수 없으므로 주의해야 합니다.

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

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

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

인덱스 키 값의 크기

InnoDB 스토리지 엔진에서 데이터를 저장하는 가장 기본 단위는 페이지 또는 블록이며, 이는 디스크의 모든 읽기 및 쓰기 작업의 최소 작업 단위입니다. 페이지는 또한 InnoDB 스토리지 엔진이 버퍼 풀에서 데이터를 버퍼링하는 기본 단위입니다. 인덱스도 페이지 단위로 관리되며, 루트와 브랜치, 리프 노드를 구분하는 기준이 페이지 단위입니다.

DBMS의 B-Tree는 자식 노드의 개수가 가변적인 구조입니다. 자식 노드의 개수는 인덱스의 페이지 크기와 키 값의 크기에 따라 결정됩니다. InnoDB 스토리지 엔진의 페이지 크기는 innodb_page_size 시스템 변수를 이용해 4KB ~ 64KB 사이의 값을 선택할 수 있지만, 기본값은 16KB입니다.

인덱스 키 값이 커지면 한 페이지에 인덱스를 다 저장하지 못해 여러 번 디스크에서 읽어야 합니다. 이로 인해 인덱스를 구성하는 키 값의 크기가 커지면 디스크에서 읽어야 하는 횟수가 늘어나며, 그만큼 검색 속도가 느려집니다.

게다가 인덱스 키 값의 길이가 길어짐에 따라 전체 인덱스의 크기도 커집니다. 하지만 InnoDB의 버퍼 풀이나 MyISAM의 키 캐시 영역은 크기가 제한되어 있기 때문에 인덱스 크기가 커질수록 메모리에 캐시할 수 있는 레코드 수가 줄어듭니다. 이는 결과적으로 메모리의 효율성이 떨어지게 됩니다.

B-Tree의 깊이

B-Tree 인덱스의 깊이는 매우 중요하지만, 직접 제어할 방법이 없습니다. 그러나 B-Tree의 깊이는 MySQL에서 값을 검색할 때 디스크를 얼마나 많이 랜덤하게 읽어야 하는지와 관련된 문제입니다. 결국 인덱스 키 값의 크기가 커질수록 한 인덱스 페이지가 담을 수 있는 인덱스 키 값의 개수가 줄어들고, 같은 레코드 건수라도 B-Tree의 깊이가 깊어져서 디스크 읽기가 더 많이 필요하게 됩니다. 이러한 이유로 인덱스 키 값의 크기는 가능한 작게 유지하는 것이 좋습니다.

선택도 (기수성)

인덱스에서 선택도 또는 기수성은 거의 같은 의미로 사용되며, 이는 인덱스 키 값 중 유니크한 값의 수를 의미합니다. 예를 들어, 전체 인덱스 키 값이 100개인데 유니크한 값의 수가 10개라면 기수성은 10입니다. 인덱스 키 값 중 중복된 값이 많아질수록 기수성은 낮아지고 동시에 선택도도 떨어집니다. 인덱스의 경우 선택도가 높을수록 검색 대상이 줄어들어 처리 속도가 빨라집니다.

읽어야 하는 레코드의 건수

인덱스를 통해 테이블의 레코드를 읽는 것은 인덱스를 거치지 않고 바로 테이블의 레코드를 읽는 것보다 더 많은 비용이 드는 작업입니다. 예를 들어, 테이블에 100만 건의 레코드가 있고 그 중 50만 건을 읽어야 하는 쿼리가 있다면, 전체 테이블을 읽어 필요하지 않은 50만 건을 버리는 것이 효율적인지, 아니면 인덱스를 통해 필요한 50만 건만 읽어 오는 것이 효율적인지 판단해야 합니다.

인덱스를 사용하는 손익 분기점을 결정할 필요가 있습니다. 일반적으로 DBMS의 옵티마이저는 인덱스를 통해 레코드 1건을 읽는 것이 테이블에서 직접 레코드 1건을 읽는 것보다 4~5배 정도 더 많은 비용이 든다고 추정합니다. 따라서 인덱스를 통해 읽어야 할 레코드 건수가 전체 테이블 레코드의 20~25%를 넘으면, 인덱스를 사용하지 않고 테이블을 모두 읽어 필요한 레코드만 필터링하는 방식이 더 효율적입니다.

전체 100만 건의 레코드 중 50만 건을 읽어야 하는 작업은 손익 분기점을 훨씬 초과하므로, MySQL 옵티마이저는 인덱스를 사용하지 않고 테이블을 처음부터 끝까지 읽어 처리할 것입니다. 이런 경우에는 인덱스를 강제로 사용하도록 힌트를 추가해도 성능상의 이점이 없습니다. 물론, MySQL 옵티마이저는 기본적으로 힌트를 무시하고 테이블을 읽는 방식으로 처리할 것입니다.

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

인덱스 레인지 스캔

인덱스 레인지 스캔은 결정된 인덱스 범위 내에서 검색을 수행하는 방식입니다. 검색 대상 값의 수나 결과 레코드 건수와 관계없이 레인지 스캔이라고 합니다. 루트 노드에서 시작하여 브랜치 노드를 거쳐 리프 노드까지 이동해야 필요한 레코드 시작 지점을 찾을 수 있습니다. 시작 지점을 찾으면, 리프 노드의 레코드만 순서대로 읽습니다. 이렇게 차례로 읽는 것을 스캔이라고 합니다. 리프 노드의 끝까지 읽으면, 다음 리프 노드로 이동해 계속 스캔하고, 스캔을 멈출 위치에 도달하면 읽은 레코드를 반환하고 쿼리를 종료합니다.

B-Tree 인덱스의 리프 노드를 스캔하며 데이터 파일의 레코드를 읽어야 하는 경우가 많습니다. 인덱스 스캔은 루트와 브랜치 노드를 사용하여 시작 위치를 찾고, 해당 인덱스를 구성하는 칼럼의 정순 또는 역순으로 정렬된 상태로 레코드를 가져옵니다. 이는 인덱스 자체의 정렬 특성 때문에 자동으로 발생합니다.

검색 조건에 일치하는 항목들을 인덱스 리프 노드에서 찾으면, 데이터 파일에서 레코드를 읽어오는 과정이 필요합니다. 이 과정에서 리프 노드에 저장된 레코드 주소를 사용하여 데이터 파일의 레코드를 읽어올 때 랜덤 I/O가 발생하며, 이 작업은 비용이 많이 듭니다. 따라서 읽어야 할 데이터 레코드가 전체의 20~25%를 초과하면, 인덱스를 통한 읽기보다 테이블의 데이터를 직접 읽는 것이 더 효율적입니다.

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

인덱스 풀 스캔

인덱스 풀 스캔은 인덱스를 사용하면서 인덱스의 처음부터 끝까지 모두 읽는 방식입니다. 이 방식은 주로 쿼리의 조건절에 사용된 컬럼이 인덱스의 첫 번째 컬럼이 아닌 경우에 사용됩니다. 예를 들어, 인덱스가 (A, B, C) 칼럼 순서로 생성되었으나 쿼리 조건절이 B 또는 C 컬럼으로 검색하는 경우입니다.

일반적으로 인덱스 크기가 테이블 크기보다 작기 때문에, 인덱스만 읽는 것이 적은 디스크 I/O로 쿼리를 처리하기 때문에 직접 테이블을 처음부터 끝까지 읽는 것보다 효율적입니다. 쿼리가 인덱스에 포함된 컬럼만으로 조건을 처리할 수 있을 때 이 방식이 주로 사용됩니다. 하지만, 인덱스와 데이터 레코드를 모두 읽어야 하는 경우에는 이 방식이 사용되지 않습니다.

루스 인덱스 스캔

루스 인덱스 스캔이란 말 그대로 느슨하게 또는 듬성듬성하게 인덱스를 읽는 것을 의미한다. 루스 인덱스 스캔은 인덱스 스캔과 비슷하게 작동하지만 중간에 필요치 않은 인덱스 키 값은 무시하고 다음으로 넘어가는 형태로 처리한다. 일반적으로 GROUP BY 또는 집합 함수 가운데 MAX() 또는 MIN() 함수에 대해 최적화를 하는 경우에 사용된다.

mysql> 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 그룹 별로 첫 번째 레코드의 emp_no 값만 읽으면 된다. 즉 인덱스에서 WHERE 조건을 만족하는 범위 전체를 다 스캔할 필요가 없다는 것을 옵티마이저는 알고 있기 때문에 조건에 만족하지 않는 레코드는 무시하고 다음 레코드로 이동한다.

인덱스 스킵 스캔

데이터베이스 서버에서 인덱스의 핵심은 값이 정렬돼 있다는 것이며 이로 인해 인덱스를 구성하는 컬럼의 순서가 매우 중요하다.

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

이 인덱스를 사용하려면 WHERE 조건절에 gender 컬럼에 대한 비교 조건이 필수다.

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

위 두 쿼리 중에서 gender 컬럼과 birth_date 컬럼의 조건을 모두 가진 두 번째 쿼리는 인덱스를 효율적으로 사용할 수 있지만 gender 컬럼에 대한 비교가 없는 첫 번쨰 쿼리는 인덱스를 사용할 수 가 없다. 주로 이런 경우에는 birth_date 컬럼부터 시작하는 인덱스를 새로 생성해야만 했다.

MySQL 8.0 버전부터는 옵티마이저가 gender 컬럼을 건너뛰어서 birth_date 컬럼만으로도 인덱스 검색이 가능하게 해주는 인덱스 스킵 스캔 최적화 기능이 도입됐다. 루스 인덱스 스캔은 GROUP BY 작업을 처리학 위해 인덱스를 사용하는 경우에만 적용할 수 있었지만 인덱스 스킵 스캔은 WHERE 조건절의 검색을 위해 사용 가능하도록 용도가 훨씬 넓어졌다.

옵티마이저는 내부적으로 아래 2개의 쿼리를 실행하는 것과 비슷한 형태의 최적화를 실행하게 된다.

mysql> SELECT * FROM employees WHERE birth_date>='1965-02-01';

-- 
mysql> SELECT * FROM employees WHERE gender='M' birth_date>='1965-02-01';
mysql> SELECT * FROM employees WHERE gender='F' birth_date>='1965-02-01';

위와 같이 최적화가 진행되기 때문에 다음과 같은 단점이 존재한다.

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

다중 칼럼 인덱스

실제 서비스용 데이터베이스에서는 다중 컬럼 인덱스(복합 컬럼 인덱스)가 더 많이 사용됩니다. 이는 2개 이상의 컬럼을 포함하는 인덱스로, "Concatenated Index"라고도 합니다.

다중 컬럼 인덱스에서 각 컬럼의 위치는 매우 중요하며, 뒤에 있는 컬럼은 앞에 있는 컬럼에 의존하여 정렬됩니다. 예를 들어, 4개의 컬럼을 가진 인덱스에서 세 번째 컬럼은 두 번째 컬럼에 의존하여 정렬되고, 네 번째 컬럼은 세 번째 컬럼에 의존하여 정렬됩니다. 이렇게 각 컬럼의 정렬은 앞선 컬럼이 같은 레코드에서만 의미가 있습니다. 이러한 이유로 다중 컬럼 인덱스에서는 인덱스 내에서 각 컬럼의 위치가 상당히 중요하다.

Full Text search 검색 인덱스

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

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

인덱스 알고리즘

Full Text search 검색에서는 문서 본문의 내용에서 사용자가 검색하게 될 키워드를 분석해 내고 빠른 검색용으로 사용할 수 있게 이러한 키워드로 인덱스를 구축한다. Full Text search 인덱스는 문서의 키워드를 인덱싱하는 기법에 따라 크게 단어의 어근 분석과 n-gram 분석 알고리즘으로 구분할 수 있다.

어근 분석 알고리즘

MySQL 서버의 Full Text search 인덱스는 불용어 처리, 어근 분석 과정을 거쳐서 색인 작업이 수행된다. 불용어 처리는 검색에서 별 가치가 없는 단어를 모두 필터링해서 제거하는 작업을 의미한다. 어근 분석은 검색어로 선정된 단어의 뿌리인 원형을 찾는 작업이다.

n-gram 알고리즘

어근 분석 알고리즘은 Full Text search적인 검색 엔진을 고려하는 것이 아니라면 멈용적으로 적용하기 쉽지 않다. 이런 단점을 보완하기 위한 방법으로 n-gram 알고리즘이 도입된 것이다. n-gram은 단순히 키워드를 검색해내기 위한 인덱싱 알고리즘이다.

n-gram이란 본문을 무조건 몇 글자씩 잘라서 인덱싱하는 방법이다. 형태소 분석보다는 알고리즘이 단순하고 국가별 언어에 대한 이해와 준비 작업이 필요 없는 반면 인덱스의 크기는 상당히 큰 편이다. n-gram에서 n은 인덱싱할 키워드의 최소 글자 수를 의미하는데 일반적으로는 2글자 단위로 키워드를 쪼개서 인덱싱하는 2-gram 방식이 많이 사용된다.

To be or not to be. That is the question

각 단어는 다음과 같이 띄어씌기(공백)와 마침표(.)를 기준으로 10개의 단어로 구분되고 2글자씩 중첩해서 토큰으로 분리된다. 이렇게 구분된 토큰은 불용어를 걸러내는 작업을 수행하고 중복된 토큰을 하나의 인덱스 엔트롤 병합되어 저장된다.

불용어 변경 및 삭제

n-gram의 토큰 파싱 및 불용어 처리 예시에서 결과를 보면 "ti"와 "at", "ha" 같은 토큰들은 "a"와 "i" 철자가 불용어로 등록돼 있기 때문에 모두 걸려져서 버려졌다. 실제로 이 같은 불용어 처리는 사용자에게 도움이 되기보다는 사용자를 더 혼란스럽게 하는 기능일 수도 있다. 그래서 불용어 처리 자체를 완전히 무시하거나 MySQL 서버에 내장된 불용어 대신 사용자가 직접 불용어를 등록하는 방법을 권장한다.

Full Text search 인덱스의 가용성

Full Text search 인덱스를 사용하려면 반드시 다음 두 가지 조건을 갖춰야 한다.

• 쿼리 문장이 Full Text search를 위한 문법 (MATCH ... AGAINST ... ) 을 사용
• 테이블이 Full Text search 대상 컬럼에 대해서 전문 인덱스 보유

다음과 같은 테이블의 doc_body 컬럼에 Full Text search 인덱스를 생성했다고 해보자.

mysql> CREATE TABLE tb_test (
		doc_id INT,
        doc_body TEXT,
        PRIMARY KEY (doc_id),
        FULLTEXT KEY fx_docbody (doc_body) WITH PARSER ngram
      ) ENGINE=InnoDB;

다음과 같은 검색 쿼리로도 원하는 검색 결과를 얻을 수 있지만 Full Text search 인덱스를 사용하지 못하고 풀 테이블 스캔으로 쿼리를 처리한다.

mysql> SELECT * FROM tb_test WEHRE doc_body LIKE %애플%;

Full Text search 인덱스를 사용하려면 반드시 MATCH (...) AGAINST (...) 구문으로 쿼리를 작성해야 하며 Full Text search 인덱스를 구성하는 컬럼들은 MATCH 절의 괄호 안에 모두 명시돼야 한다.

mysql> SELECT * FROM tb_test WEHRE MATCH(doc_body) AGAINST('애플' IN BOOLEAN MODE);

함수 기반 인덱스

때로는 컬럼의 값을 변형해서 만들어진 값에 대해 인덱스를 구축해야 할 때도 있는데 이러한 경우 함수 기반의 인덱스를 활용하면 된다. MySQL 8.0 버전부터 함수 기반 인덱스를 지원하기 시작했는데 MySQL 서버에서 함수 기반 인덱스를 구현하는 방법은 다음과 같이 두 가지로 구분할 수 있다.

• 가상 컬럼을 이용한 인덱스
• 함수를 이용한 인덱스
  MySQL 서버의 함수 기반 인덱스는 인덱싱할 값을 계산하는 과정의 차이만 있을 뿐 실제 인덱스의 내부적인 구조 및 유지관리 방법은 B-Tree 인덱스와 동일하다.

가상 컬럼을 이용한 인덱스

다음과 같이 사용자 정보를 저장하는 테이블이 있다고 가정해보자

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

그런데 first_name과 last_name을 합쳐서 검색해야 하는 요건이 생겼다면 가상 컬럼을 추가하고 그 가상 컬럼에 대한 인덱스를 생성할 수 있다.

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

이제부터는 full_name 컬럼에 대한 검색도 새로 만들어진 ix_fullname 인덱스를 이용해 실행 계획이 만들어지는 것을 확인할 수 있다. 가상 컬럼이 VIRTUAL이나 STORED 옵션 중 어떤 옵션으로 생성됐든 관계없이 해당 컬럼에 인덱스를 생성할 수 있다. 가상 컬럼은 테이블에 새로운 컬럼을 추가하는 것과 같은 효과를 내기 때문에 실제 테이블의 구조가 변경된다는 단점이 있다.

함수를 이용한 인덱스

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))
      );

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

멀티 밸류 인덱스

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

클러스터링 인덱스

MySQL 서버에서 클러스터링은 테이블 레코드를 비슷한 것들끼리 묶어서 저장하는 형태로 구현되는데 이는 주로 비슷한 값들을 동시에 조회하는 경우가 많다는 점에 착안한 것이다. MySQL에서 클러스터링 인덱스는 InnoDB 스토리지 엔진에서만 지원한다.

클러스터링 인덱스

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

클러스터링 인덱스는 프라이머리 키 값에 의해 레코드의 저장 위치가 결정되므로 사실 인덱스 알고리즘이라기보다 테이블 레코드의 저장 방식이라고 볼 수 있다. 그래서 "클러스터링 인덱스"와 "클러스터링 테이블"은 동의어로 사용되기도 한다. 또한 클러스터링의 기준이 되는 프라이머리 키는 클러스터링 키라고도 표현한다. 일반적으로 InnoDB와 같이 클러스터링 인덱스로 저장되는 테이블은 프라이머리 키 기반의 검색이 매우 빠르며 대신 레코드 저장이나 프라이머리 키의 변경이 상대적으로 느리다.

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

프라이머리 키가 세컨더리 인덱스에 어떤 영향을 미치는지 한 번 살펴보자.
MyISAM이나 MEMORY 테이블 같은 클러스터링되지 않은 테이블은 INSERT될 때 처음 저장된 공간에서 절대 이동하지 않는다. 데이터 레코드가 저장된 주소는 내부적인 레코드 아이디 역할을 해 그 주소를 이용해 실제 데이터 레코드를 찾아온다. 그래서 MyISAM 테이블이나 MEMORY 테이블에서는 프라이머리 키와 세컨더리 인덱스는 구조적으로 아무런 차이가 없다.

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

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

mysql> CREATE TABLE employees (
		emp_no INT NOT NULL,
        first_name VARCHAR(10) NOT NULL,
        PRIMARY KEY (emp_no),
        INDEX ix_firstname (first_name),
      );
      
mysql> SELECT * FROM employees WHERE first_name='Aamer';

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

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

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

장점

• 프라이머리 키(클러스터링 키)로 검색할 때 처리 성능이 매우 빠름(특히, 프라이머리 키를 범위 검색하는 경우 매우 빠름)
• 테이블의 모든 세컨더리 인덱스가 프라이머리 키를 가지고 있기 때문에 인덱스만으로 처리될 수 있는 경우가 많음(이를 커버링 인덱스라고 한다.)

단점

• 테이블의 모든 세컨더리 인덱스가 클러스터링 키를 갖기 때문에 클러스터링 키 값의 크기가 클 경우 전체적으로 인덱스의 크기가 커짐
• 세컨더리 인덱스를 통해 검색할 때 프라이머리 키로 다시 한번 검색히야 하므로 느림
• INSERT할 때 프라이머리 키에 의해 레코드의 저장 위차가 결정되므로 느림
• 프라이머리 키를 변경할 때 레코드를 DELETE하고 INSERT하는 작업이 필요하기 때문에 느림

클러스터링 인덱스의 장점은 빠른 읽기이며 단점은 느린 쓰기이다. 일반적으로 웹 서비스와 같은 온라인 트랜잭션 환경 (OLTP, On-Line Transaction Processing)에서는 쓰기와 읽기의 비율이 2:8 정도이기 때문에 읽기를 빠르게 유지하는 것이 매우 중요하다.

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

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

클러스터링 테이블의 경우 모든 세컨더리 인덱스가 프라이머리 키(클러스터링 키) 값을 포함한다. 그래서 프라이머리 키의 크기가 커지면 세컨더리 인덱스도 자동으로 크기가 커진다. 하지만 일반적으로 테이블에 세컨더리 인덱스가 4~5개 정도 생성된다는 것을 고려하면 세컨더리 인덱스의 크기는 급격히 증가한다. 또한 인덱스가 커질수록 같은 성능을 내기 위해 그만큼 메모리가 더 필요해지므로 InnoDB 테이블의 프라이머리 키는 신중하게 선택해야 한다.

프라이머리 키는 AUTO-INCREMENT보다는 업무적인 컬럼을 생성

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

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

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

AUTO_INCREMENT컬럼을 인조 식별자로 사용할 경우

여러 개의 컬럼이 복합으로 프라이머리 키가 만들어지는 경우 프라이머리 키의 크기가 길어질 때가 가끔 있다. 하지만 프라이머리 키의 크기가 길어도 세컨더리 인덱스가 필요하지 않다면 그래도 프라이머리 키를 사용하는 것이 좋다. 세컨더리 인덱스도 필요하고 프라이머리 키의 크기도 길다면 AUTO_INCREMENT 컬럼을 추가하고 이를 프라이머리 키로 설정하면 된다. 이렇게 프라이머리 키를 대체하기 위해 인위적으로 추가된 프라이머리 키를 인조 식별자라고 한다. 그리고 로그 테이블과 같이 조회보다는 INSERT 위주의 테이블들은 AUTO_INCREMENT를 이용한 인조 식별자를 프라이머리 키로 설정하는 것이 성능 향상에 도움이 된다.

유니크 인덱스

유니크는 사실 인덱스라기보다는 제약 조건에 가깝다고 볼 수 있다. 2개 이상 저장될 수 없음을 의미하는데 MySQL에서는 인덱스 없이 유니크 제약만 설정할 방법이 없다. 유니크 인덱스에서도 NULL도 저장될 수 있는데 NULL은 특정 값이 아니므로 2개 이상 저장될 수 있다. MySQL에서 프라이머리 키는 기본적으로 NULL을 허용하지 않는 유니크 속성이 자동으로 부여된다. InnoDB 테이블의 프라이머리 키는 클러스터링 키의 역할도 하므로 유니크 인덱스와는 근본적으로 다르다.

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

유니크 인덱스와 유니크하지 않은 일반 세컨더리 인덱스는 사실 인덱스 구조상 아무런 차이점이 없다. 유니크 인덱스와 일반 세컨더리 인덱스의 읽기와 쓰기를 성능 관점에서 한번 살펴보자.

인덱스 읽기

유니크 인덱스가 읽기가 빠르다는 것은 사실이 아니다.
유니크하지 않은 세컨더리 인덱스에서 한 번 더 해야 하는 작업은 디스크 읽기가 아니라 CPU에서 컬럼 값을 비교하는 작업이기 때문에 이는 성능상 영향이 거의 없다고 볼 수 있다. 유니크하지 않은 세컨더리 인덱스는 중복되는 값이 허용되므로 읽어야 할 레코드가 많아서 느린 것이지 인덱스 자체의 특성 때문에 느린 것이 아니다.

인덱스 쓰기

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

유니크 인덱스 사용시 주의사항

유니크 인덱스는 꼭 필요한 경우에만 사용하자. 그리고 하나의 테이블에서 같은 컬럼에 유니크 인덱스와 일반 인덱스를 각각 중복해서 생성해 둔 경우가 가끔 있는데 MySQL의 유니크 인덱스는 일반 다른 인덱스와 같은 역할을 하므로 중복해서 인덱스를 생성할 필요는 없다. 이미 유니크 인덱스도 일반 세컨더리 인덱스와 같은 역할을 동일하게 수행할 수 있으므로 세컨더리 인덱스를 중복으로 만들어 줄 필요는 없다.

똑같은 컬럼에 대해 프라이머리 키와 유니크 인덱스를 동일하게 생성한 경우도 있는데 이 또한 불필요한 중복이므로 주의하자.

결론적으로 유일성이 꼭 보장돼야 하는 컬럼에 대해서는 유니크 인덱스를 생성하되 꼭 필요하지 않다면 유니크 인덱스보다는 유니크하지 않은 세컨더리 인덱스를 생성하는 방법도 한 번씩 고려해보자.

외래 키

MySQL에서 외래 키는 InnoDB 스토리지 엔진에서만 생성할 수 있으며 외리캐 제약이 설정되면 자동으로 연관되는 테이블의 컬럼에 인덱스까지 생성된다. 외래키가 제거되지 않은 상태에서는 자동으로 생성된 인덱스를 삭제할 수 없다.

InnoDB 외래키 관리에는 중요한 두 가지 특징이 있다.

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

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

자식 테이블의 외래 키 변경은 부모 테이블의 확인이 필요해서 부모 테이블의 레코드가 쓰기 잠금이 걸려있으면 해제될 때까지 기다린다.

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

부모 테이블의 외래 키를 삭제할 경우 외래키의 특성(ON DELETE CASCADE)이 동시에 삭제되기 때문에 자식 테이블의 레코드가 쓰기 잠금이 걸려있다면 풀릴 때까지 대기해야 한다.