mysql 참조 제약 조건 생성시 자동 인덱스 생성 테스트

mysql 의 innodb 엔진에서는 테이블 생성시 참조제약조건만 걸고 해당 컬럼에 인덱스를 생성하지 않아도 자동으로 인덱스가 생성이 된다. 

constraint 만 걸고 테이블을 생성하는 것과 명시적으로 키를 생성하는 것의 차이를 알아보자. 

drop table parent;
drop table child_no_idx;
drop table child_idx;


-- parent 테이블 생성 (일반 컬럼 추가)
CREATE TABLE parent (
    id INT NOT NULL PRIMARY KEY,
    name VARCHAR(50),
    email VARCHAR(100),
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
) ENGINE=InnoDB;

-- child 테이블 생성 (제약조건만 명시)
CREATE TABLE child_no_idx (
    id INT NOT NULL PRIMARY KEY,
    parent_id INT,
    name VARCHAR(50),
    email VARCHAR(100),
    description TEXT,
    CONSTRAINT `fk_no_idx_parent_id` FOREIGN KEY (`parent_id`) REFERENCES parent(id)
) ENGINE=InnoDB;

-- child 테이블 생성 (제약조건 + key를 명시)
CREATE TABLE child_idx (
    id INT NOT NULL PRIMARY KEY,
    parent_id INT,
    name VARCHAR(50),
    email VARCHAR(100),
    description TEXT,
    KEY `ix04_child` (`parent_id`),
    CONSTRAINT `fk_idx_parent_id` FOREIGN KEY (`parent_id`) REFERENCES parent(id)
) ENGINE=InnoDB;


-- 인덱스 확인을 위한 쿼리
SHOW INDEX FROM child_no_idx;

SHOW INDEX FROM child_idx;

참조제약조건이 걸려있으면 인덱스를 생성하지 않아도 자동으로 인덱스가 (제약조건 이름으로) 생성이 되었다. 

이제 세컨더리 인덱스를 아래와 같이 추가해보자. 

create index ix01_child on child_no_idx(name, parent_id);
create index ix02_child on child_no_idx(parent_id, name);

제약조건 이름으로 자동 생성되었던 인덱스가 사라졌다. 

참조 제약조건 컬럼(parent_id) 을 선두로 하는 인덱스가 명시적으로 생기면 자동 생성된 인덱스가 사라진다.  

 

두번째 명시적으로 key 를 만들었던 경우를 비교해보자. 

세컨더리 인덱스를 동일하게 추가해보자. 

create index ix01_child on child_idx(name, parent_id);
create index ix02_child on child_idx(parent_id, name);

사라지지 않고 남아있다.  

물론 ix02_child 인덱스가 parent_id 컬럼을 포함하므로 ix04_parent_id 인덱스는 삭제가 되어도 상관이 없다. 

다만 (제약조건으로 인해) 자동 생성된 인덱스는 자동 삭제가 될수도 있다는 것을 유의해야하고, 이를 인지 하지 않은 상태에서 인덱스를 추가할 경우 의도치 않게 삭제될수 있으니 무조건 명시적으로 key 를 만드는 것이 좋다.