8. 인덱스 (4)

함수 기반 인덱스

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

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

가상 컬럼을 이용한 인덱스

1
2
3
4
5
6
7
CREATE TABLE user
(
    user_id    BIGINT,
    first_name VARCHAR(10),
    last_name VARCHAR(10),
    PRIMARY KEY (user_id)
);

이전 버전의 MySQL은 해당 테이블에서 first_name과 last_name을 합쳐 검색해야 하는 요건이 생겼다면 full_name이라는 컬럼을 추가하고 모든 레코드에 대해 해당 컬럼을 업데이트 하는 작업을 거처야 인덱스를 생성 할 수 있었다.

하지만 MySQL 8.0 버전 부터는 가상 컬럼을 추가하고 해당 가상 컬럼에 인덱스를 생성할 수 있게 됐다.

1
2
3
ALTER TABLE user
    ADD full_name VARCHAR(30) AS (CONCAT(first_name, ' ', last_name)) VIRTUAL,
    ADD INDEX ix_fullname (full_name);

가상 컬럼이 VIRTUAL, STORED 옵션 중 어떤 옵션으로 생성됐든 관계 없이 해당 가상 컬럼에 인덱스를 생성할 수 있다. 가상 컬럼은 테이블에 새로운 컬럼을 추가하는 것과 같은 효과를 내기 때문에 실제 테이블의 구조가 변경된다는 단점이 있다.

함수를 이용한 인덱스

가상 컬럼은 MySQL 5.7 버전에서도 사용할 수 있었지만 함수를 직접 인덱스 생성 구문에 사용할 수는 없었다. 하지만 MySQL 8.0 버전 부터는 테이블 구조를 변경하지 않고, 함수를 직접 사용하는 인덱스를 생성할 수 있게 됐다.

1
2
3
4
5
6
7
8
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 옵티마이저는 다른 표현식으로 간주해서 함수 기반 인덱스를 사용하지 못한다.

가상 컬럼과 함수를 직접 이용하는 인덱스는 사용법과 문법에서 조금 차이가 있지만, 내부적으로 동일한 구현 방법을 사용하므로 어떤 방법을 사용하더라도 둘의 성능 차이는 발생하지 않는다.

멀티 밸류 인덱스

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

JSON 포맷으로 데이터를 저장하는 MongoDB는 처음부터 이런 형태의 인덱스를 지원하고 있었지만 MySQL 서버는 멀티 밸류 인덱스에 대한 지원 없이 JSON 타입의 컬럼만 지원했다. 하지만 배열 형태에 대한 인덱스 생성이 되지 않아 MongoDB의 기능과 많이 비교되곤 했다.

MySQL 8.0 버전으로 업그레이드되면서 JSON 관리 기능은 MongoDB에 비해서도 부족함이 없는 상태로 발전했다.

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
CREATE TABLE user
(
    user_id    BIGINT AUTO_INCREMENT PRIMARY KEY,
    first_name VARCHAR(10),
    last_name VARCHAR(10),
    credit_info JSON,
    INDEX imx_creditscores ((CAST(credit_info->'$.credit_scores' AS UNSIGNED ARRAY)))
);

INSERT INTO user VALUES (1, 'mario', 'lee', '{"credit_scores":[360, 353, 351]}');

멀티 밸류 인덱스를 활용하기 위해서는 일반적인 조건 방식을 사용하면 안 되고, 반드시 다음 함수들을 이용해서 검색해야 옵티마이저가 인덱스를 활용한 실행 계획을 수립한다.

MEMBER OF()
JSON_CONTAINS()
JSON_OVERLAPS()

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

MySQL 서버의 Worklog에는 DECIMAL, INTEGER, DATETIME, VARCHAR/CHAR 타입에 대해 멀티 밸류 인덱스를 지원한다고 명시돼 있지만 MySQL 8.0.21 버전에서는 VARCHAR/CHAR 타입에 대해서는 지원하지 않는다. 하지만 곧 VARCHAR/CHAR타입의 배열 형태 CAST와 멀티 밸류 인덱스가 지원될 것으로 예상된다.

클러스터링 인덱스

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

클러스터링 인덱스

클러스터링 인덱스는 테이블의 프라이머리 키에 대해서만 적용되는 내용이다. 즉 프라이머리 키 값이 비슷한 레코드끼리 묶어서 저장하는 것을 클러스터링 인덱스라고 표현한다.

따라서 프라이머리 키 값에 의해 레코드에 저장 위치가 결정되며, 프라이머리 키 값이 변경된다면 그 레코드의 물리적인 저장 위치가 바뀌어야 한다는 것을 의미한다.

프라이머리 키 값으로 클러스터링 된 테이블은 프라이머리 키 값 자체에 대한 의존도가 상당히 크기 때문에 신중히 프라이머리 키를 결정해야한다.

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

클러스터링 인덱스 구조를 보면 클러스터링 테이블의 구조 자체는 일반 B-Tree와 비슷하다. 하지만 세컨더리 인덱스를 위한 B-Tree의 리프 노드와는 달리 클러스터링 인겟스의 리프 노드에는 레코드의 모든 컬럼이 같이 저장돼 있음을 알 수 있다.

1
UPDATE tb_test SET emp_no=10002 WHERE emp_no=10007;

프라이머리 키가 없는 InnoDB 테이블에서는 InnoDB 스토리지 엔진이 다음 우선순위대로 프라이머리 키를 대체할 컬럼을 선택한다.

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

InnoDB 스토리지 엔진이 적절한 클러스터링 키 후보를 찾지 못하는 경우 InnoDB 스토리지 엔진이 내부적으로 레코드의 일련번호 컬럼을 생성하는데, 자동으로 추가된 프라이머리 키는 사용자에게 노출되지 않으며, 쿼리 문장에 명시적으로 사용할 수 없다. 즉 프라이머리 키나 유니크 인덱스가 전혀 없는 InnoDB 테이블에서는 아무 의미 없는 숫자 값으로 클러스터링되어 사용자에게 아무런 혜택도 주지 않는다.

따라서 InnoDB 테이블에서 클러스터링 인덱스는 테이블당 단 하나만 가질수 있는 엄청난 혜택이므로 가능하다면 프라이머리 키를 명시적으로 생성하는 것이 좋다.

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

MyISAM이나 MEMORY 테이블 같은 클러스터링되지 않은 테이블은 INSERT될 때 처음 저장된 공간에서 절대 이동하지 않는다. 데이터 레코드가 저장된 주소는 내부적인 레코드 아이디(ROWID) 역할을 하며, 프라이머리 키나 세컨더리 인덱스의 각 키는 그 주소를 이용해 실제 데이터를 찾아오기 때문에 프라이머리 키와 세컨더리 인덱스는 구조적으로 아무런 차이가 없다.

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

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

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

장점

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

단점

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

일반적으로 웹 서비스와 같은 온라인 트랜잭션 환경에서는 쓰기와 읽기 비율이 2:8, 1:9 정도이기 때문에 조금 느린 쓰기를 감수하고 읽기를 빠르게 유지하는 것은 매우 중요하다.

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

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

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

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

레코드 한 건을 생각하면 커 보이지 않을 수 있으나 레코드 건수가 100만 건만 돼도 인덱스의 크기가 거의 190MB나 증가한다. 또한 인덱스가 커질수록 같은 성능을 내기 위해 그만큼의 메모리가 더 필요해지므로 InnoDB 테이블의 프라이머리 키는 신중하게 선택해야 한다.

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

InnoDB의 프라이머리 키는 클러스터링 키로 사용되며, 이 값에 의해 레코드의 위치가 결정된다. 즉 프라이머리 키로 검색하는 경우 클러스터링 되지않은 테이블에 비해 매우 빠르게 처리될 수 있음을 의미한다. 프라이머리 키는 의미만큼이나 중요한 역할을 하기 때문에 대부분 검색에서 상당히 빈번하게 사용되는 것이 일반적이다. 그러므로 설령 컬럼의 크기가 크더라도 업무적으로 해당 레코드를 대표할 수 있다면 그 컬럼을 프라이머리 키로 설정하는 것이 좋다.

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

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

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

여러 개의 컬럼이 복합으로 프라이머리 키가 만들어지는 경우 프라이머리 키의 크기가 길어질 때가 가끔 있는데, 프라이머리 키의 크기가 길어도 세컨더리 인덱스가 필요하지 않다면 그대로 프라이머리 키를 사용하는 것이 좋다. 세컨더리 인덱스도 필요하고 프라이머리 키의 크기도 길다면 AUTO_INCREMENT컬럼을 추가하고 이를 프라이머리 키로 설정한다.

이렇게 프라이머리 키를 대체하기 위해 인위적으로 추가된 프라이머리 키를 인조 식별자(Surrogate key)라고 한다. 그리고 로그 테입르과 같이 조회보다는 INSERT 위주의 테이블들은 AUTO_INCREMENT를 이용한 인조 식별자를 프라이머리 키로 설정하는 것이 성능 향상에 도움이 된다.

유니크 인덱스

유니크는 인덱스라기보다는 제약 조건에 가깝다. 테이블이나 인덱스에 같은 값이 2개 이상 저장될 수 없음을 의미하는데, MySQL에서는 인덱스 없이 유니크 제약만 설정할 방법이 없다.

유니크 인덱스에서 NULL도 저장될 수 있는데, NULL은 특정 값이 아니므로 2개 이상 저장될 수 있다. MySQL에서 프라이머리 키는 기본적으로 NULL을 허용하지 않는 유니크 속성이 자동으로 부여된다.

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

유니크 인덱스와 유니크하지 않은 일반 세컨더리 인덱스는 인덱스 구조상 아무런 차이점이 없다. 하지만 읽기와 쓰기에 대해 성능적으로 다른 부분이 있다.

인덱스 읽기

많은 사람들이 유니크 인덱스가 빠르다고 생각하지만, 유니크하지 않은 세컨더리 인덱스에서 한 번 더 해야 하는 작업은 디스크 읽기가 아니라 CPU에서 컬럼 값을 비교하는 작업이기 때문에 성능상 영향이 거의 없다.

유니크하지 않은 세컨더리 인덱스는 중복된 값이 허용되므로 읽어야 할 레코드가 많아서 느린것이지, 인덱스 자체의 특성 때문에 느린 것이 아니다.

하나의 값을 검색하는 경우, 유니크 인덱스와 일반 세컨더리 인덱스는 사용되는 실행 계획이 다르다. 하지만 이는 인덱스의 성격이 유니크한지 아닌지에 따른 차이일 뿐 큰 차이는 없다.

인덱스 쓰기

새로운 레코드가 INSERT 되거나 인덱스 컬럼의 값이 변경되는 경우에는 인덱스 쓰기 작업이 필요하다. 그런데 유니크 인덱스의 키 값을 쓸 때는 중복된 값이 있는지 없는지 체크하는 과정이 한 단계 더 필요하다. 그래서 유니크하지 않은 세컨더리 인덱스의 쓰기보다 느리다.

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

또한 InnoDB 스토리지 엔진에는 인덱스 키의 저장을 버퍼링 하기 위해 체인지 버퍼(Change Buffer)를 사용하여 인덱스의 저장이나 변경 작업이 상당히 빨리 처리되지만, 유니크 인덱스는 반드시 중복 체크를 해야하므로 작업 자체를 버퍼링 하지 못한다. 이 때문에 유니크 인덱스는 일반 세컨더리 인덱스보다 변경 작업이 더 느리다.

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

꼭 필요한 경우라면 유니크 인덱스를 생성하는 것은 당연하나, 성능이 좋아질 것으로 생각하고 불필요하게 유니크 인덱스를 생성하지는 않는 것이 좋다.
하나의 테이블에서 같은 컬럼에 유니크 인덱스와 일반 인덱스를 각각 중복해서 생성해 둔 경우가 가끔 있는데, 같은 역할을 하므로 중복해서 인ㄷ게스를 생성할 필요는 없다.
똑같은 컬럼에 대해 프라이머리 키와 유니크 인덱스를 동일하게 생성하는 경우도 있는데 이 또한 중복이다.

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

외래키

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

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

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

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
CREATE TABLE tb_parant (
    id INT NOT NULL ,
    fd VARCHAR(100) NOT NULL, 
    PRIMARY KEY (id)
) ENGINE=InnoDB;

CREATE TABLE tb_child (
    id INT NOT NULL,
    pid INT DEFAULT NULL,
    fd VARCHAR(100) DEFAULT NULL,
    PRIMARY KEY (id),
    KEY ix_parentid (pid),
    CONSTRAINT child_ibfk_1 FOREIGN KEY (pid) REFERENCES tb_parant (id) ON DELETE CASCADE 
) ENGINE=InnoDB;

INSERT INTO tb_parant VALUES (1, 'p1'), (2, 'p2');
INSERT INTO tb_child VALUES (100, 1, 'c100');

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

작업 번호	커넥션-1	커넥션-2
1	BEGIN;
2	UPDATE tb_parent SET fb=‘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

2번 커넥션 는 부모 테이블의 변경 작업이 완료될 때까지 대기한다. 1번 커넥션에서 트랜잭션이 종료하면 2번 커넥션의 대기중이던 작업이 즉시 처리되는 것을 확인할 수 있다. 즉 자식 테이블의 외래 키 컬럼의 변경은 부모 테이블의 확인이 필요한데, 이 상태에서 부모 테이블의 해당 레코드가 쓰기 잠금이 걸려있으면 해당 쓰기 잠금이 해제될 때까지 기다리게 된다.

자식 테이블의 외래키가 아닌 컬럼의 변경은 외래키로 인한 잠금 확장이 발생하지 않는다.

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

작업 번호	커넥션-1	커넥션-2
1	BEGIN;
2	UPDATE tb_child SET fb=‘changed-100’ WHERE id=100;
3		BEGIN;
4		DELETE FROM tb_parent WHERE id=1;
5	ROLLBACK;
6		Query OK, 1 row affected

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

데이터베이스에서 외래 키를 물리적으로 생성하려면 이러한 현상으로 인한 잠금 경합까지 고려해 모델링을 진행하는 것이 좋다.
물리적으로 외래키를 생성하면 자식 테이블에 레코드가 추가되는 경우 해당 참조키가 부모 테이블에 있는지 확인하는데, 물리적인 외래키의 고려 사항은 이러한 체크 작업이 아니라 이러한 체크를 위해 연관 테이블에 읽기 잠금을 걸어야 한다는 점이다.
이렇게 잠금이 다른 테이블로 확장되면 그만큼 전체적으로 쿼리의 동시 처리에 영향을 미친다.