【MySQL基础】05：表及存储引擎（Table and Storage Engine）

     本教程以MySQL8为主版本（内容兼顾MySQL5.7）。

     所有MySQL文章的目录为：总目录    https://blog.csdn.net/zyplanke/article/details/102968014     

 

存储引擎概述

     与大多数的数据库不同，MySQL的表table有一个存储引擎（storage engine）的概念。可以对每一个表Table设置不同的存储引擎，这样我们可以表的特点和用途选择最合适的存储引擎。所谓的存储引擎，是指表的数据管理和组织的方式（比如数据以什么样的形式、格式存放，是否支持事物、是否支持加密，是否有缓存机制等等）。在其他数据库中这种方式是随某个数据库产品固定的。但MySQL是灵活的、可配置的、可定制化的。

    MySQL可插拔存储引擎体系结构，是MySQL的重要特性。使DBA可以为特定的应用程序需求选择某个的存储引擎，来进行相应的数据存放、是否需要事物等。 MySQL支持多种存储引擎，甚至我们也可以定义和加载自己的存储引擎（可插入的存储引擎机制）。

    MySQL5.x及MySQL8.x支持的存储引擎有：InnoDB、MyISAM、MEMORY、CSV、ARCHIVE、BLACKHOLE、MERGE、FEDERATED、NDB等存储引擎。 其中InnoDB是支持事务一致性的存储引擎。 使用SHOW ENGINES命令，查看当前MySQL server端已经支持的存储引擎。

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.00 sec)

      小提示：从MySQL 5.5起，用户create table 默认使用的存储引擎已经从原来的MyISAM改为InnoDB。从MySQL8.0起，系统表自身的存储引擎也换成InnoDB。MySQL默认自带的系统表不再使用MyISAM。  从官方文档中拿出大量篇幅来介绍InnoDB，也从侧面反映出InnoDB的重要性，可以说没有InnoDB的事务保证，就没有MySQL如此广泛的使用。

对表Table指定存储引擎

mysql> CREATE TABLE t1 (i INT) ENGINE = INNODB;
Query OK, 0 rows affected (0.03 sec)

mysql> CREATE TABLE t2 (i INT) ENGINE = MyISAM;
Query OK, 0 rows affected (0.00 sec)

mysql> CREATE TABLE t3 (i INT) ENGINE = MEMORY;
Query OK, 0 rows affected (0.00 sec)

    还可以修改Table的存储引擎，准确说是convert转换存储引擎，因为修改存储引擎需要锁定并复制表数据。 有风险，生产环境不建议使用此命令修改存储引擎。推荐使用Percona OSC工具。

mysql> insert into t3 values (9999);
Query OK, 1 row affected (0.00 sec)

mysql> ALTER TABLE t3 ENGINE=INNODB;
Query OK, 1 row affected (0.01 sec)
Records: 1  Duplicates: 0  Warnings: 0

    通过show表的定义DDL语句来查看当前表的存储引擎（及字符集）

mysql> show create table t3;
+-------+----------------------------------------------------------------------------------------+
| Table | Create Table                                                                           |
+-------+----------------------------------------------------------------------------------------+
| t3    | CREATE TABLE `t3` (
  `i` int(11) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 |
+-------+----------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

存储引擎对比

      下表是常用存储引擎特性对比，数据来源来自于MySQL官方文档Table 16.1 （特别注意，网上有些资料是错误的）

特性	MyISAM	Memory	InnoDB	Archive	NDB
B树索引