MySQL INSERT 문의 라이프사이클

1. 배경: 기본 MySQL 아키텍처

전반적으로 MySQL은 서버 레이어와 스토리지 엔진 레이어의 두 부분으로 대략 나뉩니다.

서버 레이어

여기에는 연결 관리자, 쿼리 캐시, 파서, 옵티마이저, 실행기 등이 포함됩니다. 저장 프로시저, 트리거 및 뷰와 같은 기능도 이 레이어에서 구현됩니다.

연결 관리자: 클라이언트와 서버 간의 연결을 처리합니다. 클라이언트 요청을 수락하고, 인증 및 권한 검사를 수행하고, 연결 수명 주기를 관리합니다.
쿼리 캐시: 이전 버전의 MySQL에서 사용할 수 있지만 최근 버전에서는 더 이상 권장되지 않습니다. 쿼리와 그 결과를 캐싱하여 쿼리 성능을 향상시킵니다. 그러나 높은 동시성 및 대규모 데이터베이스에서는 잠금 및 오버헤드 문제로 인해 성능 병목 현상이 발생할 수 있습니다.
파서: SQL 쿼리를 분석하고, 구문과 의미를 검증하고, 정확성을 보장합니다. SQL 문을 옵티마이저와 실행기가 사용하는 내부 데이터 구조로 변환합니다.
옵티마이저: 파서로부터 쿼리를 받아 가장 효율적인 실행 방법을 결정합니다. 옵티마이저는 성능 향상을 위해 적절한 인덱스, 조인 순서 및 액세스 방법을 선택하여 최적의 실행 경로를 찾습니다.
실행기: 옵티마이저가 생성한 실행 계획을 실행하고, 스토리지 엔진에서 데이터를 검색하고, 클라이언트 요청을 처리합니다. 스토리지 엔진과 상호 작용하여 쿼리를 실행하고 결과를 사용자에게 반환합니다.
스토리지 엔진 레이어: 데이터 저장 및 검색을 담당합니다. MySQL은 InnoDB, MyISAM 및 Memory와 같은 여러 스토리지 엔진을 지원합니다. 일상적인 개발에서는 일반적으로 InnoDB가 사용됩니다. MySQL 버전 5.5부터 InnoDB가 기본 스토리지 엔진이었습니다.

이제 기본 MySQL 아키텍처를 소개했으므로 쓰기 SQL 문을 처리할 때 각 구성 요소가 수행하는 작업을 살펴보겠습니다.

2. 연결 관리자

쓰기 SQL 문을 실행하려면 일반적으로 MySQL 클라이언트에서 명령어를 입력하여 MySQL 서버에 연결하는 것으로 시작합니다. 서버 측에서는 클라이언트와의 연결을 설정하고 인증을 처리하며 연결 수명 주기를 관리하는 연결 관리자입니다.

연결 명령어:

mysql -h(ip address) -P(port) -u(username) -p

연결 명령어와 올바른 암호를 입력하고 클래식 TCP 핸드셰이크를 완료하면 MySQL 서버에 대한 연결이 성공적으로 설정됩니다.

그런 다음 쓰기 SQL 명령을 직접 입력하고 결과를 확인할 수 있습니다.

mysql> insert into user_score_tab(user_id,score) values(888,10);
Query OK, 1 row affected (0.02 sec)

3. 쿼리 캐시

MySQL 버전 5.6 이하에서는 성공적인 연결 후 쿼리 캐시를 사용하여 SQL 쿼리를 최적화할 수 있었습니다. 쿼리하는 테이블이 업데이트되거나 삽입되면 캐시가 지워집니다. 따라서 쓰기 SQL을 실행하면 캐시가 지워집니다.

실제로 최신 버전의 MySQL(예: 8.0)에서는 쿼리 캐시가 완전히 사용되지 않습니다. 이는 높은 동시성 및 대규모 데이터베이스 환경에서 쿼리 캐싱이 성능 문제를 일으킬 수 있기 때문입니다. 테스트 결과 쿼리 캐시를 비활성화하면 전반적인 성능과 확장성이 향상될 수 있습니다.

4. 파서

쓰기 SQL 명령을 MySQL 서버에 제출하면 명령을 구문 분석하여 수행해야 할 작업을 이해해야 합니다.

파서는 먼저 어휘 분석을 수행합니다. 제출하는 SQL은 문자열과 공백으로 구성되며 MySQL은 각 문자열의 의미를 분석합니다. 예를 들어 이 insert SQL에서:

insert into user_score_tab(user_id,score) values(888,10);

insert into 키워드를 구문 분석하고 user_score_tab을 테이블로, user_id, score를 열 이름으로 식별합니다. 어휘 분석 후 구문 분석이 이어집니다.

구문 분석은 SQL 문이 MySQL 구문 규칙을 준수하는지 확인합니다. 예를 들어 SQL이 올바르지 않으면 다음과 같은 오류가 반환됩니다.

mysql> inser into user_score_tab(user_id,score) value(888,10);
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'inser into user_score_tab(user_id,score) value(888,10)' at line 1

어휘 및 구문 분석을 모두 완료하면 시스템은 이것이 insert SQL임을 이해합니다.

5. 옵티마이저

간단한 INSERT SQL 문의 경우 옵티마이저는 복잡한 쿼리 계획 생성을 수행하지 않습니다.

옵티마이저는 인덱스 선택 및 유지 관리를 담당하지만 이 경우 복잡한 쿼리 최적화에 참여하지 않습니다. 테이블에 기본 키 또는 인덱스가 있는 경우 옵티마이저는 데이터 일관성을 유지하기 위해 인덱스가 업데이트되었는지 확인하고 데이터 삽입 중에 제약 조건 조건을 확인합니다.

INSERT 문은 또한 데이터베이스가 데이터에 액세스하는 방법, 사용할 인덱스 및 데이터 작업 순서를 자세히 설명하는 실행 계획을 생성합니다.

6. 실행기

실행기는 실제 SQL 작업을 수행하고 데이터베이스 시스템의 핵심 실행 모듈입니다. INSERT 문의 경우 실행기는 실제 데이터 삽입 프로세스를 처리합니다.

삽입 지점 결정: 옵티마이저의 실행 계획을 기반으로 실행기는 데이터를 삽입해야 하는 테이블의 정확한 위치(예: 기본 키 또는 고유 키로 지정된 위치)를 식별합니다.
데이터 페이지 로드: 대상 데이터 페이지가 이미 메모리(버퍼 풀)에 있는 경우 직접 사용합니다. 그렇지 않으면 해당 데이터 페이지를 디스크에서 메모리로 로드합니다.
인덱스 업데이트: 테이블에 인덱스(기본 키, 고유 인덱스 등)가 있는 경우 실행기는 그에 따라 업데이트합니다.

7. 버퍼 풀

버퍼 풀은 데이터베이스 테이블의 데이터 페이지, 인덱스 페이지 및 기타 콘텐츠를 캐싱하는 MySQL InnoDB 스토리지 엔진의 메모리 영역입니다. 주요 목적은 읽기/쓰기 성능을 개선하고 디스크 I/O 작업을 줄이는 것입니다.

실행기는 버퍼 풀 내의 적절한 데이터 페이지에 새 데이터를 삽입합니다. 이 작업은 메모리에서 발생하며 디스크의 파일을 즉시 수정하지 않습니다.

8. 실행 취소 로그

데이터가 실제로 삽입되기 전에 InnoDB는 실행 취소 로그를 생성합니다. INSERT 작업의 경우 실행 취소 로그는 새로 삽입된 레코드를 삭제하는 방법을 기록합니다(트랜잭션이 나중에 중단된 경우 삽입을 롤백하는 데 사용됨).

실행 취소 로그가 생성되는 이유는 무엇입니까?

트랜잭션이 롤백되면 MySQL은 커밋되지 않은 작업을 실행 취소해야 합니다. 실행 취소 로그를 사용하면 MySQL은 삽입되었지만 커밋되지 않은 레코드를 삭제하여 트랜잭션의 원자성을 보장할 수 있습니다.

9. 재실행 로그

실행기가 데이터를 삽입한 후 작업은 즉시 재실행 로그에 기록됩니다.

데이터 안정성을 보장하기 위해 MySQL은 미리 쓰기 로깅(WAL) 메커니즘을 사용합니다. 데이터가 물리적으로 디스크에 쓰여지기 전에 작업은 먼저 재실행 로그에 기록됩니다.

프로세스는 무엇입니까?

MySQL은 먼저 작업을 재실행 로그에 쓰고 prepare(사전 커밋) 상태로 표시합니다. 즉, 충돌이 발생할 경우 MySQL은 재실행 로그를 재생하여 데이터를 복구할 수 있습니다.

10. Binlog에 쓰기

재실행 로그에 쓰는 동안 MySQL은 복제 및 재해 복구 목적으로 binlog에도 작업을 씁니다.

binlog는 INSERT INTO와 같은 SQL 작업의 세부 정보를 기록하는 MySQL의 논리 로그입니다. 물리 로그인 재실행 로그와 다릅니다.

11. 트랜잭션 커밋(2단계 커밋)

2단계 커밋 프로세스에서 MySQL은 트랜잭션이 커밋될 때 재실행 로그 상태를 commit으로 업데이트합니다.

2단계 커밋이 필요한 이유는 무엇입니까?

binlog와 재실행 로그 간의 일관성을 보장하기 위해. 시스템이 충돌하면 MySQL은 재실행 로그에서 작업을 재생하고 binlog를 사용하여 누락된 데이터를 복구할 수 있습니다.

12. 디스크에 데이터 플러싱

실행기는 메모리의 더티 페이지를 디스크에 즉시 플러싱하지 않습니다. 백그라운드 스레드는 특정 전략(예: 주기적 플러싱)에 따라 버퍼 풀에서 테이블스페이스 파일로 더티 페이지를 비동기적으로 플러싱합니다. 이렇게 하면 잦은 디스크 I/O 작업을 방지하고 성능을 향상시킬 수 있습니다.

백엔드 프로젝트 호스팅을 위한 최고의 선택, Leapcell입니다.

Leapcell은 웹 호스팅, 비동기 작업 및 Redis를 위한 차세대 서버리스 플랫폼입니다.

다국어 지원

Node.js, Python, Go 또는 Rust로 개발하십시오.

무제한 프로젝트를 무료로 배포

사용량에 대해서만 지불하십시오. 요청도 없고, 요금도 없습니다.

탁월한 비용 효율성

유휴 요금 없이 사용한 만큼만 지불하세요.
예: $25는 평균 응답 시간 60ms에서 694만 건의 요청을 지원합니다.

간소화된 개발자 경험

간편한 설정을 위한 직관적인 UI.
완전 자동화된 CI/CD 파이프라인 및 GitOps 통합.
실행 가능한 통찰력을 위한 실시간 메트릭 및 로깅.

손쉬운 확장성 및 고성능

고도의 동시성을 쉽게 처리할 수 있는 자동 확장.
운영 오버헤드가 전혀 없습니다. 빌드에만 집중하십시오.

설명서에서 자세히 알아보십시오!

X에서 팔로우하세요: @LeapcellHQ