인덱스는 데이터베이스 쿼리의 성능에서 매우 중요한 부분이다. 아무리 MySQL 서버의 옵티마이저가 발전하고 성능이 개선되었다고 해도 여전히 관리자의 역할이 중요하고, 관리자에게 있어 인덱스에 대한 지식은 중요한 부분이다.

디스크 읽기 방식

데이터 저장 매체는 컴퓨터에서 가장 느린 부분이다. 데이터베이스 성능 튜닝은 디스크 I/O를 줄이는 것이 중요하다.

HDD와 SSD

SSD는 기존 HDD에서 데이터 저장용 플래터(원판)을 제거하고 그 대신 플래시 메모리를 장착하고 있다. 그래서 디스크 원판을 기계적으로 회전시킬 필요가 없으므로 아주 빨리 데이터를 읽고 쓸 수 있다.

메모리와 디스크의 처리 속도는 10만 배 이상의 차이를 보인다. 그에 비해, 플래시 메모리를 사용하는 SSD는 1000배 가량의 속도 차이를 보인다.

디스크의 헤더를 움직이지 않고 한 번에 많은 데이터를 읽는 순차 I/O에서는 SSD가 HDD보다 조금 빠르거나 비슷한 성능을 보인다. 하지만 SSD의 장점은 기존 HDD보다 랜던 I/O가 빠르다는 것이다. 데이터베이스 서버에서 순차 I/O 작업은 그다지 비중이 크지 않고 랜덤 I/O를 통해 작은 데이터를 읽고 쓰는 작업이 대부분이므로 SSD의 장점은 DBMS용 스토리지에 최적이라고 볼 수 있다.

랜덤 I/O와 순차 I/O

랜덤 I/O라는 표현은 HDD의 플레터(원판)을 돌려서 읽어야 할 때 데이터가 저장된 위치로 디스크 헤더를 이동시킨 다음 데이터를 읽는 것을 의미하는데, 이는 순차 I/O 과정과 같다.

순차 I/O는 3개의 페이지(3 x 16KB)를 디스크에 기록하기 위해 1번 시스템 콜을 요청하지만, 랜덤 I/O는 3개의 페이지를 디스크에 기록하기 위해 3번 시스템 콜을 요청한다. 디스크에 데이터를 쓰고 읽는데 걸리는 시간은 디스크 헤더를 움직여서 읽고 쓸 위치로 옮기는 단계에서 결정된다. 따라서 순차 I/O는 랜덤 I/O보다 거의 3배 빠르다고 볼 수 있다. 즉, 디스크의 성능은 디스크 헤더의 위치 이동 없이 얼마나 많은 데이터를 한 번에 기록하느냐에 의해 결정된다고 볼 수 있다. 그래서 여러 번 쓰기 또는 읽기를 요청하는 랜덤 I/O 작업이 부하가 훨씬 더 크다. 데이터베이스 대부분의 작업은 이러한 작은 데이터를 번번히 읽고 쓰기 때문에 MySQL 서버에는 그룹 커밋이나 바이너리 로그 버퍼 또는 InnoDB 로그 버퍼 등의 기능이 내장돼 있다.

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

쿼리 튜닝을 통해 랜던 I/O를 순차 I/O로 바꿔서 실행할 방법은 많이 없기에, 일반적으로 쿼리 튜닝의 목적은 랜덤 I/O 자체를 줄이는 것이 목적이다. 여기서랜덤 I/O를 줄이는 것은 쿼리를 처리하는 데 꼭 필요한 데이터만 읽도록 쿼리를 개선하는 것이다.

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

인덱스

DBMS는 데이터베이스 테이블의 모든 데이터를 검색해서 원하는 결과를 빠르게 가져오기 위해 인덱스를 이용한다.

특징

• 칼럼의 값과 해당 레코드가 저장된 주소를 키와 값의 쌍으로 인덱스를 만든다.
• 저장되는 컬럼 값을 이용해 항상 정렬된 상태를 유지한다.

장단점

• 이미 정렬돼 있어서 아주 빨리 원하는 값을 찾아올 수 있다.
• 항상 값을 정렬해야 INSERT, UPDATE, DELETE 성능이 느리다.

결론적으로 DBMS에서 인덱스는 데이터의 저장(INSERT, UPDATE, DELETE) 성능을 희생하고 그 대신 데이터의 읽기 속도를 높이는 기능이다. 따라서 테이블의 인덱스를 하나 더 추가할지 말지는 데이터의 저장 속도를 어디까지 희생할 수 있는지, 읽기 속도를 얼마나 더 빠르게 만들어야 하는냐에 따라 결정된다.

SELECT 쿼리 문장의 WHERE 조건절에 사용디는 칼럼이라고 해서 전부 인덱스로 생성하면 데이터 저장 성능이 떨어지고 인덱스의 크기가 비대해져 오히려 역효과만 불러올 수 있다.

역할별 인덱스

• 프라이머리 키: 레코드를 대표하는 칼럼의 값으로 만들어진 인덱스로, 테이블에서 해당 레코드를 식별할 수 있는 기준 값이 되기 때문에 이를 식별자로 부른다. PK는 NULL 값을 허용하지 않으며 중복을 허용하지 않는다.
• 세컨더리 인덱스: PK를 제외한 나머지 모든 인덱스를 지칭한다.

유니크 인덱스는 PK와 성격이 비슷해 대체키라고도 불리기도 하며, 별도로 분류하기도 하고 세컨더리 인덱스로 분류하기도 한다.

데이터 저장 방식별 인덱스

• B-Tree: 가장 일반적으로 사용되는 인덱스로, 칼럼의 값을 변형하지 않고 원래의 값을 이용해 인덱싱하는 알고리즘이다.
• Hash Index: 칼럼의 값으로 해시값을 인덱싱하는 알고리즘으로, 매우 빠른 검색을 지원한다. 하지만 값을 변형해서 인덱싱하므로 Prefix 일치와 같이 값의 일부 검색이나 범위 검색이 불가능하다.

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

DBMS의 쿼리를 실행해야하는 옵티마이저에게 동등 조건(Equal, =)으로 검색했을 때, 1건의 레코드만 찾아도 된다는 것을 알려주는 효과가 있다. 그뿐만 아니라, 유니크 인덱스로 인한 MySQL의 처리방식의 변화나 차이점이 상당하다.

• Unique Index: 같은 값이 1개만 존재하는지를 의미한다.
• Non-Unique Index: 같은 값이 1개 이상 존재할 수 있다.

B-Tree 인덱스

B-Tree는 칼럼의 원본 값을 변형시키지 않고(물론 값의 앞부분만 잘라서 관리하기는 하지만) 인덱스 구조체 내에서는 항상 정렬된 상태로 유지한다. 특별한 경우가 아닌 경우, 대부분 인덱스는 B-Tree를 사용한다.

구조 및 특성

B-Tree는 트리구조의 최상위에 하나의 루트 노드(Root Node)가 존재하고 그 하위에 자식 노드가 붙어 있는 형태다. 트리 구조의 최상위에 하나의 ""데이터베이스에서 인덱스와 실제 데이터가 저장된 데이터는 따로 관리되는데, 인덱스의 리프 노드는 항상 실제 데이터 레코드를 찾아가기 위한 주솟값을 가지고 있다.

위 그림과 같이 인덱스의 키 값은 모두 정렬되어 있지만, 데이터 파일의 레코드는 정렬돼 있지 않고 임의의 순서로 저장돼 있다. 데이터 파일의 레코드는 삭제와 변경이 존재하기에 항상 INSERT된 순서로 존재하지 않는다. 만약 전혀 삭제와 변경이 이루어지지 않았다면, 맞을 수도 있다. 레코드가 삭제되어 빈 공간이 생기면 그 다음의 INSERT는 가능한 한 삭제된 공간을 재활용하도록 DBMS가 설계되기 때문에 항상 INSERT된 순서로 저장되지 않는다.

인덱스는 테이블의 키 칼럼만 가지고 있으므로 나머지 칼럼을 읽으려면 데이터 파일에서 해당 레코드를 찾아야 한다. 이를 위해 인덱스의 리프 노드는 데이터 파일에 저장된 레코드의 주소를 가진다.

위 그림은 InnoDB 테이블의 인덱스의 데이터 파일의 관계를 보여주는데, InnoDB 스토리지 엔진을 사용하는 테이블에서느누 PK가 ROWID 역할을 한다. MyISAM과 InnoDB의 인덱스에서 큰 차이점은 세컨더리 인덱스를 통해 데이터 파일의 레코드를 찾아가는 방법에 있다. MyISAM 테이블은 세컨더리 인덱스가 물리적인 주소를 가지는 반면 InnoDB 테이블은 PK를 주소처럼 사용하기 때문에 논리적인 주소를 가진다고 볼 수 있다.

따라서 InnoDB 테이블에서 인덱스를 통해 레코드를 읽을 때는 데이터 파일에 바로 접근할 수 없다. 그림에서와 같이 인덱스에 저장돼 있는 PK 값을 이용해 PK 인덱스를 한 번 더 검색한 후, PK 인덱스의 리프 페이지에 저장돼 있는 레코드를 읽는다. 즉, InnoDB 스토리지 엔진에서는 모든 세컨더리 인덱스 검색에서 데이터 레코드를 읽기 위해서는 반드시 PK를 저장하고 있는 B-Tree를 검색해야 한다.

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

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

인덱스 키 추가

새로운 키 값이 B-Tree에 저장될 때 테이블의 스토리지 엔진에 따라 새로운 키 값이 즉시 인덱스에 저장될 수도 있고 그렇지 않을 수도 있다. B-Tree에 저장될 때는 저장될 키 값을 이용해 B-Tree 상의 적절한 위치를 검색해야 한다. 저장될 위치가 결정되면 레코드의 키 값과 대상 레코드의 주소 정보를 B-Tree의 리프 노드에 저장한다. 리프 노드가 꽉 차서 더는 저장할 수 없을 때는 리프 노드가 분리돼야 하는데, 이는 상위 브랜치 노드까지 처리의 범위가 넓어진다. 이러한 작업 탓에 B-Tree는 상대적으로 쓰기 작업에 비용이 많이 든다.

인덱스 추가 오버헤드 예측

인덱스 추가로 인한 영향은 테이블 칼럼의 수, 칼럼의 크기, 인덱스 칼럼의 특성 등이 INSERT나 UPDATE 문장에 영향을 준다. 대략적으로 테이블에 레코드를 추가하는 작업을 1이라고 가정하면 해당 테이블의 인덱스에 키를 추가하는 작업 비용을 1.5라 예측한다.

예를 들어 테이블에 인덱스가 3개가 있다면 이때 테이블에 인덱스가 하나도 없는 경우는 작업 비용이 1이고, 3개인 경우에는 5.5(1.5 * 3 + 1) 정도로 예측한다.

MyISAM이나 MEMORY 스토리지 엔진은 INSERT 문장이 실행될 경우 즉시 새로운 키 값을 B-Tree 인덱스에 저장한다. 하지만 InnoDB 스토리지 엔진은 이 작업을 체인지 버퍼로 지연시켜 나중에 처리한다. 하지만 PK나 유니크 인덱스의 경우 중복 확인이 필요하기 때문에 즉시 B-Tree에 추가된다.

인덱스 키 삭제

인덱스 키 값이 저장된 B-Tree의 리프 노드를 찾아 삭제 마크만 하면 작업이 완료된다. 이렇게 삭제 마킹된 인덱스 키 공간은 계속 그대로 방치하거나 재활용할 수 있다.

인덱스 키 삭제로 인한 마킹 작업 또한 디스크 쓰기가 필요하므로 이 작업 역시 I/O 작업이 필요하다. MySQL 5.5버전 이상 버전의 InnoDB 스토리지 엔진에서는 이 작업 또한 체인지 버퍼에서 버퍼링되어 지연 처리될 수 있다. 처리가 지연된 인덱스 키 삭제 또한 사용자에게는 특별한 악영향 없이 MySQL 서버가 내부적으로 처리하므로 특별히 걱정할 것이 없다.

MyISAM이나 MEMORY 스토리지 엔진은 인덱스 키 삭제가 완료된 후에 쿼리 실행이 완료된다.

인덱스 키 변경

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

인덱스 키 검색

인덱스 추가, 삭제, 변경 작업을 희생하면서 인덱스를 구축하는 이유는 바로 빠른 검색을 위해서다. 인덱스를 검색하는 작업은 B-Tree의 루트 노드부터 시작해 브랜치 노드를 거쳐 최종 리프 노드까지 이동하면서 비교 작업을 수행하는데, 이 과정을 "트리 탐색"이라고 한다. 인덱스 트리 탐색은 SELECT에서만 사용하는 것이 아닌 UPDATE나 DELETE를 처리하기 위해 항상 레코드를 먼저 검색해야하는 경우에도 사용된다.

B-Tree 인덱스 검색이 사용 가능한 경우)

• 100% 일치 검색
• 값의 앞부분만 일치하는 검색
• 부등호("<", ">") 비교 조건의 검색

B-Tree 인덱스 검색이 사용 불가능한 경우)

• 키 값의 뒷부분만 검색하는 경우
• 인덱스의 키 값에 변형이 가해진 후 비교되는 경우
• 함수나 연산을 수행한 결과로 정렬 또는 검색하는 작업

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

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

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

인덱스 키 값의 크기

페이지(블럭)

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

인덱스 키 값의 크기

일반적으로 DBMS의 B-Tree 자식 노드의 개수는 가변적인 구조다. 이러한 자식 노드의 개수는 인덱스의 페이지 크기와 키 값의 크기에 따라 결정된다. InnoDB 스토리지 엔진의 페이지 크기(innodb_page_size)를 기본값인 16KB로, 인덱스의 키를 16바이트로 설정하자. 그럼 인덱스 페이지는 아래와 같이 자식노드주소와 함께 담긴다.

하나의 인덱스 페이지에는 $(16 * 1024) / (16 + 12) = 585$개의 자식 노드를 저장할 수 있다. 인덱스를 구성하는 키 값이 커지면 저장할 수 있는 자식 노드 개수가 작아진다. 따라서 하나의 인덱스 페이지로 읽기가 불가능해 디스크로부터 읽어야 하는 횟수가 늘어나고, 그만큼 느려진다는 것을 의미한다.

인덱스 키 값의 길이가 길어지면 전체적인 인덱스의 크기가 커진다는 것을 의미한다. InnoDB의 버퍼 풀은 캐시할 수 있는 공간이 한정적이기 때문에 하나의 레코드를 위한 인덱스 크기가 커지면 커질수록 메모리에 캐시해 둘 수 있는 레코드 수는 줄어든다.

B-Tree 깊이

B-Tree 인덱스의 깊이는 상당히 중요하지만 직접 제어할 방법은 없다. B-Tree의 깊이는 MySQL에서 값을 검색할 때 몇 번이나 랜덤하게 디스크를 읽어야 하는지와 직결되는 문제다. 결론적으로 인덱스 키 값의 크기가 커지면 커질수록 하나의 인덱스 페이지가 담을 수 있는 인덱스 키 값의 개수가 적어지고, 그 때문에 같은 레코드 건수라 하더라도 B-Tree의 깊이가 깊어져서 디스크 읽기가 더 많이 필요하게 된다.

따라서 인덱스 키 값의 크기는 가능하면 작게 만드는 것이 유리하다.

아무리 대용량 데이테베이스라도 B-Tree의 깊이가 5단계 이상까지 깊어지는 경우는 흔치 않다.

선택도(기수성)

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

선택도가 조ㅓㅎ지 않다고 하더라도 정렬이나 그루핑과 같은 작업을 위해 인덱스를 만드는 것이 훨씬 나은 경우도 많다. 인덱스가 항상 검색에만 사용되는 것이 아니므로 여러 가지 용도를 고려해 적절히 인덱스를 설계할 필요가 있다.

읽어야 하는 레코드의 건수

인덱스를 통해 테이블의 레코드를 읽는 것은 인덱스를 거치지 않고 바로 테이블의 레코드를 읽는 것보다 높은 비용이 드는 작업이다.

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

전체 20~25% 이상의 레코드를 읽을 때는 강제로 인덱스를 사용하도록 힌트를 추가해도 서능상 얻을 수 있는 이점이 없다. 물론 이러한 작업이 MySQL의 옵티마이저가 기본적으로 힌트를 무시하고 테이블을 직접 읽는 방식으로 처리하겠지만 기본으로 알고 있어야 할 사항이다.

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

어떤 경우에 인덱스를 사용하게 유도할지, 또는 사용하지 못하게 할지 판단하려면 MySQL 스토리지 엔진이 어떻게 인덱스를 이용해서 실제 레코드를 읽어 내는지 알아야 한다. MySQL이 인덱스를 이용하는 대표적인 방법 세 가지를 살펴보겠다.

인덱스 레인지 스캔

인덱스 레인지 스캔은 인덱스의 접근 방법 가운데 가장 대표적인 접근 방식으로, 뒤에서 설명할 나머지 두 가지 접근 방식보다는 빠른 방법이다. 인덱스를 통해 레코드를 한 건만 읽는 경우와 한 건 이상을 읽는 경우를 각각 다른 이름으로 구분하지만, 이번 절에서는 모두 묶어서 "인덱스 레인지 스캔"이라고 표현했다. 여기서는 인덱스 B-Tree의 필요한 영역을 스캔하는 데 어떤 작업이 필요한지만 이해할 수 있으면 충분하다. 다음 쿼리를 예제로 살펴보자.

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

인덱스 레인지 스캔은 검색해야 할 인덱스의 범위가 결정됐을 때 사용하는 방식이다. 검색하려는 값의 수나 검색 결과 레코드 건수와 관계없이 레인지 스캔이라고 표현한다. 위 그림의 화살표에서도 알 수 있듯이 루트 노드에서부터 비교를 시작해 브랜치 노드를 거치고 최종적으로 리프 노드까지 찾아 들어가야만 비로소 필요한 레코드의 시작 지점을 찾을 수 있다. 일단 시작해야 할 위치를 찾으면 그때부터는 리프 노드의 레코드만 순서대로 읽으면 된다. 이처럼 차례대로 쭉 읽는 것을 스캔이라고 표현한다. 만약 스캔하다가 리프 노드의 끝까지 읽으면 리프 노드 간의 링크를 이용해 다음 리프 노드를 찾아서 다시 스캔한다. 그리고 최종적으로 스캔을 멈춰야 할 위치에 다다르면 지금까지 읽은 레코드를 사용 자에게 반환하고 쿼리를 끝낸다. 그림에서 두꺼운 선은 스캔해야 할 위치 검색을 위한 비교 작업을 의미하며, 두꺼운 화살표가 지나가는 리프 노드의 레코드 구간은 실제 스캔하는 범위를 표현한다. 그림은 실제 인덱스만을 읽는 경우를 보여준다. 하지만 B-Tree 인덱스의 리프 노드를 스캔하면서 실제 데이터 파일의 레코드를 읽어 와야 하는 경우도 많은데, 이 과정을 좀 더 자세히 살펴보자.

B-Tree 인덱스에서 루트와 브랜치 노드를 이용해 스캔 시작 위치를 검색하고, 그 지점부터 필요한 방향(오름차순 또는 내림차순)으로 인덱스를 읽어 나가는 과정을 그림 8.9에서 확인할 수 있다. 중요한 것은 어떤 방식으로 스캔하든 관계없이, 해당 인덱스를 구성하는 칼럼의 정순 또는 역순으로 정렬된 상태로 레코드를 가져온다는 것이다. 이는 별도의 정렬 과정이 수반되는 것이 아니라 인덱스 자체의 정렬 특성 때문에 자동으로 그렇게 된다. 그림에서 또 한 가지 중요한 것은 인덱스의 리프 노드에서 검색 조건에 일치하는 건들은 데이터 파일에서 레코드를 읽어오는 과정이 필요하다는 것이다. 이때 리프 노드에 저장된 레코드 주소로 데이터 파일의 레코드를 읽어오는데, 레코드 한 건 한 건 단위로 랜덤 I/O가 한 번씩 일어난다. 그림처럼 3건의 레코드가 검색 조건에 일치했다고 가정하면, 데이터 레코드를 읽기 위해 랜덤 I/O가 최대 3번 필요한 것이다. 그래서 인덱스를 통해 데이터 레코드를 읽는 작업은 비용이 많이 드는 작업으로 분류된다. 그리고 인덱스를 통해 읽어야 할 데이터 레코드가 20~25%를 넘으면 인덱스를 통한 읽기보다 테이블의 데이터를 직접 읽는 것이 더 효율적인 처리 방식이 된다. 인덱스 레인지 스캔은 다음과 같이 크게 3단계를 거친다는 점을 살펴봤다.

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

쿼리가 필요로 하는 데이터에 따라 3번 과정은 필요하지 않을 수도 있는데, 이를 커버링 인덱스라고 한다. 커버링 인덱스로 처리되는 쿼리는 디스크의 레코드를 읽지 않아도 되기 때문에 랜덤 읽기가 상당히 줄어들고 성능은 그만큼 빨라진다. MySQL 서버에서는 1번과 2번 단계의 작업이 얼마나 수행됐는지를 확인할 수 있게 다음과 같은 상태 값을 제공한다.

mysql> SHOW STATUS LIKE 'Handler%';

| Variable_name			| Value 	| 
| Handler_read_first	| 71 		| 
| Handler_read_last 	| 1 		|
| Handler_read_key 		| 567 		|
| Handler_read_next 	| 3447233 	|
| Handler_read_prev 	| 19 		|

• Handler_read_key: 1번 단계가 실행된 횟수
• Handler_read_next: 2번 단계로 읽은 레코드 건수 중 인덱스 정순으로 읽은 레코드 건수
• Handler_read_prev: 2번 단계로 읽은 레코드 건수 중 인덱스 역순으로 읽은 레코드 건수
• Handler_read_first, Handler_read_last: 인덱스의 첫 번째 레코드와 마지막 레코드를 읽은 횟수를 의미하는데, 이 둘은 MIN() 또는 MAX() 와 같이 제일 큰 값 또는 제일 작은 값만 읽는 경우 증가하는 상태 값이다.

실제 인덱스만 읽었는지 인덱스를 통해 테이블의 레코드를 읽었는지(3번 단계)는 구분하지 않는다.

인덱스 풀 스캔

인덱스 레인지 스캔과 마찬가지로 인덱스를 사용하지만 인덱스 레인지 스캔과는 달리 인덱스의 처음부터 끝까지 모두 읽는 방식을 인덱스 풀 스캔이라고 한다. 대표적으로 퀴리의 조건절에 사용된 칼럼이 인덱스의 첫 번째 칼럼이 아닌 경우 인덱스 풀 스캔 방식이 사용된다. 예를 들어, 인덱스는 (A, B, C) 칼럼의 순서로 만들어져 있지만 쿼리의 조건절은 B 칼럼이나 C 칼럼으로 검색하는 경우다.

일반적으로 인덱스의 크기는 테이블의 크기보다 작으므로 직접 테이블을 처음부터 끝까지 읽는 것보다는 인덱스만 읽는 것이 효율적이다. 쿼리가 인덱스에 명시된 칼럼만으로 조건을 처리할 수 있는 경우 주로 이 방식이 사용된다. 인덱스뿐만 아니라 데이터 레코드까지 모두 읽어야 한다면 절대 이 방식으로 처리되지 않는다.

그림 8.10에서 인덱스 풀 스캔의 예를 살펴볼 수 있다. 먼저 인덱스 리프 노드의 제일 앞 또는 제일 뒤 로 이동한 후, 인덱스의 리프 노드를 연결하는 링크드 리스트(Linked list, 리프 노드 사이를 연결하는 세로로 그려진 두 쌍의 화살표)를 따라서 처음부터 끝까지 스캔하는 방식을 인덱스 풀 스캔이라고 한다. 이 방식은 인덱스 레인지 스캔보다는 빠르지 않지만 테이블 풀 스캔보다는 효율적이다. 앞에서도 언급했듯이 인덱스에 포함된 칼럼만으로 쿼리를 처리할 수 있는 경우 테이블의 레코드를 읽을 필요가 없기 때문이다. 인덱스의 전체 크기는 테이블 자체의 크기보다는 훨씬 작으므로 인덱스 풀 스캔은 테이 블 전체를 읽는 것보다는 적은 디스크 I/O로 퀴리를 처리할 수 있다.

여기서 특별히 방식을 언급하지 않고 "인덱스를 사용한다"라고 표현한 것은 "인덱스 레인지 스캔"이나 뒤에서 설명할 "루스 인덱스 스캔" 방식으로 인덱스를 사용한다는 것을 의미한다. 인덱스 풀 스캔 방식 또한 인덱스를 이용 하는 것이지만 효율적인 방식은 아니며, 일반적으로 인덱스를 생성하는 목적은 아니다. 역으로 테이블 전체를 읽거나 인덱스 풀 스캔 방식으로 인덱스를 사용하는 경우는 "인덱스를 사용하지 못한다" 또는 "인덱스를 효율적으로 사용하지 못한다"라는 표현을 사용했다.

루스 인덱스 스캔

오라클 DBMS의 "인덱스 스킵 스캔"이 라고 하는 기능과 작동 방식은 비슷하지만 MySQL에서는 이를 "루스 인덱스 스캔"이라고 한다. MySQL 5.7 버전까지는 MySQL의 루스 인덱스 스캔 기능이 많이 제한적이 었지만, MySQL 8.0 버전부터는 다른 상용 DBMS에서 지원하는 인덱스 스킵 스캔과 같은 최적화를 조금씩 지원하기 시작했다. 앞에서 소개한 두 가지 접근 방법("인덱스 레인지 스캔"과 "인덱스 풀 스캔")은 "루스 인덱스 스캔"과는 상반된 의미에서 "타이트(Tight) 인덱스 스캔"으로 분류한다. 루스 인덱스 스캔 이란 말 그대로 느슨하게 또는 듬성듬성하게 인덱스를 읽는 것을 의미한다.

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

SELECT dept_no, MIN(emp_no) 
FROM dept_emp 
WHERE dep_no BETWEEN 'd992' AND 'd004' 
GROUP BY dept_no; 

이 쿼리에서 사용된 dept_emp 테이블은 dept_no와 emp_no라는 두 개의 칼럼으로 인덱스가 생성돼 있다. 또한 이 인데스는 (dept_no, emp_no) 조합으로 정렬까지 돼 있어서 그림 8.11에서와 같이 dept_no 그룹 별로 첫 번째 레코드의 emp_no 값만 읽으면 된다. 즉 인덱스에서 WHERE 조건을 만족하는 범위 전체를 다 스캔할 필요가 없다는 것을 옵티마이저는 알고 있기 때문에 조건에 만족하지 않는 레코드는 무시하고 다음 레코드로 이동한다. 그림 8.11을 보면 인덱스 리프 노드를 스캔하면서 불필요한 부분은 그냥 무시하고 필요한 부분(회색 바탕 색깔의 레코드)만 읽었음을 알 수 있다. 루스 인덱스 스캔을 사용하려면 여러 가지 조건을 만족해야 하는데, 이러한 제약 조건은 '실행 계획'에서 자세히 언급한다.

인덱스 스킵 스캔

데이터베이스 서버에서 인덱스의 핵심은 값이 정렬돼 있다는 것이며, 이로 인해 인덱스를 구성하는 칼럼의 순서가 매우 중요하다. 예를 들어, employees 테이블에 다음과 같은 인덱스를 생성해보자.

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' AND birth_date>='1965-02-01';

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

MySQL 8.0 버전부터는 옵티마이저가 gender 칼럼을 건너뛰어서 birth_date 칼럼만으로도 인덱스 검색이 가능하게 해주는 인덱스 스킵 스캔(Index skip scan) 최적화 기능이 도입됐다. 물론 MySQL 8.0 이전 버전에서도 인덱스 스킵 스캔과 비슷한 최적화를 수행하는 루스 인데스 스캔(Loose index scan) 이라는 기능이 있었지만 루스 인덱스 스캔은 GROUP BY 작업을 처리하기 위해 인덱스를 사용하는 경우에만 적용할 수 있었다. 하지만 MySQL 8.0 버전에 도입된 인덱스 스킵 스캔은 WHERE 조건절의 검색을 위해 사용 가능하도록 용도가 훨씬 넓어진 것이다.

우선 인덱스 스킵 스캔 기능을 비활성화하고, MySQL 8.0 이전 버전에서 어떤 실행 계획으로 처리됐는지를 한 번 살펴보자.

mysql> SET optimizer_switch='skip_scan=off';

mysql> EXPLAIN 
		SELECT gender, birth_ date 
        FROM employees 
        WHERE birth_date>=' 1965-02-01'; 
        
| id	| table	 	| type		 | key					 |Extra		 |
| 1 	| employees | index 	| ix_gender_birthdate 	| Using where; Using index|

위의 쿼리는 WHERE 조건절에 gender 칼럼에 대한 조건 없이 birth_date 칼럼의 비교 조건만 가지고 있기 때문에 쉽게 ix_gender_birthdate 인덱스를 효율적으로 이용할 수 없다. 위의 실행 계획에서 type 칼럼이 "index"라고 표시된 것은 인덱스를 처음부터 끝까지 모두 읽었다(풀 인덱스 스캔)는 의미이므로 인덱스를 비효율적으로 사용한 것이다. 이 예제 쿼리는 인덱스에 있는 gender 칼럼과 birth_date 칼럼만 있으면 처리를 완료할 수 있기 때문에 ix_gender_ birthdate 인덱스를 풀 스캔한 것이다. 만약 예제 퀴리가 employees 테이블의 모든 칼럼을 가져와야 했다면 테이블 풀 스캔을 실행했을 것이다.

이제 MySQL 8.0 버전부터 도입된 인덱스 스킵 스캔을 활성화하고, 동일 쿼리의 실행 계획을 다시 확인해보자.

mysql> SET optimizer_switch='skip_scan=on'

mysql> EXPLAIN SELECT gender, birth_date FROM employees WHERE birth_ date>=' 1965-02-01'; / id I table I type I key I Extra I 1 I employees I range I ix_gender_ birthdate I Using where; Using index for skip scan 

이번에는 퀴리의 실행 계획에서 type 칼럼의 값이 "range"로 표시됐는데, 이는 인덱스에서 꼭 필요한 부분만 읽었다는 것을 의미한다. 그리고 실행 계획의 Extra 칼럼에 "Using index for skip scan"이라 는 문구가 표시됐는데, 이는 ix_gender_birthdate 인덱스에 대해 인덱스 스킵 스캔을 활용해 데이터를 조회했다는 것을 의미한다. MySQL 옵티마이저는 우선 gender 칼럼에서 유니크한 값을 모두 조회해 서 주어진 쿼리에 gender 칼럼의 조건을 추가해서 쿼리를 다시 실행하는 형태로 처리한다. 다음의 그림 8.12는 인덱스 스킵 스캔이 어떻게 처리되는지를 보여준다.

gender 칼럼은 성별을 구분하는 칼럼으로 'M'과 'F' 값만 가지는 ENUM 타입의 칼럼이다. 그래서 gender 칼럼에 대해 가능한 값 2개('M'과 'F')를 구한 다음, 옵티마이저는 내부적으로 아래 2개의 쿼리를 실행 하는 것과 비슷한 형태의 최적화를 실행하게 된다.

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

여기서 gender 칼럼이 ENUM('M' 'F') 타입이기 때문에 이런 처리가 가능한 것은 아니다. 칼럼이 타입이 더라도 MySQL 서버는 인덱스를 루스 인덱스 스캔과 동일한 방식으로 읽으면서 인덱스에 존재하는 모든 값을 먼저 추 출하고 그 결과를 이용해 인덱스 스킵 스캔을 실행한다.

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

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

첫 번째 조건은 쿼리 실행 계획의 비용과 관련된 부분인데, 만약 유니크한 값의 개수가 매우 많다면 MySQL 옵티마이저는 인덱스에서 스캔해야 할 시작 지점을 검색하는 작업이 많이 필요해진다. 그래서 퀴리의 처리 성능이 오히려 더 느려질 수도 있다. 예를 들어 (emp_no, dept_no) 조합으로 만들어진 인덱 스에서 스킵 스캔을 실행한다고 가정하면 사원의 수만큼 레인지 스캔 시작 지점을 검색하는 작업이 필 요해져 퀴리의 성능이 매우 떨어진다. 그래서 인덱스 스킵 스캔은 인덱스의 선행 칼럼이 가진 유니크한 값의 개수가 소량일 때만 적용 가능한 최적화라는 것을 기억하자.

두 번째 제약 조건은 아래 예제 쿼리를 통해 한번 살펴보자.

mysql> EXPLAIN SELECT FROM employees WHERE birth_ date>= 1965-02-01'; i id I table I type I key I rows I Extra - 1 I employees I ALL I NULL I 300363 I Using where -

위의 쿼리는 WHERE 조건절은 동일하지만 SELECT 절에서 employees 테이블의 모든 칼럼을 조회하도록 변 경했다. 이 쿼리는 ix_gender_birthdate 인덱스에 포함된 gender 칼럼과 birth_date 칼럼 이외의 나머지 칼럼도 필요로 하기 때문에 인덱스 스킵 스캔을 사용하지 못하고 풀 테이블 스캔으로 실행 계획을 수립 한 것을 확인할 수 있다. 하지만 이 제약 사항은 MySQL 서버의 옵티마이저가 개선되면 충분히 해결될 수 있는 부분으로 보인다.

다중 칼럼 인덱스

실제 서비스용 데이터베 이스에서는 2개 이상의 칼럼을 포함하는 인덱스가 더 많이 사용된다. 두 개 이상의 칼럼으로 구성된 인덱스를 다중 칼럼 인덱스(또는 복합 칼럼 인덱스)라고 하며, 또한 2개 이상의 칼럼이 연결됐다고 해서 "Concatenated Index"라고도 한다. 그림 8.13은 2개 이상의 칼럼을 포함하는 다중 칼럼 인덱스의 구조를 보여준다.

그림 8.13에서는 편의상 루트 노드는 생략했으나 실제로 데이터 레코드 건수가 작은 경우에는 브랜치 노드가 없는 경우도 있을 수 있다. 하지만 루트 노드와 리프 노드는 항상 존재한다. 그림 8.13은 다중 칼럼 인덱스일 때 각 인덱스를 구성하는 칼럼의 값이 어떻게 정렬되어 저장되는지 설명해준다. 이 그림에서 중요한 것은 인덱스의 두 번째 칼럼은 첫 번째 칼럼에 의존해서 정렬돼 있다는 것이다. 즉, 두 번째 칼럼의 정렬은 첫 번째 칼럼이 똑같은 레코드에서만 의미가 있다는 것이다. 그림 8.13에서는 칼럼이 2개뿐이지만, 만약 칼럼이 4개인 인덱스를 생성한다면 세 번째 칼럼은 두 번째 칼럼에 의존해서 정렬되고 네 번째 칼럼은 다시 세 번째 칼럼에 의존해서 정렬된다. 위의 예제에서 emp_no 값의 정렬 순서가 빠르다고 하더라도 dept_no 칼럼의 정렬 순서가 늦다면 인덱스의 뒤쪽에 위치한다. 그래서 위의 그 림에서 emp_no 값이 "10003"인 레코드가 인덱스 리프 노드의 제일 마지막(하단)에 위치하는 것이다. 다중 칼럼 인덱스에서는 인덱스 내에서 각 칼럼의 위치(순서)가 상당히 중요하며, 그것을 아주 신중히 결정해야 하는 이유가 바로 그것이다.

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

인덱스를 생성할 때 설정한 정렬 규칙에 따라서 인덱스의 키 값은 항상 오름차순이거나 내림차순으로 정렬되어 저장된다. 하지만 어떤 인덱스가 오름차순으로 생성됐다고 해서 그 인덱스를 오름차순으로만 읽을 수 있다는 뜻은 아니다. 사실 그 인덱스를 거꾸로 끝에서부터 읽으면 내림차순으로 정렬된 인덱스로도 사용될 수 있다. 인덱스를 어느 방향으로 읽을지는 쿼리에 따라 옵티마이저가 실시간으로 만들어 내는 실행 계획에 따라 결정된다.

인덱스의 정렬

정렬 일반적인 상용 DBMS에서는 인덱스를 생성하는 시점에 인덱스를 구성하는 각 칼럼의 정렬을 오름차순 또는 내림차순으로 설정할 수 있다. MySQL 5.7 버전까지는 칼럼 단위로 정렬 순서를 혼합(ASC와 DESC 혼합)해서 인덱스를 생성할 수 없었다. 이런 문제점을 해결하기 위해 숫자 칼럼의 경우 -1을 곱한 값을 저장하는 우회 방법을 사용했었다. 하지만 MySQL 8.0 버전부터는 다음과 같은 형태의 정렬 순서를 혼합한 인덱스도 생성할 수 있게 됐다.

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

아마도 MySQL 5.7에서도 위와 같이 오름차순 칼럼과 내림차순 칼럼을 혼합한 인덱스를 생성했다고 기억하는 사용자도 있을 것이다. 실제 이렇게 인덱스를 생성해도 아무런 에러 없이 인덱스가 생성됐을 것이다. 하지만 실제 인덱 스는 모두 오름차순 정렬만으로 인덱스가 생성됐다. MySQL 5.7 버전까지는 ASC 또는 DESC 키워드는 앞으로 만들 어질 버전에 대한 호환성을 위해 문법상으로만 제공된 것이다.

인덱스 스캔 방향

first_name 칼럼에 대한 인덱스가 포함된 employees 테이블에 대해 다음 쿼리를 실행하는 과정을 살펴보자. MySQL은 이 퀴리를 실행하기 위해 인덱스를 처음부터 오름차순으로 끝까지 읽어 first_name이 가장 큰(오름차순으로 읽었을 때 가장 마지막 레코드) 값 하나를 가져오는 것일까?

mysql> SELECT * FROM employees ORDER BY first_name DESC LIMIT 1; 

그렇지 않다. 인덱스는 항상 오름차순으로만 정렬돼 있지만 인덱스를 최솟값부터 읽으면 오름차순으로 값을 가져올 수 있고, 최댓값부터 거꾸로 읽으면 내림차순으로 값을 가져올 수 있다는 것을 MySQL 옵티마이저는 이미 알고 있다. 그래서 위의 쿼리는 인덱스를 역순으로 접근해 첫 번째 레코드만 읽으면 된다. 그림 8.14는 인덱스를 정순으로 읽는 경우와 역순으로 읽는 경우를 보여준다.

즉, 인덱스 생성 시점에 오름차순 또는 내림차순으로 정렬이 결정되지만 쿼리가 그 인덱스를 사용하는 시점에 인덱스를 읽는 방향에 따라 오름차순 또는 내림차순 정렬 효과를 얻을 수 있다. 오름차순으로 생성된 인덱스를 정순으로 읽으면 출력되는 결과 레코드는 자동으로 오름차순으로 정렬된 결과가 되고, 역순으로 읽으면 그 결과는 내림차순으로 정렬된 상태가 되는 것이다.

mysql> SELECT * FROM employees WHERE first_name>='Anneke' ORDER BY first_name ASC LIMIT 4; 
mysql> SELECT * FROM employees ORDER BY first_name DESC LIMIT 5; 

위의 첫 번째 퀴리는 first_name 칼럼에 정의된 인덱스를 이용해 "Anneke"라는 레코드를 찾은 후, 정순으로 해당 인덱스를 읽으면서 4개의 레코드만 가져오면 아무런 비용을 들이지 않고도 원하는 정렬 효과를 얻을 수 있다. 두 번째 쿼리는 이와 반대로 employees 테이블의 first_name 칼럼에 정의된 인덱스를 역순으로 읽으면서 처음 다섯 개의 레코드만 가져오면 된다. 쿼리의 ORDER BY 처리나 MIN() 또는 MAX() 함수 등의 최적화가 필요한 경우에도 MySQL 옵티마이저는 인덱스의 읽기 방향을 전환해서 사용하도록 실행 계획을 만들어 낸다.

내림차순 인덱스

내림차순, 오름차순 인덱스 내부성능 비교

MySQL 서버에서 다음 두 쿼리는 실제 내림차순인지 오름차순인지와 관계없이 인덱스를 읽는 순서만 변경해서 해결할 수 있다는 것을 살펴봤다.

mysql> SELECT * FROM employees ORDER BY first_name ASC LIMIT 10; 
mysql> SELECT * FROM employees ORDER BY first_name DESC LIMIT 10; 

물론 다음과 같이 2개 이상의 칼럼으로 구성된 복합 인덱스에서 각각의 칼럼이 내림차순과 오름차순이 혼합된 경우에는 MySQL 8.0의 내림차순 인덱스로만 해결될 수 있다.

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

그렇다면 first_name 칼럼을 역순으로 정렬하는 요건만 있다면 다음 2개 인덱스 중에서 어떤 것을 선택 하는 것이 좋을까? 아니면 두 인덱스 모두 동일한 성능을 보일까?

mysql> CREATE INDEX ix_ firstname_asc ON employees (first_name ASC ); 
mysql> CREATE INDEX ix_ firstname_ desc ON employees (first_name DESC); 

이 궁금중에 대한 답을 찾기 위해 MySQL 8.0부터 지원되는 내림차순 인덱스에 대해 조금 깊이 있게 살펴보자.

우선 내용의 이해도를 높이기 위해 간단히 용어를 그림 8. 15와 같이 정리했다.

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

이제 내림차순 인덱스의 필요성에 대해 간단한 테스트 결과를 살펴보면서 알아보자. 간단한 테스트를 위해 다음과 같이 테스트용 테이블을 생성하고 대략 1천만 건 정도의 레코드를 준비해, 따져 보면 역순 정렬 쿼리가 정순 정렬 쿼리보다 28.9% 더 시간이 걸리는 것을 확인할 수 있다. 하나의 인덱스를 정순으로 읽느냐 또는 역순으로 읽느냐에 따라 이런 차이가 발생한다는 것은 쉽게 이해하기 어려울 수 있다. MySQL 서버의 InnoDB 스토리지 엔진에서 정순 스캔과 역순 스캔은 페이지(블록) 간의 양방향 연결 고리(Double linked list)를 통해 전진(Forward)하느냐 후진(Backward)하느냐의 차이만 있지만, 실제 내부적으로는 InnoDB에서 인덱스 역순 스캔이 인덱스 정순 스캔에 비해 느릴 수밖에 없는 다음의 두 가지 이유가 있다.

• 페이지 잠금이 인덱스 정순 스캔(Forward index scan)에 적합한 구조
• 페이지 내에서 인덱스 레코드가 단방향으로만 연결된 구조(그림 8.16에서 보다시피 InnoDB 페이지 내부에서 레코드들이 단방향으로만 링크를 가진 구조다.)

그림 8.16에서는 InnoDB 페이지 내부에서 레코드들이 정렬 순서대로 저장돼 있는 것처럼 표시돼 있지만 실제로 InnoDB 페이지는 힙(Heap)처럼 사용되기 때문에 물리적으로 저장이 순서대로 배치되지는 않는다. 그리고 각 데이터 페이지(innoDB 스토리지 엔진에서 데이터 파일은 프라이머리 키 인덱스 자체라는 것에 주의하자)나 인덱스 페이지의 엔트리(데이터 레코드 또는 인덱스 키)는 키 값과 데이터를 가지는데, 인덱스(프라이머리 키 인덱스와 세컨더리 인덱스 모두)의 루트 노드 또는 브랜치 노드라면 자식 노드의 주소를 가진다. 프라이머리 키에서 리프 노드의 "데이터"는 실제 레코드의 칼럼 값들이며, 세컨더리 인덱스 페이지에서는 프라이머리 키 값을 가진다.

인덱스 정렬 순서 선택

내림차순과 오름차순 인덱스의 내부적인 차이로 인한 성능을 살펴봤다. 이제 서비스 요건에 맞게 어떤 정렬 순서의 인덱스를 선택해야 할지 살펴보자. 일반적으로 인덱스를 ORDER BY DESC하는 쿼리가 소량의 레코드에 드물게 실행되는 경우라면 내림차순 인덱스를 굳이 고려할 필요는 없어 보인다. 예를 들어, 다음 쿼리를 한번 살펴보자.

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

이 퀴리의 경우 다음 두 가지 인덱스 모두 적절한 선택이 될 수 있다.

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

하지만 위 쿼리가 많은 레코드를 조회하면서 빈번하게 실행된다면 오름차순 인덱스보다는 내림차순 인덱스가 더 효율적이라고 볼 수 있다.

또한 많은 쿼리가 인덱스의 앞쪽만 또는 뒤쪽만 집중적으로 읽어서 인덱스의 특정 페이지 잠금이 병목이 될 것으로 예상된다면 쿼리에서 자주 사용되는 정렬 순서대로 인덱스를 생성하는 것이 잠금 병목 현상을 완화하는 데 도움이 될 것이다.

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

퀴리의 WHERE 조건이나 GROUP BY, 또는 ORDER BY 절이 어떤 경우에 인덱스를 사용할 수 있고 어떤 방식으로 사용할 수 있는지 식별할 수 있어야 한다. 그래야만 쿼리의 조건을 최적화하거나, 역으로 퀴리에 맞 게 인덱스를 최적으로 생성할 수 있다. 어떤 조건에서 인덱스를 사용할 수 있고 어떨 때 사용 할 수 없는지 살펴보겠다. 또한 인덱스를 100% 활용할 수 있는지, 일부만 이용하게 되는지도 함께 살펴본다.

비교 조건의 종류와 효율성

다중 칼럼 인덱스에서 각 칼럼의 순서와 그 칼럼에 사용된 조건이 동등 비교("=")인지 아니면 크다(">") 또는 작다("<") 같은 범위 조건인지에 따라 각 인덱스 칼럼의 활용 형태가 달라지며, 그 효율 또한 달라진다. 다음 예제를 한번 살펴보자.

mysql> SELECT * FROM dept_emp WHERE dept_no='d002' AND emp_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'인지 비교하는 과정을 거쳐야 한다. 그림 8.17은 두 인덱스의 검색 과정을 보여준다.

이처럼 인덱스를 통해 읽은 레코드가 나머지 조건에 맞는지 비교하면서 취사선택하는 작업을 '필터링' 이라고도 한다. 케이스 B 인덱스에서는 최종적으로 dept_no= 'd002' 조건을 만족(필터링)하는 레코드 5 건을 가져온다. 즉, 이 경우에는 5건의 레코드를 찾기 위해 7번의 비교 과정을 거친 것이다. 왜 이런 현상이 발생했을까? 그 이유는 그림 8.13 '다중 칼럼 인덱스'에서 설명한 다중 칼럼 인덱스의 정렬 방식 (인덱스의 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)

그림 8.18에서는 인덱스 키 값의 정렬만 표현하지만 사실은 인덱스 키 값의 이런 정렬 특성은 빠른 검색의 전제 조건이다. 즉 하나의 칼럼으로 검색해도 값의 왼쪽 부분이 없으면 인덱스 레인지 스캔 방식의 검색이 불가능하다. 또한 다중 칼럼 인덱스에서도 왼쪽 칼럼의 값을 모르면 인덱스 레인지 스캔을 사용할 수 없다.

케이스 A의 인덱스가 지정된 employees 테이블에 대해 다음과 같은 쿼리가 어떻게 실행되는지 한번 살 펴보자.

mysql> SELECT * FROM employees WHERE first_name LIKE '%mer'; 

이 쿼리는 인덱스 레인지 스캔 방식으로 인덱스를 이용할 수는 없다. 그 이유는 first_name 칼럼에 저장된 값의 왼쪽부터 한 글자씩 비교해 가면서 일치하는 레코드를 찾아야 하는데, 조건절에 주어진 상숫값 (%mer)에는 왼쪽 부분이 고정되지 않았기 때문이다. 따라서 정렬 우선순위가 낮은 뒷부분의 값만으로는 왼쪽 기준(Left-most) 정렬 기반의 인덱스인 B-tree에서는 인덱스의 효과를 얻을 수 없다. 케이스 B의 인덱스가 지정된 dept_emp 테이블에 대해 다음 쿼리가 어떻게 실행되는지 한번 살펴보자.

mysql> 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 절에도 똑같이 적용된다. GROUP BY나 ORDER BY에 대해서는 나중에 다시 자세히 살펴보겠다.

가용성과 효율성 판단

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

• NOT-EQUAL로 비교된 경우("<>", "NOT IN". "NOT BETWEEN", "IS NOT NULL")
  • .. WHERE column <> 'N'
  • .. WHERE column NOT IN (10,11,12)
  • .. WHERE column IS NOT NULL
• LIKE '%??'(앞부분이 아닌 뒷부분 일치) 형태로 문자열 패턴이 비교된 경우
  • .. WHERE column LIKE '%승환'
  • .. WHERE column LIKE '_승환'
  • .. WHERE column 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 조건도 작업 범위 결정 조건으로 인덱스를 사용한다.

mysql> .. 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까지 범위 결정 조건으로,
-- // colum_4는 체크 조건으로 사용됨 
mysql> .. WHERE column_1 = 1 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 IN (2,4) AND column_3 = 30 AND colum_4 LIKE '김승%'

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

작업 범위 결정 조건으로 인덱스를 사용하는 쿼리 패턴은 이 밖에도 상당히 많이 있겠지만, 대표적 인 것을 기억해 두면 좀 더 효율적인 쿼리를 쉽게 작성할 수 있다. 또한 여기서 설명하는 내용은 모두 B-Tree 인덱스의 특징이므로 MySQL뿐 아니라 대부분의 RDBMS에도 동일하게 적용된다.

R-Tree 인덱스

공간 인덱스는 R-Tree 인덱스 알고리즘을 이용해 2차원의 데이터를 인덱싱하고 검색하는 목적의 인덱스다. 기본적인 내부 메커니즘은 B-Tree와 흡사하다. B-Tree는 인덱스를 구성하는 칼럼의 값이 1차원의 스칼라 값인 반면, R-Tree 인덱스는 2차원의 공간 개념 값이라는 것이다. 최근 GPS나 지도 서비스를 내장하는 스마트 폰이 대중화되면서 SNS 서비스가 GIS와 GPS에 기반 을 둔 서비스로 확장되고 있다. 이러한 위치 기반의 서비스를 구현하는 방법은 여러 가지가 있겠지만 MySQL의 공간 확장(Spatial Extension)을 이용하면 간단하게 이러한 기능을 구현할 수 있다. MySQL의 공간 확장에는 다음과 같이 크게 세 가지 기능이 포함돼 있다.

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

전문 검색 인덱스

함수 기반 인덱스

때로는 컬럼의 값을 변형해서 만들어진 값에 대해 인덱스를 구축해야 할 때도 있는데 이러한 경우 함수 기반의 인덱스를 활용하면 된다. 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 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))
      );
      

함수를 직접 사용하는 인덱스는 테이블의 구조는 변경하지 않고 계산된 결과값의 검색을 빠르게 만들어준다. 함수 기반 인덱스를 제대로 활용하려면 반드시 조건절에 함수 기반 인덱스에 명시된 표현식 그대로 사용돼야 한다. 함수 생성 시 명시된 표현식과 쿼리의 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 테이블에서 프라이머리 키(클러스터링 인덱스)는 더 큰 장점을 제공하기 때문에 성능 저하에 대해 너무 걱정하지 않아도 된다.

세컨더리 인덱스를 통한 검색 과정

1. 세컨더리 인덱스의 리프 노드에서 프라이머리 키(PK) 값 찾기

• 세컨더리 인덱스의 리프 노드에는 해당 세컨더리 키와 함께 해당 레코드의 프라이머리 키(PK) 값이 저장되어 있다.
• 세컨더리 키를 통해 리프 노드에서 해당 레코드의 프라이머리 키 값을 찾는다.

2. 프라이머리 키를 이용한 클러스터드 인덱스 검색

• 찾아진 프라이머리 키 값을 이용하여 클러스터드 인덱스를 검색한다.
• 클러스터드 인덱스는 테이블의 레코드를 정렬된 순서로 유지하고 있으므로, 해당 프라이머리 키 값에 해당하는 레코드의 위치를 빠르게 찾을 수 있다.
• 클러스터드 인덱스를 통해 실제 데이터 레코드를 가져온다.

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

장점

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

단점

• 테이블의 모든 세컨더리 인덱스가 클러스터링 키를 갖기 때문에 클러스터링 키 값의 크기가 클 경우 전체적으로 인덱스의 크기가 커짐
• 세컨더리 인덱스를 통해 검색할 때 프라이머리 키로 다시 한번 검색히야 하므로 느림
• 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)이 동시에 삭제되기 때문에 자식 테이블의 레코드가 쓰기 잠금이 걸려있다면 풀릴 때까지 대기해야 한다.