MySQL(7)

오늘 한 것

8.6 함수 기반 인덱스

일반적인 인덱스는 칼럼의 값 일부(left-most니까 앞부분) 또는 전체에 대해서만 인덱스 생성이 허용된다. 하지만 때로는 칼럼의 값을 변형해서 만들어진 값에 대해 인덱스를 구축해야 하는 경우가 있다.

8.0부터 이러한 함수기반 인덱스를 지원한다. 두가지 방법이 있다.

1. 가상 칼럼을 이용한 인덱스
2. 함수를 이용한 인덱스

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

8.6.1 가상 칼럼을 이용한 인덱스

CREATE TABLE user {
  user_id BIGINT,
  first_name VARCHAR(10)
  last_name VARCHAR(10)
  PRIMARY KEY (user_id)
};

여기서 first_name과 last_name을 합쳐서 검색해야 하는 요건이 있다면, 이전에는 full_name이라는 칼럼을 추가하고, 모든 레코드에 대해 full_name을 업데이트 하는 작업을 거쳐야 full_name에 대한 인덱스를 만들 수 있었다.

8.0부터는 가상칼럼으로 이렇게도 된다.

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

가상칼럼이 VIRTUAL이나 STORED 옵션 중 어떤 옵션으로 생성됐든 관계없이 해당 가상 칼럼에 인덱스를 생성할 수 있다.

그러나 가상칼럼은 테이블에 새로운 칼럼을 추가하는 것과 같은 효과를 내기 때문에 실제 테이블의 구조가 변경된다는 단점이 있다.

VIRTUAL과 STORED의 차이는 15.8절에 다룬다.

함수를 이용한 인덱스

가상칼럼은 5.7버전에서도 사용할 수 있었지만, 그때는 함수를 직접 인덱스 생성 구문에 사용할 수는 없었다. 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) = 'Matt Lee'

만약 이 예제를 실행했을 때 옵티마이저가 표시하는 실행 계획이 ix_fullname 인덱스를 사용하지 않는 것으로 표시된다면 CONCAT 함수에 사용된 공백 문자 리터럴 때문일 가능성이 높다. 이 경우 다음 3개의 시스템 변수의 값을 동일 콜레이션 으로 일치 시킨 후 , 다시 테스트를 수행해보자.

collation_connection collation_database collation_server

이 두 방법은 같은 내부 구현을 갖는다. 따라서, 성능차이는 없다.

8.7 멀티 밸류 인덱스

전문 검색 인덱스를 제외한 모든 인덱스는 레코드 1건이 1개의 인덱스 키 값을 가진다. 즉, 인덱스 키와 데이터 레코드는 1:1의 관계를 가진다. 하지만 멀티밸류인덱스는 하나의 데이터 레코드가 여러개의 키 값을 가질 수 있는 형태의 인덱스다.

일반적인 RDBMS를 생각한다면, 이러한 인덱스는 정규화에 위배되는 형태다. 하지만, 최근 RDBMS 들이 JSON 데이터 타입을 지원하기 시작하면서 JSON 의 배열타입의 필드에 저장된 원소들에 대한 인덱스 요건이 발생한 것이다.

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

8.0이 되면서 이제 업그레이드 되어 부족함이 크게 없어졌다.

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

1. MEMBER OF()
2. JSON_CONTAINS()
3. JSON_OVERLAPS()

8.8 클러스터링 인덱스

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

8.8.1 클러스터링 인덱스

클러스터링 인덱스(MySQL 서버에서는 인덱스와 키는 동의어로 사용되며, 따라서 클러스터링 키 라고도 한다) 는 PK에만 적용되는 내용이다. 즉, PK값이 비슷한 레코드끼리 묶어서 저장하는 것을 클러스터링 인덱스 라고도 한다.

여기서 중요한 것은,

• PK에 의해 레코드의 저장 위치가 결정된다.
• PK변경시 레코드의 물리적인 저장위치가 변경된다.

사실 인덱스 알고리즘이라기보단 테이블 레코드의 저장 방식이라고 볼 수 있다.

그래서 클러스터링 인덱스 = 클러스터링 키 = 클러스터링 테이블이 다 같은 의미다.

InnoDB와 같이 항상 클러스터링 인덱스로 저장되는 거는 PK에 대한 검색이 매우 빠르다. 대신 레코드의 저장이나 PK의 변경이 상대적으로 느리다.

일반적인 BTree인덱스도 인덱스 대상 칼럼으로 정렬되어 잇지만, 클러스터링 인덱스라고 부르려면 PK를 기준으로 정렬되어 잇어야 한다.

클러스터링 테이블의 구조 자체는 일반 BTree와 비슷하다. 하지만 세컨더리 인덱스를 위한 BTree의 리프노드와는 달리 여기서는 리프노드에 레코드의 모든 칼럼이 같이 저장되어 있다.

만약 아래와 같이 PK를 변경하면 어떻게 될까

UPDATE tb_test SET emp_no=100002 WHERE emp_no=100007;

변경 대상이 페이지가 변경되었다. 실제로 PK값이 변경되는 경우는 별로 없을거다.

InnoDB가 아닌 테이블의 데이터 레코드는 PK나 인덱스 키 값이 변경되었다고 해서 실제 데이터 레코드의 위치가 변경되지는 않는다. 데이터 레코드가 insert될때 데이터 파일의 끝(또는 임의의 빈 공간)에 저장된다. 이렇게 한번 결정된 위치는 절대 바뀌지 않고, 레코드가 저장된 주소는 내부적으로 레코드를 식별하는 아이디로 인식된다. 이 주소를 ROWID 라고 표현하며, 일부 DBMS에서는 이 값을 사용자가 직접 조회하거나 쿼리에 사용할 수 있다. 근데 MySQL은 감춰져잇다.

그러면 PK가 없는 InnoDB테이블은 어떻게 클러스터링 테이블로 구성될까? 다음 우선순위로 결정한다.

1. PK가 잇으면 PK로 결정
2. NOT NULL 옵션의 유니크 인덱스 중에서 첫번째 인덱스를 클러스터링 키로 선택
3. 자동으로 유니크 한 값을 가지도록 증가되는 칼럼을 내부적으로 추가하고 클러스터링 키로 선택

이렇게 3번과 같은 경우, 아무 의미없는 PK 클러스터링 인덱스가 되면, 쿼리 문장에 명시적으로 사용할 수 없으니 아무런 혜택을 주지 못한다. 따라서 가능하다면 PK를 꼭 명시적으로 생성하자.

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

이제 PK가 세컨더리 인덱스에 어떤 영향을 미치는지 보자.

MyISAM이나 MEMORY 테이블 같은 클러스터링 되지 않은 테이블은 처음 저장된 공간에서 절대 이동하지 않는다.

데이터 레코드가 저장된 주소는 ROWID 역할을 한다고 언급한 바 있다. 그리고 PK나 세컨더리 인덱스의 각 키는 그 ROWID를 사용해서 실제 데이터 레코드를 찾아온다.

그래서 MyISAM, Memory 테이블에서는 PK와 세컨더리 인덱스는 구조적으로 아무런 차이가 없다. 
그러면 InnoDB테이블에서 세컨더리 인덱스가 실제 레코드가 저장된 주소를 갖고잇으면 어떻게 될까?
클러스터링 키 값이 변경될 때 마다 데이터 레코드의 주소가 변경되고, 그때마다 테이블의 모든 인덱스에 저장된 주솟값을  변경해야 한다. 이런 오버헤드를 제거하기 위해 InnoDB의 모든 세컨더리 인덱스는 해당 레코드가 저장된 주소가 아니라 PK를 저장한다. 

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

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

대체로 장점은 빠른 읽기, 단점은 느린 쓰기이다. 그런데 일반적으로 웹서비스와 같은 온라인 트랜잭션 환경 (OLTP)에서는 쓰기와 읽기의 비용이 1:9 또는 2:8이므로, 조금 느린 쓰기를 감수할 만 하다.

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

innodb에서 주의할 사항이 있다.

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

모든 세컨더리 인덱스가 PK값을 포함한다. 그래서 PK 크기가 커지면 세컨더리 인덱스가 자동으로 크기가 커진다. 하지만 일반적으로 테이블에 세컨더리 인덱스가 4~5개 정도 생성된다는 것을 고려하면 세컨더리 인덱스 크기는 급격하게 증가한다. 5개의 세컨더리 인덱스를 가지는 테이블의 PK가 10바이트인 경우와 50바이트인 경우를 한번 비교해보자.

1000만건이면 1.9GB가 증가한다.

8.8.4.2 프라이머리 키는 AUTO-INCREMENT 보다는 업무적인 칼럼으로 생성(가능한 경우)

기본으로 클러스터링 인덱스기때문에 PK 조회 속도가 빠르다. MyISAM 같은경우 논 클러스터링이라 PK로 뭘 선택해도 성능차이는 별로 없으나 InnoDB에선 차이가 크다. 또 PK는 의미만큼이나 중요한 역할을 하므로 대부분 검색에서 상당히 빈번하게 사용된다. 그러므로 그 칼럼의 크기가 크더라도, 업무적으로 해당 레코드를 대표할 수 있다면 그 칼럼을 PK로 설정하는 것이 옳다.

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

AUTO-INCREMENT 라도 써서 PK를 만들어라, 어차피 PK를 아예 안하고 Unique 도 없으면 내부적으로 일련번호 칼럼을 추가한다. 문제는 이렇게 추가된거는 사용자가 전혀 접근할 수 없으므로 차라리 AUTO_INCRE를 하는게 그래도 접근이 된다. 또한 ROW 기반 복제나 InnoDB 클러스터 에서는 모든 테이블이 PK를 가져야만 정상적인 복제성능을 보장하므로 그냥 꼭 생성하라.

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

여러개의 칼럼이 복합 PK가 되는 경우, PK 크기가 길어지는 경우가 있다. 하지만 PK가 길어도 세컨더리 인덱스가 필요하지 않다면 그대로 PK를 사용하는 것이 좋다. 세컨더리 인덱스도 필요하고 PK 크기도 길다면 AUTO_INCREMENT 칼럼을 추가하고, 이를 PK로 설정하면 된다. 이렇게 PK를 대체하기위해 인위적으로 추가된 PK를 인조 식별자(Surrogate Key) 라고 한다. 그리고 로그 테이블과 같이 조회보다는 INSERT 위주의 테이블들은 AUTO_INCREMENT를 이용한 인조 식별자를 PK로 설정하는 것이 성능 향상에 도움이 된다.

8.9 유니크 인덱스

유니크는 인덱스라기보단 제약조건에 가깝다. 그대로 테이블이나 인덱스에 같은 값이 2개 이상 저장될 수 없음을 의미한다.

  MySQL에서는 인덱스 없이 유니크 제약만 설정할 방법이 없다.
  유니크 인덱스에서 NULL도 저장될 수 있는데, NULL은 특정 값이 아니므로 2개 이상 저장될 수 있다.

MySQL에서 PK는 기본적으로 Null을 허용하지 않는 유니크 속성이 자동으로 부여된다. MyISAM, MeMORY에서 PK는 사실 NULL이 허용되지 않는 유니크 인덱스와 동일하지만, InnoDB의 PK는 클러스터링 키의 역할도 하므로 유니크 인덱스와는 근본적으로 다르다.

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

유니크 인덱스와 NON UNIQUE 세컨더리 인덱스는 사실 인덱스의 구조상 아무런 차이점이 없다.

8.9.1.1 인덱스 읽기

많은 사람이 유니크 인덱스가 빠르다고 생각하지만, 사실이 아니다. 어떤 책에서는

 유니크 인덱스는 1건만 읽으면 되지만, 비 유니크 세컨더리 인덱스에서는 레코드를 한건 더 읽어야 하므로 느리다

라고 하는데, 비 유니크 세컨더리 인덱스에서 한 번 더 해야하는 작업은 디스크 읽기가 아니라 CPU 에서 칼럼값을 비교하는 것 이므로 성능상 영향이 거의 없다.

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

즉, 레코드 1건을 읽는데 0.1초가 걸리고, 2건을 읽는데 0.2초가 걸렸다고 했을때, 후자를 느리게 처리됐다고 할 수 없는것과 같다.

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

1개의 레코드를 읽느냐 2개 이상의 레코드를 읽느냐의 차이만 있다는 것을 의미하고 읽어야할 레코드 건수가 같다면, 성능상의 차이는 미미하다.

8.9.1.2 인덱스 쓰기 성능 : 비>유니크

새로운 레코드가 insert 되거나 인덱스 칼럼의 값이 update 되는 경우에는, 인덱스 쓰기 작업이 필요하다. 그런데 유니크 인덱스의 키 값을 쓸때는 중복된 값이 있는지 체크하는 과정이 하나 더 필요하다.

그래서 유니크가 비유니크 세컨더리 인덱스보다 쓰기가 느리다.

그런데 MySQL에서는 유니크 인덱스에서 중복된 값을 체크할 때는 읽기 잠금을 사용하고, 쓸때는 쓰기 잠금을 사용하는데, 이 과정에서 데드락이 아주 빈번하게 발생한다. 또한 InnoDB 스토리지 엔진에서는 인덱스 키의 저장을 버퍼링하기 위해 체인지 버퍼 가 사용된다. 그래서 인덱스의 저장이나 변경이 빨리 처리되지만

유니크 인덱스는 반드시 중복 체크를 해야하므로 작업 자체를 버퍼링 하지 못한다. 

따라서 변경작업도 더 느리다.

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

꼭 필요한 경우라면 유니크 인덱스를 생성하는 것은 당연하다. 그러나 성능 향상을 생각하고 불필요하게 생성하지 않는것이 좋다.

MySQL의 유니크 인덱스는 다른 일반 인덱스와 같은 역할을 하므로 중복해서 인덱스를 생성할 필요는 없다.

결론적으로 유일성이 꼭 보장되어야 하는 칼럼에 대해 유니크 인덱스를 생성하되, 꼭 필요하지 않다면 유니크보단 비 유니크 세컨더리 인덱스를 고려해보자.

UNIQUE 제약조건은 데이터의 무결성을 위해 필요한 것이지, 단일 레코드 검색 성능을 높이기 위한 용도가 아니라는 점을 이해하는 것이 중요합니다.

8.10 외래키

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

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

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

위와 같은 테이블에서 언제 자식 테이블의 변경이 잠금 대기를 하고, 언제 부모 테이블의 변경이 잠금 대기를 하는지 예제로 살펴보자.

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

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