MySQL 인덱스의 원리와 구조 이해하기

Posted May 25, 2024 Updated Feb 28, 2025

By green 42 min read

Real MySQL 8.0 8장 “인덱스”를 학습한 내용입니다.

B-Tree 인덱스

최사위에 하나의 루트노드가 존재하고 그 하위에는 브랜치 노드, 가장 말단에는 리프노드가 붙어 있다.

데이터베이스에서 인덱스와 실제 데이터가 저장된 데이터는 따로 관리되는데 인덱스 리프 노드에 항상 실제 데이터 레코드를 찾아가기 위한 주솟값이 저장된다.

인덱스의 키 값은 정렬되어 있지만, 리프노드에서 찾아가는 데이터 파일의 레코드는 정렬되어 있지 않다.

스토리지 엔진의 차이점

MyISAM

레코드 주소는 MyISAM 테이블의 생성 옵션에 따라 레코드가 테이블에 INSERT된 순번이거나 데이터 파일 내 위치(offset)이다. 세컨더리 인덱스가 물리적인 주소를 가진다.

InnoDB

프라이머리 키가 RowID의 역할을 한다. 프라이머리 키를 주소처럼 사용하기 때문에 논리적인 주소를 가진다라고 말하며 인덱스에 저장돼 있는 프라이머리 키 값을 이용해 프라이머리 키 인덱스를 한 번 더 검색한다. 그 후 프라이머리 키 인덱스의 리프 페이지에 저장되어 있는 레코드를 읽는다.

B-Tree 인덱스 키 추가 및 삭제

인덱스 키 추가

B-Tree에 새로운 키 값을 저장하기 위해서는 먼저 적절한 삽입 위치를 탐색해야한다. 저장 위치가 결정되면 레코드의 키 값과 대상 레코드의 주소 정보를 B-Tree의 리프 노드에 저장한다. 만약, 리프 노드가 꽉 차서 더 이상 저장 할 수 없을 때는 리프 노드가 split되어야 하는데 상위 브랜치 노드까지 작업이 필요하다. 따라서 새로운 키를 쓰는 작업은 비용이 많이 든다.

대략적인 계산은 레코드를 추가하는 작업 비용을 1로 계산했을 때 인덱스에 키를 추가하는 작업을 1.5로 예측한다. 테이블에 인덱스가 없다면 작업비용은 1이고, 인덱스가 3개 있다면 1.5*3 + 1 = 5.5 정도로 비용이 예측된다. 여기서 대부분이 디스크로부터 인덱스 페이지를 읽고 쓰기를 하는 시간이다.

인덱스 키 삭제

키 값이 저장된 B-Tree의 리프노드를 찾아 삭제 마크만 한다. 이 키 공간은 방치 또는 재활용이 가능하다.

인덱스 키 변경

B-tree의 키 변경은 키를 삭제한 후 다시 새로운 키 값을 추가하는 방식이다.

인덱스 키 검색

B-Tree 인덱스를 이용한 검색은 100%일치나 값 앞부분만 일치하는 경우에만 사용이 가능하다. 부등호 비교 조건에서도 인덱스를 활용할 수 있지만, 인덱스를 구성하는 키 값의 뒷 부분만 검색하는 용도로는 인덱스를 사용할 수 없다.

가장 중요한 점은 인덱스의 키 값에 변형이 가해지고 비교하는 경우 절대 B-Tree의 빠른 검색 기능을 사용할 수 없다.

InnoDB에서는 인덱스가 더 특별한 의미를 가지고 있는데 InnoDB 테이블에서 지원하는 레코드 잠금이나 넥스트 키락(갭락)이 검색을 수행한 인덱스를 잠근 후 테이블의 레코드를 잠그는 방식으로 구현되어 있다. 즉, update나 delete문장이 실행 될 때 테이블에 적절히 사용할 인덱스가 없으면 불필요하게 많은 레코드를 잠근다.

B-Tree 인덱스 사용에 영향을 미치는 요소

인덱스를 구성하는 컬럼의 크기, 레코드 건수, 유니크한 인덱스 키 값의 개수 등에 의해 검색이나 변경 작업의 성능이 영향 받는다

인덱스 키 값의 크기

InnoDB 스토리지 엔진은 디스크에 저장하는 기본 단위를 페이지 또는 블록이라고 한다. 이 단위는 모든 디스크 읽기 및 쓰기 작업의 최소 단위이다. 이 페이지는 InnoDB 스토리지 엔진의 버퍼 풀에서 데이터를 버퍼링하는 기본 단위이기도 하다. 즉 인덱스는 페이지 단위로 관리된다.

B-Tree의 자식 노드의 개수는 인덱스 페이지 크기와 키 값의 크기에 따라 결정된다. 이 페이지 크기는 innodb_page_size 시스템 변수를 이용해 4KB ~ 64KB 사이의 값을 선택할 수 있다. 기본 값은 16KB이다.

16KB짜리 하나의 인덱스 페이지에는 16* 1024의 공간이 있고 인덱스키가 16바이트, 자식 노드의 주소 영역이 평균 12바이트로 가정한다면 16*1024 / (16+12) = 585개의 키를 저장할 수 있다.

인덱스 키 값이 커질 경우 한 페이지에서 인덱스 개수가 500여개에서 300여개로 줄어드니 디스크로부터 읽어야 하는 횟수가 늘어나 속도가 느려지게 된다. 또한, 인덱스 키 값이 길어진다는 것은 전체적인 인덱스의 크기가 커지는 것을 의미한다.

B-Tree 깊이

B-Tree 깊이가 3인 경우 키 값이 16바이트면 최대 2억개 키를 담을 수 있다. 만약 키 값이 32바이트로 늘어나면 5천만개로 줄어든다. 인덱스 키 값의 크기가 커지면 하나의 인덱스 페이지가 담을 수 있는 키 값의 개수가 적어지고, 같은 레코드 건수라도 B-Tree의 depth가 깊어져 디스크 읽기가 더 많이 필요해질 수 있다.

선택도(기수성)

인덱스에서 선택도(Selectivity) 또는 기수성(Cardinality)는 거의 같은 의미로 사용되며 모든 인덱스 키 값 가운데 유니크한 값의 수를 의미한다. 전체 인덱스 키 값이 100개인데 그 중 유니크한 값이 10개면 기수성은 10이다.

인덱스 키 값 가운데 중복된 값이 많아질 수록 기수성과 선택도는 낮아진다. 반대로 선택도가 높을 수록 검색 대상이 줄어 속도가 빨라진다

읽어야 하는 레코드 건수

인덱스를 통해 테이블 레코드를 읽는 것은 바로 테이블의 레코드를 읽는 것보다 비용이 높다. 보통 인덱스를 통해 레코드 1건 읽는 것이 직접 레코드를 1건 읽는거보다 4~5배 많은 비용이 드는 작업으로 예측한다.

인덱스를 통해 읽어야 할 레코드 개수(옵티마이저가 판단한 예상 건수)가 전체 테이블 레코드의 20~25%를 넘어서면 인덱스를 이용하지 않고 테이블을 모두 읽고 필요한 레코드만 필터링 하는 것이 효율적이다.

100만건 레코드 가운데 50만 건을 읽어야 한다면 인덱스를 활용하는 의미가 없어진다. 물론 이런 작업은 MySQL 옵티마이저가 알아서 힌트를 무시하고 테이블을 직접 읽는 방식으로 알아서 처리해준다.

B-Tree 인덱스를 통한 데이터 읽기

인덱스 레인지 스캔

검색해야 할 인덱스의 범위가 결정 됐을 때 사용하는 방식이다.

루트 노드에서부터 비교를 시작해서 브랜치 노드를 거쳐 최종적으로 리프 노드까지 도달해야 필요한 레코드의 시작 지점을 찾을 수 있다.시작 지점을 찾은 뒤에는 조건에 맞는 리프 노드의 레코들르 순서대로 읽는다.

레인지 스캔은 별도의 정렬 과정 없이 인덱스를 구성하는 컬럼의 오름차순 또는 내림차순 정렬된 상태로 레코드를 가져온다.

추가적으로 중요한 부분은 인덱스의 리프 노드에서 검색 조건에 일치하는 건들을 데이터 파일에서 레코드로 읽어 오는 과정이 필요하다. 리프 노드에 저장된 레코드 주소로 데이터 파일의 레코드를 읽어올 때 레코드 한 건 단위로 랜덤 I/O 가 일어난다. 즉 레코드 10개가 검색 조건에 일치했다면 데이터 레코드를 읽기 위해 랜덤 I/O가 최대 10번 필요하다.

다르게 표현하면 인덱스를 통해 데이터 레코드를 읽는 것은 비용이 많이 드는 편이고, 인덱스를 통해 읽어야 할 데이터 레코드가 20~25%를 넘는다면 인덱스를 활용하지 않고 테이블의 데이터를 직접 읽어들이는 게 더 비용이 적을 수 있다.

인덱스 풀 스캔

인덱스 레인지 스캔과 다른점은 인덱스 풀 스캔은 리프 노드를 연결하는 링크드 리스트를 따라서 처음부터 끝까지 하는 방식이다.

대표적이 사용 예는 쿼리의 조건절에 사용된 컬럼이 인덱스의 첫 번째 컬럼이 아닌 경우이다.

ex) 인덱스는 A,B,C이지만 조건절 컬럼에는 B 또는 C로 검색

쿼리가 인덱스에 명시된 컬럼만으로 조건을 처리 할 수 있는 경우 이 방식이 사용되고, 일반적으로 인덱스의 크기는 테이블의 크기보다 작기 때문에 직접 테이블을 읽을 때보다는 효율적이다.

루스 인덱스 스캔

루스 인덱스 스캔은 인덱스 레인지 스캔과 비슷하게 동작하나 중간에 필요치 않은 인덱스 키 값은 스킵하고 다음으로 넘어가는 형태로 처리한다. 일반적으로 GROUP BY 또는 집합 함수 MAX 또는 MIN 함수를 최적화 하는 경우에 사용된다.

  
SELECT detp_no, MIN(emp_no)
FROM dept_emp
WHERE dep_no BETWEEN 'd002' AND 'd004'
GROUP BY dept_no;

위 쿼리처럼 dept_no와 emp_no 라는 두 개의 컬럼으로 인덱스가 생성되어 있다고 가정한다.

인덱스는 (dept, emp_no) 조합으로 정렬 되어 있어 dept_no 그룹 별로 첫 번째 레코드의 emp_no 만 읽으면 된다. 즉, 인덱스에서 where 조건을 만족하는 범위를 다 확인할 필요가 없다는 것을 옵티마이저가 알고 있기 때문에 스킵하고 다음 레코드로 이동한다.

인덱스 스킵 스캔

인덱스의 핵심은 값이 정렬되어 있다는 것이다. 따라서 인덱스를 구성하는 컬럼의 순서가 매우 중요하다.

성별, 생년월일 순서로 인덱스가 설정 되어 있다고 할 때 MySQL 8.0 이전에는 생년월일만 조건에 활용되면 인덱스를 전혀 사용하지 못하였다. 성별 조건 AND 생년월일 조건으로 조회해야 인덱스를 사용할 수 있었다.

하지만 MySQL 8.0부터는 옵티마이저가 성별 컬럼을 건너뛰고 생년월일 컬럼만으로도 인덱스 검색이 가능해주는 인덱스 스킵 스캔 최적화 기능이 도입되었다.

skip_scan을 off하면 실행계획에서 type이 index 으로 인덱스 풀스캔으로 처리하는데 on으로 변경하면 type이 range 로 처리한다. MySQL 옵티마이저는 우선 성별 컬럼에서 유니크한 값을 모두 조회하고 주어진 쿼리에 성별 컬럼의 조건을 추가해서 쿼리를 다시 실행하는 형태로 처리 할 것이다.

8.0버전에서 새로 도입된 기능이기 때문에 아래와 같은 단점은 아직 남아있다.

WHERE 조건절에 조건이 없는 인덱스의 선행 컬럼의 유니크한 값의 개수가 적어야 한다.
쿼리가 인덱스에 존재하는 컬럼만으로 처리할 수 있어야 한다(커버링 인덱스)

다중 컬럼 인덱스

두 개 이상의 컬럼으로 구성된 인덱스를 뜻하며 Concatenated Index 라고 부르기도 한다.

정렬 함수 처럼 두 번째 컬럼은 첫 번째 컬럼의 정렬 안에서 정렬이 된다. 이와 같은 이유로 인덱스의 컬럼 순서가 중요하다.

B-Tree 인덱스의 정렬 및 스캔 방향

인덱스를 생성할 때 설정한 정렬 규칙에 따라 인덱스의 키 값이 정렬된다. 스캔의 경우엔 인덱스 정렬의 반대 방향으로도 읽어 나갈 수 있다. 읽는 방향은 옵티마이저의 실행계획에 따라 결정된다.

이름의 오름 차순으로 인덱스키가 정렬된 상태에서 다음과 같은 쿼리를 실행한다고 가정해보자

  
SELECT *
FROM employees
ORDER BY first_name DESC
LIMIT 1

옵티마이저는 인덱스를 역순으로 접군해 첫 번째 레코드만 읽는다.

내림차순 인덱스

InnoDB에서 인덱스 역순 스캔이 인덱스 정순 스캔에 비해 느리다. 그 이유는 아래와 같다.

페이지 잠금이 인덱스 정순 스캔에 적합하다
페이지 내에서 인덱스 레코드가 연결된 구조이다.

B-Tree 인덱스의 가용성과 효율성

비교 조건의 종류와 효율성

다중 컬럼 인덱스에서 각 컬럼의 순서와 그 컬럼에 사용한 조건이 동등 비교(=)인지 아니면 범위 조건( <, > )에 따라 각 인텍스 컬럼의 활용 형태가 달라진다.

인덱스를 통해 읽은 레코드가 나머지 조건에 부합한지 확인하는 작업을 필터링이라고 하는데 동등 비교보다 범위 조건이 먼저 작성되면 필터링의 횟수가 늘어난다.

즉 비교의 순서에 따라 인덱스가 비교 작업의 범위를 좁히는데 도움이 안되고 쿼리 조건에 부합하는지 필터링하는 용도로만 사용될 수 도 있다.

작업 범위를 결정하는 조건은 많을수록 쿼리 처리 성능을 높이지만 체크 조건은 많다고 해서 쿼리의 성능을 높이지 못한다. 오히려 쿼리 실행을 느리게 할 수 있다.

인덱스 가용성

B-Tree 인덱스의 특징은 왼쪽 값에 기준해서 오른쪽 값이 정렬돼 있다는 것이고, 다중 컬럼 인덱스의 컬럼에 대해서도 적용된다.

즉, 저장된 값의 왼쪽부터 한 글자씩 비교하며 레코드를 찾아야하는데 왼쪽 부분이 고정되어 있지 않는 LIKE '키워드' 는 B-Tree에서는 인덱스 효과를 얻을 수 없다.

가용성과 효율성 판단

아래 조건에서는 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 ‘keyword’
- WHERE column like ‘_keyword’
- WHERE column like ‘%keyword%’
스토어드 함수나 다른 연산자로 인덱스 컬럼이 변형된 경우
- 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

MySQL에서는 NULL 값도 인덱스에 저장되기 때문에 IS NULL 조건도 작업 범위 결정 조건으로 인덱스를 사용한다.

다중 컬럼에서 인덱스 INDEX ix_test ( column_1, column_2, column_3 … , column_n ) 가 있다고 가정하자.

작업 범위 결정 조건으로 인덱스를 사용하는 경우는 아래와 같다.

column_1 ~ column_(i-1) 컬럼까지 동등 비교 형태 ( = 또는 IN) 으로 비교
column_1 컬럼에 대해 다음 연산자 중 하나로 비교
- 동등 ( = 또는 IN )
- 크다 작다 ( > 또는 < )
- LIKE로 좌측 일치 패턴 ( 키워드% )

반대로 작업 범위 결정 조건으로 인덱스를 사용하지 못하는 경우는 아래와 같다

column_1 컬럼에 대한 조건이 없는 경우
column_1 컬럼의 비교조건이 위 인덱스 사용 불가 조건 중 하나인 경우

작업 범위 결정 조건이 인덱스를 사용하는 쿼리는 column_1부터 column_i까지 작업 범위 결정 조건으로 사용되고, column_(i+1)부터 column_n 까지의 조건은 체크 조건으로 사용된다.

전문 검색 인덱스

전문검색 인덱스는 문서 전체에 대한 분석과 검색을 위한 인덱싱 알고리즘을 뜻한다. 문서의 키워드를 인덱싱하는 기법에 따라 크게 단어의 어근 분석과 n-gram 분석 알고리즘으로 구분한다.

어근 분석 알고리즘

MySQL 서버의 전문 검색 인덱스는 아래 두 가지 과정을 거쳐서 색인 작업을 수행한다.

불용어 (stop word) 처리
어근 분석 (stemming)

불용어 처리는 검색에서 가치가 없는 단어를 필터링하여 제거하는 것을 뜻한다.

어근 분석은 검색어로 선정된 단어의 뿌리인 원형을 찾는 작업이다. (오픈소스 MeCab 사용)

n-gram 알고리즘

단순히 키워드를 검색해내기 위한 인덱싱 알고리즘이다. 형태소 분석보다 알고리즘이 단순하고 국가별 언어에 대한 이해와 준비 작업이 필요 없지만, 인덱스의 크기는 상당히 크다.

n-gram에서 n은 인덱싱할 키워드의 최소 글자 수를 의미하고 일반적으로 2글자 단위로 키워드드를 쪼개서 인덱싱하는 2-gram 방식이 많이 사용된다.

MySQL 서버는 구분된 토큰을 단순한 B-Tree에 저장하고 더 빠르기 위해 2단계 인덱싱과 같은 방법도 사용이 가능하다.

불용어 변경 및 삭제

불용어 처리는 사용자에게 도움이 될 수 있지만 혼란을 줄 수 도 있다. 따라서 내장된 불용어 대신 사용자가 직접 불용어를 등록하는 방법을 권장한다.

불용어 처리를 무시하는 방법은 두 가지이다.

스토리지 엔진에 관계없이 MySQL 서버의 모든 전문 검색 인덱스에 대해 불용어를 완전히 제거한다
InnoDB 스토리지 엔진을 사용하는 테이블의 전문 검색 인덱스에 대해서만 불용어 처리를 무시한다.

전문 검색 인덱스의 가용성

전문 검색 인덱스를 사용하려면 반드시 아래 두 가지 조건을 갖춰야 한다

쿼리 문장이 전문 검색을 위한 문법(MATCH … AGAINST) 을 사용해야한다.
테이블이 전문 검색 대상 컬럼에 대해 전문 인덱스를 보유해야한다.

  
SELECT * FROM tb_test
WHERE MATCH(doc_body) AGAINST('애플' IN BOOLEAN MODE);

함수 기반 인덱스

컬럼의 값을 변형해 만들어진 값에 대해 인덱스를 만들고 싶을 때 함수 기반의 인덱스를 사용한다. MySQL 서버는 8.0 부터 지원하기 시작하였다.

구현 방법은 아래 두 가지로 구분이 가능하다.

가상 컬럼을 이용한 인덱스
함수를 이용한 인덱스

인덱싱할 값을 계산하는 과정만 차이가 있고, 실제 인덱스의 내부적인 구조 및 유지관리 방법은 B-Tree 인덱스와 동일하다.

가상 컬럼을 이용한 인덱스

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

first_name과 last_name을 합친 가상 컬럼에 대한 인덱스를 추가할 수 있다.

  
ALTER TABLET user
ADD full_name VARCHAR(3) AS(CONCAT(first_name, ' ', last_name)) VIRTUAL,
ADD INDEX ix_fullname(full_name);

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

함수를 이용한 인덱스

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 조건절에 사용된 표현식이 다르다면 옵티마이저는 다른 표현식으로 간주하여 인덱스를 활용하지 못한다.

멀티 밸류 인덱스

하나의 데이터 레코드가 여러 개의 키 값을 가질 수 있는 형태다. JSON 데이터 타입을 지원하기 시작하면서 JSON 배열 타입의 필드에 저장된 원소들에 대한 인덱스 요건이 많이 발생하여 탄생하게 되었다.

아래와 같은 함수들을 이용해서 검색해야 옵티마이저가 인덱스를 활용한 실행계획을 수립한다.

MEMBER OF()
JSON_CONTAINS()
JSON_OVERLAPS()

클러스터링 인덱스

클러스터링 인덱스는 테이블의 프라이머리 키에 대해서만 적용된다. 프라이머리 키 값이 비슷한 레코드끼리 묶어서 저장하는 것을 뜻한다. 중요한 부분은 프라이머리 키 값에 의해 레코드의 저장위치가 결정되는 것이다. 즉, 프라이머리 키 값이 변경되면 레코드의 물리적 저장 위치가 변경 된다.

이러한 특성때문에 테이블 레코드 저장 방식이라고 볼 수 있고, 클러스터링 인덱스와 클러스터링 테이블은 동의어로 사용된다. 이런 테이블은 당연히 프라이머리 키 기반 검색이 매우 빠르다. 하지만 레코드의 저장이나 프라이머리 키의 변경이 상대적으로 느리다.

프라이머리 키가 없는 InnoDB 테이블은 InnoDB 스토리지 엔진이 다음 우선순위대로 프라이머리 키를 대체할 컬럼을 선택한다

프라이머리 키가 있으면 디폴트로 프라이머리 키를 클러스터링 키로 선택
NOT NULL 옵션의 유니크 인덱스 중 첫 번째 인덱스를 클러스터링 키로 선택
자동으로 유니크한 값을 가지도록 증가되는 컬럼을 내부적으로 추가하고 클러스터링 키로 선택

3번으로 추가된 프라이머리 키는 사용자에게 노출되지 않고 쿼리에 사용할 수도 없다. 즉 아무 의미 없는 값으로 클러스터링 되고 전혀 이용하지 못한다. 그래서 가능하다면 프라이머리 키를 명시적으로 생성하는 것이 좋다.

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

클러스터링 키 값이 변경 될 때 마다 데이터 레코드의 주소가 변경되고, 해당 테이블의 모든 인덱스가 저장된 주솟값을 변경해야 하는데 이런 오버헤드를 제거하기 위해 InnoDB 테이블의 모든 세컨더리 인덱스는 해당 레코드가 저장된 주소가 아닌 프라이머리 키 값을 저장하도록 되어 있다.

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

장점

프라이머리 키로 검색 시 매우 빠르다
테이블의 모든 세컨더리 인덱스가 프라이머리 키를 가지고 있기 때문에 인덱스만으로 처리될 수 있는 경우가 많다(커버링 인덱스)

단점

테이블의 모든 세컨더리 인덱스가 클러스터링 키를 갖기 때문에 클러스터링 키 값이 크면 전체 인덱스의 크기가 커진다
세컨더리 인덱스를 통해 검색할 때 프라이머리 키로 다시 검색해야하기 때문에 처리 성능이 느리다

일반적으로 웹 서비스와 같은 온라인 트랜잭션 환경에서는 쓰기, 읽기 비율이 2:8 정도이기 때문에 빠른 읽기를 유지하는 것이 중요하다.

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

클러스터링 인덱스의 크기
프라이머리 키는 AUTO-INCREMENT보다는 가능한 경우 업무적인 컬럼으로 생성한다.
프라이머리 키는 반드시 명시하는 것
- ROW 기반의 복제나 InnoDB Cluster에서는 모든 테이블이 프라이머리 키를 가져야만 하는 정상적인 복제 성능을 보장하기도 한다
AUTO-INCREMENT 칼럼을 인조 식별자로 사용하는 경우

유니크 인덱스

유니크는 인덱스라기보다 제약 조건에 가깝다.

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

인덱스의 구조상 아무런 차이가 없지만 읽기와 성능 관점에서 다른점이 있다.

인덱스 읽기

유니크 인덱스는 사실 빠르지 않다. 유니크 하지 않는 세컨더리 인덱스에서는 레코드를 한 건 더 읽어야 하지만 디스크 읽기가 아니라 CPU에서 컬럼 값을 비교하는 작업으로 성능상 거의 영향이 없다. 다만 유니크하지 않는 세컨더리 인덱스는 중복된 값이 허용되기 때문에 읽어야 할 레코드가 많아 느릴 수 있다.

즉, 읽어야 할 레코드 건수가 같다면 성능상 차이는 작다.

인덱스 쓰기

새로운 레코드가 추가되거나 인덱스 컬럼의 값이 변경되면 인덱스 쓰기 작업이 필요하다.

이때 유니크 인덱스의 키 값을 쓸 때는 중복된 값이 있는지 체크하는 과정이 추가적으로 필요하다. 따라서 유니크 하지 않는 세컨더리 인덱스의 쓰기보다 더 느리다.

일반 세컨더리 인덱스

InnoDB 스토리지 엔진에서는 인덱스의 키 저장을 버퍼링하기 위해 체인지 버퍼가 사용된다.

유니크 인덱스

MySQL에서는 유니크 인덱스에서 중복된 값을 체크할 때 읽기 잠금을 사용하고 쓰기는 쓰기 잠금을 사용하는데 이 과정에서 데드락이 아주 빈번히 발생한다.

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

유니크 인덱스 사용

하나의 테이블에서 같은 컬럼에 유니크 인덱스와 일반 인덱스를 중복해서 생성하는 것은 불필요하다.

똑같은 컬럼에 대해 프라이머리 키와 유니크 인덱스를 동일하게 생성한 경우도 불필요하다.

유일성이 보장되어야 하는 컬럼에 대해서는 유니크 인덱스를 생성하되 꼭 필요하지 않다면 유니크 인덱스보다 유니크하지 않는 세컨더리 인덱스를 생성하는 방법을 고려해보자.

외래키

MySQL에서 외래키는 InnoDB 스토리지 엔진에서만 생성할 수 있으며, 외래키 제약이 설정되면 자동으로 연관되는 테이블의 컬럼에 인덱스까지 생성된다. 외래키가 제거 되지 않으면 자동으로 생성된 인덱스는 삭제할 수 없다.

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

테이블의 변경이 발생하는 경우에만 잠금 경합이 발생한다
외래키와 연관되지 않는 변경은 최대한 잠금 경합을 발생시키지 않는다

database

This post is licensed under CC BY 4.0 by the author.