MySQL系列：9 存储引擎

前言

         前文提及MySQL为三层架构（连接层、SQL层、存储层）数据库系统，存储层即为数据存储的抽象表达，其具体由不同类型的存储引擎实现。

         MySQL采用分层设计，暴露API支持插件式的存储引擎。有些通用的存储引擎在MySQL编译发布时已一并编译进mysqld中，如InnoDB、MyISAM。show engines命令可以查看系统所支持的索引引擎及其特性，如下：

mysql> show engines;
+--------------------+---------+----------------------------------------------------------------+--------------+------+------------+
| Engine             | Support | Comment                                                        | Transactions | XA   | Savepoints |
+--------------------+---------+----------------------------------------------------------------+--------------+------+------------+
| MyISAM             | YES     | MyISAM storage engine                                          | NO           | NO   | NO         |
| CSV                | YES     | CSV 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         |
| PERFORMANCE_SCHEMA | YES     | Performance Schema                                             | NO           | NO   | NO         |
| InnoDB             | DEFAULT | Supports transactions, row-level locking, and foreign keys     | YES          | YES  | YES        |
| ARCHIVE            | YES     | Archive storage engine                                         | NO           | NO   | NO         |
| MEMORY             | YES     | Hash based, stored in memory, useful for temporary tables      | NO           | NO   | NO         |
| FEDERATED          | YES     | Federated MySQL storage engine                                 | NO           | NO   | NO         |
+--------------------+---------+----------------------------------------------------------------+--------------+------+------------+
9 rows in set (0.00 sec)

         如上列出了笔者MySQL系统锁支持的所有存储引擎，不同存储引擎有着其不同的特性（存储媒介、事务支持、备份和恢复等）。最常用的为InnoDB，其支持事务、XA分布式事务、保存点等，也是MySQL默认的存储引擎(MySQL5.5开始)；

InnoDB概述

         InnoDB为MySQL默认的存储引擎，其拥有极高的可靠性和性能，也正因为如此，使MySQL一跃成为企业级数据库的可选方案。MySQL主要特性有：

1. 支持事务；
2. 通过MVCC（多版本并发控制）实现行级锁、持续性的非加锁读；
3. 快速自动恢复

……

       InnoDB表空间

• 与ORACLE的概念类似，表空间是数据的逻辑存储点，表空间对应的物理文件为数据的物理存储点。MySQL InnoDB引擎的元数据、undo log、buffer等数据也默认存储于系统表空间，此表空间也可以包括多个物理文件或原始分区。查询表空间文件：

mysql>  show variables like '%data_file%';
+----------------------------+------------------------+
| Variable_name              | Value                  |
+----------------------------+------------------------+
| innodb_data_file_path      | ibdata1:12M:autoextend |
| innodb_temp_data_file_path | ibtmp1:12M:autoextend  |
+----------------------------+------------------------+
2 rows in set (0.00 sec)

以上包括InnoDB存储引擎的默认表空间文件（ibdata1）和临时表空间文件（ibtmp1），如下：

 

• 默认公共的表空间文件中包含回滚段（rollback segment）用于存储undo log，可通过配置innodb_undo_directory、innodb_undo_tablespaces将undo日志从默认表空间分离。----从5.7.13开始已废弃。

 

• InnoDB引擎默认将每个用户表存储于单独的表空间文件中，系统表空间仅存储其源数据。选项innodb_file_per_table可配置此功能。

mysql> show variables like '%innodb_file_per_table%';
+-----------------------+-------+
| Variable_name         | Value |
+-----------------------+-------+
| innodb_file_per_table | ON    |
+-----------------------+-------+
1 row in set (0.01 sec)

InnoDB共享表空间配置

与ORACLE为表空间增加文件类似，我们也可以为MySQL的共享表空间。

• 增加共享表空间文件数量

可在配置文件或mysqld的启动选项中，增加innodb_data_file_path选项可支持多个表空间文件，效果如下（似乎已废弃—实验不成功）：

innodb_data_file_path=ibdata1:50M;ibdata2:50M:autoextend

• 表空间自动增长的的大小，单位M

mysql> show variables like '%innodb_autoextend_increment%';
+-----------------------------+-------+
| Variable_name               | Value |
+-----------------------------+-------+
| innodb_autoextend_increment | 64    |
+-----------------------------+-------+
1 row in set (0.00 sec)

相关命令

• 查看默认存储引擎

除了上面的show engines外，default_storage_engine系统变量也存储默认存储引擎。

mysql> SELECT @@default_storage_engine;
+--------------------------+
| @@default_storage_engine |
+--------------------------+
| InnoDB                   |
+--------------------------+
1 row in set (0.00 sec)

• 查看表的存储引擎

mysql> show create table zavier.table_name;

• 配置文件设置默认存储引擎

default-storage-engine=<Storage Engine>

• 设置当前会话的存储引擎

SET @@storage_engine=<Storage Engine>;

• 建表时指定存储引擎

CREATE TABLE t (i INT) ENGINE = <Storage Engine>;

总结

InnoDB作为MySQL的默认存储引擎，有着其独特的性能和优势。然而，技术是不断发展的，随着MySQL版本的不断更新，一些配置选项也在不断优化。读者使用时需要在官网上寻找与自己MySQL版本匹配的ref文档参考。