Real MySQL의 인덱스 내용을 개인 공부를 위해 정리한 글입니다.
모든 저작권은 Real MySQL 8.0 (위키북스 / 백은빈, 이성욱 저)에 있음을 밝힙니다.
인덱스란
DBMS에서 인덱스는 데이터의 저장(insert, update, delete) 성능을 희생하고 데이터의 읽기 성능을 높이는 기능이다.
인덱스의 종류
- 프라이머리 키 (Primary Key): 레코드를 대표하는 컬럼의 값으로 만들어진 인덱스를 말한다. 식별자라고도 하며 NULL값을 허용하지 않고 중복을 허용하지 않는 것이 특징이다.
- 보조 키 혹은 세컨더리 인덱스 (Secondary Index) : 유니크 인덱스는 프라이머리 키와 성격이 비슷하고 프라이머리 키를 대체해여 사용할 수 도 있어 대체 키라고도 한다.
데이터 저장 방식에는 B-Tree 알고리즘 혹은 Hash를 사용하는데 보통 컬럼 값으로 정렬을 한 B-Tree 알고리즘을 많이 사용한다.
데이터의 중복 허용 여부에 따라 유니크 인덱스와 유니크 하지 않은 인덱스 (Non-Unique Index)로 구분할 수 있다.
B-Tree 인덱스
구조
루트 노드가 있고 루트 노드는 각 브랜치 노드(자식 노드)와 이어져 있다. 최 하위에는 리프 노드가 존재하고 인덱스 레코드에는 실제 데이터 파일에 저장된 데이터를 가리키는 주솟값을 가지고 있다.
인덱스의 키 값은 항상 정렬되어 있지만 데이터 파일에는 임의의 순서를 가진다.
MyISAM과 InnoDB에서 인덱스 구조 차이
MyISAM에서는 리프 노드에 레코드 주소가 저장되며 실제 파일 내의 주솟값(ROWID)을 저장한다.
InnoDB에서는 프라이머리 키가 저장되며 ROWID 역할을 하는데 실제 데이터를 읽기 위해 다시 데이터 파일에서 프라이머리 키를 저장하고 있는 B-Tree를 한번 더 읽어야한다.
B-Tree 인덱스 키 추가 및 삭제
- 인덱스 키 추가
B-Tree에서 인덱스의 추가는 적절한 위치를 검색하는 비용과 해당 브랜치 노드가 꽉 찬 상태인 경우 두 개로 분리되어야하는 데 이때 상위 노드까지 작업이 전파된다.
MyISAM에서는 INSERT는 즉시 이루어지지만 InnoDB에선 INSERT는 작업을 지연시켜 체인지 버퍼를 이용하여 나중에 처리한다.
- 인덱스 키 삭제
해당 키 값이 저장된 B-TRee의 리프 노드를 찾아서 그냥 삭제 마크만 하면 작업이 완료된다. 삭제 마크도 디스크 쓰기가 필요하므로 I/O가 수반되며 InnoDB 엔진에서는 지연되어 처리될 수 있다.
- 인덱스 키 변경
인덱스 키 값이 변경되면 리프 노드의 위치가 변경되어야 한다. B-Tree의 키 값 변경 작업은 기존 키를 삭제하고 새로운 변경된 키를 추가하도록 한다.
인덱스 키 검색
인덱스를 찾는 검색 작업은 B-Tree의 루트 노드부터 시작해 브랜치 노드를 거쳐 리프 노드까지 이동하면서 비교 작업을 수행하는데 이 과정을 "트리 탐색"이라고 한다.
B-Tree의 인덱스를 검색할 때 효율적으로 사용하려면 인덱스를 이용한 검색은 반드시 값의 앞부분(Left-Most)을 일치시켜 찾는 때만 적용된다. 부등호 비교 조건에서도 인덱스를 활용할 수있지만, 인덱스를 구성하는 키 값의 뒷부분만 검색하는 용도로는 인덱스를 사용할 수 없다.
또한 기존 키 값을 함수나 연산을 통해 변형하여 새로운 값으로 검색을 하는 경우에도 B-Tree의 빠른 검색을 이용할 수 없다.
InnoDB 엔진을 이용하는 테이블은 레코드 잠금이나 넥스트 키락이 검색을 수행한 인덱스를 잠근 후 테이블의 레코드를 잠근 후 테이블 레코드를 잠그는 방식으로 구현돼 있다. 따라서 update나 delete 문장이 실행될 때 테이블에 적절하게 사용할 수 있는 인덱스가 없으면 불필요하게 많은 레코드를 잠근다.
B-Tree 인덱스 사용에 영향을 미치는 요소
인덱스 키 값의 크기
InnoDB 스토리지 엔진은 디스크에 데이터를 저장하는 가장 기본 단위를 페이지 혹은 블록이라고한다. 모든 디스크 I/O 작업에 최소 작업 단위가 된다. 또한 페이지는 InnoDB 버퍼 풀에서 데이터를 버퍼링하는 기본 단위이기도 하다.
기본 페이지의 크기는 innodb_page_size 시스템 변수로 설정가능하며 기본값은 16KB이다.
인덱스 하나가 인덱스 키 값을 저장하는 16바이트 자식 노드 정보를 12바이트에 담아 총 28바이트가 필요하다 하자. 인덱스 페이지 16KB에는 16*1024/(16+12) = 585개가 저장된다. 페이지 하나에 인덱스 585개가 저장된다. 500개의 인덱스를 읽어야한다면 페이지 하나를 읽는 것만으로도 충분하다.
인덱스 키 값이 길어진다는 것은 인덱스 하나의 크기가 커진다는 의미고 페이지 하나에 들어가는 인덱스 개수는 줄어든다. 인덱스 캐시 영역 (InnoDB의 버퍼풀 혹은 MyISAM의 키 캐시)은 메모리 제한이 있기 때문에 캐시할 수 있는 인덱스 수는 줄어들고 검색 성능이 떨어진다.
B-Tree 깊이
인덱스 길이가 커지면 같은 깊이를 가진 트리가 수용할 수 있는 인덱스 개수가 적어지므로 동일한 수를 갖는 두 인덱스를 트리에 담더라도 길이가 더 긴 인덱스가 깊이가 깊어질 수 밖에 없다. 트리 깊이가 깊어지면 읽기 횟수가 더 많이 요구된다.
Cardinality 기수성
인덱스 키가 가질 수 있는 값의 모든 경우의 수 혹은 유니크한 값의 집합에 대한 차수이다. 중복된 값이 많아질 수록 검색 대상이 많아지기 때문에 효율이 떨어진다.
읽어야 하는 레코드 수
전체 레코드 수의 반을 조회해야하는 쿼리가 있다면 인덱스를 사용하지 않고 전체 테이블을 한번 읽고 필터에 따라 거르는 것이 더 빠를 수 있다.
B-Tree 인덱스를 통한 데이터 읽기
1. 인덱스 레인지 스캔
범위를 기반으로하여 처음 시작 범위 값부터 끝나는 범위 값까지 읽는 것을 말한다.
SELECT * FROM employees WHERE first_name BETWEEN 'Ebbe' AND 'Gad';
실제 데이터 파일을 읽어오는 과정은 다음과 같다.
인덱스의 리프 노드에서 검색 조건이 일치하는 건들은 데이터 파일에서 레코드를 읽어오는 과정이 필요하다. 리프 노드에 저장된 레코드 주소로 데이터 파일의 레코드를 읽어오는데, 레코드 한 건 단위로 랜덤 I/O가 한 번씩 일어난다.
인덱스 레인지 스캔은 다음 3단계를 거친다.
1. 인덱스에서 조건에 만족하는 값이 저장된 위치를 찾는다.
2. 1번에서 탐색된 위치로부터 필요한 만큼 인덱스를 차례대로 쭉 읽는다. 이 과정을 인덱스 스캔이라고 한다.
3. 2번에서 읽어 들인 인덱스 키와 레코드 주소를 이용해 레코드가 저장된 페이지를 가져오고 최종 레코드를 읽어온다.
쿼리가 필요로 하는 데이터에 따라 3번 과정은 필요하지 않을 수 도 있는데, 이를 커버링 인덱스라고 한다. 커버링 인덱스로 처리되는 쿼리는 디스크의 레코드를 읽지 않아도 되기 때문에 랜덤 읽기가 상당히 줄어들고 성능은 그만큼 빨라진다.
2. 인덱스 풀 스캔
인덱스의 처음부터 끝까지 모두 읽는 방식을 인덱스 풀 스캔이라고 한다.
조회 컬럼이 인덱스의 첫 번째 컬럼이 아닌 경우 인덱스 풀 스캔이 사용된다. 인덱스는 (A,B,C)에 만들어졌지만 B 컬럼이나 C 컬럼으로 조회할 때 발생한다.
인덱스의 리프 노드를 연결하는 링크드 리스트를 따라서 처음부터 끝까지 스캔하는 방식을 인덱스 풀 스캔이라고한다. 인덱스에 포함된 컬럼만으로 쿼리를 처리할 수 있는 경우 테이블의 레코드를 읽을 필요가 없기 떄문에 테이블 풀 스캔보다 효율적이다.
3. 루스 인덱스 스캔
일반적인 DBMS에서 인덱스 스킵 스캔이라고 불리는 기능이 MySQL에서는 루스 인덱스 스캔이라고 한다.
인덱스 레인지 스캔방식과 유사하게 동작하지만 중간에 필요치 않은 인덱스 키 값은 무시하고 다음으로 넘어가는 형태로 처리한다. GROUP BY 또는 집합 함수 가운데 MAX() 또는 MIN() 함수에 대해 최적화를 하는 경우에 사용된다.
SELECT dept_no MIN(emp_no) FROM dept_emp WHERE dep_no BETWEEN 'd002' AND 'd004' GROUP BY dept_no;
쿼리에 사용된 dept_emp 테이블은 (dept_no, emp_no) 조합으로 인덱스가 생성되어 있다. 따라서 해당 컬럼으로 정렬이 되어 있기 때문에 dept_no 그룹 별로 첫 번째 레코드의 emp_no 값만 읽으면 된다. Where 조건을 만족하는 범위 전체를 다 스캔할 필요가 없다는 것을 옵티마이저는 알고 있기 때문에 조건에 만족하지 않는 레코드는 무시하고 다음으로 넘어간다.
4. 인덱스 스킵 스캔
employees 테이블에 (gender, birth_date) 컬럼을 이용한 인덱스가 있다하자.
ALTER TABLE employees ADD INDEX ix_gender_birthdate (gender, birth_date);
인덱스를 사용하려면 where 조건절에 두 컬럼을 모두 사용해야한다.
// 인덱스를 사용할 수 없음.
SELECT * FROM employees WHERE birth_date>='1965-02-01';
// 인덱스를 사용할 수 있음.
SELECT * FROM employees WHERE gender='M' AND birth_date>='1965-02-01';
MySQL 8.0부터는 gender 컬럼을 건너뛰어서 birth_date 컬럼만으로도 인덱스 검색이 가능하게 해주는 인덱스 스킵 스캔 (Index skip scan) 최적화 기능이 도입됐다. MySQL 8.0 이전 버전에서도 인덱스 스킵 스캔과 비슷한 최적화를 수행하는 루스 인덱스 스캔을 사용하는 경우에만 적용할 수 있었다.
인덱스 스킵 스캔 기능을 사용하지 않는다면, 첫번째 쿼리는 풀 인덱스 스캔을 사용하게된다.
SET optimizer_switch='skip_scan=on';
으로 인덱스 스킵 스캔 기능을 사용하게 되면, 인덱스 스킵 스캔을 사용하게 되고 다음과 같이 수행된다.
결국 다음 두 쿼리를 실행하는 것과 비슷하게 실행된다.
- SELECT gender, birth_date FROM employees WHERE gender='M' AND birth_date>='1965-02-01';
- SELECT gender, birth_date FROM employees WHERE gender='F' AND birth_date>='1965-02-01';
인덱스 스킵 스캔은 MySQL 8.0에 도입되었기 때문에 다음과 같은 단점이 있다.
- WHERE 조건절에 조건이 없는 인덱스의 선행 컬럼의 유니크한 값의 개수가 적어야함
- 쿼리가 인덱스에 존재하는 컬럼만으로 처리 가능해야 함 (커버링 인덱스)
다중 컬럼 인덱스
2개 이상 컬럼으로 구성된 인덱스를 말한다. 인덱스 컬럼의 순서에 따라 정렬의 순서가 결정되므로 인덱스의 순서는 중요하다.
B-Tree 인덱스의 정렬 및 스캔 방향
인덱스를 생성할 때 정렬 규칙에 따라 키 값은 항상 오름차순이거나 내림차순으로 정렬되어 저장된다. 어떤 인덱스가 오름차순으로 정렬되옸다고 항상 오름차순으로만 읽을 수 있는 것은 아니다. 읽는 방향은 옵티마이저가 판단하여 최적화된 실행계획에 따른다.
- 인덱스의 정렬
일반 DBMS에서 인덱스를 생성하는 시점에 구성하는 각 컬럼의 정렬을 오름차순 혹은 내림차순으로 설정할 수 있다.
CREATE INDEX ix_teamname_userscore ON employees (team_name ASC, user_score DESC)
- 인덱스 스캔 방향
SELECT * FROM employees ORDER BY first_name DESC LIMIT 1;
내림차순 정렬된 결과로 첫번째 레코드를 가져오는 쿼리는 오름차순으로 읽을 까 혹은 내림차순으로 읽을 까
옵티마이저는 해당 쿼리를 역순으로 읽어 값을 가져올때 더 빠르다는 것을 앍고 있기 때문에 역순으로 읽는다.
인덱스 생성 시점에 정렬되는 순서가 결정되지만 옵티마이저는 쿼리를 보고 읽는 방향의 순서를 결정하기 때문에 마치 정렬을 한 것처럼 최적화하여 읽는다.
내림차순 인덱스
- 순회 방향에 따른 성능 차이
- 오름차순 인덱스 : 작은 값의 인덱스 키가 트리 왼쪽으로 정렬된 인덱스
- 내림차순 인덱스 : 큰 값의 인덱스 키가 트리 왼쪽으로 정렬된 인덱스
- 인덱스 정순 스캔 : 인덱스 키의 크고 작음에 관계없이 인덱스 리프 노드의 왼쪽 페이지부터 오른쪽으로 스캔
- 인덱스 역순 스캔 : 인덱스 키의 크고 작음에 관계없이 인덱스 리프 노드으 오른쪽 페이지부터 왼쪽으로 스캔
InnoDB 페이지 내에서 레코드의 연결은 정순 스캔에 적합하도록 되어 있다. 페이지 내에서 인덱스 레코드는 단방향으로만 연결된 구조이다.
ORDER BY ... DESC 쿼리가 드물게 발생한다면 굳이 내림차순 인덱스를 생성할 필요는 없다.
SELECT * FROM tab WHERE userid=? ORDER BY score DESC LIMIT 10;
위 쿼리에 대해 두가지 인덱스 모두 적절한 선택이 된다.
- INDEX (userid ASC, score ASC)
- INDEX (userid DESC, score DESC)
하지만 위 쿼리가 빈번하게 조회된다면 오름차순 인덱스보다 내림차순 인덱스가 더 효율적일 수 있다.
B-Tree 인덱스의 가용성과 효율성
쿼리의 WHERE 조건이나 GROUP BY 또는 ORDER BY 절이어떤 경우에 인덱스를 사용할 수 있고 어던 방식으로 사용할 수 있느지 식별할 수 있어야 한다. 그래야 쿼리의 조건을 쵲거화하거나 역으로 쿼리에 맞게 인덱스를 최적으로 생성할 수 있다.
비교 조건의 종류와 효율성
SELECT * FROM dept_emp WHERE dept_no='d002' AND emp_no >= 10114;
이 쿼리를 위해 dept_emp 테이블에 각각 칼럼의 순서만으로 두가지 케이스로 인덱스를 생성했다고 가정하자.
- 케이스 A : INDEX (dept_no, emp_no)
- 케이스 B : INDEX (emp_no, dept_no)
A 케이스는 "dept_no = 'd002' AND emp_no >= 10114"인 레코드를 찾고 그 이후에는 dept_no가 d002가 아닐때까지 쭉 읽기만 하면 된다. B 케이스 의 경우에는 "emp_no >= 10114 AND dept_no='d002'" 인 레코드를 찾고 레코드가 dpet_no='d002'인지 비교하는 과정을 거쳐야한다. B의 경우 dept_no는 비교작업을 줄이는데 아무런 도움을 주지 못하고 단지 쿼리의 조건에 맞는 지 검사하는 용도로만 사용됐다.
A 케이스에서 사용된 조건은 작업 범위를 결정하므로 '작업 범위 결정 조건'이라 하고 B 케이스에서 비교 작업을 줄이지 못하고 거름종이 역할만 하는 조건을 '필터링 조건' 또는 '체크 조건'이라고 표현한다. B 케이스에서 emp_no 칼럼만 작업 범위 결정 조건이고 dept_no는 필터링 조건으로 사용된 것이다.
인덱스의 가용성
B-Tree 인덱스의 특징은 왼쪽 값에 기준에서 오른쪽 값이 정렬되어 있다는 것이다. 컬럼 검색 조건에서 왼쪽 부분의 값을 모르면 인덱스를 사용할 수 없다.
SELECT * FROM employees WHERE first_name LIKE '%mer';
왼쪽 부분이 고정되지 않았기 때문에 정렬 우선순위가 낮은 뒷부분의 값만으로는 왼쪽 기준 정렬 기반의 인덱스를 사용할 수 없다.
SELECT * FROM dept_emp WHERE emp_no>=10144;
인덱스가 (dept_no, emp_no) 칼럼 순서대로 생성돼 있다면 인덱스의 선행 칼럼인 dept_no 조건 없이 emp_no 값으로만 검색하면 인덱스를 효율적으로 사용할 수 없다.
가용성과 효율성 판단
다음의 경우 B-Tree 인덱스 특성상 인덱스를 작업 범위 결정 조건으로 사용할 수 없다.
- NOT-EQUAL로 비교된 경우 ("<>", "NOT IN", "NOT BETWEEN", "IS NOT NULL")
- ... WHERE column <> 'N'
- ... WHERE column NOT IN (10, 11, 12)
- ... WHERE column IS NOT NULL
- LIKE "%??' 형태로 문자열 패턴이 비교된 경우
- ... WHERE column LIKE '%승환'
- ... WHERE column LIKE '_승환'
- ... WHERE column LIKE '%승%'
- 스토어드 함수나 다른 연산자로 인덱스 칼럼이 변형된 후 비교된 경우
- ... WHERE SUBSTRING(column, 1, 1) = 'X'
- ... WHERE DAYOFMONTH(column) = 1
- NOT-DETERMINISTIC 속성의 스토어드 함수가 비교 조건에 사용된 경우
- ... WHERE column = deterministic_function()
- 데이터 타입이 서로 다른 비교(인덱스 칼럼의 타입을 변환해야 비교가 가능한 경우)
- ... WHERE char_column = 10
- 문자열 데이터 타입의 콜레이션이 다른 경우
- ... WHERE utf8_bin_char_column = euckr_bin_char_column
일반 DBMS와 다르게 MySQL에서 NULL을 허용하므로 MySQL에서는 NULL 값 비교도 작업 범위 결정 조건으로 인덱스를 사용한다.
... WHERE column IS NULL ...
다중 컬럼으로 만들어진 인덱스는 어떤 조건에서 사용될 수 있고 어떤 경우에 절대 사용할 수 없는지 살펴보자
INDEX ix_test ( column_1, column_2, ..., column_n )
작업 범위 결정 조건으로 인덱스를 사용하지 못하는 경우
- column_1 칼럼에 대한 조건이 없는 경우
- column_2 칼럼의 비교 조건이 위의 인덱스 사용 불가 조건 중 하나인 경우
작업 범위 결정 조건으로 인덱스를 사용하는 경우
- column_1 ~ column_(i-1) 칼럼까지 동등 비교 형태로 비교
- column_i 컬럼에 대해 다음 연산자 중 하나로 비교
- 동등 비교 ("=" 또는 "IN")
- 크고 작음 비교 (">" 또는 "<")
- LIKE로 좌측 일치 패턴 (LIKE '승환%');
전문 검색 인덱스의 가용성
전문 검색 인덱스를 사용하려면 다음 두 가지 조건을 갖춰야 한다.
- 쿼리 문장이 전문 검색을 위한 문법을 사용
- 테이블이 전문 검색 대상 컬럼에 대해서 전문 인덱스 보유
다음과 같은 테이블의 doc_body 컬럼에 전문 검색 인덱스를 생성했다고 해보자
CREATE TABLE tb_test (
doc_id INT,
doc_body TEXT,
PRIMARY KEY (doc_id),
FULLTEXT KEY fx_docbody (doc_body) WITH PARSER ngram
) ENGINE=InnoDB;
다음과 같은 쿼리를 사용할 경우 전문 검색 인덱스를 이용해 효율적으로 쿼리가 실행된 것이 아니라 테이블을 처음부터 끝까지 읽는 풀 테이블 스캔으로 쿼리를 처리한다.
SELECT * FROM tb_test WHERE doc_body LIKE '%애플%';
전문 검색 인덱스를 사용하려면 다음과 같이 MATCH (...) AGAINST (...) 구문으로 검색 쿼리를작성해야하며 전문 검색 인덱스를 구성하는 컬럼들은 MATCH 절의 괄호 안에 모두 명시돼야 한다.
SELECT * FROM tb_test WHERE MATCH(doc_body) AGAINST('애플' IN BOOLEAN MODE);
함수 기반 인덱스
MySQL 서버는 8.0 버전부터 함수 기반 인덱스를 지원하기 시작했는데, MySQL 서버에서 함수 기반 인덱스를 구현하는 방법은 두 가지로 구분할 수 있다.
- 가상 칼럼을 이용한 인덱스
- 함수를 이용한 인덱스
함수 기반 인덱스는 값을 계산하는 과정의 차이만 있을 뿐 실제 인덱스의 내부적인 구조 및 유지 관리 방법은 B-Tree인덱스와 동일하다.
1. 가상 칼럼을 이용한인덱스
다음과 같이 사용자 정보를 저장하는 테이블이 있다고 가정하자.
CREATE TABLE user (
user_id BIGINT,
first_name VARCHAR(10),
last_name VARCHAR(10),
PRIMARY KEY (user_id)
);
first_name과 last_name을 합쳐서검색해야하는 요건이 생겼다면 이전 버전의 MySQL 서버에서는 full_name이라는 칼럼을 추가하고 모든 레코드에 대해 full_name을 업데이트하는 작업을 거쳐야 했다. 그래야 비로소 full_name 칼럼에 대해 인덱스를 생성할 수 있었다.
하지만 MySQL 8.0 버전부터는 다음과 같이 가상 칼럼을 추가하고 그 가상 칼럼에 인덱스를 생성할 수 있게 됐다.
ALTER TABLE user
ADD full_name VARCHAR(30) AS (CONCAT(first_name,' ',last_name)) VIRTUAL,
ADD INDEX ix_fullname (full_name);
full_name 칼럼에 대한 검색도 새로 만들어진 ix_fullname 인덱스를 이용해 실행 계획이 만들어지는 것을 확인할 수 있다.
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)='Matt Lee';
이 예제를 실행했을 때 옵티마이저가 표시하는 실행 계획이 "ix_fullname" 인덱스를 사용하지 않는 것으로 표시된다면 CONCAT 함수에 사용된 공백 문자 리터럴 때문일 가능성이 높다.
이 경우 다음 3개의 시스템 변수 값을 동일 콜레이션으로 일치시킨 후 다시 테스트를 수행해보자.
- collation_connection
- colation_database
- collation_server
멀티 밸류 인덱스
전문 검색 인덱스를 제외한 모든 인덱스는 레코드 1건이 1개의 인덱스 키 값을 가진다. 인덱스 키와 데이터 레코드는 1:1의 관계를 가진다. 멀티 밸류 인덱스는 하나의 데이터 레코드가 여러 개의 키 값을 가질 수 있는 형태의 인덱스다. 일반적인 DBMS에서 이러한 인덱스는 정규화에 위배되는 형태이다. 하지만 최근 RDBMS들이 JSON 데이터 타입을 지원하기 시작하면서 JSON 배열 타입의 필드에 저장된 원소들에 대한 인덱스 요건이 발생했다.
MySQL 8.0부터 멀티밸류 인덱스에 대한 지원이 되는 JSON 타입의 칼럼을 지원하게 되었다.
다음과 같이 신용 정보 점수를 배열로 JSON 타입 칼럼에 저장하는 테이블을 가정해보자
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, 'Matt', 'Lee', '{"credit_scores":[360, 353, 351]}');
멀티 밸류 인덱스를 활용하기 위해서는 일반적인 조건 방식을 사용하면 안 되고, 반드시 다음 함수들을 이용해서 검색해야 옵티마이저가 인덱스를 활용한 실행 계획을 수립한다.
- MEMBER OF()
- JSON_CONTAINS()
- JSON_OVERLAPS()
SELECT * FROM user WHERE 360 MEMBER OF(credit_info->'$.credit_scores');
EXPLAIN SELECT * FROM user WHERE 360 MEMBER OF (credit_info->'$.credit_scores');
클러스터링 인덱스
MySQL 클러스터링 인덱스는 InnoDB 스토리지 엔진에서만 지원하며 나머지 스토리지 엔진에서는 지원되지는 않는다.
클러스터링 인덱스는 테이블의 프라이머리 키에 대해서만 적용되는 내용이다. 프라이머리 키 값이 비슷한 레코드끼리 묶어서 저장하는것을 클러스터링 인덱스라고 표현한다. 프라이머리 키 값이 변경된다면 레코드의 물리적인 저장 위치가 바뀌어야한다는 것을 의미하기도 한다. 프라이머리 키값으로 클러스터링된 테이블은 프라이머리 키 값 자체에 대한 의존도가 상당히 크기 때문에 신중히 프라이머리 키를 결정해야한다.
클러스터링 인덱스는 프라이머리 키 값에 의해 레코드 저장 위치가 결정되므로 인덱스 알고리즘이라기보다 테이블 레코드의 저장 방식에 가깝다.
vs B-Tree 인덱스
B-Tree 인덱스도 인덱스 키 값으로 이미 정렬되어 저장된다. 일반적인 B-Tree 인덱스를 클러스터링 인덱스라고 부르지 않는데 테이블의 레코드가 프라이머리 키값으로 정렬되어 저장된 경우만 "클러스터링 인덱스" 또는 "클러스터링 테이블" 이라고 한다.
클러스터링 테이블 구조 자체는 B-Tree와 비슷하나 세컨더리 인덱스를 위한 B-Tree의 리프 노드와 달리 그림의 클러스터링 인덱스의 리프 노드에는 레코드의 모든 칼럼이 같이 저장돼 있음을 알 수 있다. 클러스터링 테이블은 자체가 하나의 거대한 인덱스 구조로 관리되는 것이다.
다음과 같이 프라이머리 키 (employees 테이블의 emp_no 컬럼)을 변경하는 문장이 실행되면 클러스터링 테이블의 데이터 레코드에는 어떤 변화가 일어날까?
UPDATE tb_test SET emp_no=100002 WHERE emp_no=100007;
위 그림에서는 emp_no가 100007인 레코드는 3번 페이지에 저장돼 있었다. 하지만 다음 그림에서 emp_no가 100002로 변경되면서 2번 페이지로 이동한 것을 알 수 있다. 실제로 프라이머리 키가 변경될 일은 거의 없지만 클러스터링 테이블에서 프라이머리 키값은 중요하다.
프라이머리 키가 없는 InnoDB 테이블은 어떻게 클러스터링 테이블로 구성될까. 프라이머리 키가 없는 경우에는 InnoDB 스토리지 엔진이 다음 우선순이대로 프라이머리 키를 대체할 칼럼을 선택한다.
1. 프라이머리 키가 있으면 기본적으로 프라이머리 키를 클러스터링 키로 선택
2. NOT NULL 옵션의 유니크 인덱스 중에서 첫 번째 인덱스를 클러스터링 키로 선택
3. 자동으로 유니크한 값을 가지도록 증가되는 칼럼을 내부적으로 추가한 후 클러스터링 키로 선택
InnoDB 스토리지 엔진이 적절한 클러스터링 키 후보를 찾지 못하는 경우 InnoDB 스토리지 엔진이 내부적으로 레코드의 일련번호 칼럼을 생성한다.
세컨더리 인덱스에 미치는 영향
프라이머리 키가 데이터 레코드 저장에 미치는 영향을 알아봤다면 세컨더리 인덱스에 어떤 영향을 미치는 지 보자.
MyISAM이나 MEMORY 테이블 같은 클러스터링되지 않은 테이블은 INSERT될 때 처음 저장된 공간에서 절대 이동하지 않는다. 데이터 레코드가 저장된 주소는 내부적인 레코드 아이디(ROWID) 역할을 한다고 언급했었다. 그리고 프라이머리 키나 세컨더리 인덱스의 각 키는 그 주소(ROWID)를 이용해 실제 데이터 레코드를 찾아온다.
MyISAM이나 MEMORY 테이블에서는 프라이머리 키와 세컨더리 인덱스는 구조적으로 아무런 차이가 없다.
InnoDB 테이블에서 세컨더리 인덱스가 실제 레코드가 저장된 주소를 가지고 있다면 어떻게 될까? 클러스터링 키 값이 변경될 때마다 데이터 레코드의 주소가 변경되고 그대마다 해당 테이블의 모든 인덱스에 저장된 주솟값을 변경해야 할 것이다. 이런 오버헤드를 제거하기 위해 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='Aamer';
MyISAM : ix_firstname 인덱스를 검색해서 레코드의 주소를 확인한 후 레코드의 주소를 이용해 최종 레코드를 가져온다.
InnoDB : ix_firstname 인덱스를 검색해 레코드의 프라이머리 키 값을 확인한 후 프라이머리 키 인덱스를 검색해서 최종 레코드를 가져온다.
클러스터링 인덱스의 장점과 단점
| 장점 | 프라이머리키(클러스터링 키)로 검색할 때 처리 성능이 매우 빠름, 특히 프라이머리 키를 범위 검색하는 경우 매우 빠름 테이블의 모든 세컨더리 인덱스가 프라이머리 키를 가지고 있기 때문에 인덱스만으로 처리될 수 있는 경우가 많음(커버링 인덱스) |
| 단점 | 테이블의 모든 세컨더리 인덱스가 클러스터링 키를 갖기 때문에 클러스터링 키 값의 크기가 클 경우 전체적으로 인덱스의 크기가 커짐 세컨더리 인덱스를 통해 검색할 때 프라이머리 키로 다시 한번 검색해야 하므로 처리 성능이 느림 INESRT할 때 프라이머리 키에 의해 레코드의 저장 위치가 결정되기 때문에 처리 성능이 느림 프라이머리 키를 변경할 때 레코드를 DELETE하고 INSERT하는 작업이 필요하기 때문에 처리 성능이 느림 |
클러스터링 테이블 사용시 주의 사항
클러스터링 인덱스 키의 크기
클러스터링 테이블의 경우 모든 세컨더리 인덱스가 프라이머리 키 값을 포함한다. 그래서 프라이머리 키의 크기가 커지면 세컨더리 인덱스도 자동으로 크기가 커진다.
5개의 세컨더리 인덱스를 가지는 테이블의 프라이머리 키가 10바이트인 경우와 50바이트인 경우를 한번 비교해보자.
| 프라이머리 키 크기 | 레코드당 증가하는 인덱스 크기 | 100만 건 레코드 저장시 증가하는 인덱스 크기 |
| 10바이트 | 10바이트 * 5 = 50바이트 | 50바이트 * 1,000,000 = 47MB |
| 50바이트 | 50바이트 * 5 = 250바이트 | 250바이트 * 1,000,000 = 238MB |
- 프라이머리 키는 AUTO-INCREMENT보다는 업무적인 칼럼으로 생성
프라이머리 키를 이용한 검색은 굉장히 빠르므로 자주 사용되는 업무적인 컬럼을 프라이머리 키로 사용하면 매우 효과적이다.
- 프라이머리 키는 반드시 명시할 것
프라이머리 키가 없는 테이블을 자주 보게 되는데 가능하면 AUTO_INCREMENT 칼럼을 이용해서라도 프라이머리 키는 생성하는 것을 권장한다.
유니크 인덱스
유니크는 인덱스보다는 제약 조건에 가깝다. 테이블이나 인덱스에 같은 값이 2개 이상 저장될 수 없음을 의미하는데, MySQL에서는 인덱스 없이 유니크 제약만 설정할 방법이 없다. 유니크 인덱스에서 NULL도 저장될 수 있는데 NULL은 특정 값이 아니므로 2개 이상 저장될 수 있다. MySQL에서 프라이머리 키는 기본적으로 NULL을 허용하지 않는 유니크 속성이 자동으로 부여된다.
유니크 인덱스와 일반 세컨더리 인덱스의 비교
유니크 인덱스와 유니크하지 않은 일반 세컨더리 인덱스는 사실 인덱스의 구조상 아무런 차이점이 없다.
인덱스 읽기
유니크 하지 않은 세컨더리 인덱스는 중복된 값이 허용되므로 CPU가 읽어야할 컬럼 수가 유니크 인덱스보다 많아서 느린 것이지 유니크 인덱스와 세컨더리 인덱스의 성능 차이는 거의 없다.
인덱스 쓰기
새로운 레코드가 INSERT되거나 인덱스 컬럼의 값이 변경되는 경우 인덱스 쓰기 작업이 필요하다.
유니크 인덱스의 키 값을 쓸 대는 중복된 값이 있는지 없는지 체크하는 과정이 한 단계 더 필요하다. 그래서 유니크하지 않은 세컨더리 인덱스의 쓰기보다 느리다. MySQL에서는 유니크 인덱스에서 중복된 값을 체크할 떄는 읽기 잠금을 사용하고 쓰기를 할 때는 쓰기 잠금을사용하는데 이 과정에서 데드락이 빈번히 발생한다. InnoDB에서는 인덱스 키의 저장을 버퍼링하기 위해 체인지 버퍼가 사용된다.
유니크 인덱스 사용 시 주의사항
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의 유니크 인덱스는 일반 다른 인덱스와 같은 역할을 하므로 중복해서 인덱스를 생성할 필요는 없다. 따라서 위 테이블에서 이미 nick_name이라는 컬럼에 대해서는 유니크 인덱스인 ux_nickname이 있기 때문에 ix_nickname 인덱스는 필요하지 않다.
외래키
MySQL에서 외래키는 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');
INSRET INTO tb_child VALUES (100, 1, 'child-100');
1. 자식 테이블의 변경이 대기하는 경우
| 작업 번호 | 커넥션-1 | 커넥션-2 |
| 1 | BEGIN; | |
| 2 | UPDATE tb_parent SET fd='change-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번 커넥션에서 먼저 트랜잭션을 시작하고 부모(tb_parent) 테이블에서 id가 2인 레코드에 UPDATE를 실행한다. 1번 커넥션이 tb_parent 테이블에서 id가 2인 레코드에 대해 쓰기 잠금을 획득한다.
2번 커넥션에서 자식 테이블(tb_child)의 외래키 칼럼(부모의 키를 참조하는 칼럼)인 pid를 2로 변경하는 쿼리를 실행해보자. 이 쿼리(작업번호 4번)는 부모 테이블의 변경 작업이 완료될 때까지 대기한다.
다시 1번 커넥션에서 ROLLBACK이나 COMMIT으로 트랜잭션을 종료한다.
2번 커넥션의 대기 중이던 작업이 즉시 처리되는 것을 확인할 수 있다. 즉 자식 테이블의 외래 키 칼럼의 변경(INSERT, UPDATE)는 부모 테이블의 확인이 필요한데 이 상태에서 부모 테이블의 해당 레코드가 쓰기 잠금이 걸려 있으면 해당 쓰기 잠금이 해제딜 때까지 기다리게 되는 것이다.
자식 테이블의 외래키(pid)가 아닌 칼럼(tb_child 테이블의 fd 칼럼과 같은)의 변경은 외래키로 인한 잠금 확장이 발생하지 않는다.
2. 부모 테이블의 변경 작업이 대기하는 경우
| 작업 번호 | 커넥션-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 테이블에서 id가 1인 레코드를 삭제하는 경우 이 쿼리는 tb_child 테이블의 레코드에 대한 쓰기 잠금이 해제될 때까지 기다려야 한다.
이는 자식 테이블이 생성될 때 정의된 외래키 특성(ON DELETE CASCADE) 때문에 부모 레코드가 삭제되면 자식 레코드도 동시에 삭제되는 식으로 작동하기 때문이다.
외래키를 생성하면 자식 테이블에 레코드가 추가되는 경우 해당 참조키가 부모 테이블에 있는지 확인한다. 물리적인 외래키의 고려 사항은 이러한 체크 작업이 아니라 체크를 위해 연관 테이블에 읽기 잠금을 걸어야한다. 또한 이렇게 잠금이 다른 테이블로 확장되면 그만큼 전체적으로 쿼리의 동시 처리에 영향을 미친다.
'DB > MySQL' 카테고리의 다른 글
| MySQL 옵티마이저와 힌트 (0) | 2022.05.08 |
|---|---|
| MySQL MVCC - 멀티 버전 동시성 제어 (0) | 2022.05.01 |
| MySQL 트랜잭션과 잠금 (0) | 2022.01.02 |
| MySQL InnoDB 스토리지 엔진 아키텍처 (0) | 2021.12.30 |
| MySQL 아키텍쳐 (0) | 2021.12.29 |