[MySQL-Datenbank] Interpretation von Kapitel 1: MySQL-Architektur und -Geschichte

Vorwort:

Dieses Kapitel beschreibt kurz die MySQL-Serverarchitektur, die Hauptunterschiede zwischen verschiedenen Speicher-Engines und die Bedeutung der Unterschiede

Gibt einen Überblick über den historischen Hintergrund von MySQL, Benchmark testet und vereinfacht Details und Demonstrationsfälle zur Diskussion der Prinzipien von MySQL

Text:

Die MySQL-Architektur kann in einer Vielzahl verschiedener Szenarien angewendet werden, kann in Anwendungen eingebettet werden und unterstützt Data Warehouses , Inhaltsindizes und Bereitstellungssoftware, hochverfügbare redundante Systeme, Online-Transaktionsverarbeitungssysteme usw.;

Das wichtigste Merkmal von MySQL ist seine Speicher-Engine-Architektur, die die Abfrageverarbeitung und andere Systemaufgaben von Daten trennt Speicherung und Abruf;

1.1Logische MySQL-Architektur

1.2 Parallelitätskontrolle

Sperrgranularität:

Sperrstrategie: Suche nach einem Gleichgewicht zwischen Sperraufwand und Datensicherheit Ausgewogen, jede Speicher-Engine kann eine bestimmte Sperrstrategie und Granularität implementieren

Tabellensperre: Tabellensperre, die einfachste und minimale Kosten zum Sperren der gesamten Tabelle

Sperre auf Zeilenebene: Zeilensperre, maximale Unterstützung für Parallelität, maximaler Sperr-Overhead wird auf der Ebene der Speicher-Engine implementiert (auf ihre eigene Weise)

1.3 Transaktionen

Unabhängige Arbeitseinheit, a Satz atomarer SQL-Abfragen

Isolationsstufe:

Vier Typen, jeder legt die in der Transaktion vorgenommenen Änderungen fest. Eine geringere Isolation kann zu einer höheren Parallelität und einem geringeren Overhead führen

READ UNCOMMITTED uncommitted read

Die Änderungen in der Transaktion werden nicht rechtzeitig festgeschrieben und sind auch für andere Transaktionen sichtbar. Die Transaktion liest nicht festgeschriebene Daten: Dirty Read; selten verwendet

READ COMMITTED-Übermittlung Lesen

fast Datenbank-Standard-Isolationsstufe, nicht-MySQL vom Anfang bis zum Ende einer Transaktion, nur die von der übermittelten Transaktion vorgenommenen Änderungen sind sichtbar , und die vorgenommenen Änderungen sind für andere Transaktionen nicht sichtbar; ist nicht wiederholbar. Lesen Sie : Führen Sie dieselbe Abfrage zweimal aus. Die Ergebnisse können unterschiedlich sein (Änderungen anderer Transaktionen)

WIEDERHOLBARES LESEN Wiederholbares Lesen

MySQL-Standard, gelöst Dirty Read, dieselbe Transaktion liest dasselbe Ergebnis mehrmals Phantom Read : Wenn eine Transaktion Datensätze in einem bestimmten Bereich liest , eine andere Transaktion fügt ein neues in die Bereichsdatensätze ein, die aktuelle Transaktion liest den Bereich der Datensätze erneut, Phantomzeilen

SERIALIZABLE: Serialisierbar

am höchsten, erzwingt die serielle Ausführung von Transaktionen, um Phantomleseprobleme zu vermeiden, Sperre beim Lesen jeder Datenzeile (kann viele Zeitüberschreitungen und Sperrkonflikte verursachen), selten verwendet

Deadlock

1. Zwei mehrere Transaktionen belegen einander auf derselben Ressource und fordern die gegenseitige Sperrung der Ressourcen auf

2

3. Mehrere Transaktionen sperren gleichzeitig dieselbe Ressource.

Beim Ausführen von Anweisungen in derselben Reihenfolge Speicher-Engines erzeugen Deadlocks, einige jedoch nicht. Deadlocks haben zwei Gründe: aufgrund realer Datenkonflikte (schwer zu vermeiden) und aufgrund der Implementierung der Speicher-Engine Wenn der Deadlock gesendet wird, kann der Deadlock nur durch teilweises oder vollständiges Zurücksetzen einer der Transaktionen aufgehoben werden: InnoDB setzt die Transaktion zurück, die die minimale exklusive Sperre auf Zeilenebene enthält.

1.3.4 Transaktionen in MySQL : Speicher-Engine-Implementierung

MySQL verfügt über zwei Transaktionsspeicher-Engines: InnoDB und NDB-Cluster

AUTOCOMMIT automatisch festschreiben

Der automatische Festschreibungsmodus wird standardmäßig übernommen Starten Sie explizit eine Transaktion. Jede Abfrage wird als Transaktion behandelt, um eine Festschreibungsoperation auszuführen. Sie kann über die AUTOCOMMIT-Variable =1 =ON, deaktiviert =0 =OFF aktiviert werden (alle Abfragen befinden sich in einer Transaktion bis zum expliziten Festschreibungs-Rollback). Die Transaktion endet und gleichzeitig wird eine neue Transaktion gestartet.

MySQL kann die Isolationsstufe festlegen. Die neue Stufe wird wirksam Die nächste Transaktion wird gestartet. Die Konfigurationsdatei legt die gesamte Bibliothek fest.

set session transaction isolation level read committed;

Empfehlung: Führen Sie LOCK TABLES zu keinem Zeitpunkt aus, egal um welche Speicher-Engine es sich handelt verwendet

1.4 Multi-Version Concurrency Control MVCC

Datenbanken MySQL, Oracle, Postgresql usw. implementieren alle MVCC, und ihre jeweiligen Implementierungsmechanismen sind unterschiedlich [Quelle]

MVCC : Jeder mit der Datenbank verbundene Leser sieht zu einem bestimmten Zeitpunkt einen

Snapshot

der Datenbank, und der Schreibvorgang ist für die Außenwelt nicht sichtbar, bevor er festgeschrieben wird.

Bei der Aktualisierung Markieren Sie die alten Daten als veraltet und fügen Sie an anderer Stelle eine neue Datenversion hinzu (mehrere Datenversionen, nur eine ist die neueste), sodass frühere Daten gelesen werden können

Funktionen:

1. Jede Datenzeile hat eine Version, die jedes Mal aktualisiert wird, wenn die Daten aktualisiert werden.2. Kopieren Sie beim Ändern die aktuelle Version und ändern Sie sie nach Belieben, ohne die Transaktionen zu beeinträchtigen.

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;
数据量大的话，分批处理（放事务中）

1.6MySQL时间线Timeline

 早期MySQL破坏性创新，有诸多限制，且很多功能只能说是二流的，但特性支持和较低的使用成本，使受欢迎；5.x早起引入视图、存储过程等，期望成为“企业级”数据库，但不算成功，5.5显著改善

1.7MySQL开发模式

遵循GPL开源协议，全部源代码开发给社区，部分插件收费；

1.8总结

mysql分层架构，上层是服务器层的访问和查询执行引擎，下层存储引擎（最重要）

相关文章：

【MySQL数据库】第二章解读：MySQL基准测试

【MySQL数据库】第三章解读：服务器性能剖析（上）

Das obige ist der detaillierte Inhalt von[MySQL-Datenbank] Interpretation von Kapitel 1: MySQL-Architektur und -Geschichte. Für weitere Informationen folgen Sie bitte anderen verwandten Artikeln auf der PHP chinesischen Website!