Real MySQL 8.0 4.1장 하편

1. 쿼리 실행 구조

쿼리 파서

• 사용자 요청으로 들어온 쿼리 문장을 토큰(MySQL이 인식할 수 있느 ㄴ최소 단위의 어휘나 기호)으로 분리해 트리 형태의 구조로 만들어 내는 작업
  • 기본 문법 오류를 이 과정에서 발견

    트리구조 예시
    

전처리기

• 파서 과정에서 만들어진 파서 트리를 기반으로 쿼리 문장에 구조적인 문제점이 있는지 확인
• 토큰을 테이블 이름, 칼럼 이름, 내장 함수 같은 객체를 매핑해 해당 객체의 존재 여부와 접근 권한 등을 확인
  • 토큰 매핑, 존재 여부 및 접근 권한 확인

옵티마이저

• 사용자의 요청으로 들어온 쿼리 문장을 저렴한 비용으로 가장 빠르게 처리할지를 결정하는 역할을 담당
• DBMS의 두뇌라고 볼 수 있음
• 책에서는 옵티마이저가 선택하는 내용과, 좋은 선택을 유도하는 방법을 배울 것
• 중요한 역할, 영향 범위가 매우 넓음

실행 엔진

• 실행 엔지노가 핸들러는 DBMS의 손과 발
• 만들어진 계획대로 각 핸들러에게 요청해서 받은 결과를 또 다른 핸들러 요청의 입력으로 연결 하는 역할

  GROUP BY 예시

  1. 실행 엔진이 핸들러에게 임시 테이블을 만들라고 요청
  2. 다시 실행 엔진은 WHERE 절에 일치하는 레코드를 읽어오라고 핸들러에게 요청
  3. 읽어온 레코드들을 1번에서 준비한 임시 테이블로 저장하라고 다시 핸들러에게 요청
  4. 데이터가 준비된 임시 테이블에서 필요한 방식으로 데이터를 읽어 오라고 핸들러에게 다시 요청
  5. 최정적으로 실행 엔진은 결과를 사용자나 다른 모듈로 넘김

  요약: 임시 테이블 생성 - WHERE 조건 일치하는 데이터 읽기 - 임시 테이블에 저장 - 다시 조건 수행 - 결과 전송

핸들러(스토리지 엔진)

• MySQL 서버 가장 밑단에서 MySQL 실행 엔진의 요청에 따라 데이터를 디스크로 저장하고 디스크로부터 익어 오는 역할
  • 이게 곧 스토리지 엔진

2. 복제(Replication)

• 매우 중요한 역할
• 16장에서 다룰 예정

3. 쿼리 캐시

• 빠른 응답을 필요로 하는 웹 기반의 응용 프로그램에서 매우 중요한 역할
• SQL의 실행 결과를 메모리에 캐시하고, 동일 SQL 쿼리가 실행되면 테이블을 읽지 않고 결과 반환
• 동일 SQL에서는 매우 좋은 결과를 낳지만, 테이블의 데이터가 변경되면 캐시에 젖장된 결과 중 변경된 테이블과 연관된 것들을 모두 삭제(Invalidate) 해야해서 심각한 동시 처리 성능 저하를 유발함
  • 따라서 MySQL 8.0 버전에서 기능이 제거되고, 시스템 변수도 제거됨

4. 스레드 풀

• MySQL 엔터프라이즈 에디션에서 제공되는 기능
• 서버에 내장된 MySQL 스레드 풀 대신 플러그인으로 제공되는 Percona Server의 스레드 풀을 볼 예정
• 스레드 풀은 내부적으로 사용자의 요청을 처리하는 스레드 개수를 줄여서 동시 처리되는 요청이 많다 하더라도 MySQL 서버의 CPU가 제한된 개수의 스레드 처리에만 집중할 수 있게 해서 서버의 자원 소모를 줄이는 것이 목적
  • 실제 눈에 띄는 성능 향상은 드물음 == 무조건 좋은 성능인+ 것은 아님
  • CPU의 스케줄링이 제대로 되어 CPU 시간을 확보해야함
    • CPU 친화도 상승, 불필요한 context switch 감소
    • context switch : 하나의 프로세스가 CPU를 사용 중인 상태에서 다른 프로세스가 CPU를 사용하도록 하기 위해, 이전의 프로세스의 상태(문맥)를 보관하고 새로운 프로세스의 상태를 적재하는 작업
• Percona 스레드 풀은 CPU 코어의 개수만큼 스레드 그룹을 생성 (thread_pool_size)
  • 일반적으로 CPU 코어의 개수와 맞추는 것이 좋음
  • 스레드 풀이 처리 중인 작업이 있지만 추가 요청이 있다면 thread_pool_oversubscribe에 설정된 값 만큼 추가로 받아들임
• 스레드 풀이 가득 차있을 경우 새로운 작업 스레드를 추가할지, 기존 작업 스레드가 처리를 완료할 때까지 기다릴지 판단
  • 스레드 풀의 타이머 스레드가 thread_pool_stall_limit 시스템 변수의 밀리초 만큼 기다리고, 새로운 스레드를 생성
    • 너무 낮추면(eg. 0) 스레드 풀을 안쓰는게 나음
  • 이 떄 전체 스레드 개수는 thread_pool_max_threds를 넘을 수 없음
  • 선순위 혹은 후순위 큐를 이용해 특정 트랜잭션이나 쿼리를 우선적으로 처리할 수 있는 기능도 제공
    • 먼저 시작된 트랜잭션을 처리하도록 순서를 바꿈
      
      

5. 트랜잭션 지원 메타 데이터

메타 데이터(데이터 딕셔너리)
데이터베이스 서버에서 테이블의 구조 정보와 스토어드 프로그램 등의 정보를 의미한다.

MySQL 5.7까지는 파일 기반의 메타 데이터 저장으로인해 생성 및 변경 작업이 트랜잭션을 지원하지 않아, 비정상 종료 시 일관적이지 않은 데이터로 데이터베이스가 깨지는 경우가 발생했다.

MySQL 8.0 버전부터는 관련 정보를 InnoDB 테이블에 저장한다. 사용자 인증과 권한 등의 정보를 담은 시스템 테이블과 함께 메타데이터는 mysql DB에 저장하고 있다. mysql은 mysql.ibd 테이블 스페이스에 저장된다.

information_schema

information_schema DB의 테이블에 메타 데이터들이 있으며 아래처럼 조회가능하다.(mysql.table은 권한적으로 막혀있다.)

information_schema 테이블	내용
COLUMNS	모든 스키마의 컬럼 확인
ENGINES	사용되는 엔진 확인
EVENTS	생성된 Event(스케줄) 확인
KEY_COLUMN_USAGE	사용된 키 컬럼 확인
PROCESSLIST	수행 중인 프로세스 리스트
SCHEMATA	생성된 스키마 확인
SCHEMA_PRIVILEGES	스키마 권한 확인
TABLES	생성된 모든 테이블 정보
TABLE_PRIVILEGES	테이블 권한 확인
TRIGGERS	생성된 트리거 확인 가능
USER_PRIVILEGES	사용자 권한 정보
VIEWS	생성된 뷰 정보

InnoDB 스토리지 엔진 외 메타 데이터

InnoDB 스토리지 엔진의 메타 정보는 테이블 기반으로 저장하지만, MyISAM이나 CSV 등과 같은 스토리지 엔진의 메타 정보는 여전히 저장할 공간이 필요하다.

이를 위해 SDI(Serialized Dictionary Information) 파일을 사용한다. 기존의 *.FRM파일과 동일한 역할을 한다. ibd2sdi 유틸리티를 이용해 스키마 정보를 추출해 InnoDB도 SDI로 만들 수 있다.

mysql DB의 스키마를 JSON 파일로 덤프

CLI
linux> ibd2sdi mysql_data_dir/mysql.ibd > mysql_schema.json
linux> cat mysql_schema.json