MySQL 서버는 크게 사람의 머리 역할을 담당하는 MySQL 엔진과 손발 역할을 담당하는 Storage 엔진으로 구분할 수 있다.
MySQL 엔진
MySQL 엔진은 커넥션 핸들러, SQL 파서, 전처리기, 옵티마이저가 중심을 이룬다.
- 커넥션 핸들러: 클라이언트로부터의 접속 및 쿼리 요청을 처리
- SQL 파서 및 전처리기: SQL을 분해하여 처리
- 옵티마이저: 쿼리의 최적화된 실행을 도움
Storage 엔진
Storage 엔진은 실제 데이터를 디스크 Storage에 저장하거나 디스크 Storage로부터 데이터를 읽어 오는 부분을 전담한다.
MySQL 엔진은 하나지만 스토리지 엔진은 여러개를 동시에 사용 할 수 있다.
- 종류: InnoDB, MyISAM
MyISAM
- Table Level Locking으로 CUD시 테이블 전체 락이 걸려 Write가 많은 서비스에 불리
- 소수의 사용자들이 데이터를 생성하고 유저들이 읽기만 하는 서비스에 유리
- 트랜잭션 및 외래 키 제약조건 미지원
InnoDB
- 프라이머리 키에 의한 클러스터링:
- InnoDB의 모든 테이블은 기본적으로 프라이머리 키를 기준으로 클러스터링 되어 저장된다.
- 프라이머리 키가 클러스터링 인덱스이기 때문에 프라이머리 키를 통한 레인지 스캔의 속도가 매우 빠르다는 장점을 지닌다.
- 트랜잭션 (Transaction) 지원:
- InnoDB는 ACID(원자성, 일관성, 고립성, 지속성)를 지원하는 트랜잭션 안전(transaction-safe) 스토리지 엔진이다.
- 트랜잭션을 사용하여 데이터의 무결성을 보장하며, 롤백(Rollback)과 커밋(Commit) 기능을 지원한다.
- 외래 키 (Foreign Key) 제약:
- 외래 키 제약조건을 지원하여 두 테이블 간의 관계를 정의하고 무결성을 유지할 수 있다.
- 이로 인해, 부모 테이블과 자식 테이블에도 인덱스가 생기고 LOCK이 여러 테이블로 전파되는데, 이로 인한 데드락 발생 확률이 높기 때문에 주의해야 한다.
- 행 레벨 잠금 (Row-level Locking):
- InnoDB는 행 레벨의 잠금을 제공하여 동시성을 향상시킨다.
- 이로 인해 여러 트랜잭션이 동일한 테이블의 다른 레코드에 동시에 접근할 수 있습니다.
- MVCC(Multi Version Concurrency Control):
- 레코드 레벨의 트랜잭션을 지원하는 DBMS가 제공하는 기능이며, 잠금을 사용하지 않는 일관된 읽기를 제공하는데 목적이 있다.
- InnoDB에서는
언두 로그(undo log)를 이용해 해당 기능을 구현한다.
- UPDATE 문이 실행되면 InnoDB의 버퍼 풀은 새로운 값으로 변경되고, 기존 값은 언두 로그에 복사된다.
- 아직 COMMIT, ROLLBACK이 되지 않은 상황에서 해당 레코드를 조회하면 버퍼 풀이 아닌 언두 로그의 기존 값을 조회한다. (이는 격리 수준이 READ_COMMITED 이상일 때만 해당한다.)
- 즉, 이런 식으로 하나의 레코드에 대해 여러 버전이 유지되고, 필요에 따라 다른 버전을 사용하는 것이
MVCC이다. COMMIT을 하면 버퍼 풀에 있는 변경 내역을 디스크에 반영하여 영구적으로 만든다. 이 때, 언두 로그의 데이터가 바로 삭제되지는 않고, 기존 데이터를 필요로 하는 트랜잭션이 없을 때 삭제된다.ROLLBACK을 하면 언두 로그에 있는 기존 데이터를 버퍼 풀로 다시 복구하고, 언두 로그의 데이터를 삭제한다.
- Non-Locking Consistent Read(잠금 없는 일관된 읽기):
- InnoDB Storage 엔진은 MVCC 기술을 사용해 잠금을 걸지 않고 읽기 작업을 수행한다.
- 격리 수준이
SERIALIZABLE을 제외하고는 순수한 SELECT 작업은 다른 트랜잭션의 변경 작업과 관계 없이 항상 LOCK을 대기하지 않고 바로 실행된다.
Handler API
Handler API는 MySQL 엔진에서 Storage 엔진에게 데이터 쓰기 또는 읽기 요청할 때 사용하는 API이다.
- MySQL 엔진과 스토리지 엔진 둘 사이의 데이터를 주고받을 수 있도록 해준다.
- Handler API를 통해 어떠한 작업이 있었는지 조회할 수 있다.
쿼리 동작 방식
1. 쿼리 캐시(Query Cache)
- SQL의 실행 결과를 메모리에 캐시하고, 동일 쿼리가 실행되면 테이블을 읽지 않고 즉시 결과를 반환한다.
- 속도가 매우 빨랐으나, 테이블의 데이터가 변경되면 캐시에서 변경 전 데이터를 모두 삭제해야 했기 때문에 동시성과 관련하여 성능 저하가 많이 발생했다.
- 이러한 성능 저하와 많은 버그로 인해
MySQL 8.0 부터는 제거되었다.
2. 쿼리 파서(Query Parser)
- 요청으로 들어온 SQL 쿼리를 토큰(MySQL이 인식하는 최소 단위)로 분리해 파서 트리로 만든다.
- 기본 SQL 문법과 관련된 오류를 이 과정에서 발견한다.
3. 전처리기(Preprocessor)
- 파서 트리를 기반으로 쿼리 문장에 구조적인 문제점이 있는지 확인한다.
- 테이블 이름, 컬럼 이름, 내장 함수와 같은 개체를 매핑해 해당 객체의 존재 여부와 접근 권한 확인한다.
4. 옵티마이저(Query Optimizer)
- 사용자의 요청으로 들어온 쿼리를 저렴한 비용으로 빠르게 처리하는 역할을 담당한다.
- 옵티마이저 종류
비용 기반 최적화(Cost-based Optimizer, CBO)- 현재 대부분의 DBMS가 선택하고 있는 방식이다.
- 쿼리를 처리하기 위한 여러 가능한 방법을 만들고, 각 단위 작업의 비용 정보와 실행 계획별 비용을 산출한다.
- 산출된 실행 방법 별로 비용이 최소로 소요되는 처리방식을 선택하여 쿼리를 실행한다.
규칙 기반 최적화(Rule-based Optimizer, RBO)- 대상 테이블의 레코드 건수나 선택도 등을 고려하지 않고 옵티마이저에 내장된 우선순위에 따라 실행 계획을 수립한다.
- 따라서 항상 같은 쿼리에 대해서는 같은 실행 방법을 만드는데, 데이터의 분포도가 다양하므로 규칙 기반에는 한계점이 존재한다.
- 최적화 작업 예시
- 불필요한 조건 제거 및 복잡한 연산의 단순화
- 여러 테이블의 조인이 있으면 어떤 순서로 테이블을 읽을지 결정
- 각 테이블에서 사용된 조건과 인덱스 통계 정보를 이용해 사용할 인덱스 결정
- 가져온 레코드들을 임시 테이블에 넣고 다시 한번 가공해야 하는지 결정
5. 실행 엔진(Query Execution Engine)
- 옵티마이저와 핸들러 사이에서 역할을 수행하는 것으로, 옵티마이저에 의해 만들어진 실행 계획에 따라 Storage 엔진에게 요청한다.
- 또한 각 핸들러에게 요청해서 받은 결과를 또 다른 핸들러 요청의 입력으로 연결한다.
6. 스토리지 엔진(Storage Engine) = Handler
- 실행 엔진의 핸들러 API 요청에 따라, 데이터를 디스크로 저장하거나 디스크로부터 읽어오는 작업을 수행한다.
