MySQL 스토리지 엔진에 대해 이야기해 보세요.

MySQL의 스토리지 엔진은 MySQL 아키텍처의 중요한 부분이자 MySQL 아키텍처의 핵심입니다. 플러그인 스토리지 엔진은 다른 데이터베이스와 구별되는 중요한 기능입니다. 이는 MySQL 아키텍처의 서버 측 맨 아래에 있으며 기본 물리적 구조의 구현입니다. 다양한 기술 방식으로 파일이나 메모리에 데이터를 저장하는 데 사용됩니다. 수준. 일반적인 MySQL 스토리지 엔진에는 InnoDB, MyISAM, Memory, Archive 등이 있습니다. 이들은 서로 다른 특정 애플리케이션에 따라 해당 스토리지 엔진 테이블을 설정할 수 있습니다.

다양한 스토리지 엔진에 대해 이야기하기 전에 몇 가지 기본 개념을 이해해야 합니다.

(1) 트랜잭션

트랜잭션은 A입니다. 원자성 SQL 문 집합 또는 독립적인 작업 단위입니다. 데이터베이스 엔진이 이 SQL 문 집합을 데이터베이스에 성공적으로 적용할 수 있으면 충돌이나 다른 이유로 인해 문을 실행할 수 없는 경우 실행됩니다. 그러면 모든 명령문이 실행되지 않습니다. 즉, 트랜잭션 내의 모든 문은 성공적으로 실행되거나 실패합니다.

은행 애플리케이션의 일반적인 예:

은행 데이터베이스에 수표 테이블과 저축 테이블이라는 두 개의 테이블이 있다고 가정합니다. 이제 고객 A가 당좌 예금 계좌에서 2,000위안을 이체하려고 합니다. 예금 계좌에 입금하려면 최소한 세 단계를 거쳐야 합니다.

a. A의 당좌 예금 잔액이 2,000위안 이상인지 확인합니다.

b.

c. A씨의 예금잔고에 2,000위안을 추가합니다.

이 세 단계는 하나의 거래로 패키지되어야 합니다. 한 단계라도 실패하면 모든 단계를 롤백해야 합니다. 그렇지 않으면 은행 고객인 A가 설명할 수 없는 2,000위안의 손실을 입을 수 있으며 문제가 발생할 수 있습니다. . 이는 일반적인 트랜잭션으로, 분할할 수 없는 가장 작은 작업 단위로 전체 트랜잭션의 모든 작업이 성공적으로 제출되거나 실패하여 일부만 실행하는 것이 불가능합니다. 거래.

(2) 읽기 잠금 및 쓰기 잠금

동시에 데이터를 수정해야 하는 SQL이 여러 개 있을 때마다 동시성 제어 문제가 발생합니다. .

공용 메일함을 가정하면 사용자 A가 메일함을 읽는 동시에 사용자 B가 메일함에서 이메일을 삭제하고 있다면 결과는 어떻게 될까요? 고객 A는 읽을 때 오류가 발생하여 종료되거나 일관성 없는 사서함 데이터를 읽을 수 있습니다. 사서함을 데이터베이스의 테이블로 취급하면 동일한 문제가 있음을 알 수 있습니다.

이런 고전적인 문제를 해결하는 방법이 동시성 제어입니다. 즉, 동시 읽기 또는 쓰기를 처리할 때 두 가지 유형의 잠금으로 구성된 잠금 시스템을 구현하면 문제를 해결할 수 있습니다. 이 두 가지 잠금 유형은 공유 잠금과 배타적 잠금(읽기 잠금 및 쓰기 잠금이라고도 함)입니다.

읽기 잠금은 공유됩니다. 즉, 여러 클라이언트가 서로 간섭하지 않고 동시에 동일한 리소스를 읽을 수 있습니다. 쓰기 잠금은 배타적입니다. 즉, 쓰기 잠금은 다른 쓰기 잠금과 읽기 잠금을 차단합니다. 이 방법을 통해서만 주어진 시간에 한 명의 사용자만 쓰기를 수행할 수 있으며 다른 사용자가 쓰기 중인 동일한 리소스를 읽을 수 없도록 할 수 있습니다. 쓰기 잠금은 읽기 잠금보다 우선순위가 높습니다.

(3) 행 잠금 및 테이블 잠금

실제 데이터베이스 시스템에서는 잠금이 매 순간 발생하며 잠금도 세분화되어 있어 공유성이 향상되는 방법 리소스 동시 발급은 잠금을 보다 선택적으로 만들고 모든 리소스가 아닌 수정이 필요한 데이터의 일부만 잠그려고 하므로 정밀한 잠금이 필요합니다. 그러나 잠금에는 잠금 획득, 잠금 해제 여부 확인, 잠금 해제 등의 리소스도 필요하므로 시스템 오버헤드가 증가합니다. 소위 잠금 전략은 잠금 오버헤드와 데이터 보안 간의 균형을 찾는 것입니다. 이 균형은 성능에도 영향을 미칩니다.

각 MySQL 스토리지 엔진에는 고유한 잠금 전략과 잠금 세분성이 있습니다. 가장 일반적으로 사용되는 두 가지 중요한 잠금 전략은 테이블 잠금과 행 잠금입니다.

테이블 잠금은 가장 비용이 적게 드는 전략이며 사용자가 테이블에 쓸 때 먼저 쓰기 잠금을 획득해야 합니다. 이는 다른 사용자가 테이블에 대한 모든 읽기 및 쓰기 작업을 차단합니다. . 쓰기 잠금이 없으면 다른 읽기 사용자가 읽기 잠금을 얻을 수 있으며 읽기 잠금은 서로를 차단하지 않습니다. 행 잠금은 최대 동시 처리를 지원할 수 있지만 최대 잠금 오버헤드도 발생합니다. 이는 지정된 레코드만 잠그고 다른 프로세스는 여전히 동일한 테이블의 다른 레코드에서 작동할 수 있습니다. 테이블 수준 잠금은 빠르지만 충돌이 많고, 행 수준 잠금은 충돌이 적지만 느립니다.​  

 

위의 개념을 이해하면 서로 다른 스토리지 엔진 간의 차이점을 잘 구분할 수 있습니다.

InnoDB 스토리지 엔진

MySQL 스토리지 엔진은 공식 스토리지 엔진과 타사 스토리지 엔진으로 나눌 수 있으며, InnoDB는 강력한 타사 스토리지 엔진입니다. 상대적으로 좋은 성능과 자동 충돌 복구 기능은 현재 널리 사용되고 있으며 현재 MySQL 스토리지 엔진의 주류입니다. 트랜잭션 스토리지와 비트랜잭션 스토리지 모두에서 널리 사용됩니다.

InnoDB 스토리지 엔진은 트랜잭션, 행 잠금, 비잠금 읽기 및 외래 키를 지원합니다.

특별한 이유가 없다면 테이블 생성 시 우선적으로 InnoDB를 사용하는 것을 고려해 볼 수 있습니다. InnoDB는 또한 시간을 들여 깊이 배울 가치가 있는 아주 좋은 스토리지 엔진입니다. 우리는 이 스토리지 엔진을 앞으로 연구할 계획이므로 여기서는 자세히 다루지 않겠습니다.

2. MyISAM 스토리지 엔진

MyISAM 스토리지 엔진은 MySQL이 등장하고 개선되기 이전에는 주류 MySQL 스토리지 엔진이었습니다. 하지만 주로 트랜잭션을 지원하지 않기 때문에 단계적으로 폐지되고 있습니다. 이는 MySQL 개발자가 모든 애플리케이션에 트랜잭션이 필요한 것은 아니라고 생각하여 트랜잭션을 지원하지 않는 스토리지 엔진이 존재하기 때문일 수 있습니다.

MyISAM은 트랜잭션, 행 수준 잠금, 테이블 잠금 및 전체 텍스트 인덱스를 지원하지 않습니다. 가장 큰 결함은 충돌 후 안전하게 복구할 수 없다는 것입니다.

MyISAM은 디자인이 단순하고 데이터가 컴팩트한 형식으로 저장되므로 특정 시나리오에서는 성능이 매우 좋습니다. 그러나 테이블 잠금으로 인해 모든 쿼리가 "잠겨" 있는 경우 성능 문제가 발생합니다. 오랜 시간 상태, 테이블 잠금이 원인입니다.

따라서 상대적으로 작고 복구 작업을 허용할 수 있는 읽기 전용 데이터 또는 테이블의 경우 트랜잭션이 필요하지 않은 애플리케이션의 경우 MyISAM 스토리지 엔진을 선택하면 다음을 수행할 수 있습니다. 더 높은 성능을 위해 MyISAM 스토리지 엔진을 사용하는 테이블은 MySQL과 함께 제공되는 기본 information_schema 라이브러리에 존재합니다.

rree

3. 메모리 저장 엔진

메모리 저장 엔진은 테이블의 데이터를 메모리에 담기 때문에 매우 빠르지만, 테이블 잠금을 지원하므로 동시성 성능이 좋지 않습니다. 가장 나쁜 점은 데이터베이스가 다시 시작되거나 충돌한 후 테이블의 모든 데이터가 손실된다는 것입니다. 이는 임시 데이터를 저장하는 임시 테이블에만 적합합니다. 스토리지 엔진은 일반적으로 MySQL에서 사용됩니다. 쿼리의 중간 결과 세트를 저장합니다. 예를 들어 MySQL과 함께 제공되는 기본 information_schema 라이브러리에는 메모리 스토리지 엔진을 사용하는 많은 테이블이 있습니다.

| TRIGGERS | CREATETEMPORARY TABLE `TRIGGERS` (
  `TRIGGER_CATALOG` varchar(512) NOT NULLDEFAULT '',
  `TRIGGER_SCHEMA` varchar(64) NOT NULL DEFAULT'',
  `TRIGGER_NAME` varchar(64) NOT NULL DEFAULT'',
  `EVENT_MANIPULATION` varchar(6) NOT NULLDEFAULT '',
  `EVENT_OBJECT_CATALOG` varchar(512) NOT NULLDEFAULT '',
  `EVENT_OBJECT_SCHEMA` varchar(64) NOT NULLDEFAULT '',
  `EVENT_OBJECT_TABLE` varchar(64) NOT NULLDEFAULT '',
  `ACTION_ORDER` bigint(4) NOT NULL DEFAULT'0',
  `ACTION_CONDITION` longtext,
  `ACTION_STATEMENT` longtext NOT NULL,
  `ACTION_ORIENTATION` varchar(9) NOT NULLDEFAULT '',
  `ACTION_TIMING` varchar(6) NOT NULL DEFAULT'',
  `ACTION_REFERENCE_OLD_TABLE` varchar(64)DEFAULT NULL,
  `ACTION_REFERENCE_NEW_TABLE` varchar(64)DEFAULT NULL,
  `ACTION_REFERENCE_OLD_ROW` varchar(3) NOTNULL DEFAULT '',
  `ACTION_REFERENCE_NEW_ROW` varchar(3) NOTNULL DEFAULT '',
  `CREATED` datetime DEFAULT NULL,
  `SQL_MODE` varchar(8192) NOT NULL DEFAULT '',
  `DEFINER` varchar(77) NOT NULL DEFAULT '',
  `CHARACTER_SET_CLIENT` varchar(32) NOT NULLDEFAULT '',
  `COLLATION_CONNECTION` varchar(32) NOT NULLDEFAULT '',
  `DATABASE_COLLATION` varchar(32) NOT NULLDEFAULT ''
)ENGINE=MyISAM DEFAULT CHARSET=utf8 |

4. 아카이브 스토리지 엔진

아카이브 스토리지 엔진은 INSERT 및 SELECT 작업만 지원하고 행 잠금을 지원하지만 트랜잭션에 안전하지는 않습니다. 엔진의 가장 큰 장점은 압축률이 일반적으로 1:10에 달할 수 있으며 더 작은 디스크 공간에 동일한 데이터를 저장할 수 있다는 것입니다.

Archive 스토리지 엔진은 기록 데이터, 로그 정보 데이터 등과 같은 아카이브된 데이터를 저장하는 데 매우 적합합니다. 이러한 유형의 데이터는 매우 많은 양의 데이터를 포함하는 경우가 많으며 기본적으로 INSERT 및 SELECT 작업만 수행합니다. . 이 스토리지 엔진을 사용하면 디스크 공간을 많이 절약할 수 있습니다.

특정 라이브러리에 2억 5천만 개의 레코드가 있는 기록 테이블을 예로 들어보겠습니다.

|TABLES | CREATE TEMPORARY TABLE `TABLES` (
  `TABLE_CATALOG` varchar(512) NOT NULL DEFAULT'',
  `TABLE_SCHEMA` varchar(64) NOT NULL DEFAULT'',
  `TABLE_NAME` varchar(64) NOT NULL DEFAULT '',
  `TABLE_TYPE` varchar(64) NOT NULL DEFAULT '',
  `ENGINE` varchar(64) DEFAULT NULL,
  `VERSION` bigint(21) unsigned DEFAULT NULL,
  `ROW_FORMAT` varchar(10) DEFAULT NULL,
  `TABLE_ROWS` bigint(21) unsigned DEFAULTNULL,
  `AVG_ROW_LENGTH` bigint(21) unsigned DEFAULTNULL,
  `DATA_LENGTH` bigint(21) unsigned DEFAULTNULL,
  `MAX_DATA_LENGTH` bigint(21) unsigned DEFAULTNULL,
  `INDEX_LENGTH` bigint(21) unsigned DEFAULTNULL,
  `DATA_FREE` bigint(21) unsigned DEFAULT NULL,
  `AUTO_INCREMENT` bigint(21) unsigned DEFAULTNULL,
  `CREATE_TIME` datetime DEFAULT NULL,
  `UPDATE_TIME` datetime DEFAULT NULL,
  `CHECK_TIME` datetime DEFAULT NULL,
  `TABLE_COLLATION` varchar(32) DEFAULT NULL,
  `CHECKSUM` bigint(21) unsigned DEFAULT NULL,
  `CREATE_OPTIONS` varchar(255) DEFAULT NULL,
  `TABLE_COMMENT` varchar(2048) NOT NULLDEFAULT ''
) ENGINE=MEMORY DEFAULT CHARSET=utf8|

원래 InnoDB 스토리지 엔진으로 기본 설정되었을 때 테이블 크기는 12G였습니다.

mysql> select TABLE_ROWSfrom TABLES where TABLE_NAME='history';
+------------+
| TABLE_ROWS |
+------------+
|  251755162 |
+------------+
1 row in set (0.01 sec)

Archive 스토리지 엔진을 사용하여 위 테이블을 재구축하고 동일한 데이터를 다시 삽입하면 테이블 크기가 2G 미만이 되어 스토리지의 압축률이 좋은 것을 알 수 있습니다.

다른 스토리지 엔진은 덜 일반적으로 사용되므로 여기서는 논의하지 않습니다.

위 내용은 MySQL 스토리지 엔진 내용에 관한 내용입니다. 더 많은 관련 내용은 PHP 중국어 홈페이지(www.php.cn)를 참고해주세요!