InnoDB

InnoDB 是 MySQL 中第一个提供外键约束的存储引擎，而且它对事务的处理能力是其它存储引擎无法与之相比的。

MySQL 5.5 版本以后，默认存储引擎由 MyISAM 修改为 InnoDB。InnoDB 是目前最重要、使用最广泛的存储引擎。

InnoDB 一直在持续改进，随着处理能力的不断提高，其优秀的性能和可维护性使它成为生产中普遍推荐使用的存储引擎。一般情况下，除非有特别的原因需要使用其它存储引擎，否则应该优先考虑 InnoDB 引擎。

InnoDB优势

InnoDB 之所以如此受宠，主要在于其功能方面的较多优势。

1）支持事务安装

InnoDB 最重要的一点就是支持事务，可以说这是 InnoDB 成为 MySQL 中最流行的存储引擎的一个非常重要的原因。InnoDB 还实现了 SQL92 标准所定义的 4 个隔离级别（READ UNCOMMITTED，READ COMMITTED，REPEATABLE READ 和 SERIALIZABLE）。

InnoDB 对事务安全的支持，让很多之前因为特殊业务而放弃使用 MySQL 的用户转向支持 MySQL。以及对数据库选型持观望态度的用户来说，也大大增加了对 MySQL 的好感。

2）灾难恢复性好

InnoDB 通过 commit、rollback、crash-recovery 来保障数据的安全。

具体来说，crash-recovery 就是指如果服务器因为硬件或软件的问题而崩溃，不管当时数据是怎样的状态，在重启 MySQL 后，InnoDB 都会自动恢复到发生崩溃之前的状态，并回到用户离开的地方。

3）使用行级锁

InnoDB 改变了 MyISAM 的锁机制，实现了行锁。虽然 InnoDB 的行锁机制是通过索引来完成的，但毕竟在数据库中 99%的 SQL 语句都要使用索引来检索数据。行锁定机制也为 InnoDB 在承受高并发压力的环境下增强了不小的竞争力。

在 SQL 查询中可以自由地将 InnoDB 类型的表与其他类型的表混合起来，甚至在同一个查询中也可以混合。

4）实现了缓冲处理

InnoDB 提供了专门的缓存池，实现了缓冲管理，不仅能缓冲索引也能缓冲数据，常用的数据可以直接从内存中处理，比从磁盘获取数据处理速度要快。相比之下，MyISAM 只是缓存了索引。

InnoDB 的表和索引在一个逻辑表空间中，表空间可以包含数个文件（或原始磁盘分区）。这与 MyISAM 表不同，比如在 MyISAM 表中每个表被保存在分离的文件中。InnoDB 表可以是任何尺寸，即使在文件尺寸被限制为 2GB 的操作系统上。

5）支持外键

InnoDB 支持外键约束，检查外键、插入、更新和删除，以确保数据的完整性。在存储表中数据时每张表的存储都按主键顺序存放，如果没有显式地在定义表时指定主键，InnoDB 会为每一行生成一个 6 字节的 ROWID ，并以此作为主键。

InnoDB 实现外键引用这一重要特性，使在数据库端控制部分数据的完整性成为可能。虽然很多数据库系统调优专家都建议不要这样做，但是对于不少用户来说，大部分情况下，在数据库端加外键控制仍然是成本最低的选择。

6）适合需要大型数据库的网站

InnoDB 被用在众多需要高性能的大型数据库网站上。

InnoDB 是为处理巨大数据量时的最大性能设计，它的 CPU 效率可能是任何其他基于磁盘的关系数据库引擎所不能匹敌的。

除了以上几个亮点之外，InnoDB 常常还有很多其它的功能特色带给使用者惊喜。当然，使用 InnoDB 存储引擎肯定也有缺点。相对于其它存储引擎来说，使用 InnoDB 存储引擎的读写效率稍差，且占用的数据空间相对较大。

物理存储

使用 InnoDB 时，MySQL 会在数据目录（Data）下创建一个名为 ibdata1 的 10MB 大小的自动扩展数据文件，以及两个名为 ib_logfile0 和 ib_logfile1 的 5MB 大小的日志文件。

InnoDB 存储引擎和 MyISAM 不太一样，虽然也有 .frm 文件来存放表结构定义相关的元数据，但是表数据和索引数据是存放在一起的。至于是每个表单独存放还是所有表存放在一起，用户可以自己设置（下面会介绍如何设置）。

InnoDB 的物理存储结构分为两大部分：

1. 数据文件（表数据和索引数据）

数据文件用来存放数据表中的数据和所有的索引数据，包括主键和其他普通索引。

InnoDB 存储的数据采用表空间（Tablepace）进行存放设计。表空间是用来存放 MySQL 系统相关信息的一个特殊共享表空间。

InnoDB 的表空间分为以下两种形式：

  1. 共享表空间，表数据和索引都存放在同一个表空间。默认的表空间文件就是上面所提到的 MySQL 初始化路径下的 ibdata1 文件。
  2. 独立表空间，每个表的数据和索引被存放在一个单独的 .ibd 文件中。


可以通过以下命令查看 MySQL 是否使用独立表空间：

  1. mysql> SHOW VARIABLES LIKE 'innodb_file_per_table%';
  2. +-----------------------+-------+
  3. | Variable_name         | Value |
  4. +-----------------------+-------+
  5. | innodb_file_per_table | ON    |
  6. +-----------------------+-------+
  7. 1 row in set, 1 warning (0.01 sec)

2. 日志文件

默认情况下，InnoDB 存储引擎的数据目录下会有两个名为 ib_logfile0 和 ib_logfile1 的文件。在 MySQL 官方手册中将其称为 InnoDB 存储引擎的重做日志文件（redo log file）。

重做日志文件对 InnoDB 存储引擎至关重要。InnoDB 可以通过重做日志将数据库宕机时已经完成但还没有来得及将数据写入磁盘的事务恢复，也能将所有部分完成并已经写入磁盘的未完成事务回滚，并且将数据还原，以此来保证数据的完整性。

每个 InnoDB 存储引擎至少有 1 个重做日志文件组（group），每个文件组下至少有 2 个重做日志文件，如默认的 ib_logfile0 和 ib_logfile1。

如果你的数据库中有 InnoDB 的表，那么千万别全部删除 InnoDB 的日志文件，这很可能会让你的数据库 Crash，无法启动，或者丢失数据。
数据库不工作或停止响应、进程中断等情况，在业界也叫做数据库 Crash。
在 MySQL 启动参数文件设置中，InnoDB 的所有参数基本上都带有前缀“innodb_”，不论是 InnoDB 数据还是和日志相关，或者是其他一些性能，事务等等相关的参数都是一样。

下面是影响重做日志文件的参数：

  • innodb_log_file_size：指定每个重做日志的大小。
  • innodb_log_files_in_group：指定日志文件组中重做日志文件的数量，默认为 1。
  • innodb_mirrored_log_groups：指定日志镜像文件组的数量，默认为 1。
  • innodb_log_group_home_dir：指定日志文件组所在路径，默认为./。


简而言之，MySQL 中所有和 InnoDB 相关的系统变量都以“innodb_”做为前缀。

【Mysql-InnoDB 系列】InnoDB 架构

https://cloud.tencent.com/developer/article/1797780

MyISAM

1、 存储结构

MyISAM：每个MyISAM在磁盘上存储成三个文件。第一个文件的名字以表的名字开始，扩展名指出文件类型。.frm文件存储表定义。数据文件的扩展名为.MYD (MYData)。索引文件的扩展名是.MYI (MYIndex)。
InnoDB：所有的表都保存在同一个数据文件中（也可能是多个文件，或者是独立的表空间文件），InnoDB表的大小只受限于操作系统文件的大小，一般为2GB。存储为两个文件 .frm .ibd

2、 存储空间

MyISAM：可被压缩，存储空间较小。支持三种不同的存储格式：静态表(默认，但是注意数据末尾不能有空格，会被去掉)、动态表、压缩表。
InnoDB：需要更多的内存和存储，它会在主内存中建立其专用的缓冲池用于高速缓冲数据和索引。

3、 可移植性、备份及恢复

MyISAM：数据是以文件的形式存储，所以在跨平台的数据转移中会很方便。在备份和恢复时可单独针对某个表进行操作。
InnoDB：免费的方案可以是拷贝数据文件、备份binlog，或者用mysqldump，在数据量达到几十G的时候就相对痛苦了。

4、 事务支持

MyISAM：强调的是性能，每次查询具有原子性,其执行数度比InnoDB类型更快，但是不提供事务支持。
InnoDB：提供事务支持事务，具有事务(commit)、回滚(rollback)和崩溃修复能力(crash recovery capabilities)的事务安全(transaction-safe (ACID compliant))型表。

5、 AUTO_INCREMENT

MyISAM：可以和其他字段一起建立联合索引。引擎的自动增长列必须是索引，如果是组合索引，自动增长可以不是第一列，他可以根据前面几列进行排序后递增。
InnoDB：InnoDB中必须包含只有该字段的索引。引擎的自动增长列必须是索引，如果是组合索引也必须是组合索引的第一列。

6、 表锁差异

MyISAM：只支持表级锁，用户在操作myisam表时，select，update，delete，insert语句都会给表自动加锁，如果加锁以后的表满足insert并发的情况下，可以在表的尾部插入新的数据。
InnoDB：支持事务和行级锁，是innodb的最大特色。行锁大幅度提高了多用户并发操作的新能。但是InnoDB的行锁，只是在WHERE的主键是有效的，非主键的WHERE都会锁全表的。

7、 全文索引

MyISAM：支持 FULLTEXT类型的全文索引
InnoDB：不支持FULLTEXT类型的全文索引，但是innodb可以使用sphinx插件支持全文索引，并且效果更好。

8、 表主键

MyISAM：允许没有任何索引和主键的表存在，索引都是保存行的地址。
InnoDB：如果没有设定主键或者非空唯一索引，就会自动生成一个6字节的主键(用户不可见)，数据是主索引的一部分，附加索引保存的是主索引的值。

9、 表的具体行数

MyISAM：保存表的总行数，如果select count() from table;会直接取出出该值。
InnoDB：没有保存表的总行数，如果使用select count() from table；就会遍历整个表，消耗相当大，但是在加了wehre条件后，myisam和innodb处理的方式都一样。

10、 CURD操作

MyISAM：如果执行大量的SELECT，MyISAM是更好的选择。
InnoDB：如果你的数据执行大量的INSERT或UPDATE，出于性能方面的考虑，应该使用InnoDB表。DELETE 从性能上InnoDB更优，但DELETE FROM table时，InnoDB不会重新建立表，而是一行一行的删除，在innodb上如果要清空保存有大量数据的表，最好使用truncate table这个命令。

11、 是否支持外键

MyISAM：不支持，InnoDB：支持

如何选择存储引擎

MyISAM：默认的MySQL插件式存储引擎。如果应用是以读操作和插入操作为主，只有很少的更新和删除操作，并且对事务的完整性、并发性要求不是很高，那么选择这个存储引擎是非常适合的。MyISAM是在Web、数据仓储和其他应用环境下最常使用的存储引擎之一。

InnoDB：用于事务处理应用程序，支持外键。如果应用对事务的完整性有比较高的要求，在并发条件下要求数据的一致性，数据操作除了插入和查询以外，还包括很多的更新、删除操作，那么InnoDB存储引擎应该是比较合适的选择。InnoDB存储引擎除了有效地降低由于删除和更新导致的锁定，还可以确保事务的完整提交（Commit）和回滚（Rollback），对于类似计费系统或者财务系统等对数据准确性要求比较高的系统，InnoDB都是合适的选择。

MEMORY：将所有数据保存在 RAM 中，在需要快速定位记录和其他类似数据的环境下，可提供极快的访问。MEMORY 的缺陷是对表的大小有限制，太大的表无法缓存在内存中，其次是要确保表的数据可以恢复，数据库异常终止后表中的数据是可以恢复的。MEMORY表通常用于更新不太频繁的小表，用以快速得到访问结果。

InnoDB 的表是根据主键进行展开的 B+tree 的聚集索引。MyIsam 则非聚集型索引，myisam 存储会有两个文件，一个是索引文件，另外一个是数据文件，其中索引文件中的索引指向数据文件中的表数据。
聚集型索引并不是一种单独的索引类型，而是一种存储方式，InnoDB 聚集型索引实际上是在同一结构中保存了 B+tree 索引和数据行。当有聚簇索引时，它的索引实际放在叶子页中。
image.png

MySQL 中 MyISAM 中的查询为什么比 InnoDB 快？

https://cloud.tencent.com/developer/article/1401129

Memory

文件系统存储特点

称HEAP存储引擎，所以数据保存在内存中（服务器重启则表的数据丢失，但是表结构是保留的，表结构保存在磁盘文件中，而表的内容是存储在内存中）

功能特点

  • 支持HASH索引（等值查询）和BTree索引（范围查找）（默认HASH）
  • 所有字段都为固定长度varchar(10) = char(10)
  • 不支持BLOG和TEXT等大字段
  • Memory存储引擎使用表级锁
  • 表的最大大小由max_heap_table_size参数决定（默认16M，对存在的表修改是无效的）

实例

首先创建表

  1. MySQL [test]> create table mymemory (id int,c1 varchar(10),c2 char(10),c3 text ) engine = memory;
  2. ERROR 1163 (42000): The used table type doesn't support BLOB/TEXT columns
  3. MySQL [test]> create table mymemory (id int,c1 varchar(10),c2 char(10) ) engine = memory;        
  4. Query OK, 0 rows affected (0.02 sec)

我们能够看到，表是不支持TEXT字段的
我们再看下文件系统

  1. [root@wangerxiao test]# ls -lh  mymemo* 
  2. -rw-r----- 1 mysql mysql 8.5K Mar 17 15:25 mymemory.frm

只有一个保存表结构的文件
下面我们再看下表的索引
首先，新建两个索引

  1. MySQL [test]> create index idx_c1 on mymemory(c1);
  2. Query OK, 0 rows affected (0.01 sec)
  3. Records: 0  Duplicates: 0  Warnings: 0
  5. MySQL [test]> create index idx_c2 using btree on mymemory(c2);   
  6. Query OK, 0 rows affected (0.01 sec)
  7. Records: 0  Duplicates: 0  Warnings: 0

我们查看当前索引类型

  1. MySQL [test]> show index from mymemory \G
  2. *************************** 1. row ***************************
  3.         Table: mymemory
  4.    Non_unique: 1
  5.      Key_name: idx_c1
  6.  Seq_in_index: 1
  7.   Column_name: c1
  8.     Collation: NULL
  9.   Cardinality: 0
  10.      Sub_part: NULL
  11.        Packed: NULL
  12.          Null: YES
  13.    Index_type: HASH
  14.       Comment: 
  15. Index_comment: 
  16. *************************** 2. row ***************************
  17.         Table: mymemory
  18.    Non_unique: 1
  19.      Key_name: idx_c2
  20.  Seq_in_index: 1
  21.   Column_name: c2
  22.     Collation: A
  23.   Cardinality: NULL
  24.      Sub_part: NULL
  25.        Packed: NULL
  26.          Null: YES
  27.    Index_type: BTREE
  28.       Comment: 
  29. Index_comment: 
  30. 2 rows in set (0.00 sec)

存在两个索引，一个为默认的，一个是指定的BTree。
接下来我们查看表的状态

  1. MySQL [test]> show table status like 'mymemory'\G
  2. *************************** 1. row ***************************
  3.            Name: mymemory
  4.          Engine: MEMORY
  5.         Version: 10
  6.      Row_format: Fixed
  7.            Rows: 0
  8.  Avg_row_length: 86
  9.     Data_length: 0
  10. Max_data_length: 7799082
  11.    Index_length: 0
  12.       Data_free: 0
  13.  Auto_increment: NULL
  14.     Create_time: 2017-03-17 15:30:16
  15.     Update_time: NULL
  16.      Check_time: NULL
  17.       Collation: utf8mb4_general_ci
  18.        Checksum: NULL
  19.  Create_options: 
  20.         Comment: 
  21. 1 row in set (0.00 sec)

Memory存储引擎表和临时表的区别

临时表分两类：系统使用临时表，create temporary table 建立的临时表。无论哪种表，只有当前session是可见的。而Memory表是所有线程都可以使用的。

系统使用临时表又分为两类：查过限制使用Myisam临时表，未超过限制使用Memory表。
使用场景

  • 用于查找或者是映射表，例如邮编和地区的对应表
  • 用于保存数据分析中产生的中间表
  • 用于缓存周期性聚合数据的结果表

注意一点是：Memory数据易丢失，所以要求数据可再生
memory存储引擎是MySQL中的一类特殊的存储引擎。其使用存储在内存中的内容来创建表，而且所有数据也放在内存中。这些特性都与InnoDB，MyISAM存储引擎不同。

讲解一些memory存储引擎的文件存储形式，索引类型，存储周期和优缺点。
每个基于memory存储引擎的表实际对应一个磁盘文件，该文件的文件名与表名相同，类型为frm类型。该文件只存储表的结构，而其数据文件，都是存储在内存中的，这样有利于对数据的快速的处理，提高整个表的处理效率。
值得注意的是：服务器需要有足够的内存来维持memory存储引擎的表的使用。如果不需要了，可以释放这些内存，甚至可以删除不需要的表。

Memory存储引擎默认使用哈希(HASH)索引，其速度比使用B型树(BTREE)索引快。如果我们需要使用B型树索引，可以在创建索引时选择使用。
这里来整理一个小的技巧：
Memory存储引擎通常很少用到，至少我是没有用到过。因为Memory表的所有数据都是存储在内存上的，如果内存出现异常会影响到数据的完整性。
如果重启机器或者关机，表中的所有数据都将消失，因此，基于Memory存储引擎的表的生命周期都比较短，一般都是一次性的。
Memory表的大小是受到限制的，表的大小主要取决于2个参数，分别是max_rows和max_heap_table_size。其中，max_rows可以在创建表时指定，max_heap_table_size的大小默认为16MB，可以按需要进行扩大。
因此，其基于内存中的特性，这类表的处理速度会非常快，但是，其数据易丢失，生命周期短。基于其这个缺陷，选择Memory存储引擎时需要特别小心。

Archive

文件系统存储特点：

  • 以zlib对表数据进行压缩，磁盘I/O更少
  • 数据存储在ARZ为后缀的文件中

Archiv存储引擎的特点

  • 只支持insert和select操作（支持行级锁和缓冲区，可以实现高并发的插入）
  • 只允许在自增ID列上加索引

Archive表比MyISAM表要小大约75%，比支持事务处理的InnoDB表小大约83%。当数据量非常大的时候Archive的插入性能表现会较MyISAM为佳。

Archive表的性能是否可能超过MyISAM？答案是肯定的。根据MySQL工程师的资料，当表内的数据达到1.5GB这个量级，CPU又比较快的时候，Archive表的执行性能就会超越MyISAM表。因为这个时候，CPU会取代I/O子系统成为性能瓶颈。别忘了Archive表比其他任何类型的表执行的物理I/O操作都要少。

较小的空间占用也能在你移植MySQL数据的时候发挥作用。当你需要把数据从一台MySQL服务器转移到另一台的时候，Archive表可以方便地移植到新的MySQL环境，你只需将保存Archive表的底层文件复制过去就可以了。

存储

往archive表插入的数据会经过压缩，archive使用zlib进行数据压缩，archive支持optimize table、 check table操作。
一个insert语句仅仅往压缩缓存中插入数据，插入的数据在压缩缓存中被锁定，当select操作时会触发压缩缓存中的数据进行刷新。insert delay除外。
对于一个bulk insert操作只有当它完全执行完才能看到记录，除非在同一时刻还有其它的inserts操作，在这种情况下可以看到部分记录，select从不刷新bulk insert除非在它加载时存在一般的Insert操作。

检索

对于检索请求返回的行不会压缩，且不会进行数据缓存；一个select查询会执行完整的表扫描；当一个select查询发生时它查找当前表所有有效的行，select执行一致性读操作，注意，过多的select查询语句会导致压缩插入性能变的恶化，除非使用bulk insert或delay insert，可以使用OPTIMIZE TABLE 或REPAIR TABLE来获取更好的压缩，可以使用SHOW TABLES STATUS查看ARCHIVE表的记录行。

实例：


建立一张表

  1. MySQL [test]> create  table myarchive( id int auto_increment not null , c1  varchar(10),c2  char(10),key(id)) engine = archive;
  2. Query OK, 0 rows affected (0.06 sec)

查看文件系统

  1. [root@wangerxiao test]# ls  -lh  myarchi*
  2. -rw-r----- 1 mysql mysql 8.5K Mar 17 14:47 myarchive.ARZ
  3. -rw-r----- 1 mysql mysql 8.5K Mar 17 14:47 myarchive.frm

ARZ文件存储表内容，frm文件存储表结构（MySQL服务器层）。
我们往数据表里插入一些数据

  1. MySQL [test]> insert into myarchive (c1,c2) values ('aa','bb'),('cc','dd');
  2. Query OK, 2 rows affected (0.02 sec)
  3. Records: 2  Duplicates: 0  Warnings: 0
  5. MySQL [test]> select * from myarchive;
  6. +----+------+------+
  7. | id | c1   | c2   |
  8. +----+------+------+
  9. |  1 | aa   | bb   |
  10. |  2 | cc   | dd   |
  11. +----+------+------+
  12. 2 rows in set (0.01 sec)

我们看到可以进行查询操作，现在看是否可以进行删除操作。

  1. MySQL [test]> delete from myarchive where id = 1;
  2. ERROR 1031 (HY000): Table storage engine for 'myarchive' doesn't have this option

更新操作

  1. MySQL [test]> update myarchive  set c1='aaaa' where id =1;
  2. ERROR 1031 (HY000): Table storage engine for 'myarchive' doesn't have this option

我们接着查看是否可以在非自增键上创建索引

  1. MySQL [test]> create index idx_c1 on myarchive(c1); 
  2. ERROR 1069 (42000): Too many keys specified; max 1 keys allowed

使用场景


日志和数据采集类应用（不支持OLTP）

分区

Archive存储引擎支持分区

  1. ALTER TABLE tb_archive 
  2. PARTITION BY RANGE(id) PARTITIONS 3( PARTITION part0 VALUES LESS THAN (5),  PARTITION part1 VALUES LESS THAN (10),  PARTITION part2 VALUES LESS THAN (MAXVALUE)) ;
  4. ALTER TABLE tb_archive 
  5. PARTITION BY LIST COLUMNS (name) (
  6.     PARTITION a VALUES IN ('A','B'),
  7.     PARTITION b VALUES IN ('C'),
  8.     PARTITION c VALUES IN ('D')
  9. );
  11. ALTER TABLE tb_archive 
  12. PARTITION BY KEY(address)
  13. PARTITIONS 3;

总结

由于高压缩和快速插入的特点Archive非常适合作为日志表的存储引擎，但是前提是不经常对该表进行查询操作。