SQL 업데이트 문을 실행하는 방법은 무엇입니까?

이 글에서는 SQL 업데이트 문을 실행하는 과정을 소개합니다. 도움이 필요한 친구들이 모두 참고할 수 있기를 바랍니다.

1. 소개

앞서 질의문의 실행 과정을 체계적으로 이해하고, 실행 과정에 관여하는 처리 모듈을 소개했습니다. 일반적으로 쿼리문의 실행 과정은 커넥터, 분석기, 옵티마이저, 실행기 등의 기능 모듈을 거쳐 스토리지 엔진에 도달한다는 사실을 아직도 기억하고 계시리라 믿습니다.

그렇다면 업데이트 문의 실행 흐름은 어떻게 될까요?

MySQL이 반달 안에 어느 순간의 상태로 복원될 수 있다는 DBA 동료의 말을 자주 들어보셨을 것입니다. 놀라시면서도 이것이 어떻게 이루어지는지 궁금하시죠?

2. 명령문 분석

테이블에 대한 업데이트 명령문부터 시작해 보겠습니다. 다음은 이 테이블의 생성 명령문입니다. 이 테이블에는 기본 키 ID와 정수 필드 c가 있습니다.

mysql> create table T(ID int primary key, c int);

ID를 설정하려는 경우 =2 이 줄의 값에 1을 더하면 다음과 같이 SQL문이 작성됩니다.

mysql> update T set c=c+1 where ID=2;

앞서 SQL문의 기본 실행 링크를 소개해 드렸는데요. 여기에서도 그림을 가져오실 수 있습니다. 먼저 아래의 사진 리뷰를 간단히 살펴보세요. 우선, 쿼리문과 업데이트문에 대해서도 동일한 프로세스 집합이 뒤따를 것이라는 점은 확실하게 말할 수 있습니다.

문을 실행하기 전에 먼저 데이터베이스에 연결해야 합니다. 이것이 커넥터의 작업입니다.

앞서 테이블에 업데이트가 있으면 이 테이블과 관련된 쿼리 캐시가 무효화되므로 이 명령문은 테이블 T에 캐시된 모든 결과를 지운다고 말했습니다. 이것이 일반적으로 쿼리 캐싱 사용을 권장하지 않는 이유입니다.

다음으로 분석기는 어휘 및 구문 분석을 통해 이것이 업데이트 문임을 알게 됩니다. 최적화 프로그램은 인덱스 ID를 사용하기로 결정합니다. 그러면 실행자는 실제로 실행하고, 이 행을 찾고, 업데이트하는 일을 담당합니다.

쿼리 프로세스와 달리 업데이트 프로세스에는 오늘 논의할 주인공인 두 가지 중요한 로그 모듈인 redo log(redo log)와 binlog(archive log)도 포함됩니다. MySQL을 접하게 되면 이 두 단어를 빼놓을 수 없는데, 다음 내용에서 계속해서 강조하겠습니다. 하지만 리두 로그와 빈로그 디자인에는 흥미로운 측면이 많이 있으며 이러한 디자인 아이디어는 자신의 프로그램에도 사용될 수 있습니다.

중요 로그 모듈: redo log

아직도 "공이지"라는 기사를 기억하실지 모르겠습니다. 호텔 매니저는 손님의 신용 기록을 기록하는 데 특별히 사용하는 핑크색 보드를 가지고 있습니다. 신용카드로 결제하는 사람이 많지 않으면 게시판에 고객의 이름과 계좌를 적을 수 있습니다. 하지만 신용이 있는 사람이 너무 많으면 팬 게시판에서 이를 추적할 수 없는 경우가 항상 발생합니다. 이때 상점 주인은 신용 기록을 위해 특별히 장부를 가지고 있어야 합니다.

누군가 돈을 빌리거나 빚을 갚고 싶다면 가게 주인은 일반적으로 두 가지 방법이 있습니다.

• 한 가지 방법은 직접 장부를 작성하고 신용을 가산하거나 공제하는 것입니다.
• 다른 방법은 먼저 적는 것입니다. 이 계좌를 분홍색 판에 올려 놓고, 마감시간 이후에 장부를 꺼내세요.

사업이 번창하고 카운터가 매우 바쁠 때 전자가 운영하기 너무 번거롭기 때문에 가게 주인은 확실히 후자를 선택할 것입니다. 먼저, 이 사람의 총 신용 계좌 기록을 찾아야 합니다. 생각해 보세요. 이름을 찾으려면 가게 주인이 돋보기를 쓰고 천천히 검색해야 할 수도 있습니다. 계산을 위해 주판을 꺼내고 마침내 결과를 다시 입력합니다. 원장.

이 모든 과정은 생각하기에도 번거롭습니다. 대조적으로, 분홍색 판에 먼저 적어 두는 것이 더 쉽습니다. 생각해 보세요, 가게 주인이 핑크보드의 도움을 받지 못하면 장부를 기록할 때마다 장부를 뒤집어야 하기 때문에 효율성이 참을 수 없을 정도로 낮지 않습니까?

마찬가지로 이 문제는 MySQL에도 존재합니다. 모든 업데이트 작업을 디스크에 기록해야 하고 디스크도 해당 레코드를 찾아 업데이트해야 한다면 전체 프로세스의 IO 비용과 검색 비용은 다음과 같습니다. 매우 높습니다. 이 문제를 해결하기 위해 MySQL의 디자이너들은 호텔 점원의 핑크보드와 유사한 아이디어를 사용하여 업데이트 효율성을 향상시켰습니다.

핑크 보드와 원장 간의 전체 협력 과정은 실제로 MySQL에서 자주 언급되는 WAL 기술입니다. WAL의 전체 이름은 Write-Ahead Logging입니다. 핵심은 로그를 먼저 쓴 다음 디스크에 쓰는 것입니다. 즉, 바쁘지 않을 때 장부를 먼저 쓰겠습니다.

구체적으로, 레코드를 업데이트해야 하는 경우 InnoDB 엔진은 먼저 해당 레코드를 리두 로그(핑크보드)에 기록하고 메모리를 업데이트합니다. 이때 업데이트가 완료됩니다. 동시에 InnoDB 엔진은 적절한 시간에 작업 기록을 디스크에 업데이트하며, 이 업데이트는 가게 주인이 문을 닫은 후 하는 것처럼 시스템이 상대적으로 유휴 상태일 때 수행되는 경우가 많습니다.

오늘 신용 계좌가 많지 않으면 가게 주인은 정리를 위해 문을 닫을 때까지 기다릴 수 있습니다. 그런데 특정일에 신용계좌가 많아 핑크보드가 가득 차면 어떻게 해야 할까요? 이때 가게 주인은 어쩔 수 없이 일을 내려놓고 핑크보드에 있는 신용기록 중 일부를 원장에 업데이트한 뒤 핑크보드에서 이 기록을 지워 새로운 계좌를 마련할 수밖에 없었다.

이와 유사하게 InnoDB의 redo 로그는 고정된 크기를 가지고 있습니다. 예를 들어 4개의 파일로 구성할 수 있으며 각 파일의 크기는 1GB입니다. 그러면 이 "핑크 보드"는 총 4GB 작업을 기록할 수 있습니다. 처음부터 쓰기 시작한 다음, 아래 그림과 같이 처음으로 돌아가서 루프를 작성합니다.

write pos는 현재 레코드의 위치입니다. 쓰기 중에 뒤로(시계방향) 이동합니다. 3번 파일의 끝까지 쓴 후 0번 파일의 처음으로 돌아갑니다. 체크포인트는 삭제될 현재 위치이며, 레코드를 삭제하기 전에 레코드를 데이터 파일로 업데이트해야 합니다.

글 작성 위치와 체크포인트 사이의 공간은 새로운 작업을 기록하는 데 사용할 수 있는 "핑크 보드"의 빈 부분입니다. 쓰기 위치가 체크포인트를 따라잡는다면 '핑크보드'가 꽉 찼다는 의미입니다. 이때는 새로운 업데이트를 수행할 수 없습니다. 체크포인트를 진행하려면 먼저 일부 기록을 중지하고 지워야 합니다.

리두 로그를 사용하면 InnoDB는 데이터베이스가 비정상적으로 다시 시작되더라도 이전에 제출된 레코드가 손실되지 않도록 보장할 수 있습니다. 이 기능을 충돌 안전이라고 합니다.

충돌 안전의 개념을 이해하려면 이전의 신용 기록 예를 생각해 보세요. 핑크보드나 장부에 신용기록이 기록되어 있기만 하면 점주가 갑자기 며칠간 영업을 중단하는 등 나중에 잊어버리더라도 장부의 데이터를 통해 신용계좌를 명확히 할 수 있고, 영업 재개 후 핑크보드.

중요 로그 모듈: binlog

앞서 말했듯이 MySQL은 전체적으로 두 부분으로 구성됩니다. 하나는 주로 MySQL의 기능 수준에서 작업을 수행하는 서버 계층이고, 다른 하나는 이를 담당하는 엔진 계층입니다. 보관과 관련된 특정 사항. 위에서 이야기한 핑크보드 redo 로그는 InnoDB 엔진 고유의 로그이며, Server 계층에도 binlog(archived log)라는 자체 로그가 있습니다.

왜 로그가 2개냐고 물어보실 것 같은데요?

처음에는 MySQL에 InnoDB 엔진이 없었거든요. MySQL의 자체 엔진은 MyISAM이지만 MyISAM에는 충돌 방지 기능이 없으며 binlog 로그는 보관에만 사용할 수 있습니다. InnoDB는 다른 회사에서 플러그인 형태로 MySQL에 도입했습니다. binlog에만 의존하면 충돌 방지 기능이 없으므로 InnoDB는 충돌 방지 기능을 달성하기 위해 다른 로그 시스템, 즉 redo 로그를 사용합니다.

이 두 로그에는 다음 세 가지 차이점이 있습니다.

1. redo 로그는 InnoDB 엔진에 고유합니다. binlog는 MySQL의 서버 계층에서 구현되며 모든 엔진에서 사용할 수 있습니다.
2. redo 로그는 "특정 데이터 페이지에서 어떤 수정이 이루어졌는지"를 기록하는 물리적 로그입니다. binlog는 "ID가 있는 행의 c 필드를 추가하세요"와 같은 이 문의 원래 논리를 기록하는 논리적 로그입니다. =2 1".
3. redo 로그는 루프로 작성되며 공간은 항상 소모됩니다. binlog를 추가로 작성할 수 있습니다. "쓰기 추가"는 binlog 파일이 특정 크기에 도달한 후 다음 파일로 전환하고 이전 로그를 덮어쓰지 않음을 의미합니다.

이 두 로그에 대한 개념적 이해를 바탕으로 이 간단한 업데이트 문을 실행할 때 실행기와 InnoDB 엔진의 내부 프로세스를 살펴보겠습니다.

1. 실행자는 먼저 라인 ID=2를 얻기 위해 엔진을 찾습니다. ID는 기본 키이며 엔진은 이 행을 찾기 위해 트리 검색을 직접 사용합니다. ID=2인 행이 있는 데이터 페이지가 이미 메모리에 있으면 실행기로 직접 반환됩니다. 그렇지 않으면 먼저 디스크에서 메모리로 읽어온 다음 반환해야 합니다.
2. 실행자는 엔진에서 제공한 행 데이터를 가져오고 이 값에 1을 더합니다. 예를 들어 이전에는 N이었지만 지금은 N+1이며 새 데이터 행을 가져온 다음 엔진 인터페이스를 호출하여 이 새로운 데이터 행을 작성하십시오.
3. 엔진은 이 새로운 데이터 행을 메모리에 업데이트하고 업데이트 작업을 리두 로그에 기록합니다. 이때 리두 로그는 준비 상태입니다. 그런 다음 실행이 완료되었으며 언제든지 트랜잭션을 제출할 수 있음을 실행자에게 알립니다.
4. 실행자는 이 작업의 binlog를 생성하고 binlog를 디스크에 씁니다.
5. Executor는 엔진의 커밋 트랜잭션 인터페이스를 호출하고, 엔진은 방금 작성된 Redo 로그를 커밋 상태로 변경하며 업데이트가 완료됩니다.

여기서 이 업데이트 문의 실행 흐름도를 제공합니다. 그림의 밝은 상자는 InnoDB 내에서 실행됨을 나타내고 어두운 상자는 실행기에서 실행됨을 나타냅니다.

리두 로그 작성은 준비와 커밋의 두 단계로 나누어져 있다는 점을 눈치채셨을 것입니다.

2단계 제출

"2단계 제출"이 필요한 이유는 무엇인가요? 이는 두 로그 간의 논리를 일관되게 만들기 위한 것입니다. 이 문제를 설명하려면 기사 시작 부분에 있는 질문부터 시작해야 합니다. 반달 내에 데이터베이스를 임의의 초 상태로 복원하는 방법은 무엇입니까?

앞서 말했듯이 binlog는 모든 논리 연산을 기록하고 "쓰기 추가" 형식을 채택합니다. DBA가 반달 이내에 복원할 수 있다고 약속하면 백업 시스템은 확실히 지난 반달의 모든 binlog를 저장하고 시스템은 전체 데이터베이스를 정기적으로 백업합니다. 여기서 "정규"는 시스템의 중요성에 따라 달라지며 하루에 한 번 또는 일주일에 한 번일 수 있습니다.

예를 들어 어느 날 오후 2시와 같이 지정된 초로 복원해야 할 때 정오에 실수로 테이블이 삭제된 것을 발견하고 데이터를 검색해야 하는 경우

• 먼저 가장 최근 시간을 찾습니다. 전체 백업, 운이 좋다면 어젯밤의 백업일 수도 있고 이 백업에서 임시 데이터베이스로 복원할 수도 있습니다.
• 그런 다음 백업 시간부터 시작합니다. 시점에 백업된 binlog를 순차적으로 꺼내어, 그 전 그 순간 정오에 실수로 테이블이 삭제될 때까지 재생한다.

이렇게 하면 임시 데이터베이스를 실수로 삭제하기 전의 온라인 데이터베이스와 동일하게 됩니다. 그러면 필요에 따라 임시 데이터베이스에서 테이블 데이터를 꺼내 온라인 데이터베이스에 복원할 수 있습니다.

자, 이제 데이터 복구 프로세스에 대한 이야기를 마쳤으니 다시 돌아와서 로그에 "2단계 커밋"이 필요한 이유에 대해 이야기해 보겠습니다. 여기서 우리는 설명하기 위해 모순에 의한 증명을 사용할 수도 있습니다.

아직도 이전 업데이트 문을 예로 사용하고 있습니다. ID=2인 현재 행의 c 필드 값이 0이라고 가정하고, 업데이트 문을 실행하는 동안 첫 번째 로그가 기록된 후 두 번째 로그가 기록되기 전에 충돌이 발생한다고 가정하면 어떻게 될까요?

1. 리두 로그를 먼저 작성한 다음 binlog를 작성하세요. redo 로그가 기록될 때 binlog가 기록되기 전에 MySQL 프로세스가 비정상적으로 다시 시작된다고 가정해 보겠습니다. 앞서 말했듯이 리두 로그가 작성된 후 시스템이 충돌하더라도 데이터는 여전히 복원될 수 있으므로 복구 후 이 줄의 c 값은 1입니다. 그러나 binlog가 완료되기 전에 충돌이 발생했기 때문에 이 명령문은 현재 binlog에 기록되지 않았습니다. 따라서 나중에 로그를 백업할 때 저장된 binlog에는 이 문장이 포함되지 않습니다. 그러면 이 binlog를 사용하여 임시 라이브러리를 복원해야 하는 경우 이 명령문의 binlog가 손실되므로 이번에는 임시 라이브러리가 업데이트되지 않습니다. 복원된 행의 c 값은 0입니다. 원래 라이브러리의 값과 동일합니다.
2. binlog를 먼저 작성한 다음 로그를 다시 실행하세요. binlog가 작성된 후 충돌이 발생하면 redo 로그가 아직 작성되지 않았으므로 충돌 복구 후 트랜잭션이 유효하지 않으므로 이 줄의 c 값은 0입니다. 그러나 "Change c from 0 to 1" 로그가 binlog에 기록되었습니다. 따라서 나중에 binlog를 사용하여 복원하면 트랜잭션이 하나 더 나오게 되는데, 복원된 행의 c 값은 1로 원래 데이터베이스의 값과 다릅니다.

"2단계 커밋"을 사용하지 않으면 데이터베이스의 상태가 해당 로그를 사용하여 복원된 라이브러리의 상태와 일치하지 않을 수 있음을 알 수 있습니다.

언제든지 임시 도서관을 복원해야 하는 상황은 없을 확률이 매우 낮지 않나요?

사실 아니요. 이 프로세스는 오작동 후 데이터를 복구하는 데만 필요한 것은 아닙니다. 용량을 확장해야 할 때, 즉 시스템의 읽기 용량을 늘리기 위해 더 많은 대기 데이터베이스를 구축해야 할 때, 이제 일반적인 관행은 이를 달성하기 위해 전체 백업을 사용하고 binlog를 적용하는 것입니다. 온라인 마스터 데이터베이스와 슬레이브 데이터베이스 간의 불일치.

간단히 말하면 redo log와 binlog는 모두 트랜잭션의 커밋 상태를 나타내는 데 사용될 수 있으며, 2단계 커밋은 두 상태를 논리적으로 일관되게 유지하는 것입니다.

관련 추천: "mysql 튜토리얼"

위 내용은 SQL 업데이트 문을 실행하는 방법은 무엇입니까?의 상세 내용입니다. 자세한 내용은 PHP 중국어 웹사이트의 기타 관련 기사를 참조하세요!