Real My SQL - 아키텍처(2)

쿼리를 실행하는 관점에서 MySQL 구조를 설명할 수 있다.

 

파서

 

 사용자로부터 요청된 SQL 문장을 서버가 인식할 수 있는 토큰으로 파싱해 트리형태의 구조를 만들어 내는 작업을 의미하다. SQL 문법 오류는 이 단계에서 필터링된다.

 

전처리기

 파서 과정에서 만들어진 파서 트리를 기반으로 SQL 문장에 구조적인 문제가 있는지 확인한다. 각 토큰이 테이블이나 칼럼이름, 내장함수와 같은 개체에 매핑해 객체의 존재여부와 접근권한 등을 확인하는 단계이다. 실제 존재하지 않거나 권한상 접근할 수 없을경우 이 단계에서 필터링된다.

 

옵티마이저

 요청된 문장을 저렴한 비용으로 가장 빠르게 처리하는 DBMS의 두뇌역할이다.

 

실행엔진

 옵티마이저에 의해 수립된 실행계획을 기반으로 실행엔진은 핸들러에게 요청한다.

 

핸들러(스토리지 엔진)

 실행엔진의 요청에 따라 데이터를 읽거나 쓰는 작업을 한다. 핸들러가 MyISAM 테이블을 조작하는 경우에는 MyISAM 스토리지 엔진이며 InnoDB 테이블을 조작하는 경우에는 InnoDB 스토리지 엔진이 된다.

 

 

복제(Replication)

 복제는 2대 이상의 MySQL 서버가 동일한 데이터를 가질 수 있도록 실시간으로 동기화하는 기술이다. 일반적으로 갱신에 해당되는 작업을 하는 MySQL 서버와 SELECT 쿼리 위조로 데이터를 읽기만 하는 MySQL 서버로 나뉜다. 전자를 마스터라고 칭하며 후자를 슬레이브라고 한다. 일반적으로 마스터는 반드시 1대이며 슬레이브는 1대이상이다.

 

마스터(Master)

 - 마스터 서버에서 실행되는 DML, DDL 문장은 바이너리 로그에 기록된다. 슬레이브 서버에서 변경 내역을 요청하면 마스터 서버는 바이너리 로그를 읽어 슬레이브로 전달한다. Binlog dump라는 스레드가 담당하며 슬레이브 당 하나의 스레드가 할당된다.

 

슬레이브(Slave)

 - 바이너리 로그를 받아올 마스터 서버의 정보(IP, Port, 계정)을 가지고 있으며 릴레이 로그를 가지고 있다. 슬레이브 서버의 I/O 스레드는 마스터 서버에 변경 내역을 요청하고, 받아 온 내역을 릴레이 로그에 기록한다. 그리고 슬레이브 서버의 SQL 스레드가 릴레이 로그에 기록된 변경 내역을 재실행함으로써 마스터의 데이터를 동기화한다. I/O 스레드와 SQL 스레드는 마스터 서버에서는 작동되지 않으며 슬레이브 서버에서만 작동된다.

 

* 주의사항

• 슬레이브는 하나의 마스터만 설정가능
• 마스터와 슬레이브의 데이터 동기화를 위해 슬레이브는 읽기 전용으로 설정
• 슬레이브 서버용 장비 스펙은 마스터 서버 스펙과 동일한 사양으로 하는것이 적합
• 복제가 불필요한 경우는 바이너리 로그에 기록 중지
• 바이너리 로그와 트랜잭션 격리 수준

쿼리 캐시

 쿼리 캐시는 실행한 SQL 문장의 결과를 캐시하는 기능이다. MySQL은 동일한 쿼리 문장을 요청할 때 다음과 같은 확인 절차를 거쳐야 한다.

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

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

3. 트랜잭션 내의 실행된 쿼리일경우, 그 결과가 가시범위 내의 트랜잭션에서 만들어진 결과인가?(InnoDB의 경우)

4. 쿼리의 사용된 기능이 캐시되어도 동일한 결과를 보장하는가?

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

6. 쿼리에 의해 만들어진 결과 사이즈가 캐시하기엔 너무 크지 않은가?

7. 그밖에 캐시하지 못하는 요소가 적용되었는가?

 

위에 나열된 각각의 절차를 자세히 살펴보겠다.

 

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

 - 쿼리 캐시는 가장 앞단에서 처리되며 비교 방식을 통해 처리된다. 비교 대상으로는 공백이나 탭과 같은 문자까지 모두 포함되며, 대소문자까지 완전히 동일해야 한다. 

 

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

 - 사용자가 요청한 쿼리가 쿼리 캐시에 적재되어 있더라도 권한이 없으면 그 결과를 가져올 수 없다.

 

3. 트랜잭션 내의 실행된 쿼리일경우, 그 결과가 가시범위 내의 트랜잭션에서 만들어진 결과인가?(InnoDB의 경우)

 - InnoDB의 경우 모든 트랜잭션은 트랜잭션 ID를 갖는다. 트랜잭션 ID는 6byte 숫자로 구성되어 있으며 순차적으로 할당된다. 트랜잭션 ID를 비교하여 어떤 트랜잭션이 먼저 실행된 트랜잭션인지 구분할 수 있으며 먼저 실행된 트랜잭션이 자신보다 느린 즉, ID값이 큰 트랜잭션에서 변경된 작업 내역이나 쿼리 결과를 참조할 수 없다. 이를 트랜잭션의 가시범위라고 하며 쿼리 캐시에 있는 값도 이 가시 범위 내에서만 사용 가능하다.

 

4. 쿼리의 사용된 기능이 캐시되어도 동일한 결과를 보장하는가?

 - CURRENT_DATE(), SYSDATE(), RAND() 등과 같은 호출 시점에 결과가 달라지는 요소가 있으면 쿼리 캐시를 사용할 수 없다. 

 - 프리페어 스테이트먼트의 경우 쿼리문장 자체에 변수가 사용되지 때문에 문장으로 쿼리 캐시를 찾을 수 없다. 이 때 프리페어 스테이트먼트는 응용 프로그램에서 프리페어 스테이트먼트를 사용했더라도 MySQL에서는 프리페어 스테이트먼트로 사용되지 않는다는 점이다. 프리페어 스테이트먼트를 사용하려면 응용 프로그램에서 커넥션 시 특별한 옵션을 통해 명시해줘야 한다. MySQL 5.1 버전에서는 이러한 제약이 없어졌다.

 

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

 - 캐시가 만들어지고 변경되었다면 쿼리 캐시를 무효화해야한다. 쿼리 캐시를 크게 잡을경우 캐시를 제거할 때 상당한 시간이 소요되며 삭제하는 동안에는 여러 스레드가 접근할 수 없기 때문에 적절한 크기로 쿼리 캐시를 할당하는게 좋다 적절한 크기는 32M ~ 64M이다.

 

6. 쿼리에 의해 만들어진 결과 사이즈가 캐시하기엔 너무 크지 않은가?

 - 쿼리 결과로 캐시할 때 적정한 크기가 존재하며 query_cache_limit 설정 파라미터를 통해 설정한다. 캐시에는 SQL문장을 처리하는데 상당한 시간이 걸리지만 결과의 크기가 적은 Group By, Distinct, COUNT()와 같은 결과가 쿼리 캐시를 사용하기에 아주 적합하다.

 

7. 그밖에 캐시하지 못하는 요소가 적용되었는가?

• 임시 테이블을 사용하는 경우
• 사용자 변수를 사용하는 경우
• 칼럼기반의 권한설정
• LOCK IN SHARE MODE 힌트
• FOR UPDATE 힌트
• UDF 사용
• 독립적인 SELECT 문장이 아닌 서브쿼리 문장
• SP, Trigger에서 사용된 쿼리
• SQL NO CACHE 힌트