MySQL的存储引擎

存储引擎介绍

存储引擎说白了就是如何存储数据、如何为存储的数据建立索引和如何更新、查询数据等技术的实现方法。因为在关系数据库中数据的存储是以表的形式存储的，所以存储引擎也可以称为表类型（即存储和操作此表的类型）。

MySQL 数据表主要支持六种存储引擎，分别是：BDB、HEAP、ISAM、MERGE、MYISAM、InnoDB。

这六种又分为两类：一类是“事务安全型”(transaction-safe)，包括 BDB 和 InnoDB；其余都属于第二类，称为”非事务安全型”(non-transaction-safe)。

BDB

BDB 全称是 “Berkeley DB”，它是 MySQL 最早的具有事务能力的表的类型，由 Sleepycat Software（http://www.sleepycat.com）开发。它提供了事务控制功能，它确保一组命令全部执行成功，或者当任何一个命令出现错误时所有命令的结果都被回退。可以想像在电子银行中事务控制能力是非常重要的。

最新版本的 MySQL 已经计划移除对 BDB 的支持，转而全力发展 InnoDB。InnoDB 对 MySQL 有更好的特性支持，而且开发社区活跃。

InnoDB

InnoDB 是较新的事务安全型存储引擎，用于事务处理应用程序，支持 BDB 的几乎所有特性，并具有众多新特性，包括 ACID 事务支持。

特性：

事务处理机制 支持外链 崩溃后能立即恢复 支持外键功能，级联删除 支持并发能力

查看存储引擎 通过 SHOW ENGINES 查看数据库默认使用的存储引擎

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

如上查询结果可知，InnoDB 是当前数据库默认的存储引擎。

通过 SHOW CREATE TABLE 语句查看表所使用的存储引擎

mysql> show create table student;
+---------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Table   | Create Table                                                                                                                                                                                                                                                                                                                                                                                               |
+---------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| student | CREATE TABLE `student` (
  `id` bigint(20) unsigned zerofill NOT NULL AUTO_INCREMENT,
  `name` varchar(30) DEFAULT NULL,
  `age` tinyint(3) unsigned DEFAULT NULL,
  `score` double(4,1) DEFAULT NULL,
  `birthday` date DEFAULT NULL,
  `insert_time` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=7 DEFAULT CHARSET=gbk |
+---------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

如上查询结果可知，student 表使用的存储引擎是 InnoDB。

通过 SHOW VARIABLES 命令查看当前数据库的存储引擎设置信息

mysql> show variables like '%storage_engine%';
+----------------------------------+--------+
| Variable_name                    | Value  |
+----------------------------------+--------+
| default_storage_engine           | InnoDB |
| default_tmp_storage_engine       | InnoDB |
| disabled_storage_engines         |        |
| internal_tmp_disk_storage_engine | InnoDB |
+----------------------------------+--------+
4 rows in set (0.00 sec)

通过数据字典表查看某个表的存储引擎

mysql> select engine from information_schema.tables where table_schema = 'schema_name' and table_name = 'table_name' 

注：table_schema 是指数据库名称

mysql> select engine,table_name from information_schema.tables where table_schema = 'test' and table_name = 'student'; 
+--------+------------+
| engine | table_name |
+--------+------------+
| InnoDB | student    |
+--------+------------+
1 row in set (0.00 sec)

通过 SHOW TABLE STATUS 查看表的存储引擎

查看某数据库所有表引擎：

mysql> SHOW TABLE STATUS FROM `db_name`; 

或者切换数据库再查看：

mysql> use qydpw;
mysql> show table status;
+---------+--------+---------+------------+------+----------------+-------------+-----------------+--------------+-----------+----------------+---------------------+---------------------+------------+----------------+----------+----------------+---------+
| Name    | Engine | Version | Row_format | Rows | Avg_row_length | Data_length | Max_data_length | Index_length | Data_free | Auto_increment | Create_time         | Update_time         | Check_time | Collation      | Checksum | Create_options | Comment |
+---------+--------+---------+------------+------+----------------+-------------+-----------------+--------------+-----------+----------------+---------------------+---------------------+------------+----------------+----------+----------------+---------+
| student | InnoDB |      10 | Dynamic    |    6 |           2730 |       16384 |               0 |            0 |         0 |              7 | 2021-10-08 22:13:53 | 2021-10-08 22:13:53 | NULL       | gbk_chinese_ci |     NULL |                |         |
+---------+--------+---------+------------+------+----------------+-------------+-----------------+--------------+-----------+----------------+---------------------+---------------------+------------+----------------+----------+----------------+---------+
1 row in set (0.00 sec)

查看满足条件的表的状态信息：

mysql> show table status like '%advice%';
+-----------+--------+---------+------------+------+----------------+-------------+-----------------+--------------+-----------+----------------+---------------------+-------------+------------+-----------------+----------+----------------+---------+
| Name      | Engine | Version | Row_format | Rows | Avg_row_length | Data_length | Max_data_length | Index_length | Data_free | Auto_increment | Create_time         | Update_time | Check_time | Collation       | Checksum | Create_options | Comment |
+-----------+--------+---------+------------+------+----------------+-------------+-----------------+--------------+-----------+----------------+---------------------+-------------+------------+-----------------+----------+----------------+---------+
| tf_advice | InnoDB |      10 | Compact    |    8 |           2048 |       16384 |               0 |        16384 |         0 |             17 | 2020-08-23 15:10:48 | NULL        | NULL       | utf8_general_ci |     NULL |                |         |
+-----------+--------+---------+------------+------+----------------+-------------+-----------------+--------------+-----------+----------------+---------------------+-------------+------------+-----------------+----------+----------------+---------+
1 row in set (0.00 sec)

查看某个表的存储引擎：

mysql> show table status where name ='student';
+---------+--------+---------+------------+------+----------------+-------------+-----------------+--------------+-----------+----------------+---------------------+---------------------+------------+----------------+----------+----------------+---------+
| Name    | Engine | Version | Row_format | Rows | Avg_row_length | Data_length | Max_data_length | Index_length | Data_free | Auto_increment | Create_time         | Update_time         | Check_time | Collation      | Checksum | Create_options | Comment |
+---------+--------+---------+------------+------+----------------+-------------+-----------------+--------------+-----------+----------------+---------------------+---------------------+------------+----------------+----------+----------------+---------+
| student | InnoDB |      10 | Dynamic    |    6 |           2730 |       16384 |               0 |            0 |         0 |              7 | 2021-10-08 22:13:53 | 2021-10-08 22:13:53 | NULL       | gbk_chinese_ci |     NULL |                |         |
+---------+--------+---------+------------+------+----------------+-------------+-----------------+--------------+-----------+----------------+---------------------+---------------------+------------+----------------+----------+----------------+---------+
1 row in set (0.00 sec)

设置存储引擎 设置表的存储引擎

mysql> alter table table_name engine = InnoDB;

设置数据库默认的存储引擎

mysql> SET session default_storage_engine = InnoDB;
mysql> SET global default_storage_engine = InnoDB;

通过更改STORAGE_ENGINE配置变量，能够方便地更改MySQL服务器的默认存储引擎。