빅데이터 학습 2부 MYSQL 고급

Article Directory

• 5 MySQL Architecture
• • 5.1 MyISAM
  • 5.2 Innodb
  • • 5.2.1 시스템 테이블 공간과 독립 테이블 공간을 선택하는 방법
    • 5.2 . 2 Innodb 스토리지 엔진 특징
    • 5.2.3 Innodb 상태 확인
    • 5.2.4 적용 가능한 시나리오
    • 5.2.4 (확장) 잠금이란 무엇입니까
    • • 5.2.3.1 잠금이란 무엇입니까?
      • 5.2.3.2 잠금 유형:
      • 5.2.3.3 쓰기 잠금과 읽기 잠금 간의 호환성 관계(한 행에 대한 호환성)
      • 5.2.3.4 잠금 세분성:
      • 5.2.3.5 차단 및 교착 상태
  • 5. 3 CSV 스토리지 엔진
  • • 5.3.1 CSV 스토리지 엔진의 특징
    • 5.3.2 CSV 스토리지 엔진의 적용 가능한 시나리오
  • 5.4 아카이브 스토리지 엔진
  • • 5.4.1 파일 시스템 스토리지 특성
    • 5.4.2 아카이브 스토리지 엔진 기능
    • 5.4.3 아카이브 스토리지 엔진 사용 시나리오
  • 5.5 메모리 스토리지 엔진
  • • 5.5.1 파일 시스템 스토리지 기능
    • 5.5.2 메모리 기능 기능
    • 5.5.3 쉽게 혼동되는 개념 메모리 5.5.4 메모리의 사용 시나리오 5.6 연합 스토리지 엔진
    5.6.1 Federated의 특징 5.6.2 Federated
  • 5.6.3 연합 해당 시나리오 사용 방법
  6 올바른 스토리지 엔진을 선택하는 방법
  • • 이전 기사에서 이어짐
    • 5 MySQL 아키텍처
• 간단한 작업을 위해 더 일반적으로 사용되는 스토리지 엔진 중 일부를 선택해 보겠습니다. mysql은 데이터베이스의 성능에 직접적인 영향을 미치게 되므로 스토리지 엔진을 사용하기 전에 스토리지 엔진의 몇 가지 특성을 주의 깊게 이해하시기 바랍니다.

5.1 MyISAM

MyISAM은 MySQL5.5 이전의 기본 스토리지 엔진이었습니다. 이러한 이유로 아직 MyISAM 스토리지 엔진을 사용하는 서버가 많이 있습니다. 동시에 MyISAM은 현재 많은 시스템 테이블과 임시 테이블에서 사용되는 스토리지 엔진입니다. 여기서 언급하는 임시 테이블은 우리가 create table을 통해 생성하는 테이블을 의미합니다. 정렬, 그룹화 및 기타 작업 중에 숫자가 특정 크기를 초과하면 쿼리 최적화 프로그램에 의해 임시 테이블이 생성됩니다.

MyISAM 스토리지 엔진은 MYD와 MYI로 구성됩니다. MYD는 데이터 파일의 확장자이고 MYI는 이 두 가지 확장자를 가진 데이터 파일과 인덱스 파일에 테이블을 저장합니다. 기능:

동시성 및 잠금 수준
MyISAM은 행 수준 잠금이 아닌 테이블 수준 잠금을 사용합니다. 즉, 테이블의 데이터를 수정할 때 전체 테이블을 잠가야 하며, 공유 잠금도 테이블을 읽을 때 모든 테이블에 추가됩니다. 여기에서 MyISAM을 엔진으로 사용하는 테이블 읽기 및 쓰기 작업이 상호 배타적이라는 것을 알 수 있습니다. MyISAM은 동시 읽기 및 쓰기 작업에 그다지 좋지 않다는 것을 알 수 있습니다. 읽기 전용 작업에만 해당하는 경우 공유 잠금이 공유 잠금을 차단하지 않으므로 동시성 측면에서 성능이 나쁘지 않습니다.

테이블 손상 복구 MyISAM은 예상치 못한 종료로 인해 손상된 MyISAM 테이블을 확인하고 복구할 수 있도록 지원하지만 여기서 언급한 복구는 데이터 복구가 아닙니다. MyISAM은 트랜잭션 스토리지 엔진이 아니기 때문에 트랜잭션 복구에 필요한 관련 로그가 필요하기 때문입니다. , MyISAM 테이블을 복구하면 데이터가 손실될 수 있다는 점에 유의하세요. check table tablename을 통해 테이블을 확인하고, repair table tablename을 통해 테이블을 복원할 수 있습니다.

MyISAM 테이블에서 지원되는 인덱스 유형create table创建出来的表，是指在排序、分组等操作中，当数量超过一定的大小之后，有查询优化器建立的临时表。
MyISAM存储引擎是由MYD和MYI组成，MYD是数据文件的扩展名，MYI是索引文件的扩展名，这个存储引擎是将表存储在以这两个为扩展名的数据文件和索引文件中。

特性：

• 并发性与锁级别
  MyISAM使用的是表级锁，并不是行级锁，这也就意味着对表中的数据进行修改时，需要对整个表进行加锁，而在对表的读取时也对所有的表加共享锁，从这里我们可以看到，使用MyISAM做引擎的表读取和写入两种操作是互斥的。由此可以看到MyISAM对于读写的并发操作并不会很好。如果只对于只读取操作的话，就并发性而言，性能也还不错，因为共享锁不会阻塞共享锁。
• 表损坏修复
  MyISAM支持由于任意意外关闭而损坏的MyISAM表，进行检查和修复操作，但是这里说的修复并不是数据的恢复，因为MyISAM并不是一种事务性的存储引擎，所以它不能进行事务恢复所需要的相关日志，所以要注意MyISAM表的恢复可能会造成数据的丢失。
  我们可以通过check table tablename对表进行检查，通过repair table tablename对表进行恢复。
• MyISAM表支持的索引类型
  MyISAM支持全文索引，并且在mysql5.7版本之前唯一原生就支持全文索引的官方存储引擎。
• MyISAM表支持数据压缩
  如果MyISAM表示一张很大的只读表，也就是在表创建完导入数据后，就不会对表进行任何修改操作了，那我们就可以对表进行压缩了，以减少磁盘I/O。 我们可以使用myisampack MyISAM은 전체 텍스트 인덱싱을 지원하며 mysql5.7 이전에 전체 텍스트 인덱싱을 기본적으로 지원하는 유일한 공식 스토리지 엔진이었습니다.

MyISAM 테이블은 데이터 압축을 지원합니다.

MyISAM이 대규모 읽기 전용 테이블을 나타내는 경우, 즉 테이블이 생성되고 데이터를 가져온 후 테이블이 수정되지 않으면 테이블을 압축하여 데이터를 줄일 수 있습니다. 디스크 I/O. myisampack 명령을 사용하여 테이블을 압축할 수 있습니다. 압축은 테이블을 독립적으로 압축하므로 데이터 행을 읽을 때 전체 테이블의 압축을 풀 필요가 없습니다.

• 제한 사항:
• Version 큰 테이블을 저장하는 경우 MAX_Rows 및 AVG_ROW_LENGTH

Version을 수정해야 합니다. 256TB

• 적용 가능한 시나리오 :
• 비트랜잭션 애플리케이션
• 읽기 전용 애플리케이션(보고서 등)

공간 애플리케이션

🎜5.2 Innodb🎜🎜

Innodb는 MySQL 5.5 이상 버전의 기본 스토리지 엔진입니다. Innodb는 트랜잭션 스토리지를 위한 스토리지 엔진으로, 트랜잭션 처리를 지원합니다.
Innodb는 테이블스페이스에 대한 고유한 개념을 가지고 있으며, 데이터는 innodb_file_per_table 매개변수에 의해 결정되는 테이블스페이스에 저장됩니다. 이 매개변수가 ON이면 시스템입니다. 각 Innodb 테이블에 대해 확장자가 ibd인 파일이 생성됩니다. 이 매개변수가 OFF인 경우 데이터는 시스템의 공유 테이블 공간, 즉 에 저장됩니다. >ibdataX, X는 기본적으로 1부터 시작하는 숫자를 나타냅니다. innodb_file_per_table这个参数来决定的，如果这个参数为ON，则会对每个Innodb表建立一个扩展名为ibd的系统文件，如果这个参数为OFF时，则会把数据存储到系统的共享表空间，也就是ibdataX，X代表的时一个数字，默认从1开始。
查看这个参数的命令为：
show variables like 'innodb_file_per_table';
修改这个参数的命令为：
set global innodb_file_per_table=off;

5.2.1 系统表空间和独立表空间要如何选择

比较：

系统表空间	独立表空间
无法简单的收缩文件大小	可以通过optimize table命令收缩系统文件
会产生IO瓶颈	可以同时向多个文件刷新数据

建议：

• 对Innodb使用独立表空间

把原来存在于系统表空间中的表转移到独立表空间中的方法。
步骤：

1. 使用mysqldump导出所有数据库表数据
2. 停止MySQL服务，修改参数，并删除Innodb相关文件
3. 重启MySQL服务，重建Innodb系统表空间
4. 重新导入数据

5.2.2 Innodb存储引擎的特性

• Innodb是一种事务性存储引擎
• 完全支持事务的ACID特性（之前介绍过的原子性，一致性等）
• Redo Log 和 Undo Log
  Redo Log实现了事务的持久性，有两部分组成，其中一个是内存中的工作日志持久缓冲区，是由innodb_log_buffer_size决定它的大小，另一个是重构日志文件，也就是我们在文件系统中看到的ib_logflie的相关文件。Undo Log实现了事务的原子性，在事务失败时进行回滚操作。Redo Log是顺序读写的，Undo Log是随机读写的，如果可以的话可以将数据存储在固态硬盘中，以提高性能。
• Innodb支持行级锁
  行级锁和表级锁是不一样的，行级锁的特点就是可以最大程度的支持并发，行级锁是由存储引擎层实现的。

5.2.3 Innodb状态检查

可以使用以下命令对Innodb状态进行检查：
show engine innodb status 이 매개변수를 보는 명령은 다음과 같습니다.

show Variables like 'innodb_file_per_table';

이 매개변수를 수정하는 명령은 다음과 같습니다. set global innodb_file_per_table=off;

5.2.1 시스템 테이블스페이스와 독립 테이블스페이스 선택 방법

비교:

시스템 테이블스페이스
• 독립 테이블스페이스

단순히 파일 크기를 줄이는 것은 불가능하다

테이블 최적화 명령을 전달하면 시스템 파일이 축소됩니다.
• IO 병목 현상이 발생합니다.

동시에 여러 파일로 데이터를 새로 고칠 수 있습니다.

권장 사항: 원래 시스템 테이블스페이스에 존재했던 테이블을 독립 테이블스페이스로 옮기는 방식. 단계:

다시 가져오기 data ol>5.2.2 Innodb 스토리지 엔진의 특징Innodb는 트랜잭션 스토리지 엔진입니다트랜잭션의 ACID 특성(이전에 소개한 원자성, 일관성 등)을 완벽하게 지원합니다

	Innodb에 독립 테이블스페이스 사용
mysqldump를 사용하여 모든 데이터베이스 테이블 데이터 내보내기	MySQL 서비스 중지, 매개변수 수정 및 Innodb 관련 파일 삭제	MySQL 서비스 다시 시작, Innodb 시스템 테이블 공간 재구축

🎜Redo 로그 및 Undo Log🎜 Redo Log는 트랜잭션 지속성을 구현하며 두 부분으로 구성됩니다. 하나는 innodb_log_buffer_size에 의해 크기가 결정되는 메모리의 작업 로그 영구 버퍼이고, 다른 하나는 파일 시스템에서 수행하는 재구성된 로그 파일입니다. ib_logflie의 파일은 . Undo Log는 트랜잭션의 원자성을 인식하고 트랜잭션이 실패할 경우 롤백 작업을 수행합니다. Redo Log는 순차적으로 읽고 기록되며, Undo Log는 무작위로 읽고 기록됩니다. 가능하면 SSD에 데이터를 저장하여 성능을 향상시킬 수 있습니다. 🎜🎜Innodb는 행 수준 잠금을 지원합니다.🎜 행 수준 잠금과 테이블 수준 잠금은 스토리지 엔진 계층에서 최대한 동시성을 지원할 수 있다는 것이 특징입니다. . 🎜🎜🎜🎜5.2.3 Innodb 상태 확인🎜🎜🎜다음 명령을 사용하여 Innodb 상태를 확인할 수 있습니다. 🎜show Engine innodb status🎜🎜🎜5.2.4 적용 가능한 시나리오🎜🎜🎜Innodb가 적합합니다. 대규모 애플리케이션의 경우 대부분의 OLTP 애플리케이션은 mysql5.7 버전 이후 Innodb가 이미 전체 텍스트 인덱스 및 공간 기능을 지원하기 때문입니다. 🎜🎜🎜5.2.4 (확장) 자물쇠란 무엇인가요🎜🎜🎜🎜5.2.3.1 자물쇠란 무엇인가요? 🎜🎜🎜🎜잠금의 주요 기능은 공유 리소스에 대한 동시 액세스를 관리하는 것입니다.🎜🎜잠금은 트랜잭션 격리를 달성하는 데 사용됩니다.🎜🎜🎜🎜5.2.3.2 잠금 유형: 🎜🎜🎜🎜공유 잠금(읽기 잠금이라고도 함)🎜 🎜 배타적 잠금(쓰기 잠금이라고도 함)🎜🎜🎜🎜5.2.3.3 쓰기 잠금과 읽기 잠금 간의 호환성 관계(한 행에 대한 호환성)🎜🎜🎜🎜🎜🎜🎜🎜🎜쓰기 잠금🎜🎜읽기 잠금🎜🎜🎜🎜 🎜 🎜쓰기 잠금🎜🎜호환 안 됨🎜🎜호환 안 됨🎜🎜🎜🎜읽기 잠금🎜🎜호환 안 됨🎜🎜호환🎜🎜🎜🎜

실제 상황에서는 결과가 위 표의 결과와 다를 수 있습니다. 주로 Innodb의 잠금 메커니즘이 매우 복잡하고 최종 결과에 영향을 미치는 잠금이 많기 때문입니다.

5.2.3.4 잠금 세분성:

• 테이블 수준 잠금
• 행 수준 잠금

5.2.3.5 차단 및 교착 상태

차단: 차단은 서로 다른 잠금 간의 호환성 관계로 인해 발생합니다. 어떤 시점에서는 한 트랜잭션의 잠금이 다른 트랜잭션의 잠금이 해제될 때까지 기다려야 하며, 이 잠금이 차지하는 리소스는 블록을 형성합니다.
교착 상태(Deadlock): 교착 상태란 트랜잭션 실행 중에 두 개 이상의 트랜잭션이 상대방이 기다리고 있는 리소스를 점유할 때 발생하는 예외를 말합니다. 정의에서 볼 수 있듯이, 차단된 트랜잭션이 여러 개일 때 차단된 트랜잭션이 기다리고 있는 자원을 점유하는 경우를 말하며, 차단된 트랜잭션이 여러 개일 때 서로가 기다리고 있는 리소스를 점유하는 경우를 교착상태(deadlock)라고 합니다.

5.3 CSV 저장 엔진

CSV 저장 엔진은 csv 파일을 mysql 테이블 파일로 처리할 수 있습니다. 이 저장 엔진의 저장 형식은 csv 저장 엔진의 데이터 저장 방법이 매우 독특합니다. MyISAM 또는 Innodb에 테이블을 저장하면 이 두 파일은 바이너리 형식으로 저장되지만 CSV 저장 엔진은 텍스트가 파일에 저장되기 때문에 데이터 파일을 직접 볼 수 없습니다. 즉, more와 같은 파일을 보는 명령을 통해 볼 수 있고, CSV 파일의 형식과 요구 사항을 충족하는 한 vi 명령을 사용하여 CSV 저장소 엔진의 테이블을 보고 편집할 수 있습니다. 데이터 손상에 대해 걱정할 필요가 없습니다.
mysql에서 CSV 스토리지 엔진 테이블을 생성하면 3가지 파일 시스템의 파일을 볼 수 있어야 합니다. 이 세 가지 파일 이름은 모두 테이블 이름을 기반으로 하지만 각각 접미사로 csv, csm 및 frm이 있습니다. csv 파일은 CSV 저장소 엔진의 데이터 파일입니다. csm 파일은 테이블의 메타데이터와 테이블 상태, 데이터 볼륨을 저장합니다. FRM 파일은 테이블 구조 정보를 저장합니다.

5.3.1 CSV 저장 엔진의 특징

• 가장 큰 특징은 CSV 형식으로 데이터를 저장한다는 점
  CSV의 각 열은 , 로 구분되며, 텍스트의 내용이 동봉되어 있습니다. 아래와 같이 큰따옴표로 묶습니다. ,来分隔的，并且文本的内容是以双引号来引起来的，如下图所示：
  
• 所有列必须都是不能为NULL的
  在建表的时候所有的列都必须是非空的，不能存储为NULL的值
• 不支持索引
  不适合大表，不适合在线处理
• 可以对数据文件直接编辑
  保存文本文件内容

5.3.2 CSV存储引擎的适用场景

CSV存储引擎适合作为数据交换的中间表

5.4 Archive存储引擎

5.4.1 文件系统存储特点

Archive存储引擎会缓存所有的写，并且利用zlib对插入的行进行压缩，因此Archive存储引擎相对于MyISAM存储引擎的表更加节省磁盘I/O，对于同样数量级的数据，Archive存储引擎相对于MyISAM和Innodb更加节省存储空间。一个几T的Innodb的表存储在Archive存储引擎当中，可能只需要几百兆的存储空间。
Archive存储引擎的表的数据是以ARZ为后缀的一个文件，和其他引擎一样，也存在一个以frm为后缀的系统文件，用于存储表的结构信息。

5.4.2 Archive存储引擎的特点

• 只支持insert和select
• 모든 열은 NULL이 아니어야 합니다.
테이블을 생성할 때 모든 열은 Null이 아니어야 하며 NULL 값으로 저장할 수 없습니다.

인덱스를 지원하지 않습니다. 대규모 테이블에는 적합하지 않습니다.

데이터 파일을 직접 편집할 수 있습니다

텍스트 파일 내용 저장

5.3.2 CSV 스토리지 엔진의 적용 시나리오

CSV 스토리지 엔진은 데이터 교환을 위한 중간 테이블로 적합합니다mysql 튜토리얼(동영상)

위 내용은 빅데이터 학습 2부 MYSQL 고급의 상세 내용입니다. 자세한 내용은 PHP 중국어 웹사이트의 기타 관련 기사를 참조하세요!