2001년 MySQL 3.23 버전이 출시되면서 널리 사용되기 시작했고, 지속적인 업데이트와 개선을 통해 현재까지 20년이 넘는 시간이 흘렀습니다.
MySQL의 성능을 최대한 발휘하고 안정적으로 사용하려면 설계 철학을 정확히 이해해야 하며, 이에 따라 MySQL의 논리적 아키텍처를 이해하는 것이 중요합니다. 본 문서에서는 특정 SQL 조회 문장의 실행 과정을 통해 MySQL 아키텍처 내 각 구성 요소를 자세히 설명하겠습니다.
MySQL 논리 아키텍처 개요
MySQL의 가장 중요한 특징 중 하나는 플러그형 저장 엔진 아키텍처(pluggable storage engine architecture)입니다. 이 구조는 쿼리 처리 및 시스템 작업과 데이터 저장/추출을 분리하여 설계되었습니다. 공식 문서에서 설명하는 내용은 다음과 같습니다:
The MySQL pluggable storage engine architecture enables a database professional to select a specialized storage engine for a particular application need while being completely shielded from the need to manage any specific application coding requirements.
즉, 개발자는 특정 애플리케이션 요구사항에 맞춘 전용 저장 엔진을 선택할 수 있으며, 이때 관련된 코드 변경 없이도 쉽게 적용할 수 있습니다. 서로 다른 저장 엔진 간의 기능 차이는 애플리케이션에 영향을 주지 않으며, 필요한 경우 새로운 엔진을 추가하거나 기존 엔진을 교체해도 큰 변경 없이 작동할 수 있습니다. 서버는 일관되고 사용하기 쉬운 API를 제공하여 저장 엔진의 복잡성을 숨깁니다.
MySQL 논리 아키텍처는 일반적으로 서버 계층과 저장 엔진 계층으로 나뉩니다:
- 서버 계층은 대부분의 핵심 서비스 기능을 담당하며, 연결 관리, 쿼리 파싱, 분석, 최적화, 캐시 등 모든 내장 함수(날짜, 시간, 수학, 암호화 함수 등) 및 저장 엔진 간 기능(저장 프로시저, 트리거, 뷰 등)을 포함합니다. 특히 가장 상위에 위치한 연결 관리자는 MySQL 연결과 권한 인증을 담당합니다. 이 부분은 MySQL 고유의 기능이 아닌 대부분의 클라이언트/서버 구조에서 공통적으로 사용되는 개념입니다.
- 두 번째 계층은 저장 엔진 계층입니다 (InnoDB, MyISAM, Memory 등 여러 엔진 지원). 저장 엔진은 MySQL 내 데이터의 저장 및 추출을 담당하며, 상위 서버 요청에 응답합니다. 각 엔진은 고유의 장단점이 있으며, 서로 통신할 수 없기 때문에 상황에 맞게 적절한 엔진을 선택해야 합니다. 서버는 API를 통해 저장 엔진과 통신하며, 이러한 인터페이스는 엔진 간 차이를 가려주어 쿼리 처리에 영향을 주지 않습니다. 저장 엔진 API는 "트랜잭션 시작", "기본키로 행 조회" 등의 작업을 위한 수십 가지 함수를 포함합니다.
MySQL 5.1 이하 버전에서는 기본 저장 엔진이 MyISAM이었으나, MySQL 5.5.5 이후부터는 InnoDB가 기본값으로 설정되었습니다.
연결 관리자 (Connector)
MySQL 5.7 공식 문서에 따르면 연결 관리자는 다음과 같이 정의됩니다:
MySQL Connectors provide connectivity to the MySQL server for client programs.
즉, 클라이언트 프로그램이 MySQL 서버에 연결할 수 있도록 해줍니다. 구체적으로 두 가지 작업을 수행합니다: MySQL 연결 관리와 권한 검증.
MySQL 서버에 접속하려면 일반적으로 사용자 이름과 비밀번호를 입력해야 하며, 원격 서버에 있는 경우 호스트 이름도 필요합니다:
shell> mysql -h host -u user -p
Enter password: ********
같은 머신에서 실행하는 경우 호스트 이름은 생략 가능합니다:
shell> mysql -u user -p
이 명령어는 대부분의 사용자가 익숙한 형태입니다.
위 명령어로 TCP 3-way handshake를 통해 연결이 성립되면, 연결 관리자는 사용자 이름과 비밀번호를 바탕으로 인증을 진행합니다:
- 사용자 이름 또는 비밀번호가 잘못된 경우 "Access denied for user" 오류가 발생하고 클라이언트는 종료됩니다.
- 인증이 성공하면 아래 메시지가 나타납니다:
mysql>는 MySQL이 준비되었음을 의미하며, 이제 SQL 문장을 입력할 수 있습니다.
연결 관리자는 단순히 사용자 이름과 비밀번호를 비교하는 것을 넘어서, 해당 사용자가 특정 쿼리를 실행할 수 있는 권한이 있는지도 검증합니다 (예: world 데이터베이스의 Country 테이블에 대한 SELECT 권한). 이후 연결 내 모든 권한 판단은 이 시점의 권한 정보를 기반으로 합니다. 따라서 한 사용자가 연결을 성공적으로 맺은 후에도, 다른 터미널에서 관리자 계정으로 해당 사용자의 권한을 변경해도 현재 연결에는 영향을 미치지 않습니다. 즉, 권한이 수정된 후 새로 생성된 연결만 새로운 권한을 반영합니다.
연결이 설정되면 아무런 작업 없이 대기 상태(Sleep)가 됩니다. 이 때, MySQL 연결(또는 스레드)는 항상 특정 상태를 유지하며, 그 상태는 현재 MySQL이 무엇을 하고 있는지를 나타냅니다. 현재 상태를 확인하는 방법 중 하나는 SHOW FULL PROCESSLIST 명령어이며, 결과의 Command 열이 현재 상태를 보여줍니다. 쿼리 생명 주기 동안 상태는 여러 번 변경되며, 예를 들어 Sleep 상태는 클라이언트로부터 새로운 요청을 기다리는 상태이고, Query 상태는 현재 쿼리를 실행하거나 결과를 클라이언트에 반환하는 상태입니다.
기본 설정으로, 연결이 Sleep 상태로 8시간 이상 지속되면 서버가 연결을 종료하며, 이후 해당 연결에서의 모든 작업은 실패합니다. 이 시간은 wait_timeout 매개변수로 조정할 수 있습니다.
쿼리 캐시 (Query Cache)
연결이 성공적으로 이루어졌다면, 이제 SELECT 문을 입력하여 조회할 수 있습니다. 다음 단계는 쿼리 캐시입니다.
공식 문서에 따르면:
The query cache stores the text of a SELECT statement together with the corresponding result that was sent to the client. If an identical statement is received later, the server retrieves the results from the query cache rather than parsing and executing the statement again. The query cache is shared among sessions, so a result set generated by one client can be sent in response to the same query issued by another client.
쿼리 캐시는 SELECT 문과 해당 결과를 함께 저장합니다. 이후 동일한 문장이 들어오면 다시 파싱 및 실행하지 않고 캐시에서 결과를 가져옵니다. 세션 간 공유되어 다른 클라이언트의 동일한 쿼리에도 같은 결과를 반환할 수 있습니다.
쿼리가 캐시에 HIT되면, MySQL은 결과 반환 전에 사용자 권한을 다시 확인합니다. 이 작업은 SQL 문장을 다시 파싱하지 않아도 되므로 효율적입니다. 캐시에는 접근할 테이블 정보가 이미 저장되어 있어 권한 확인이 가능합니다.
캐시의 문제점은 일관성입니다. 그러나 MySQL은 자동으로 오래된 데이터를 반환하지 않도록 설계되어 있습니다:
The query cache does not return stale data. When tables are modified, any relevant entries in the query cache are flushed.
테이블이 수정될 때 캐시 내 관련 항목은 삭제됩니다 (flushed). 이로 인해 캐시는 항상 최신 데이터를 유지합니다.
하지만 이 특성 때문에 MySQL 5.7.20부터는 쿼리 캐시 사용이 권장되지 않으며, MySQL 8.0에서는 완전히 제거되었습니다:
The query cache is deprecated as of MySQL 5.7.20, and is removed in MySQL 8.0.
예를 들어, 높은 트래픽을 받는 포럼 시스템에서 게시글 테이블을 지속적으로 조회하고, 동시에 자주 업데이트된다면, 모든 쿼리 캐시가 업데이트 시 삭제되어 성능에 큰 부담이 되기 때문입니다.
MySQL 8.0 이전 버전에서는 query_cache_type 매개변수를 DEMAND로 설정하여 쿼리 캐시 사용을 모두 비활성화할 수 있습니다. 그리고 명시적으로 캐시를 사용하고 싶은 쿼리에는 SQL_CACHE 키워드를 사용할 수 있습니다:
mysql> select SQL_CACHE * from t1 where id = 1;
구문 분석기 (Parser)
쿼리 캐시에 HIT되지 않았거나 비활성화된 경우, MySQL 서버는 SQL 문장을 실행 계획으로 변환하고 이를 저장 엔진과 연동합니다. 이 과정은 여러 하위 단계로 나뉩니다: SQL 구문 분석, 사전 처리, 실행 계획 최적화. 이 과정 중 오류(예: 문법 오류) 발생 시 쿼리는 중단됩니다.
구문 분석과 사전 처리는 구문 분석기(parser)의 역할이며, 실행 계획 최적화는 최적화기(optimizer)의 역할입니다. 우선 구문 분석기부터 살펴보겠습니다.
SQL 구문 분석은 키워드를 기준으로 문장을 해석하고, 유효한 구문 트리(parse tree)를 생성하여 문법 규칙에 따라 올바른지 검증합니다. 예를 들어, 잘못된 키워드 사용이나 순서 오류, 따옴표 불일치 등을 확인합니다.
사전 처리는 구문 트리의 유효성을 검사하며, 예를 들어 테이블과 컬럼 존재 여부, 테이블명 및 필드명의 정확성을 확인합니다.
최적화기 (Optimizer)
구문 트리가 유효하다면, MySQL은 사용자가 어떤 작업을 원하는지 알게 됩니다. 하지만 같은 쿼리라도 여러 실행 계획이 존재할 수 있으며, 결과는 같지만 성능은 다릅니다.
예를 들어:
mysql> select * from t1 where id = 10 and name = "good";
이 쿼리에 대해 name = good 먼저 검색 후 id = 10 검색, 또는 반대로 수행할 수 있습니다. 이 두 가지 실행 계획은 비용이 다를 수 있습니다.
최적화기는 이러한 실행 계획 중 가장 효율적인 것을 선택합니다. 이 실행 계획은 단순한 데이터 구조이며, 다른 관계형 DB처럼 바이트 코드를 생성하지 않습니다.
최적화기는 저장 엔진의 타입에 관계없이 작동하지만, 엔진은 최적화에 영향을 줄 수 있습니다. 최적화기는 저장 엔진에게 비용 정보나 통계 정보를 요청합니다.
최적화가 완료되면 실행 계획이 결정되어 실행 단계로 넘어갑니다.
실행기 (Executor)
쿼리 캐시에 HIT되지 않은 경우, 실행기(executor)는 사용자가 해당 테이블에 대한 조회 권한이 있는지 먼저 확인합니다. 권한이 없는 경우 권한 오류가 반환됩니다.
권한 검증이 완료되면 실행기는 실행 계획에 따라 단계별로 작업을 수행합니다. 이 과정에서 저장 엔진의 API를 호출하여 다양한 작업을 수행하며, 이러한 인터페이스는 "handler API"라고 합니다.
각 테이블은 handler 인스턴스로 표현되며, 최적화 단계에서 각 테이블에 대해 handler 인스턴스가 생성됩니다. 최적화기는 이 인스턴스를 통해 테이블의 컬럼명, 인덱스 통계 정보 등을 얻습니다.
예시:
mysql> select * from t1 where id = 10;
InnoDB 엔진을 사용하는 경우, 실행 흐름은 다음과 같습니다 (id가 인덱스가 아니라면 전체 테이블 스캔):
- InnoDB 엔진 인터페이스를 호출하여 첫 번째 행을 가져온 후, id 값이 10인지 확인합니다. 맞다면 결과 집합에 추가하고, 아니면 다음 행으로 이동하여 반복합니다.
- 조건에 맞는 모든 행들을 수집한 후, 이를 클라이언트에 반환합니다.
요약
쿼리 실행 전체 과정은 다음과 같습니다:
- 클라이언트가 서버에 연결 후 쿼리 요청
- 서버는 쿼리 캐시 검사. 캐시에 HIT되면 결과 반환, 그렇지 않으면 다음 단계로
- SQL 구문 분석 및 사전 처리, 유효한 구문 트리 생성
- 최적화기에서 실행 계획 생성
- 실행기에서 저장 엔진 API 호출하여 실행 후 결과 반환