[Real MySQL 8.0] 인덱스

 

랜덤 I/O 와 순차 I/O

인덱스를 설명하기 전, 랜덤 I/O 와 순차 I/O 에 대해 알아야한다.

고전적인 HDD 디스크는 디스크를 돌려 순차적으로 읽을 경우 한바퀴에 모든 데이터를 조회 할 수있다.

반면에, 읽어야 하는 데이터를 순차적으로 조회하지 않는다면 3개의 데이터를 조회하는데 3번 디스크를 돌려야한다.

이를 랜덤 I/O 라고 한다.

 

즉 일반적으로 순차 I/O 가 랜덤 I/O 보다 훨씬 성능이 좋다.

이는 디스크를 돌리지 않는 SDD 에서도 마찬가지이다.

 

인덱스 레인지 스캔은 일반적으로 랜덤 I/O 이며, 풀 스캔의 경우 순차 I/O 이다.

그래서 테이블의 데이터 대부분을 읽어야하는 경우, 풀 스캔 후 가공하는 편이 성능이 더 좋다.

 

인덱스

인덱스란 말 그대로 목차와 비슷한 역할을 한다.

인덱스를 사용해 조회하는 것이 일반적으로 풀스캔보다 빠른 이유는 바로 정렬에 있다.

 

일반적으로 데이터 추가 비용이 1 이라고 한다면, 인덱스를 추가하는 비용은 1.5 정도된다.

테이블에 인덱스가 3개라면? 1 + 1.5 * 3 의 비용이 든다는 것이다.

즉 인덱스란 입력 성능과 검색 성능의 트레이드오프라고 할 수 있다.

 

또한 InnoDB 는 레코드나 갭 잠금이 인덱스를 잠그는 형태이므로, 더 큰 의미를 지닌다.

적절한 인덱스가 없는 경우, 불필요하게 많은 데이터를 잠글 수 있다.

 

B-Tree 인덱스

B-Tree 인덱스는 일단 현재 가장 대표적인 인덱스 알고리즘이라고 할 수 있다.

B(Balanced)-Tree 의 장점은 어떤 데이터에 접근하건 Log(n) 의 시간복잡도를 지닌다는 점이다.

 

InnoDB 의 B-Tree 인덱스 구조 

트리의 선두엔 루트 노드가 있고 트리의 최 하층엔 리프노드가 있으며, 그 이외의 중간 계층은 브랜치 노드라고 한다. 

각 노드가 가질수 있는 페이지 크기는 정해져 있으며, 그 크기가 넘어가면 뎁스가 증가한다.

각각의 노드는 다음 노드의 특정 페이지를 가르키며, 리프노드는 데이터 레코드의 디스크 위치를 가르킨다.

 

InnoDB 에서는 클러스터 인덱스인 PK 인덱스에 대해서만 리프노드가 데이터의 디스크 위치를 지니며,

세컨더리 인덱스의 리프노드는 PK 값을 지닌다.

 

세컨더리 인덱스의 리프노드가 데이터 주소가 아닌 PK 값을 가지는 이유가 궁금할 것이다.

InnoDB 에서 데이터는 클러스터링 인덱스인 PK 를 기준으로 정렬되어 저장되는데, 거의 그럴 일은 없겠지만 

클러스터링 인덱스의 값이 변경될 경우, 데이터의 위치도 변경되게 되는데 그럴때 마다 모든 인덱스 리프노드의 주솟값을 변경해주어야 할 것이다. InnoDB 엔진은 이런 오버헤드를 없애기 위해 세컨더리 인덱스의 리프노드에는 PK 주솟값을 지니게 구현되었다.

 

즉 세컨더리 인덱스는 트리 탐색후 PK 의 트리를 재 탐색하여 디스크를 조회하며 2Log(n) 의 시간 복잡도를 지니게 된다.

 

인덱스 성능에 영향을 미치는 요소

인덱스 키 값의 크기

InnoDB 에서 디스크에 데이터를 저장하는 기본 단위를 페이지라고 하는데, 이는 버퍼풀의 버퍼링에서도 마찬가지이며

인덱스에서도 마찬가지이다. 이 페이지의 크기는 innodb_page_size 변수로 조절할 수 있고 기본값은 16kb 이다.

 

이렇게 고정적인 크기를 지니는 단위이기 때문에, 키 값의 크기가 커진다면 페이지 당 지닐 수 있는 페이지 수가 줄어들 것이다. 또한 트리의 뎁스도 더 깊어질 것이다.즉 한번에 n 개의 레코드를 조회할 때, 더 많은 페이지를 읽어야하고 더 많이 디스크를 조회하게 된다는 뜻이다.

 

또한 버퍼풀에서도 이는 마찬가지이며, 캐싱할 수 있는 크기는 한정적인데 데이터 자체의 크기가 커지게 되는것과 마찬가지이므로 

메모리 효율도 떨어지게 된다. 

 

선택도와 카디널리티

유니크한 값의 수. 중복된 값이 많아질 수록 선택도와 기수성은 떨어진다.

기수성과 선택도가 높을 수록,  검색할 대상이 줄어들기 때문에 빠르게 처리된다.

하지만 선택도가 좋지 않더라도 그루핑과 정렬을 위해 인덱스를 만드는 경우도 있다.

 

일반적으로 인덱스는 데이터 1건을 직접 읽는것 보다 5배 정도 더 비용이 든다.

즉 데이터의 20~25 퍼센트 이상을 읽는 작업은 옵티마이저가 인덱스를 사용하지 않고 풀 스캔을 진행한다.

이런 경우는 힌트를 강제로 사용하더라도 의미가 없다.

 

인덱스 가용성

B-Tree 는 왼쪽값에 기준해 오른쪽 값이 정렬되어 있다.

여기서 왼쪽이란 하나의 컬럼 뿐만 아니라 다중컬럼 인덱스에 대해서도 마찬가지이다.

이런 정렬의 특성 상, 값의 왼쪽 부분을 알아야 인덱스 스캔이 가능하다.

 

다음은 B-Tree 인덱스 스캔의 제약사항이다.

• NOT 연산의 경우
• 문자열 뒷 부분의 일치 검색 LIKE 'ㅇㅇ%'
• 인덱스가 스토어드 함수나 다른 연산자로 컬럼이 변형된 후 비교된 경우
• 데이터 타입이 서로 다른 경우
• 문자열 콜레이션이 다른 경우
• 복합 인덱스의 경우, 위의 모든 경우를 포함하여 인덱스의 첫 컬럼이 인덱스를 타지 않은 경우

 

인덱스 스캔

인덱스 레인지 스캔

일반적으로 다른 스캔 방식들 보다 빠른 방식이다.

인덱스의 조회 범위가 지정되었을 경우 사용되는 방식이다.

검색 값의 수나, 결과랑은 관계 없이 리프 노드까지 찾아 들어간 후, 스캔의 시작점을 찾고 데이터를 순차적으로 읽는다.

인덱스를 읽는건 순차적으로 읽지만, 실제 데이터는 인덱스처럼 정렬되어 있지 않기 때문에 레코드를 읽는건 건마다 랜덤 I/O가 발생한다.

위에서 설명한 인덱스가 직접 레코드를 읽는것 보다 5배 정도 느리다는 이유이다.

 

커버링 인덱스

하지만 쿼리가 요청하는 데이터에 따라 랜덤 I/O 과정은 필요가 없을 수도 있다.

출력 데이터와 조건 데이터가 모두 태운 인덱스에 포함되는 경우가 그 경우 이다.

이를 커버링 인덱스라고 한다.

 

인덱스 풀 스캔

인덱스의 처음부터 끝까지 모두 다 읽는 경우를 인덱스 풀 스캔이라고 한다. 

대표적으로 복합 인덱스의 첫번째 컬럼을 활용하지 않은 경우, 인덱스 풀 스캔이 수행된다.

하지만 이는 커버링 인덱스일 경우의 이야기이고, 디스크에 랜덤 I/O를 해야한다면 옵티마이저에 의해 절대 선택되지 않고 테이블 풀 스캔으로 데이터를 조회한다.

 

루스 인덱스 스캔

앞의 인덱스 레인지스캔과 풀 스캔은 특정 범위의 인덱스를 모두 읽기 때문에 타이트 인덱스 스캔이라고 한다.

루스 인덱스 스캔은 그 반대로, 인덱스를 듬성 듬성하게 읽는다.

 

루스 인덱스 스캔은 특별한 경우에만 사용되는데, 예시와 함께 살펴보자.

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

위의 쿼리의 경우 dept_no 가 002, 004 사이에서 emp_no 의 첫번째 값만 읽으면 된다.

 

 

인덱스 스킵 스캔

인덱스의 핵심은 정렬 인덱스 구성 컬럼의 순서이다. 위에서 살펴봤듯 첫번째 컬럼이 최우선 정렬 기준이기 때문에

첫번째 컬럼 조건이 인덱스 스캔을 만족하지 않으면 인덱스를 타지 못한다.

하지만 MySQL 8.0 버전 부터는 optimizer_switch='skip_scan=on' 변수로 스킵스캔을 활성화 하면

위와 같은 경우에도 인덱스를 사용가능하다.

예시로 살펴보자

 

(gender, birth_date) 가 인덱스인 employees 테이블이 존재할때, 아래와 같은 쿼리를 수행한다면

SELECT gender, birth_date
FROM employees
WHERE birth_date > '1964-02-01'

인덱스 풀스캔으로 동작 할 것이다. 만약 조회 컬럼이 더 있다면 테이블 풀스캔으로 동작한다.

하지만 SET optimizer_switch='skip_scan=on'  을 세팅한 후라면?

SELECT gender, birth_date FROM employees WHERE gender = 'M' AND birth_date > '1964-02-01';
SELECT gender, birth_date FROM employees WHERE gender = 'F' AND birth_date > '1964-02-01';

두개의 쿼리를 수행해,  결과를 조합하는것과 비슷한 방식으로 동작하여 인덱스를 활용할 수 있다.

만약 gender 가 enum 타입이 아닐 경우, 테이블내에 존재하는 유니크 값을 추출해 그 결과값을 활용하여 동일한 동작을 한다.

 

인덱스 스킵스캔을 사용할 때에는

WHERE 조건절에 조건이 없는 인덱스의 선행컬럼. (위의 경우엔 gender) 의 유니크한 값의 갯수가 많지 않아야한다.

많으면 효율이 급격히 떨어진다.

또한 커버링 인덱스를 사용하는 경우에만 활용할 수 있다.

 

인덱스의 효율

예를들어 dept = 1000 and emp_no > 20 이라는 조건을 사용하는 쿼리가 있을때,

(dept, emp_no) 인 인덱스와 (emp_no, dept) 인 인덱스 중 어느쪽이 더 효율이 좋을까?

 

• (dept, emp_no) 의 경우
  dept 가 1000 이고, emp_no 가 20인  리프노드의 레코드를 찾아 순서대로 dept 가 1001 인 페이지에 도달할때 까지 읽으면 된다.
• (emp_no, dept) 의 경우
  emp_no = 20 인 리프노드의 레코드를 찾고, 페이지 끝까지 dept_no 가 1000인지 여부를 판단하여 솎아내야한다.

두번째 경우가 훨씬 많은 페이지를 읽어야하므로 당연히 전자가 훨씬 빠를것이다.

두번째 케이스에서 emp_no 를 범위 결정조건, dept 를 필터링 조건이라고한다.

범위 결정 조건은 인덱스 페이지의 조회 범위를 결정하며,

필터링 조건은 레코드의 데이터를 읽을지 말지 여부를 결정한다.

범위 결정 조건은 조회속도에 큰 영향을 주지만, 필터링 조건은 오히려 느리게 만들때가 더 많다.

인덱스의 컬럼들이 범위 결정조건으로써 조회 범위를 좁혀주도록 인덱스를 잘 설정해야한다.

 

Hash 인덱스

해시 인덱스는 주로 메모리 기반의 데이터베이스에서 디폴트로 사용되며,

InnoDB 에서는 Adaptive Hash Index 라는 형태로 사용된다.

어댑티브 해시 인덱스에 대해서는 아래에서 다루고 우선 해시 인덱스의 특징부터 살펴보자.

 

Hash 인덱스의 특징

해시 인덱스의 가장 주요한 특성으로는 모든 데이터에 O(1) 의 상수복잡도로서 매우 빠르게 접근할 수 있다는 점과 

해시로 키 값을 저장하기 때문에, 키의 주소공간 차지가 적다는 점도 있다. 

 

하지만 Hash 의 특성상 동등 비교에는 사용가능하나, 실제로 정렬이 되어 저장되는 형식이 아니기 때문에 범위 검색이나 전문 검색 혹은 소팅을 목적으로는 사용할 수 없다.

 

MySQL 의 Hash Index

B-Tree 인덱스의 경우 자주 조회하는 데이터임에도 항상 트리를 조회해야하고, 세컨더리 인덱스의 경우 두번 인덱스 트리를 탐색해야하므로 2 log(n) 의 시간 복잡도가 발생한다. 

 

MySQL InnoDB 엔진 에서는 이러한 단점을 극복하기 위해 Adaptive Hash Index 라는 형태로 해시 인덱스를 지원하는데,

innodb_adaptive_hash_index 라는 옵션을 키면 자동으로 옵티마이저가 자주 사용하는 쿼리에 대하여

내부적으로 판단하여  해시 인덱스를 생성하고 사용한다.

 

어댑티브 해시 인덱스를 사용하면 빠른 조회속도와 메모리 효율을 얻을 수 있지만,

사용자가 직접 인덱스가 사용되는 쿼리를 핸들링 할 수 없고 인덱스의 생성과 소멸 모두 컨트롤 할 수 없으므로 메모리 사용량을 항상 모니터링 해야한다. 또 오래 사용하지 않은 테이블에도 해시 인덱스가 남아있어 라이브 서비스 운영중에 영향을 주는 경우가 있으므로

Drop 시 주의를 기울여야 한다.

 

전문검색 인덱스

앞에서 살펴본 인덱스들은 전문검색에는 특화되어있지 않다고 설명했다.

전문검색이 필요할 경우 전문검색에 특화된 데이터베이스를 사용하는 방법도 존재하지만,

그리 전문적인 검색이 필요하지 않다면 MySQL의 전문검색 인덱스를 활용하는 방법도 손쉽게 문제를 해결할 수 있는 방법이 된다.

 

전문검색 인덱스는 텍스트에서 키워드를 추출하여 키워드에서 불용어를 처리하고 추출된 키워드를 바탕으로 인덱스를 구성한다.

MySQL 의 전문검색 인덱스는 어근분석과 n-gram 인덱스로 구분지을 수 있다.

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

양이 많지 않기 때문에, 주로 알고리즘 내에서 상수로 정의하여 사용하며, MySQL 에서는 사용자 별도 정의 기능을 제공한다.

 

어근 분석 알고리즘

어근 분석 검색어로 선정된 단어의 뿌리인 원형을 찾는 작업이다. MeCab 이라는 오픈소스 형태소분석 플러그인이 존재한다.

한국어 일본어는 영어와 같이 단어 변형이 거의 없어 어근분석보다는 명사와 조사를 구분하는 기능이 중요한데,

MeCab 은 원래 일본어의 형태소 분석을 목적으로 만들어졌지만 한국어도 구조가 거의 유사하여 활용할 수 있다.

하지만 MeCab 을 이용한 형태소분석 전문검색은 단어사전부터 시작해서 완성도있는 세팅을 위해 많은 노력이 필요하다.

 

N-gram 알고리즘

MeCab 을 이용한 형태소 분석은 매우 전문적인 전문검색 알고리즘이므로, 정확도를 높이기 위해서 세팅에 많은 리소스를 투자하여야 한다. N-gram 알고리즘은 그런 단점을 보완하려고 나온 알고리즘이다. 형태소 분석이 문장을 이해하기 위한 알고리즘이라면, n-gram 은 단순히 키워드를 추출해내기 위한 인덱싱 알고리즘이다. N-gram 알고리즘은 본문을 무조건 n 글자씩 잘라서 인덱싱하는 방법이다.

 

예를들어,

Hi Hello Index World!

라는 문장이 있다면, 우선 [Hi, Hello, Index, World!] 총 4개 단어를 추출하고 각 단어를 n 개씩 자른다.

주로 2-gram 알고리즘을 이용하니 2개씩 잘라보면

Hi He el ll lo in nd de ex wo or rl ld d! 이런식으로 토큰이 추출되고, 여기서 불용어를 포함하거나 불용어와 일치하는 단어는 제거하여

인덱스로 만든다.  예제를 살펴보면 알 수 있듯 n-gram 은 간단히 문장을 토큰화 해주지만 인덱스의 크기가 상당히 크다는 단점이 있다.

 

또 불용어 처리의 경우 포함만 되어도 키워드가 제거되기 때문에 불용어 처리는 아예 해제하거나, 사용자 정의를 통하여 설정하는 편이 좋다.

information_schema.innodb_ft_default_stopword 시스템 변수에서 기본 불용어를 확인 할 수 있다.

 

사용자 정의 불용어 처리 방법

• My.cnf 에 ft_stopword_file= 빈문자열일 경우 비활성화, 혹은 불용어 정의 파일 path (모든 엔진에서 해당 방법으로 처리됨)
• innodb_ft_enable_stopword=false 로 비활성화 할 수 있고 
  사용자 정의 불용어 처리를 하려면, 불용어 처리 테이블을 만들고 innodb_ft_server_stopword_table
  시스템 변수에 테이블을 등록 (innodb 에서만 변경)

innodb_ft_user_stopword_table 을 이용하는 방법도 있지만 위 방법을 사용해야 전문검색 인덱스별로 다른 불용어를 지정가능하다.

 

전문 검색 인덱스를 사용하는 필수조건

• 쿼리 문장이 전문검색을 위한 문법. MATCH… AGAINST 사용 (LIKE '%%' )(X)
• 테이블이 전문검색 대상 칼럼에 대해 전문검색 인덱스를 포함.

 

인덱스 알고리즘이외에도 특수한 인덱스 기능들을 살펴보자.

 

가상 컬럼을 이용한 인덱스

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

위와 같은 테이블이 존재할때, first_name 과 last_name 을 이어 붙인 값을 기준으로 검색하고 싶을때 인덱스를 태우고 싶다면,

full_name 이라는 컬럼을 생성해 모든 데이터를 업데이트 쳐주어야 했었다. 하지만 MySQL 8.0 버전 부터는 

가상 컬럼을 추가하고, 가상 컬럼에 대한 인덱스를 생성할 수 있게 됬다.

 

ALTER TABLE user 
    ADD full_name VARHCAR(25) AS (CONCAT(first_name,' ',last_name)) VIRTUAL
    ADD INDEX ix_fullname(full_name);

 

함수를 이용한 인덱스

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

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)))
 );

위에서 인덱스 컬럼이 함수에 의해 변경되면 인덱스를 태울수 없다고 하였지만, 함수를 이용한 인덱스를 추가하면 추가한 경우에 한해서는 이용 가능하다. 함수를 이용한 인덱스는 인덱스에 명시한 함수와 완전히 일치하는 함수를 조건절에 사용해야 태울 수 있다.

결과는 같더라도 표현식이 다르다면 옵티마이저는 다른 것으로 판단하고 인덱스를 사용하지 못한다.

 

유니크 인덱스

유니크 인덱스는 사실 인덱스로서의 특징보다는, 제약조건으로서의 특징이 강하다고 볼 수 있다.

MySQL 에서는 인덱스 설정 없이 유니크 제약조건을 걸 방법이 존재하지 않는다.

 

유니크 인덱스에서의 Null

MySQL 은 유니크 인덱스에 대하여 Null 값을 허용한다. Null 은 특정 값이 아니기 때문에 2개이상 저장될 수 있다.

Not Null 이면서 유니크인 인덱스와 PK 의 차이는 클러스터링 인덱스 여부에 달려있다. 

 

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

 

유니크 인덱스가 인덱스로서 성능이 세컨더리 인덱스 보다 더 좋다는 오해가 있지만, 인덱스로서의 성능은 디스크 I/O 를 줄이는 것이 아니기 때문에 사실 거의 비슷하다.  유니크 인덱스가 빠른 경우는 유니크 제약조건으로 인해 읽어야할 레코드 수가 더 적어서 그런 것이지 동일한 레코드 수를 읽을때에 세컨더리 인덱스보다 성능이 더 좋다는 의미가 아니라는 뜻이다.

 

읽기에서는 위에서 설명한대로 인덱스로서 성능차이가 거의 없지만 쓰기에서는 오히려 중복 여부를 체크를 해야하므로,

세컨더리 인덱스보다 성능이 떨어진다. MySQL 에서는 유니크 인덱스의 중복을 체크할 때, 읽기 잠금을 사용하고

쓰기할때 쓰기 잠금을 사용한다. 이로 인한 데드락이 빈번하게 발생한다.

또한 중복체크로 인해 체인지 버퍼를 이용한 인덱스 처리 작업을 버퍼링 하지 못하기 때문에 일반 세컨더리 인덱스보다 더 느리게 처리된다.

 

그러므로 성능을 향상시키기 위해 무분별하게 유니크 인덱스를 생성하지 않는 것이 좋고, 꼭 유일성이 보장되어야 하는 컬럼에 대하여 생성하자. 또 유니크 인덱스는 일반 세컨더리 인덱스가 하는 기능을 모두 수행할 수 있기 때문에 동일한 컬럼에 대하여 인덱스를 중복으로 생성할 필요가 없다.

 

외래키

MySQL 에서 외래키는 InnoDB 스토리지 엔진에서만 생성할 수 있다.

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

 

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

• X 락이 발생하는 경우, 연관 테이블에도 잠금경합이 발생
• 외래키와 연관되지 않는 컬럼의 변경은 최대한 잠금 경합을 발생시키지 않음.

CREATE TABLE parent (
	id INT NOT NULL,
    name VARCHAR(100) NOT NULL, PRIMARY KEY (id)
)

CREATE TABLE child (
    id INT NOT NULL,
    parent_id INT DEFAULT NULL,
    name VARCHAR(100) DEFAULT NULL,
    PRIMARY KEY (id),
    KEY ix_parentid(parent_id) 
    CONSTRAINT child_ibfk_1 FOREIGN KEY (parent_id) REFERENCES parent(id) ON DELETE CASCADE
)

위와 같은 테이블 두개가 있을 때,

 

Connection - 1	Connection - 2
BEGIN;
UPDATE parent SET name = '변경' WHERE id = 1;
	BEGIN;
	UPDATE child SET parent_id = 1 WHERE id = 100;

위와 같은 두 커넥션이 존재한다고 생각해보자. 

Connection - 2 번의 X 락은 Connection -1 번에 걸린 X 락에 대하여 대기한다. 

즉 부모테이블의 레코드에 걸린 X 락은 자식 테이블의 연관 컬럼 변경작업에 대하여 전파된다고 볼 수 있다.

FK 컬럼의 변경은 항상 부모의 상태를 확인한다. 그때 변경잠금이 걸려있으면, 대기한다.

하지만 FK 가 아닌 컬럼의 변경은 외래키로 인한 잠금확장이 발생하지 않는다.

 

반대로 부모에서 자식에게 영향을 받는 경우를 살펴보자

 

우선 child 테이블의 id 가 1번인 컬럼이 parent 의 id 1번 과 연관관계가 있다고 가정하자.

Connection - 1	Connection - 2
BEGIN;
UPDATE child  SET name = '변경' WHERE id = 1;
	BEGIN;
	DELETE FROM parent WHERE id = 1;

이때 Connection-2 번의 부모의 id = 1 인 레코드를 지우는 쿼리는 DELETE ON CASECADE 에 의해 

자식도 함께 삭제 해야하므로, child id =1 인 레코드에 대해서 X 락을 생성하려고 시도하고, 해당 락은 Connection - 1 에서 걸린 락의 해제를 대기하게 된다.

 

이처럼 외래키를 물리적으로 생성하려면 잠금경합까지 고려하여 모델링을 하는 것이 좋다.

외래키는 단순히 연관 테이블의 데이터에 대한 확인 뿐만아니라 읽기 잠금을 걸기 때문에, 쿼리의 동시처리 능력을 저하시킬 수 있다.