MySQL은 크게 서버 계층과 스토리지 엔진 계층, 두 가지 주요 부분으로 나눌 수 있습니다.

서버 계층은 커넥터(Connector), 쿼리 캐시(Query Cache), 분석기(Parser), 최적화기(Optimizer), 실행기(Executor) 등을 포함하며, MySQL의 대부분의 핵심 서비스 기능과 모든 내장 함수(날짜, 시간, 수학, 암호화 함수 등)를 담당합니다. 스토어드 프로시저, 트리거, 뷰와 같이 다양한 스토리지 엔진에 걸쳐 작동하는 기능들도 이 계층에서 구현됩니다.

스토리지 엔진 계층은 데이터의 저장 및 추출을 담당합니다. 플러그인 아키텍처를 가지며 InnoDB, MyISAM, Memory 등 여러 스토리지 엔진을 지원합니다. 현재 가장 널리 사용되는 스토리지 엔진은 InnoDB이며, MySQL 5.5.5 버전부터 기본 스토리지 엔진으로 채택되었습니다.

MySQL 서버 계층의 주요 구성 요소

• 커넥터: 클라이언트와의 연결을 설정하고, 관리하며, 사용자 인증을 수행합니다.
• 쿼리 캐시: (MySQL 8.0부터 삭제됨) 이전에 실행된 쿼리 결과가 존재하면 즉시 반환하고, 그렇지 않으면 다음 단계로 진행합니다.
• 분석기: SQL 쿼리 문장을 어휘 분석, 구문 분석하여 구문 트리를 생성하여 테이블 이름, 필드, 문장 유형을 식별합니다.
• 최적화기: 쿼리 비용을 고려하여 가장 효율적인 실행 계획을 선택합니다.
• 실행기: 최적화된 실행 계획에 따라 SQL 쿼리를 실행하고, 스토리지 엔진으로부터 레코드를 읽어 클라이언트에 반환합니다.

1. 커넥터

사용자가 데이터베이스에 연결을 시도하면, 커넥터가 이를 담당합니다. 커넥터는 클라이언트와의 연결을 설정하고, 권한을 가져오며, 연결을 유지 및 관리하는 역할을 합니다. 일반적인 연결 명령어는 다음과 같습니다:

mysql -h[IP 주소] -P[포트] -u[사용자명] -p

명령어를 입력한 후에는 비밀번호를 입력해야 합니다. 비밀번호를 명령어 뒤에 직접 붙여 쓰는 것은 보안상 권장되지 않습니다. 특히 운영 서버에서는 절대 사용하지 않아야 합니다.

클라이언트 도구인 mysql은 서버와의 연결을 설정합니다. 표준 TCP 핸드셰이크가 완료되면, 커넥터는 사용자가 입력한 사용자명과 비밀번호를 사용하여 사용자 신원을 인증합니다. 인증에 실패하면 "Access denied for user" 오류가 발생하고 클라이언트 프로그램은 종료됩니다. 인증이 성공하면, 커넥터는 권한 테이블에서 해당 사용자가 가진 권한을 조회합니다. 이 시점에서 읽어온 권한은 해당 연결이 유지되는 동안의 모든 권한 판단 로직에 사용됩니다.

이는 사용자가 한 번 연결을 성공하면, 관리자 계정으로 해당 사용자의 권한을 변경하더라도 이미 존재하는 연결의 권한에는 영향을 미치지 않는다는 것을 의미합니다. 변경된 권한은 새로 생성되는 연결에만 적용됩니다.

연결이 완료된 후 클라이언트에서 아무런 동작이 없으면 해당 연결은 유휴 상태가 됩니다. SHOW PROCESSLIST 명령어로 이를 확인할 수 있으며, 'Command' 컬럼에 "Sleep"으로 표시됩니다.

클라이언트가 너무 오랫동안 활동하지 않으면 커넥터는 자동으로 연결을 끊습니다. 이 시간은 wait_timeout 매개변수로 제어되며, 기본값은 8시간입니다. 연결이 끊긴 후 클라이언트가 다시 요청을 보내면 "Lost connection to MySQL server during query" 오류를 받게 됩니다. 이 경우 계속 진행하려면 다시 연결해야 합니다.

데이터베이스에서 장기 연결은 클라이언트가 지속적으로 요청을 보낼 때 동일한 연결을 계속 사용하는 것을 의미합니다. 단기 연결은 몇 번의 쿼리를 실행한 후 연결을 끊고, 다음 쿼리 시 다시 연결을 설정하는 방식입니다. 연결 설정 과정은 비교적 복잡하므로, 가능한 한 연결 설정을 줄이기 위해 장기 연결을 사용하는 것이 좋습니다.

그러나 모든 연결에 장기 연결을 사용하면 MySQL의 메모리 사용량이 급증할 수 있습니다. 이는 MySQL이 실행 중에 임시로 사용하는 메모리가 연결 객체 내부에 관리되기 때문입니다. 이러한 리소스는 연결이 끊어질 때만 해제됩니다. 따라서 장기 연결이 누적되면 메모리 사용량이 너무 커져 시스템에 의해 강제 종료(OOM)될 수 있으며, 이는 MySQL이 비정상적으로 재시작되는 현상으로 나타날 수 있습니다.

이 문제를 해결하기 위한 두 가지 방안은 다음과 같습니다:

1. 정기적으로 장기 연결을 끊습니다. 일정 시간 사용하거나, 프로그램에서 메모리를 많이 사용하는 대규모 쿼리 실행 후 연결을 끊고, 필요할 때 다시 연결합니다.
2. MySQL 5.7 또는 그 이후 버전을 사용한다면, 큰 작업을 수행한 후 mysql_reset_connection 명령어를 실행하여 연결 리소스를 재초기화할 수 있습니다. 이 과정은 재연결이나 권한 재인증 없이 연결을 처음 생성되었을 때의 상태로 복원합니다.

2. 쿼리 캐시 (MySQL 8.0부터 삭제됨)

연결이 설정되면 SELECT 문을 실행할 수 있으며, 이 논리는 쿼리 캐시 단계로 이어집니다.

MySQL은 쿼리 요청을 받으면 먼저 쿼리 캐시를 확인하여 이전에 동일한 쿼리가 실행되었는지 확인합니다. 이전에 실행된 쿼리와 그 결과는 키-값 쌍 형태로 메모리에 캐시될 수 있습니다. 키는 쿼리 문장이고, 값은 쿼리 결과입니다. 쿼리가 캐시에서 해당 키를 찾으면, 값이 즉시 클라이언트에 반환됩니다. 쿼리가 캐시에 없으면 다음 실행 단계로 진행되며, 실행이 완료된 후 결과는 쿼리 캐시에 저장됩니다. 쿼리 캐시가 적중하면 복잡한 작업을 수행할 필요 없이 결과를 즉시 반환하므로 효율성이 매우 높습니다.

그러나 대부분의 경우 쿼리 캐시 사용을 권장하지 않습니다. 그 이유는 쿼리 캐시가 이점보다 단점이 더 많기 때문입니다.

쿼리 캐시는 매우 자주 무효화됩니다. 테이블에 대한 업데이트가 발생하면 해당 테이블과 관련된 모든 쿼리 캐시가 지워집니다. 따라서 힘들게 결과를 저장하더라도 사용되기도 전에 업데이트로 인해 모두 지워질 수 있습니다. 업데이트 부하가 큰 데이터베이스에서는 쿼리 캐시의 적중률이 매우 낮습니다. 시스템 설정 테이블과 같이 오랫동안 업데이트되지 않는 정적 테이블의 쿼리에만 적합합니다.

다행히 MySQL은 "필요에 따라 사용"하는 방식을 제공했습니다. query_cache_type 매개변수를 DEMAND로 설정하면 기본적으로 SQL 문에 쿼리 캐시를 사용하지 않습니다. 쿼리 캐시를 사용하려는 특정 쿼리에는 다음과 같이 SQL_CACHE 힌트를 명시적으로 지정할 수 있습니다:

SELECT SQL_CACHE * FROM my_config_table WHERE setting_id = 10;

중요한 점은 MySQL 8.0 버전부터 쿼리 캐시 기능 전체가 삭제되었다는 것입니다. 즉, 8.0부터는 이 기능이 완전히 존재하지 않습니다.

3. 분석기

쿼리 캐시에 적중하지 않으면 실제 쿼리 실행이 시작됩니다. 우선 MySQL은 사용자가 무엇을 원하는지 알아야 하므로 SQL 문을 분석해야 합니다.

분석기는 먼저 "어휘 분석"을 수행합니다. 입력된 SQL 문은 여러 문자열과 공백으로 구성되어 있으며, MySQL은 그 안에 있는 문자열이 각각 무엇을 의미하는지 식별해야 합니다. 예를 들어, MySQL은 "SELECT" 키워드를 통해 이것이 쿼리 문장임을 식별하고, "my_table"을 테이블 이름으로, "column_name"을 열 이름으로 식별합니다.

이러한 식별 작업이 완료되면 "구문 분석"을 수행합니다. 어휘 분석 결과를 바탕으로 구문 분석기는 입력된 SQL 문이 MySQL 문법 규칙을 준수하는지 판단합니다. 문법이 올바르지 않으면 "You have an error in your SQL syntax" 오류 메시지를 받게 됩니다. 예를 들어, SELECT에서 'S'를 빼먹은 경우:

ELCET * FROM sample_table WHERE id = 1;

ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'ELCET * FROM sample_table WHERE id = 1' at line 1

일반적으로 구문 오류는 첫 번째 오류 발생 위치를 알려주므로 "use near" 다음의 내용을 주의 깊게 살펴보아야 합니다.

4. 최적화기

분석기를 거쳐 MySQL은 사용자가 무엇을 원하는지 알게 됩니다. 그러나 실행에 들어가기 전에 최적화기 처리를 거쳐야 합니다.

최적화기는 테이블에 여러 인덱스가 있을 때 어떤 인덱스를 사용할지 결정하거나, 여러 테이블을 조인(JOIN)하는 문장에서 각 테이블의 연결 순서를 결정합니다. 예를 들어, 다음과 같은 두 테이블 조인 쿼리를 실행한다고 가정해 봅시다:

SELECT * FROM products p JOIN categories c USING(category_id) WHERE p.price > 100 AND c.name = 'Electronics';

• 하나는 products 테이블에서 price > 100인 레코드의 category_id 값을 먼저 가져와서, 이 값을 기준으로 categories 테이블과 조인한 다음, categories.name이 'Electronics'인지 판단하는 방법이 있습니다.
• 다른 하나는 categories 테이블에서 name = 'Electronics'인 레코드의 category_id 값을 먼저 가져와서, 이 값을 기준으로 products 테이블과 조인한 다음, products.price가 100보다 큰지 판단하는 방법이 있습니다.

이 두 실행 방법은 논리적 결과는 같지만, 실행 효율성에는 차이가 있습니다. 최적화기의 역할은 어떤 실행 계획을 사용할지 결정하는 것입니다.

최적화기 단계가 완료되면, 해당 쿼리의 실행 계획이 확정되고 실행기 단계로 넘어갑니다. 최적화기가 어떻게 인덱스를 선택하는지, 잘못 선택할 가능성은 없는지 등에 대한 의문이 있다면, 추후 별도의 글에서 자세히 설명할 것입니다.

5. 실행기

MySQL은 분석기를 통해 사용자가 무엇을 원하는지, 최적화기를 통해 어떻게 해야 하는지 알게 되었으므로, 이제 실행기 단계로 들어가 쿼리를 실행합니다.

실행을 시작하기 전에, 먼저 해당 테이블에 대한 쿼리 권한이 있는지 확인합니다. 권한이 없으면 다음과 같이 "권한 없음" 오류가 반환됩니다. (실제 구현에서는 쿼리 캐시 적중 시 결과를 반환할 때 권한 검사를 수행하며, 최적화기 이전에 precheck를 호출하여 권한을 검증하기도 합니다.)

SELECT * FROM customer_data WHERE customer_id = 123;

ERROR 1142 (42000): SELECT command denied to user 'guest'@'localhost' for table 'customer_data'

권한이 있으면 테이블을 열고 계속 실행합니다. 테이블을 열 때 실행기는 테이블의 엔진 정의에 따라 해당 엔진이 제공하는 인터페이스를 사용합니다.

예를 들어, customer_data 테이블의 customer_id 필드에 인덱스가 없는 경우 실행기의 실행 흐름은 다음과 같습니다:

1. InnoDB 엔진 인터페이스를 호출하여 테이블의 첫 번째 행을 가져옵니다. customer_id 값이 123인지 판단하고, 123이 아니면 건너뛰고, 123이면 해당 행을 결과 집합에 저장합니다.
2. 엔진 인터페이스를 호출하여 "다음 행"을 가져오고, 테이블의 마지막 행까지 동일한 판단 로직을 반복합니다.
3. 실행기는 위 순회 과정에서 모든 조건을 만족하는 행들로 구성된 레코드 집합을 클라이언트에 결과 집합으로 반환합니다.

이로써 쿼리 실행이 완료됩니다.

인덱스가 있는 테이블의 경우, 실행 논리도 유사합니다. 처음에는 "조건을 만족하는 첫 번째 행 가져오기" 인터페이스를 호출하고, 그 후에는 "조건을 만족하는 다음 행 가져오기" 인터페이스를 반복적으로 호출합니다. 이러한 인터페이스는 모두 엔진 내부에 이미 정의되어 있습니다.

데이터베이스의 느린 쿼리 로그(slow query log)에서 rows_examined 필드를 볼 수 있습니다. 이 값은 해당 쿼리 실행 중에 스캔된 행의 수를 나타냅니다. 이 값은 실행기가 엔진으로부터 데이터 행을 가져올 때마다 누적됩니다. 특정 상황에서는 실행기의 한 번 호출이 엔진 내부에서 여러 행을 스캔하게 할 수 있으므로, 엔진이 스캔한 행 수와 rows_examined는 항상 완전히 일치하지는 않습니다.