史上最简单 MySQL 数据库引擎

Myth丶恋晨 2022-04-18 06:27 277阅读 0赞

## 一、数据库引擎： ##

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

## 二、数据库引擎任务： ##

在数据库引擎文档中，各主题的顺序遵循用于实现使用数据库引擎进行数据存储的系统的任务的主要顺序。

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

## 三、MySQL 数据库引擎类别： ##

你能用的数据库引擎取决于mysql在安装的时候是如何被编译的。要添加一个新的引擎，就必须重新编译 MySQL。在缺省情况下，MYSQL 支持三个引擎：ISAM、MYISAM 和 HEAP。另外两种类型 INNODB 和 BERKLEY（BDB），也常常可以使用。

*  ISAM
 *  MYISAM
 *  HEAP
 *  INNODB和BERKLEYDB

**1、ISAM：**  
ISAM 是一个定义明确且历经时间考验的数据表格管理方法，它在设计之时就考虑到数据库被查询的次数要远大于更新的次数。因此，ISAM 执行读取操作的速度很快，而且不占用大量的内存和存储资源。ISAM 的两个主要不足之处在于，它不支持事务处理，也不能够容错：如果你的硬盘崩溃了，那么数据文件就无法恢复了。如果你正在把 ISAM 用在关键任务应用程序里，那就必须经常备份你所有的实时数据，通过其复制特性，MySQL 能够支持这样的备份应用程序。

**2、MYISAM：**  
MYISAM 是MySQL 的 ISAM 扩展格式和缺省的数据库引擎。除了提供 ISAM 里所没有的索引和字段管理的功能，MYISAM 还使用一种表格锁定的机制，来优化多个并发的读写操作。其代价是你需要经常运行 OPTIMIZE TABLE命令，来恢复被更新机制所浪费的空间。MYISAM 还有一些有用的扩展，例如用来修复数据库文件的MYISAMCHK 工具和用来恢复浪费空间的 MYISAMPACK 工具。  
　　MYISAM 强调了快速读取操作，这可能就是为什么 MySQL 受到了 WEB 开发如此青睐的主要原因：在 WEB开发中你所进行的大量数据操作都是读取操作。所以，大多数虚拟主机提供商和 INTERNET 平台提供商只允许使用 MYISAM 格式。

**3、HEAP：**  
HEAP 允许只驻留在内存里的临时表格。驻留在内存里让 HEAP 要比 ISAM 和 MYISAM 都快，但是它所管理的数据是不稳定的，而且如果在关机之前没有进行保存，那么所有的数据都会丢失。在数据行被删除的时候，HEAP 也不会浪费大量的空间。HEAP 表格在你需要使用 SELECT 表达式来选择和操控数据的时候非常有用。要记住，在用完表格之后就删除表格。

**4、INNODB 和 BERKLEYDB：**  
INNODB 和 BERKLEYDB（BDB）数据库引擎都是造就 MySQL 灵活性的技术的直接产品，这项技术就是 MySQL++ API。在使用 MySQL 的时候，你所面对的每一个挑战几乎都源于 ISAM 和 MYISAM 数据库引擎不支持事务处理也不支持外来键。尽管要比 ISAM 和 MYISAM 引擎慢很多，但是 INNODB 和 BDB 包括了对事务处理和外来键的支持，这两点都是前两个引擎所没有的。如前所述，如果你的设计需要这些特性中的一者或者两者，那你就要被迫使用后两个引擎中的一个了。

## 四、MySQL 数据引擎查看与修改： ##

**1、查看当前支持的引擎和默认引擎：**

SHOW ENGINES;

输出结果如下：  
![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl80MjY3MDQ1NQ_size_16_color_FFFFFF_t_70]

**2、更改数据库引擎：**

1）更改方式1：修改配置文件my.ini：

将my-small.ini 另存为 my.ini，在 [mysqld] 后面添加 default-storage-engine=InnoDB，
    重启服务，数据库默认的引擎修改为 InnoDB

2）建表的时候指定：

CREATE TABLE `table1` (
        `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT COMMENT '主键',
        `name` varchar(100) NOT NULL COMMENT '名称'
    ) TYPE=MyISAM;

3）建表后更改：

ALTER TABLE `table1` TYPE = InnoDB;

**3、查看修改结果：**

方法一：

SHOW TABLE STATUS FROM mytest;

方法二：

SHOW CREATE TABLE `table1`

## 五、MyIASM 和 Innodb引擎详解： ##

**1、Innodb 引擎：**  
Innodb 引擎提供了对数据库 ACID 事务的支持，并且实现了 SQL 标准的四种隔离级别，关于数据库事务与其隔离级别的内容我在另一篇文章有详解介绍，大家可以去看看，在这就不多说了。该引擎还提供了行级锁和外键约束，它的设计目标是处理大容量数据库系统，它本身其实就是基于 MySQL 后台的完整数据库系统，MySQL 运行时 Innodb 会在内存中建立缓冲池，用于缓冲数据和索引。但是该引擎不支持 FULLTEXT 类型的索引，而且它没有保存表的行数，当 SELECT COUNT(\*) FROM TABLE 时需要扫描全表。当需要使用数据库事务时，该引擎当然是首选。由于锁的粒度更小，写操作不会锁定全表，所以在并发较高时，使用 Innodb 引擎会提升效率。但是使用行级锁也不是绝对的，如果在执行一个 SQL 语句时 MySQL 不能确定要扫描的范围，InnoDB表 同样会锁全表。

**2、MyIASM 引擎：**  
MyIASM 是 MySQL默认的引擎，但是它没有提供对数据库事务的支持，也不支持行级锁和外键，因此当 INSERT(插入) 或 UPDATE(更新) 数据时即写操作需要锁定整个表，效率便会低一些。不过和 Innodb 不同，MyIASM 中存储了表的行数，于是 SELECT COUNT(\*) FROM TABLE 时只需要直接读取已经保存好的值而不需要进行全表扫描。如果表的读操作远远多于写操作且不需要数据库事务的支持，那么 MyIASM 也是很好的选择。

**3、两种引擎的选择：**  
大尺寸的数据集趋向于选择 InnoDB 引擎，因为它支持事务处理和故障恢复。数据库的大小决定了故障恢复的时间长短，InnoDB 可以利用事务日志进行数据恢复，这会比较快。主键查询在 InnoDB 引擎下也会相当快，不过需要注意的是如果主键太长也会导致性能问题，关于这个问题我会在下文中讲到。大批的 INSERT 语句(在每个 INSERT 语句中写入多行，批量插入)在 MyISAM 下会快一些，但是 UPDATE 语句在 InnoDB 下则会更快一些，尤其是在并发量大的时候。

**4、Index —— 索引：**

索引（Index）是帮助 MySQL 高效获取数据的数据结构。MyIASM 和 Innodb 都使用了树这种数据结构做为索引。下面我接着讲这两种引擎使用的索引结构，讲到这里，首先应该谈一下 B-Tree 和 B+Tree 。

###### 1）MyIASM 引擎的索引结构： ######

MyISAM 引擎的索引结构为 B+Tree，其中 B+Tree 的数据域存储的内容为实际数据的地址，
    也就是说它的索引和实际的数据是分开的，只不过是用索引指向了实际的数据，这种索引就是所谓的非聚集索引。

如下图所示：  
![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl80MjY3MDQ1NQ_size_16_color_FFFFFF_t_70 1]

这里设表一共有三列，假设我们以 Col1 为主键，则上图是一个 MyISAM 表的主索引（Primary key）示意。可以看出 MyISAM 的索引文件仅仅保存数据记录的地址。在 MyISAM 中，主索引和辅助索引（Secondary key）在结构上没有任何区别，只是主索引要求 key 是唯一的，而辅助索引的 key 可以重复。如果我们在 Col2 上建立一个辅助索引，则此索引的结构如下图所示：  
![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl80MjY3MDQ1NQ_size_16_color_FFFFFF_t_70 2]

同样也是一颗 B+Tree，data 域保存数据记录的地址。因此，MyISAM 中索引检索的算法为首先按照 B+Tree 搜索算法搜索索引，如果指定的 key 存在，则取出其 data 域的值，然后以 data 域的值为地址，读取相应数据记录。

###### 2）Innodb引擎的索引结构： ######

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

***两者区别：***

第一个重大区别是 InnoDB 的数据文件本身就是索引文件。从上文知道，MyISAM 索引文件和数据文件是分离的，
    索引文件仅保存数据记录的地址。而在 InnoDB 中，表数据文件本身就是按 B+Tree 组织的一个索引结构，
    这棵树的叶节点 data 域保存了完整的数据记录。这个索引的 key 是数据表的主键，因此 InnoDB 表数据文件本身就是主索引。

![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl80MjY3MDQ1NQ_size_16_color_FFFFFF_t_70 3]

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

第二个与 MyISAM 索引的不同是 InnoDB 的辅助索引 data 域存储相应记录主键的值而不是地址。换句话说，InnoDB 的所有辅助索引都引用主键作为 data 域。例如，下图为定义在 Col3 上的一个辅助索引：

![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl80MjY3MDQ1NQ_size_16_color_FFFFFF_t_70 4]

这里以英文字符的 ASCII 码作为比较准则。聚集索引这种实现方式使得按主键的搜索十分高效，但是辅助索引搜索需要检索两遍索引：首先检索辅助索引获得主键，然后用主键到主索引中检索获得记录。

**小结：**  
了解不同存储引擎的索引实现方式对于正确使用和优化索引都非常有帮助，例如知道了 InnoDB 的索引实现后，就很容易明白为什么不建议使用过长的字段作为主键，因为所有辅助索引都引用主索引，过长的主索引会令辅助索引变得过大。再例如，用非单调（可能是指“非递增”的意思）的字段作为主键在 InnoDB 中不是个好主意，因为 InnoDB 数据文件本身是一颗B+Tree，非单调（可能是指“非递增”的意思）的主键会造成在插入新记录时数据文件为了维持 B+Tree 的特性而频繁的分裂调整，十分低效，而使用自增字段作为主键则是一个很好的选择。

[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl80MjY3MDQ1NQ_size_16_color_FFFFFF_t_70]: /images/20220418/c110cbf4e38941fc8d106da8f114c2d6.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl80MjY3MDQ1NQ_size_16_color_FFFFFF_t_70 1]: /images/20220418/b9850d7d8d6546b2bb815a3230749915.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl80MjY3MDQ1NQ_size_16_color_FFFFFF_t_70 2]: /images/20220418/a97a6b1b7d2b4508b19abeaa913e64fb.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl80MjY3MDQ1NQ_size_16_color_FFFFFF_t_70 3]: /images/20220418/32144795fe6d4ac38120d8b2a1987e00.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl80MjY3MDQ1NQ_size_16_color_FFFFFF_t_70 4]: /images/20220418/0600726c101143d8ad332e1e4aff1892.png