mysql Myisam Innodb
Mysql版本：5.6.20
Msql整体架构图
-
Connectors指的是不同语言中与SQL的交互。
-
Management Serveices & Utilities： 系统管理和控制工具。
-
Connection Pool： 连接池。管理缓冲用户连接，线程处理等需要缓存的需求。
-
SQL Interface： SQL接口。接受用户的SQL命令，并且返回用户需要查询的结果。比如select from就是调用SQL Interface。
-
Parser：解析器。SQL命令传递到解析器的时候会被解析器验证和解析。解析器是由Lex和YACC实现的，是一个很长的脚本。主要功能：
将SQL语句分解成数据结构，并将这个结构传递到后续步骤，以后SQL语句的传递和处理就是基于这个结构的。
如果在分解构成中遇到错误，那么就说明这个sql语句是不合理的。 -
Optimizer: 查询优化器。
SQL语句在查询之前会使用查询优化器对查询进行优化。他使用的是“选取-投影-联接”策略进行查询。用一个例子就可以理解： select uid,name from user where gender = 1;
这个select 查询先根据where 语句进行选取，而不是先将表全部查询出来以后再进行gender过滤。这个select查询先根据uid和name进行属性投影，而不是将属性全部取出以后再进行过滤，将这两个查询条件联接起来生成最终查询结果。 -
Cache和Buffer： 查询缓存。
如果查询缓存有命中的查询结果，查询语句就可以直接去查询缓存中取数据。这个缓存机制是由一系列小缓存组成的。比如表缓存，记录缓存，key缓存，权限缓存等。 -
Engine ：存储引擎。
存储引擎是MySql中具体的与文件打交道的子系统。也是Mysql最具有特色的一个地方。
Mysql的存储引擎是插件式的。它根据MySql AB公司提供的文件访问层的一个抽象接口来定制一种文件访问机制（这种访问机制就叫存储引擎）。现在有很多种存储引擎，各个存储引擎的优势各不一样，最常用的InnoDB,BDB,MyISAM,。
默认下MySql是使用 Innodb 引擎( mysql 5.5.5以前的版本默认存储引擎是 Myisam )，它查询速度快，有较好的索引优化和数据压缩技术。但是它支持事务。
官网直译：MySQL 8.0支持的存储引擎
InnoDB ：MySQL 8.0中的默认存储引擎。 InnoDB是一种适用于MySQL的事务安全（ACID兼容）存储引擎，具有提交，回滚和崩溃恢复功能，可保护用户数据。 InnoDB行级锁定（没有升级到更粗略的粒度锁）和Oracle风格的一致非锁定读取增加了多用户并发性和性能。 InnoDB将用户数据存储在聚簇索引中，以减少基于主键的常见查询的I / O. 为了保持数据完整性， InnoDB还支持FOREIGN KEY参照完整性约束。
MyISAM ：这些表占地面积小。 表级锁定限制了读/写工作负载的性能，因此它通常用于Web和数据仓库配置中的只读或大部分读取工作负载。
Memory ：将所有数据存储在RAM中，以便在需要快速查找非关键数据的环境中快速访问。 该发动机以前称为HEAP发动机。 其用例正在减少; InnoDB及其缓冲池内存区域提供了一种通用且持久的方式来将大部分或全部数据保存在内存中， NDBCLUSTER为大型分布式数据集提供快速键值查找。
CSV ：它的表格实际上是逗号分隔值的文本文件。 CSV表允许您以CSV格式导入或转储数据，以便与读取和写入相同格式的脚本和应用程序交换数据。 由于CSV表未编制索引，因此通常在正常操作期间将数据保留在InnoDB表中，并且仅在导入或导出阶段使用CSV表。
Archive(档案、文件)：这些紧凑的无索引表用于存储和检索大量很少引用的历史，存档或安全审计信息。
Blackhole(黑洞) ：Blackhole存储引擎接受但不存储数据，类似于Unix /dev/null设备。 查询始终返回空集。 这些表可用于将DML语句发送到从属服务器的复制配置，但主服务器不保留其自己的数据副本。
NDB(NDB集群)：此集群数据库引擎特别适用于需要尽可能高的正常运行时间和可用性的应用程序。
Merge(合并) ：允许MySQL DBA或开发人员对一系列相同的MyISAM表进行逻辑分组，并将它们作为一个对象引用。 适用于数据仓库等VLDB环境。
Federated(联邦)：提供链接单独的MySQL服务器以从许多物理服务器创建一个逻辑数据库的功能。 非常适合分布式或数据集市环境。
Example ：此引擎用作MySQL源代码中的示例，说明如何开始编写新的存储引擎。 它主要是开发人员感兴趣的。 存储引擎是一个什么都不做的“ 存根 ” 。 您可以使用此引擎创建表，但不能在其中存储数据或从中检索数据。
Innodb文件类型
b.frm ：描述表结构文件，字段长度等
如果采用独立表存储模式，data目录中还会产生b.ibd文件（存储数据信息和索引信息）
如果采用共存储模式的，数据信息和索引信息都存储在ibdata1中
Innodb存储结构图
Innodb特点
InnoDB：
- 支持事务安全的引擎
- 支持外键、行锁、事务是他的最大特点
- 如果有大量的update和insert，建议使用InnoDB，特别是针对多个并发和QPS较高的情况。
Innodb索引
Innodb 至少会创建一个索引，如果没有默认配置的索引，它会创建一个聚簇索引
InnoDB索引结构 B+树
B树
- 定义任意非叶子结点最多只有M个儿子，且M>2；
- 根结点的儿子数为[2, M]；
- 除根结点以外的非叶子结点的儿子数为[M/2, M]，向上取整；
- 非叶子结点的关键字个数=儿子数-1；
- 叶子结点的关键字个数=M-1；
如图是一个3阶b树，顺便讲一下查询元素5的过程：
█b树的插入删除元素操作：
比如我们要在下图中插入元素4,删除元素11：
B+树
- 有n棵子树的非叶子结点中含有n个关键字（b树是n-1个），这些关键字不保存数据，只用来索引，所有数据都保存在叶子节点（b树是每个关键字都保存数据）。
- 所有的叶子结点中包含了全部关键字的信息，及指向含这些关键字记录的指针，且叶子结点本身依关键字的大小自小而大顺序链接。
- 所有的非叶子结点可以看成是索引部分，结点中仅含其子树中的最大（或最小）关键字。
- 通常在b+树上有两个头指针，一个指向根结点，一个指向关键字最小的叶子结点。
- 同一个数字会在不同节点中重复出现，根节点的最大元素就是b+树的最大元素。
Innodb索引类型:
辅助索引：如果条件查询为辅助索引查询，需要先通过辅助索引B+树找到辅助索引，再通过辅助索引的书签找到聚集索引。因此，如果如果是一个三阶的索引树(不严谨)，需要磁盘I/O6次才能找到对应的行数据。
- Innodb索引构建方式:
对于辅助索引的创建，InnoDB存储引擎会对表加上一个S锁。在创建的过程中，不需要重建表，因此速度极快。但是在创建的过程中，由于上了S锁，因此创建的过程中该表只能进行读操作。删除辅助索引操作就更简单了，只需在InnoDB存储引擎的内部视图更新下，将辅助索引的空间标记为可用，并删除MySQL内部视图上对于该表的索引定义即可。
Innodb事务
-
原子性
ACID模型的原子性方面主要涉及InnoDB 事务 。 相关的MySQL功能包括：
自动提交设置。
COMMIT语句。
ROLLBACK声明。 -
一致性
ACID模型的一致性方面主要涉及内部InnoDB处理以保护数据免于崩溃。 相关的MySQL功能包括：
InnoDB 双写缓冲区 。
InnoDB 崩溃恢复 。 -
隔离性
ACID模型的隔离方面主要涉及InnoDB 事务 ，特别是适用于每个事务的隔离级别 。 相关的MySQL功能包括：
自动提交设置。
SET ISOLATION LEVEL语句。
InnoDB 锁定的低级细节。 在性能调优期间，您可以通过INFORMATION_SCHEMA表查看这些详细INFORMATION_SCHEMA 。 -
持久性
ACID模型的持久性方面涉及MySQL软件功能与您的特定硬件配置交互。 由于取决于CPU，网络和存储设备的功能的许多可能性，这方面是最复杂的提供具体指导方针。 （这些指南可能采取购买“ 新硬件 ”的形式。）相关的MySQL功能包括：
InnoDB 双写缓冲区 ，由innodb_doublewrite配置选项打开和关闭。
配置选项innodb_flush_log_at_trx_commit 。
配置选项sync_binlog 。
配置选项innodb_file_per_table 。
在存储设备中写入缓冲区，例如磁盘驱动器，SSD或RAID阵列。
存储设备中的电池备份缓存。
用于运行MySQL的操作系统，特别是它对fsync()系统调用的支持。
Innodb 事务隔离级别
| 事务隔离级别 | 脏读 | 不可重复读 | 幻读 |
|---|---|---|---|
| 读未提交（read-uncommitted） | 是 | 是 | 是 |
| 不可重复读 （read-committed） | 否 | 是 | 是 |
| 可重复读（repeatable-read） | 否 | 否 | 是 |
| 串行化（serializable） | 否 | 否 | 否 |
脏读：在一个事务中读取了另一个事务未提交的数据，并进一步更新
| 时间 | 事务A | 事务B |
|---|---|---|
| T1 | 开始事务 | |
| T2 | 开始事务 | |
| T3 | 查询账号余额100元 | |
| T4 | 取出50元，账号余额：50元 | |
| T5 | 查询账号余额：50元 | |
| T6 | 回滚事务 | |
| T7 | 汇入10元，此时账号余额：60元 | |
| T8 | 提交事务 |
不可重复读：在一个事务中读取了另一个事务提交前后的数据
幻读：在一个事务中读取了另一个事务已经提交的新增数据
| 时间 | 事务A | 事务B |
|---|---|---|
| T1 | 开始事务 | |
| T2 | 开始事务 | |
| T3 | 查询所有账号下总余额为500元 | |
| T4 | A用户注册了一个账号的同时汇入了100元 | |
| T5 | 查询所有账号下总余额为600元 | |
| T6 | 提交事务 |
丢失更新(双写)：在一个事务中覆盖了另一个事务更新的数据
| 时间 | 事务A | 事务B |
|---|---|---|
| T1 | 开始事务 | |
| T2 | 开始事务 | |
| T3 | A账号修改账号名称为张三 | |
| T4 | A账号修改账号名称为李四 | |
| T5 | 提交事务 | |
| T6 | 提交事务 |
MyISAM
特性：
1:不支持事务、不具备AICD特性(原子性、一致性、分离性、永久性);
2:表级别锁定形式(更新数据时锁定整个表、这样虽然可以让锁定的实现成本很小但是同时大大降低了其并发的性能)；
3:读写相互阻塞(不仅会在写入的时候阻塞读取、还会在读取的时候阻塞写入、但是读取不会阻塞读取);
4:只会缓存索引(myisam通过key_buffer_size来设置缓存索引，提高访问性能较少磁盘IO的压力、但是只缓存索引、不缓存数据);
5:读取速度快、占用资源比较少;
6:不支持外键约束、只支持全文检索；
7:是MySQL5.5.5版本之前的默认存储引擎；
扫一扫
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
