찾다

집  >  기사  >  데이터 베이스  >  MySQL 아키텍처 원리에 대한 자세한 그래픽 설명

MySQL 아키텍처 원리에 대한 자세한 그래픽 설명

WBOY
WBOY앞으로
2022-05-17 17:54:142530검색

이 기사에서는 mysql에 대한 관련 지식을 제공하며, 주로 아키텍처 원리에 대한 관련 내용을 소개합니다. MySQL Server 아키텍처는 크게 네트워크 연결 계층, 서비스 계층, 스토리지 엔진 계층 및 시스템으로 나눌 수 있습니다. 파일 레이어를 살펴보시면 모든 분들께 도움이 되길 바랍니다.

MySQL 아키텍처 원리에 대한 자세한 그래픽 설명

추천 학습: mysql 동영상 튜토리얼

Mysql 아키텍처 원리

1. Mysql 아키텍처

MySQL 서버 아키텍처는 위에서 아래로 크게 네트워크 연결 계층, 서비스 계층, 스토리지 엔진 계층으로 나눌 수 있습니다. 및 시스템 파일 계층.

MySQL 아키텍처 원리에 대한 자세한 그래픽 설명

네트워크 연결 레이어

  • 클라이언트 커넥터: MySQL 서버와의 설정을 지원합니다. 현재, 해당 API 기술을 통해 MySQL과 연결을 설정하는 일반적인 Java, C, Python, .NET 등과 같은 거의 모든 주류 서버 측 프로그래밍 기술을 지원합니다.

서비스 계층(MySQL Server)

서비스 계층은 MySQL Server의 핵심이며 주로 시스템 관리 및 제어 도구, 연결 풀, SQL 인터페이스, 파서, 쿼리 최적화 프로그램 및 캐시의 여섯 부분으로 구성됩니다.

  • Connection Pool: 클라이언트와 데이터베이스 간의 연결을 저장하고 관리하는 역할을 담당합니다. 하나의 스레드가 하나의 연결을 관리합니다.

  • 시스템 관리 및 제어 도구(관리 서비스 및 유틸리티): 백업 및 복구, 보안 관리, 클러스터 관리 등

  • SQL 인터페이스(SQL 인터페이스): 클라이언트, 사용자가 쿼리해야 하는 결과를 반환합니다. DML, DDL, 저장 프로시저, 뷰, 트리거 등

  • Parser(Parser): 요청된 SQL을 구문 분석하여 "구문 분석 트리"를 생성하는 역할을 담당합니다. 그런 다음 일부 MySQL 규칙에 따라 구문 분석 트리가 합법적인지 추가로 확인하십시오.

  • 쿼리 최적화 프로그램(Optimizer): "파싱 트리"가 파서 문법 검사를 통과하면 최적화 프로그램으로 넘겨져 실행 계획으로 변환된 후 스토리지 엔진과 상호 작용합니다.

    성별 = 1인 사용자의 이름, uid 선택;

    Select--"Projection--"Join strategy

    1. select는 모든 데이터를 쿼리한 다음 필터링하는 대신 먼저 where 문을 기반으로 선택합니다. 쿼리 선택 속성 투영은 uid 및 이름을 기반으로 수행되며 모든 필드가 제거되지는 않습니다.
    2. 이전 선택과 투영을 연결하여 최종적으로 쿼리 결과를 생성합니다.
    • 캐싱 메커니즘은 다음으로 구성됩니다. 일련의 작은 캐시. 예를 들어 테이블 캐시, 레코드 캐시, 권한 캐시, 엔진 캐시 등이 있습니다. 쿼리 캐시에 적중 쿼리 결과가 있는 경우 쿼리 문은 쿼리 캐시에서 데이터를 직접 가져올 수 있습니다.

    • 스토리지 엔진 계층(플러그형 스토리지 엔진)

스토리지 엔진은 MySQL에서 데이터의 저장 및 검색을 담당하며 기본 시스템 파일과 상호 작용합니다. MySQL 스토리지 엔진은 플러그인입니다. 서버의 쿼리 실행 엔진은 인터페이스를 통해 스토리지 엔진과 통신합니다. 현재는 각각 고유한 특성을 지닌 많은 스토리지 엔진이 있으며 가장 일반적인 것은 MyISAM과 InnoDB입니다.
  • 시스템 파일 계층(파일 시스템)

이 계층은 데이터베이스 데이터와 로그를 파일 시스템에 저장하고 스토리지 엔진과의 상호 작용을 완료하는 역할을 담당합니다. 파일의 물리적 저장 계층입니다. 주로 로그 파일, 데이터 파일, 구성 파일, pid 파일, 소켓 파일 등이 포함됩니다.

  • 로그 파일
    • 오류 로그
      • 기본적으로 활성화되어 있으며, '%log_error%'와 같은 변수를 표시합니다.
    • 일반 쿼리 로그(일반 쿼리 로그)
      • 일반 쿼리 문을 기록하고, '%general%'과 같은 변수를 표시합니다. ;
    • 바이너리 로그(binary log)
      • 는 MySQL 데이터베이스에서 수행된 변경 작업을 기록하며, 명령문의 발생 시간과 실행 시간을 기록하지만, select, show 등을 수행하는 SQL은 기록하지 않습니다. 데이터베이스를 수정하지 마십시오. 주로 데이터베이스 복구 및 마스터-슬레이브 복제에 사용됩니다.
      • '%log_bin%' 같은 변수 표시; //활성화 여부
      • '%binlog%' 같은 변수 표시; //매개변수 보기
      • 바이너리 로그 표시
    • 느린 쿼리 로그( 느린 쿼리 로그)
      • 실행 시간이 초과된 모든 쿼리 SQL을 기록합니다. 기본값은 10초입니다.
      • '%slow_query%'와 같은 변수 표시; //활성화 여부
      • '%long_query_time%'와 같은 변수 표시 //Duration
  • 구성 파일
    • 은 모든 MySQL 구성 정보 파일을 저장하는 데 사용됩니다. my.cnf, my.ini 등과 같은
  • 데이터 파일
    • db.opt 파일: 이 라이브러리에서 사용하는 기본 문자 집합과 확인 규칙을 기록합니다.
    • frm 파일: 테이블 구조의 정의 정보 등 테이블과 관련된 메타데이터(meta) 정보를 저장합니다. 각 테이블에는 frm 파일이 있습니다.
    • MYD 파일: MyISAM 스토리지 엔진 전용이며 MyISAM 테이블의 데이터를 저장합니다. 각 테이블에는 .MYD 파일이 있습니다.
    • MYI 파일: MyISAM 테이블의 인덱스 관련 정보를 저장하는 MyISAM 스토리지 엔진 전용입니다. 각 MyISAM 테이블은 .MYI 파일에 해당합니다.
    • ibd 파일 및 IBDATA 파일: InnoDB 데이터 파일(인덱스 포함)을 저장합니다. InnoDB 스토리지 엔진에는 독점 테이블스페이스와 공유 테이블스페이스라는 두 가지 테이블스페이스 모드가 있습니다. 전용 테이블스페이스는 .ibd 파일을 사용하여 데이터를 저장하며 각 InnoDB 테이블은 하나의 .ibd 파일에 해당합니다. 공유 테이블스페이스는 .ibdata 파일을 사용하고, 모든 테이블은 하나(또는 여러 개의 자체 구성) .ibdata 파일을 사용합니다.
    • ibdata1 파일: 테이블 메타데이터, Undo 로그 등을 저장하는 시스템 테이블스페이스 데이터 파일입니다.
    • ib_logfile0, ib_logfile1 파일: Redo 로그 로그 파일입니다.
  • pid 파일
    • pid 파일은 Unix/Linux 환경에서 실행되는 mysqld 애플리케이션의 프로세스 파일로, 다른 많은 Unix/Linux 서버 프로그램과 마찬가지로 자체 프로세스 ID를 저장합니다.
  • socket 파일
    • socket 파일은 Unix/Linux 환경에서도 사용 가능합니다. Unix/Linux 환경에서 클라이언트 연결이 이루어지면 TCP/IP 네트워크를 거치지 않고 사용자가 Unix Socket을 사용하여 MySQL에 직접 연결할 수 있습니다.

2. MySQL 운영 메커니즘

MySQL 아키텍처 원리에 대한 자세한 그래픽 설명

  1. 연결(커넥터 및 연결 풀)을 설정하고 클라이언트/서버 통신 프로토콜을 통해 MySQL과 연결을 설정합니다. MySQL 클라이언트와 서버 간의 통신 방법은 "반이중"입니다. 각 MySQL 연결에는 항상 연결이 수행 중인 작업을 식별하는 스레드 상태가 있습니다.
    • 통신 메커니즘:
      • 전이중: 전화 통화 등 동시에 데이터를 보내고 받을 수 있습니다.
      • 반이중: 동시에 데이터를 보내거나 받는 것이 아닌 특정 순간을 의미합니다. 예를 들어 초기 워키토키
      • simplex: 데이터만 보낼 수 있거나 데이터만 받을 수 있습니다. 예를 들어 일방통행
    • Thread status: show processlist; //사용자가 실행 중인 스레드 정보를 봅니다. 루트 사용자는 모든 스레드를 볼 수 있으며, 다른 사용자는 자신의
      • id만 볼 수 있습니다. 스레드 ID, kill xx를 사용할 수 있습니다. ;
      • user: 이 스레드를 시작한 사용자
      • Host: 요청을 보낸 클라이언트의 IP 및 포트 번호
      • db: 현재 명령이 실행되는 라이브러리
      • Command : 이 스레드가 실행 중인 작업 명령
        • Create DB: 현재 라이브러리 생성 작업
        • Drop DB: 라이브러리 작업 삭제
        • Execute: 준비된 명령문 실행
        • Close Stmt: 준비된 명령문 닫기
        • Query: 명령문 실행
        • Sleep: 클라이언트가 명령문을 보내기를 기다리는 중
        • Quit: 종료 중
        • Shutdown: 서버 종료
      • Time: 스레드가 현재 상태에 있는 시간(초)을 나타냅니다.
      • State: 스레드 상태
        • 업데이트 중: 일치하는 레코드 검색 및 수정 중
        • 잠자는 중: 현재 클라이언트의 새 요청 전송을 기다리는 중
        • 시작 중: 요청 처리 중
        • 체크 테이블: 데이터 테이블을 확인 중
        • 닫는 테이블: 데이터 in the table is being Refreshing to the disk
        • Locked: 다른 쿼리에 의해 레코드가 잠겼습니다
        • Sending Data: Select 쿼리 처리와 동시에 그 결과를 클라이언트에 전송
      • Info: 일반적으로 명령문을 기록합니다. 스레드에 의해 실행되며 기본적으로 처음 100자를 표시합니다. show full processlist의 전체 사용법을 보고 싶습니다.
  2. Query Cache(Cache&Buffer)는 쿼리 캐시가 켜져 있고 쿼리 중에 정확히 동일한 SQL 문이 쿼리되는 경우 MySQL의 쿼리 캐시(Cache&Buffer)입니다. 쿼리 캐시 프로세스에서 쿼리 결과는 클라이언트에 직접 반환됩니다. 쿼리 캐시가 켜져 있지 않거나 정확히 동일한 SQL 문이 쿼리되지 않으면 파서는 구문 및 의미 분석을 수행하고 "구문 분석 트리"를 생성합니다.
    • Select 쿼리 및 SQL 문의 결과를 캐시합니다.
    • Select 쿼리를 실행할 때 먼저 캐시를 쿼리하여 사용 가능한 레코드 세트가 있는지, 요구 사항이 정확히 동일한지(매개변수 값 포함) 확인합니다.
    • 쿼리 캐시가 켜져 있어도 다음 SQL을 캐시할 수 없습니다.
      • 쿼리 문에서 SQL_NO_CACHE를 사용합니다
      • 쿼리 결과가 query_cache_limit 설정보다 큽니다.
      • 몇 가지가 있습니다. now()
    • '%query_cache %'와 같은 변수 표시, //쿼리 캐시 활성화 여부, 공간 크기, 제한 사항 등을 확인합니다.
    • 'Qcache%'와 같은 상태 표시 ; //자세한 캐시 매개변수, 사용 가능한 캐시 공간, 캐시 블록, 캐시 크기 등을 봅니다.
  3. 분석 파서는 클라이언트가 보낸 SQL을 구문 분석하고 "파싱 트리"를 생성합니다. 전처리기는 일부 MySQL 규칙을 기반으로 "파싱 트리"가 유효한지 여부를 추가로 확인합니다. 예를 들어 데이터 테이블과 데이터 열이 존재하는지 확인하고 이름과 별칭을 구문 분석하여 모호한지 확인하고 최종적으로 새로운 "파싱 트리".
  4. 쿼리 최적화 프로그램(Optimizer)은 "파싱 트리"를 기반으로 최적의 실행 계획을 생성합니다. MySQL은 최적의 실행 계획을 생성하기 위해 다양한 최적화 전략을 사용하며, 이는 정적 최적화(컴파일 시간 최적화)와 동적 최적화(런타임 최적화)라는 두 가지 범주로 나눌 수 있습니다.
    • 등가 변환 전략
      • 5=5 및 a>5가 a>5로 변경
      • a 5 및 a=5
      • 로 변경됨 결합 지수를 기준으로 조정
    • count, min, max 및 기타 기능 최적화
      • InnoDB 엔진 min 함수는 가장 왼쪽 인덱스만 찾으면 됩니다.
      • InnoDB 엔진 max 함수는 가장 오른쪽 인덱스만 찾으면 됩니다.
      • MyISAM 엔진 카운트 (*), 계산이 필요하지 않고 직접 반환
    • 쿼리를 조기에 종료합니다.
      • 제한 쿼리를 사용하여 후속 데이터를 계속 순회하지 않고 제한에 필요한 데이터를 얻습니다.
    • MySQL에서의 최적화
      • MySQL 먼저 쿼리를 정렬한 다음 이진 분할 방법을 사용하여 데이터를 찾습니다. 예를 들어 (2,1,3)의 id가 (1,2,3)이 되는 경우, 쿼리 실행 엔진은 SQL 문을 실행하는 역할을 담당합니다. SQL 문에 있는 테이블의 저장소를 기반으로 엔진 유형과 해당 API 인터페이스는 기본 저장소 엔진 캐시 또는 실제 파일과 상호 작용하여 쿼리 결과를 얻고 이를 클라이언트에 반환합니다. 쿼리 캐시를 활성화하면 SQL 문과 결과가 모두 쿼리 캐시(Cache&Buffffer)에 저장됩니다. 이후에도 동일한 SQL 문을 실행하면 결과가 바로 반환됩니다.
      쿼리 캐싱이 활성화된 경우 쿼리 결과를 먼저 캐시하세요
    반환된 결과가 너무 많으니 증분 모드를 사용하여 반환하세요
  5. 실행을 시작할 때 먼저 이 테이블 T에 대한 쿼리 실행 권한이 있는지 확인해야 합니다. 그렇지 않으면 권한 없음 오류가 반환됩니다. (쿼리 캐시에 도달하면 쿼리 캐시가 결과를 반환할 때 권한 확인이 수행됩니다. 쿼리는 또한 최적화 프로그램 전에 권한을 확인하기 위해 사전 확인을 호출합니다.)
    • 권한이 있으면 테이블을 열고 실행을 계속하세요. 테이블이 열리면 실행기는 테이블의 엔진 정의에 따라 엔진이 제공하는 인터페이스를 사용합니다. 실행기의 실행 흐름은 다음과 같습니다.
    • select * from test where age >
    • InnoDB 엔진 인터페이스를 호출하여 이 테이블의 첫 번째 행을 가져오고 age 값이 10인지 확인합니다. 그런 다음 이 행을 결과 세트에 저장합니다.
    • 엔진 인터페이스를 호출하여 "다음 행"을 가져오고 이 테이블의 마지막 행을 가져올 때까지 동일한 판단 논리를 반복합니다.
      • Executor는 위의 순회 과정에서 조건을 충족하는 모든 행으로 구성된 레코드 세트를 결과 세트로 클라이언트에 반환합니다.
      • 3. MySQL 스토리지 엔진
    스토리지 엔진은 MySQL 아키텍처의 세 번째 계층에 위치하며, MySQL에서 데이터의 저장 및 추출을 담당합니다. . 이는 제공된 파일 액세스 계층 추상 인터페이스에 의해 사용자 정의된 파일 액세스 메커니즘인 MySQL을 기반으로 합니다.

현재 데이터베이스에서 지원하는 엔진 정보를 보려면

showengines

명령을 사용하세요.

5.5 이전 버전에서는 MyISAM 스토리지 엔진을 기본으로 사용하였고, 5.5 버전부터는 InnoDB 스토리지 엔진을 사용하였습니다.

  • InnoDB: 트랜잭션을 지원하고 커밋, 롤백 및 충돌 복구 기능이 있으며 트랜잭션 안전성이 있습니다.
  • MyISAM: 트랜잭션 및 외래 키를 지원하지 않으며 액세스 속도가 빠릅니다.
  • 메모리: 테이블을 생성하기 위해 메모리를 사용합니다. 데이터는 메모리에 있고 기본적으로 해시 인덱스가 사용되지만 일단 닫히면 데이터가 손실됩니다.
  • Archive: 아카이브 유형 엔진, 삽입 및 선택 문만 지원합니다.
  • Csv: 데이터 저장; CSV 파일에서는 파일 제한으로 인해 모든 열에 null이 아님을 필수로 지정해야 합니다. 또한 CSV 엔진은 인덱스 및 파티션을 지원하지 않으므로 데이터 교환을 위한 중간 테이블에 적합합니다.
  • BlackHole, 들어가기만 하고 나가지는 않으며, 들어갈 때 사라지고, 삽입된 모든 데이터는 저장되지 않습니다.
  • 연합: 원격 MySQL 데이터베이스의 테이블에 액세스할 수 있습니다. 로컬 테이블은 데이터를 저장하지 않으며 원격 테이블의 내용에 액세스합니다.
  • MRG_MyISAM: MyISAM 테이블의 조합은 동일한 구조를 가져야 합니다. 병합 테이블 자체에는 데이터가 없습니다. Merge 작업은 MyISAM 테이블 그룹에서 작동할 수 있습니다.

트랜잭션 및 외래 키

    InnoDB는 보안 및 무결성을 갖추고 많은 삽입 또는 업데이트 작업에 적합한 트랜잭션 및 외래 키를 지원합니다.
    • MyISAM은 트랜잭션 및 외래 키를 지원하지 않으며 고속 저장 및 검색을 제공합니다. , 다수의 선택 쿼리 작업에 적합
    • 잠금 메커니즘
    • InnoDB는 행 수준 잠금, 지정된 레코드 잠금을 지원합니다. 잠금은 인덱스를 기반으로 구현됩니다.
    • MyISAM은 테이블 수준 잠금을 지원하여 전체 테이블을 잠급니다.
    • 인덱스 구조
    • InnoDB는 클러스터형 인덱스(clustered index)를 사용하여 인덱스와 레코드를 함께 캐싱합니다.
    • MyISAM은 비클러스터형 인덱스(non-clustered index)를 사용하며, 인덱스와 레코드가 분리되어 있습니다.
    • 동시 처리 기능
    • MyISAM은 테이블 잠금을 사용하므로 쓰기 작업의 동시성 비율이 낮고 읽기 간 차단이 없으며 읽기 및 쓰기 차단이 가능합니다.
    • InnoDB 읽기 및 쓰기 차단은 격리 수준과 관련될 수 있으며 다중 버전 동시성 제어(MVCC)를 사용하여 높은 동시성을 지원할 수 있습니다.
    • 저장 파일
    • InnoDB 테이블은 두 개의 파일, 즉 하나의 .frm에 해당합니다. 테이블 구조 파일과 하나의 .ibd 데이터 파일. InnoDB 테이블은 최대 64TB를 지원합니다.
    • MyISAM 테이블은 .frm 테이블 구조 파일, MYD 테이블 데이터 파일 및 .MYI 인덱스 파일의 세 가지 파일에 해당합니다.
    • MySQL5.0부터 기본 제한은 256TB입니다.

      • 적용 가능한 시나리오
    • MyISAM
    • 트랜잭션 지원 필요 없음(지원되지 않음)
      • 동시성이 상대적으로 낮음(잠금 메커니즘 문제)
      • 데이터 수정이 상대적으로 적고 주로 읽기
      • 데이터 일관성 요구 사항이 높지 않음
      • InnoDB
      • 트랜잭션 지원 필요(트랜잭션 특성이 더 좋음)
      • 행 수준 잠금은 높은 동시성에 대한 적응성이 우수함
      • 데이터 업데이트가 더 빈번한 시나리오
      • 높은 데이터 일관성 요구 사항
      • 하드웨어 장치에 대용량 메모리가 있는 경우 , InnoDB의 향상된 캐싱 기능을 사용하여 메모리 활용도를 높이고 디스크 IO
      • 요약
    • 두 엔진 중에서 선택하는 방법은 무엇입니까?
    • 거래가 필요하신가요? 예, InnoDB
      • 에는 동시 수정이 있습니까? 네, InnoDB
      • 는 빠른 쿼리와 적은 데이터 수정을 추구합니까? 네, MyISAM
      • 대부분의 경우 InnoDB를 사용하는 것이 좋습니다
    InnoDB 저장 구조

MySQL 버전 5.5부터는 기본적으로 InnoDB가 엔진으로 사용됩니다. 자동 충돌 복구 기능이 있습니다. 다음은 공식 InnoDB 엔진 아키텍처 다이어그램으로, 주로 메모리 구조와 디스크 구조의 두 부분으로 나뉩니다.

InnoDB 메모리 구조MySQL 아키텍처 원리에 대한 자세한 그래픽 설명

메모리 구조에는 주로 버퍼 풀, 변경 버퍼, 적응형 해시 인덱스 및 로그 버퍼의 네 가지 주요 구성 요소가 포함됩니다.

  • 버퍼 풀: BP라고도 하는 버퍼 풀입니다. BP는 페이지를 기반으로 하며 기본 크기는 16K입니다. BP의 하위 계층은 페이지를 관리하기 위해 연결된 목록 데이터 구조를 사용합니다. InnoDB가 테이블 레코드와 인덱스에 액세스하면 페이지 페이지에 캐시됩니다. 나중에 사용하면 디스크 IO 작업이 줄어들고 효율성이 향상됩니다.
    • 페이지 관리 메커니즘
      • 페이지는 상태에 따라 세 가지 유형으로 나눌 수 있습니다:
        1. free 페이지: 사용되지 않은 페이지, 사용되지 않음
        2. clean 페이지: 사용된 페이지, 데이터가 수정되지 않음
        3. dirty 페이지: 더티 페이지, 페이지가 사용되었고, 데이터가 수정되었으며, 페이지의 데이터와 디스크의 데이터가 일치하지 않습니다.
      • 위의 세 가지 페이지 유형에 대해 InnoDB는 세 가지 연결 목록 구조를 통해 유지 관리합니다.
        1. 무료 목록: 여유 버퍼를 나타냅니다. 여유 페이지를 관리합니다.
        2. 플러시 목록: 디스크에 플러시해야 하는 버퍼를 나타내며 더티 페이지를 관리하며 내부 페이지는 수정 시간별로 정렬됩니다. 더티 페이지는 플러시 링크드 리스트와 LRU 링크드 리스트 모두에 존재하지만 서로 영향을 미치지 않습니다. LRU 링크드 리스트는 페이지의 가용성과 저장을 관리하는 반면, 플러시 링크드 리스트는 플러싱 작업을 관리합니다. 더티 페이지.
        3. lru 목록: 사용 중인 버퍼를 나타내며, 클린 페이지와 더티 페이지를 관리합니다. 버퍼는 중간점을 기준으로 하며, 자주 액세스하는 데이터를 저장하는 새 목록 영역입니다. 목록은 덜 사용되는 데이터를 저장하는 영역으로 37%를 차지합니다.
    • LRU 알고리즘 유지 관리 개선
      • 일반 LRU: 끝 제거 방법, 연결 목록의 선두부터 새 데이터가 추가되고 공간이 해제되면 끝에서 제거됩니다.
      • 수정된 LRU: 연결 목록이 새 부분과 이전 부분으로 나누어집니다. 요소를 추가하면 목록의 선두부터 삽입되지 않고 중간 지점부터 삽입됩니다. 데이터에 빠르게 액세스하면 페이지가 새 목록의 선두로 이동합니다. 액세스되지 않은 경우 점차적으로 이전 목록의 끝으로 이동하여 제거될 때까지 기다립니다.
      • 새 페이지 데이터를 버퍼 풀로 읽어올 때마다 InnoDb 엔진은 사용 가능한 페이지가 있는지, 충분한지 확인합니다. 사용 가능한 페이지는 사용 가능 목록에서 삭제되고 LRU 목록에 배치됩니다. 여유 페이지가 없으면 LRU 알고리즘에 따라 LRU 연결 목록의 기본 페이지가 제거되고 메모리 공간이 해제되어 새 페이지에 할당됩니다.
    • 버퍼 풀 구성 매개변수
      • '%innodb_page_size%'와 같은 변수 표시; //페이지 페이지 크기 보기
      • '%innodb_old%'와 같은 변수 표시 //lru 목록에서 이전 목록 매개변수 보기
      • 와 같은 변수 표시 '%innodb_buffer%'; //버퍼 풀 매개변수를 확인하세요
      • 권장 사항: innodb_buffer_pool_size를 전체 메모리 크기의 60%-80%로 설정하고 innodb_buffer_pool_instances를 여러 개로 설정하여 캐시 경합을 피할 수 있습니다.
  • Change Buffer: 쓰기 버퍼, CB라고 합니다. DML 작업을 수행할 때 BP에 해당 페이지 데이터가 없으면 디스크 페이지가 즉시 버퍼 풀에 로드되지 않고 대신 버퍼 변경 사항이 CB에 기록되며 나중에 데이터를 읽을 때 데이터가 병합되어 BP 중간에 복원됩니다.
    • ChangeBuffer는 BufferPool 공간을 차지합니다. 기본값은 25%이고 허용되는 최대값은 50%입니다. 읽기 및 쓰기 비즈니스 볼륨에 따라 조정될 수 있습니다. Parameter innodb_change_buffer_max_size;
    • 레코드가 업데이트되면 해당 레코드는 BufferPool에 존재하며 메모리 작업인 BufferPool에서 직접 수정됩니다. 레코드가 BufferPool에 없으면(적중 없음) 디스크에 데이터를 쿼리하고 디스크 IO를 방지할 필요 없이 ChangeBuffer에서 직접 메모리 작업이 수행됩니다. 다음에 레코드를 쿼리할 때 먼저 디스크에서 읽은 다음 ChangeBuffer에서 정보를 읽고 병합한 다음 마지막으로 BufferPool에 로드합니다.
    • 쓰기 버퍼, 고유하지 않은 일반 인덱스 페이지에만 적용 가능
    • 인덱스에 고유성이 설정된 경우 InnoDB는 수정 시 고유성 확인을 수행해야 하므로 디스크를 쿼리하고 IO 작업을 수행해야 합니다. 레코드는 BufferPool에 직접 쿼리된 다음 버퍼 풀에서 수정됩니다. ChangeBuffer에서는 작동되지 않습니다.
  • 적응형 해시 인덱스: 적응형 해시 인덱스는 BP 데이터에 대한 쿼리를 최적화하는 데 사용됩니다. InnoDB 스토리지 엔진은 테이블 인덱스 검색을 모니터링하여 해시 인덱스를 구축하면 속도가 향상될 수 있다고 판단되면 해시 인덱스를 구축하므로 이를 적응형이라고 합니다. InnoDB 스토리지 엔진은 액세스 빈도와 패턴을 기반으로 특정 페이지에 대한 해시 인덱스를 자동으로 생성합니다.
  • 로그 버퍼: 로그 버퍼는 로그 파일에 기록할 데이터(Redo/Undo)를 디스크에 저장하는 데 사용됩니다. 로그 버퍼의 내용은 정기적으로 디스크 로그 파일에 새로 고쳐집니다. 로그 버퍼가 가득 차면 자동으로 디스크로 플러시됩니다. BLOB 또는 다중 행 업데이트와 같은 대규모 트랜잭션 작업이 발생하는 경우 로그 버퍼를 늘리면 디스크 I/O를 절약할 수 있습니다.
    • LogBuffer는 주로 InnoDB 엔진 로그를 기록하는 데 사용됩니다. DML 작업 중에 Redo 및 Undo 로그가 생성됩니다.
    • LogBuffer 공간이 가득 차면 자동으로 디스크에 기록됩니다.innodb_log_buffer_size 매개변수를 늘려 디스크 IO 빈도를 줄일 수 있습니다.
    • innodb_flush_log_at_trx_commit 매개변수는 로그 새로 고침 동작을 제어합니다. 기본값은 1
      • 0입니다. 1초마다 로그 파일을 쓰고 디스크 작업을 플러시합니다(로그 파일 LogBuffer 쓰기 --> OS 캐시, OScache 플러시 --> 디스크 파일), 데이터는 최대 1초 동안 손실됩니다
      • 1: 트랜잭션 제출, 로그 파일 쓰기 및 디스크 즉시 플러시, 데이터는 손실되지 않지만 빈번한 IO 작업이 발생합니다
      • 2: 트랜잭션 제출 즉시 1초마다 로그 파일을 작성하고 디스크를 플러시합니다

InnoDB 디스크 구조

InnoDB 디스크에는 주로 테이블스페이스, InnoDB 데이터 사전, Doublewrite 버퍼, Redo 로그 및 Undo 로그가 포함됩니다.

  • 테이블스페이스: 테이블 구조와 데이터를 저장하는 데 사용됩니다. 테이블스페이스는 시스템 테이블스페이스, 독립 테이블스페이스, 일반 테이블스페이스, 임시 테이블스페이스, Undo 테이블스페이스 및 기타 유형으로 구분됩니다.

    • 시스템 테이블스페이스(The System Tablespace)

      • 에는 InnoDB 데이터 사전, Doublewrite Storage가 포함됩니다. 버퍼, 변경 버퍼 및 실행 취소 로그를 위한 영역입니다. 시스템 테이블스페이스에는 기본적으로 시스템 테이블스페이스의 모든 사용자가 작성한 테이블 데이터 및 인덱스 데이터도 포함됩니다. 시스템 테이블스페이스는 여러 테이블이 공유하므로 공유 테이블스페이스입니다. 이 공간의 데이터 파일은 innodb_data_file_path 매개변수에 의해 제어됩니다. 기본값은 ibdata1:12M:autoextend입니다(파일 이름은 ibdata1, 12MB, 자동 확장됨). 
      • CREATE TABLESPACE ts1 ADD DATAFILE ts1.ibd Engine=InnoDB; //创建表空 间ts1 CREATE TABLE t1 (c1 INT PRIMARY KEY) TABLESPACE ts1; //将表添加到ts1 表空间

    • 독립 테이블스페이스(File-Per-Table Tablespaces)

      • 는 기본적으로 활성화되어 있습니다. 독립 테이블스페이스는 시스템이 아닌 자체 데이터 파일에 생성됩니다. 테이블 공간. innodb_file_per_table 옵션이 활성화되면 테이블스페이스에 테이블이 생성됩니다. 그렇지 않으면 시스템 테이블스페이스에 innodb가 생성됩니다. 각 테이블 파일 테이블스페이스는 기본적으로 데이터베이스 디렉터리에 생성되는 .ibd 데이터 파일로 표시됩니다. 테이블스페이스 테이블 파일은 동적(동적) 및 압축(압축) 행 형식을 지원합니다.

    • 일반 테이블스페이스

      • 일반 테이블스페이스는 create tablespace 구문을 통해 생성된 공유 테이블스페이스입니다. 일반 테이블스페이스는 mysql 데이터 디렉터리 외부의 다른 테이블스페이스에 생성될 수 있으며, 여러 테이블을 수용할 수 있으며 모든 행 형식을 지원합니다.

    • Undo 테이블스페이스

      • Undo 테이블스페이스는 Undo 로그 파일이 포함된 하나 이상의 파일로 구성됩니다. MySQL 5.7 이전에는 Undo가 시스템 테이블스페이스 공유 영역을 차지했습니다. 5.7부터 Undo는 시스템 테이블스페이스와 분리되었습니다.
      • InnoDB에서 사용하는 실행 취소 테이블스페이스는 innodb_undo_tablespaces 구성 옵션에 의해 제어되며 기본값은 0입니다. 매개변수 값이 0이면 시스템 테이블스페이스 ibdata1을 사용한다는 의미이고, 매개변수 값이 0보다 크다면 undo_001, undo_002 등을 사용한다는 의미입니다.

    • 임시 테이블스페이스

      • 는 세션 임시 테이블스페이스와 전역 임시 테이블스페이스의 두 가지 유형으로 나뉩니다.
        1. 세션 임시 테이블스페이스는 사용자가 생성한 임시 테이블과 디스크 내부의 임시 테이블을 저장합니다.
        2. 전역 임시 테이블스페이스는 사용자 임시 테이블의 롤백 세그먼트(롤백 세그먼트)를 저장합니다. mysql 서버가 정상적으로 종료되거나 비정상적으로 종료되면 임시 테이블스페이스는 제거되고 서버가 구동될 때마다 다시 생성된다.

  • 데이터 사전(InnoDB 데이터 사전)

    • InnoDB 데이터 사전은 조회 테이블, 인덱스, 테이블 필드와 같은 객체에 대한 메타데이터를 포함하는 내부 시스템 테이블로 구성됩니다. 메타데이터는 물리적으로 InnoDB 시스템 테이블스페이스에 위치합니다. 역사적인 이유로 데이터 사전 메타데이터는 InnoDB 테이블 메타데이터 파일(.frm 파일)에 저장된 정보와 어느 정도 겹칩니다.

  • Doublewrite Buffer

    • 는 시스템 테이블 공간에 위치하며 저장 영역이다. BufferPage 페이지가 디스크의 실제 위치로 새로 고쳐지기 전에 데이터는 Doublewrite 버퍼에 저장됩니다. 페이지가 기록되는 동안 운영 체제, 스토리지 하위 시스템 또는 mysqld 프로세스가 충돌하는 경우 InnoDB는 충돌 복구 중에 Doublewrite 버퍼에서 페이지의 적절한 백업을 찾을 수 있습니다. 대부분의 경우 doublewrite 버퍼는 기본적으로 활성화되어 있습니다. Doublewrite 버퍼를 비활성화하려면 innodb_doublewrite를 0으로 설정하면 됩니다. Doublewrite 버퍼를 사용할 때는 innodb_flush_method를 O_DIRECT로 설정하는 것이 좋습니다.
      • MySQL의 innodb_flush_method 매개변수는 innodb 데이터 파일과 다시 실행 로그의 열기 및 플러시 모드를 제어합니다. 세 가지 값이 있습니다: fdatasync(기본값), O_DSYNC, O_DIRECT. O_DIRECT를 설정하면 데이터 파일 쓰기 작업이 운영 체제에 데이터를 캐시하지 않고, 사전 읽기를 사용하지 않고, InnodbBuffer에서 디스크 파일로 직접 쓰도록 알립니다.
      • 기본 fdatasync는 먼저 운영 체제 캐시에 쓴 다음 fsync() 함수를 호출하여 데이터 파일 및 다시 실행 로그의 캐시 정보를 비동기적으로 플러시하는 것을 의미합니다.

  • 다시 실행 로그

    • 리두 로그는 충돌 복구 중 불완전한 트랜잭션으로 작성된 데이터를 수정하는 데 사용되는 디스크 기반 데이터 구조입니다. MySQL은 순환 방식으로 리두 로그 파일을 작성하고 InnoDB의 버퍼 풀에 대한 모든 수정 사항을 기록합니다. 정전 등 인스턴스 장애가 발생하여 데이터 파일에 데이터가 업데이트되지 못한 경우, 데이터베이스를 재시작할 때 데이터베이스를 다시 실행하여 데이터 파일에 데이터를 다시 업데이트해야 합니다. 읽기 및 쓰기 트랜잭션이 실행되는 동안 리두 로그가 계속 생성됩니다. 기본적으로 리두 로그는 ib_logfile0 및 ib_logfile1이라는 두 개의 파일로 디스크에 물리적으로 표시됩니다.

  • 실행 취소 로그

    • 실행 취소 로그는 트랜잭션 시작 전에 저장된 수정된 데이터의 백업으로, 예외적인 상황에서 트랜잭션을 롤백하는 데 사용됩니다. 실행 취소 로그는 논리적 로그이며 행별로 기록됩니다. Undo 로그는 시스템 테이블스페이스, Undo 테이블스페이스, 임시 테이블스페이스에 존재합니다.

새 버전 구조 진화

MySQL 아키텍처 원리에 대한 자세한 그래픽 설명

  • MySQL 5.7 버전
    • 은 Undo 로그 테이블 공간을 공유 테이블 공간 ibdata 파일과 분리했으며, 사용자가 MySQL 설치 시 파일 크기와 수량을 지정할 수 있습니다.
    • 임시 테이블 또는 임시 쿼리 결과 세트의 데이터를 저장하는 임시 테이블 공간을 추가했습니다.
    • 버퍼 풀 크기는 데이터베이스 인스턴스를 다시 시작하지 않고도 동적으로 수정할 수 있습니다.
  • MySQL 8.0 버전
    • 은 공유 테이블스페이스 ibdata에서 InnoDB 테이블의 데이터 사전과 Undo를 완전히 분리했습니다. 과거에는 ibdata의 데이터 사전이 독립 테이블스페이스 ibd의 데이터 사전과 일치해야 했습니다. 파일 버전 8.0 필요하지 않습니다.
    • 임시 임시 테이블 공간은 여러 개의 물리적 파일로 구성할 수도 있으며 모두 InnoDB 스토리지 엔진이며 인덱스를 생성할 수 있어 처리 속도가 빨라집니다.
    • 사용자는 Oracle 데이터베이스와 같은 일부 테이블 공간을 설정할 수 있습니다. 각 테이블 공간은 여러 실제 파일에 해당합니다. 각 테이블 공간은 여러 테이블에서 사용할 수 있지만 테이블은 하나의 테이블 공간에만 저장할 수 있습니다.
    • Doublewrite 버퍼는 공유 테이블 공간 ibdata와도 분리되어 있습니다.

InnoDB 스레드 모델

MySQL 아키텍처 원리에 대한 자세한 그래픽 설명

  • IO Thread
    • 는 읽기 및 쓰기 처리를 위해 InnoDB에서 대량의 AIO(Async IO)를 사용하므로 데이터베이스 성능을 크게 향상시킬 수 있습니다. InnoDB에는 쓰기 4개, 읽기 4개, 삽입 버퍼 1개, 로그 스레드 1개를 포함하여 10개의 IO 스레드가 있습니다.
        읽기 스레드: 작업을 읽고 디스크에서 캐시 페이지로 데이터를 로드하는 역할을 담당합니다. 4
      • 쓰기 스레드: 쓰기 작업과 캐시된 더티 페이지를 디스크에 플러시하는 역할을 담당합니다. 4
      • 로그 스레드: 로그 버퍼 내용을 디스크로 플러시하는 역할을 담당합니다. 1
      • 삽입 버퍼 스레드: 쓰기 버퍼 내용을 디스크로 플러시하는 역할을 담당합니다.
    • Purge Thread
    • 트랜잭션이 커밋된 후에는 해당 트랜잭션에 사용된 실행 취소 로그가 더 이상 필요하지 않으므로 퍼지 스레드는 할당된 실행 취소 페이지를 재활용해야 합니다.
    • '%innodb_purge_threads%'와 같은 변수 표시
    • Page Cleaner Thread
    • 더티 데이터를 디스크에 플러시하는 기능은 더티 데이터가 플러시된 후 해당 리두 로그를 덮어쓸 수 있음을 의미합니다. 데이터를 동기화할 수 있으며
    • 리두 로그 재활용 목적을 달성합니다. 쓰기 스레드 스레드 처리가 호출됩니다.
      • '%innodb_page_cleaners%'와 같은 변수 표시;
    • 마스터 스레드
    • 마스터 스레드는 InnoDB의 메인 스레드로, 가장 높은 우선순위로 다른 스레드의 스케줄링을 담당합니다. 그 기능은 데이터 일관성을 보장하기 위해 버퍼 풀의 데이터를 디스크에 비동기식으로 새로 고치는 것입니다. 포함: 더티 페이지 새로 고침(페이지 클리너 스레드), 실행 취소 페이지 재활용(제거 스레드), 다시 실행 로그 새로 고침(로그 스레드), 병합된 쓰기 버퍼 등 내부에는 1초마다 하나씩, 10초마다 하나씩, 두 가지 주요 프로세스가 있습니다.
    • 1초마다 작업:
      • 로그 버퍼 새로 고침 및 디스크 플러시
      • 쓰기 버퍼 데이터 병합, IO 읽기 및 쓰기 압력에 따라 작동 여부 결정
      • 더티 페이지 비율에 따라 디스크에 더티 페이지 데이터 새로 고침 75% 도달 전용 작업(innodb_max_dirty_pages_pct,
      • innodb_io_capacity)
      • 10초마다 작업:
      • 더티 페이지 데이터를 디스크로 새로 고침
      • 쓰기 버퍼 데이터 병합
      • 로그 버퍼 새로 고침
      • 쓸모 없는 실행 취소 페이지 삭제
InnoDB 데이터 파일

InnoDB 파일 저장 구조

MySQL 아키텍처 원리에 대한 자세한 그래픽 설명

  • InnoDB 데이터 파일 저장 구조

    • 分为 ibd数据文件 --> Segment（段）–>Extent（区）–> Page（页）–>Row（行）    
      • Tablesapce表空间，用于存储多个ibd数据文件，用于存储表的记录和索引。一个文件包含多个段。
      • Segment段，用于管理多个Extent，分为数据段（Leaf node segment）、索引段（Non-leaf node
        segment）、回滚段（Rollback segment）。一个表至少会有两个segment，一个管理数据，一个管理索引。每多创建一个索引，会多两个segment。
      • Extent区，一个区固定包含64个连续的页，大小为1M。当表空间不足，需要分配新的页资源，不会
        一页一页分，直接分配一个区。
      • Page页，用于存储多个Row行记录，大小为16K。包含很多种页类型，比如数据页，undo页，系统页，事务数据页，大的BLOB对象页。
      • Row行，包含了记录的字段值，事务ID（Trx id）、滚动指针（Roll pointer）、字段指针（Field
        pointers）等信息。

    • Page是文件最基本的单位，无论何种类型的page，都是由page header，page trailer和page body组成。如下图所示

MySQL 아키텍처 원리에 대한 자세한 그래픽 설명

  • InnoDB文件存储格式

    • 通过 SHOW TABLE STATUS 命令 查看

      MySQL 아키텍처 원리에 대한 자세한 그래픽 설명

      • 一般情况下，如果row_format为REDUNDANT、COMPACT，文件格式为Antelope；如果row_format为DYNAMIC和COMPRESSED，文件格式为Barracuda。

      • 通过 information_schema 查看指定表的文件格式

        • select * from information_schema.innodb_sys_tables;

  • File文件格式（File-Format）

    • 在早期的InnoDB版本中，文件格式只有一种，随着InnoDB引擎的发展，出现了新文件格式，用于支持新的功能。目前InnoDB只支持两种文件格式：Antelope 和 Barracuda。
      • Antelope: 先前未命名的，最原始的InnoDB文件格式，它支持两种行格式：COMPACT和REDUNDANT，MySQL 5.6及其以前版本默认格式为Antelope。
      • Barracuda: 新的文件格式。它支持InnoDB的所有行格式，包括新的行格式：COMPRESSED和 DYNAMIC。
    • 通过innodb_file_format 配置参数可以设置InnoDB文件格式，之前默认值为Antelope，5.7版本开始改为Barracuda。

  • Row行格式（Row_format）

MySQL 아키텍처 원리에 대한 자세한 그래픽 설명

  • 表的行格式决定了它的行是如何物理存储的，这反过来又会影响查询和DML操作的性能。如果在单个page页中容纳更多行，查询和索引查找可以更快地工作，缓冲池中所需的内存更少，写入更新时所需的I/O更少。

  • InnoDB存储引擎支持四种行格式：REDUNDANT、COMPACT、DYNAMIC和COMPRESSED。

  • DYNAMIC和COMPRESSED新格式引入的功能有：数据压缩、增强型长列数据的页外存储和大索引前缀。

  • 每个表的数据分成若干页来存储，每个页中采用B树结构存储；

  • 如果某些字段信息过长，无法存储在B树节点中，这时候会被单独分配空间，此时被称为溢出页，该字段被称为页外列。

    • REDUNDANT 行格式
      • 使用REDUNDANT行格式，表会将变长列值的前768字节存储在B树节点的索引记录中，其余
        的存储在溢出页上。对于大于等于786字节的固定长度字段InnoDB会转换为变长字段，以便
        能够在页外存储。
    • COMPACT 行格式
      • 与REDUNDANT行格式相比，COMPACT行格式减少了约20%的行存储空间，但代价是增加了
        某些操作的CPU使用量。如果系统负载是受缓存命中率和磁盘速度限制，那么COMPACT格式
        可能更快。如果系统负载受到CPU速度的限制，那么COMPACT格式可能会慢一些。
    • DYNAMIC 行格式
      • 使用DYNAMIC行格式，InnoDB会将表中长可变长度的列值完全存储在页外，而索引记录只包含指向溢出页的20字节指针。大于或等于768字节的固定长度字段编码为可变长度字段。DYNAMIC行格式支持大索引前缀，最多可以为3072字节，可通过innodb_large_prefix参数控制。
    • COMPRESSED 行格式
      • COMPRESSED行格式提供与DYNAMIC行格式相同的存储特性和功能，但增加了对表和索引
        数据压缩的支持。

  • 在创建表和索引时，文件格式都被用于每个InnoDB表数据文件（其名称与*.ibd匹配）。修改文件格式的方法是重新创建表及其索引，最简单方法是对要修改的每个表使用以下命令：

    • ALTER TABLE 表名 ROW_FORMAT=格式类型;

Undo Log

Undo Log介绍

  • Undo：意为撤销或取消，以撤销操作为目的，返回指定某个状态的操作。

  • Undo Log：数据库事务开始之前，会将要修改的记录存放到 Undo 日志里，当事务回滚时或者数据库崩溃时，可以利用 Undo 日志，撤销未提交事务对数据库产生的影响。

  • Undo Log产生和销毁：Undo Log在事务开始前产生；事务在提交时，并不会立刻删除undo log，innodb会将该事务对应的undo log放入到删除列表中，后面会通过后台线程purge thread进行回收处理。Undo Log属于逻辑日志，记录一个变化过程。例如执行一个delete，undolog会记录一个insert；执行一个update，undolog会记录一个相反的update。

  • Undo Log存储：undo log采用段的方式管理和记录。在innodb数据文件中包含一种rollback segment回滚段，内部包含1024个undo log segment。可以通过下面一组参数来控制Undo log存储。

  • #相关参数命令

show variables like '%innodb_undo%';

Undo Log作用

  • 实现事务的原子性
    • Undo Log 是为了实现事务的原子性而出现的产物。事务处理过程中，如果出现了错误或者用户执行了 ROLLBACK 语句，MySQL 可以利用 Undo Log 中的备份将数据恢复到事务开始之前的状态。

  • 实现多版本并发控制（MVCC）

    • Undo Log 在 MySQL InnoDB 存储引擎中用来实现多版本并发控制。事务未提交之前，Undo Log保存了未提交之前的版本数据，Undo Log 中的数据可作为数据旧版本快照供其他并发事务进行快照读。

MySQL 아키텍처 원리에 대한 자세한 그래픽 설명

  • 事务A手动开启事务，执行更新操作，首先会把更新命中的数据备份到 Undo Buffer 中；

  • 事务B手动开启事务，执行查询操作，会读取 Undo 日志数据返回，进行快照读；

  • Redo Log 和 Binlog

Redo Log 日志

  • Redo Log 介绍

    • Redo：顾名思义就是重做。以恢复操作为目的，在数据库发生意外时重现操作。
    • Redo Log：指事务中修改的任何数据，将最新的数据备份存储的位置（Redo Log），被称为重做日志。
    • Redo Log 的生成和释放：随着事务操作的执行，就会生成Redo Log，在事务提交时会将产生Redo Log写入Log Buffer，并不是随着事务的提交就立刻写入磁盘文件。等事务操作的脏页写入到磁盘之后，Redo Log 的使命也就完成了，Redo Log占用的空间就可以重用（被覆盖写入）。

  • Redo Log工作原理

MySQL 아키텍처 원리에 대한 자세한 그래픽 설명

  • Redo Log 是为了实现事务的持久性而出现的产物。防止在发生故障的时间点，尚有脏页未写入表
    的 IBD 文件中，在重启 MySQL 服务的时候，根据 Redo Log 进行重做，从而达到事务的未入磁盘
    数据进行持久化这一特性。

  • Redo Log写入机制

    Redo Log 文件内容是以顺序循环的方式写入文件，写满时则回溯到第一个文件，进行覆盖写。

MySQL 아키텍처 원리에 대한 자세한 그래픽 설명

    • write pos 是当前记录的位置，一边写一边后移，写到最后一个文件末尾后就回到 0 号文件开头；
    • checkpoint 是当前要擦除的位置，也是往后推移并且循环的，擦除记录前要把记录更新到数据文件；
    • write pos 和 checkpoint 之间还空着的部分，可以用来记录新的操作。如果 write pos 追上checkpoint，表示写满，这时候不能再执行新的更新，得停下来先擦掉一些记录，把 checkpoint推进一下。

  • Redo Log相关配置参数

    • 每个InnoDB存储引擎至少有1个重做日志文件组（group），每个文件组至少有2个重做日志文件，默认为ib_logfile0和ib_logfile1。可以通过下面一组参数控制Redo Log存储：

      • show variables like '%innodb_log%';

    • Redo Buffer 持久化到 Redo Log 的策略，可通过 Innodb_flush_log_at_trx_commit 设置：

MySQL 아키텍처 원리에 대한 자세한 그래픽 설명

  • 0：每秒提交 Redo buffer ->OS cache -> flush cache to disk，可能丢失一秒内的事务数据。由后台Master线程每隔 1秒执行一次操作。
  • 1（默认值）：每次事务提交执行 Redo Buffer -> OS cache -> flush cache to disk，最安全，性能最差的方式。
  • 2：每次事务提交执行 Redo Buffer -> OS cache，然后由后台Master线程再每隔1秒执行OS cache -> flush cache to disk 的操作。
  • 一般建议选择取值2，因为 MySQL 挂了数据没有损失，整个服务器挂了才会损失1秒的事务提交数
    据。

Binlog日志

  • Binlog 记录模式

    • Redo Log 是属于InnoDB引擎所特有的日志，而MySQL Server也有自己的日志，即 Binary log（二进制日志），简称Binlog。Binlog是记录所有数据库表结构变更以及表数据修改的二进制日志，不会记录SELECT和SHOW这类操作。Binlog日志是以事件形式记录，还包含语句所执行的消耗时间。开启Binlog日志有以下两个最重要的使用场景。
      • 主从复制：在主库中开启Binlog功能，这样主库就可以把Binlog传递给从库，从库拿到Binlog后实现数据恢复达到主从数据一致性。
      • 数据恢复：通过mysqlbinlog工具来恢复数据。
    • Binlog文件名默认为“主机名_binlog-序列号”格式，例如oak_binlog-000001，也可以在配置文件中指定名称。文件记录模式有STATEMENT、ROW和MIXED三种，具体含义如下。
      • ROW（row-based replication, RBR）：日志中会记录每一行数据被修改的情况，然后在slave端对相同的数据进行修改。
        • 优点：能清楚记录每一个行数据的修改细节，能完全实现主从数据同步和数据的恢复。
        • 缺点：批量操作，会产生大量的日志，尤其是alter table会让日志暴涨。
      • STATMENT（statement-based replication, SBR）：每一条被修改数据的SQL都会记录到master的Binlog中，slave在复制的时候SQL进程会解析成和原来master端执行过的相同的SQL再次执行。简称SQL语句复制。
        • 优点：日志量小，减少磁盘IO，提升存储和恢复速度
        • 缺点：在某些情况下会导致主从数据不一致，比如last_insert_id()、now()等函数。
      • MIXED（mixed-based replication, MBR）：以上两种模式的混合使用，一般会使用STATEMENT模式保存binlog，对于STATEMENT模式无法复制的操作使用ROW模式保存binlog，MySQL会根据执行的SQL语句选择写入模式。

  • Binlog 文件结构

    • MySQL的binlog文件中记录的是对数据库的各种修改操作，用来表示修改操作的数据结构是Log event。不同的修改操作对应的不同的log event。比较常用的log event有：Query event、Row event、Xid event等。binlog文件的内容就是各种Log event的集合。

    • Binlog文件中Log event结构如下图所示：

MySQL 아키텍처 원리에 대한 자세한 그래픽 설명

  • Binlog写入机制

    • 根据记录模式和操作触发event事件生成log event（事件触发执行机制）
    • 将事务执行过程中产生log event写入缓冲区，每个事务线程都有一个缓冲区Log Event保存在一个binlog_cache_mngr数据结构中，在该结构中有两个缓冲区，一个是stmt_cache，用于存放不支持事务的信息；另一个是trx_cache，用于存放支持事务的信息。
    • 事务在提交阶段会将产生的log event写入到外部binlog文件中。
    • 不同事务以串行方式将log event写入binlog文件中，所以一个事务包含的log event信息在binlog文件中是连续的，中间不会插入其他事务的log event。

  • Binlog文件操作

    • 根据记录模式和操作触发event事件生成log event（事件触发执行机制）
    • 将事务执行过程中产生log event写入缓冲区，每个事务线程都有一个缓冲区
    • Log Event保存在一个binlog_cache_mngr数据结构中，在该结构中有两个缓冲区，一个是stmt_cache，用于存放不支持事务的信息；另一个是trx_cache，用于存放支持事务的信息。
    • 事务在提交阶段会将产生的log event写入到外部binlog文件中。
    • 不同事务以串行方式将log event写入binlog文件中，所以一个事务包含的log event信息在
      binlog文件中是连续的，中间不会插入其他事务的log event。

  • Binlog文件操作

    • Binlog状态查看

      • show variables like 'log_bin';

    • 开启Binlog功能

      • set global log_bin = mysqllogbin; 
ERROR 1238 (HY000): Variable 'log_bin' is a read only variable

      • 需要修改my.cnf或my.ini配置文件，在[mysqld]下面增加log_bin=mysql_bin_log，重启MySQL服务。

      • #log-bin=ON 
#log-bin-basename=mysqlbinlog 
binlog-format=ROW 
log-bin=mysqlbinlog

    • 使用show binlog events命令

      • show binary logs; //等价于show master logs; 

show master status; 

show binlog events; 

show binlog events in 'mysqlbinlog.000001';

    • 使用 mysqlbinlog 命令

      • mysqlbinlog "文件名" 

mysqlbinlog "文件名" > "test.sql"

    • 使用 binlog 恢复数据

      • //按指定时间恢复 
mysqlbinlog --start-datetime="2020-04-25 18:00:00" --stop- datetime="2020-04-26 00:00:00" mysqlbinlog.000002 | mysql -uroot -p1234 

//按事件位置号恢复 
mysqlbinlog --start-position=154 --stop-position=957 mysqlbinlog.000002 | mysql -uroot -p1234

      • mysqldump：定期全部备份数据库数据。mysqlbinlog可以做增量备份和恢复操作。

    • 删除Binlog文件

      • purge binary logs to 'mysqlbinlog.000001'; //删除指定文件 

purge binary logs before '2020-04-28 00:00:00'; //删除指定时间之前的文件 

reset master; //清除所有文件

      • 可以通过设置expire_logs_days参数来启动自动清理功能。默认值为0表示没启用。设置为1表示超出1天binlog文件会自动删除掉。

  • Redo Log和 Binlog区别

    • Redo Log是属于InnoDB引擎功能，Binlog是属于MySQL Server自带功能，并且是以二进制文件记录。
    • Redo Log属于物理日志，记录该数据页更新状态内容，Binlog是逻辑日志，记录更新过程。
    • Redo Log日志是循环写，日志空间大小是固定，Binlog是追加写入，写完一个写下一个，不会覆盖使用。
    • Redo Log作为服务器异常宕机后事务数据自动恢复使用，Binlog可以作为主从复制和数据恢复使用。Binlog没有自动crash-safe能力。

推荐学习：mysql视频教程

위 내용은 MySQL 아키텍처 원리에 대한 자세한 그래픽 설명의 상세 내용입니다. 자세한 내용은 PHP 중국어 웹사이트의 기타 관련 기사를 참조하세요!

성명：
이 기사는 csdn.net에서 복제됩니다. 침해가 있는 경우 admin@php.cn으로 문의하시기 바랍니다. 삭제
이전 기사：mysql에 현재 로그인된 사용자를 쿼리하는 방법다음 기사：mysql에 현재 로그인된 사용자를 쿼리하는 방법

관련 기사

더보기