MySQL 데이터베이스에 대한 일반적인 면접 질문을 요약하고 정리합니다.-MySQL 튜토리얼-php.cn

이 기사는 mysql에 대한 관련 지식을 제공합니다. 주로 데이터베이스 제조업체의 인터뷰 질문을 요약하여 모두에게 도움이 되기를 바랍니다.

MySQL 데이터베이스에 대한 일반적인 면접 질문을 요약하고 정리합니다.

1. 데이터베이스의 일반적인 패러다임:

첫 번째 정규형(1NF): 더 이상 나눌 수 없는 테이블의 열을 나타내며 각 데이터베이스의 테이블 열은 분할할 수 없는 기본 데이터 항목이며 동일한 열에 여러 값이 있을 수 없습니다.

두 번째 정규형(2NF): 1NF를 기준으로 다음 두 부분도 포함됩니다. 첫째, 테이블에는 기본 키가 있어야 합니다. 둘째, 테이블의 기본 키가 아닌 열은 기본 키에 완전히 종속되어야 하며 기본 키의 일부에만 의존할 수 없습니다.

세 번째 정규 형식(3NF): 기본 2NF에서는 기본 키에 대한 기본 키가 아닌 열의 전이적 종속성이 제거되고, 기본 키가 아닌 열은 기본 키에 직접 종속되어야 합니다.

BC 정규형(BCNF): 3NF를 기반으로 코드 부분에 대한 주요 속성의 전이적 종속성이 제거됩니다

2 SQL 문의 실행 프로세스:

2.1. 드라이버 및 데이터베이스 연결 풀 :

(1) 클라이언트는 데이터베이스와 통신하기 전에 데이터베이스 드라이버를 통해 MySQL과 연결을 설정한 후 SQL 문을 보냅니다.

(2) 잦은 연결 생성 및 파괴로 인한 시스템 성능 저하를 줄이기 위해 데이터베이스 연결 풀은 연결이 필요한 경우 연결 풀에서 직접 가져오고 사용 후 연결 스레드를 일정 수로 유지합니다. 연결 풀. 일반적인 데이터베이스 연결 풀에는 Druid, C3P0, DBCP

2.2 및 MySQL 아키텍처의 서버 계층 실행 프로세스가 포함됩니다.

(1) 커넥터: 주로 클라이언트와의 연결 설정, 권한 획득, 유지 및 관리를 담당합니다. connections

(2) 쿼리 캐시: 캐시에서 먼저 쿼리하고, 캐시에서 쿼리를 찾을 수 없으면 직접 반환합니다.

MySQL 캐시는 기본적으로 꺼져있습니다. 즉, 캐시 사용을 권장하지 않으며, MySQL8.0 버전에서는 전체 쿼리 캐시 기능이 삭제되었습니다. 이는 주로 사용 시나리오의 제한으로 인해 발생합니다.

먼저 캐시의 데이터 저장 형식에 대해 이야기하겠습니다. 키(sql 문) - 값(데이터 값)이므로 SQL 문(키)이 약간 다른 경우

테이블의 데이터는 정적이 아니므로 대부분 자주 변경되며 데이터베이스의 데이터가 변경되면 이 테이블과 관련된 해당 캐시된 데이터를 제거해야 합니다.

(3) 파서/분석기: 분석기의 역할은 주로 실행될 SQL 문에 대해 어휘 분석 및 구문 분석을 수행하고 최종적으로 추상 구문 트리를 얻은 후 전처리기를 사용하여 추상 구문 트리에 대한 의미 수정을 수행하는 것입니다. .추상 구문 트리에 테이블이 있는지 확인합니다. 테이블이 있으면 선택 프로젝션 열 필드가 있는지 확인합니다.

(4) 옵티마이저(Optimizer): 주로 SQL의 어휘 분석 및 구문 분석을 거쳐 얻은 구문 트리를 이용하여 데이터 사전의 내용과 통계 정보를 거쳐 일련의 연산을 거쳐 최종적으로 선택을 포함한 실행 계획을 도출한다.

인덱스 쿼리 사용 여부를 분석할 때 동적 데이터 샘플링 통계 분석을 통해 얻습니다. 통계적으로 분석되는 한 분석 오류가 발생할 수 있으므로 이를 사용하지 않고 SQL을 실행할 경우 index 를 사용하는 경우 이 요소도 고려해야 합니다.

(5) Executor: 일련의 실행 계획에 따라 스토리지 엔진에서 제공하는 API 인터페이스를 호출하여 작업 데이터를 호출하고 SQL 실행을 완료합니다.

2.3. Innodb 스토리지 엔진 실행 프로세스:

(1) 먼저 MySQL 실행기는 스토리지 엔진의 API를 호출하여 실행 계획에 따라 데이터를 쿼리합니다.

(2) 스토리지 엔진은 먼저 버퍼 풀에서 데이터를 쿼리합니다. 쿼리가 발견되면 캐시 풀에 넣습니다

(3) 데이터를 버퍼 풀에 로드하는 동안 이 데이터의 원본 기록은 실행 취소 로그 파일에 저장됩니다

(4) innodb는 버퍼 풀에서 업데이트 작업을 수행합니다

(5) 업데이트된 데이터는 리두 로그 버퍼에 기록됩니다

(6) 커밋 트랜잭션은 제출과 동시에 다음 세 가지 작업을 수행합니다

(7) (첫 번째) 리두 로그 버퍼의 데이터를 리두 로그 파일에 플래시합니다

(8) (두 번째) 작업 기록을 bin 로그 파일에 씁니다

(9) (세 번째) bin을 넣습니다 bin 로그의 위치는 redo 로그에 기록되고, redo 로그 끝에 커밋 표시가 추가됩니다.

(10) 백그라운드 스레드를 사용하여 업데이트된 데이터를 플러시합니다. 특정 기회에 MySQL 데이터베이스에 버퍼 풀을 추가하여 메모리와 데이터베이스의 데이터가 통합되도록 합니다

3. InnoDB와 MyISAM의 차이점은 무엇입니까?

스토리지 엔진은 기반이 되는 물리적 데이터에 대해 실제 연산을 수행하는 구성요소로, 서버 서비스 계층의 데이터 운용을 위한 다양한 API를 제공합니다. 일반적으로 사용되는 스토리지 엔진에는 InnoDB, MyISAM 및 Memory가 있습니다. 여기서는 주로 InnoDB와 MyISAM의 차이점을 소개합니다.

(1) 트랜잭션: MyISAM은 트랜잭션을 지원하지 않고 InnoDB는 트랜잭션을 지원합니다.

(2) 잠금 수준: MyISAM은 테이블 수준 잠금만 지원하고 InnoDB는 행 수준 잠금을 지원하며 테이블 수준 잠금은 기본적으로 행 수준 잠금이 사용되지만 행 잠금은 인덱스를 통해 데이터를 쿼리할 때만 사용되며, 그렇지 않으면 테이블 잠금이 사용됩니다. 행 수준 잠금은 잠금을 획득하고 해제하는 각 작업에서 테이블 잠금보다 더 많은 리소스를 소비합니다. 행 잠금을 사용하면 교착 상태가 발생할 수 있지만 테이블 수준 잠금을 사용하면 교착 상태가 없습니다

(3) 기본 키와 외래 키: MyISAM은 인덱스와 기본 키가 없는 테이블의 존재를 허용하며 외래 키를 지원하지 않습니다. InnoDB의 기본 키는 비워둘 수 없으며 기본 키 자동 증가를 지원합니다. 기본 키 또는 비어 있지 않은 고유 인덱스가 설정되지 않은 경우 6바이트 기본 키가 자동으로 생성되고 외래 키 무결성 제약 조건을 지원합니다

(4) 인덱스 구조: MyISAM과 InnoDB는 모두 B+ 트리 인덱스를 사용합니다. MyISAM의 기본 키 인덱스와 보조 인덱스의 데이터 필드는 행 데이터 레코드가 저장되는 주소입니다. 그러나 InnoDB 기본 키 인덱스의 Data 필드에는 행 데이터 레코드의 주소가 아닌 해당 행의 모든 데이터 내용이 저장되는 반면, 보조 인덱스의 Data 필드에는 기본 인덱스의 값이 저장됩니다.

InnoDB의 보조 인덱스는 기본 키 인덱스의 값을 저장하므로 보조 인덱스를 사용하려면 인덱스를 두 번 검색해야 합니다. 먼저 보조 인덱스를 검색하여 기본 키를 얻은 다음 기본 키를 사용하여 기본 인덱스. 이것이 너무 긴 필드를 기본 키로 사용하는 것을 권장하지 않는 이유입니다. 보조 인덱스에는 기본 키 열이 포함되어 있으므로 기본 키가 너무 긴 필드를 사용하면 다른 보조 인덱스가 더 커지게 되므로 기본 키는 가능한 한 작게 만듭니다.

(5) 전체 텍스트 인덱스: MyISAM은 버전 5.6 이전에는 전체 텍스트 인덱스를 지원하지 않았습니다.

(6) 특정 번호. of columns in the table:

① MyISAM: 테이블의 총 행 수를 저장합니다. 테이블에서 select count()를 사용하면 전체 테이블 스캔이 필요 없이 값이 직접 제거됩니다.
② InnoDB: 테이블의 총 행 수가 저장되지 않습니다. 테이블에서 select count()를 사용하면 테이블 전체를 순회해야 하므로 많은 비용이 소모됩니다.

(7) 저장 구조:

① MyISAM은 테이블 정의를 저장하는 .frm 파일, 데이터를 저장하는 .MYD 파일, 인덱스를 저장하는 .MYI 파일의 세 가지 파일을 디스크에 저장합니다.
② InnoDB: 데이터와 인덱스를 테이블 공간에 저장합니다. 모든 테이블은 동일한 데이터 파일에 저장됩니다. InnoDB 테이블의 크기는 일반적으로 2GB인 운영 체제 파일 크기에 의해서만 제한됩니다.

(8) 저장공간 :

① MyISAM : 압축이 가능하며 저장공간이 더 작습니다. 세 가지 다른 저장 형식을 지원합니다: 정적 테이블(기본값이지만 데이터 끝에 공백이 없어야 하므로 제거됩니다.), 동적 테이블 및 압축 테이블.
② InnoDB: 더 많은 메모리와 스토리지가 필요하며 데이터 및 인덱스 캐싱을 위해 메인 메모리에 자체 전용 버퍼 풀을 구축합니다.

(9) 적용 가능한 시나리오:

① 롤백 및 충돌 복구 기능을 제공하는 ACID 트랜잭션 기능이 필요하고 행 잠금 수준 동시성 제어가 필요한 경우 InnoDB가 좋은 선택입니다.
② 데이터 테이블이 주로 레코드를 쿼리하는 데 사용되며 쓰기 작업보다 읽기 작업이 훨씬 많고 데이터베이스 트랜잭션 지원이 필요하지 않은 경우 MyISAM 엔진은 더 높은 처리 효율성을 제공할 수 있습니다.

참고: MyISAM 스토리지 엔진은 mysql8.0에서 폐기되었습니다. 버전

4. ACID 및 트랜잭션 구현 원리는 무엇입니까?

데이터베이스 트랜잭션은 동시성 제어의 기본 단위로, 모두 실행되거나 전혀 실행되지 않는 논리적 작업 집합을 나타냅니다.

4.1 거래 ACID:

(1) 원자성: 트랜잭션은 분할할 수 없는 작업 단위입니다. 트랜잭션의 작업은 성공하거나 실패합니다.

(2) 격리: 트랜잭션에 의해 운영되는 데이터가 제출되기 전에 다른 트랜잭션에 표시되는 정도입니다.

(3) 지속성: 트랜잭션이 커밋되면 데이터베이스의 데이터 변경 사항이 영구적입니다.

(4) 일관성: 거래는 데이터 무결성과 비즈니스 일관성을 파괴할 수 없습니다. 예를 들어, 돈을 이체할 때 거래가 성공하든 실패하든 양측 간의 총 금액은 변하지 않습니다.

4.2. ACID 구현 원칙:

4.2.1. 원자성: MySQL의 롤백 로그 실행 취소 로그를 통해 달성됩니다. 트랜잭션이 실행되면 InnoDB는 해당 실행 취소 로그를 생성합니다. 실패하거나 롤백이 호출되어 트랜잭션이 롤백되는 경우 실행 취소 로그의 정보를 사용하여 데이터를 수정 전의 방식으로 롤백할 수 있습니다. 来

4.2.2. 격리:

(1) 트랜잭션 격리 수준:

동시 환경에서 데이터 읽기의 무결성과 일관성을 보장하기 위해 데이터베이스는 4가지 트랜잭션 격리 수준을 제공합니다. 격리 수준이 높을수록 , 데이터가 더욱 완전하고 일관되게 보장될 수 있지만, 높은 동시성 성능에 미치는 영향이 커지고 실행 효율성이 낮아집니다. (4개의 격리 수준은 위에서 아래로 증가합니다)

Read Uncommitted: 트랜잭션이 실행 중에 다른 트랜잭션의 커밋되지 않은 데이터를 읽을 수 있도록 허용합니다.
Read Committed: 실행 중에 트랜잭션을 읽을 수 있습니다.
반복 읽기(기본 수준): 동일한 트랜잭션 내에서 언제든지 쿼리 결과가 일관됩니다.
읽기 직렬화: 모든 트랜잭션은 하나씩 실행되며, 각 읽기에는 테이블 수준 공유 잠금이 필요합니다. , 읽기와 쓰기가 서로 차단됩니다.

(2) 트랜잭션 동시성 문제:

트랜잭션의 격리를 고려하지 않으면 트랜잭션 동시성 환경에서 문제가 발생할 수 있습니다.

업데이트 손실: 두 개 이상의 트랜잭션 작업이 동일한 경우 그런 다음 선택한 값을 기반으로 데이터가 업데이트됩니다. 각 트랜잭션은 다른 트랜잭션의 존재를 인식하지 못하기 때문에 업데이트 손실 문제가 발생합니다. 마지막 업데이트가 다른 트랜잭션의 업데이트를 덮어씁니다.
더티 읽기(Dirty read): 트랜잭션 A가 데이터에 액세스하고 데이터를 수정했음을 의미합니다(트랜잭션이 커밋되지 않음). 나중에 트랜잭션 A가 롤백을 취소하고 수정된 데이터를 원래 위치로 복원합니다. 원래 값. B가 읽은 데이터는 데이터베이스의 데이터와 일치하지 않습니다. 즉, B가 읽은 데이터는 더티 데이터입니다.
비반복 읽기: 한 트랜잭션 내에서 동일한 데이터를 여러 번 읽지만, 이 기간 동안 다른 트랜잭션이 데이터를 수정하고 커밋했기 때문에 전후에 읽은 데이터가 일치하지 않습니다.
팬텀 읽기: 트랜잭션에서, 동일한 데이터(일반적으로 범위 쿼리)를 두 번 읽지만 다른 트랜잭션에서 데이터를 추가하거나 삭제하기 때문에 두 번의 결과가 일치하지 않습니다.

다른 트랜잭션 격리 수준은 동시 환경에서 다른 동시성 문제를 갖습니다.

(3) 트랜잭션 격리 구현 원칙:

Innodb 트랜잭션 격리 수준은 MVVC 및 잠금 메커니즘에 의해 결정됩니다. 구현:

① MVCC(Multi-Version Concurrency Control, 다중 버전 동시성 제어)는 MySQL의 InnoDB 스토리지 엔진이 트랜잭션 격리 수준을 구현하는 특정 방법으로 읽기 커밋 및 반복 가능 읽기의 두 가지 격리 수준을 구현하는 데 사용됩니다. 커밋되지 않은 읽기 격리 수준은 MVCC를 사용하지 않고 항상 최신 데이터 행을 읽습니다. 읽기 직렬화 격리 수준에서는 모든 읽기 행을 잠가야 하는데 이는 단순히 MVCC를 사용하여 달성할 수 없습니다.

MVCC는 레코드의 각 행 뒤에 두 개의 숨겨진 열을 저장하여 구현됩니다. 하나는 행의 트랜잭션 ID를 저장하고 다른 하나는 행의 롤백 세그먼트 포인터를 저장합니다. 새로운 거래가 시작될 때마다 새로운 거래 ID가 자동으로 증가됩니다. 트랜잭션이 시작되면 현재 트랜잭션의 영향을 받는 행의 트랜잭션 ID 필드에 트랜잭션 ID가 배치되고 롤백 세그먼트의 포인터에는 행 레코드의 모든 버전 데이터가 링크된 형태로 구성됩니다. undo 로그 롤백 로그의 목록, 즉 해당 값이 실제로 실행 취소 로그 행의 히스토리 연결 목록을 가리킨다고 합니다.

데이터베이스에 동시에 접근할 때 트랜잭션 내 데이터에 대해 MVCC 다중 버전 관리를 수행하여 쓰기 작업이 읽기 작업을 차단하는 것을 방지하고 스냅샷 읽기 방식의 팬텀 읽기 문제는 버전 비교를 통해 해결할 수 있지만 팬텀의 경우 현재 읽기의 읽기, MVCC는 이를 해결할 수 없으며 임시 키 잠금으로 해결해야 합니다.

② 잠금 메커니즘:

MySQL 잠금 메커니즘의 기본 작동 원리는 다음과 같습니다. 트랜잭션이 데이터베이스를 수정하기 전에 해당 잠금을 획득해야 합니다. 잠금을 획득한 트랜잭션만 트랜잭션 작업 중에 데이터를 수정할 수 있습니다. , 데이터의 이 부분은 잠금입니다. 다른 트랜잭션이 데이터를 수정해야 하는 경우 현재 트랜잭션이 커밋되거나 롤백되어 잠금을 해제할 때까지 기다려야 합니다.

배타적 잠금은 더티 읽기를 해결합니다.

공유 잠금은 반복 불가능한 읽기를 해결합니다.

프로 키 잠금은 팬텀 읽기를 해결합니다.

4.2.3 지속성:

지속성은 ，SQL 실행 시 리두 로그에 의존합니다. 실행된 SQL 문은 Redo 로그 파일에 저장되지만 효율성을 높이기 위해 Redo 로그에 데이터를 쓰기 전에 먼저 메모리의 Redo 로그 버퍼 캐시에 기록됩니다. 쓰기 프로세스는 다음과 같습니다: 데이터베이스에 데이터를 쓸 때 실행 프로세스는 먼저 리두 로그 버퍼에 기록됩니다. 리두 로그 버퍼의 수정된 데이터는 정기적으로 디스크의 리두 로그 파일로 새로 고쳐집니다. 디스크 플러싱(즉, 리두 로그 버퍼가 디스크의 리두 로그 파일에 로그를 기록함)이라고 합니다.

리두 로그 버퍼를 사용하면 데이터 읽기 및 쓰기 효율성이 크게 향상될 수 있지만 새로운 문제도 발생합니다. MySQL이 다운되고 리두 로그 버퍼에서 수정된 데이터가 메모리의 디스크에 플러시되지 않은 경우 , 데이터 손실로 이어질 수 있으며 거래의 내구성을 보장할 수 없습니다. 트랜잭션의 내구성을 보장하기 위해 트랜잭션이 커밋되면 fsync 인터페이스가 다시 실행 로그를 플러시하도록 호출됩니다. 새로 고침 빈도는 innodb_flush_log_at_trx_commit 변수에 의해 제어됩니다.

0: 디스크를 플러시하지 않음을 의미합니다.

1: 모든 트랜잭션 트랜잭션이 제출되면 버퍼 풀의 데이터가 디스크로 플러시됩니다.

2: 트랜잭션이 제출되면 버퍼 풀의 데이터가 디스크에 해당하는 OS 캐시에 기록됩니다. 파일을 직접 입력하는 대신 . OS 캐시의 데이터가 디스크 파일에 기록되기까지 1초 정도 걸릴 수 있습니다.

4.2.4. 일관성:

일관성은 트랜잭션이 데이터 무결성과 비즈니스 일관성을 파괴할 수 없다는 사실을 나타냅니다.

데이터 무결성: 엔터티 무결성, 열 무결성(예: 필드 유형, 크기, 길이) 요구 사항을 충족해야 함), 외래 키 제약 조건 등

비즈니스 일관성: 예를 들어 은행 송금의 경우 거래 성공 여부에 관계없이 양 당사자 간의 총액은 변경되지 않습니다.

5. 데이터베이스의 잠금 메커니즘?

데이터베이스의 여러 트랜잭션이 동일한 데이터에 동시에 액세스할 때 동시 작업을 제어하지 않으면 잘못된 데이터를 읽고 저장하여 데이터베이스의 일관성이 파괴될 수 있습니다. MySQL 잠금 메커니즘의 기본 작동 원리는 트랜잭션이 데이터베이스를 수정하기 전에 먼저 해당 잠금을 획득해야 한다는 것입니다. 잠금을 획득한 트랜잭션만 트랜잭션 작업 중에 데이터를 수정할 수 있습니다. 다른 트랜잭션에서 이를 수정해야 하는 경우 데이터는 현재 트랜잭션이 커밋되거나 롤백되어 잠금이 해제될 때까지 기다려야 합니다.

다양한 분류 방법에 따라 잠금 유형은 다음과 같은 유형으로 나눌 수 있습니다.

잠금의 세분성으로 구분: 테이블 수준 잠금, 행 수준 잠금, 페이지 수준 잠금
잠금 유형으로 구분: 공유(잠금 S 잠금), 배타적 잠금(X 잠금)
잠금 사용 전략으로 구분: 낙관적 잠금, 비관적 잠금

5.1, 테이블 수준 잠금, 행 수준 잠금, 페이지 수준 잠금:

테이블 수준 잠금: 세분성이 가장 큰 잠금 수준, 잠금 충돌 가능성이 가장 높고 동시성은 가장 낮지만 오버헤드가 적고 잠금이 빠릅니다.
행 수준 잠금: 발생률이 가장 작은 수준 잠금 충돌 가능성이 가장 작고 동시성이 가장 높지만 비용이 많이 들고 잠금 추가 속도가 느리며 교착 상태가 발생합니다.
페이지 수준 잠금: 잠금 세분성은 테이블 수준 잠금과 행 수준 잠금 간에 제한됩니다. 잠금은 절충안이며 동시성은 평균입니다. 오버헤드와 잠금 시간도 테이블 잠금과 행 잠금 간에 제한되며 교착 상태가 발생합니다.

다양한 스토리지 엔진이 다양한 잠금 메커니즘을 지원합니다.

InnoDB 스토리지 엔진은 행 수준 잠금과 테이블 수준 잠금을 지원합니다. 행 수준 잠금은 기본적으로 사용되지만 행 수준 잠금은 인덱스를 통해 데이터를 쿼리할 때만 사용되며, 그렇지 않은 경우에는 테이블 수준 잠금이 사용됩니다.
MyISAM 및 MEMORY 스토리지 엔진은 테이블 수준 잠금을 사용합니다.
BDB 스토리지 엔진은 페이지 잠금을 사용하지만 테이블 수준 잠금도 지원합니다.

5.2, InnoDB 행 잠금:

InnoDB 행 잠금 잠금 유형:

배타적 잠금(X 잠금, 쓰기) 잠금): 트랜잭션이 배타적 잠금을 획득한 후 잠금 범위 내의 데이터 행에 대한 쓰기 작업을 수행할 수 있습니다. 잠금 기간 동안 다른 트랜잭션은 더 이상 데이터 행의 해당 부분에 대한 잠금(공유 잠금, 배타적 잠금)을 획득할 수 없습니다. ), 배타적 잠금을 획득한 트랜잭션만 데이터를 업데이트합니다.

업데이트, 삭제 및 삽입 작업의 경우 InnoDB는 일반 SELECT 문과 관련된 데이터 행에 자동으로 배타적 잠금을 추가하지만 InnoDB는 잠금을 추가하지 않습니다.

5.3. InnoDB 테이블 잠금 및 의도 잠금:

InnoDB 엔진은 행 잠금과 테이블 잠금의 공존을 허용하여 다중 세분성 잠금 메커니즘을 구현하지만 테이블 잠금과 행 잠금은 서로 충돌합니다. 그들은 서로 다른 잠금 범위를 가지고 있습니다. 테이블 잠금을 추가하려면 먼저 테이블의 모든 레코드를 탐색하여 배타적 잠금이 있는지 확인해야 합니다. 이 순회 확인 방법은 분명히 비효율적인 방법입니다. MySQL은 테이블 잠금과 행 잠금 간의 충돌을 감지하기 위해 의도 잠금을 도입합니다.

의도 잠금은 테이블 수준 잠금이기도 하며 읽기 의도 잠금(IS 잠금)과 쓰기 의도 잠금(IX 잠금)으로 구분됩니다. 트랜잭션이 레코드에 행 잠금을 추가하려는 경우 먼저 해당 의도 잠금을 테이블에 추가합니다. 나중에 트랜잭션이 테이블을 잠그려면 먼저 의도한 잠금이 존재하는지 여부만 확인하면 됩니다. 존재하는 경우 테이블 잠금을 빠르게 설정할 수 없습니다. 그렇지 않으면 효율성을 높이기 위해 기다려야 합니다.

5.4. InnoDB 행 잠금 및 키 잠금 구현:

InnoDB 행 잠금은 인덱스의 인덱스 항목을 잠그는 방식으로 구현됩니다. 행 잠금은 인덱스를 통해 데이터를 검색할 때만 사용할 수 있으며, 그렇지 않으면 테이블 잠금이 사용됩니다.

InnoDB에서는 팬텀리딩 현상을 해결하기 위해 넥스트키 잠금(next-key)을 도입했습니다. 지수에 따라 왼쪽이 열린 구간, 오른쪽이 닫힌 구간으로 구분됩니다. 범위 쿼리 수행 시 인덱스에 도달하여 데이터를 검색할 수 있으면 해당 레코드가 있는 간격과 다음 간격이 잠깁니다. 실제로 Next-Key = Record Locks + Gap Locks

Gap Locks: 정확한 쿼리 대신 범위 쿼리를 사용하여 데이터를 검색하고 공유 또는 배타적 잠금을 요청할 때 InnoDB는 기존 데이터 레코드의 인덱스 항목을 잠급니다. 범위 조건을 충족하는 경우, 키 값이 조건 범위 내에 있지만 존재하지 않는 레코드를 간격(GAP)이라고 합니다.

레코드 잠금: 고유 인덱스와 레코드 존재에 대한 정확한 쿼리를 사용할 때 레코드 잠금을 사용하세요

5.5, 잠금 메커니즘을 사용하여 동시성 문제를 해결합니다.

X 잠금은 더티 읽기를 해결합니다

S 잠금 반복 불가능한 읽기 문제 해결

Pro-key 잠금으로 팬텀 읽기 문제 해결

InnoDB 스토리지 엔진의 잠금 메커니즘과 MyISAM 스토리지 엔진의 잠금 메커니즘에 대한 자세한 내용은 MySQL 데이터베이스: 잠금 메커니즘_Zhang을 참조하세요. 데이터베이스의 Weipeng Blog-CSDN Blog_Lock 메커니즘

6. MySQL 인덱스의 구현 원리:

인덱스는 기본적으로 탐색해야 하는 행 수를 줄여 쿼리 성능을 높이고 전체 쿼리를 방지하는 데이터 구조입니다. 책의 목차와 마찬가지로 데이터베이스의 테이블 스캔을 통해 콘텐츠를 더 빠르게 찾을 수 있습니다. (테이블은 최대 16개까지 가능)

6.1.인덱스의 장점과 단점:

(1) 인덱스의 장점:

쿼리로 검색해야 하는 행 수 감소, 속도 쿼리를 실행하고 전체 테이블 스캔을 피하세요. 이는 인덱스를 생성하는 주된 이유이기도 합니다.
인덱스의 데이터 구조가 B+ 트리인 경우 그룹화 및 정렬을 사용하면 쿼리에서 그룹화 및 정렬하는 시간을 크게 줄일 수 있습니다.
고유 인덱스를 생성하면 데이터베이스 테이블에 있는 각 데이터 행의 고유성을 보장할 수 있습니다.

(2) 인덱스의 단점:

테이블의 데이터가 추가, 삭제, 수정될 때 인덱스도 업데이트해야 하며, 데이터 양이 증가함에 따라 유지 관리 시간도 늘어납니다.
인덱스는 물리적 공간을 차지해야 합니다. 클러스터형 인덱스를 만들려면 필요한 공간이 더 커집니다.

6.2. 인덱스 사용 시나리오:

(1) 인덱스를 생성할 열:

WHERE 절에 자주 나타나는 열에 인덱스를 생성하여 조건 판단 속도를 높입니다.
범위로 접근하는 열이나 그룹화 또는 정렬 기준으로 사용되는 열은 인덱스가 정렬되어 있으므로 인덱스를 사용하면 정렬 쿼리 시간을 단축할 수 있습니다.
연결된 열에 자주 사용되는 이 열은 주로 외래 키이므로 연결 속도를 높일 수 있습니다.
기본 키 열로서 열의 고유성을 강화하고 테이블의 데이터 배열 구조를 구성합니다.
(2) 색인을 생성하면 안 되는 열은 무엇인가요?

차별성이 크지 않은 열입니다. 이러한 열은 쿼리 결과에서 성별과 같은 값이 거의 없기 때문에 결과 집합의 데이터 행은 테이블의 데이터 행 중 많은 부분을 차지합니다. 테이블에서 검색했습니다. 인덱스를 늘려도 검색 속도는 크게 향상되지 않습니다.

쿼리에서 거의 사용되지 않는 열은 색인화하면 안 됩니다. 이러한 열은 거의 사용되지 않으므로 인덱스를 추가하면 실제로 시스템 유지 관리 속도가 줄어들고 공간 요구 사항이 늘어납니다.

인덱스를 추가하면 검색 성능 향상보다 수정 비용 증가가 훨씬 클 경우 인덱스를 생성하면 안 됩니다. 인덱스를 추가하면 검색 성능은 향상되지만 수정 성능은 저하됩니다. 인덱스를 줄이면 수정 성능이 향상되고 검색 성능이 저하됩니다.

텍스트, 이미지, 비트 데이터 형식으로 정의된 열은 색인화하면 안 됩니다. 이러한 열의 데이터 볼륨은 상당히 크거나 값이 거의 없습니다.

6.3. 인덱스 분류:

(1) 일반 인덱스, 고유 인덱스, 기본 키 인덱스, 전체 텍스트 인덱스, 결합 인덱스.

일반 인덱스: 아무런 제한이 없는 가장 기본적인 인덱스

고유 인덱스: 단, 인덱스 열의 값은 고유해야 하며, null 값이 허용되며, NULL 값이 여러 개 있을 수 있습니다. 복합 인덱스의 경우 컬럼 값의 조합이 고유해야 합니다.

기본 키 인덱스: null 값을 허용하지 않는 특수 고유 인덱스입니다.

전체 텍스트 인덱스: 전체 텍스트 인덱스는 MyISAM 테이블에만 사용할 수 있으며 CHAR, VARCHAR 또는 TEXT 유형만 지원합니다. 퍼지 일치 작업과 같이 덜 효율적인 대체에 사용되며 일회성 전체에 사용할 수 있습니다. 다중 필드 결합 전체 텍스트 인덱스를 통한 퍼지 일치.

결합 인덱스: 주로 MySQL의 효율성을 높이기 위해 복합 인덱스를 생성할 때 제한 조건으로 가장 일반적으로 사용되는 열을 내림차순으로 맨 왼쪽에 배치해야 합니다.

(2) 클러스터형 인덱스와 비클러스터형 인덱스:

데이터가 저장되는 물리적 순서와 인덱스 값의 순서에 따라 분류하면 인덱스는 클러스터형 인덱스와 비클러스터형 인덱스 두 가지로 나눌 수 있습니다. :

클러스터형 인덱스: 테이블의 데이터 저장 순서는 인덱스 값의 순서와 일치합니다. 기본 테이블은 클러스터형 인덱스 열의 데이터를 업데이트할 때 최대 1개만 가질 수 있습니다. 변경 사항은 비용이 많이 들기 때문에 자주 업데이트되는 열에 대해 클러스터형 인덱스를 설정하는 것은 적절하지 않습니다. 인덱스 값의 순서와 일치하지 않습니다. 기본 테이블에는 여러 클러스터가 있을 수 있습니다.

6.4. 인덱스 데이터 구조:

일반적인 인덱스 데이터 구조에는 B+Tree, Hash 인덱스가 포함됩니다.

(1) 해시 인덱스: MySQL의 메모리 스토리지 엔진만이 메모리 테이블의 기본 인덱스 유형인 해시 인덱스를 지원합니다. 해시 인덱스는 데이터를 해시값 형태로 정리하므로 쿼리 효율이 매우 높고 한 번에 찾을 수 있다.

해시 인덱스의 단점:

해시 인덱스는 동일한 값 쿼리만 만족할 수 있지만 범위 쿼리 및 정렬은 만족할 수 없습니다. 데이터가 해시 알고리즘을 통과한 후 크기 관계가 변경될 수 있기 때문입니다.

복합 인덱스 생성 시, 복합 인덱스의 일부 컬럼만 쿼리에 사용할 수는 없습니다. 해시 인덱스는 여러 컬럼 데이터를 결합한 후 해시 값을 계산하기 때문에 개별 컬럼 데이터에 대한 해시 값을 계산하는 것은 의미가 없습니다.

해시 충돌이 발생하면 해시 인덱스는 테이블 데이터 스캔을 피할 수 없습니다. 단순히 Hash 값을 비교하는 것만으로는 충분하지 않기 때문에 실제 값을 비교하여 요구 사항을 충족하는지 판단해야 합니다.

(2) B+Tree 인덱스: B+Tree는 mysql에서 가장 많이 사용되는 인덱스 데이터 구조로 Innodb와 Myisam 스토리지 엔진 모드의 인덱스 유형이다. B+Tree 인덱스는 검색 시 루트 노드부터 리프 노드까지 여러 번의 IO 작업이 필요합니다. 쿼리 속도는 Hash 인덱스만큼 좋지는 않지만 정렬과 같은 작업에는 더 적합합니다.

B+트리 인덱스의 장점:

페이지 내 노드는 콘텐츠를 저장하지 않으며 IO당 더 많은 행을 읽을 수 있어 디스크 I/O 읽기 횟수가 크게 줄어듭니다.

순차 액세스 포인터가 있는 B+트리: B+Tree의 모든 인덱스 데이터는 리프 노드에 저장되며, 각 리프 노드에는 인접한 리프 노드에 대한 포인터가 추가됩니다. 이는 간격 쿼리의 효율성을 높이기 위해 수행됩니다.

6.5. B+Tree를 인덱스로 사용하는 이유:

인덱스 자체도 매우 크고 메모리에 모두 저장하는 것이 불가능하므로

인덱스는 인덱스 형태로 디스크에 저장되는 경우가 많습니다. 파일. 이 경우 인덱스 검색 과정에서 디스크 I/O 소비가 발생하게 되며, 메모리 액세스에 비해 디스크 I/O 액세스 소비가 몇 배 더 높기 때문에 가장 중요한 것은 인덱스 검색의 품질을 평가하는 것입니다. 인덱스로서의 데이터 구조 지표는 검색 프로세스 중 디스크 I/O 작업 수의 점근적 복잡성을 나타냅니다. 즉,

index의 데이터 구조는 검색 과정에서 디스크 I/O 액세스 횟수를 최소화해야 합니다. (1) 지역성 원리 및 프로그램 사전 읽기:

디스크 자체는 메인 메모리에 비해 접근 속도가 훨씬 느리고 기계적 이동 비용도 더해지므로 효율성을 높이기 위해서는 디스크 I/O가 반드시 필요합니다. 최소화. 이 목표를 달성하기 위해 디스크는 엄격하게 요구에 따라 읽는 것이 아니라 매번 미리 읽는 경우가 많습니다. 1바이트만 필요하더라도 디스크는 이 위치에서 시작하여 일정 길이의 데이터를 순차적으로 읽어옵니다. 메모리. 이에 대한 이론적 근거는 컴퓨터 과학의 유명한 지역성 원칙입니다. 즉, 데이터 조각이 사용될 때 일반적으로 근처의 데이터가 즉시 사용됩니다. 프로그램 실행 중에 필요한 데이터는 일반적으로 집중되어 있습니다.

디스크 순차 읽기는 매우 효율적이므로(탐색 시간이 필요하지 않고 회전 시간이 매우 적음) 미리 읽기는 지역성이 있는 프로그램의 I/O 효율성을 향상시킬 수 있습니다. 미리 읽기의 길이는 일반적으로 페이지의 정수배입니다. 프로그램이 읽을 데이터가 메인 메모리에 없으면 페이지 폴트 예외가 발생합니다. 이때 시스템은 읽기 신호를 디스크에 보내고 디스크는 데이터의 시작 위치를 찾습니다. 하나 이상의 페이지를 거꾸로 읽습니다. 메모리에 로드한 다음 비정상적으로 반환되고 프로그램이 계속 실행됩니다.

(2) B+Tree 지수 성능 분석:

위에서 언급했듯이 일반적으로 디스크 I/O 수는 인덱스 구조의 품질을 평가하는 데 사용됩니다. B-트리 분석부터 시작해 보겠습니다. B-트리 검색에는 최대 h개의 노드에 대한 액세스가 필요합니다. 동시에 데이터베이스는 디스크 미리 읽기 원칙을 교묘하게 사용하여 노드 크기를 한 페이지와 동일하게 설정합니다. 즉, 새 노드가 생성될 때마다 페이지 공간이 직접 생성되어 노드가 페이지에 물리적으로 저장되고 컴퓨터 스토리지 할당이 페이지 정렬되어 각 노드가 완전히 로드될 수 있습니다. I/O가 하나만 있으면 됩니다. B-트리에서 검색하려면 최대 h-1 I/O(루트 노드가 메모리에 상주함)가 필요하며 시간 복잡도는 O(h)=O(logdN)입니다. 일반적인 실제 적용에서 외향차 d는 매우 큰 수(보통 100 이상)이므로 h는 매우 작습니다. 정리하자면, B-트리를 인덱스 구조로 사용하는 것은 매우 효율적입니다.

레드-블랙 트리와 같은 구조의 경우 시간 복잡도도 O(h)이지만 h는 분명히 훨씬 더 깊고 논리적으로 가까운 노드는 물리적으로 멀리 떨어져 있을 수 있으므로 지역성을 활용할 수 없습니다. 분명히 B-tree보다 훨씬 나쁩니다.

또한 인덱스 데이터 구조로는 B+Tree가 더 적합한 이유는 내부 노드의 외부 차수 d와 관련이 있습니다. 위의 분석을 통해 d가 클수록 인덱스 성능이 좋아지는 것을 알 수 있으며, 데이터 도메인이 제거되므로 아웃 차수 d의 상한은 노드의 키와 데이터 크기에 따라 달라집니다. B+트리의 노드에서 더 큰 외부 수준을 가질 수 있으므로 디스크 IO 수가 더 적습니다.

(3) B+트리 인덱스와 B-트리 인덱스 비교?

B-Tree와 B+Tree의 구조에 따르면 파일 시스템이나 데이터베이스 시스템에서 B+ 트리가 B-tree보다 더 많은 이점을 가지고 있음을 알 수 있습니다.

(1) B+ 트리. 데이터베이스 스캔이 유익합니다. B-트리는 디스크 IO 성능을 향상시키지만 요소 순회 효율성이 낮은 문제를 해결하지 못하는 반면, B+ 트리는 모든 키워드 정보를 스캔하는 문제를 해결하기 위해 리프 노드만 순회하면 되므로 범위 쿼리 및 sorting 다른 작업의 경우 B+ 트리의 성능이 더 높습니다.

(2) B+ 트리의 디스크 IO 비용이 더 낮습니다. B+ 트리 내부 노드의 데이터 필드는 데이터를 저장하지 않으므로 내부 노드가 B 트리의 데이터 필드보다 작습니다. 동일한 내부 노드의 키워드가 모두 동일한 디스크 블록에 저장된다면, 디스크 블록은 더 많은 키워드를 담을 수 있다. 검색해야 할 키워드가 한 번에 메모리에 많이 읽혀지고, I/O 읽기 및 쓰기 횟수가 상대적으로 줄어듭니다.

(3) B+ 트리의 쿼리 효율성이 더 안정적입니다. B+ 트리의 내부 노드는 리프 노드에 있는 키워드의 인덱스일 뿐이므로 데이터를 저장하지 않습니다. 따라서 모든 키워드 검색은 루트 노드에서 리프 노드까지의 경로를 취해야 합니다. 모든 키워드 쿼리의 경로 길이는 동일하므로 각 데이터에 대한 쿼리 효율성이 동일합니다.

(4) MySQL의 InnoDB 및 MyISAM 스토리지 엔진에 B+Tree 인덱스를 구현합니까?

MyISAM과 InnoDB는 모두 B+ 트리 인덱스를 사용합니다. MyISAM의 기본 키 인덱스와 보조 인덱스의 데이터 필드는 모두 행의 주소를 저장하지만 InnoDB의 기본 키 인덱스는 행의 주소가 아닌 행의 모든 데이터를 저장합니다. , 보조 인덱스의 데이터 필드에는 기본 인덱스의 값이 저장됩니다.

인덱스 길이 제한:

Innodb의 결합 인덱스의 경우 각 열의 길이가 767바이트를 초과하면 Innodb의 단일 열 인덱스의 경우 767바이트를 초과하는 열에 접두사 인덱스가 사용됩니다. 767을 초과하면 접두사 인덱스를 사용합니다(처음 255자 사용).

MyISAM의 결합 인덱스의 경우 생성된 인덱스 길이의 합은 1000바이트를 초과할 수 없습니다. 그렇지 않으면 오류가 보고되고 MyISAM의 단일 열에 대한 생성이 실패합니다. 인덱스, 최대 길이는 1000을 초과할 수 없습니다. 그렇지 않으면 경고가 발생하지만 생성은 성공적이며 최종 생성은 접두사 인덱스입니다(처음 333자 사용)

7. 구조 최적화:

(1) MySQL SQL 최적화 및 인덱스 최적화: https://blog.csdn.net/a745233700/article/details/84455241

(2) MySQL 테이블 구조 최적화: https://blog.csdn .net/a745233700/article/details /84405087

8. 데이터베이스 매개변수 최적화:

MySQL은 IO 집약적 애플리케이션이며 주요 책임은 데이터 관리 및 저장입니다. 그리고 우리는 메모리에서 데이터베이스를 읽는 시간이 마이크로초 수준인 반면, 일반 하드 디스크에서 IO를 읽는 시간은 밀리초 수준이라는 것을 알고 있습니다. 둘 사이의 차이는 3배입니다. 따라서 데이터베이스를 최적화하기 위해 가장 먼저 최적화해야 할 단계는 IO이며, 디스크 IO를 메모리 IO로 최대한 변환합니다. 따라서 MySQL 데이터베이스의 매개변수를 최적화할 때 우리는 주로 디스크 IO를 줄이는 매개변수를 최적화합니다. 예를 들어 query_cache_size를 사용하여 쿼리 캐시의 크기를 조정하고 innodb_buffer_pool_size를 사용하여 버퍼 크기를 조정합니다.

9. explain의 실행 계획:

실행 계획은 쿼리 분석기를 거친 후 SQL 문에서 얻은 추상 구문 트리와 관련 테이블의 통계 정보를 기반으로 한 쿼리 계획입니다. 생성된 쿼리 최적화 프로그램에 의해. 동적 데이터 샘플링과 통계적 분석의 결과이므로 분석 오류, 즉 실행 계획이 최적이 아닐 수 있습니다. Explain 키워드를 사용하면 MySQL이 SQL 쿼리 문을 실행하는 방법을 알고, select 문의 성능 병목 현상을 분석하고, 쿼리를 개선할 수 있습니다.

중요한 것은 id, type, key입니다. key_len, androws, extra:

(1) id: id 열은 SQL 실행 순서의 식별자로 이해될 수 있습니다.

다른 id 값: id 값이 클수록 우선 순위가 높아지고 더 빨리 실행됩니다.

동일한 id 값: 위에서 아래로 순차적으로 실행됩니다.

id 열은 null입니다. 결과 집합이 아니라 쿼리에 필요합니다.

(2) select_type : 일반 질의, 결합 질의, 하위 질의 등 복잡한 질의를 구별하기 위해 주로 사용되는 질의 유형

(3) 테이블: explain 행이 어느 테이블에 접근하는지 나타냄

(4) 유형: 액세스 유형, 즉 MySQL이 테이블에서 행을 찾는 방법을 결정합니다. 최고에서 최악까지: system > const > eq_ref > index_merge > index_subquery > index > index_merge를 제외하고 다른 유형은 하나의 인덱스만 사용할 수 있습니다. 일반적으로 유형은 참조 수준에 있어야 하며 범위 검색은 범위 수준에 도달해야 합니다.

system: 테이블에 단 하나의 데이터 일치(시스템 테이블과 동일)가 있으며 이는 const 유형의 특수한 경우로 간주될 수 있습니다.

const: 인덱스를 통해 한 번 발견되며 다음의 사용을 나타냅니다. 기본 키 인덱스 또는 고유 인덱스

eq_ref: 기본 키 또는 고유 인덱스의 필드는 연결에 사용되며 일치하는 데이터의 한 행만 반환됩니다.

ref: 충족하는 여러 행을 반환할 수 있는 일반 인덱스 스캔입니다. 쿼리 조건.

fulltext: 전체 텍스트 인덱스 검색, 전체 텍스트 인덱스가 우선순위가 높습니다. 전체 텍스트 인덱스와 일반 인덱스가 동시에 존재하는 경우 mysql은 여부에 관계없이 전체 텍스트 인덱스를 사용하는 데 우선 순위를 둡니다. 비용.

ref_or_null: null 값 비교가 추가된다는 점을 제외하면 ref 메서드와 유사합니다.

index_merge: 쿼리가 두 개 이상의 인덱스를 사용함을 나타냅니다. 인덱스 병합의 최적화 방법이며, 최종적으로 공통 및/또는 조건은 서로 다른 인덱스를 사용합니다.

unique_subquery: 하위 쿼리가 중복 값 없이 고유한 값을 반환하는 in-form 하위 쿼리에 사용됩니다.

index_subquery: 보조 인덱스 또는 상수 목록을 사용하는 in-form 하위 쿼리에 사용되며 하위 쿼리는 반복된 값을 반환할 수 있습니다. 하위 쿼리를 중복 제거하려면 인덱스를 사용하세요.

range: >, <, between, in, like 등과 같은 연산자를 사용하는 쿼리에 일반적으로 사용되는 인덱스 범위 스캔입니다.

index: 전체 테이블 스캔, 인덱스 트리를 처음부터 끝까지 스캔합니다.

all: 전체 테이블을 탐색하여 일치하는 행을 찾습니다. (Index와 ALL 모두 전체 테이블을 읽지만 인덱스는 인덱스에서 읽습니다. ALL은 하드 디스크에서 읽기)

NULL: MySQL은 최적화 프로세스 중에 명령문을 분해하고 실행 중에 테이블이나 인덱스에 액세스할 필요조차 없습니다

(5) available_keys: 쿼리 중에 사용할 수 있는 인덱스

(6) key : 이 테이블에 대한 액세스를 최적화하기 위해 실제로 사용되는 인덱스

(7) key_len: 쿼리를 최적화하기 위해 실제로 사용되는 인덱스 길이, 즉 인덱스에 사용되는 바이트 수입니다. 이 값을 통해 다중 컬럼 인덱스에서 실제로 인덱스의 어떤 필드가 사용되는지 계산할 수 있습니다.

(8)ref: 키와 함께 사용되는 필드 또는 상수를 표시합니다.

(9)rows: 테이블 통계 및 인덱스 선택을 기반으로 정확한 수치가 아닌 쿼리를 위해 읽어야 하는 행 수를 대략적으로 추정합니다. 값.

(10)추가: 기타 추가 정보

색인 사용: 포함 색인 사용

색인 조건 사용: 쿼리된 열은 색인에 포함되지 않으며 where 필터 조건은 색인을 사용합니다

임시 사용: 임시 테이블 사용하기 중간 결과 저장은 group by 및 order by 작업에서 자주 사용됩니다. 일반적으로 group by 열에 인덱스가 없기 때문일 수도 있지만, 열이 둘 다 있기 때문일 수도 있습니다. 일반적으로 이는 파일 정렬을 사용하여 쿼리를 최적화해야 함을 나타냅니다. MySQL에는 쿼리 결과를 정렬하는 두 가지 방법이 있는데, 하나는 인덱스를 사용하는 것이고, 다른 하나는 파일 정렬(외부 정렬)입니다. 퀵 정렬 기반, 성능 저하). 데이터 양이 많을 경우 CPU 집약적 프로세스가 되므로 적절한 인덱스를 설정하면 정렬 성능을 최적화할 수 있습니다

실행 계획에 대한 자세한 내용은 다음 기사를 참조하세요: https: //blog.csdn.net/a745233700/article/details/84335453

10. MySQL 마스터-슬레이브 복제:

10.1. MySQL 마스터-슬레이브 복제의 원리:

슬레이브는 마스터에서 binlog 바이너리 로그 파일을 가져온 다음 로그 파일을 해당 SQL 문을 슬레이브 서버에서 다시 실행하여 데이터 일관성을 보장합니다. 마스터-슬레이브 복제 과정은 비동기식이므로 슬레이브와 마스터 간 데이터 지연이 발생할 수 있으며, 데이터의 최종 일관성만 보장할 수 있습니다. 마스터와 슬레이브 사이의 전체 복제 프로세스는 주로 세 가지 스레드에 의해 완료됩니다.

(1) 슬레이브 SQL 스레드: 릴레이 로그 릴레이 로그를 읽고 로그에 포함된 업데이트를 실행하기 위해 생성되며 슬레이브 측에 위치

(2) 슬레이브 I/O 스레드: 마스터 서버 Binlog Dump 스레드에서 보낸 내용을 읽고 슬레이브 측에 있는 슬레이브 서버의 릴레이 로그에 저장합니다.

(3) Binlog 덤프 스레드(라고도 함) IO 스레드): bin-log 바이너리 로그의 내용을 마스터 측에 있는 슬레이브 서버로 보냅니다.

참고: 마스터 서버에 두 개의 슬레이브 서버가 있는 경우 두 개의 슬레이브 서버가 있습니다. 마스터 서버. Binlog 덤프 스레드, 각 슬레이브 서버에는 두 개의 스레드가 있습니다.

10.2. 마스터-슬레이브 복제 프로세스:

(1) 마스터 서버가 SQL 문을 실행한 후 binlog 바이너리 파일 ;

(2) 슬레이브 측의 IO 스레드는 마스터 측에 연결하고 지정된 bin 로그 파일의 지정된 pos 노드 위치에서(또는 파일의 맨 처음부터) 다음 로그 내용을 복사하도록 요청합니다. 로그).

(3) 마스터 측은 슬레이브 측으로부터 IO 스레드 요청을 받은 후 복제 프로세스를 담당하는 IO 스레드에 알리고, 요청 정보에 따라 지정된 binlog 로그의 지정된 pos 노드 위치 이후의 로그 정보를 읽습니다. 슬레이브 측 IO 스레드의 후, 슬레이브 측 IO 스레드로 반환됩니다. 반환 정보에는 binlog 로그에 포함된 정보 외에도 마스터 측에서 반환된 정보의 binlog 파일 이름과 binlog 로그의 POS 노드 위치도 포함됩니다.

(4) 마스터 측 IO에서 반환된 정보를 받은 후 슬레이브 측 IO 스레드는 수신된 binlog 로그 내용을 슬레이브 측 릴레이 로그 파일 끝에 차례로 쓰고, binlog를 읽습니다. 이름과 위치 노드 위치는 master-info 파일에 기록되므로(파일은 슬레이브 측에 저장됨) 다음 동기화 중에 데이터 동기화를 시작할 위치를 마스터에 알릴 수 있습니다.

(5) 슬레이브 측의 SQL 스레드가 릴레이 로그를 감지 파일에 내용을 추가한 후 릴레이 로그 파일의 내용을 즉시 파싱한 후 마스터 측에서 실제로 실행된 SQL 문으로 복원하고, 그런 다음 마스터 측과 슬레이브 측 간의 데이터 일관성을 달성하기 위해 실행됩니다. ;

10.3. 마스터-슬레이브 복제의 이점:

(1) 읽기 및 쓰기 분리, 데이터베이스 성능 향상 슬레이브 서버를 동적으로 추가하고, 마스터 서버에서 쓰기 및 업데이트를 수행하고, 슬레이브 서버에서 업데이트를 수행합니다. 읽기 기능을 실행합니다.

(2) 데이터가 슬레이브 서버에 복사되었기 때문에 데이터 보안이 향상되며, 슬레이브 서버가 복제 프로세스를 종료할 수 있으므로 마스터 서버의 해당 데이터를 파기하지 않고 슬레이브 서버에 백업할 수 있습니다.

(3) 마스터 서버에서 실시간 데이터를 생성하고, 슬레이브 서버에서 이 데이터를 분석하여 마스터 서버의 성능을 향상시킵니다.

10.4 MySQL에서 지원하는 복제 유형과 그 장점과 단점:

binlog 로그 파일에는 두 가지 형식이 있습니다. 하나는 명령문 기반(명령문 기반 복제)이고 다른 하나는 행 기반(기반)입니다. 행 복사). 기본 형식은 명령문 기반입니다. 형식을 변경하려면 서비스 시작 시 -binlog-format 옵션을 사용하십시오.

mysqld_safe –user=msyql –binlog-format=format&

(1) 명령문 기반 복제: 마스터 서버에서 실행되는 SQL 문과 슬레이브 서버에서 동일한 명령문이 실행되는 것입니다. 효율성이 상대적으로 높습니다. 정확한 복사가 불가능하다는 것이 확인되면 행 기반 복사가 자동으로 선택됩니다.

장점:

① SQL문을 기록하므로 저장공간을 적게 차지합니다. binlog 로그에는 데이터베이스 작업을 설명하는 이벤트가 포함되어 있지만 이러한 이벤트에는 삽입, 업데이트, 생성, 삭제 및 기타 작업과 같이 데이터베이스를 변경하는 작업만 포함됩니다. 반대로 select, desc 등 유사한 작업은 기록되지 않습니다.

② binlog 로그 파일은 데이터베이스를 변경하는 모든 명령문을 기록하므로 이 파일을 데이터베이스 감사의 기반으로 사용할 수 있습니다.

단점:

① 안전하지 않습니다. 데이터를 변경하는 모든 내용이 기록되는 것은 아닙니다. 비결정적 동작은 기록되지 않습니다. 예를 들어, 삭제 또는 업데이트 문의 경우 제한이 사용되었지만 주문 기준이 없으면 이는 비결정적 명령문이므로 기록되지 않습니다.

② 업데이트, 인덱스 조건 없이 insert...select 문을 사용하려면 더 많은 데이터를 잠가야 하므로 데이터베이스 성능이 저하됩니다.

(2) 행 기반: 슬레이브 서버에서 명령을 실행하는 대신 변경된 내용을 복사합니다. mysql5.0부터 지원됩니다.

장점:

① 모든 변경 사항이 복사됩니다.

② 업데이트, 삽입...select 및 기타 명령문에 대해 잠기는 행 수가 적습니다.

단점:

① binlog 로그 파일을 통해 볼 수 없는 것 일단 명령문이 실행되면, 슬레이브 서버에서 어떤 명령문이 수신되었는지 알 수 있는 방법은 어떤 데이터가 변경되었는지만 알 수 있습니다.

② 데이터를 기록하기 때문에 Binlog 로그 파일이 차지하는 저장 공간이 Statement 기반보다 큽니다.

③ 데이터 양이 많은 작업에는 시간이 더 오래 걸립니다.

(3) 혼합형 복제 : 기본적으로 명령문 기반 복제를 채택하며, 명령문 기반 복제가 정확하지 않다고 판단되면 행 기반 복제를 채택합니다.

마스터-슬레이브 복제에 대한 자세한 내용은 다음 문서를 참조하세요: https://blog.csdn.net/a745233700/article/details/85256818

11 읽기 및 쓰기 분리:

11.1. 원리:

읽기와 쓰기의 분리는 데이터베이스의 쓰기 작업이 쿼리의 효율성에 영향을 미치는 문제를 해결하며 읽기가 쓰기보다 훨씬 큰 시나리오에 적합합니다. 읽기-쓰기 분리를 실현하는 기본은 마스터-슬레이브 복제를 사용하여 자체 데이터 변경 사항을 슬레이브 데이터베이스 클러스터에 동기화한 다음 마스터 데이터베이스가 쓰기 작업을 처리하는 것입니다. 읽기 작업도 수행), 슬레이브 데이터베이스가 읽기 작업 처리를 담당하므로 쓰기 작업을 수행할 수 없습니다. 그리고 압박 상황에 따라 여러 슬레이브 데이터베이스를 배포하여 읽기 작업 속도를 높이고 기본 데이터베이스에 대한 부담을 줄이며 시스템의 전반적인 성능을 향상시킬 수 있습니다.

11.2 읽기 및 쓰기 분리가 성능을 향상시키는 이유:

(1) 부하를 공유하기 위해 물리적 서버를 추가합니다.

(2) 마스터와 슬레이브는 자신의 쓰기 및 읽기만 담당합니다. X 잠금 및 S 잠금 경합을 크게 완화합니다.

(3) 슬레이브 라이브러리는 쿼리 성능을 향상하고 시스템 오버헤드를 절약하도록 MyISAM 엔진을 구성할 수 있습니다.

(4) 마스터-슬레이브 복제의 또 다른 주요 기능은 중복성을 높이고 향상시키는 것입니다. 가용성. 데이터베이스 서버가 다운된 후 다른 슬레이브 데이터베이스를 조정하여 가능한 한 빨리 서비스를 복원할 수 있습니다.

11.3.Mysql 읽기 및 쓰기 구현 방법:

(1) 프로그램 코드 내부 구현 기반: 코드에서 선택 및 삽입을 기반으로 라우팅 분류가 수행됩니다. 장점은 프로그램이 코드로 구현되어 추가적인 하드웨어 비용이 들지 않기 때문에 성능이 더 좋다는 점이다. 단점은 개발자가 직접 구현해야 하고, 운영 및 유지보수 담당자가 시작할 방법이 없다는 점이다.

(2) 중간 프록시 계층 기반 구현: 프록시는 일반적으로 애플리케이션 서버와 데이터베이스 서버 사이에 있습니다. 프록시 데이터베이스 서버는 애플리케이션 서버로부터 요청을 수신하고 이를 판단에 따라 백엔드 데이터베이스로 전달합니다. 대표적인 프록시 레이어는 다음과 같습니다.

12. 세로 하위 테이블, 세로 하위 데이터베이스, 가로 하위 테이블, 가로 하위 데이터베이스

읽기와 쓰기의 분리로 데이터베이스 읽기 부담이 해결됩니다. 및 쓰기 작업은 데이터베이스의 저장 공간을 분산시키지 않습니다. 압력, 하위 데이터베이스 및 하위 테이블을 사용하면 데이터베이스의 저장 병목 현상을 해결하고 데이터베이스의 쿼리 효율성을 향상시킬 수 있습니다.

12.1. 수직 분할:

(1) 수직 테이블 파티셔닝: 테이블을 필드에 따라 여러 테이블로 나누고 각 테이블은 필드의 일부를 저장합니다. 일반적으로 자주 사용되는 필드는 한 테이블에 배치되고, 덜 자주 사용되는 필드는 다른 테이블에 배치됩니다.

장점:

(1) IO 경쟁을 피하고 테이블이 잠길 확률을 줄입니다. 큰 필드는 덜 효율적이기 때문에 첫째, 큰 필드는 더 많은 공간을 차지하고 단일 페이지에 저장되는 행 수가 줄어들어 IO 작업이 증가하고 둘째, 데이터 양이 크고 시간이 오래 걸립니다. 읽기.

(2)는 인기 있는 데이터의 쿼리 효율성을 더 향상시킬 수 있습니다.

(2) 수직 데이터베이스 분할: 서로 다른 비즈니스 모듈에 따라 테이블을 서로 다른 데이터베이스로 분할합니다. 이는 비즈니스 간 결합도가 매우 낮고 비즈니스 로직이 명확한 시스템에 적합합니다.

장점:

비즈니스 내 결합을 줄이고 다양한 비즈니스의 계층적 관리를 용이하게 합니다.

IO 및 데이터베이스 연결 수를 늘리고 단일 시스템 하드웨어 스토리지 리소스의 병목 현상 문제를 해결할 수 있습니다

( 3) 수직 분할 (서브 데이터베이스, 서브 테이블)의 단점:

기본 키가 중복되어 중복된 컬럼을 관리해야 함

트랜잭션 처리가 복잡해짐

여전히 데이터 양이 너무 많은 문제가 있음 단일 테이블

12.2 , 가로 분할:

(1) 수평 테이블 분할: 동일한 데이터베이스에서 특정 규칙에 따라 동일한 테이블의 데이터를 여러 테이블로 분할합니다.

장점:

단일 테이블의 과도한 데이터 볼륨 문제 해결

IO 경쟁을 피하고 테이블 잠금 가능성을 줄입니다.

(2) 수평 데이터베이스 분할: 동일한 테이블의 데이터를 표준에 따라 분할합니다. 특정 규칙을 다른 데이터베이스에 적용할 수 있으며, 다른 데이터베이스를 다른 서버에 배치할 수 있습니다.

장점:

단일 데이터베이스에서 대용량 데이터의 병목 현상 문제 해결

IO 충돌이 감소하고 잠금 경쟁이 감소하며 한 데이터베이스의 문제가 다른 데이터베이스에 영향을 미치지 않아 시스템의 안정성과 가용성이 향상됩니다.

(3) 수평 분할의 단점(테이블 및 데이터베이스):

샤딩된 트랜잭션의 일관성을 해결하기 어렵습니다.

교차 노드 JOIN 성능이 좋지 않고 논리가 복잡해집니다.

데이터 확장이 유지 관리가 어렵고 어렵습니다

12.3. 하위 데이터베이스 및 하위 테이블 문제에 대한 해결책:

(1) 트랜잭션 문제:

① 옵션 1: 분산 트랜잭션 사용:

장점: 데이터베이스로 관리되며 간단하고 효과적입니다.

단점: 특히 샤드가 많아질수록 성능 비용이 높습니다.

② 옵션 2: 프로그램과 데이터베이스가 공동으로 구현을 제어합니다. 원칙은 여러 데이터베이스에 걸친 분산 트랜잭션을 단일 데이터베이스에만 존재하는 여러 개의 작은 트랜잭션으로 분해하고 각 작은 트랜잭션을 전체적으로 제어하도록 애플리케이션에 맡기는 것입니다. 거래.

장점: 성능상의 장점

단점: 애플리케이션에서 트랜잭션의 유연한 제어가 필요합니다. Spring의 트랜잭션 관리를 사용한다면 변경 작업에 어려움을 겪게 될 것입니다.

(2) 교차 노드 조인 문제:

이 문제를 해결하는 일반적인 방법은 두 개의 쿼리로 구현하는 것입니다. 첫 번째 쿼리의 결과 집합에서 관련 데이터의 ID를 찾고 실행합니다. 이 ID를 기반으로 하는 두 번째 쿼리 관련 데이터를 가져오기 위해 두 번째 쿼리를 요청합니다.

(3) Cross-node count, order by, group by, paging, aggregation 기능 문제:

이러한 문제는 전체 데이터 수집을 기반으로 계산이 필요하기 때문입니다. 대부분의 에이전트는 병합 작업을 자동으로 처리하지 않습니다. 이 솔루션은 각 노드에서 결과를 얻은 다음 애플리케이션 측에서 병합합니다. 조인과 달리 각 노드의 쿼리를 병렬로 실행할 수 있기 때문에 하나의 큰 테이블보다 속도가 훨씬 빠릅니다. 그러나 결과 집합이 크면 애플리케이션 메모리 소비가 문제가 됩니다.

12.4. 데이터베이스를 테이블로 나눈 후 ID 키를 어떻게 처리합니까?

데이터베이스를 테이블로 분할한 후에는 각 테이블의 ID가 1부터 시작할 수 없으므로 글로벌 ID를 설정하는 방법은 주로 다음과 같습니다.

(1) UUID:

장점: ID는 로컬에서 생성되고 원격 호출이 필요하지 않으며 전역적으로 고유하고 반복되지 않습니다.

단점: 공간을 많이 차지하고 색인 생성에 적합하지 않습니다.

(2) 데이터베이스 자동 증가 ID: 데이터베이스를 테이블로 나눈 후 데이터베이스 자동 증가 ID를 사용하려면 서비스가 요청을 받을 때마다 기본 키 생성 전용 라이브러리가 필요합니다. 먼저 이 라이브러리에 데이터의 경우 데이터베이스에 의해 자동으로 증가되는 ID를 얻고 이 ID를 사용하여 하위 데이터베이스 및 하위 테이블에 데이터를 씁니다.

장점: 간단하고 구현이 쉽습니다.

단점: 높은 동시성에서는 병목 현상이 발생합니다.

(3) Redis 생성 ID:

장점: 데이터베이스에 의존하지 않으며 성능이 더 좋습니다.

단점: 새로운 구성 요소의 도입으로 시스템 복잡성이 증가합니다

(4) 트위터의 눈송이 알고리즘: 64비트 길이의 ID로, 이 중 1비트는 사용되지 않고 41비트는 밀리초로 사용되며 10비트는 작업 머신 ID로 사용되며 12비트는 일련 번호로 사용됩니다.

1bit: 첫 번째 비트의 기본값은 0입니다. 왜냐하면 이진수의 첫 번째 비트가 1이면 음수이지만 ID는 음수가 될 수 없기 때문입니다.

41bit: 타임스탬프를 나타내며 단위는 다음과 같습니다. 밀리초.

10bit: 작업 기계 ID를 기록합니다. 그 중 5비트는 컴퓨터실 ID를 나타내고 5비트는 기계 ID를 나타냅니다.

12bit: 동일한 밀리초 내에 생성된 서로 다른 ID를 기록하는 데 사용됩니다.

(5) Meituan의 Leaf 분산 ID 생성 시스템, Meituan-Dianping 분산 ID 생성 시스템:

13. 파티셔닝:

파티셔닝은 특정 규칙에 따라 테이블 데이터를 다른 위치에 저장하는 것입니다. , 테이블의 데이터 파일을 여러 개의 작은 블록으로 분할합니다. 데이터를 쿼리할 때는 데이터가 어느 영역에 저장되어 있는지만 알고 해당 영역에 직접 쿼리할 필요가 없습니다. 쿼리 성능을 향상시킵니다. 동시에, 테이블 데이터가 특히 크고 하나의 디스크에 들어갈 수 없는 경우 데이터를 다른 디스크에 할당하여 스토리지 병목 현상 문제를 해결할 수도 있습니다. 여러 디스크를 사용하면 디스크의 IO 효율성을 향상시킬 수 있습니다. 데이터베이스의 성능. 분할된 테이블을 사용하는 경우 파티션 필드는 기본 키 또는 고유 인덱스에 배치되어야 하며 테이블당 최대 파티션 수는 1024입니다. 일반적인 파티션 유형은 범위 파티션, 목록 파티션, 해시 파티션입니다. , 키 파티션,

(1) 범위 분할: 연속된 간격 범위에 따라 분할

(2) 목록 분할: 주어진 집합의 값에 따라 분할을 선택합니다.

(3) 해시 파티셔닝: 테이블에 삽입될 이러한 행의 열 값을 사용하여 계산된 사용자 정의 표현식의 반환 값을 기준으로 파티셔닝합니다. 이 함수에는 음수가 아닌 정수 값을 생성하는 MySQL의 유효한 표현식이 포함될 수 있습니다.

(4) 키 파티셔닝: HASH 파티셔닝과 유사하지만, 키 파티셔닝은 하나 이상의 열 계산만 지원하고 키 파티셔닝의 해시 함수는 MySQL 서버에서 제공한다는 점이 다릅니다.

(1) 테이블 파티셔닝의 장점:

① 확장성:

파티션을 여러 디스크로 나누면 단일 디스크 용량 병목 문제를 해결하고 더 많은 데이터를 저장할 수 있으며 단일 디스크 파티셔닝 문제를 해결할 수 있습니다. . 디스크 IO 병목 현상 문제.

② 데이터베이스 성능 향상:

데이터베이스 검색 중에 탐색해야 하는 데이터의 양을 줄입니다. 쿼리 시 해당 데이터에 해당하는 파티션만 쿼리하면 됩니다.

Innodb 단일 인덱스의 상호 배타적 접근 제한을 피하세요

sum() 및 count()와 같은 집계 함수의 경우 각 파티션에서 병렬로 처리할 수 있으므로 결국 얻은 결과만 계산하면 됩니다. 모든 파티션별

③ 편리한 데이터 운영 및 유지 관리 :

관리의 편리성 보존의 의미가 사라진 데이터의 경우, 해당 파티션을 삭제하여 빠르게 삭제할 수 있습니다. 예를 들어, 특정 시간의 기록 데이터를 삭제하려면 truncate를 직접 실행하거나 전체 파티션을 직접 삭제하는 것이 삭제하는 것보다 더 효율적입니다.

일부 시나리오에서는 단일 파티션 테이블의 백업 및 복구가 더 효율적입니다.

14. 일반적으로 자동 증가 ID 또는 UUID가 기본 키로 사용됩니까?

(1) 자동 증가 ID:

자동 증가 ID 사용의 장점:

필드 길이는 UUID보다 훨씬 작습니다.

데이터베이스는 자동으로 번호가 매겨져 순서대로 저장되므로 검색이 편리합니다.

기본 키 중복을 걱정할 필요가 없습니다

자체 증가 ID 사용의 단점:

자체 증가이기 때문에 일부 비즈니스 시나리오는 다른 비즈니스 볼륨에서 쉽게 찾을 수 있습니다.

데이터 마이그레이션이 발생하거나 테이블이 병합될 때 매우 번거로울 것입니다.

높은 동시성 시나리오에서는 자동 증가 잠금 경쟁으로 인해 데이터베이스 처리량이 감소합니다

(2) UUID: 범용 고유 식별 코드 , UUID는 현재 시간, 카운터, 하드웨어 식별자 등의 데이터를 계산하여 생성됩니다.

UUID 사용의 장점:

고유한 식별, 중복 문제를 고려할 필요가 없으며 데이터를 분할하고 병합할 때 전역 고유성을 달성할 수 있습니다.

데이터베이스 처리량을 향상시키기 위해 애플리케이션 계층에서 생성될 수 있습니다.

거래량 유출 걱정이 없습니다.

UUID 사용의 단점:

UUID가 무작위로 생성되기 때문에 무작위 IO가 발생하여 삽입 속도에 영향을 미치고 하드 디스크 사용량이 낮아집니다.

UUID는 많은 공간을 차지합니다. 인덱스를 많이 만들수록 효과는 더 커집니다.

UUID 간의 크기를 비교하는 것은 자체 증가하는 ID보다 훨씬 느리므로 쿼리 속도에 영향을 미칩니다.

일반적인 상황에서 MySQL은 자동 증가 ID 사용을 권장합니다. MySQL의 InnoDB 스토리지 엔진에서는 기본 키 인덱스가 클러스터형 인덱스이고 기본 키 인덱스의 B+ 트리 리프 노드에 기본 키가 저장되기 때문입니다. 기본 키 인덱스가 자동 증분 ID인 경우 순서대로만 정렬하면 됩니다. UUID인 경우 ID가 무작위로 생성되므로 데이터 이동 중에 많은 양의 데이터가 이동하게 됩니다. 삽입으로 인해 대량의 메모리 조각화가 발생하여 삽입 성능이 저하됩니다.

15. 뷰:

뷰는 하나 이상의 테이블(또는 뷰)에서 파생된 테이블이며 해당 콘텐츠는 쿼리로 정의됩니다. 뷰는 가상 테이블로, 뷰에 대한 정의만 데이터베이스에 저장되며 뷰에 해당하는 데이터는 저장되지 않습니다. 보기. 뷰는 기본 테이블 위에 구축된 테이블이라고 할 수 있으며, 그 구조와 내용은 기본 테이블에서 나오며 기본 테이블의 존재를 기반으로 존재합니다. 뷰는 하나의 기본 테이블 또는 여러 기본 테이블에 해당할 수 있습니다. 뷰는 논리적 의미에서 설정된 기본 테이블과 새로운 관계의 추상화입니다.

(1) 뷰의 장점:

작업을 단순화하고 자주 사용하는 데이터를 뷰로 정의합니다.

보안, 사용자는 보이는 데이터만 쿼리하고 수정할 수 있습니다.

논리적 독립성, 차폐 실제 구조의 영향을 제거합니다. table

(2) 뷰의 단점:

성능이 좋지 않습니다. 뷰가 복잡한 다중 테이블로 구성된 경우 데이터베이스는 뷰에 대한 쿼리를 기본 테이블에 대한 쿼리로 변환해야 합니다. 정의된 뷰의 간단한 쿼리라도 데이터베이스는 이를 복잡한 조합으로 변환하므로 일정 시간이 소요됩니다.

16. 저장 프로시저 절차:

SQL 문을 먼저 컴파일한 다음 실행해야 하며, 저장 프로시저는 특정 기능을 완료하기 위한 SQL 문 집합입니다. 사용자는 프로시저 이름과 주어진 인수를 사용하여 저장소를 호출할 수 있습니다.

프로그램을 사용하여 데이터베이스 운영을 위한 복잡한 논리를 구현할 수도 있는데 왜 저장 프로시저가 필요한가요? 주된 이유는 프로그램을 사용하여 API를 호출하는 효율성이 상대적으로 느리기 때문입니다. 애플리케이션이 실행을 위해 SQL 문을 MYSQL 엔진에 넘겨야 작업을 직접 수행하는 것이 좋습니다. 그것은 가장 능숙하고 완료할 수 있는 능력입니다.

저장 프로시저의 장점:

(1) 표준 구성 요소 프로그래밍: 저장 프로시저가 생성된 후 저장 프로시저의 SQL 문을 다시 작성할 필요 없이 프로그램에서 여러 번 호출할 수 있습니다. 그리고 DBA는 애플리케이션 소스 코드에 영향을 주지 않고 언제든지 저장 프로시저를 수정할 수 있습니다.

(2) 더 빠른 실행 속도: 작업에 대량의 Transaction-SQL 코드가 포함되어 있거나 여러 번 실행되는 경우 저장 프로시저는 일괄 처리보다 훨씬 빠르게 실행됩니다. 저장 프로시저는 미리 컴파일되어 있기 때문에 처음으로 저장 프로시저를 쿼리하면 최적화 프로그램이 이를 분석하고 최적화하여 최종적으로 시스템 테이블에 저장되는 실행 계획을 제공합니다. 일괄 Transaction-SQL 문은 실행될 때마다 컴파일하고 최적화해야 하며 속도가 상대적으로 느립니다.

(3) SQL 언어의 기능 및 유연성 향상: 저장 프로시저는 제어문으로 작성할 수 있고 강력한 유연성을 가지며 복잡한 판단과 복잡한 작업을 완료할 수 있습니다.

(4) 네트워크 트래픽 감소: 동일한 데이터베이스 개체에 대한 작업(예: 쿼리, 수정)의 경우 이 작업과 관련된 Transaction-SQL 문을 저장 프로시저로 구성하면 클라이언트에서 저장 프로시저가 호출될 때 컴퓨터 이때 네트워크에는 호출문만 전송되므로 네트워크 트래픽이 크게 줄어들고 네트워크 부하가 줄어듭니다.

(5) 보안 메커니즘으로 최대한 활용: 특정 저장 프로세스를 실행하는 권한을 제한함으로써 해당 데이터에 대한 액세스 권한을 제한하여 무단 사용자의 데이터 액세스를 방지하고 데이터 보안을 보장할 수 있습니다.

17. 트리거:

트리거는 트리거가 위치한 테이블에서 지정된 이벤트가 발생하고 정의된 조건이 충족될 때 정의된 문 집합입니다. 트리거가 실행됩니다. 데이터 무결성을 보장하기 위해 데이터베이스 측에 트리거 기능을 적용할 수 있습니다. 트리거는 호출을 사용하여 호출해야 하는 특수 저장 프로시저이지만, 트리거는 호출이나 수동 호출을 사용할 필요가 없다는 점에서 다릅니다. 특정 테이블에 데이터를 삽입하거나 삭제하거나 수정할 때 실행이 발생합니다. 데이터베이스 자체의 표준 기능보다 더 정교하고 복잡한 데이터 제어 기능을 갖추고 있습니다.

18. 커서:

커서는 수영의 식별자이며 포인터 역할을 할 수 있습니다. 커서는 쿼리 데이터베이스에서 반환된 결과 집합의 모든 레코드를 탐색하는 데 사용할 수 있습니다. 즉, 해당 작업을 수행하기 위해 한 번에 한 행의 데이터만 가리키고 검색할 수 있습니다. 커서를 사용하지 않을 때는 누군가가 한꺼번에 모든 것을 주고 빼앗아 가는 것과 같습니다. 커서를 사용한 후에는 누군가가 하나씩 주는 것과 같습니다. 이것이 좋은지 아닌지 확인한 다음 스스로 선택하십시오.

추천 학습: mysql 비디오 튜토리얼