What Happens When a SQL is Executed?

어떻게 간단한 SQL 명령으로 성능을 발휘할까?

복잡한 프로세스의 절차가 있다
프로세스의 절차를 모두 알아야 모든 성능을 이끌어낼 수 있다

1. 데이터의 저장, 인덱스, 접근을 알아서 쿼리 성능 이 향상
2. 효과적인 인덱스 전략
3. 메모리 할당부터 캐싱, 쿼리 병렬처리까지의 리소스 관리 개선
4. 성능 병목 현상을 알아내기 위해

SQL Statement

• 어떻게 검색해야 될지 보다는 무엇을 검색해야 될지에 초점을 맞춘다
• 검색 및 조작의 구현을 추상화 해놨음
• SQL의 이식성은 큰 장점

SQL Execution (Query)

MySQL

• Server tier
• Storage Engine tier

Server tier

• connectors
• query caches
• analyzers
• optimizers
• executors

mysql의 대부분의 핵심기능을 구현하고 있다

Storage Engine tier

• data storage
• retrieval

기본적으로 InnoDB지만 다양한 엔진으로 바꿀수 있다

Connection Pool

• 클라이언트와의 연결, 권한, 연결 관리에 책임을 가지고 있다
• 한번 연결이 되면, 유저의 권한이 바뀌어도 이미 존재하는 연결에 영향을 주지 않는다

Query Cache

• 테이블이 자주 바뀌면 캐시를 무효화 한다
• 이러한 한계로 MySQL은 8.0부터 캐리를 없앴다

Command Parser

1. SQL문자열을 분류
2. 구문 분석을 수행하여 문이 mysql의 구문 규칙과 컴파일되는지 여부를 결정

Optimizer

• SQL 문을 실행하기 전 optimizer와 협의
• 여러 인덱스중 어떤것을 사용할지 결정
• 조인 순서를 결정

Query Executor

• 테이블에 대한 권한 유무를 확인
• 스토리지 엔진에서 제공한 인터페이스를 활용
• 인덱스가 없으면 완전 탐색
• 인덱스는 B+ tree 구조
• clustered index, non-clustered index

SQL Execution (Update)

• 단순 select보다 복잡하다
• 일관성을 위해
• redo log
• binlog

Redo Log

• Write-Ahead Log (WAL)
• 실제 db에 반영전에 log 파일에 기록
• db에 반영하기 전에 시스템에 이상이 생기면 복구한다

1. logical redo layer
2. physical redo layer
3. file layer

Binlog

1. Scope of use: redo log는 InnoDB engine 고유, bin log는 server layer 작동하고 모든 스토리지 엔진에 접근 가능
2. Type of logging: redo log는 물리적 변경사항을 저장, sql문을 논리적 형식으로 저장
3. Size management: redo log는 사이즈가 고정되어 재활용, binglog는 계속 추가 가능

• executor는 메모리에 먼저 수정한다
• Two-Phase Commit (2PC)

1. fist phase - redo log에 기록된다
2. secod phase - binlog에 기록된다

Reference

• https://blog.bytebytego.com/p/what-happens-when-a-sql-is-executed