MySQL 引擎

一 存储引擎概述

1. 为什么要合理选择数据库存储引擎

MySQL 中的数据用各种不同的技术存储在文件(或内存)中，这些技术中的每一种技术都使用不同的存储机制、索引技巧、锁定水平并且最终提供广泛的不同的功能和能力。通过选择不同的技术，你能够获得额外的速度或者功能，从而改善你的应用的整体功能。

这些不同的技术以及配套的相关功能在 MySQL 中被称作存储引擎(也叫表类型)。MySQL 默认配置了许多不同的存储引擎，可以预先设置或者在 MySQL 服务器中启用。你可以选择合适用于服务器、数据库和表格的存储引擎，以便在选择如何存储你的信息、如何检索这些信息以及你需要的数据结合什么性能和功能的时候为你提供最大的灵活性。

2. 定义

数据库引擎是用于存储、处理和保护数据的核心服务。利用数据库引擎可控制访问权限并快速处理事务，从而满足企业内大多数需要处理大量数据的应用程序的要求。使用数据库引擎创建用于联机事务处理或联机分析处理数据的关系数据库。这包括创建用于存储数据的表和用于查看、管理和保护数据安全的数据库对象(如索引、视图和存储过程)。

3. 存储引擎的作用

• 设计并创建数据库以保存系统所需的关系或 XML 文档。
• 实现系统以访问和更改数据库中存储的数据。包括实现网站或使用数据的应用程序，还包括生成使用 SQL Server 工具和使用工具以使用数据的过程。
• 为单位或客户部署实现的系统。
• 提供日常管理支持以优化数据库的性能。

二 MySQL 引擎

1. MySQL 支持的数据引擎(我使用的 MySQL 版本是 mysql-cluster-gpl-7.5.9-winx64)

通过下图可以看到存储引擎主要有： InnoDB，MyIsam，Memory ，后续我们主要分析这三个，其余感兴趣的自行百度。

2. 如何修改数据库引擎

• 修改配置文件 my.ini

未修改前如下图，需在 [mysqld] 后面添加 default-storage-engine=InnoDB，重启服务，数据库默认的引擎修改为 InnoDB。

• 在创建表时指定：

查看创建成功：

• 创建表之后更改

3. InnoDB

在MySQL 5. 5 及以后的版本用的是 InnoDB 作为默认引擎。

InnoDB 是一个事务型的存储引擎，有行级锁和外键约束。InnoDB 引擎提供了对数据库 ACID 事务的支持，并且实现了 SQL 标准的四种隔离级别，关于数据库事务以及隔离级别的内容请看数据库事务与其隔离级别这类型的文章。该引擎还提供了行级锁和外键约束，他的设计目标是处理大容量数据库系统，它本身其实就是基于 MySQL 后台的完整数据库系统，MySQL 运行时 InnoDB 会在内存中建立缓冲池，用于缓冲数据与索引。但是该引擎不支持 FULLTEXT 类型(全文搜索)的索引，它没有保存表的行数，在执行 select count(*) from talbel 时需要扫描全表。当需要使用数据库事务时，该引擎是首选。由于锁的粒度小，在进行写操作时不会锁定全表，所以在写操作并发较高时，使用 InnoDB 引擎会提升效率。但是使用行级锁也不是绝对的，如果在执行一个 SQL 语句时 MySQL 不能确认要扫描的范围，InnoDB 同样会锁全表。

补充：事务 ACID

A 事务的原子性(Atomicity)：指一个事务要么全部执行，要么不执行，即一个事务不可能只执行到一半就停止了。

C 事务的一致性(Consistency)：指事务的运行并不改变数据库中数据的一致性。例如，完整性约束了 x + y = 8，一个事务改变了 x ，那么 y 也应该随之改变。

I 事务的隔离性(Isolation)：指两个以上的事务不会出现交错执行的状态，因为交错执行可能会导致数据不一致。

D 事务的持久性(Durability)：指事务执行成功后，该事务对数据库所做的更改便是持久的保存在数据库之中，不会无缘无故的回滚。

适用场景

• 经常需要更新的表，适合处理多重并发的请求。
• 支持事务。
• 适用于大容量数据库系统，支持从灾难中恢复(通过 bin-log 日志等)。
• 外键约束。
• 支持自动增加列属性 auto_increment。

MySQL 官方对 InnoDB 的讲解

• InnoDB 给 MySQL 提供了具有提交、回滚和崩溃恢复能力的事务安全(ACID) 存储引擎
• InnoDB 锁定在行级并且也在 select 语句提供了一个 Oracle 风格一致的非锁定读，这些特色增加了多用户部署和性能。没有在 InnoDB 中扩大锁定的需要，因为在 InnoDB 中行级锁定非常适合非常小的空间。
• InnoDB 也支持 foreign key 强制。在 SQL 查询中，你可以自由地将 InnoDB 类型的表与其它 MySQL 的表的类型混合起来，甚至在同一个查询中也可以混合。
• InnoDB 是为处理巨大数据量时的最大性能设计的，它的 CPU 效率可能是任何其它基于磁盘的关系数据库引擎所不能匹敌的。
• InnoDB 被用来在众多需要高性能的大型数据库站点上产生。

4. MyISAM

在MySQL 5. 5版本以前用的是 MyISAM 作为默认引擎，它没有提供对数据库事务的支持，也不支持行级锁和外键，因此在进行 insert 或 update 操作时(写操作)会锁定整个表，导致效率偏低。MyISAM 引擎是保存了表的行数，在执行 select count(*) from table 时，可直接读取已经保存的值而不需要扫描全表。如果表的读操作远远多于写操作时，且不需要事务支持时，MyISAM 可作为数据引擎的首选。

引擎在创建表的时候，会创建三个文件，一个是 .frm 文件用于存储表的定义，一个是 .MYD 文件用于存储表的数据，另一个是 .MYI 文件用于存储索引。操作系统对大文件的操作是比较慢的，这样将表分为三个文件，那么 .MYD 这个文件单独存放数据自然可以优化数据库的查询等操作。有索引管理和字段管理，MyISAM 还使用一种表格锁定的机制，来优化多个并发的读写操作，其代价是需要经常运行 OPTIMIZE TABLE 命令来恢复被更新机制所浪费的空间，否则碎片会随之增加，最终影响数据访问性能。

补充：MyISAM 索引方法-索引顺序存取方法

• MyISAM 是一个定义明确且经历时间考验的数据表格管理方法，它在设计之时就考虑到数据库查询的次数要远大于更新的次数。
• MyISAM 执行读取操作的速度很快，而且不占用大量的内存和存储资源。
• 在设计之初就预想数据组织成有固定长度的记录，按顺序存储的。MyISAM 是一种静态索引结构。

适用场景

• MyISAM 存储引擎独立于操作系统，也就是可以在 Windows 上使用，也可以比较简单的将数据转移到 Linux 操作系统上去。
• 不支持事务的设计，但并不代表着有事务操作的项目不能使用 MyISAM 存储引擎，可以在 service 层根据自己的业务需求进行相应的控制。
• 不支持外键的表设计。
• 查询大量数据时速度很快，如果数据库 insert 和 update 的操作比较少的情况下比较适用。
• 进行大批量插入操作时执行速度也比较快。
• 对整张表进行加锁的场景。
• MyISAM 极度强调快速读取操作。
• MyISAM 中存储了表的行数，在执行 select count(*) from table 时只需要直接读取已经保存好的值而不需要进行全表扫描。如果表的读操作远远多于写操作且不需要数据库事务的支持，那么 MyISAM 是很好的选择。

缺点：

• 不支持事务处理。
• 不支持行级锁和外键。
• 不适用于经常 update 的表，效率低。
• 不能在表损坏后恢复数据(是不能主动恢复的)。不能容错，如果硬盘崩溃了，那么数据文件就无法恢复了。如果你把 MyISAM 用在关键任务应用程序里，那就必须经常备份所有的实时数据，通过其复制特性，MySQL 能够支持这样的备份应用程序。

5. Memory

也叫堆内存(heap) ，使用存在内存中的内容来创建表，每个 Memory 表只实际对应一个磁盘文件。Memory 类型的表访问非常的快，因为它的数据是放在内存中的，并且默认使用 hash 索引。但是一旦服务关闭，表中的数据就会丢失。Memory 允许只驻留在内存里的临时表格。驻留在内存里让 Memory 比 MyISAM 和 InnoDB 都快，但是它所管理的数据是不稳定的，如果在关机之前没有进行保存操作，那么所有的数据都会丢失。在删除数据行时，Memory 也不会浪费大量的空间。Memory 表格在需要使用 select 来选择和操控数据时非常有用。

适用场景

• 那些内容变化不频繁的代码表，或者作为统计操作的中间结果表，便于高效的在内存中进行结果分析并得到最终的统计结果。
• 目标数据比较小，而且非常频繁的进行访问，在内存中存放数据，数据量太大容易造成内存溢出，可以通过参数 max_heap_table_size 控制 Memory 表的大小。
• 数据是临时的，而且必须立即需用到，那么就可以放在内存中。
• 存储在 Memory 表中的数据突然丢失也没有太大关系。

补充

• 要求存储的数据是数据长度不变的格式，如 Blob 和 Text 类型的数据是不可用的(长度不固定)。
• 在用完表格后就删除表格。
• Memory 同时支持散列索引和 B 树索引，B 树索引可以使用部分查询和通配查询，也可以用 <,>  和 >= 等操作符方便数据挖掘，散列索引相等的比较快但是对于范围的比较就慢很多了。

三 引擎对比

1. 事务：MyISAM 不支持事务处理等高级处理，而 InnoDB 提供事务支持以及外键等高级数据库功能。

InnoDB 的行锁是不绝对的，在执行一个 SQL 语句时，若 MySQL 不能确定要扫描的范围，InnoDB 同样会锁定全表，例如 update table set num=1 where name like 'a%'

2. 性能：MyISAM 强调的是性能，它的执行速度要比 InnoDB 更快。

3. 行数保存：MyISAM 中保存了表的具体行数，在执行 select count(*) from talble 时，MyISAM 只需简单的读出保存好的行数即可，而 InnoDB 中不保存表的具体行数，在执行 select cout(*) from table 时，需要扫描一遍整个表来计算行数。注意，当 count() 语句中包含 where 条件时，两表的操作是一样的。

4. 索引存储：对于 auto_increment 类型的字段，InnoDB 中必须包含只有该字段的索引，但是在 MyISAM 表中，可以和其他字段一起建立联合索引。MyISAM 支持全文索引(FULLTEXT)、压缩索引，InnoDB 不支持。

MyISAM 的索引和数据是分开的，并且索引是有压缩的，内存使用率对应提高了不少。能加载更多索引，而 InnoDB 的索引和数据是紧密捆绑的，没有使用压缩从而会造成 InnoDB 比 MyISAM 体积庞大。

InnoDB 存储引擎被完全与 MySQL 服务器整合，InnoDB 存储引擎为在内存中缓存数据和索引而维持它自己的缓冲池，InnoDB 存储的表和索引在一个表空间中，表空间可以包含数个文件(或原始磁盘分区)。这与 MyISAM 表不同，MyISAM 中的每个表被存在分离的文件中。

5. 服务器数据备份：InnoDB 必须导出 SQL 来备份，load table form master 操作对 InnoDB 是不起作用的，需先把 InnoDB 表改成 MyISAM 表，导入数据后再改成 InnoDB 表，但是对于使用额外的 InnoDB 特性(如外键)的表不适用。而且 MyISAM 应对错误编码导致的数据恢复速度快，MyISAM 的数据是以文件的形式存储，所以在跨平台的数据转移中会很方便，在备份和恢复时可单独对某个表进行操作。InnoDB 是拷贝数据、备份 binlog，或者用 mysqldump，在数据量达到几十G时就很痛苦了。

6. 锁的支持：MyISAM 只支持表锁，InnoDB 支持表锁和行锁，行锁大幅度提升了多用户并发操作的性能，但是 InnoDB 的行锁只在 where 的主键是有效的，非主键的 where 都会锁定全表。

使用 InnoDB 的场景：

1. 可靠性高或者要求事务处理，则必须使用 InnoDB。

2. 表的更新和查询都很频繁，并且表锁定的机会比较大的情况指定 InnoDB 数据引擎的创建。

使用 MyISAM 的场景：

1. 做很多 count 的计算，如日志和业务表的调查。

2. 插入修改操作不频繁，查询操作非常频繁的。

补充：

1. 大容量的数据集时趋向于选择 InnoDB ，因为它支持事务处理和故障恢复，InnoDB 可以利用数据日志来进行数据的恢复，主键的查询在 InnoDB 中也是比较快的。

2. 在执行大批量的 insert 语句时在 MyISAM 引擎中执行的速度比较快，但是 update 语句在 InnoDB 中执行的会比较快，尤其在并发量大的时候。

四 InnoDB 和 MyISAM 引擎原理

1. 聚簇索引与非聚簇索引：

聚簇索引：指主索引文件和数据文件为同一份文件，聚簇索引主要用在 InnoDB 存储引擎中，即在查询过程中，找到了索引，便找到了数据文件。在 InnoDB 中，既存储主键索引值，又存储行数据，称为聚簇索引。InnoDB 索引指向主键对数据的引用，非主键索引则指向对主键的引用。在聚簇索引中，数据会被按照顺序整理排列，当使用 where 进行顺序、范围、大小检索时，会大大加速检索效率。

非聚簇索引：以 MyISAM 为例，一个数据表是由 .frm(存表定义) 、.myd(存表数据)、.myi(存表索引) 组成，在用到索引时，先到 .myi(索引树)中进行查找，取到数据所在 .myd 的行位置，拿到数据。在 MyISAM 中，索引文件和数据文件是独立分开的，则称为非聚簇索引。非聚簇索引的叶子节点存储的是数据存放的地址，主索引和辅助索引没什么区别。

2. MyISAM 引擎的索引结构：

MyISAM 索引结构用 B+ Tree 来存储数据，B+ Tree 的数据结构存储的内容为实际数据的地址，它的索引和实际的数据是分开的，只不过是用索引指向了实际的数据，这种索引是非聚集索引。

 因此，过程为：MyISAM 中索引检索的算法为首先按照 B+ Tree 搜索算法搜索索引，如果指定的 key 存在，则取出其 data 域的值，然后以 data 域的值为地址，根据 data 域的值去读取相应数据记录。

3. InnoDB 引擎的索引结构：

InnoDB 索引结构也是用 B+ Tree 来存储，InnoDB 的索引文件本身就是数据文件，即 B+ Tree 的数据结构存储的就是实际的数据，这种索引就是聚集索引。这个索引的 key 就是数据表的主键，因此 InnoDB 表数据文件本身就是主索引。InnoDB 的辅助索引数据域存储的也是相应记录主键的值而不是地址，所以当以辅助索引查找时，会先根据辅助索引找到主键，再根据主键索引找到实际的数据。InnoDB 不建议使用过长的主键，否则会使辅助索引变大，建议使用自增的字段作为主键，这样 B+ Tree 的每一个节点都会被顺序填满，而不会频繁的分裂调整，会有效的提升插入数据的效率。

如上图可知，叶节点包含了完整的数据记录，这种索引叫做聚集索引，因为 InnoDB 的数据文件本身要按主键聚集，所以 InnoDB 要求表必须有主键(MyISAM 可以没有)，如果没有显示指定，MySQL 会自动选择一个可以唯一标识数据记录的列作为主键，如果不存在这种列，则 MySQL 自动为 InnoDB 表生成一个隐含字段作为主键，这个字段长度为 6 个字节，类型为长整形。

与 MyISAM 索引不同的是 InnoDB 的辅助索引 data 域存储相应记录主键的值而不是地址(辅助索引都引用主键作为 data 域)。过程为：将主键组织到一颗 B+ 树中，而行数据就存储在叶子节点上，若使用 “where id=13” 这样的条件查找主键，则按照 B+ 树的检索算法可查找到对应的叶节点，之后获得行数据。若对 Name 列进行条件检索时，则需要在辅助索引 B+ 树中检索 Name，到达其叶子节点获取对应的主键，然后使用主键在主索引 B+ 树中再执行一次 B+ 树检索操作，最终到达叶子节点即可获得整行数据。