기본 데이터베이스 이론에 대한 자세한 소개-MySQL 튜토리얼-php.cn

1. 데이터베이스 패러다임

첫 번째 패러다임: 열은 분리될 수 없습니다. 예: [연락처](이름, 성별, 전화번호), 연락처에는 집 전화번호와 회사 전화번호가 있지만 이 테이블 구조 디자인은 1NF를 달성하지 못함

두 번째 정규형: 기본 키가 있어 완전한 종속성을 보장합니다. 예: 주문 세부 정보 테이블 [OrderDetail](OrderID, ProductID, UnitPrice, 할인, 수량, ProductName), 할인(할인), Quantity(수량)는 기본 키(OderID, ProductID)에 완전히 종속되는 반면 UnitPrice, ProductName은 종속됩니다. on ProductID의 경우 2NF를 준수하지 않습니다.

세 번째 정규형: 전이적 종속성 없음(기본 키가 아닌 열 A는 기본 키가 아닌 열 B에 종속되고 기본 키가 아닌 열 B는 기본 키에 따라 다름) : 주문 테이블 [Order] (OrderID, OrderDate , CustomerID, CustomerName, CustomerAddr, CustomerCity) 기본 키는 (OrderID), CustomerName, CustomerAddr, CustomerCity입니다. 기본 키 전송을 통해 기본 키에 의존하므로 3NF를 준수하지 않습니다.

2. 안티 패턴이란 무엇입니까?

Paradigm은 데이터 중복을 방지하고 데이터베이스 공간을 줄이며 데이터 무결성 유지 문제를 줄일 수 있습니다.

그러나 데이터베이스 정규화 설계를 통해 데이터베이스 업무에 관련된 테이블 수가 늘어나고, 관련된 업무 테이블을 다중 테이블 연결로 쿼리해야 할 수 있으며, 이로 인해 성능이 저하되고 데이터베이스 및 테이블에 도움이 되지 않습니다. 샤딩. 따라서 성능 우선 순위를 위해서는 데이터베이스 구조에 안티 패턴 설계, 즉 공간을 시간과 거래하고, 테이블 간의 연관 쿼리를 피하기 위해 데이터 중복성을 채택하는 것이 필요할 수 있습니다. 데이터 일관성 문제의 경우, 강력한 데이터 일관성을 충족하기 어렵기 때문에 일반적으로 저장된 데이터는 사용자 간에 최대한 일관성을 유지해야 시스템이 짧은 시간 내에 자체 복구 및 수정 후 결국 일관성을 달성할 수 있습니다. 기간.

데이터베이스를 설계할 때 안티패턴을 신중하게 사용해야 합니다. 일반적으로 정규화된 데이터베이스 디자인을 사용하면 제품의 유연성이 향상되고 데이터베이스 계층에서 데이터 무결성을 유지할 수 있으므로 가능한 한 정규화된 데이터베이스 디자인을 사용하십시오.

가끔 성능을 향상시키는 가장 좋은 방법은 중복된 데이터를 동일한 테이블에 저장하는 것입니다. 소량의 더티 데이터가 허용될 수 있다면 완전히 독립적인 요약 테이블이나 캐시 테이블을 만드는 것이 매우 좋은 방법입니다. 예를 들어, 다운로드 횟수 정보를 캐시하도록 '다운로드 횟수 테이블'을 설계하면 대용량 데이터의 경우 전체 정보를 조회하는 속도를 높일 수 있습니다.

또 다른 일반적인 시나리오는 확장성을 고려하여 BLOB 및 TEXT 유형 열을 사용하여 JSON 구조 데이터를 저장할 수 있습니다. 이 방법의 장점은 언제든지 이 필드에 새 속성을 추가할 수 있고 테이블 구조가 변경되지 않는다는 것입니다. 필수의. 그러나 이 설계의 단점도 명백합니다. 즉, 지정된 속성을 얻으려면 전체 필드 내용을 얻고 디코딩해야 하며 인덱싱, 정렬, 집계와 같은 작업을 수행할 수 없습니다. 따라서 보다 복잡한 사용 시나리오를 고려해야 한다면 MongoDB와 같은 문서 데이터베이스를 사용하는 것이 더 좋습니다.

3. 데이터베이스 트랜잭션

트랜잭션은 데이터베이스 작업의 불가분한 시퀀스이며 데이터베이스 동시성 제어의 기본 단위입니다.

(1) 트랜잭션의 특징

원자성: 트랜잭션에 포함된 일련의 데이터베이스 작업이 모두 성공적으로 실행되거나 모두 롤백됩니다.

일관성: 트랜잭션의 실행 결과는 데이터베이스를 하나의 일관성 상태로 만들어야 합니다.

격리(Isolation): 동시에 실행되는 트랜잭션은 서로 영향을 미칠 수 없습니다.

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

(2). 트랜잭션 동시성으로 인한 문제

더티 읽기: 하나의 트랜잭션이 다른 트랜잭션의 커밋되지 않은 데이터를 읽습니다.

반복 불가능한 읽기의 초점은 수정이며, 동일한 조건에서 두 개의 읽기를 수행합니다. 즉, 읽은 데이터는 다른 트랜잭션에 의해 수정될 수 있습니다.

팬텀 읽기: 팬텀 읽기의 초점은 동일한 조건에서 두 번 읽은 레코드의 수입니다.

(3) 격리 수준

격리 수준은 한 세션의 트랜잭션이 다른 세션의 트랜잭션에 미칠 수 있는 영향을 결정합니다.

ANSI 표준은 MySQL의 InnoDB에서 지원되는 4가지 격리 수준을 정의합니다. 즉:

READ UNCOMMITTED(커밋되지 않은 읽기): 가장 낮은 격리 수준으로 종종 더티 읽기(dirty read)라고 하며 한 트랜잭션이 다른 트랜잭션을 읽을 수 있도록 허용합니다. 트랜잭션이 아직 완료되지 않았습니다. 커밋된 데이터는 성능을 향상시킬 수 있지만 더티 읽기 문제로 이어질 수 있습니다.

READ COMMITTED: 다른 트랜잭션에 의해 커밋된 레코드만 트랜잭션에서 볼 수 있도록 허용됩니다.

REPEATABLE READ(반복 읽기): 트랜잭션이 시작된 후 이 트랜잭션이 커밋되거나 롤백될 때까지 다른 트랜잭션에 의한 데이터베이스 수정 사항이 이 트랜잭션에 표시되지 않습니다. 그러나 다른 트랜잭션의 삽입/삭제 작업은 이 트랜잭션에서 볼 수 있습니다. 즉, 이 격리 수준에서는 팬텀 읽기 문제를 피할 수 없습니다. 트랜잭션에서 반복된 선택의 결과는 해당 트랜잭션에서 데이터베이스가 업데이트되지 않는 한 동일합니다.

SERIALIZABLE(직렬화 가능): 가장 높은 수준의 격리로 트랜잭션만 직렬로 실행할 수 있습니다.

MySQL의 기본 격리 수준은 REPEATABLE READ입니다.

기본 데이터베이스 이론에 대한 자세한 소개

4. 저장 프로시저란 무엇인가요? 장점과 단점은 무엇입니까?

저장 프로시저는 미리 컴파일되어 데이터베이스에 저장된 SQL 문 모음입니다. 또한 저장 프로시저는 일부 T-SQL 문으로 구성된 코드 블록입니다. 이러한 T-SQL 문 코드는 메서드(단일 테이블 또는 여러 테이블 추가, 삭제, 수정 및 쿼리)와 같은 일부 기능을 구현한 다음 이 코드를 제공합니다. block 이 함수를 사용할 때 이름을 지정하고 호출하면 됩니다. 저장 프로시저는 다음과 같은 특징을 가지고 있습니다:

4.1 저장 프로시저는 생성될 때만 컴파일됩니다. 일반적으로 SQL 문은 실행될 때마다 한 번씩 컴파일됩니다. , 따라서 저장 프로시저를 사용하면 데이터베이스 실행 효율성이 향상됩니다.

4.2. SQL 문이 변경되면 코드를 수정하지 않고 데이터베이스의 저장 프로시저만 수정할 수 있습니다.

4.3. 클라이언트는 일련의 SQL 전송을 실행하는 것보다 확실히 더 좋습니다.

4.4 저장 프로시저를 통해 권한이 없는 사용자는 간접적으로 제어되는 데이터베이스에 액세스할 수 있으므로 데이터 보안이 보장됩니다.

5. Drop, delete, truncate

의 차이점에 대해 간단히 이야기해 보겠습니다. SQL에서 drop, delete, truncate는 모두 삭제를 의미하지만 세 가지 사이에는 몇 가지 차이점이 있습니다.

Delete는 모두 삭제하거나 삭제하는 데 사용됩니다. 삭제를 실행한 후 삭제 명령은 이 테이블의 모든 삭제 트리거를 트리거합니다.

Truncate는 테이블의 모든 데이터를 삭제합니다. 롤백할 수 없으며 이 테이블의 트리거는 트리거되지 않습니다. TRUNCATE는 삭제보다 빠르며

Drop 명령은 데이터베이스에서 테이블을 삭제하며 모든 데이터 행, 인덱스 및 권한도 삭제됩니다. 모든 DML 트리거가 실행되지 않으며 이 명령은 롤백될 수 없습니다.

따라서 테이블이 더 이상 필요하지 않으면 drop을 사용하고, 일부 데이터 행을 삭제하려면 delete를 사용하고, 테이블을 유지하고 모든 데이터를 삭제하려면 truncate를 사용하세요.

6. 뷰란 무엇인가요? 커서란 무엇입니까?

뷰는 일반적으로 하나의 테이블 또는 여러 테이블의 행이나 열의 하위 집합으로, 뷰를 추가, 삭제, 수정 및 쿼리할 수 있습니다. 특히, 뷰를 수정해도 기본 테이블에는 영향을 주지 않습니다. 다중 테이블 쿼리에 비해 데이터를 더 쉽게 얻을 수 있습니다.

커서는 쿼리 결과 집합을 단위로 효율적으로 처리합니다. 커서를 셀의 특정 행에 배치하여 결과 집합의 현재 행에서 하나 이상의 행을 검색할 수 있습니다. 결과 세트의 현재 행을 변경할 수 있습니다. 커서는 일반적으로 사용되지 않지만, 데이터를 하나씩 처리해야 할 때 커서는 매우 중요합니다.

mysql을 운영할 때 우리는 MySQL 검색 작업이 결과 집합이라는 행 집합을 반환한다는 것을 알고 있습니다. 반환된 행 집합은 SQL 문과 일치하는 모든 행(0개 이상의 행)입니다. 예를 들어 간단한 SELECT 문을 사용하면 첫 번째 행, 다음 행 또는 처음 10개 행을 가져올 수 없으며 모든 행을 한 번에 하나씩 처리하는 쉬운 방법도 없습니다. 배치). 검색된 행에서 하나 이상의 행을 앞이나 뒤로 이동해야 하는 경우가 있습니다. 이것이 커서가 사용되는 이유입니다. 커서는 MySQL 서버에 저장된 데이터베이스 쿼리이며 SELECT 문이 아니라 해당 문에 의해 검색된 결과 집합입니다. 커서가 저장된 후 응용 프로그램은 필요에 따라 커서 내의 데이터를 스크롤하거나 찾아볼 수 있습니다. 커서는 사용자가 화면의 데이터를 스크롤하고 데이터를 찾아보거나 변경해야 하는 대화형 응용 프로그램에서 주로 사용됩니다.

7. 트리거란 무엇인가요?

트리거는 정의된 조건이 충족될 때 실행되고 트리거에 정의된 일련의 문을 실행하는 테이블과 관련된 데이터베이스 개체입니다. 이 트리거 기능은 애플리케이션이 데이터베이스 측에서 데이터베이스의 무결성을 보장하는 데 도움이 될 수 있습니다.

8. 슈퍼 키, 후보 키, 기본 키, 외래 키

슈퍼 키: 관계에서 튜플을 고유하게 식별할 수 있는 속성 집합을 관계형 패턴의 슈퍼 키라고 합니다. 하나의 속성을 슈퍼 키로 사용할 수 있으며 여러 속성을 결합하여 슈퍼 키로 사용할 수도 있습니다. 슈퍼키에는 후보키와 기본키가 있습니다.

후보 키: 최소 슈퍼키, 즉 중복되는 요소가 없는 슈퍼키입니다.

기본 키: 저장된 데이터 개체를 고유하고 완전하게 식별하는 데이터베이스 테이블의 데이터 열 또는 속성 조합입니다. 데이터 열에는 기본 키가 하나만 있을 수 있으며 기본 키의 값은 누락될 수 없습니다. 즉, null이 될 수 없습니다.

외래 키: 한 테이블에 존재하는 다른 테이블의 기본 키를 이 테이블의 외래 키라고 합니다.

9. 거래란 무엇인가요? 자물쇠란 무엇입니까?

트랜잭션: 논리적 작업 단위로 함께 바인딩된 SQL 문 그룹입니다. 문 작업이 실패하면 전체 작업이 실패하고 향후 작업은 작업 이전 상태로 롤백되거나 그것에 노드가 되십시오. 무언가가 실행되거나 실행되지 않도록 하기 위해 트랜잭션을 사용할 수 있습니다. 그룹화된 문이 트랜잭션으로 간주되려면 ACID 테스트, 즉 원자성, 일관성, 격리성 및 내구성을 통과해야 합니다.

잠금: 모든 DBMS에서 잠금은 트랜잭션 구현의 핵심입니다. 잠금은 트랜잭션의 무결성과 동시성을 보장할 수 있습니다. 실생활의 잠금과 마찬가지로 특정 데이터의 소유자가 특정 기간 동안 특정 데이터 또는 데이터 구조를 사용하는 것을 방지할 수 있습니다. 물론 잠금도 레벨로 구분됩니다.

10. 데이터베이스 잠금 메커니즘

데이터베이스 잠금 메커니즘은 다양한 공유 리소스에 동시에 액세스하고 액세스가 질서있게 되도록 데이터의 일관성을 보장하기 위해 데이터베이스에서 설계한 규칙입니다. 각 MySQL 스토리지 엔진은 행 수준 잠금, 페이지 수준 잠금, 테이블 수준 잠금의 세 가지 유형(수준)의 잠금 메커니즘을 사용합니다.

테이블 수준 잠금(테이블 수준): 테이블 수준 잠금은 MySQL 스토리지 엔진 중에서 가장 세부적인 잠금 메커니즘입니다. 이 잠금 메커니즘의 가장 큰 특징은 구현 논리가 매우 간단하고 시스템에 최소한의 부정적인 영향을 미친다는 것입니다. 따라서 잠금을 획득하고 해제하는 것이 매우 빠릅니다. 테이블 수준 잠금은 전체 테이블을 한 번에 잠그기 때문에 우리를 괴롭히는 교착 상태 문제를 피할 수 있습니다. 물론, 큰 잠금 세분화의 가장 큰 부정적인 영향은 리소스 잠금에 대한 경합 가능성이 가장 높아 효율성이 크게 감소한다는 것입니다. 테이블 수준 잠금은 읽기 잠금과 쓰기 잠금으로 구분됩니다. 페이지 수준 잠금(page-level): 페이지 수준 잠금의 특징은 잠금 세분성이 행 수준 잠금과 테이블 수준 잠금 사이에 있으므로 잠금을 획득하는 데 필요한 리소스 오버헤드와 동시 처리 기능이 가능하다는 것입니다. 또한 위의 두 가지 사이에 제공됩니다. 또한 페이지 수준 잠금과 행 수준 잠금은 교착 상태를 유발합니다. 행 수준 잠금(row-level): 행 수준 잠금의 가장 큰 특징은 잠긴 개체의 세분성이 매우 작다는 점이며, 이는 현재 주요 데이터베이스 관리 소프트웨어가 달성하는 가장 작은 잠금 세분성이기도 합니다. 잠금 세분성은 매우 작기 때문에 리소스 잠금에 대한 경합 가능성도 최소화되어 애플리케이션에 가능한 한 많은 동시 처리 기능을 제공하고 높은 동시성을 요구하는 일부 애플리케이션 시스템의 전체 성능을 향상시킬 수 있습니다. 행 수준 잠금은 동시 처리 기능에 있어서 큰 장점을 갖고 있지만, 많은 단점도 가지고 있습니다. 잠금 리소스의 세분성은 매우 작기 때문에 잠금을 획득하고 해제할 때마다 더 많은 작업을 수행해야 하며 자연스럽게 소비도 더 커집니다. 또한 행 수준 잠금은 교착 상태가 발생하기 가장 쉽습니다. InnoDB의 행 수준 잠금도 공유 잠금과 배타적 잠금의 두 가지 유형으로 나누어집니다. 마찬가지로 InnoDB도 의도 잠금(테이블 수준 잠금)이라는 개념을 도입하여 의도 공유 잠금과 의도 배타 잠금이 있으므로 InnoDB는 실제로 네 가지 잠금 유형, 즉 공유 잠금(S), 배타적 잠금(X), 의도 공유 잠금(IS), 의도 배타적 잠금(IX)

MySQL 데이터베이스에서는 테이블 수준 잠금이 주로 MyISAM에서 사용됩니다. 메모리, CSV 및 일부 비트랜잭션 스토리지 엔진. 행 수준 잠금은 주로 Innodb 스토리지 엔진 및 NDBCluster 스토리지 엔진에서 사용되며 페이지 수준 잠금은 주로 BerkeleyDB 스토리지 엔진의 잠금 방법입니다.

의도 잠금의 기능은 트랜잭션이 리소스 잠금을 획득해야 할 때 필요한 리소스가 이미 배타적 잠금에 의해 점유된 경우 트랜잭션이 행을 잠가야 하는 테이블에 적절한 의도 잠금을 추가할 수 있다는 것입니다. 공유 잠금이 필요한 경우 테이블에 의도 공유 잠금을 추가하세요. 특정 행(또는 일부 행)에 배타적 잠금을 추가해야 하는 경우 먼저 테이블에 의도 배타적 잠금을 추가하세요. 다중 의도 공유 잠금은 동시에 존재할 수 있지만 동시에 하나의 의도 배타적 잠금만 존재할 수 있습니다.

기본 데이터베이스 이론에 대한 자세한 소개