뒤집어진 사과집

MySQL 구조와 실행 순서

MySQL 구조

• MySQL 엔진
  • Connection Handler: Client와의 연결을 관리하고 쿼리 요청을 처리
  • SQL 인터페이스: DML, DDL, Procedure, View 등 SQL 인터페이스 제공
  • SQL 파서: SQL 문법 오류 탐지 및 서버에서 처리하기 위한 트리 형태로 파싱
  • SQL 옵티마이저: 쿼리 최적화 및 실행 계획 수립
  • 캐시와 버퍼: 성능 향상을 위한 보조 저장소 기능
• Storage 엔진
  • 핸들러 API를 통해 MySQL 엔진이 스토리지 엔진에 읽기/쓰기 요청
  • 스토리지 엔진은 플러그인 형태로 제공되기 때문에 테이블마다 선택 가능(InnoDB가 기본값)

쿼리 실행 순서

1. SQL 파싱
   • MYSQL 서버의 SQL 파서라는 모듈에서 처리하며, 토큰 단위의 SQL 파싱 트리를 생성
2. 전처리
   • 파싱 트리를 기반으로 쿼리 문장의 구조적 문제점 검사
   • 테이블과 칼럼의 존재 여부, 접근 권한 등도 체크
3. 최적화 및 실행 계획 수립
   • 옵티마이저가 파싱 트리를 통해 최적의 실행 계획을 수립
   • 불필요한 조건 제거 및 복잡한 연산의 단순화, 레코드의 임시 테이블 저장 등을 처리
4. 데이터 로드 및 처리
   • 위 실행 계획을 기반으로 Storage 엔진으로부터 데이터 로드
   • MySQL 엔진에서 받은 레코드를 조인하거나 정렬하는 작업을 수행

MySQL 옵티마이저

옵티마이저란

• DBMS의 목적은 많은 데이터를 안전하고 빠르게 저장 및 관리하는 것
• 이를 위해 옵티마이저가 쿼리를 최적화하며, 2가지 종류가 있음
• 비용 기반 최적화(Cost-based optimizer, CBO)
  • 현재 대부분의 DBMS는 CBO 방식을 사용
  • 각 단위 작업의 비용 정보와 통계 정보를 활용하여 비용을 산출
• 규칙 기반 최적화(Rule-based optimizer, RBO)
  • 단순히 옵티마이저에 내장된 우선 순위에 따라 실행 계획을 수립
  • 같은 쿼리에 대해서는 항상 같은 계획을 수립한다는 특징을 가짐

MySQL의 Optimizer

• MySQL의 옵티마이저는 비용 기반으로 쿼리를 최적화함
• 어디까지나 추정 값이며 항상 최적의 실행 계획을 만들어낼 수 있는 것은 아님
• 사용자가 보완할 수 있도록 EXPLAIN 기능을 제공함 → 쿼리 튜닝에 활용

EXPLAIN/ANALYZE

자세한 포스팅은 다음 글을 참고!

• EXPLAIN
  • 옵티마이저가 주어진 쿼리에 대해서 수립한 실행 계획
  • 실제 수행 순서가 아닌 MySQL의 통계 정보를 기반으로 계산한 예측 값임
• EXPLAIN ANALYZE(MySQL 8부터 지원)
  • 실제 실행된 쿼리의 계획과 단계 별 소요 시간을 TREE 형식으로 출력
  • 들여쓰기가 같은 레벨에서는 상단에 위치한 라인이 먼저 실행됨
  • 들여쓰기가 다른 레벨에서는 가장 안쪽에 위치한 라인이 먼저 실행됨

통계 정보

통계 저장 범위의 확장

• MySQL 5.5: 각 테이블의 통계 정보가 메모리에 관리되어 서버 재시작 시 모두 사라짐
• MySQL 5.6: InnoDB 스토리지 엔진에 통계 정보를 영구적으로 관리
• MySQL 8: 데이터 분포도를 수집해 저장하는 히스토그램 정보가 도입

자동/수동 수집

• 기본적으로 자동 수집되며, 필요에 따라 수집 방식 조정 가능
• ANALYZE TABLE 명령어를 통해 특정 테이블의 최신 통계 정보 갱신 가능
• SHOW INDEX 명령어를 통해 최신 통계 정보 확인 가능
• 기본적으로 자동 수집되며, 필요에 따라 수집 방식 조정 가능
• 히스토그램은 명령어를 통해 수동으로 수집되며 시스템 딕셔너리에 컬럼 단위로 저장되어 관리됨. MySQL 서버가 시작될 때 information_schema.column_statistics 테이블에 로드하여 활용

자동 갱신 케이스

• 테이블이 새로 오픈되는 경우
• 테이블의 레코드가 대량으로 변경되는 경우(테이블 전체 레코드 1/6 규모의 INSERT, UPDATE, DELETE가 실행되는 경우)
• ANALYZE TABLE 명령이 실행되는 경우
• SHOW TABLE STATUS 혹은 SHOW INDEX 명령이 실행되는 경우
• InnoDB 모니터가 활성화되는 경우
• Innodb_stats_on_metadata 시스템 설정이 ON인 상태에서 SHOW TABLE STATUS 명령이 실행되는 경우

히스토그램의 용도

• MySQL의 일반적인 통계는 인덱스를 기반으로 카디널리티를 예측하지만, 인덱스가 없는 칼럼의 데이터 분포를 제대로 반영하지 못할 수 있음
• MySQL 5.7까지는 실제 응용 프로그램의 데이터는 항상 균등한 분포를 가지지 않는다는 점을 간과, 정확도가 높지 않았음
• 예를 들어, 아래 테이블에서 order_status 칼럼의 값이 complete 90%와 canceled 10%의 비중이라면 MySQL의 기본 통계는 이를 반영하지 못해 잘못된 실행 계획을 세울 수 있음

SELECT * FROM orders WHERE order_status = 'completed';

⇒ 히스토그램을 수집하면 데이터 분포를 정확히 이해하고 최적의 실행 계획을 수립할 수 있음

References

• MySQL 아키텍처 (MySQL 엔진 구조, 스토리지 엔진 구조, 스레드 구조, 메모리 구조, 쿼리 실행 구조, Replication 구조)
• [MySQL] 옵티마이저란?
• [Real MySQL 8.0] 실행 계획 통계 정보와 실행 계획을 확인하는 방법
• [MySQL] 통계 정보