MySQL 스토리지 엔진에 대한 예비 연구

MySQL 스토리지 엔진에 대한 예비 연구

목차:

1. 스토리지 엔진 소개

2.

3, MyISAM과 InnoDB Lock 비교

4. 두 스토리지 엔진의 인덱스 비교

1 스토리지 엔진 소개

지침: 5.7 기준 .19 MySQL 데이터베이스.

그림 1.1 데이터베이스 버전

Navicat for MySQL에서 테스트됨:

Enter sql: showengine;

그림 1.2 스토리지 엔진 카테고리

매개변수 설명:

Engine: 스토리지 엔진 이름

Support: MySQL이 이 엔진을 지원하는지 여부

Comment: 엔진 설명

Transaction: 트랜잭션 처리 지원 여부

X 에이 : 분산 트랜잭션 처리 및 트랜잭션 처리 환경 여부, 외래 키, 기본 저장 엔진, "즉시 사용 가능" 지원.

l

MyISAM

은 주로 읽기 전용 데이터 웨어하우스, 전자 상거래 및 엔터프라이즈 애플리케이션에 적합합니다. MyISAM은 고급 캐싱 및 인덱싱 메커니즘을 사용하여 데이터 검색 및 인덱싱 속도를 향상시키지만 트랜잭션이나 외래 키는 지원하지 않습니다.

l Blackhole

은 애플리케이션이 실제로 데이터를 쓰고 있지만 디스크에 데이터를 저장하고 싶지 않은 테스트 시나리오에 적합합니다. Blackhole 스토리지 엔진은 특정 요구 사항을 충족합니다. 바이너리 로깅이 활성화된 경우 Blackhole 스토리지 엔진을 복제 토폴로지의 릴레이 또는 프록시로 사용하여 SQL 문이 로그에 기록됩니다. 이 경우 Relay Agent는 마스터의 데이터를 처리하여 슬레이브에게 전송하지만 자체적으로 데이터를 저장하지는 않습니다.

l CSV

은 CSV 로그 파일을 작성하고 구조화된 비즈니스 데이터를 스프레드시트로 빠르게 가져오는에 적합합니다. CSV 스토리지 엔진은 표 형식의 CSV(쉼표로 구분된 값) 파일을 생성하고 읽고 씁니다. 인덱싱 메커니즘을 제공하지 않고, 날짜/시간 값을 저장 및 변환할 때 특정 문제가 있으며, 데이터 저장에 효율적이지 않으므로 주의해서 사용해야 합니다.

l Memory

은 우편번호 목록, 지방 및 도시 목록, 분류 목록 등과 같이 자주 액세스하고 거의 변경되지 않는 정적 데이터 에 적합하며 액세스에 적합합니다. 스냅샷 기술을 사용하여 유통 데이터 또는 과거 데이터의 데이터베이스 . 메모리(HEAP라고도 함)는 해싱 메커니즘을 사용하여 자주 사용되는 데이터를 검색하여 더 빠르게 검색할 수 있는 메모리 내 저장소입니다. 데이터는 메모리에 저장되고 MySQL 세션 내에서만 유효하므로 종료 시 데이터가 새로 고쳐지고 삭제됩니다.

l Federated

은 분산 또는 데이터 세트 환경에 적합합니다. 통합 스토리지 엔진을 사용하면 여러 데이터베이스 서버의 테이블을 조인할 수 있습니다. 데이터를 이동하지 않으며 동일한 스토리지 엔진을 사용하기 위해 원격 테이블이 필요하지 않습니다. 페더레이션된 스토리지 엔진은 현재 대부분의 MySQL 배포판에서 비활성화되어 있습니다.

l Archive

은 거의 액세스하지 않는 대량의 아카이브 또는 기록 데이터를 저장하고 검색 하는 데 적합합니다. Archive 스토리지 엔진은 대용량 데이터를 압축된 형식으로 저장하며 인덱스를 지원하지 않으며 테이블 스캔을 통해서만 접근할 수 있습니다.

l MRG_MYISAM

은 데이터가 하나 이상의 데이터베이스의 여러 테이블에 저장되는 데이터 웨어하우스와 같은 매우 큰 데이터베이스 애플리케이션에 적합합니다. MRG_MYISAM 스토리지 엔진의 가장 큰 특징은 큰 테이블을 여러 개의 작은 테이블로 나누어 서로 다른 디스크에 저장하고 이러한 작은 테이블을 병합한 다음 동시에 액세스하는 속도입니다. 각각의 작은 테이블은 더 적은 양의 데이터를 관리해야 하기 때문입니다.

단점:

l은 복합 테이블을 형성하기 위해 동일한 MyISAM 테이블을 사용해야 합니다.

l 교체 작업을 사용할 수 없습니다.

l 인덱스는 단일 인덱스보다 낮습니다. 테이블.

2 InnoDB와 MyISAM 성능 비교

참고: 테스트 테이블에는 36개 필드, 및 988218개의 레코드가 포함되어 있습니다.

InnoDB 스토리지 엔진의 테스트 데이터베이스는 이 테이블을 포함하는 Innodbtest이고 테이블 이름은 Innodbtable입니다. MyISAM 스토리지 엔진의 테스트 데이터베이스는 이 테이블을 포함하는 Myisamtest이며 테이블 이름은 Myisamtable입니다.

MySQL에서 InnoDB 및 MyISAM 스토리지 엔진을 사용하여 테이블을 테스트합니다. 먼저 예비 작업을 수행합니다.

(1) MyISAM 스토리지 엔진을 테스트하는 테이블의 스토리지 엔진을 기본 InnoDB에서 MyISAM으로 변경합니다.

alter table myisamtable engine=myisam;

그림 2.1 스토리지 엔진 수정

(2) 데이터베이스의 문자 인코딩을 수정하고 utf-8

alter database myisamtest character set utf8;
alter database innodbtest character set utf8;

그림 2.2 InnoDB 스토리지 엔진 테스트 라이브러리 문자 인코딩 수정

🎜

그림 2.3 MyISAM 스토리지 엔진 테스트 라이브러리 문자 인코딩 수정

두 스토리지 엔진의 일부 기능 테스트:

l 스토리지 구조

(1) InnoDB :

테이블 데이터는 1.21GB 크기의 데이터 파일인 Innodbtable.ibd에 저장됩니다. 테이블 구조의 정의 정보를 포함하여 테이블과 관련된 메타데이터 정보가 innodbtable.frm 파일에 저장됩니다. 데이터베이스의 일부 정의 정보는 db.opt에 정의되어 있습니다.

그림 2.4 InnoDB 디스크 저장 디렉터리

그림 2.5 db.opt 파일 콘텐츠

(2) MyISAM:

.frm 파일: 저장 공간 및 앰프 ; 테이블 구조의 정의 정보 등을 포함한 테이블 관련 메타데이터 정보

.MYD 파일: 크기 853.34MB, MyISAM 테이블의 데이터를 저장합니다.

.MYI 파일: 34.11MB 크기로 MyISAM 테이블의 인덱스 관련 정보를 저장합니다.

db.opt: ​​​​데이터베이스의 일부 정의 정보를 정의합니다.

그림 2.6 MyISAM 디스크 저장 디렉터리

그림 2.7 db.opt 파일 내용

내가 선택

(1) InnoDB:

그림 2.8 InnoDB 선택 테스트

(2) MyISAM:

그림 2 .9 MyISAM 선택 테스트

l 삽입

(1) InnoDB:

그림 2.10 InnoDB 삽입 테스트

(2) MyISAM:

그림 2.11 MyISAM 삽입 테스트

l 업데이트

(1) InnoDB:

그림 2.12 InnoDB 업데이트 테스트

(2) MyISAM:

그림 2.13 MyISAM 업데이트 테스트

l 삭제

(1) InnoDB:

그림 2.14 InnoDB 삭제 테스트

(2) MyISAM:

MyISAM 테스트 삭제

어디에서 삭제

(1) InnoDB:

그림 2.16 InnoDB 삭제 위치 테스트

(2) 내 ISAM:

그림 2.17 MyISAM 삭제 where test

l count 없이 where

(1) InnoDB:

그림 2 .18 Where 테스트 없이 InnoDB 계산

(2) MyISAM:

그림 2.19 where 테스트 없이 MyISAM 개수

l group by

（1）InnoDB：

 

图2.20 InnoDB的group by测试

（2）MyISAM：

图2.21 MyISAM的group by测试

l 外键

创建一个新表，将测试表的主键作为新表的外键进行测试：

create table `foreigntest`(
`id` int primary key not null,
`taskid` varchar(64) not null,
`host` varchar(128) not null default '',
`month` char(8) not null,
constraint `fk_task_h_m` foreign key (`taskid`,`host`,`month`)
references `innodbtable`(`taskid`,`host`,`month`)
) charset=utf8mb4

（1）InnoDB：

 

图2.22 InnoDB的外键测试

（2）MyISAM：

 

图2.23 MyISAM的外键测试

 

总结如下表：

에 도달 할 때 상대적으로 어려운 것입니다. 모두 삭제 더 좋음(0.24초) Change더 좋음(0.12초)chaBetter(65.57초)Lock테이블 잠금만 지원됩니다외래 키지원되지 않음

	InnoDB	MyISAM
存储结构	.ibd：存放表数据； .frm文件：存储与表相关的元数据信息，包括表结构的定义信息等； 基于磁盘的资源是InnoDB表空间数据文件和它的日志文件，InnoDB表的大小只受限于操作系统文件的大小，一般为2GB。	每个表在磁盘上存储成三个文件： .MYD文件：存放表的数据。 .MYI文件：存放表的索引相关信息。 .frm文件：存储与表相关的元数据信息，包括表结构的定义信息等；
存储空间	InnoDB 테이블은 더 많은 메모리와 디스크 스토리지를 필요로 하며, 데이터와 인덱스 캐싱을 위해 메인 메모리에 자체 전용 버퍼 풀을 구축합니다.	MyISAM은 압축이 가능하며 저장 공간이 더 작습니다. frefe 포트 가능성 ree free 솔루션은 데이터 파일을 복사하거나 Binlog를 백업하거나 MysqlDump를 사용하는 것일 수 있습니다. 이는 데이터 볼륨이 수십 개의 g
MyISAM 데이터는 파일 형태로 저장되기 때문에 크로스 플랫폼 데이터 전송에 매우 편리합니다. 백업 및 복구 중에 테이블을 개별적으로 운영할 수 있습니다	트랜잭션 보안	트랜잭션(커밋), 롤백(롤백) 및 충돌 복구 기능으로 트랜잭션 지원
는 트랜잭션을 지원하지 않으며 각 쿼리는 원자적입니다.	increased	더 좋음(0.15초)
(0.40초) ㅋㅋㅋ		(263.86 초)
		(0.20초)
		(139.75초)
		테이블 잠금 및 행 잠금 지원 행 잠금은 다중 사용자 동시 작업의 성능을 크게 향상시킵니다. 그러나 InnoDB의 행 잠금은 WHERE의 기본 키에서만 유효합니다. 기본 키가 아닌 WHERE는 전체 테이블을 잠급니다.
		지원됨
count 없이 where	는 테이블의 특정 행 수를 저장하지 않으므로 행별 스캔 통계(70.88초)가 필요합니다.	이 더 좋습니다. MyISAM은 테이블에 특정 행 수를 저장합니다. 행 수는 단순히 판독됩니다. (0.09초)
그룹별	(35.14초)	더 좋음(4.75초)

참고:

[1]Tablespace: InnoDB는 데이터, 인덱스 및 롤백 메커니즘을 포함하여 시스템 독립적인 파일을 구성하는 데 사용되는 도구입니다. 기본적으로 모든 테이블은 테이블스페이스(공유 테이블스페이스라고 함)를 공유합니다. 공유 테이블스페이스는 여러 파일로 자동 확장되지 않습니다. 기본적으로 테이블스페이스는 데이터가 증가함에 따라 증가하는 단일 파일만 차지합니다. 테이블스페이스가 새 파일을 생성하도록 허용하려면 자동 확장 옵션을 사용하십시오.

[2]충돌 복구 기능: InnoDB 스토리지 엔진은 두 가지 디스크 기반 메커니즘을 사용하여 데이터, 즉 로그 파일과 테이블 공간을 저장합니다. InnoDB는 종료되거나 충돌하기 전에 이러한 로그를 사용하여 데이터 복구를 다시 작성합니다. 프로그램 시작 시 InnoDB는 로그를 읽고 자동으로 더티 페이지를 디스크에 기록하여 시스템 충돌 전에 버퍼링된 업데이트를 복원합니다.

3 확률은 가장 높고 동시성은 가장 낮습니다. (2) 행 수준 잠금: 높은 오버헤드, 느린 잠금이 발생할 수 있습니다. 잠금 세분성은 가장 작고 잠금 충돌 가능성은 가장 낮으며 동시성은 가장 높습니다.

(3) MyISAM 테이블에 대한 읽기 작업은 동일한 테이블에 대한 다른 사용자의 읽기 요청을 차단하지 않지만 동일한 테이블에 대한 쓰기 요청은 차단합니다. 동일한 테이블의 읽기 및 쓰기 요청; MyISAM 테이블의 읽기 및 쓰기 작업과 쓰기 및 쓰기 작업은 직렬입니다(스레드가 테이블에 대한 쓰기 잠금을 획득하면 잠금을 보유한 스레드만 액세스할 수 있습니다). 업데이트 작업을 수행합니다. 다른 스레드의 읽기 및 쓰기 작업은 잠금이 해제될 때까지 기다립니다.)

(4) 공유 잠금(들): 하나의 트랜잭션이 행을 읽을 수 있도록 허용하여 다른 트랜잭션이 독점 액세스를 얻지 못하도록 합니다. 동일한 데이터 세트에 잠금.

(5) 배타적 잠금(X): 배타적 잠금을 획득한 트랜잭션을 허용하여 데이터를 업데이트하고 다른 트랜잭션이 동일한 데이터 세트 공유 읽기 잠금 및 배타적 쓰기 잠금을 획득하지 못하도록 합니다.

(6) UPDATE, DELETE 및 INSERT 문의 경우 InnoDB는 일반 SELECT 문의 관련 데이터 세트에 자동으로 배타적 잠금(X)을 추가하지만 InnoDB는 잠금을 추가하지 않습니다.

4 인덱스 구조, 이 트리의 리프 노드 데이터 필드는 완전한 데이터 레코드를 저장합니다. 이 인덱스의 키는 데이터 테이블의 기본 키이므로 InnoDB 테이블 데이터 파일 자체가 기본 인덱스가 됩니다.

l InnoDB 테이블에서 사용하는 인덱스는 클러스터형 인덱스입니다. 클러스터형 인덱스는 인덱스뿐만 아니라 데이터 자체도 저장하는 데이터 구조입니다. 따라서 인덱스의 값을 찾으면 별도의 디스크 탐색 없이 바로 데이터를 검색할 수 있습니다.

l 기본 키 인덱스 또는 테이블의 첫 번째 인덱스는 클러스터형 인덱스를 사용하여 생성됩니다.

l InnoDB의 모든 보조 인덱스는 기본 키를 데이터 필드로 참조합니다. 보조 인덱스를 생성하면 클러스터형 인덱스의 키워드(기본 키, 고유 키 또는 행 ID)도 보조 인덱스에 저장되므로 해당 키워드에 따라 빠르게 검색하고 원본 데이터를 클러스터에서 빠르게 얻을 수 있습니다. 클러스터형 인덱스. 즉, 기본 키 열을 사용하여 보조 인덱스를 스캔하는 경우 쿼리에서는 보조 인덱스만 사용하여 데이터를 얻으면 됩니다.

l MyISAM:

l 인덱스 파일과 데이터 파일은 분리되어 있으며, 인덱스 파일에는 데이터 레코드의 주소만 저장됩니다. B+tree를 인덱스 구조로 사용하여 리프 노드의 데이터 필드는 데이터 레코드의 주소를 저장합니다.

l MyISAM에서는 기본 인덱스의 키가 고유해야 하는 반면 보조 인덱스의 키는 반복될 수 있다는 점을 제외하면 기본 인덱스와 보조 인덱스(보조 키) 사이에 구조적 차이가 없습니다.

l 주요 차이점:

l 기본 인덱스의 차이점: InnoDB의 데이터 파일 자체가 인덱스 파일입니다. MyISAM의 인덱스와 데이터는 분리되어 있습니다.

l 보조 인덱스의 차이점: InnoDB의 보조 인덱스 데이터 필드에는 주소 대신 해당 레코드의 기본 키 값이 저장됩니다. MyISAM의 보조 인덱스와 기본 인덱스 사이에는 큰 차이가 없습니다.

참고:

B+ 트리: m차 B+ 트리의 경우 다음과 같은 특성을 갖습니다.

l 노드에 n개의 키워드를 포함하는 n개의 하위 트리가 있습니다.

l 모든 리프 노드에는 모든 키워드에 대한 정보와 이러한 키워드가 포함된 레코드에 대한 포인터가 포함되어 있습니다. 그리고 리프 노드 자체는 키워드의 크기에 따라 작은 것부터 큰 것 순으로 연결됩니다.

l 모든 비터미널 노드는 인덱스 부분으로 간주될 수 있으며, 노드는 하위 트리(루트 노드)에서 가장 큰(또는 가장 작은) 키워드만 포함합니다.

l B+ 트리에서는 검색 성공 여부에 관계없이 각 검색은 루트에서 리프 노드까지의 경로를 취합니다.

l 트리의 각 노드에는 최대 m개의 하위 트리가 포함됩니다.

위 내용은 MySQL 스토리지 엔진에 대한 예비 연구의 상세 내용입니다. 자세한 내용은 PHP 중국어 웹사이트의 기타 관련 기사를 참조하세요!