[MySQL] 인덱스

kwang-sub·2024년 5월 7일
mysql

MySQL 8.0

목록 보기
9/14

인덱스

인덱스를 설명할 때 자주 사용되는 표현으로 책의 목차를 이용한다. 목차를 인덱스를 나타내고 내용은 데이터 파일을 나타낸다. 인덱스는 키와 값이 쌍을 이루는데 키는 인덱스 컬럼의 값이고 값은 레코드에 주소를 가지고 있다.

인덱스에서 중요한 점은 컬럼의 값을 주어진 순서로 미리 정렬해서 보관한다는 것이다. 이는 프로그래밍 언어에서 사용되는 SortedList와 같은 자료구조이다.
SortedList는 데이터가 추가, 수정, 삭제되는 경우 재정렬해야하기 때문에 과정이 복잡하고 느리지만 이미 정렬되어 있기에 조회는 빠르게 처리된다.

랜덤 I/O와 순차 I/O

갑자기 인덱스에서 입출력에 대한 설명이 왜 나올까?
데이터는 결국 저장 장치를 통해 읽고 쓰기 때문에 저장 장치가 중요하고 최근에는 SSD가 많이 사용되고 있다.
SSD는 HDD와 비교했을때 순차 I/O 작업을 할 경우 비슷하거나 조금 빠른 수준이지만 랜덤 I/O 작업에 경우 압도적으로 성능상 우위에 있다.
그리고 대부분의 DBMS에서는 랜덤 I/O를 통한 데이터 작업이 대부분이므로 데이터베이스 서버는 SSD를 이용하는 게 성능상 훨씬 유리하다.

알아야할 점!
1. 같은 장치에서 순차 I/O와 랜덤 I/O를 비교하면 순차 I/O가 상대적으로 빠르다.(SSD도 마찬가지)
2. 쿼리튜닝으로 랜덤 I/O를 순차 I/O로 바꾸기는 어려우며 쿼리 튜닝은 랜덤 I/O를 최소한에 데이터에 접근하도록 하는것이다.

인덱스의 종류

인덱스는 역할별로 프라이머리 키와 보조 키(세컨더리 인덱스)로 구분할 수 있으며 저장 방식(알고리즘), 중복 값의 허용 여부에 따라서도 구분하기도 한다.

B-Tree 인덱스

데이터베이스의 인덱싱 알고리즘 중 가장 먼저 또한 일반적으로 사용되는 알고리즘으로 현재도 가장 많이 사용한다.
B-Tree에 B는 바이너리(이진) 트리가 아니라 Balanced를 의미하며 구조는 아래와 같다.

노드명특징
루트노드최상위에 1개만 존재하며 다른 노드로의 경로를 제공
브랜치노드중간에 위치하며 루트 노드를 제외한 모든 노드로 인덱스를 검색하는데 사용
리프노드최하위에 위치하며 실제 데이터 레코드의 주솟값을 저장

인덱스 추가
B-Tree에서 인덱스의 추가는 비용이 많이드는 작업으로 스토리지 엔진에 따라 추가되는 키 값이 즉시 적용될 수도 있고 아닐 수도 있다. 저장될 위치가 정해지면 리프노드에 저장하게 된다.
이때 리프노드의 공간이 있다면 꽉 차있는 경우 분리가 필요한데 분리가 되면서 상위 노드인 브랜치 노드도 작업이 필요하므로 이 작업은 많은 비용을 소모하게 된다.

MyISAM, MEMORY, InnoDB의 경우 프라이머리 키나 유니크 인덱스의 경우 중복 체크가 필요하기에 즉시 B-Tree에 즉시 추가하고 그 외의 InnoDB는 지연시켜 나중에 추가하기도 한다.

인덱스 삭제
B-Tree에 키 값 삭제는 해당 리프노드를 찾아 삭제 마킹 작업만 하면 이후에 추가 시 재사용된다.

인덱스 변경
리프 노드의 위치는 인덱스의 키 값에 따라 변경된다. 따라서 키 값이 변경되면 단순히 키 값을 변경하는게 아닌 기존 키를 삭제하고 새롭게 추가하는 방식으로 동작한다.

인덱스 검색
B-Tree의 루트 노드부터 리프 노드까지 이동하면서 비교 작업을 하는데 이를 트리 탐색이라고 한다. 트리 탐색은 조회뿐만 아니라 수정, 삭제를 위해 대상을 검색할 때도 사용된다.
인덱스 비교 작업은 일치 또는 값의 앞부분만 일치, 부등호를 이용한 사용 할 수 있다.
다만 뒷부분 일치, 인덱스 값에 변형을 한 값을 이용할 경우 인덱스 트리 탐색을 사용할 수 없다.

주의할 점!
InnoDB 테이블은 레코드 잠금, 넥스트 키락 검색을 수행할 때 사용된 인덱스를 잠근 후 레코드를 잠근다. 따라서 적절한 인덱스를 사용하지 않은 경우 많은 레코드가 잠길 수 있으며 간혹 모든 레코드가 잠길 수 있으니 인덱스 설계가 성능에 엄청 큰 영향을 끼친다.

B-Tree 인덱스 영향 끼치는 요소

1. 인덱스 키 값의 크기
데이터 페이지 크기 / 인덱스 키 영역 크기 + 인덱스 주소영역 크기 해당 공식으로 하나의 인덱스 페이지가 저장할 수 있는 인덱스 키의 갯수를 구할 수 있다.
인덱스 키 값이 커지면 한 페이지당 저장할 수 있는 인덱스 키의 갯수가 줄어들 것이다.

그렇다면 버퍼 풀에 인덱스의 크기가 다르지만 같은 레코드 갯수를 가져온다면 인덱스가 큰 경우 데이터 페이지를 가져올 때 더 많은 페이지를 가져와야하며 메모리에 적재할 수 있는 레코드 총 갯수는 줄어들게 된다.

따라서 인덱스의 크기를 최소화하는 것은 성능상 우위를 가져갈 수 있다.

2. B-Tree 깊이
위의 B-Tree에 이미지를 보면 3단계로 나누어져 있다. 하지만 위는 역할로 나누어진 것일뿐 데이터의 크기 또는 다른 상황에 따라서 브랜치 노드가 브랜치 노드를 가리키는 즉 깊이가 3이상으로 깊어질 수 있다.

이러한 경우도 더 많은 브랜치 노드를 따라가야하기 때문에 인덱스의 크기를 작게 유지하는 것이 중요하다를 다시 한번 강조한다.

3. 선택도(기수성)
선택도란 인덱스 키 값 가운데 유니크한 값의 수를 말하며 중복 값이 많을수록 선택도는 높아지고 적을수록 낮아진다. 인덱스가 생성되면 통계정보가 생성되는데 이 중에 유니크한 값의 수 즉 기수성도 통계 데이터로 제공된다. 따라서 인덱스로 생성된 컬럼은 유니크 할수록 검색 성능이 올라간다.

결론은 인덱스의 선택도가 낮아지면 대상이 되는 데이터 갯수가 적어지기 때문에 성능이 빨라진다.

4. 조회 대상 건수
인덱스를 통한 읽기는 그냥 읽는 방법보다 4~5배 비용이 더 든다.
따라서 조회 대상이 20~25% 이상일 경우 전체 데이터 중에서 필터링하는 것이 보다 효율적이다.

인덱스를 이용하는 방법

인덱스 레이지 스캔
가장 대표적인 방법으로 다른 방법보다 빠른 방법으로 세부적으로 한건과 여러건일 경우가 나누어진다.
검색하는 수의 상관없이 인덱스의 범위가 결정됐을때 사용하는 방식이며 아래와 같은 순으로 동작한다.

  1. 루트노드에서 브랜치 노드 그리고 리프 노드를 통해 조건을 만족하는 첫번째 인덱스를 찾는다.
  2. 해당 인덱스부터 순차적으로 조건을 만족하는 인덱스를 페이지간 링크를 이용해 끝까지 찾아낸다.
  3. 대상이 된 인덱스 키와 주소를 가지고 실제 레코드가 저장된 페이지를 가져오고 최종적으로 레코드를 읽어온다.

위의 3의 경우 대상 레코드별로 실제 디스크 I/O작업이 이루어지는데 이는 인덱스 스캔이 많은 비용을 지불하게 되는 이유다. 경우에 따라 3은 필수이지 않은데 커버링 인덱스의 경우가 그렇다. 커버링 인덱스는 쿼리 결과에 필요한 컬럼이 인덱스에 모두 포함된 경우 필요한 데이터를 인덱스 페이지에 다 가지고 있는 경우이다. 해당 경우 I/O 작업이 이루어지지 않아 성능은 빨라질 수 있다.

인덱스 풀 스캔
인덱스의 처음부터 끝까지 모두 읽는 방식으로 조건절에 사용된 컬럼(인덱스)가 첫번째가 아닌 경우 해당 방식으로 스캔하게 된다. 다른 인덱스 방식보다 효율적인 방법은 아니지만 전체 테이블에 접근하는 방식보다는 효율적이다. 따라서 조회 쿼리에 필요한 내용이 인덱스에 모두 있는 경우(커버링 인덱스) 이 방식으로 처리된다.

다시 한번 정리하면 인덱스 풀 스캔 방식은 효율적인 방식이 아니므로 실행계획에서 풀 스캔을 하고 있다면 다른 스캔 방법을 고려해보자

루스 인덱스 스캔
루스 인덱스 스캔은 다른 DBMS의 인덱스 스킵 스캔과 비슷하며 느슨하게 또는 듬성듬성 인덱스를 읽는 방식이다. 인덱스 레인지 스캔과 비슷하게 작동하지만 중간에 필요없는 인덱스는 무시(SKIP)하고 다음으로 넘어가는 형태이다.

인덱스 스킵 스캔

ALTER TABLE employees ADD INDEX ix_gender_birthdate (gender, birth_date)

위와 같이 두개 이상의 컬럼을 인덱스로 사용하는 경우 MySQL 8.0이전 버전에서는 맨앞에 있는 컬럼의 값을(gender) 조건절로 사용해야지만 인덱스 스캔 방식을 사용할 수 있다.

하지만 8.0이후 버전에서는 해당 스캔 방식을 지원하게 되었는데 아래의 옵션을 이용해서 옵티마이저에 기능을 이용할 수 있다.

SET optimizer_switch='skip_scan=on'

이렇게 하면 위의 생성된 인덱스의 예로 birth_date만 조건절에 사용해도 ix_gender_birthdate 인덱스를 사용해 조회를 할 수 있다.

이는 내부적으로 사용하지 않은 gender컬럼의 유니크한 값을 모두 가져와서 일일히 조건절에 동등조건으로 채워 조회를 한다. 즉 gender가 'M', 'F'일 경우 2번의 쿼리가 동작하는 형태와 비슷하다고 생각하면 된다.

인덱스 스킵 스캔은 아래와 같은 조건을 충족해야만 성능을 보장하거나 동작한다!
1. WHERE 조건절에 사용되지 않은 인덱스의 다른 컬럼의 유니크한 값의 갯수가 적어야한다.
2. 쿼리에 필요한 컬럼이 모두 인덱스이어야한다.(커버링인덱스)

인덱스 정렬

인덱스는 키 값을 오름차순 또는 내림차순으로 정렬한 후 저장한다는 특징이 있다.
A라는 인덱스가 오름차순으로 정렬되어 저장되었다하더라도 역순으로 읽으면 내림차순이 되기 때문에 어떠한 방향으로 저장되었다고 하더라도 양방향 조회가 가능하다.

다중 인덱스로 설정된 a, b, c라는 컬럼이 있다면 인덱스를 생성할 때 지정한 순서대로 우선 정렬된다. 따라서 a컬럼으로 우선 정렬되고 같은 값이라면 b컬럼으로 정렬하는 방식이다.
인덱스를 지정하는 순서에 따라 정렬순서가 바뀌기 때문에 인덱스를 생성할 때 지정하는 순서가 중요하다.

주의할 점!
위에서 알아본 내용으로 인덱스에 방향을 정하더라도 양방향 조회가 가능하다. 다만 인덱스 레코드간 링크는 단방향으로 이루어졌기에 정방향으로 조회했을때가 성능상 유리하다.
따라서 레코드가 많은 테이블의 경우 자주 사용하는 정렬 방향에 따라 인덱스를 생성하면 약간의 성능 향상 효과를 누릴 수 있다.

인덱스 효율성

비교조건의 따른 효율성
동등 비교(=), 범위 비교(>,<)가 있으며 다중 컬럼 인덱스이면 각 컬럼에 사용된 조건 순서에 따라 효율성이 달라진다.

동등 비교, 범위 비교순인 경우

select * 
from dept_emp
where dept_no = 'd002' and emp_no >= 10114;

INDEX(dept_no, emp_no) 인덱스일 경우 아래와 같이 동작하며 정확히 쿼리에 대상이 되는 레코드만을 가져온다.
1. dept_no 컬럼이 동등비교가 되어 대상 레코드를 제한한다.
2. 1에서 제한된 레코드에서 emp_no 컬럼의 범위에 포함되는 마지막 레코드까지의 데이터를 가져온다.

범위 비교, 동등 비교순인 경우 

select * 
from dept_emp
where emp_no >= 10114 and dept_no = 'd002';

INDEX(emp_no, dept_no) 인덱스일 경우 아래와 같이 동작하며 범위비교에서 대상이 된 레코드를 가져온 다음 대상 레코드를 동등 조건으로 일일히 필터링한다.
1. emp_no 컬럼이 범위비교가 되어 대상 레코드를 전체를 가져온다.
2. 1에서 대상이 된 전체 레코드를 dept_no조건으로 일일히 동등 비교하여 조건에 맞는 데이터를 가져온다.

동등조건은 필터 역할을 하고 비교 조건은 범위 결정 역할을 한다.
동등조건은 대상이 되는 데이터들에 대해서 체크를 하기에 모든 데이터가 조건 검증 대상이 된다.
비교조건은 인덱스가 정렬이 되어있기 때문에 해당 조건에 해당하는 첫번째 값을 찾고 그로부터 끝까지 가져오기 때문에 더 이상 조건 검증을 하지 않는다.

따라서 비교조건이 성능에 유리함이 있고 성능향상을 기대할 수 있고 동등 조건은 많아질수록 성능상 불리한 경우도 있으니 주의하자!

인덱스 가용성

B-Tree 인덱스에 특징은 왼쪽부터 오른쪽으로 정렬된다는 점이다. 이러한 특성은 다중 컬럼에 적용되며 순서대로 왼쪽부터 정렬되어 들어간다. 따라서 첫번째 컬럼이 레인지 스캔이 안되고 두번째 컬럼이 레인지 스캔 조건이 충족된다 해도 해당 쿼리는 인덱스 레인지 스캔을 이용할 수 없다.

또한 선행 컬럼에 아래와 같은 조건을 적용했다면 작업 범위 결정으로 사용할 수 없으며 경우에 따라 체크 조건으로 인덱스를 사용할 수 있다..

작업 범위 조건으로 사용할 수 없는 케이스는 다음과 같다.
1. NOT EQUAL("<>", "NOT IN", "NOT BETWEEN", "IS NOT NULL")
2. LIKE '%??' (뒷부분 일치 조건)
3. 함수나 연산자로 컬럼이 변형된 경우
4. NOT DETERMINISTIC 함수가 EQUAL 조건에 사용된 경우
5. 데이터 타입이 서로 다른 비교
6. 문자열 데이터 타입의 콜레이션이 다른 경우

R-Tree 인덱스

공간 인덱스를 나타내며 2차원의 데이터를 인덱싱하고 검색하는 목적의 인덱스다. B-Tree와 흡사한 형태이다. 주소와 같은 공간 데이터를 저장 및 인덱스 기능과 더불어 연산 함수 기능도 제공한다.

이와 관련해서는 아래 TODO를 통해서 꼭 실습해보자

전문 검색 인덱스

B-Tree 인덱스는 키워드화 된 인덱스인 반면 전문 검색 인덱스는 게시글 본문과 같은 문서 내용 전체 중 사용자가 검색하게 될 키워드를 분석해서 인덱스화해서 특정 키워드가 포함된 문서를 검색하는 용도이다.

어근 분석 알고리즘

불용어 처리
본문 중 가치가 없는 단어를 필터링해서 제거하는 방식으로 제외할 단어는 상수로 관리되며 시스템에 기본 값이 존재하며 사용자 정의 상수로 필터링도 가능하다.

어근 분석
검색어로 선정된 단어의 뿌리인 원형을 찾는 작업으로 다음 오픈소스를 통해서 적용이 가능하다. 한글, 일본어는 MeCab(일본어 전용)이고 서구권 언어는 MongoDB전용인 Snowball을 사용할 수 있다. 다만 적용자체는 어렵지 않으나 단어 사전을 제공한 후 문장의 구조 인식을 위해 샘플을 통해 언어 학습이 필요한데 해당 과정은 오랜 시간이 걸리는 작업이다.

n-gram 알고리즘

인덱싱할 키워드의 최소 글자 수 n을 이용하여 본문을 무조건 몇 글자씩 잘라서 인덱싱하는 방법이다. 잘라서 관리되는 키워드들 중에 불용어로 등록되어 있는 경우 걸러버린다.
따라서 불필요하게 불용어 처리 되어서 인덱싱이 제공되지 않을 수 있으며 다음과 같은 방법들을 통해 해결할 수 있다.

불용어 무시 처리
1. my.cnf파일에 ft_stopword_file='' 설정 후 서버 재시작
2. 전문 검색 인덱스에서만 불용어 무시 처리는 SET GLOBAL innodb_ft_enable_stopword=off;

사용자 지정 불용어 사용 처리
1. my.cnf파일에 ft_stopword_file='사용자파일경로' 설정 후 서버 재시작
2. 불용어 테이블 사용 (InnoDB 테이블에만 적용 가능한 방법)

create table my_stopword(value varchar(30)) engine = innodb;
insert into my_stopword(value) values ('MySQL');

set global innodb_ft_server_stopword_table = 'employees/my_stopword';
-- 위에 불용어 테이블 생성 후 생성된 전문 검색 인덱스만 적용 가능
alter table tb_bi_gram add fulltext index fx_title_body(title, body) with parser ngram;

전문검색 인덱스 사용시 필수 조건
1. 쿼리 문장이 전문검색을 위한 문법 사용(match() against())

select *
from tb_test
where match(doc_body)  AGAINST('애플' in boolean mode ) ;
  1. 조회 대상 테이블 컬럼이 전문 인덱스 보유
create table tb_test(
    doc_id int primary key ,
    doc_body text,
    -- 전문 인덱스
    fulltext key fx_docbody (doc_body) with parser ngram
) engine = InnoDB;

함수 기반 인덱스

컬럼 값을 변형해서 만들어진 값에 대해 인덱스를 생성할때 사용하는 방식이다.

가상 컬럼방식
가상 컬럼을 추가하고 해당 컬럼에 인덱스를 추가하는 방식이다.

-- user 테이블에 full_name이라는 가상 컬럼(virtual)을 만들고 인덱스 추가
alter table user
add full_name varchar(30) as (concat(first_name, ' ', last_name)) virtual,
add index ix_fullname(full_name);

가상 컬럼은 virtual 또는 stored 키워드를 사용해서 추가할 수 있다.
위와 같은 방법 사용시 데이터 추가시에 full_name에 데이터를 입력하지 않아도 first_name, last_name으로 자동 생성되어 값이 할당된다.

함수 직접 사용 방식

create table user2 (
    user_id BIGINT primary key ,
    first_name varchar(10),
    last_name varchar(10),
    -- 함수 자체를 인덱스로 생성
    index ix_fullname((concat(first_name, ' ', last_name)))
);

-- 해당 함수 사용시 인덱스를 이용한 조회
explain
select *
from user2
where concat(first_name, ' ', last_name) = 'choi kwang';

해당 방식은 명시된 함수가 정확히 그대로 사용되야한다는 주의점이 있다.

함수 기반 인덱스 방식은 내부적으로 같은 방식을 사용한다. 따라서 성능차이는 없고 편의성을 생각하면 가상 컬럼을 쓰는게 좋아보인다.

멀티 벨류 인덱스

하나의 데이터 레코드가 여러 개의 키 값을 가질 수 있는 형태의 인덱스이다.
MySQL에서 JSON과 같은 타입의 데이터를 하나의 컬럼으로 저장하는 방법을 지원하기 시작했는데 JSON 타입을 저장할 경우 각 원소들에 인덱스를 지원하기 위한 방법이다.
member of(), JSON_CONTAINS(), JSON_OVERLAPS()와 같은 함수를 지원한다.
또한 멀티밸류인덱스에서는 DECIMAL, INTEGER, DATETIME, VARCHAR/CHAR를 지원한다.

create table user3 (
    user_id BIGINT auto_increment primary key ,
    first_name varchar(10),
    last_name varchar(10),
    -- json 형식 지원
    credit_info JSON,
    index mx_creditscores ((cast(credit_info->'$.credit_scores' as unsigned array)))
);

select *
from user3
where 360 member of (credit_info->'$.credit_scores');

클러스터링 인덱스

테이블의 프라이머리 키에 대해서 비슷한 레코드끼리 묶어서 저장하는 그룹핑된 인덱스 저장 방식이다.(사실상 테이블 저장 방식에 가깝다.)
B-Tree 인덱스도 인덱스의 키 값으로 정렬되어 저장되어 같다고 생각할 수 있지만 여기서 중요한 것은 테이블의 프라이머리 키라는 점이다. 따라서 일반적인 B-Tree 인덱스를 클러스터링 인덱스라 하지 않고 프라이머리 키 값으로 정렬되어 저장된 경우만 클러스터링 인덱스, 클러스터링 테이블이라고 부른다.

InnoDB 테이블에서 아래와 같은 순서대로 클러스터링 인덱스를 생성한다.
1. 프라이머리 키가 있으면 프라이머리 키를 인덱스 키로 선택
2. NOT NULL 옵션의 유니크 인덱스 중 첫번째 인덱스를 키로 선택
3. 자동으로 유니크한 값을 가지도록 증가되는 컬럼을 내부적으로 추가 후 키로 선택

프라이머리 키와 세컨더리 인덱스

InnoDB테이블은 클러스터링 인덱스로 인해 프라이머리 키가 변경되면 해당하는 데이터 레코드에 위치가 변경된다.
그렇다면 여기서 세컨더리 인덱스가 실제 데이터 주소를 갖고 있다면 프라이머리 키가 변경될때마다 해당 테이블에 모든 세컨더리 인덱스에 주소 값도 다시 정렬되어야해서 엄청난 비용이 발생할 것이다.

따라서 세컨더리 인덱스는 프라이머리 키를 참조하게 구현하고 실제 데이터 주소는 프라이머리 키만 참조하도록 구현되어있다.

클러스터링 테이블 장단점

InnoDB 테이블과 같은 클러스터링이 된 테이블은 아래와 같은 장단점이 있다.

장점단점
프라이머리 키로 검색시 성능이 빠름세컨더리 인덱스로 검색시 프라이머리 키를 사용해 성능이 느림
세컨더리 인덱스가 프라이머리 키와 연결되어있기 때문에 커버링 인덱스로 커버될 가능성이 높음모든 세컨더리 인덱스가 프라이머리 키를 가지고 있기 때문에 프라이머리 키가 커질 경우 전체적인 인덱스가 커짐
데이터 삽입시 프라이머리 키에 의해 레코드 저장위치가 결정되어 처리 성능이 느림

클러스터링 테이블 주의점

프라이머리 키 크기가 크면 전체적인 인덱스 크기가 생각보다 쉽고 많이 증가한다.
5개의 세컨더리 인덱스가 있다고 가정하고 프라이머리 키 크기에 따라 인덱스 크기 증가를 확인해보자.

프라이머리 키 크기레코드당 증가하는 인덱스 크기100만 건 레코드 저장 시 증가하는 인덱스 크기
10바이트10바이트 * 5 = 50바이트50바이트 * 1,000,000 = 47MB
50바이트50바이트 * 5 = 250바이트250바이트 * 1,000,000 = 238MB

프라이머리 키는 가능하면 실제로 사용하는 업무적인 컬럼으로 생성(Auto-Increment x)
프라이머리 키로 검색하게 되는 경우 엄청나게 빠른 성능을 기대할 수 있다. 따라서 검색 필터에 자주 사용되거나 대표로 노출이 많은 컬럼(커버링 인덱스)을 사용하면 좋다.
프라이머리 키는 반드시 명시하자.
프라이머리 키를 추가하지 않으면 내부적으로 일련번호 컬럼을 추가하는데 이는 사용자가 접근할 수 없다. 따라서 Auto-Increment을 이용해서라도 생성하는게 좋다.
Auto-Increment 컬럼을 인조 식별자로 사용할 경우
아래와 같은 경우 해당 키 사용을 우선시해라.

사용키상황
본질 식별자프라이머리 키가 길지만 세컨더리 인덱스가 필요치 않은 경우
인조 식별자프라이머리 키가 길고 세컨더리 인덱스도 필요한 경우

유니크 인덱스

컬럼에 유일한 값이라는 제약조건에 가까운 인덱스이다. 유니크 제약조건을 추가하려면 유니크 인덱스를 설정 해야한다.
유니크 인덱스가 일반 세컨더리 인덱스보다 빠르다는 편견이 있는데 그건 유니크 인덱스라서 빠른게 아니라 유니크 인덱스에 있는 값이 하나뿐이라서 빠르다는것이다.
따라서 읽어야하는 건수가 같다면 유니크 인덱스랑 세컨더리 인덱스는 성능상 차이는 거의 없다.
쓰기인 경우 유니크 인덱스는 같은 값이 있는지 검사를 한번 더 하기 때문에 성능상 불리하다.
유니크 인덱스를 사용하면 성능이 좋아질거라 생각해서 중복으로 인덱스를 생성하는 경우가 있는데 값이 유일하다는 조건검사만 할 뿐 성능상 전혀 이점이 없으니 유일하다면 유니크인덱스를 아니면 세컨더리 인덱스를 사용하면 된다.

외래키

MySQL에서 외래키는 InnoDB 스토리지 엔진에서만 사용가능하다. InnoDB에 외래키는 아래와 같은 특징이 있다.
테이블의 변경(쓰기 잠금)이 발생하는 경우에만 잠금 경합이 발생한다.

  • 자식 테이블 레코드 변경시
    외래키 변경에 해당하는 부모 테이블에 레코드가 락이 걸려있으면 자식 테이블은 대기한다.
    부모 테이블과 상관 없는 컬럼일 경우 부모 테이블에 락이 걸려있어도 대기하지 않는다.
  • 부모 테이블 레코드 변경시
    자식 테이블에 레코드가 삭제되어 락이 걸려있는 경우 부모 테이블은 대기한다.

외래키와 연관되지 않은 컬럼의 변경은 최대한 잠금 경합을 발생시키지 않는다.

TODO

아래 내용 참고해서 R-Tree + JPA 적용해보기
https://tecoble.techcourse.co.kr/post/2023-10-04-spatial-data/

profile
kwang-sub
백엔드 개발일지
이전 포스트

[MySQL] 암호화

다음 포스트

[MySQL] 옵티마이저

0개의 댓글