mysql 이론과 기본 지식에 대한 자세한 소개-MySQL 튜토리얼-php.cn

이 글에서는 mysql에 대한 이론과 기본 지식을 자세하게 소개하겠습니다. 도움이 필요한 친구들이 모두 참고할 수 있기를 바랍니다.

mysql 이론과 기본 지식에 대한 자세한 소개

mysql 아키텍처

1. 네트워크 연결 계층

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

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

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

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

SQL 인터페이스(SQL 인터페이스): 클라이언트가 보낸 다양한 SQL 명령을 수락하고 반환하는 데 사용됩니다. 사용자의 쿼리 결과입니다. DML, DDL, 저장 프로시저, 뷰, 트리거 등

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

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

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

Select-》Projection-》Join strategy

1) 모든 데이터를 쿼리한 다음 필터링하는 대신 먼저 where 문을 기반으로 선택을 선택하고

2) 쿼리 선택 모든 필드를 꺼내는 대신 uid 및 이름을 기반으로 속성 투영을 수행합니다

3) 이전 선택과 투영을 연결하여 최종적으로 쿼리 결과를 생성합니다

캐시(Cache&Buffer): 캐싱 메커니즘은 일련의 작은 캐시로 구성됩니다. 예를 들어 테이블 캐시, 레코드 캐시, 권한 캐시, 엔진 캐시 등이 있습니다. 쿼리 캐시에 적중 쿼리 결과가 있는 경우 쿼리 문은 쿼리 캐시에서 데이터를 직접 가져올 수 있습니다.

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

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

4. 시스템 파일 계층(파일 시스템)

이 계층은 파일 시스템에 데이터베이스 데이터와 로그를 저장하고 파일의 물리적 저장 계층입니다. 주로 로그 파일, 데이터 파일, 구성 파일, pid 파일, 소켓 파일 등이 포함됩니다.

로그 파일

오류 로그(오류 로그)

기본적으로 활성화되어 '%log_error%'와 같은 변수를 표시합니다.

일반 쿼리 로그(일반 쿼리 로그)

일반 쿼리 문을 기록하고, '%general'과 같은 변수를 표시합니다. %';

바이너리 로그(바이너리 로그)

는 MySQL 데이터베이스에서 수행된 변경 작업을 기록하며, 해당 명령문의 발생 시간과 실행 시간을 기록하지만, select, show 등을 수행하는 SQL은 기록하지 않습니다. 데이터베이스를 수정하지 마십시오. 주로 데이터베이스 복구 및 마스터-슬레이브 복제에 사용됩니다.

'%log_bin%'과 같은 변수 표시; //'%binlog%'와 같은 변수 표시

바이너리 로그 표시

)

실행 시간이 초과된 모든 쿼리 SQL을 기록합니다. 기본값은 10초입니다.

'%slow_query%'와 같은 변수 표시; //활성화 여부

'%long_query_time%'과 같은 변수 표시; //기간

구성 파일

은 my.xml과 같은 모든 MySQL 구성 정보 파일을 저장하는 데 사용됩니다. 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 소켓을 사용하여 MySQL에 연결할 수 있습니다.

InnoDB 및 MyISAM

트랜잭션 및 외래 키

InnoDB는 트랜잭션 및 외래 키를 지원하고 보안과 무결성을 갖추고 있으며 다수의 삽입 또는 업데이트 작업에 적합합니다.

MyISAM은 트랜잭션 및 외래 키를 지원하지 않습니다. , 이는 다수의 선택 쿼리 작업에 적합한 고속 저장 및 검색을 제공합니다

잠금 메커니즘

InnoDB는 지정된 레코드를 잠그는 행 수준 잠금을 지원합니다. 잠금은 인덱스를 기반으로 구현됩니다.

MyISAM은 테이블 수준 잠금을 지원하여 전체 테이블을 잠급니다.

인덱스 구조

InnoDB는 클러스터형 인덱스(clustered index)를 사용하여 인덱스와 레코드를 함께 캐싱합니다.

MyISAM은 비클러스터형 인덱스(non-clustered index)를 사용하며, 인덱스와 레코드가 분리되어 있습니다.

동시 처리 기능

MyISAM은 테이블 잠금을 사용하므로 쓰기 작업의 동시성 비율이 낮고 읽기 간 차단이 없으며 읽기 및 쓰기 차단이 가능합니다.

InnoDB 읽기 및 쓰기 차단은 격리 수준과 관련될 수 있으며 다중 버전 동시성 제어(MVCC)를 사용하여 높은 동시성을 지원할 수 있습니다.

저장 파일

InnoDB 테이블은 두 개의 파일, 하나는 .frm에 해당합니다. 테이블 구조 파일과 하나의 .ibd 데이터 파일. InnoDB 테이블은 최대 64TB를 지원합니다.

MyISAM 테이블은 .frm 테이블 구조 파일, MYD 테이블 데이터 파일 및 .MYI 인덱스 파일의 세 가지 파일에 해당합니다. MySQL 5.0부터 기본 제한은 256TB입니다.

Redo Log와 Binlog의 차이점

Redo Log는 InnoDB 엔진의 기능인 반면, Binlog는 MySQL Server에 내장된 기능으로 바이너리 파일로 기록됩니다.

Redo Log는 데이터 페이지의 업데이트 상태 내용을 기록하는 물리적 로그입니다. Binlog는 업데이트 프로세스를 기록하는 논리적 로그입니다.

Redo 로그는 순환적으로 작성되며, 로그 공간 크기는 고정되어 있고, Binlog는 추가 방식으로 작성되며, 하나를 쓴 후 다음을 작성하며 덮어쓰지 않습니다.

Redo Log는 서버가 비정상적으로 다운된 후 트랜잭션 데이터를 자동으로 복원하는 데 사용할 수 있습니다. Binlog는 마스터-슬레이브 복제 및 데이터 복구에 사용할 수 있습니다. Binlog에는 자동 충돌 방지 기능이 없습니다.

애플리케이션에서는 슬레이브 데이터베이스에 여러 인덱스를 추가하여 쿼리를 최적화할 수 있으며, 이러한 인덱스는 쓰기 효율성을 높이기 위해 기본 데이터베이스에서 생략될 수 있습니다.

읽기와 쓰기 분리 방식

1 쓴 후 즉시 읽기

데이터베이스에 쓴 후 일정 시간 내에 읽기 작업이 메인 데이터베이스로 이동한 후 읽기 작업이 슬레이브에 액세스합니다. 데이터 베이스.

2 보조 쿼리

먼저 슬레이브 데이터베이스로 이동하여 데이터를 읽습니다. 찾을 수 없는 경우 기본 데이터베이스로 이동하여 데이터를 읽습니다. 이 작업은 읽기 압력을 기본 라이브러리로 쉽게 되돌려줍니다. 악의적인 공격을 방지하려면 보안 및 낮은 결합에 도움이 되는 데이터베이스 액세스 API 작업을 캡슐화하는 것이 좋습니다.

3 업무에 따른 특수 처리

업무 특성 및 중요도에 따라 조정합니다. 예를 들어 중요한 업무 데이터를 실시간으로 읽고 쓸 수 있습니다. 높은 실시간 성능을 요구하지 않는 2차 업무의 경우 읽기와 쓰기를 분리하여 쿼리할 때 데이터베이스에서 쿼리를 수행할 수 있습니다.