MySQL 아키텍쳐

 

1. mysql 의 전체 구조 

 

   MYSQL 엔진 

•  클라이언트로부터의 접속 및 쿼리 요청을 처리하는 커넥션 핸들러
•  SQL 파서 및 전처리기, 그리고 쿼리의 최적화된 실행을 위한 옵티마이저
•  캐시&버퍼 

 스토리지 엔진 

•  요청된 SQL 문장을 분석하거나 최적화 
•  실제 데이터를 디스크 스토리지에 저장하거나 디스크 스토리지로부터 데이터를 읽어오는 부분 
•  InnoDB , MyISAM, Memory 등 지정할 수 있다 

 핸들러 API 

•   쿼리 실행기에서 데이터를 쓰고나 읽어야 할 때는 각 스토리지 엔진에게 쓰기 또는 읽기를 요청하는 데 이러한 요청을 핸들러 요청이라고 하는데 , 여기에 사용되는 API를 핸들러 API 라고 함
•  InnoDB 스토리지 엔진 또한 이 핸들러 API를 이용해 MySQL 엔진과 데이터를 주고 받음 

 

 

2. MySQL 스레딩 구조 

   MySQL 서버는 프로세스 기반이 아니라 스레드 기반으로 작동, 크게 포그라운드 스레드, 백그라운드 스레드로 구분

  

  포그라운드 스레드(클라이언트 스레드) 

  최소한 MySQL 서버에 접속된 클라이언트의 수만큼 존재, 

  주로 각 클라이언트 사용자가 요청하는 쿼리 문장을 처리하는 것이 임무 

  클라이언트 사용자가 작업을 마치고 커넥션을 종료하면, 해당 커넥션을 담당하던 스레드는 다시 스레드 캐시로 돌아감

  이때 이미 스레드 캐시에 일정 개수 이상의 대기 중인 스레드가 있으면 스레드 캐시에 넣지 않고 스레드를 종료시켜 일정 개수의 스레드만 스레드 캐시에 존재하게 함.

 

thread_cache_size  :스레드의 개수를 일정하게 유지하게 만들어주는 파라미터 

 

InnoDB 테이블은 데이터 버퍼나 캐시까지만 포그라운드 스레드가 처리, 나머지 버퍼로부터 디스크까지 기록하는 작업은 백그라운드 스레드가 처리 

 

 

백그라운드 스레드 

InnoDB는 여러가지 작업이 백그라운드로 처리 

인서트 버퍼를 병합하는 스레드 , 로그를 디스크로 기록하는 스레드 , InnoDB 버퍼 풀의 데이터를 디스크에 기록하는 스레드, 데이터를 버퍼로 읽어들이는 스레드, 여러 가지 잠금이나 데드락을 모니터링하는 스레드 , 

이러한 모든 스레드를 총괄하는 메인 스레드

 

innodb_write_io_threads : 쓰기 스레드의 개수를 지정하는 파라미터 , 2~4정도 권장

innodb_read_io_threads : 읽기 쓰레드의 개수를 지정하는 파라미터 

 

 

3. 메모리 할당 및 사용 구조 

 

글로벌 메모리 영역 

 일반적으로 클라이언트 스레드의 수와 무관하게 일반적으로는 하나의 메모리 공간만 할당 

 피룡에 따라 2개 이상의 메모리 공간을 할당받을 수 있지만 클라이언트의 스레드 수와는 무관하며, 생성된 글로벌 영역이 N개라 하더라도 모든 스레드에 의해 공유 

 

 키 캐시, 버퍼 풀, 쿼리 캐시 , 바이너리로그 버퍼 , 로그 버퍼 ,테이블 캐시

 

로컬 메모리 영역

MySQL 서버상에 존재하는 클라이언트 스레드가 쿼리를 처리하는데 사용하는 메모리 영역

각 클라이언트 스레드별로 독립적으로 할당되며 절대 공유되어 사용되지 않는다는 특징 

 

커넥션 버퍼,READ 버퍼, 랜덤 READ 버퍼, 정렬버퍼, 조인버퍼, RESULT 버퍼 

 

 

 

4. 쿼리 실행 구조 

 

SQL 요청 -> 쿼리 파서 -> 전처리기 -> 옵티마이저 - > 쿼리실행기  -> SQL 결과 

 

 파서 

사용자 요청으로 들어온 쿼리 문장을 토큰으로 분리해 트리 형태의 구조로 만드러 내는 작업을 의미 

쿼리 문장의 기본 문법 오류는 이 과정에서 발견되며 사용자에게 오류 메시지를 전달하기 됨 

 

 

전처리기 

파서 과정에서 만들어진 파서 트리 기반으로 쿼리 문장에 구조적인 문제점이 있는지 확인

각 토큰을 테이블 이름이나 칼럼 이름 또는 내장 함수와 같은 개체를 매핑해 해당 객체의 존재 여부와 객체의 접근권한 등을 확인해는 과정

 

 

옵티마이저 

사용자의 요청으로 들어온 쿼리 문장을 저렴한 비용으로 가장 빠르게 처리할지 결정하는 역할을 담당

 

 

실행 엔진

옵티마이저는 회사의 경영진, 실행 엔진은 중간관리자 , 핸들러는 각 업무의 실무자로 비유 

 

예시) 

 1. 실행 엔진은 핸들러에게 임시 테이블을 만들라고 요청 

 2. 다시 실행 엔진은 WHERE 절에 일치하는 레코드를 읽어오라고 핸들러에게 요청 

 3. 읽어온 레코드들을 1번에서 준비한 임시 테이블로 저장하라고 다시 핸들러에게 요청 

 4. 데이터가 준비된 임시 테이블에서 필요한 방식으로 데이터를 읽어 오라고 핸들러에게 다시 요청 

 5. 최종적으로 실행엔진은 결과를 사용자나 다른 모듈로 넘김 

 

즉, 실행 엔진은 만들어진 계획대로 각 핸들러에게 요청해서 받은 결과를 또 다른 핸들러 요청의 입력으로 연결하는 역할을 수행. 

 

 

핸들러( 스토리지 엔진) 

 MySQL 서버의 가장 밑단에서 MySQL 실행 엔진의 요청에 따라 데이터를 디스크로 저장하고 디스크로부터 읽어오는 역할을 담당 

핸들러는 결국 스토지지 엔진을 의미하며 , InnoDB 테이블을 조작하는 경우에는 핸들러가 InnoDB 스토리지 엔진이 됨 

 

 

 

5. 복제 

 

 마스터 

 INSERT,UPDATE, DELETE, SELECT 와 같은 쿼리를 이용해 데이터를 변경할 수 있는 서버 

 데이터의 구조나 내용을 변경하는 모든 쿼리 문장은 바이너리 로그에 기록

 슬레이브 서버에서 변경 내역을 요청하면 마스터 장비는 그 바이너리 로그를 읽어 슬레이브로 넘김

 마스터 장비의 프로세스 가운데 'Binlog dump' 라는 스레드가 이 일을 전담하는 스레드

 만약 하나의 마스터 서버에 10개의 슬레이브가 연결돼 있다면 'Binlog dump' 스레드는 10개가 표시됨

 

 슬레이브 

데이터(바이너리 로그) 를 받아 올 마스터 장비의 정보를 가지고 있는 경우 슬레이브가 됨 

마스터가 바이너리 로그를 가지고 있다면 슬레이브 서버는 릴레이 로그를 가지고 있음

슬레이브 서버의 I/O 스레드는 마스터 서버에 접속해 변경 내역을 요청하고, 받아 온 변경 내역을 릴레이 로그에 기록 

슬레이브 서버의 SQL 스레드가 릴레이 로그에 기록된 변경 내역을 재실행함으로써 슬레이브의 데이터를 마스터와 동일한 상태로 유지 

 

 

6. 쿼리 캐시 

 쿼리 캐시는 여러 가지 복잡한 처리 절차와 큰 비용을 들여 실행된 결과를 쿼리 캐시에 담아 두고, 동일한 쿼리 요청이 왔을 때 간단하게 쿼리 캐시에서 찾아서 바로 결과를 내려 줄게 해줌

 

쿼리의 결과를 메모리에 캐시해 두는 기능 

쿼리 캐시의 구조는 키&값 관리되는 맵과 같은 데이터 구조로 키는 쿼리 문장 , 값은 해당 쿼리의 실행 결과 

 

쿼리 처리절차 

 

1. 요청된 쿼리 문장이 쿼리 캐시에 존재하는가?

  공백이나 탭과 같은 문자,대소문자까지 완전히 동일하면 같은 쿼리로 인식 

 

2. 해당 사용자가 그 결과를 볼 수 있는 권한을 가지고 있는가? 

 

3. 트랜잭션 내에서 실행된 쿼리인 경우 가시 범위 내에 있는 결과인가? 

 

 InnoDB의 모든 트랜잭션은 순차적으로 증가하는 각 트랜잭션 ID를 갖게 됨 

 격리 수준을 준수하기 위해 각 트랜잭션은 자신의 ID보다 ID 값이 큰 트랜잭션에서 변경한 작업 내역이나 쿼리 결과는 참조할 수 없음. 이를 트랜잭션의 가시 범위라고 함, 쿼리 캐시도 그 결과를 만들어낸 트랜잭션의 ID가 가시 범위 내에 있을 때만 사용할 수 있음 

 

4. CURRENT_DATE(), SYSDATE(), RAND() 등과 같이 호출 시점에 따라 결과가 달라지는 요소가 있는가? 

 

호출될 때마다 결과 값이 달라지는 CURRENT_DATE() , SYSDATE() , RAND() 와 같은 내장 함수는 사용하지 않는 편이 쿼리 캐시의 효율을 높이는데 도움이 됨 

 

5. 프리페어 스테이트먼트의 경우 변수가 결과에 영향을 미치지 않는가?

 

쿼리 문장 자체에 변수("?") 가 사용되기 때문에 쿼리 문장 자체로 쿼리 캐시를 찾을 수가 없음.

 

6. 캐시가 만들어지고 난 이후 해당 데이터가 다른 사용자에 의해 변경되지 않았는가 ? 

 

7. 쿼리에 의해 만들어진 결과가 캐시하기에 너무 크지 않는가?

  

query_cache_limit : 특정한 크기 미만의 쿼리 결과만 캐시 하도록 설정하는 파라미터 

   1~2M 미만으로 설정하는 것이 일반적 

 

MYSQL 은 이 파라미터로 설정된 크기 미만의 쿼리 결과만 캐시함.

 

8. 그 밖에 쿼리 캐시를 사용하지 못하게 만드는 요소가 사용됐는가? 

 

 1.임시 테이블에 대한 쿼리 

 2. 사용자 변수의 사용 

 3. 컬럼 기반의 권한 설정 

 4. LOCK IN SHARE MODE 힌트 

 5. FOR UPDATE 힌트 

 6. UDF(User Defined Funtion)  사용 

 7. 독립적인 SELECT 문장이 아닌 일부분의 서브 쿼리 

 8. 스트어드 루닡에서 사용된 쿼리 

 9. SQL_NO_CACHE 힌트 

 

Qache_hits - 쿼리 캐시로 처리된  SELECT 쿼리의 수 

Com_select - 쿼리 캐시에서 결과를 찾지 못해서 MySQL 서버가 실행한 횟수 

 

qache_hits + com_select = mysql 서버로 요청된 모든 select 문장의 총 합 

 

 

 

Reference 

Real MySQL