[MySQL] 그외 인덱스(유니크, 외래키)

유니크 인덱스

---

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

유니크 인덱스와 일반 보조 인덱스의 비교

---

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

인덱스 읽기

많은 사람이 유니크 인덱스가 빠르다고 생각합니다. 하지만 이것은 사실이 아닙니다. 어떤 책에서는 유니크 인덱스는 1건만 읽으면 되지만 유니크하지 않은 일반 보조 인덱스에서는 한번 더 읽어야 하므로 느리다고 이야기합니다. 하지만 유니크하지 않은 보조 인덱스에서 한 번 더 해야 하는 작업은 디스크 읽기가 아니라 CPU에서 칼럼값을 비교하는 작업이기 때문에 이는 성능상의 영향이 거의 없다고 볼 수 있습니다. 유니크하지 않은 보조 인덱스는 중복된 값이 허용되므로 읽어야 할 레코드가 많아서 느린 것이지, 인덱스 자체의 특성 때문에 느린 것이 아니라는 것입니다. 즉, 레코드 1건을 읽는데 0.1초가 걸렸고 2건을 읽을 때 0.2초가 걸렸다고 했을 때 후자를 느리게 처리됐다고 할 수 없는 것과 같은 이치입니다.

인덱스 쓰기

새로운 레코드가 INSERT되거나 인덱스 칼럼의 값이 변경되는 경우에는 인덱스 쓰기 작업이 필요합니다. 그런데 유니크 인덱스의 키값을 쓸 때는 중복된 값이 있는지 없는지 체크하는 과정이 한 단계 더 필요합니다. 그래서 일반 보조 인덱스의 쓰기보다 느립니다. 그런데 MySQL에서는 유니크 인덱스에서 중복된 값을 체크할 때는 읽기 잠금을 사용하고, 쓰기를 할 때는 쓰기 잠금을 사용하는데 이 과정에서 데드락이 아주 빈번히 발생합니다. InnoDB 스토리지 엔진에는 인덱스 키의 저장을 버퍼링하기 위해 인서트 버퍼(Insert Buffer)가 사용됩니다. 그래서 인덱스의 저장이나 변경 작업이 상당히 빨리 처리되지만 안타깝게도 유니크 인덱스는 반드시 중복 체크를 해야 하므로 작업 자체를 버퍼링하지 못합니다. 이 때문에 유니크 인덱스는 일반 보조 인덱스보다 더 느려집니다.

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

---

꼭 필요한 경우라면 유니크 인덱스를 생성하는 것은 당연합니다. 하지만 더 성능이 좋아질 것으로 생각하고 불필요하게 유니크 인덱스를 생성하지는 않는 편이 좋습니다. 그리고 하나의 테이블에서 같은 칼럼에 유니크 인덱스와 일반 인덱스를 각각 중복해서 생성해 둔 경우가 가끔 있는데, MySQL의 유니크 인덱스는 일반 다른 인덱스와 같은 역할을 하므로 중복해서 인덱스를 생성할 필요는 없습니다. 즉, 다음과 같은 테이블에서 이미 nick_name이라는 칼럼에 대해 유니크 인덱스인 "ux_nickname"이 있기 때문에 ix_nickname 인덱스는 필요하지 않습니다. 이미 유니크 인덱스도 일반 보조 인덱스와 같은 역할을 동일하게 수행할 수 있으므로 다음과 같이 중복해서 보조 인덱스를 만들어줄 필요는 없습니다.

CREATE TABLE tb_unique (
    id INTEGER NOT NULL,
    nick_name VARCHAR(100),
    PRIMARY KEY (id),
    UNIQUE INDEX ux_nickname (nick_name),
    INDEX ix_nickname (nick_name)
);

그리고 가끔, 똑같은 칼럼에 대해 프라이머리 키와 유니크 인덱스를 동일하게 생성한 테이블도 있는데, 이 또한 불필요한 중복이므로 주의하는 것이 좋습니다. 이 밖에도 유니크 인덱스는 쿼리의 실행 계획이나 테이블의 파티셔닝에 미치는 영향이 있습니다.

결론적으로 유일성이 꼭 보장돼야 하는 칼럼에 대해서는 유니크 인덱스를 생성하되, 꼭 필요하지 않다면 유니크 인덱스보다는 유니크하지 않은 보조 인덱스를 생성하는 방법도 한 번씩 고려해 보는 것이 좋습니다.

외래키

---

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

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

테이블의 변경(쓰기 잠금)이 발생하는 경우에만 잠금 경합(잠금 대기)이 발생합니다.

외래키와 연관되지 않은 칼럼의 변경은 최대한 잠금 경합(잠금 대기)을 발생시키지 않습니다.

CREATE TABLE tb_parent (
    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, -- // parent.id 칼럼 참조
    fd VARCHAR(100) DEFAULT NULL,
    PRIMARY KEY (id),
    KEY ix_parentid (pid),
    CONSTRAINT child_ibfk_1 FOREIGN KEY (pid) REFERENCES tb_parent (id) ON DELETE CASCADE
) ENGINE-INNODB;

INSERT INTO tb_parent VALUES (1, 'parent-1'), (2, 'parent-2');
INSERT INTO tb_parent 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번 커넥션에서 먼저 트랜잭션을 시작하고 부모 테이블에서 id=2인 레코드에 UPDATE를 실행합니다. 이 과정에서 1번 커넥션이 TB_PARENT 테이블에서 id=2인 레코드에 대해 쓰기 잠금을 획득합니다. 그리고 2번 커넥션에서 자식 테이블(tb_child)의 외래키 칼럼(부모의 키를 참조하는 칼럼)인 pid를 2로 변경하는 쿼리를 실행해보겠습니다. 이 쿼리(작업번호 4번)는 부모 테이블의 변경 작업이 완료될 때까지 대기합니다. 다시 1번 커넥션에서 ROLLBACK이나 COMMIT으로 트랜잭션을 종료하면 2번 커넥션의 대기 중이던 작업이 즉시 처리되는 것을 확인할 수 있습니다. 즉 자식 테이블의 외래키 칼럼의 변경(INSERT, UPDATE)은 부모 테이블의 확인이 필요한데, 이 상태에서 부모 테이블의 해당 레코드가 쓰기 잠금이 걸려 있으면 해당 쓰기 잠금이 해제될 때까지 기다리게 되는 것입니다. 이것이 InnoDB의 외래키 관리의 첫 번째 특징에 해당합니다.

만약 자식 테이블의 외래키(pid)가 아닌 칼럼(tb_child 테이블의 fd 칼럼과 같은)의 변경은 외래키로 인한 잠금 확장이 발생하지 않습니다. 이는 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 테이블의 레코드를 삭제하려면 이 쿼리(작업번호 4번)는 tb_child 테이블의 레코드에 대한 쓰기 잠금이 해제될 때까지 기다려야 합니다. 이는 자식 테이블(tb_child)이 생성될 때 정의된 외래키의 특성(ON DELETE CASCADE) 때문에 레코드가 삭제되면 자식 레코드도 동시에 삭제되도록 작동하기 때문입니다.

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

인덱스 관련 주의 사항

---

스토리지 엔진별 지원 인덱스 목록

스토리지 엔진	인덱스 알고리즘(종류)
MyISAM	B-Tree, R-Tree(Spatial-index), Fulltext-index
InnoDB	B-Tree
Memory	B-Tree, Hash
TokuDB	Fractal-Tree
NDB (MySQL Cluster)	Hash, B-Tree

analyze와 optimize의 필요성

MySQL이나 InnoDB 테이블의 경우, 인덱스에 대한 통계 정보를 관리하고 각 통계 정보를 기반으로 쿼리의 실행 계획을 수립합니다. 인덱스에 대한 통계 정보는 아래와 같이 확인 할 수 있습니다.

root@localhost:test > SHOW INDEX FROM tb_test;

결과에서 가장 중요한 컬럼은 Cardinality 항목입니다. InnoDB와 MyISAM 모두 거의 칼럼의 Cardinality에 의존해서 실행 계획을 수립합니다.

MySQL의 인덱스 통계 정보에서 기억해야 할 점은 사용자나 DB 관리자도 모르는 사이에 통계 정보가 상당히 자주 업데이트된다는 것입니다. 그래서 쿼리의 실행 계획을 최적화하거나 동일하게 유지하기 위해 별도로 통계 정보를 백업했다가 복구하는 작업은 할 수도 없을뿐더러, 한다 해도 별로 의미가 없습니다. MySQL 서버가 케이블을 처음으로 열거나 대량의 데이터 변경 또는 테이블의 구조 변경(DDL)이 실행되면 통계 정보를 자동으로 갱신합니다.

가끔은 쿼리의 실행 계획이 의도했던 것과 너무 다르게 만들어질 때가 있습니다. 이런 경우는 인덱스의 통계 정보가 실제와는 너무 다르게 수집되어 MySQL이 실행 계획을 엉뚱하게 만들어 버리게 되는 것입니다. 이렇게 통계 정보가 크게 잘못되는 경우는 다음과 같을 때 자주 발생하는데, 이런 경우에는 ANALYZE 명령으로 통계 정보를 다시 수집해 보는 것이 좋습니다.

- 테이블의 데이터가 별로 없는 경우(주로 개발용 데이터베이스)

- 단시간에 대량의 데이터가 늘어나거나 줄어든 경우

출처: https://12bme.tistory.com/150?category=682920 [길은 가면, 뒤에 있다.]