MySQL의 기본(이해가 필요한 내용 다시 읽어보기) -1

MySQL의 메모리 구조

MySQL의 메모리 구조는 공식 문서 기준으로 글로벌 메모리 영역(Global Memory Area)과 로컬(세션/커넥션) 메모리 영역(Local/Session Memory Area)으로 나뉩니다.

글로벌 메모리 영역은 MySQL 서버가 시작될 때 운영체제로부터 할당받으며, 모든 클라이언트 스레드가 공유합니다. 대표적으로 InnoDB 버퍼 풀, 테이블 캐시, 리두 로그 버퍼, InnoDB 어댑티브 해시 인덱스, 스레드 캐시, MyISAM 키 캐시 등이 있습니다.

로컬 메모리 영역(세션/커넥션 메모리 영역)은 각 클라이언트 커넥션(스레드)별로 독립적으로 할당됩니다. 커넥션 버퍼, 소트 버퍼, 조인 버퍼, 결과 버퍼, 바이너리 로그 캐시, 네트워크 버퍼 등이 여기에 속합니다. 이 영역은 쿼리 실행 시점이나 세션 유지 동안에만 동적으로 할당되며, 쿼리 종류에 따라 할당 여부와 크기가 달라집니다.

실무에서는 글로벌 메모리와 최대 동시 커넥션 수에 따른 로컬 메모리 사용량을 모두 고려해 전체 메모리 설정을 최적화해야 하며, 실제 메모리 사용량은 쿼리 패턴, 동시 접속 수, 설정값 등에 따라 달라지므로 경험적 튜닝이 필요합니다.

InnoDB 스토리지 엔진의 핵심 특징

InnoDB는 MySQL의 대표적인 스토리지 엔진입니다. 레코드 기반의 잠금(row level lock)을 제공해 높은 동시성 처리와 뛰어난 안전성, 그리고 강력한 트랜잭션 지원을 합니다. 강력한 트랜잭션이라는 것은 row level lock이기에 트랜잭션 처리시 lock을 대기하지 않고 실행이 가능한 점을 의미합니다. 이 덕분에 실무 환경에서 많은 사용자가 동시에 데이터를 처리해도 일관성과 성능을 유지할 수 있습니다.

프라이머리 키 기반의 클러스터링 인덱스 구조

InnoDB의 모든 테이블은 프라이머리 키를 기준으로 클러스터링 인덱스(clustered index) 구조로 저장됩니다. 이는 테이블의 실제 데이터가 프라이머리 키 순서에 맞춰 디스크(실제로는 InnoDB 버퍼 풀과 페이지 구조)에 저장된다는 의미입니다. 따라서 프라이머리 키를 이용한 레인지 스캔이나 정렬 쿼리는 매우 빠르게 처리될 수 있습니다.

만약 PK가 없다면? InnoDB는 모든 컬럼이 NOT NULL인 첫 번째 유니크 인덱스를 클러스터링 인덱스로 사용합니다. 이마저도 없는 테이블이라면, 6바이트의 row ID를 생성해 숨겨진 클러스터링 인덱스(GEN_CLUST_INDEX)를 생성합니다.

또한, InnoDB에서 세컨더리 인덱스(보조 인덱스)는 데이터 레코드의 주소를 직접 저장하지 않고, 대신 해당 레코드의 프라이머리 키 값을 저장합니다. 이 구조 덕분에 세컨더리 인덱스를 통한 검색 시에도 먼저 프라이머리 키 값을 찾은 뒤, 클러스터드 인덱스(프라이머리 키)에서 실제 데이터를 조회하는 방식이 적용됩니다. 이 과정을 인덱스 루킹(Index Lookup) 또는 더블 루킹(Double Lookup)이라고 부릅니다.

이 방식은 인덱스 유지의 일관성을 높이지만, 프라이머리 키가 지나치게 크면 세컨더리 인덱스 크기에도 영향을 줄 수 있습니다. 프라이머리 키가 길수록 세컨더리 인덱스의 크기도 커집니다. 왜냐하면 세컨더리 인덱스의 각 엔트리에 프라이머리 키 전체 값이 저장되기 때문입니다. 따라서 프라이머리 키는 가급적 짧고 불변인 값으로 설계하는 것이 인덱스 효율성에 유리합니다.

실행 계획에서 프라이머리 키의 비중

실행 계획(Execution Plan) 수립 시, InnoDB의 옵티마이저는 프라이머리 키 인덱스의 활용을 우선적으로 고려합니다.
이는 프라이머리 키가 클러스터링 인덱스이기 때문에, 해당 인덱스를 활용한 쿼리가 디스크 접근이나 메모리 접근 측면에서 가장 효율적이기 때문입니다. 결과적으로, 프라이머리 키를 조건에 포함하는 쿼리는 보조 인덱스에 비해 실행 계획에서 더 높은 우선순위로 선택될 확률이 높아집니다.

InnoDB의 외래 키(Foreign Key) 지원

InnoDB 스토리지 엔진은 MySQL에서 외래 키 제약조건(Foreign Key Constraint)을 엔진 레벨에서 지원합니다.
외래 키는 부모 테이블의 특정 컬럼이 자식 테이블의 컬럼과 참조 관계를 맺고, 데이터 무결성을 강제하는 기능으로, 참조 무결성(Referential Integrity) 보장에 필수적입니다.

외래 키 생성 시 필수 사항

외래 키 제약조건을 사용하려면, 부모 테이블과 자식 테이블 양쪽 모두 참조 컬럼에 인덱스가 반드시 존재해야 합니다. 외래 키를 생성할 때 자동으로 인덱스가 생성되기도 하지만, 이미 인덱스가 존재하지 않으면 명시적으로 추가해야 오류가 발생하지 않습니다.

외래 키 사용 시 주의점 및 실무 고려사항

외래 키는 데이터 무결성 확보에는 효과적이지만, 운영환경에서 다음과 같은 불편함이나 주의사항이 존재합니다. 

1. 데이터 삽입/수정/삭제 시, 부모와 자식 테이블의 데이터 유효성(존재 여부)을 항상 체크해야 하므로, 관련 작업이 느려질 수 있습니다.
2. 외래 키 제약조건 위반으로 인해 수동 데이터 적재, 대용량 마이그레이션, 스키마 변경 작업이 실패하는 경우가 많습니다.
3. 트랜잭션 처리 중 잠금(Lock)이 여러 테이블에 전파될 수 있습니다. 이로 인해 Deadlock(교착상태)이 발생할 확률이 높아지므로, 실무에서는 외래 키 사용에 신중을 기해야 합니다.
4. 특히, 대규모 트래픽 서비스에서는 외래 키로 인한 잠금 및 Deadlock이 성능 저하 원인이 될 수 있습니다.

이 때문에 일부 서비스는 비즈니스 로직에서 참조 무결성을 직접 관리하고, DB 스키마 차원에서는 외래 키를 사용하지 않는 경우도 있습니다.


결론 
InnoDB의 외래 키는 데이터의 논리적 무결성을 보장하는 강력한 도구이지만, 실무에서는 대용량 운영 환경, 빈번한 스키마 변경, 데이터 마이그레이션 시 예상치 못한 성능 저하, 작업 실패, Deadlock 등의 이슈를 유발할 수 있습니다. 따라서 외래 키를 설계할 때는, 운영 환경의 특성과 개발 및 관리 편의성을 모두 고려한 신중한 판단이 필요합니다.