Real MySQL 8장 인덱스 #2 (8.6~)

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 스토리지 엔진의 클러스터링 테이블 구성 방법
  1. 프라이머리 키가 있으면 기본적으로 프라이머리 키를 클러스터링 키로 선택
  2. NOT NULL 옵션의 유니크 인덱스(UNIQUE INDEX) 중에서 첫 번째 인덱스를 클러스터링 키로 선택
  3. 자동으로 유니크한 값을 가지도록 증가되는 칼럼을 내부적으로 추가한 후, 클러스터링 키로 선택
• 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. 자식 테이블이 해당 부모를 참조하게 외래키 수정 // 경합
3. 1 롤백
4. 2 커밋

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

1. 자식 테이블 업데이트
2. 자식 테이블이 참조하는 부모 테이블의 해당 레코드 삭제 => cascade로 자식 테이블의 레코드 삭제 // 경합
3. 1 롤백
4. 2 커밋