MySQL系列(7)： 存储引擎介绍

MySQL 默认支持多种存储引擎，提供了灵活的存储，以支持不同场景的应用。

MySQL 8.0 版本，默认使用 InnoDB存储引擎，即建表语句(CREATE TABLE) 不带 ENGINE=子句会创建 InnoDB 表。

关于 MySQL 存储引擎，可以单独写成一个系列文章，后续有时间列入计划中。The InnoDB Storage Engine - 官网，lternative Storage Engines - 替代引擎

查看数据库提供支持的存储引擎命令：

mysql> SHOW ENGINES;
+--------------------+---------+----------------------------------------------------------------+--------------+------+------------+
| Engine             | Support | Comment                                                        | Transactions | XA   | Savepoints |
+--------------------+---------+----------------------------------------------------------------+--------------+------+------------+
| FEDERATED          | NO      | Federated MySQL storage engine                                 | NULL         | NULL | NULL       |
| MEMORY             | YES     | Hash based, stored in memory, useful for temporary tables      | NO           | NO   | NO         |
| InnoDB             | DEFAULT | Supports transactions, row-level locking, and foreign keys     | YES          | YES  | YES        |
| PERFORMANCE_SCHEMA | YES     | Performance Schema                                             | NO           | NO   | NO         |
| MyISAM             | YES     | MyISAM storage engine                                          | NO           | NO   | NO         |
| MRG_MYISAM         | YES     | Collection of identical MyISAM tables                          | NO           | NO   | NO         |
| BLACKHOLE          | YES     | /dev/null storage engine (anything you write to it disappears) | NO           | NO   | NO         |
| CSV                | YES     | CSV storage engine                                             | NO           | NO   | NO         |
| ARCHIVE            | YES     | Archive storage engine                                         | NO           | NO   | NO         |
+--------------------+---------+----------------------------------------------------------------+--------------+------+------------+
9 rows in set (0.10 sec)

查看存储引擎

1. MySQL 5.5 之前默认的存储引擎是MyISAM，之后改为InnoDB。

   -- 查看数据库支持的存储引擎
   SHOW ENGINES;
   -- 查看当前默认的存储引擎
   SHOW VARIABLES LIKE '%storage_engine%';

2. 在建表时，可在尾部指定存储引擎和字符编码,

   -- 建表时指定存储引擎
   CREATE TABLE userinfo (
     id INT(11) NOT NULL AUTO_INCREMENT,
     NAME VARCHAR(255) DEFAULT NULL,
     PASSWORD VARCHAR(255) DEFAULT NULL,
     PRIMARY KEY (id)
   ) ENGINE=INNODB DEFAULT CHARSET=utf8

3. 通过查看表创建的信息，在集息尾部有显示存储引擎和字符编码。

   -- 查看数据库表状态
   SHOW TABLE STATUS FROM DB_NAME WHERE NAME='tb_name';
   -- 查看建表信息
   show create table userinfo;

4. 修改表存储引擎:InnoDB,MyISAM

   -- 修改表的存储引擎
   ALTER TABLE tb_name ENGINE=MYISAM;

MySQL 存储引擎及特性

MySQL 在设计时就考虑了不同的应用场景，相应的提供了多种存储引擎。 下表汇总了 MySQL 提供的存储引擎支持特性的对比。

特点	MyISAM	Memory	InnoDB	Archive	NDB
B-tree 索引	yes	yes	yes	no	no
备份/时间点恢复	yes	yes	yes	yes	yes
集群数据库	no	no	no	no	yes
集群索引	no	no	yes	yes	no
数据压缩	yes(note 2)	no	yes	支持	支持
HASH索引	no	N/A	yes	no	yes
数据加密	yes(note 3)	yes(note 3)	yes(note 4)	yes(note 3)	yes(note 3)
外键	no	no	yes	no	yes(note 5)
全文搜索索引	yes	no	yes(note 6)	no	no
地理空间数据类型	yes	no	yes	yes	yes
地理空间数据索引	yes	no	yes(note 7)	no	no
Hash 索引	no	yes	no(note 8)	no	yes
索引缓存	yes	N/A	yes	no	yes
锁粒度	Table	Table	Row	Row	Row
MVCC (多版本并发控制)	no	no	yes	no	no
复制	yes	Limited	yes	yes	yes
存储限制	265TB	RAM	64TB	None	384EB
T-tree 索引	no	no	no	no	yes
事务	no	no	yes	no	yes
更新数据字 典统计信息	yes	yes	yes	yes	yes

备注：

1. note 3 由服务器内部的加密函数实现。
2. note 4 由服务器内部的加密函数实现；MySQL 5.7 版本和更新版本支持，支持数据静态表空间加密。
3. note 5 MySQL 集群 NDB 7.3 版本和更新版本支持。
4. note 6 MySQL 5.6 或更新版本，InnoDB 支持全文索引。
5. note 7 MySQL 5.7 或更新版本， InnoDB 支持地理空间数据类型的索引。
6. note 8 InnoDB 在内部利用哈希索引来实现其自适应哈希索引功能。

InnoDB/MyISAM

区别

MySQL最常用的2种存储引擎InnoDB和MyISAM在提供的功能上还是有些区别。

1. InnoDB 支持事务安全；MyISAM不支持事务。
2. InnoDB 自增列必须是索引。若是组合索引，必须是组合索引第一列；MyISAM 表的自增列可以是组合索引的其它列。
3. InnoDB 支持外键约束，MyISAM 不支持，一个包含外键的 InnoDB 表转 MyISAM 表会失败。
4. InnoDB 前缀索引的前缀长度可以达到1000字节长；MyISAM 前缀索引的前缀长度最长是767字节长。
5. InnoDB 支持 空间列 类型 索引，MyISAM 不支持。
6. InnoDB 是聚集索引，数据文件是和索引绑在一起的，必须要有主键，通过主键索引效率很高，跨平台可直接拷贝；MyISAM 是非聚集索引，数据文件是分离的，索引保存的是数据文件的指针，主键索引和辅助索引是独立的，跨平台很难直接拷贝。
7. InnoDB 不保存表的具体行数，执行 select count(*) from table 时需要全表扫描；MyISAM用一个变量保存了整个表的行数，执行上述语句时只需要读出该变量即可，速度很快。如果COUNT...WHERE后面带条件，则两则执行是一样的。

选择

1. 读操作多，可选用 MyISAM。
2. 写操作多，用 InnoDB。