MySQL 아키텍쳐
MySQL 아키텍쳐
MySQL Logical Architecture 글을 읽고 정리해 보았습니다.
MySQL 아키텍쳐
첫 번째 계층 [클라이언트]
- 두 번째 계층 [서버]
쿼리 구문 분석, 분석, 최적화, 캐싱 및 모든 내장 기능(날짜, 시간, 계산 및 암호화)을 포함하는 MySQL의 두뇌
스토리지 엔진 전반에 걸쳐 제공되는 모든 기능은 이 레벨에서 작동합니다 : 프로시저, 트리거, 뷰 등등 - 세 번째 계층 [스토리지 엔진]
스토리지 엔진은 MySQL에 저장된 모든 데이터를 저장하고 검색하는 역할 담당
MyISAM, InnoDB는 일반적으로 사용되는 스토리지 엔진
MySQL 서버는 API를 통해 스토리지 엔진과 통신
MySQL 쿼리 실행 순서
- [Client] SQL 작성 및 실행
- [Server] Query cache 확인 → 캐시에 존재하면 저장된 결과 반환 / 없다면 다음 단계 진행
- [Server] 실행 계획 → SQL 구문 분석, 사전 처리 및 최적화
- [The query execution engine] 스토리지 엔진 API 호출 → 계획 실행
- [Server] 결과 반환 → [Client]
최적화 및 실행
MySQL은 쿼리를 파싱해서 내부 구조(파싱 트리)를 생성한 다음 다양한 최적화를 적용합니다.
여기에는 쿼리 재작성, 테이블 읽는 순서 결정, 사용할 인덱스 선택등이 포함되어 있습니다.
쿼리의 특수 키워드를 통해 옵티마이저에게 힌트를 전달하여 의사 결정 프로세스에 영향을 줄 수 있습니다.
서버에 최적화의 다양한 측면을 설명하도록 요청할 수도 있습니다.
이를 통해 서버가 어떤 결정을 내리는지 알 수 있고, 쿼리, 스키마 및 설정을 재작업하기 위한 참고 지점을 제공하여 모든 것이 가능한 한 효율적으로 실행 되도록 합니다.
옵티마이저는 특정 테이블이 어떤 스토리지 엔진을 사용하는지 전혀 신경쓰지 않지만!
스토리지 엔진은 서버가 쿼리를 최적화하는 방법에 영향을 미칩니다.
옵티마이저는 스토리지 엔진에 일부 기능과 특정 작업의 비용, 테이블 데이터에 대한 통계에 대해 묻습니다.
쿼리를 구문 분석하기 전에 MySQL 서버는 쿼리 캐시를 확인합니다.
쿼리 캐시에는 SELECT 문만 결과 집합과 함께 저장할 수 있습니다.
쿼리가 이미 캐시에 있는 쿼리와 동일한 경우 서버는 쿼리를 구문 분석, 최적화 또는 실행할 필요가 없이 단순히 저장된 결과 집합을 전달하기만 하면 됩니다.
MySQL 클라이언트/서버 프로토콜
프로토콜은 반이중(서로 다른 두 지점 간에 송신 혹은 수신 중 한쪽만 교대로 데이터를 보내는 통신 방식) 입니다.
즉, MySQL 서버는 언제든지 메시지를 보내거나 받을 수 있지만 둘을 동시에 할 수는 없습니다.
클라이언트는 단일 데이터 패킷으로 서버에 쿼리를 보냅니다.
이것이 대용량 쿼리가 있는 경우 max_allowed_packet 구성 변수가 중요한 이유 입니다.
클라이언트가 쿼리를 보내면 끝이고, 결과만 기다릴 수 있습니다.
(쿼리가 너무 크면 서버는 더 이상 데이터의 수신을 거부하고 오류를 발생시킵니다.)
반면에 서버의 응답은 일반적으로 많은 데이터 패킷으로 구성됩니다.
서버가 응답하면 클라이언트는 전체 결과 집합을 받아야 하므로 LIMIT가 중요한 역할을 합니다.
클라이언트가 서버에서 rows를 가져올 때 끌어오고(pull) 있다고 생각합니다.
하지만 진실은 MySQL 서버가 rows를 생성할 때 push 하고 있습니다.
클라이언트는 pushed rows 만 받을 수 있기 때문에 rows 전송을 중지하도록 서버에 지시할 방법은 없습니다.
(관점의 차이인듯? 클라이언트는 가만히 받기만 하는 것이고, 서버가 떠먹여 주고 있다!)
🚫 쿼리 캐시
MySQL은 ‘query_cache_size’ 변수에 할당된 값에서 서버가 시작할 때 쿼리 캐시에 대해 지정된 양의 메모리를 한 번에 할당하고 초기화합니다.
이 변수를 업데이트 하면(현재 값으로 설정해도) MySQL은 즉시 모든 캐시된 쿼리를 삭제하고, 캐시의 크기를 지정된 크기로 조정 후 캐시의 메모리를 초기화 합니다.
MySQL은 캐시된 모든 쿼리를 즉시 삭제하지 않고 하나씩 삭제하기 때문에 완료될 때까지 시간이 오래 걸리고 서버가 중단될 수 있습니다.
MySQL 5.6(2013) 버전부터 deprecated, 8.0 부터 지원이 중단되었습니다.
쿼리 캐시는 여러 쓰레드들이 공유하는 자원이기 때문에 동기화를 위한 lock이 존재합니다.
(이를 ‘query cache lock’라고 합니다. 😁)
만약 캐싱하고 있는 테이블이 변경하게 되면 관련된 캐싱 데이터를 삭제하게 되는데, 이 때 lock이 걸립니다.
이 lock이 풀릴 때까지 쿼리 캐시에 접근하는 쓰레드들은 ‘Waiting for query cache lock’ 상태로 대기하게 됩니다.
따라서 테이블 변경이 잦은 경우에 lock으로 인해 심각한 동시 처리 성능 저하를 유발합니다.
(변경 중에 SELECT 요청이 많은 경우 모두 lock으로 인해 대기상태에 놓이기 때문에 성능 저하로 이어집니다.)
🧐 실시간으로 변경되지 않는 일이 없긴 하니, 오히려 성능 저하를 일으킬 수 밖에 없을거 같긴 하다.
그런데 이를 없애버린다면 계속해서 다음 단계를 밟아야 되는데.. 조회 성능은 괜찮을려나…?
쿼리 최적화 프로세스
MySQL은 SQL 쿼리를 ‘The query execution engine’의 실행 계획으로 바꿉니다.
여기에는 구문 분석, 전처리 및 최적화와 같은 여러 하위 단계가 있습니다.
오류(ex: 구문 오류)는 프로세스의 어느 지점에서나 발생할 수 있습니다.
[파서와 전처리기]
- MySQL 파서는 쿼리를 토큰으로 나누고 토큰에서 ‘Parse tree’를 만듭니다.
파서는 MySQL SQL 문법을 사용해서 쿼리를 해석하고 검증합니다.
(예를 들어 쿼리의 토큰이 유효하고 올바른 순서인지 확인, 종료되지 않은 인용 문자열과 같은 실수 확인) - 그런 다음 전처리기는 결과 구문 분석 트리에서 구문 분석기가 확인할 수 없는 추가 의미 체계를 확인합니다.
(예를 들어 테이블과 열이 있는지 확인, 이름과 별칭을 확인해서 열 참조가 모호하지 않은지 확인) - 다음으로 전처리기는 권한은 확인 합니다. 서버에 많은 권한이 없는 경우 일반적으로 매우 빠릅니다.
🤔 서버의 권한 또한 속도에 영향을 주는구나…?
[쿼리 최적화 프로그램] 이제 구문 분석 트리가 유효하고 최적화 프로그램이 이를 쿼리 실행 계획으로 전환할 준비가 되었습니다.
쿼리는 종종 다양한 방법으로 실행되고 동일한 결과를 생성할 수 있습니다.
옵티마이저의 임무는 최상의 옵션을 찾는 것입니다.
MySQL은 비용 기반 옵티마이저를 사용합니다. 즉 다양한 실행 계획의 비용을 예측하고 가장 저렴한 것을 선택하려고 합니다.
비용 단위는 원래 단일 무작위 4KB 데이터 페이지 읽기였지만 더 정교해져서 WHERE 절 비교를 실행하는 예상 비용과 같은 요소를 포함합니다.
쿼리를 실행한 다음 Last_query_cost 세션 변수를 검사하여 옵티마이저가 쿼리를 추정한 비용을 확인할 수 있습니다.
※ ‘Last_query_cost’와 같은 상태 표시]
+-----------------------------+
⎮ Variable_name | Value |
+-----------------------------+
| Last_query_cost | 10.499000 |
+-----------------------------+
이 결과는 최적화 프로그램이 쿼리를 실행하기 위해 약 10개의 임의 데이터 페이지 읽기를 수행해야 할것으로 추정한 것을 의미합니다.
테이블 또는 인덱스 당 페이지 수, 인덱스의 카디널리티, 행과 키의 길이, 키 분포와 같은 다양한 요소를 기반으로 합니다.
옵티마이저는 추정치에 어떤 유형의 캐싱 효과도 포함하지 않습니다.
모든 읽기가 디스크 I/O 작업을 초래할 것이라고 가정합니다.
[옵티마이저는 여러 가지 이유로 항상 최상의 계획을 선택하지 않을 수 있습니다.]
- 통계가 틀릴 수도 있습니다. 서버는 통계를 제공하기 위해 스토리지 엔진에 의존하며 정확한 범위에서 매우 부정확한 범위까지 다양합니다.
예를 들어 InnoDB 스토리지 엔진은 MVCC 아키텍처로 인해 테이블의 행 수에 대한 정확한 통계를 유지하지 않습니다. - 비용 메트릭은 쿼리 실행의 실제 비용과 정확히 동일하지 않으므로 통계가 정확하더라도 쿼리가 MySQL의 근사치보다 더 많거나 더 저렴할 수 있습니다.
더 많은 페이지를 읽는 계획은 읽기가 순차적이어서 디스크 I/O가 더 빠르거나 페이지가 이미 메모리에 캐시된 경우와 같은 경우에 실제로 더 저렴할 수 있습니다.
MySQL은 또한 메모리에 있는 페이지와 디스크에 있는 페이지를 이해하지 못하므로 쿼리로 인해 얼마나 많은 I/O가 발생하는지 알지 못합니다. ‘최적’이라는 MySQL의 아이디어는 사용자의 생각과 일치하지 않을 수 있습니다.
가장 빠른 실행 시간을 원하겠지만 MySQL은 쿼리를 빠르게 만들려고 하지 않습니다.
비용을 최소화하려고 하며, 우리가 보았듯이 비용을 결정하는 것은 정확한 과학이 아닙니다.- MySQL은 동시에 실행되는 다른 쿼리를 고려하지 않으므로 쿼리 실행 속도에 영향을 줄 수 있습니다.
- MySQL이 항상 비용 기반 최적화를 수행하는 것은 아닙니다.
때로는 ‘if there’s a full-text MATCH() clause, use a FULLTEXT index if one exists.’와 같은 규칙을 따릅니다.
다른 인덱스와 WHERE 절과 함께 FULLTEXT가 아닌 쿼리를 사용하는 것이 더 빠른 경우에도 이 작업을 수행합니다. - 옵티마이저는 저장된 함수 또한 사용자 정의 함수 실행과 같이 제어할 수 없는 작업의 비용을 고려하지 않습니다.
Concluding the optimizer — 가능한 모든 실행 계획을 항상 추정할 수는 없으므로 최적화 계획을 놓칠 수 있습니다.
옵티마이저는 가능한 모든 실행 계획을 항상 추정할 수 없으므로 최적의 계획을 놓칠 수 있습니다.
실행 계획
MySQL은 다른 많은 데이터베이스 제품처럼 쿼리를 실행하기 위해 바이트 코드를 생성하지 않습니다.
대신 쿼리 실행 계획은 실제로 쿼리 실행 엔진이 쿼리 결과를 생성하기 위해 따르는 명령 트리입니다.
최종 계획에는 원래 쿼리를 재구성하기에 충분한 정보가 포함되어 있습니다.
쿼리에 대해 EXPLAIN EXTENDED(query)를 실행 한 다음 SHOW WARNINGS를 실행하면 재구성된 쿼리가 표시됩니다.
(서버는 실행 계획에서 출력을 생성합니다. It thus has the same semantics as the original query, but not necessarily the same text.)
쿼리 실행 엔진
MySQL은 단순히 쿼리 실행 계획에 제공된 지침으 따릅니다.
쿼리를 실행하기 위해 서버는 검사할 행이 더 이상 없을 때까지 지침을 반복합니다.
쿼리 실행 엔진은 API 호출을 통해 스토리지 엔진과 통신합니다. 쿼리 실행에 의해 수행되는 기능은 다음과 같습니다.
- 실행 계획의 모든 명령에 대한 디스패처 역할을 합니다.
- 배치가 완료될 때까지 계획의 모든 명령을 반복하고 스토리지 엔진과 상호 작용하여 테이블 및 인덱스에서 데이터를 검색하고 업데이트 합니다.
MySQL의 스토리지 엔진
MySQL은 각 데이터베이스(스키마라고도 함)를 기본 파일 시스템에 있는 데이터 디렉토리의 하위 디렉토리로 저장합니다.
테이블을 생성할 때 MySQL은 테이블과 동일한 이름을 가진 .frm 파일에 테이블 정의를 저장합니다.
따라서 MyTable이라는 테이블을 생성할 때 MySQL은 테이블 정의를 MyTable.frm에 저장합니다.
MySQL은 파일 시스템을 사용하여 데이터베이스 이름과 테이블 정의를 저장하기 때문에 대소문자 구분은 플랫폼에 따라 다릅니다.
SHOW TABLE STATUS 명령을 사용하여 테이블에 대한 정보를 표시할 수 있습니다.
클라이언트에게 결과 반환
쿼리 실행의 마지막 단계는 클라이언트에 응답하는 것입니다.
결과 집합을 반환하지 않는 쿼리도 영향을 받은 행 수와 같은 쿼리에 대한 정보로 클라이언트 연결에 응답합니다.
쿼리가 캐시 가능한 경우 MySQL은 이 단계에서 결과를 쿼리 캐시에 저장합니다.
서버는 결과를 점진적으로 생성하고 보냅니다.
MySQL이 마지막 테이블을 처리하고 하나의 행을 성공적으로 생성하자마자 해당 행을 클라이언트에게 보낼 수 있고 보내야 합니다.
| 이것은 두가지 이점이 있습니다. 서버가 메모리에 행을 보유하지 않도록 하고 클라이언트가 가능한 한 빨리 결과를 얻기 시작한다는 것을 의미합니다. |
