8.6 함수 기반 인덱스
일반적으로 인덱스는 칼럼의 값 일부 또는 전체에 대해서만 인덱스 생성이 허용된다.
하지만 때로는 칼럼의 값을 변형해서 만들어진 값에 대해 인덱스를 구축하는 것도 필요하다.
함수 기반 인덱스를 구현하는 방법은 다음 두 가지로 구분할 수 있다.
가상 칼럼을 이용한 인덱스
함수를 이용한 인덱스
함수 기반 인덱스는 인덱싱할 값을 계산하는 과정의 차이만 있을 뿐, 실제 인덱스의 내부적인 구조 및 유지관리 방법은 B-Tree 인덱스와 동일하다.
8.6.1 가상 칼럼을 이용한 인덱스
MySQL 8.0 버전 부터는 가상 칼럼을 추가하고 그 가상 칼럼에 인덱스를 생성할 수 있게 됐다.
ALTER TABLE user
ADD full_name VARCHAR (30 ) AS (CONCAT(first_name, ' ' , last_name)) VIRTUAL,
ADD INDEX ix_fullname (full_name);
가상 칼럼은 테이블에 새로운 칼럼을 추가하는 것과 같은 효과를 내기 때문에 실제 테이블의 구조가 변경된다는 단점이 있다.
8.6.2 함수를 이용한 인덱스
MySQL 8.0 버전부터는 다음과 같이 테이블의 구조를 변경하지 않고, 함수를 직접 사용하는 인덱스를 생성할 수 있게 됐다.
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 옵티마이저는 다른 표현식으로 간주해서 함수 기반 인덱스를 사용하지 못한다.
EXPLAIN SELECT * FROM user WHERE CONCAT(first_name, ' ', last_name) = 'harris lee';
8.7 멀티 밸류 인덱스
전문 검색 인덱스를 제외한 모든 인덱스는 레코드 1건이 1개의 인덱스 키 값을 가진다. 즉, 인덱스 키와 데이터 레코드는 1:1의 관계를 가진다.
멀티 밸류 인덱스는 하나의 레코드에 여러 개의 키 값을 가질 수 있는 형태의 인덱스다.
JSON의 배열 타입의 필드에 저장된 원소들에 대한 인덱스 요건으로 인해 최근 RDBMS에서 지원하기 시작했다.
CREATE TABLE user (
user_id BIGINT AUTO_INCREMENT PRIMARY KEY,
first_name VARCHAR (10 ),
last_name VARCHAR (10 ),
credit_info JSON,
INDEX mx_creditscores ( (CAST (credit_info- > '$.credit_scores' AS UNSIGNED ARRAY )))
);
INSERT INTO user VALUES (1 , 'harris' , 'lee' , '{"credit_scores":[360, 353, 351]}' );
멀티 밸류 인덱스를 활요하기 위해서는 반드시 다음 함수들을 이용해서 검색해야 옵티마이저가 인덱스를 활용한 실행 계획을 수립한다.
MEMBER OF()
JSON_CONTAINS()
JSON_OVERLAPS()
8.8 클러스터링 인덱스
클러스터링이란 여러 개를 하나로 묶는다는 의미이다.
MySQL 서버에서 클러스터링은 테이블의 레코드를 비슷한 것(프라이머리 키를 기준으로)들끼리 묶어서 저장하는 형태로 구현된다.
주로 비슷한 값들을 동시에 조회하는 경우가 많다는 점에서 착안한 것이다.
8.8.1 클러스터링 인덱스
클러스터링 인덱스는 테이블의 프라이머리 키에 대해서만 적용되는 내용이다. 즉 프라이머리 키 값이 비슷한 레코드끼리 묶어서 저장하는 것을 클러스터링 인덱스라고 표현한다.
중요한 것은 프라이머리 키 값에 의해 레코드의 저장 위치가 결정된다는 것이다. 프라이머리 키 값이 변경된다면 그 레코드의 물리적인 저장 위치가 바뀌어야 한다는 것을 의미하기도 한다.
클러스터링 인덱스는 프라이머리 키 값에 의해 레코드의 저장 위치가 결정되므로 인덱스 알고리즘이라기보다 테이블 레코드의 저장 방식이라고 볼 수 있다.
InnoDB와 같이 항상 클러스터링 인덱스로 저장되는 테이블은 프라이머리 키 기반의 검색이 매우 빠르며, 대신 레코드의 저장이나 프라이머리 키의 변경이 상대적으로 느리다.
클러스터링 인덱스의 리프 노드에는 레코드의 모든 칼럼이 같이 저장돼 있다. 클러스터링 테이블은 그 자체로 하나의 거대한 인덱스 구조로 관리된다.
프라이머리 키 값이 바뀌면실제 레코드의 위치가 변경된다.
InnoDB 스토리지 엔진의 클러스터링 테이블 구성 방법
프라이머리 키가 있으면 기본적으로 프라이머리 키를 클러스터링 키로 선택
NOT NULL 옵션의 유니크 인덱스(UNIQUE INDEX) 중에서 첫 번째 인덱스를 클러스터링 키로 선택
자동으로 유니크한 값을 가지도록 증가되는 칼럼을 내부적으로 추가한 후, 클러스터링 키로 선택
InnoDB 테이블에서 클러스터링 인덱스는 테이블당 단 하나만 가질 수 있는 엄청난 혜택이므로 가능하다면 프라이머리 키를 명시적으로 생성하자.
8.8.2 세컨더리 인덱스에 미치는 영향
InnoDB 테이블의 모든 세컨더리 인덱스는 해당 레코드가 저장된 주소가 아니라 프라이머리 키 값을 저장하도록 구현돼 있다.
employees 테이블에서 first_name 칼럼으로 검색하는 경우 프라이머리 키로 클러스터링된 InnoDB와 그렇지 않은 MyISAM을 비교해보자
CREATE TABLE employees (
emp_no INT NOT NULL ,
first_name VARCHAR (20 ) NOT NULL ,
PRIMARY KEY (emp_no),
INDEX ix_firstname (first_name)
);
SELECT * FROM employees WHERE first_name= 'seonghun' ;
MyISAM : ix_firstname 인덱스를 검색해서 레코드의 주소를 확인한다. 해당 레코드 주소로 최종 레코드를 가져온다.
InnoDB : ix_firstname 인덱스를 검색해서 레코드의 프라이머리 키 값을 확인한다. 해당 프라이머리 키 인덱스를 검색해서 최종 레코드를 가져온다.
InnoDB가 조금 더 복잡하게 처리 됨을 확인할 수 있다.
InnoDB 테이블에서 프라이머리 키는 더 큰 장점을 제공하기 때문에 성능 저하에 대해 너무 걱정하지 않아도 된다.
8.8.3 클러스터링 인덱스의 장점과 단점
장점
프라이머리 키로 조회할 때 처리 성능이 매우 빠름
테이블의 모든 세컨더리 인덱스가 프라이머리 키를 가지고 있기 때문에 인덱스만으로 처리 되는 경우가 많다(커버링 인덱스)
단점
모든 세컨더리 인덱스가 클러스터링 키를 갖기 때문에 키 값이 크면 전체적으로 인덱스의 크기가 커진다.
세컨더리 인덱스를 통해 검색할 때 프라이머리 키로 한번 더 검색해야해서 성능이 느리다.
INSERT 할 때 프라이머리 키에 의해 레코드의 저장 위치가 결정되기 때문에 처리 성능이 느리다.
대부분 클러스터링 인덱스의 장점은 빠른 읽기이며, 단점은 느린 쓰기라는 것을 알 수 있다.
8.8.4 클러스터링 테이블 사용 시 주의사항
8.8.4.1 클러스터링 인덱스 키의 크기
모든 세컨더리 인덱스가 프라이머리 키(클러스터링 키) 값을 포함한다.
따라서, 프라이머리 키의 크기가 커지면 세컨더리 인덱스도 자동으로 크기가 커진다. 일반적으로 테이블에 세컨더리 인덱스가 여러개 존재한다면 인덱스의 크기가 급격히 커지게된다.
8.8.4.2 프라이머리 키는 AUTO-INCREMENT보다는 업무적인 칼럼으로 생성(가능한 경우)
프라이머리 키 값에 의해 레코드의 위치가 결정된다. 따라서 프라이머리 키로 검색하는 경우 매우 빠르게 처리된다.
칼럼의 크기가 크더라도 업무적으로 해당 레코드를 대표할 수 있다면 그 칼럼을 프라이머리 키로 설정하는 것이 좋다.
8.8.4.3 프라이머리 키는 반드시 명시할 것
프라이머리 키가 없는 테이블은 자동으로 생성되고 이는 사용자에게 보이지 않아서 접근할 수 없다.
프라이머리 키는 꼭 생성하자.
8.8.4.4 AUTO-INCREMENT 칼럼을 인조 식별자로 사용할 경우
프라이머리 키가 길고 세컨더리 인덱스도 필요하다면 인조 식별자를 Auto_INCREMENT 로 설정하자
8.9 유니크 인덱스
MySQL에서는 인덱스 없이 유니크 제약만 설정할 방법이 없다.
유니크 인덱스에서는 NULL도 저장될 수 있는데, NULL은 특정한 값이 아니므로 2개 이상 저장될 수 있다.
프라이머리 키는 NULL을 허요하지 않는 유니크 속성이 자동으로 부여된다.
8.9.1 유니크 인덱스와 일반 세컨더리 인덱스의 비교
8.9.1.1 인덱스 읽기
대부분 유니크 인덱스가 빠르다고 생각하지만 사실이 아니다.
같은 수의 레코드를 읽는다면 성능상 차이는 미미하다.
8.9.1.2 인덱스 쓰기
유니크 인덱스의 키 값을 쓸 때는 중복된 값이 있는지 여부를 체크하는 과정이 필요하다. 따라서 유니크하지 않은 세컨더리 인덱스보다 더 느리다.
8.9.2 유니크 인덱스 사용 시 주의사항
성능이 더 좋아질 것이라고 생각해서 불필요하게 유니크 인덱스를 생성하지 말자.
같은 컬럼에 유니크와 일반 인덱스를 중복으로 생성하지 말자.
같은 컬럼에 프라이머리 키와 유니크 인덱스를 생성하지 말자.
유니크 인덱스보다 유니크하지 않은 세컨더리 인덱스를 생성하는 것도 고려해보자.
8.10 외래키
외래키 제약이 설정되면 자동으로 연관된 테이블의 칼럼에 인덱스가 생성된다.
외래키가 제거되지 않은 상태에서는 자동으로 생성된 인덱스를 삭제할 수 없다.
외래키 관리의 특징
테이블의 변경이 발생하는 경우에만 잠금 경합이 발생한다.
외래키와 연관되지 않은 칼럼의 변경은 최대한 잠금 경합을 발생시키지 않는다.
8.10.1 자식 테이블의 변경이 대기하는 경우
부모 테이블의 업데이트
자식 테이블이 해당 부모를 참조하게 외래키 수정 // 경합
1 롤백
2 커밋
8.10.2 부모 테이블의 변경 작업이 대기하는 경우
자식 테이블 업데이트
자식 테이블이 참조하는 부모 테이블의 해당 레코드 삭제 => cascade로 자식 테이블의 레코드 삭제 // 경합
1 롤백
2 커밋
