MySQL

MySQL

정의

• RDBMS (Relational Database Management System)
  • 오픈 소스 기반의 관계형 데이터베이스 관리 시스템으로 데이터 간의 관계를 표 형식으로 정의하여 관리하는 소프트웨어이다. 전 세계적으로 가장 높은 점유율을 기록하는 오픈 소스 데이터베이스 중 하나이며 뛰어난 성능과 안정성을 바탕으로 다양한 웹 서비스의 백엔드 시스템에서 핵심적인 역할을 수행한다.

핵심 개념

• SQL (Structured Query Language)
  • 데이터베이스와 상호작용하기 위해 설계된 표준화된 질의 언어이다. 데이터의 정의, 조작, 제어 기능을 수행하며 표준 SQL 규격을 준수하면서도 MySQL만의 최적화된 확장 기능을 제공하여 개발 편의성을 높인다.
• 테이블과 스키마
  • 데이터를 저장하는 논리적 단위인 테이블과 이러한 테이블들의 구조 및 관계를 정의하는 스키마가 존재한다. 행과 열로 구성된 구조적 설계를 통해 데이터의 정형성을 유지하며 기본 키와 외래 키를 활용하여 데이터 간의 논리적 연결성을 보장한다.
• 트랜잭션과 ACID
  • 데이터베이스의 상태를 변화시키는 하나의 논리적 작업 단위를 의미하며 원자성, 일관성, 격리성, 지속성이라는 네 가지 원칙을 엄격히 준수한다. 이는 예기치 못한 시스템 오류나 동시 접근 상황에서도 데이터의 무결성과 신뢰성을 유지하게 하는 근간이 된다.

주요 특징

• InnoDB 스토리지 엔진
  • MySQL의 기본 엔진으로서 행 단위 잠금과 외래 키 제약 조건을 지원하여 높은 동시성 처리를 가능하게 한다. 장애 발생 시 데이터를 복구하는 저널링 메커니즘을 내장하고 있어 대규모 트래픽이 발생하는 엔터프라이즈 환경에 적합하다.
• 확장성 및 고성능
  • 마스터-슬레이브 구조의 복제 방식을 통해 읽기 부하를 분산하고 서비스 가용성을 확보한다. 인덱싱 기법과 쿼리 캐시 등의 기능을 통해 대용량 데이터 환경에서도 신속한 검색 성능을 유지한다.
• 유연한 호환성
  • 거의 모든 운영체제에서 구동 가능하며 다양한 프로그래밍 언어를 위한 전용 커넥터를 제공한다. 대다수의 클라우드 플랫폼에서 관리형 서비스로 제공되므로 인프라 구축과 운영의 효율성이 뛰어나다.

---

여담

• MySQL 이름의 유래

  • MySQL이라는 이름은 마이클 몬티 와이드니어스의 개인적인 배경에서 시작되었다.
  • 이름의 앞부분인 My는 와이드니어스의 첫째딸 이름인 My에서 따온 것이다. 흔히 나의 데이터베이스라는 의미의 영어 대명사 My로 생각하긴 쉽지만, 실제로는 자녀의 이름을 붙인 재밌는 사례이다.
  • 그 밖에도 마이클 와이드니어스는 이후 다른 프로젝트를 진행할 때도 가족의 이름을 활용하였다.
    둘째 딸 마리아의 이름을 딴 MariaDB, 아들 맥스의 이름을 딴 MaxDB 등이 그러하다.

MySQL 내부 구조

MySQL 캐시 및 버퍼 시스템의 구조와 원리

• 서버 계층(Server Layer)의 캐시 관리

  • Query Cache (Deprecated)

    • 쿼리의 결과값 자체를 메모리에 저장하여 동일한 요청 시 즉각 반환하던 기능이다. 하지만 테이블 데이터가 1바이트만 수정되어도 관련 캐시가 모두 삭제되는 비효율성과 락(Lock) 경합 문제로 인해 MySQL 8.0 버전에서 완전히 제거되었다.

  • Table Open Cache & Definition Cache

    • 오픈된 테이블의 핸들러 정보와 테이블 구조(Metadata)를 메모리에 보관한다. 이를 통해 매번 디스크에서 테이블 정의 파일을 읽어오는 비용을 절감한다.

    • 쉽게 요약하자면, 리소스 효율성으로 맞춰진 캐시들이다.

    • Table Open Cache

      • MYSQL은 멀티 스레드 방식이다. 여러 사용자가 동시에 접근할 때, 각 스레드가 테이블을 제어하기 위핸 헨들러 객체를 가진다.
      • 파일 시스템의 File Descriptor을 열고 닫는 비용을 줄이기 위해 이 객체들을 메모리에 유지하는 공간이다.

    • Table Definition Cache

      • 테이블의 구조 정보(스키마 정보)를 담고 있다.
      • 쿼리를 실행할 때마다 테이블에 어떤 칼럼이 있는지 매번 Parsing하면 느리기 때문에, 그 정보를 메모리에 올리는 것이다.

• InnoDB 엔진의 핵심, Buffer Pool

  • Data Page Caching

    • 디스크의 데이터 파일에서 읽어온 데이터 페이지를 메모리에 상주시킨다. 사용자의 조회 요청이 있을 때 디스크가 아닌 메모리에서 먼저 데이터를 찾음으로써 I/O 성능을 극대화한다.

  • LRU (Least Recently Used) List

    • 한정된 메모리 자원을 관리하기 위해 최근에 가장 사용되지 않은 데이터를 방출하는 알고리즘이다. MySQL은 대량 스캔 작업이 버퍼 풀을 오염시키는 것을 방지하기 위해 리스트를 Old와 New 영역으로 나누어 관리하는 변형 LRU를 사용한다.

    • MySQL의 LRU List 기법은 일반적인 LRU 원리에서 Midpoint Insertion Strategy라는 영리한 기법을 더한 방식이다.

    • 기존 LUR 기법을 사용하면 발생할 수 있는 문제

      • 어떤 개발자가 실수로 엄청나게 큰 테이블을 전체 조회했다고 가정한다.
      • 일반 LRU라면 기존에 자주 쓰던 중요한 데이터들이 이 일회성 데이터들 때문에 메모리에서 다 쫒겨나게된다.
      • 이를 버퍼 풀 오염(Buffer Pool Pollution)이라고 부른다.

    • LRU List의 방식

      • 리스트를 New와 Old 영역으로 나뉜다.
      • 새로 읽어온 데이터는 일단 Old 영역의 머리(Midpoint)에 넣는다.
      • 여기서 실제로 자주 사용된다는 것이 증명되어야만 New 영역으로 승격시킨다.
      • 즉, 일회성 데이터로부터 중요한 캐시 데이터를 보호하는 안전장치이다.

  • Change Buffer

    • 변경해야 할 보조 인덱스 페이지가 버퍼 풀에 없을 때, 이를 즉시 디스크에서 읽어와 수정하는 대신 임시로 저장하는 메모리 공간이다.
    • 이후 시스템이 한가하거나 해당 페이지를 읽어야 할 때 실제 데이터와 병합한다.

• 성능 가속을 위한 인덱스 및 로그 버퍼

  • Adaptive Hash Index
    • 사용자가 직접 생성하는 인덱스가 아니라, InnoDB가 자주 검색되는 검색 패턴을 파악하여 메모리에 자동으로 생성하는 해시 인덱스다. B-Tree의 탐색 단계를 줄여 특정 데이터에 접근하는 속도를 획기적으로 높인다.
  • Log Buffer
    • 데이터의 변경 이력을 기록하는 Redo Log를 디스크에 쓰기 전 임시로 저장하는 공간이다. 트랜잭션이 커밋될 때 로그 버퍼의 내용을 디스크에 기록함으로써 쓰기 작업의 응답 속도를 향상시킨다.

• 운영체제 및 하드웨어와의 상호작용

  • OS File System Cache
    • 운영체제 수준에서 제공하는 페이지 캐시이다. 다만 InnoDB는 이중 버퍼링으로 인한 메모리 낭비를 막기 위해 대개 Direct I/O 설정을 사용하여 OS 캐시를 거치지 않고 직접 디스크와 통신하는 방식을 선호한다.
  • Doublewrite Buffer
    • 페이지를 디스크에 기록할 때 하드웨어 결함 등으로 인해 페이지가 일부만 기록되는 현상(Partial Page Write)을 방지하기 위한 안전장치다. 실제 데이터 파일에 쓰기 전 별도의 버퍼 공간에 먼저 기록하여 데이터 무결성을 보장한다.

MySQL 데이터 조회 및 캐싱 프로세스

• MySQL이 데이터를 처리하는 과정은 크게 다음과 같은 흐름을 따른다.

• 동작 원리

  • 사용자 요청 -> 쿼리 파싱 및 최적화 -> Buffer Pool 탐색 (Memory) -> 데이터 페이지 존재 여부 확인 -> (없을 시) Disk I/O 호출 -> Buffer Pool 적재 -> 결과 반환

MySQL 아키텍처의 계층 구조 및 역할

• MySQL 엔진

  • 정의

    • MySQL 데이터베이스의 두뇌 역할을 하는 서버 계층을 의미한다.
    • 전략과 가공의 영역으로 엔진에게 스토리지 엔진에게 명령을 내리는 역할과 엔진이 가져온 데이터를 최종 결과물로 가공하는 역할을 한다.

  • 핵심 역할

    • 클라이언트 접속 관리
    • SQL 분석(Parser)
    • 쿼리 최적화(Optimizer)
    • 권한 체크 등 담당.

  • 구분

    • 데이터를 어떻게 저장하고 읽을지를 고민하는 스토리지 엔진(InnoDB, MyISAM) 등)과는 명확히 구분되는 관리자 역할이다.

  • 커넥션 핸들러

    • 커넥션 핸들러는 클라이언트가 DB에 접속할 때 가장 먼저 만나는 문지기 역할이다.
    • 역할
      1. 클라이언트의 접속 요청을 받고 ID/PW를 검증한다.
      2. 검증된 사용자에게 스레드(Thread)를 할당하여 쿼리를 처리할 수 있는 통로를 열어준다.
      3. 동시에 너무 많은 접속이 들어오지 않도록 커넥션 개수를 조절하는 교통 정리 역할도 수행한다.

  • SQL 파서(SQL Parser)

    • SQL 파서는 클라이언트가 작성한 SQL 문법이 맞는지, 글씨가 틀렸는지 검사하는 기능이다.
    • 우리가 작성하는 SQL은 결국 하나의 문장(String)일뿐이다. 컴퓨터는 이를 통쨰로 이해하지 못한다.
    • Parser(해석)는 이 문장을 잘게 쪼개서 Parse Tree라는 구조로 만든다.
    • 토큰화
      • SELECT, *, FROM, User 등 키워드 단위로 문장을 분리한다.
    • 문법 검사(Syntax Check)
      • 예를 들어 SELECT를 SELECTT라고 오타를 냈다면, 여기서 바로 문법이 틀렸다는 에러를 던진다.

  • 전처리기(Preprocessor)

    • 전처리기는 의미 검사이다.
    • 파서가 해당 문장이 문법적으로 문제가 없다는 승인을 해주면, 전처리기가 해당 문장을 이어 받아 2차 검증에 들어간다.
    • 존재 여부 확인
      • 해당 쿼리 데이터(테이블/컬럼)가 진짜 DB에 있는지 검증한다.
    • 권한 확인
      • 해당 쿼리를 날린 사용자가 이 테이블을 볼 수 있는 권한이 있는 사람인지 등을 체크한다.

  • 옵티마이저

    • 옵티마이저는 쿼리를 실행하는 수만 가지 방법 중 가장 비용(시간/자원)이 적게 드는 방법을 찾는 역할을 한다.

    • 옵티마이저는 단순히 오타를 고치거나 공백을 없애는 사소한 수준을 넘어, 결과값만 같다면 과정은 완전히 뜯어고쳐도 된다는 원칙 아래 움직인다.

    • 동작 원리

      • 인덱스 선택
        • A 인덱스를 쓰는게 빠즐지 B 인덱스를 쓰는 게 빠를지 결정한다.
      • 조인 순서
        • 여러 테이블을 합칠 때 어떤 테이블을 먼저 읽는게 효율적인지 계산한다.
      • 통계 정보 활용
        • 테이블에 데이터가 몇 건 있는지 등의 통계를 보고 실행 계획(Execution Plan)을 세운다.

    • SQL문은 개발자가 짜는 것일텐데 어떻게 최적화한 다는 말일까?

      • SQL은 무엇을(What)가져올지만 말하는 선언적 언어이다.
      • 사용자의 SQL이 서울역에서 강남역까지 가고싶다.고 작성하면 옵티마이저는 지금은 퇴근 시간이니까 지하철 2호선이 가장 빠를 것이다. 교통체증이 심해서 도로는 막을 것이다. 비용을 계산해봐야겠다.는 어떻게(How)의 역할을 한다.

    • 논리적 최적화 (Query Transformation)

      • 사용자가 작성한 SQL을 논리적으로 더 효율적인 형태로 바꾼다.
      • 예시
        • WHERE 1=1 AND status = 'active' 같은 조건이 있다면, 1=1은 항상 참이니까 아예 삭제해 버린다.
      • 복잡하게 서브쿼리로 짜놓은 걸 "이건 조인으로 푸는 게 훨씬 빠르겠는데?" 하고 내부적으로 구조를 변경한다.

    • 물리적 최적화 (Cost-based Optimization, CBO)

      • 가장 핵심적인 단계 비용(Cost)을 계산한다. 여기서 비용이란 CPU 사용량과 디스크 I/O 횟수를 의미한다.
      • 통계 정보 활용
        • 옵티마이저는 평소에 테이블의 데이터가 총 몇 건인지, 특정 칼럼에 중복된 값이 얼마나 많은지 등의 통계를 수집한다.
      • 계산기 두드리기
        • 방법 A : 인덱스를 타고 10건만 골라내기 (비용 10)
        • 방법 B : 인덱스 없이 전체 100만 건을 다 흟기 (비용 1,000,000)
        • 옵티마이저는 이 비용 계산 결과에 따라 방법 A를 선택한다.

• 캐시 및 버퍼가 위치하는 계층

  • MySQL엔진(서버 계층)
    • 쿼리 결과를 저장하던 쿼리 캐시(현재는 삭제), 테이블 구조를 담는 테이블 캐시 등이 있다.
  • 스토리지 엔진 계층(Inno DB 등)
    • 실질적으로 가장 중요한 InnoDB Buffer Pool(데이터 페이지 캐시)와 Change Buffer가 위치한다.

• 스토리지 엔진 계층

  • 정의

    • 데이터를 물리적인 장치(디스크, 메모리)에 실제로 저장하고 읽어오는 것을 전담하는 모듈을 의미한다.
    • MySQL의 가장 큰 특징 중 하나는 플러그인 아키텍처이다.
    • 마치 자동차의 엔진을 상황에 따라 가솔린, 디젤, 전기 엔진으로 갈아 끼울 수 있듯이, 개발자가 필요에 따라 InnoDB, MySAM 등 다양한 스토리지 엔진을 선택해서 사용할 수 있다. 현재 현업에서는 InnoDB를 표준으로 사용한다.

  • 핵심 역할

    • 물리적인 데이터 읽기/쓰기 (I/O 관리)
    • Index 관리
    • 트랜잭션 관리(ACID 준수)
    • 잠금(Locking)과 동시성 제어

  • 물리적인 데이터 읽기/쓰기 (I/O 관리)

    • 두뇌역할을 하는 MySQL 엔진이 이름이 김철수인 사람을 찾아줘라는 요청을 하면 실무자 역할을 하는 스토리지 엔진이 하드디스크나 메모리에서 해당 데이터가 담긴 페이지를 찾아낸다.

  • Index 관리

    • 인덱스가 실제로 어떻게 저장되고 관리되는지 스토리지 엔진의 몫이다.
    • 예시로 InnoDB는 B+Tree 구조를 사용하여 인덱스를 관리하며, 데이터 자체가 인덱스 순서대로 저장되는 클러스터링 인덱스 기능을 제공한다.

  • 트랜잭션 관리(ACID 준수)

    • 비즈니스에서 가장 중요한 데이터의 안전성을 책임진다.
    • 송금은 성공했는데 내 계좌의 잔액이 그대로인 상황 같은 대참사가 일나지 않도록, 여러 작업을 하나로 묶어 처리(Commit)하거나 문제가 생기면 되돌리는(Rollback) 기능을 수행한다. 이를 위해 Undo Log(되돌리기용)와 Redo Log(복구용)를 매우 디테일하게 기록한다.

  • 잠금(Locking)과 동시성 제어

    • 여러 사용자가 동시에 같은 데이터를 수정하려고 할 때 발생하는 충돌을 막는다.
    • InnoDB의 강점
      • 테이블 전체를 잠그지 않고, 수정 중인 특정 행(Row)만 잠그는 레코드 수준 잠금을 지원한다.
      • 덕분에 수천 명이 동시에 접속해도 서비스가 느려지지 않고 원할하게 돌아간다.

  • 엔진의 종류 및 선택

    • InnoDB, MyISAM 등 다양한 엔진을 필요에 따라 선택할 수 있으며 테이블 생성 시 별도로 지정이 가능하다.

  • 데이터 핸들링

    • 상위 계층인 MySQL 엔진으로부터 전달받은 요청에 따라 물리적인 스토리지에 데이터를 쓰거나 조회한다.

• 핸들러 API

  • 정의
    • MySQL 엔진이 스토리지 엔진에게 명령을 내릴 때 사용하는 표준화된 함수 인터페이스이다.
  • 계층 간 독립성
    • 이 API 덕분에 상위 서버 계층은 하위 스토리지 엔진의 구체적인 구현 방식을 몰라도 명령을 내릴 수 있는 구조적 분리가 가능해진다.
  • 작동 원리
    1. MySQL 엔진이 쿼리를 분석한 뒤 데이터를 한줄 읽어라는 명령을 한다.
    2. 이때 특정 엔진의 언어가 아닌, 공용어인 Handler API를 통해 명령을 전달한다.(ha_index_read, ha_write_row)
    3. InnoDB든 MyISAM이든 이 API 규격만 맞춰두면, MySQL 엔진은 상대가 누구든 상관없이 명령을 내릴 수 있다.

MySQL 테이블 핸들러

• 정의
  • 테이블 핸들러는 특정 테이블에 다루기 위해 각 스레드(작업자)가 하나씩 빌려 쓰는 전용 도구이다.
  • A라는 테이블에서 데이터를 가져와라는 명령을 내릴 때, 직접 하드디스크를 뒤지는 대신, A 테이블 전용 핸들러를 호출한다.
  • 여기서 전용은 단순히A 테이블에는 A 핸들러만 있다.는 뜻을 넘어 A 테이블을 작업하는 1번 작업자에게는 1번 전용 핸들러를 준다.는 의미에 가깝다.
  • 이유
    • 동시성
      • 만약 수많은 작업자(Thread)가 단하나의 핸들러 객체를 공유해서 쓴다면 서로의 정보가 의도치않게 덮어버리는 대참사가 발생할 수 있다.
    • 상태 괸리
      • 핸들러 안에는 현재 내가 어디까지 읽었는지(Corsor), 어떤 데이터를 잠그고 있는지(Locking) 같은 아주 사적인 정보들이 담겨져 있다.
      • 그래서 각 작업자마다 해당 테이블에 접근할 때 자신만의 전용 해들러 객체를 가지는 것이다.

MySQL 캐시 관리 전략 및 아키텍처

버퍼 풀(Buffer Pool)

• 원리

  • 데이터베이스의 실제 데이터는 하드디스크에 저장되어 있다. 하지만 디스크는 메모리보다 수천 배 느리다.
  • 그래서 자주 쓰는 데이터를 메모리의 일정 공간(버퍼 풀)에 미리 올려두고 거기서 읽고 쓰는 작업을 처리한다.

• 캐시로서의 역할

  • 자주 쓰이는 데이터를 RAM에 위치한 고속 임시저장소에 들고 잇다가 디비 조회를 생략하고 바로 응답한다.

• 버퍼로서의 역할

  • 데이터를 쓸 때 일정한 크기까지 모아두었다가, 나중에 디스크에 한꺼번에 기록한다.
  • 디스크 I/O 횟수를 줄이기 위함이다.

• 캐시전략

  • Read-through을 채용한다.
  • 서버 엔진 -> 스토리지 엔진(버퍼 풀) -> (엔진 내부에서 알아서) 디스크 확인.

• 버퍼 풀의 데이터 적재 프로세스

1. 요청 : 서버 엔진이 데이터 조회 요청
2. 버퍼 풀 탐색 : 스토리지 엔진이 메모리 안에 데이터를 확인.
3. 캐시 히트 : 메모리가 잇으면 즉시 반환.
4. 캐시 미스 : 메모리에 없으면 스토리지 엔진에 직접 디스크 접근.
5. 적재 : 디스크에서 가져온 데이터를 버퍼 풀 빈자리(Free List)를 찾아 적재한다.
6. 반환 : 이제 메모리에 올라온 데이터를 서버 엔진에 전달.

LRU List 구조

• 버퍼 풀은 전체 리스트를 `New Sublist(자주 쓰는 데이터)와 Old Sublist(갓 들어온 데이터)로 나눈다.

• 이러한 구조를 사용하는 이유는 버퍼 풀 오염(Buffer Pool Pollution)을 방지하기 위해서이다.

• 동작 원리

  1. 새로운 데이터 페이지가 들어오면 리스트의 맨 앞이 아닌 `중간(Midpoint)에 위치시킨다.
  2. 이 데이터가 짧은 시간 안에 다시 조회되면 그때 중요한 데이터로 판단하여 New 영역으로 승격 시킨다.
  3. 반면, 한 번 읽히고 다시 찾지 않는 일회성 데이터는 Old 영역에 머물다 금방 쫒겨난다.

성능 가속을 위한 보조 메커니즘

체인지 버퍼(Change Buffer)

• 정의

  • 데이터가 변경(INSERT, UPDATE, DELETE)되면 인덱스도 함께 업데이트되어야 한다.
  • 그런데 만약 그 인덱스 정보가 담긴 페이지가 현재 메모리(Buffer Pool)에 없다면 어떻게 해야하는가?

• 원친적인 방법

  • 디스크에서 해당 인덱스 페이지를 찾아 메모리로 읽어온 뒤 수정한다.

• 체인지 버퍼 방법

  • 어짜피 지금 당장 이 인덱스를 조회할게 아니라면, Cange Buffer에 나중에 이 페이지가 오면 이 페이지를 수정하라는 메모만 남겨두는 방식이다.
  • 즉, 실제 디스크 작업을 뒤로 미루는 포스트잇 같은 기능이다.

• 왜 보조 인덱스에만 사용하는가?

  • 체인지 버퍼는 보조 인덱스(Secondary Index), 그 중에서도 중복이 허용되는 인덱스에만 작동한다.

• 왜 PK는 안되는가?

  • PK나 유니크 인덱스는 중복 체크를 해야 한다.
  • 이미 갖고 있는지 확인하려면 반드시 디스크에 해당 페이지를 열어봐야 한다.
  • 어짜피 열어볼 거라면 미룰 이유가 없기 때문에 체인지 버퍼를 쓰지 않는다.

• **미뤄둔 작업은 언제 동작하는가?(Merge 과정)

  • 메모에 적어둔 변경 사항들은 다음의 상황에서 실제 데이터 페이지에 반영(Merge)된다.
  • 사용자가 조회를 요청할 때
    • 사용자가 하필 그 인덱스 페이지를 읽으려고 디스크에서 메모리로 올리면, 체인지 버퍼가 미리 적어둔 수정 사항도 함께 합쳐서 보여주도록 병합한다.
  • 시스템이 한가할 때
    • 백그라운드 스레드가 주기적인 간격으로 병합한다.
  • 체인지 버퍼 공간이 꽉 찼을 때
    • 더 이상 메모할 공간이 없으면 강제로 병합을 시작한다.

어댑티브 해시 인덱스(Adaptive Hash Index)

• 배경

  • 앞서 배운 InnoDB의 기본 인덱스는 B+Tree 구조이다. 아무리 빨라도 루트 노드에서 리프 노드까지 여러 단계를 거쳐 내려가야 한다.
  • 수학적으로는 O(log n)의 시간이 걸린다.
  • 하지만 엄청나게 바쁜 서비스에서는 이 몇 단계의 참색조차 아까울 때가 있다.
  • 이때 등장한 것이 Hash 인덱스이다.
  • 해시는 값을 넣으면 결과가 바로 나오는 O(1) 속도를 자랑한다.

• 핵심 원리

  • 이름 그대로 어댑티브(Adaptive, 적응형)하게 작동한다.
  1. 모니터링
  • InnoDB 엔진이 인덱스 검색 패턴을 주시.
  2. 판단
  • 특정 인덱스 페이지를 사용자들이 자주찾는 패턴을 감지.
  3. 생성
  • 그 데이터 페이지의 주소값을 해시 테이블(Hash Table)에 기록.
  4. 점프
  • 다음번에 똑같은 요청이 들어오면, 복잡한 B+Tree를 타고 내려가는 과정을 생략하고 해시 테이블을 보고 메모리상의 데이터 위치로 바로 이동한다.
  • 중요 조건
    • 이 기능은 버퍼 풀에 이미 올라와 있는 데이터에 대해서만 작동한다.
    • 즉, 메모리 안에서의 속도를 극한으로 끌어올리는 기술이다.
  • 트레이드 오프
    • 효과가 좋을 때
      • WHERE id = 100처럼 딱 하나의 값을 찾는 동등 비교(Point Lookup)가 압도적으로 많을 때.
    • 오히려 독이될 때
      • • WHERE id BETWEEN 1 AND 100 같은 범위 검색이 많을 때 (해시는 범위를 모른다).
      • 데이터를 삭제/변경하는 작업이 너무 많을 때 (데이터가 바뀌면 지름길인 해시 인덱스도 계속 갱신해야 해서 오히려 오버헤드가 발생한다).
      • 잠금(Lock) 경합: 여러 작업자가 동시에 해시 테이블을 수정하려고 줄을 서게 되면 병목이 발생할 수 있다.

로그 버퍼(Log Buffer)

• 사용자가 데이터를 수정하면 MySQL(InnoDB)는 두 가지 동작을 한다.

  1. 버퍼 풀에 있는 데이터 페이지를 수정.
  2. 어떤 내용이 바뀌었는지 Redo Log라는 파일에 기록한다.

• 이때, 매번 수천 명의 사용자가 데이터를 바꿀 때마다 하드디스크에 있는 리두 로그 파일을 직접 열어서 적으려면 서버는 큰 부담을 가질 것이다.

• 그래서 메모리의 아주 작은 공간에 먼저 기록해 두는데, 이 공간이 바로 로그 버퍼이다.

• 사용하는 이유

  • Random I/O vs Sequential I/O
    • 데이터 파일 자체를 수정하는 것은 디스크의 이곳저곳을 찾아다녀야 하는 아주 느린 작업이다.
    • 반면, 리두 로그는 파일 끝에 계속 이어 붙이는 순차 쓰기(Sequential Write) 방식이라 훨씬 빠르다.
  • 사용자가 COMMIT을 누를 때마다 디스크에 매번 쓰는 것이 아니라, 로그 버퍼에 모아두었다가 한꺼번에(Batch) 디스크로 옮겨 적음으로써 디스크 부하를 획기적으로 줄인다.

• 동작 원리(WAL, Write-Ahead Logging)

  • 실제 데이터를 바꾸기 전에, 로그부터 먼저 쓰는 원리이다.
  1. 메모리 수정 : 우선 버퍼 풀에 있는 데이터 페이지를 수정한다. (아직 디스크는 그대로인 더티 페이지 상태)
  2. 로그 생성 : 동시에 변경 내용을 로그 버퍼라는 메모리 공간에 아주 작게 기록한다.
  3. 로그 플러싱(Flush) : 커밋되는 순간, 로그 버퍼의 내용을 디스크의 리두 로그 파일로 옮겨 적는다.
  4. 나중에 시스템이 갑자기 꺼져도, 디스크에 안전하게 저장된 리두 로그를 읽어서 데이터를 복구한다.

• 여담

  • 로그 버퍼는 보통 16MB 정도로 작은 크기로 설정되어 있다.(설정값: innodb_log_buffer_size)
  • 대부분 서비스에서는 이 정도면 충분하지만, 만약 운영중인 서비스에서 매우 큰 이미지나 텍스트 데이터(BLOB)를 한꺼번에 대량으로 업데이트하는 작업을 수행한다면 이 버퍼가 부족해질 수 있다.
  • 버퍼가 꽉 차면 어쩔 수 없이 디스크 쓰기가 발생해 서비스가 느려지니, 이때는 이 값을 조금 키워주는 것이 기술적인 해결책이 된다.

Inno DB 엔진

• 정의
  • MySQL의 여러 스토리지 엔진 중 하나로, 현재 가장 널리 쓰이는 Default 엔진이다.
  • 과거에는 속도가 빠른 MyISAM 엔진도 많이 썼지만, 데이터의 안전성과 동시 처리 능력이 훨씬 뛰어난 InnoDB가 5.5버전부터 가장 많이 사용하고 있다.

InnoDB의 핵심 강점 : 트랜잭션과 ACID 보장

• 금융 서비스나 이커머스에서 가장 중요한 것은 내 돈이 정확히 전달되었는가이다.

• InnoDB는 ACID(원자성, 일관성, 격리성, 지속성) 원칙을 철저히 따른다.

• Undo Log

  • 작업이 실패하면 이전 상태로 되돌린다.

• Rado Log

  • 시스템이 꺼져도 로그를 보고 데이터를 복구한다.

InnoDB의 핵심 강점 : 레코드 수준 잠금 (동시성)

• 과거의 엔진들은 한 사람이 데이터를 수정하면 테이블 전체를 자물쇠로 잠갔다.
• 하지만 InnoDB는 수정 중인 특정 행만 잠근다.
• 결과적으로 1번 사용자가 1번 글을 수정하는 동안, 2번 사용자는 대기 없이 2번 글을 수정할 수 있다.
• 대규모 트래픽 처리가 가능한 비결이다.

Non-repeatable Read 문제

• READ COMMITTED 수준에서 발생할 수 있는 현상으로, 한 트랜잭션 내에서 똑같은 SELECT 쿼리를 두 번 날렸는데 그 사이에 다른 트랜잭션이 데이터를 수정/커밋하여 결과값이 다르게 나타나는 현상을 의미한다.

InnoDB의 핵심 강점 : MVCC (Multi-Version Concurrency Control)

• 읽는 사람은 쓰는 사람을 막지않고, 쓰는 사람은 읽는 사람을 막지 않는다는 원리이다.
• 데이터를 수정 중일 때 누군가 조회를 요청하면, 수정 전의 데이터를 Undo Log에서 찾아 보여준다. 덕분에 잠금 대기 없이 쾌적한 조회가 가능하다.

InnoDB의 핵심 강점 : 외래 키(Foreign Key) 지원

• 테이블 간의 관계를 데이터베이스 수준에서 강제하여, 부모 없는 자식 데이터가 생기지 않도록 데이터의 무결성을 지켜준다.

InnoDB의 내부 구조 요약

• 메모리 구역 계층
  • Buffer Pool : 데이터와 인덱스 캐싱(가장 중요한 기능)
  • Log Buffer : 변경 내역 임시 저장(안전 장치)
  • Change Buffer : 보조 인덱스 쓰기 지연(성능 향상)
  • Adaptive Hash Index : 잦은 검색의 지름길 생성
• 디스크 구역 계층
  • System Tablespace : 데이터 및 인덱스 실제 저장
  • Redo Log Files : 복구용 기록 파일

Doublewrite Buffer

• 배경

  • 더블라이트(이중 쓰기)를 해야 하는지 이해하려면, 하드웨어 한계를 먼저 이해해야한다.
  • MySQL의 단위 : InnoDB의 데이터 페이지는 보통 16KB이다.
  • 운영체제의 단위 : 하지만 OS가 파일을 쓰는 단위는 보통 4KB이다.
  • 문제는 MySQL이 16KB짜리 페이지 하나를 디스크에 쓰려고 할 때, OS 입장에서는 4KB씩 총 4번을 나누어 써야 한다.
  • 그런데 만약 2번(8KB)까지 딱 썼을 때, 갑자기 정전이되거나 천재지변이 일어나면 어떻게 될 것인가? 해당 페이지는 앞부분은 새 데이터고 뒷부분은 옛날 데이터인 깨진 데이터(Torn Page)가 되어버린다.
  • 이건 Redo Log로도 복구하기 힘든 아주 치명적인 상태이다.

• 해결책 : Doublewrite Buffer

  • 이 깨진 페이지 문제를 막기 위해 InnoDB는 진짜 장부에 적기 전에 연습장에 먼저 한번더 적어두자는 전략을 취한다.
  • 연습장에 쓰기
    • 버퍼 풀에 있는 더티 페이지를 실제 데이터 파일에 쓰기 직전에, 시스템 테이블스페이스의 Doublewrite Buffer라는 공간에 통째로 묶어서 기록한다.
  • 진짜 장부에 쓰기(Actual Data File)
    • 연습장에 안전하게 기록되었다면, 이제 실제 데이터 파일(.bd)의 원래 위치에 페이지를 하나씩 기록한다.

• 복구 과정

  • 만약 진짜 장부에 적는 도중 서버가 꺼져서 데이터가 깨졌다고 하더라도, InnoDB는 데이터를 읽다가 해당 페이지가 깨졌다는 것을 감지한다.
  • 즉시, Doublewrite Buffer를 확인하며, 쓰기 직전에 복사해둔 온전한 16KB 페이지를 가져오며 실제 데이터 파일에 덮어씌워 복구한다.

• 참고

  • 두 번 쓰면 속도도 그만큼 반감되는게 아닌가 생각할 수 있지만, Doublewrite Buffer에 저장할 때는 모아서 한번에 순차 쓰기를 하므로 생각보다 빠르다.
  • 하지만 결국 데이터를 두번 쓰는 것은 마찬가지기 때문에, IO 부하가 약간 증가한다. 그래서 일부 현업에서는 성능 향상을 위해 이 기능을 끄기도 한다.
  • 다만, 일반적인 환경에서는 데이터를 안전성을 위해 켜두는 것이 현업의 표준이라고 한다.

Data Dictionary Cache

• 우리가 어떤 테이블에 접근하려면, 그 테이블이 어떻게 생겼는지 알아야 한다.(컬럼 이름, 타입, 인덱스 등)
• 정의
  • 테이블의 구조 정보(Metadata)를 메모리에 저장해두는 공간.
• 역할
  • 과거 MySQL은 테이블 정보를 .frm이라는 파일에 저장해서 매번 읽어왔다. 하지만 MySQL 8.0부터는 이를 DB 내부의 시스템 테이블에 저장하고, 자주 쓰는 정보는 Dictionary Cache에 올려둔다.

Table (Open) Cache

• 정의

  • 실제로 열려있는 테이블의 핸들러 객체를 담아두는 공간.

• 역할

  • 테이블을 실제로 열어서 데이터를 읽으려면 OS로부터 파일 오픈 권한을 받고 메모리를 할당하는 등의 복자한 과정이 필요하다.
  • Table Cache는 이 과정이 완료된 준비된 상태의 핸들러를 보관한다.

• 참고

  • 대형 서비스에서는 수만 개의 테이블이 존재할 수 있다.

  • 속도

    • 이 캐시들이 작으면 쿼리를 날릴 때마다, 멈칫거리는 현상이 발생한다.

  • 동시성

    • table_open_cache는 앞서 언급한 대로 스레드별로 핸들러를 가질 수 있게 넉넉해야 한다. 접속자가 폭주하는데 이 캐시가 작으면, 핸들러를 새로 만드느라 CPU 점유율이 치솟을 수 있다.

  • 시스템 모니터링 대시보드를 볼 때 Opened_tables라는 지표를 확인해보자. 이 숫자가 실시간으로 계속 빠르게 올라가고 있다면, 현재 Table Cache 크기가 너무 작아서 DB가 계속 테이블을 새로 열고 닫느라 부하가 쌓여 병목이 발생하는 증거이다.

  • 이때 캐시 히트율이 낮아보이는데, table_open_cache 설정을 검토해 보는게 어떤가 생각을 해보자.

Query Cache

• 정의

  • 쿼리 캐시는 SQL 문장 자체와 그 실행 결과를 통째로 메모리에 저장해두는 기능이다.

• 동작 원리

  • 사용자가 SELECT * FROM users WHERE id=1이라는 쿼리를 날리면, DB는 이 문자을 키(Key)로, 결과 데이터를 값(Value)으로 메모리에 저장한다.

• 재요청 시

  • 똑같은 쿼리가 들어오면 SQL 파싱, 최적화, 스토리지 엔진 접근 과정을 전부 생략하고 메모리에 있는 결과를 즉시 돌려준다.

• 현재 사용하지 않는 이유

  • 이론적으로는 좋은 기능 같지만, 실제 대규모 서비스(High Traffic) 환경에서는 세 가지 치명적인 문제가 있다.
  1. 단 한줄만 바뀌어도 전부 삭제 (Invalidation)
  • 쿼리 캐시는 매우 예민하다.
  • 해당 테이블에 단 1개의 데이터만 추가(INSERT)되거나 수정(UPDATE)되어도, 그 테이블과 관련된 모든 캐시 데이터가 통쨰로 삭제된다.
  2. 토씨 하나까지 똑같아야 한다. (Strict Matching)
  • 쿼리 캐시는 쿼리 문장을 문자열 그대로 비교한다.
    • SELECT * FROM users (대문자)
    • select * from users (소문자)
    • SELECT * FROM users (공백 2개)
  • 위 세 쿼리는 결과가 같지만, 쿼리 캐시 입장에서는 서로 다른 쿼리로 인식하여 중복 저장하거나 캐시를 타지 못한다.
  3. 동시성의 병목 (Global Lock)
  • 가장 큰 기술적 결함이다.
  • 캐시를 확인하거나 업데이트할 때 쿼리 캐시 전체에 글로벌 락을 건다.
  • 즉, 한 사람이 캐시를 쓰는 동안 다른 모든 사람은 줄을 서서 기다려야 한다.
  • 사용자가 많아질수록 이 대기 시간 때문에 오히려 시스템 전체가 느려지는 역효과가 발생한다.

• 쿼리 캐시 VS 버퍼 풀

  • 쿼리 캐시와 InnoDB 버퍼 풀을 햇갈려햔다고 종종 이야기가 들리곤 했다.

  • 하지만 둘은 완전히 다른 계층이다.

  • 쿼리 캐시

    • 위치 : MySQL 엔진 계층 (상위)
    • 저장 단위 : 쿼리 문장 + 결과 집합
    • 효율성 : 데이터 변경 시 전체 삭제 (낮음)
    • 현재 상태 : MySQL 8.0에서 완전히 삭제

  • 버퍼 풀

    • 위치 : 스토리지 엔진 계층 (하위)
    • 저장 단위 : 16KB 데이터 페이지
    • 효율성 : 필요한 페이지만 교체(높음)
    • 현재 상태 : 성능 최적하의 핵심

• 참고

  • 그렇다면 현업에서는 쿼리 캐시를 쓰지 않고 무엇을 대신 사용할까?
  • 애플리케이션 레벨 캐싱
    • Redis나 Memcached같은 전문 캐시 서버를 DB 앞단에 두어, 개발자가 직접 필요한 데이터만 정교하게 캐싱한다.
  • 버퍼 풀 최적화
    • 쿼리 캐시가 하던 일을 버퍼 풀이 더 효율적으로 처리하도록 메모리 설정을 최적화한다.
  • 인덱스 튜닝
    • 쿼리 자체가 워낙 빠르게 실행되도록 이덱스를 설계하여 캐시의 필요성을 낮출 수도 있다.

참고 사항

• 핸들러 객체

  • MySQL은 크게 쿼리 파서(두뇌), 스토리지 엔진(손발)으로 나뉜다.
  • 이때 핸들러 객체는 두뇌가 손발에게 명령을 내릴 때 사용하는 전용 도구(Interface)라고 이해하면 된다.
  • 역할
    • 데이터 페이지에 5번행을 읽어와라고 명령하면, 핸들러가 실제 스토리지 엔진(InnoDB 등)에 접근해 데이터를 가져온다.
  • 왜 캐시를 하는가?
    • 매번 쿼리가 들어올 떄마다 이 도구를 새로 만들면 비용이 든다.
    • 그래서 미리 만들어둔 도구(핸들러 객체)를 Table Open Cache에 보관했다가 재사용하는 것이다.

• File Descriptor(FD)

  • 프로그래밍 언어를 넘어 운영체제 수준의 개념이다.
  • 프로세스(MySQL)가 특정 파일에 접근하기 위해 OS로부터 부여받은 번호표같은 것이다.
  • DB는 수많은 데이터 파일에 접근해야 한다. OS는 한 프로세스가 가질 수 있는 FD 개수를 제한하기 때문에, 이를 효율적으로 관리하지 못하면 Too many open files라는 유명한 에러를 마주하게 된다.

• Data Page

  • DB가 데이터를 관리하는 최소 단위의 박스
  • 예를들어 DB에 이름 하나만 가져오고 싶지만, MYSQL(InnoDB 엔진)은 하드디스크에서 데이터를 읽어올 때 한 바이트씩 가져오지 않는다. 보통 16KB 크기의 페이지 단위로 통째로 들고 온다.

• 보조 인덱스(Secondary Index)

  • CREATE INDEX로 추가하는 일반 인덱스들을 의미한다.
  • PK인 클러스터링 인덱스는 제외.

• 클러스터링 인덱스

  • 데이터 자체가 인덱스 순서대로 줄을 서서 저장되는 방식이다.
  • 보통의 인덱스(보조 인덱스)가 책 뒷면에 있는 찾아보기라면, 클러스터링 인덱스는 책 내용 그 자체가 특정 순서로 정렬되어 있는 것과 같다.
  • 물리적 정렬
    • 데이터가 디스크에 저장될 때 PK 순서대로 차곡차곡 쌓인다.
  • 테이블 당 단하나
    • 물리적인 저장 순서는 하나뿐이므로, 테이블당 오직 하나의 클러스터링 인덱스만 존재할 수 있다.(보통 이 역할을 한다.)
  • 리프 노드의 비밀
    • 인덱스의 가장 끝난(Leaf Node)에 가면 데이터의 주소값이 아니라, 실제 행(Row) 데이터가 통쨰로 들어있다.
  • 장점
    • PK를 조건으로 조회하거나 범위(BETWEEN, ORDER BY) 조회를 할 때 이미 정렬되어 있기 때문에 압도적으로 빠르다.
  • 단점
    • PK 중간에 새로운 데이터를 넣으면 기존 데이터를 뒤로 밀어내야 하는 페이지 분할현상이 발생해 수정 작업이 무거워질 수 있다.

• Undo Log

  • 데이터를 변경했을 때 변경하기 전의 이전 값을 저장해두는 공간이다.

  • 주요 목적

    • *트랜잭션 롤백
      • 작업 중 취소되어 롤백을 해야할 때, 이 로그를 보고 데이터를 원래대로 되돌린다.
    • MVCC(다중 버전 동시성 제어)
      • 사용자 A가 데이터를 수정 중일 때, 사용자 B가 요청하면 수정 중인 데이터가 아닌 Undo Log에 있는 이전 데이터를 보여준다.
      • 덕분에 읽기 작업이 멈추지 않는다.

• Redo Log

  • 변경된 내용을 디스크에 안전하게 기록하기 위한로그이다.
  • 주요 목적
    • 장애 복구(Crash Recovery)
      • MySQL은 성능을 위해 메모리(Buffer Pool)에서 먼저 데이터를 바꾼다.
      • 만약 디스크에 쓰기 전에 전원이 꺼지면 메모리 데이터는 사라진다.
      • 이때 재부팅 후 Redo Log를 읽어서 다시 실행(Redo)하여 데이터를 복구한다.
    • 핵심 원리(WAL, Write-Ahead Logging)
      • 실제 데이터 파일보다 로그를 먼저 써서 안전을 확보하는 전략이다.

• 레코드 수준 잠금(Record-level Lock)

  • 데이너베이스에서 레코드(행, Row) 하나하나에 각각 락을 거는 방식이다.
  • 특징
    • 높은 동시성
      • 1번 사용자가 10번 행을 수정 중이어도, 2번 사용자는 11번 행을 아무 제약 없이 수정할 수 있다.
      • InnoDB의 강점
        • InnoDB는 이 방식을 사용하여 수천 명의 사용자가 동시에 접속해도 서로 방해하지 않고 작업을 수행할 수 있게 한다.

• Global Lock

  • MySQL 서버 전체, 즉 모든 테이블에 자물쇠를 거는 가장 큰 범위의 잠금이다.
  • SELECT를 제외한 모든 데이터 변경(INSERT, UPDATE, DELETE)과 구조 변경이 중단된다.
  • 주로 데이터베이스 전체의 일관된 백업을 받을 때 사용한다. 모든 작업을 멈춰야 스냅샷을 찍을 수 있기 때문이다.
  • 운영 중인 서비스에서 함부로 걸었다가는 전체 서비스가 마비되는 대참사가 일어날 수 있다.

참고 문헌

• mysql
• mysql
• oreilly
• youtube
• github