[Base de données MySQL] Interprétation du chapitre 1 : Architecture et historique de MySQL

Avant-propos :

Ce chapitre décrit brièvement l'architecture du serveur MySQL, les principales différences entre les différents moteurs de stockage et l'importance des différences

Passe en revue le contexte historique de MySQL, benchmark tests et simplifie Détails et cas de démonstration pour discuter des principes de MySQL

Texte :

L'architecture MySQL peut être appliquée dans une variété de scénarios différents, peut être intégrée dans des applications et prend en charge les entrepôts de données , index de contenu et logiciels de déploiement, systèmes redondants à haute disponibilité, systèmes de traitement des transactions en ligne, etc. ;

La caractéristique la plus importante de MySQL est son architecture de moteur de stockage, qui sépare le traitement des requêtes et les autres tâches système des données. stockage et récupération ;

1.1Architecture logique MySQL

1.2 Contrôle de concurrence

Granularité du verrouillage :

Stratégie de verrouillage : à la recherche d'un équilibre entre la surcharge de verrouillage et la sécurité des données Équilibré, chaque moteur de stockage peut mettre en œuvre une stratégie de verrouillage et une granularité spécifiées

Verrouillage de table : verrouillage de table, le coût le plus basique et le plus minimum pour verrouiller la table entière

Verrouillage au niveau des lignes : verrouillage des lignes, prise en charge maximale de la concurrence, surcharge de verrouillage maximale implémentée au niveau de la couche moteur de stockage (à sa manière)

1.3 Transactions

Unité de travail indépendante, une ensemble de requêtes SQL atomiques

Niveau d'isolement :

Quatre types, chacun stipulant les modifications apportées à la transaction, une isolation plus faible peut assurer une concurrence plus élevée et réduire les frais généraux

READ UNCOMMITTED lecture non validée

Les modifications dans la transaction ne sont pas validées dans le temps et sont également visibles par les autres transactions ; la transaction lit les données non validées : lecture sale ; >

LIRE la soumission COMMITTED Lire

presque le niveau d'isolement par défaut de la base de données, non-MySQL du début à la fin d'une transaction, seules les modifications apportées par la transaction soumise sont visibles ; , et les modifications apportées par elle-même ne sont pas visibles par les autres transactions ;

ne peut pas être répétée Lire : Exécutez deux fois la même requête, les résultats peuvent être différents (modifications d'autres transactions)

REPEATABLE READ Lecture répétable

MySQL par défaut, résolu Dirty read, la même transaction lit le même résultat plusieurs fois

Lecture fantôme  : lorsqu'une transaction lit des enregistrements dans une certaine plage ; , une autre transaction insère un nouveau dans les enregistrements de plage, la transaction en cours lit à nouveau les enregistrements de plage, lignes fantômes

SERIALIZABLE : sérialisable

le plus élevé , obligeant les transactions à être exécutées en série pour éviter les problèmes de lecture fantôme, se verrouille lors de la lecture de chaque ligne de données (peut provoquer de nombreux délais d'attente et conflits de verrouillage), rarement utilisé

impasse

1. Deux transactions multiples s'occupent mutuellement sur la même ressource et demandent de verrouiller les ressources occupées l'une par l'autre ;

2. impasse ;

3. Plusieurs transactions verrouillent la même ressource en même temps

Le comportement et l'ordre des verrous sont liés au moteur d'accès. Lors de l'exécution d'instructions dans le même ordre, une certaine quantité de stockage est nécessaire. les moteurs produiront des blocages et certains ne le feront pas ;

Deadlocks Double raison de génération de verrous : à cause de conflits de données réels (difficiles à éviter) et à cause de la mise en œuvre du moteur de stockage

Après le une impasse est envoyée, l'impasse ne peut être levée qu'en annulant partiellement ou complètement l'une des transactions : InnoDB annule la transaction qui détient le verrou exclusif minimum au niveau de la ligne

1.3.4 Transactions dans MySQL : Implémentation du moteur de stockage

MySQL dispose de deux moteurs de stockage transactionnels : InnoDB et NDB Cluster

Commettre automatiquement AUTOCOMMIT

Le mode de validation automatique est adopté par défaut si vous ne le faites pas explicitement. démarrez une transaction, chaque requête sera traitée comme une transaction pour effectuer une opération de validation. Elle peut être activée via la variable AUTOCOMMIT =1 =ON, désactivée =0 =OFF (toutes les requêtes sont dans une seule transaction jusqu'à l'annulation explicite de la validation) La transaction se termine et une nouvelle transaction démarre en même temps. La modification de cette variable n'a aucun impact sur les tables non transactionnelles ;

MySQL peut définir le niveau d'isolement en définissant le niveau d'isolement de la transaction. Le nouveau niveau prendra effet lors de la prochaine. La transaction démarre. Le fichier de configuration définit toute la bibliothèque. Vous pouvez également modifier uniquement le niveau d'isolement de la session en cours

set session transaction isolation level read committed;

Recommandation : peu importe le moment. N'exécutez pas explicitement LOCK TABLES, quel que soit le moteur de stockage. est utilisé

1.4 Contrôle d'accès concurrentiel multiversion MVCC

Les bases de données MySQL, Oracle, postgresql, etc. implémentent toutes MVCC, et leurs mécanismes d'implémentation sont différents [source]

MVCC : Chaque lecteur connecté à la base de données voit un

instantané de la base de données à un certain moment, et l'opération d'écriture n'est pas visible du monde extérieur avant d'être soumise

Lors de la mise à jour ; , marquez les anciennes données comme obsolètes et ajoutez une nouvelle version des données ailleurs (plusieurs versions de données, une seule est la dernière), permettant la lecture des données précédentes

Caractéristiques :

1. Chaque ligne de données a une version, qui est mise à jour à chaque fois que les données sont mises à jour

2 Lors de la modification, copiez la version actuelle et modifiez-la à volonté, sans interférer entre les transactions

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数据库】第三章解读：服务器性能剖析（上）

Ce qui précède est le contenu détaillé de. pour plus d'informations, suivez d'autres articles connexes sur le site Web de PHP en chinois!