머리말:

이 장에서는 MySQL 서버 아키텍처, 다양한 스토리지 엔진 간의 주요 차이점, 차이점의 중요성을 간략하게 설명합니다.

MySQL의 역사적 배경과 벤치마크를 검토하고, 간단한 세부 사항과 데모 사례

텍스트 ：

MySQL 아키텍처는 다양한 시나리오에 적용될 수 있으며 데이터 웨어하우스, 콘텐츠 인덱스, 배포 소프트웨어, 고가용성 중복 시스템, 온라인 트랜잭션 처리 시스템 등을 지원하는 응용 프로그램에 포함될 수 있습니다.

MySQL의 가장 중요한 기능은 쿼리 처리 및 기타 시스템 작업을 데이터 저장 및 검색과 분리하는 스토리지 엔진 아키텍처입니다.

1.1 MySQL 논리 아키텍처

[MySQL 데이터베이스] 1장: MySQL 아키텍처와 역사의 해석

1.2 동시성 제어

잠금 세분성:

잠금 전략: 오버헤드와 데이터 잠금 보안의 균형을 유지하면서 각 스토리지 엔진은 지정된 잠금 전략과 세분성을 구현할 수 있습니다.

테이블 잠금: 테이블 잠금 전체 테이블을 잠그는 가장 기본적이고 최소한의 오버헤드

행 수준 잠금: 행 잠금 동시성에 대한 최대 지원 가장 큰 잠금 오버헤드는 스토리지에 있습니다. 엔진 계층은 (자체 방식으로)

1.3 트랜잭션

독립적인 작업 단위, 원자 SQL 쿼리 세트

격리 수준:

4개, 각각 규정 트랜잭션 수정, 낮은 격리로 더 높은 동시성 및 낮은 오버헤드 실행 가능

READ UNCOMMITTED 커밋되지 않은 읽기

트랜잭션의 수정 사항은 시간 내에 커밋되지 않으며 트랜잭션이 커밋되지 않은 데이터를 읽을 수도 있습니다. dirty read; 매우 적게 사용

READ COMMITTED

거의 데이터베이스 기본 격리 수준, 트랜잭션 시작부터 끝까지 커밋된 트랜잭션에 의해 이루어진 수정 사항만 표시됩니다. 비반복 읽기 : 동일한 쿼리를 두 번 실행하면 결과가 다를 수 있습니다(다른 트랜잭션의 수정)

REPEATABLE READ 반복 읽기

: 거래할 때 특정 범위의 레코드를 읽는 중이고 다른 트랜잭션이 해당 범위에 새 레코드를 삽입하고 현재 트랜잭션이 해당 범위의 레코드를 다시 읽습니다. 팬텀 행 SERIALIZABLE: 직렬화 가능 최적화

최고 수준으로, 트랜잭션 강제 실행 순차적으로 실행됨, 팬텀 읽기 문제 방지, 읽을 때 데이터의 각 행 잠김(많은 시간 초과 및 잠금 경합이 발생할 수 있음), 거의 사용되지 않음

deadlock [MySQL 데이터베이스] 1장: MySQL 아키텍처와 역사의 해석

1, 두 개의 여러 트랜잭션이 동시에 서로 점유

2. 여러 트랜잭션이 서로 다른 순서로 리소스를 잠그려고 시도하여 교착 상태가 발생할 수 있습니다.

3. 여러 트랜잭션이 동시에 동일한 리소스를 잠급니다. 잠금의 동작과 순서는 동일한 명령문 실행 순서로 일부 스토리지 엔진에서는 교착 상태가 발생하고 일부는 발생하지 않습니다.

교착 상태가 발생하는 두 가지 이유: 실제 데이터 충돌로 인해(하드 피함)

교착 상태가 전송된 후 트랜잭션 중 하나를 부분적으로 또는 완전히 롤백해야만 교착 상태를 해결할 수 있습니다. InnoDB는 최소 행 수준 독점 잠금을 보유한 트랜잭션을 롤백합니다. 1.3 .4 MySQL의 트랜잭션: 스토리지 엔진 구현

MySQL에는 InnoDB, NDB 클러스터라는 두 가지 트랜잭션 스토리지 엔진이 있습니다.

자동 제출 AUTOCOMMIT

기본값은 자동 제출 모드입니다. 커밋 작업을 수행하는 트랜잭션으로 간주됩니다. AUTOCOMMIT 변수 =1 =ON 및 비활성화 =0 =OFF를 통해 활성화할 수 있습니다(트랜잭션이 종료되고 새 트랜잭션이 발생할 때까지 모든 쿼리가 하나의 트랜잭션에 있음). 이 변수를 비트랜잭션 테이블로 수정하면 아무런 영향이 없습니다.

MySQL은 트랜잭션 격리 수준 설정을 통해 격리 수준을 설정할 수 있습니다. 새 수준은 다음 트랜잭션이 시작될 때 적용됩니다. 라이브러리 또는 현재 세션의 격리 수준만 변경할 수 있습니다

set session transaction isolation level read committed;

로그인 후 복사

제안: 사용되는 스토리지 엔진에 관계없이 언제든지 LOCK TABLES를 명시적으로 실행하지 마세요

1.4 다중 버전 동시성 제어 MVCC

데이터베이스 MySQL, Oracle, postgresql 등은 모두 MVCC를 구현하며 각각의 구현 메커니즘이 다릅니다. [출처]

MVCC: 데이터베이스에 연결된 각 리더는 특정 순간에 데이터베이스의

스냅샷

을 보고 쓰기 작업은 보이지 않습니다. [출처]

업데이트할 때 기존 데이터를 오래된 것으로 표시하고 다른 곳에서 데이터의 새 버전을 추가하여(여러 버전의 데이터 중 하나만 최신 버전임) 이전 데이터를 읽을 수 있도록 합니다

특징:

1. 데이터의 각 행에는 버전이 있으며, 이 버전은 데이터가 업데이트될 때마다 업데이트됩니다.

2 수정 시 각 트랜잭션을 방해하지 않고 마음대로 수정합니다.

3、保存时比较版本号，成功commit则覆盖原纪录，失败则放弃rollback

4、只在REPEATABLE READ 和READ COMMITTED两个隔离级别下工作

1.5MySQL存储引擎

mysql将每个数据库保存位数据目录下的一个子目录，创建表示，mysql在子目录下创建与表同名的.frm文件保存表的定义，不同存储引擎保存数据和索引的方式不同，但表的定义在MySQL服务层同一处理；

InnoDB:默认事务型引擎、最重要、广泛使用

处理大量短期事务；其性能和自动崩溃恢复特性、非事务型存储的需求中也很流行

数据存储在由InnoDB管理的表空间中，由一系列数据文件组成；

使用MVCC支持高并发，并实现了四个标准的隔离级别，默认是REPEATABLE READ可重复读，通过间隙锁next-key locking防止幻读，间隙锁使得InnoDB锁定查询设计的行还锁定索引中的间隙防止唤影行；

间隙锁：

当使用范围条件并请求锁时，InnoDB给符合条件的已有数据记录的索引项加锁，对应键值在条件范围内但是不存在的记录（间隙）加锁，间隙锁:【源】

//如emp表中有101条记录，其empid的值分别是 1,2,...,100,101
Select * from  emp where empid > 100 for update;

로그인 후 복사

InnoDB对符合条件的empid值为101的记录加锁，也会对empid大于101（这些记录并不存在）的“间隙”加锁；

1、上面的例子，如果不使用间隙锁，如果其他事务插入大于100的记录，本事务再次执行则幻读，但是会造成锁等待，在并发插入比较多时、要尽量优化业务逻辑，使用相等条件来访问更新数据，避免使用范围条件；

2、在使用相等条件请求给一个不存在的记录加锁时，也会使用间隙锁，当我们通过参数删除一条记录时，如果参数在数据库中不存在，库会扫描索引，发现不存在，delete语句获得一个间隙锁，库向左扫描扫到第一个比给定参数小的值，向右扫描到第一个比给定参数大的值，构建一个区间，锁住整个区间内数据；【源】

1.5.2MyIsSAM存储引擎

全文索引、压缩、空间函数，不支持事务和行级锁，崩溃后无法安全恢复

存储：

将表存储在两个文件中：数据.MYD、索引文件.MYI

表可以包含动态或静态（长度固定）行，MySQL据表定义来决定采用何种行格式

表如是变长行，默认配置只能处理256TB数据（指向记录的指针长度6字节），改变表指针长度，修改表的MAX_ROWS和AVG_ROW_LENGTH，两者相乘=表可到达的max大小，修改会导致重建整个表、表all索引；

特性：

1、对整张表加锁，读、共享锁，写、排他锁，但在读的同时可从表中插入新记录：并发插入

2、修复：可手工、自动执行检查和修复操作，CHECK TABLE mytable检查表错误，REPAIR TABLE mytable进行修复，执行修复可能会丢失些数据，如果服务器关闭，myisamchk命令行根据检查和修复操作；

3、索引特性：支持全文索引，基于分词创建的索引，支持复杂查询

4、延迟更新索引键Delayed Key Write,如果指定了DELAY_KEY_WRITE选项，每次修改完，不会立即将修改的索引数据写入磁盘，写入到内存的键缓冲区，清理此区或关闭表时将对应的索引块写入到磁盘，提升写性能，但是在库或主机崩溃时造成索引损坏、需要执行修复操作

压缩表：

表在创建并导入数据后，不再修改，比较适合，可使用myisampack对MyISAM表压缩（打包），压缩表不能修改（除非先解除压缩、修改数据、再次压缩）；减少磁盘空间占用、磁盘IO，提升查询性能，也支持只读索引；

现在的硬件能力，读取压缩表数据时解压的开销不大，减少IO带来的好处大得多，压缩时表记录独立压缩，读取单行时不需要解压整个表

性能：

设计简单，紧密格式存储；典型的性能问题是表锁的问题，长期处于locked状态：找表锁

1.5.3内建的其他存储引擎

Archive：适合日志和数据采集类应用，针对高速插入和压缩优化，支持行级锁和专业缓存区，缓存写利用zlib压缩插入的行，select扫描全表；

Blackhole：复制架构和日志审核，其服务器记录blackhole表日志，可复制数据到备库日志；

CSV：数据交换机制，将CSV文件作为MySQL表来处理，不支持索引；

Federated：访问其他MySQL服务器的代理，创建远程mysql的客户端连接将查询传输到远程服务器执行，提取发送需要的数据，默认禁用；

Memory：快速访问不会被修改的数据，数据保存在内存、不IO，表结构重启后还在但数据没了

1、查找或映射表，2、缓存周期性聚合数据， 3、保存数据分析中产生的中间数据

支持hash索引，表级锁，查找快并发写入性能低，不支持BLOB/TEXT类型的列，每行长度固定，内存浪费

Merge：myisam变种，多个myisam合并的虚拟表

NDB集群引擎：

1.5.4第三方存储引擎

OLTP类:

XtraDB基于InnoDB改进，性能、可测量性、操作灵活

PBXT：ACID/MVCC，引擎级别的复制、外键约束，较复杂架构对固态存储SSD适当支持，较大值类型BLOB优化

TokuDB：大数据，高压缩比，大数据量创大量索引

RethinkDB:固态存储

面向列的

列单独存储，压缩效率高

Infobright：大数据量，数据分析、仓库应用设计的，高度压缩，按照块（一组元数据）排序；块结构准索引，不支持索引（量大索引也没用），如查询无法再存储层使用面向列的模式执行，则需要在服务器层转换成按行处理

社区存储引擎：***

1.5.5选择合适的引擎

除非需要用到某些InnoDB不具备的特性，且无办法可以替代，否则优先选择InnoDB引擎

不要混合使用多种存储引擎，如果需要不同的存储引擎：

1、事务：需要事务支出，InnoDB XtraDB；不需要主要是select insert 那MyISAM

2、备份：定期关闭服务器来执行备份，该因素可忽略；在线热备份，InnoDB

3、崩溃恢复：数据量较大，MyISAM崩后损坏概率比InnoDB高很多、恢复速度慢

4、持有的特性：

1.5.6转换表的引擎

ALTER TABLE:最简单

ALTER TABLE mytable ENGINE=InnoDB

此会执行很长时间，MySQL按行将数据从原表复制到新表中，在复制期间可能会消耗掉系统all的I/O能力，同时原表上加读锁；会失去和原引擎相关的all特性

导出与导入：

mysqldump工具将数据导出到文件，修改文件中CREATE_TABLE语句的存储引擎选项，同时修改表名（同一个库不能存在相同的表名），mysqldump默认会自动在CREATE_TABLE语句前加上DROP TABLE语句

创建与查询：CREATE SELECT

综合上述两种方法：先建新存储引擎表，利用INSERT……SELECT语法导数

CREATE TABLE innodb_table LIKE myisam_table
ALTER TABLE innodb_table ENGINE=InnoDB;
INSERT INTO innodb_table SELECT * FROM myisam_table;
数据量大的话，分批处理（放事务中）

로그인 후 복사