勇闯BAT 聊聊Mysql的存储引擎

存储引擎是什么

MySQL架构可以分为4层，先看下，其所在的位置。

• 连接层：包括了数据库连接、授权认证、安全管理等
• Server层：包括 SQL 解析、优化、执行以及缓存等
• 存储引擎层：包括了各种存储引擎，主要负责数据的存取等
• 数据存储层：主要负责将数据存储在文件系统中，并完成与存储引擎的交互。

• 存储引擎层负责数据的存储和提取。其架构模式是插件式的，支持 InnoDB、MyISAM、Memory 等多个存储引擎。现在最常用的存储引擎是 InnoDB。

• 存储引擎其实就是对于数据库文件的一种存取机制，如何实现存储数据，如何为存储的数据建立索引以及如何更新，查询数据等技术实现的方法。

• MySQL中的数据用各种不同的技术存储在文件（或内存）中，这些技术中的每一种技术都使用不同的存储机制，索引技巧，锁定水平并且最终提供广泛的不同功能和能力。在MySQL中将这些不同的技术及配套的相关功能称为存储引擎。

查看支持的引擎

SHOW ENGINES;

常用存储引擎介绍

Archive

压缩协议进行数据的存储，数据存储为ARZ文件的格式。在归档之后很多的高级功能就不再支持了，仅仅支持最基本的插入和查询两种功能，拥有高效的插入速度，但是这种引擎不支持索引，所以查询性能较差一些。

特点：

• 只支持insert和select操作
• 只允许自增ID列建索引
• 行级锁
• 不支持事务
• 数据占用磁盘少

应用场景：

• 日志系统
• 大量的设备采集

Memory

数据都是存储在内存中，IO效率高，服务重启数据会丢失，内存表默认只有16M。

特点:

• 支持Hash、Btree索引
• 不支持大数据存储类型，例如：blob、text
• 表级锁
• 只能使用比较运算符=，<，>，=>，= <
• HEAP表不支持AUTO_INCREMENT

应用场景：

• 缓存表

MyISAM

在 MySQL 5.5 版本之前是默认的存储引擎，不支持事务，也不支持外键，最大的特点是速度快，占用资源少。

特点：

• select count（*）无需扫描所有行
• 数据（MYD）和索引（MYI）分开存储
• 表级锁，限制了读/写工作负载中的性能
• 不支持事务

应用场景：

• 通常用于Web和数据仓库配置中的只读场景。
• 以读为主的应用程序，比如博客系统、新闻门户网站

InnoDB

它是 MySQL 5.5 版本之后默认的存储引擎，最大的特点是支持事务、行级锁定、外键约束等。

特点：

• 数据和索引在一个文件(.ibd)
• 行级锁
• 支持事务

应用场景

• 更新（删除）操作频率高，或者要保证数据的完整性。
• 并发量高，支持事务和外键保证数据完整性。比如OA自动化办公系统。

概要

特别注意InnoDB的更新情况

• MySQL 5.6和更高版本提供了对FULLTEXT索引的支持。
• MySQL 5.7和更高版本提供了对地理空间索引的支持。

https://dev.mysql.com/doc/refman/5.7/en/innodb-storage-engine.html

常见问题

MyISAM 和 InnoDB的区别

来源：https://www.jianshu.com/p/a957b18ba40d

存储引擎是针对实例的还是表

存储引擎是指定在表之上的，即一个库中的每一个表都可 以指定专用的存储引擎。

自增主键最大值保存问题

一张表，里面有ID自增主键，当insert了17条记录之后，删除了第15,16,17条记录，再把Mysql重启，再insert一条记录，这条记录的ID是18还是15 ？

1.Mysql8.0以下版本
表类型为InnoDB引擎，这条记录的ID是15。因为InnoDB表只把自增主键的最大ID记录到内存中，所以重启MYSQL或者对表OPTIMIZE操作，都会使最大ID丢失。
表类型为MylSAM引擎，这条记录的ID是18。因为MylSAM表会把自增主键的最大ID记录到数据文件里面，重启MYSQL后，自增主键的最大ID也不会丢失。

2.Mysql8.0及以上版本
这条记录的ID是18，因为这个版本保存ID的值是在redo日志中的，重启之后是可以恢复的。

InnoDB引擎是否支持hash索引

上面其实已经介绍过了，是不支持Hash索引的，但是其内部有个自适应Hash索引。

自适应Hash索引特征能使InnoDB在具有适当的工作负载和足够缓冲池内存的系统上执行的更像内存中的数据库的操作，且不会牺牲事务特性或可靠性，MySQL能基于监视到的搜索规则，使用索引键的前缀构建Hash索引，前缀可以是任意长度，并且可能只有b+树中的某些值出现在Hash索引中，Hash索引其实就是对经常访问的索引页进行构建的。

这又说明其实InnoDB是支持Hash索引的，但并不是真正意义上的Hash，而是通过自己的监视情况自动对某些热点索引值构建的内存Hash。可以理解为其是索引的索引。

看下具体的图就很容易明白，这里借用沈剑老师的图：

但是我自己在InnoDB下怎么可以设置Hash索引呢？

这里对name做了hash索引，并没有报错，能正常执行。

CREATE TABLE `test` (`id` int(11) NOT NULL,`name` varchar(255) DEFAULT NULL,PRIMARY KEY (`id`),KEY `name` (`name`) USING HASH
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

这也太奇怪了，使用 show index from test查看索引信息

实际上其还是BTREE索引，应该是内部自动转换为 BTREE索引了，明显的泰国人妖，外壳妹子，内核汉子。