RealMySQL 4장 아키텍쳐 : MySQL 엔진에 대해 알아보자

출처 : RealMySQL

커넥션 핸들러

MySQL 커넥션 핸들러는 애플리케이션이 MySQL 데이터베이스와 통신할 수 있도록 돕는 역할을 합니다. JDBC는 그 중간에서 데이터베이스와 애플리케이션 간의 연결을 관리하는 대표적인 API 중 하나입니다.

Spring maven프로젝트의 pom.xml에 의존성을 추가했다.

동작 과정

 

• JDBC는 애플리케이션(spring)에서 데이터베이스로 SQL 쿼리를 전달합니다.
• MySQL JDBC 드라이버가 쿼리를 MySQL 커넥션 핸들러로 전달합니다.
• MySQL 커넥션 핸들러가 요청을 처리하고, 결과를 JDBC를 통해 애플리케이션으로 반환합니다.

 

💡그 외 SQL파서, 전처리기, 쿼리의 최적화된 실행을 위한 옵티마이저로 MySQL엔진은 이루어져있다.

 

쿼리 파서

 

사용자 요청으로 들어온 쿼리문을 MYSQL이 인식할 수 있는 최소 단위(토큰)로 분리해 트리 형태의 구조로 만들어 내고, 기본 문법 오류는 이 과정에서 발견되고 오류 메시지를 사용자에게 전달한다.

 

전처리기

파서 과정에서 만들어진 파서 트리를 기반으로 쿼리 문장에 구조적 문제점이 있는지 확인한다. 권한이 없거나 실제 존재하지 않는 등의 개체의 토큰은 이 단계에서 걸러진다.

 

옵티마이저

클라이언트 요청의 쿼리 문장을 더 저렴한 비용으로 가장 빠르게 처리할지를 결정하는 역할을 담당한다.

---

스토리지 엔진

실제 데이터를 디스크 스토리지에 저장, 읽어오는 부분은 스토리지 엔진이 담당한다. 디스크 스토리지는 실제 스토리지 엔진이 저장하는 하드웨어이다.

 

사용자가 직접 처리를 담당할 스토리지 엔진을 정하지 않으면 MySQL 5.5이상 버전에서는 InnoDB 스토리지 엔진이 처리한다. InnoDB는 안정성과 성능이 좋고, 트랜잭션 지원, 외래키, 데이터 무결성 등 현대 데이터베이스가 갖춰야할 것을 갖추고 있다.

 

책을 보면 MySQL Percona Server에 대한 내용이 많이 나오는데 굉장히 유용해보인다.

 

InnoDB

출처 : RealMySQL

 

스토리지 엔진 중 거의 유일하게 레코드 기반의 잠금을 제공하며, 그 때문에 높은 동시성 처리가 가능하고 안정적이며 성능이 뛰어나다.

 

외래 키 지원

외래 키는 부모 테이블과 자식 테이블 모두 해당 칼럼에 인덱스 생성이 필요하고, 변경 시에는 반드시 부모 테이블이나 자식 테이블에 데이터가 있는지 체크하는 작업이 필요하므로 잠금이 여러 테이블로 전파되고, 그로 인해 데드락이 발생할 때가 많으므로 개발할 때도 외래 키의 존재를 주의해야한다.

 

💡 예시로 알아보자.

외래 키의 작동 원리

1. 참조 무결성 검사
   • 자식 테이블에서 INSERT, UPDATE, DELETE 작업 시, 외래 키로 참조하는 부모 테이블의 값이 유효한지 확인.
   • 부모 테이블에서 값이 삭제되거나 변경될 때도, 자식 테이블에서 참조하고 있는 데이터를 검사.
2. 인덱스 생성 요구
   • 외래 키가 설정된 열에는 반드시 인덱스가 생성되어야 효율적인 참조 무결성 검사가 가능.
   • 부모와 자식 테이블 모두 외래 키 열에 인덱스를 생성해야 함.
3. 잠금(lock) 전파
   • 외래 키가 설정된 테이블에서 데이터를 변경하면, 관련된 다른 테이블도 잠금 상태로 전환될 수 있음.
   • 이는 동시성이 높은 환경에서 데드락을 유발할 가능성을 증가시킴.

---

데드락 예시

예제 시나리오:

• 부모 테이블: users
• 자식 테이블: orders (외래 키: user_id → users.id)

 

CREATE TABLE users ( id INT PRIMARY KEY, name VARCHAR(100) );
CREATE TABLE orders ( id INT PRIMARY KEY, user_id INT, 
FOREIGN KEY (user_id) REFERENCES users(id) );

상황 설명:

• 트랜잭션 A
  • 자식 테이블(orders)에 새로운 주문 추가 시 user_id가 유효한지 부모 테이블(users)을 참조

START TRANSACTION; DELETE FROM users WHERE id = 100; -- 'user_id=100'을 참조 중이므로 잠금 발생

 

• 트랜잭션 B
  • 동시에 부모 테이블의 데이터를 삭제하려 시도.

START TRANSACTION; INSERT INTO orders (id, user_id) VALUES (1, 100); -- 'user_id=100' 체크

 

결과

• 트랜잭션 A는 부모 테이블에서 id=100이 존재하는지 확인하는 잠금을 요청.
• 트랜잭션 B는 id=100 삭제를 시도하며 자식 테이블에서 잠금을 대기.
• 두 트랜잭션이 서로 잠금을 기다리며 데드락 발생.

---

데드락 해결 방법

1. 외래 키 사용 시 데이터 처리 순서 준수

• 삽입(INSERT): 부모 → 자식 순으로 데이터를 추가.
• 삭제(DELETE): 자식 → 부모 순으로 데이터를 삭제.

수정된 트랜잭션 흐름:

-- 트랜잭션 B: 먼저 자식 데이터를 삭제
DELETE FROM orders WHERE user_id = 100;
DELETE FROM users WHERE id = 100;

 

 

2. 외래 키 ON DELETE/UPDATE 옵션 활용

• ON DELETE CASCADE 또는 ON DELETE SET NULL 설정으로 부모 데이터 삭제 시 자식 데이터를 자동 처리.

CREATE TABLE orders ( 
id INT PRIMARY KEY, 
user_id INT,
FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE );

 

 

3. 트랜잭션 격리 수준 조정

• 격리 수준을 낮추거나 잠금 범위를 좁히는 방식으로 데드락 가능성을 줄임.

4.외래 키 대신 애플리케이션 레벨 무결성 검사

• 데이터베이스에서 외래 키를 제거하고, 애플리케이션 로직에서 참조 무결성을 관리.
  (대규모 시스템에서 성능 문제를 줄이기 위해 종종 사용되는 방법)

---

외래 키 사용의 장단점

장점:

• 데이터 무결성 보장: 데이터베이스 레벨에서 참조 관계를 자동 관리.
• 오류 감소: 애플리케이션에서 발생할 수 있는 무결성 관련 오류 방지.

단점:

• 성능 저하: 참조 무결성 검사로 인해 INSERT, DELETE가 느려질 수 있음.
• 데드락 위험: 동시성이 높은 환경에서 테이블 간 잠금 전파로 데드락 가능성 증가.
• 설계 복잡성: 잘못된 외래 키 설계는 유지보수를 어렵게 만듦.

 

자동 데드락 감지

교착 상태

InnoDB 스토리지 엔진은 교착 상태 체크를 위해 잠금 대기 목록을 그래프 형태로 관리한다. 데드락 감지 스레드를 가지고 있어서 주기적으로 그래프를 검사해 교착 상태에 빠진 트랜잭션들을 찾아서 그중 하나를 강제 종료한다. 종료 판단 기준은 트랜잭션의 언두 로그 양이며, 언두 로그 레코드를 더 적게 가진 트랜잭션이 일반적으로 롤백의 대상이 된다.

 

💡트랜잭션 강제 롤백으로 인한 MySQL 서버의 부하를 덜 유발하고 롤백해도 언두 처리 할 내용이 적기 때문이다.

 

하지만 동시 처리 스레드가 매우 많아지거나 각 트랜잭션이 가진 잠금 개수가 많아지면 데드락 감지 스레드가 느려진다. 데드락 감지 스레드를 작동하지 않도록 설정할 수 있다. innodb_lock_wait_timeout 시스템 변수를 활용해 일정 시간이 지나면 자동으로 요청이 실패하고 에러 메시지를 반환하도록 할 수 있다. 당연히 데드락 감지 스레드 작동을 멈추면 이 방법을 권장한다.

 

💡PK나 세컨더리 인덱스를 기반으로 매우 높은 동시성 처리를 요구하는 서비스가 있다면 innodb_deadlock_detect를 비활성화해서 성능 비교를 해보는 것도 좋다.

 

 

여기서 언두 로그는 무엇인가?

데이터베이스에서 트랜잭션의 원자성(Atomicity)을 보장하기 위해 사용되는 로그로, 트랜잭션이 롤백될 때 변경된 데이터를 원래 상태로 되돌리기 위한 정보를 기록합니다. MySQL의 경우, 특히 InnoDB 스토리지 엔진에서 언두 로그는 매우 중요한 역할을 합니다.

 

오랜 시간 활성 상태인 트랜잭션으로 인해 MySQL 서버가 느려질 수 있는데, 일관된 읽기를 위해 언두 로그를 삭제하지 못하고 계속 유지해야 하기 때문에 발생한다. 따라서 트랜잭션이 시작됐다면 가능한 한 빨리 롤백이나 커밋을 통해 트랜잭션을 완료하는 것이 좋다.