DBA MySQL存储引擎

功能概述

​ 关系型数据库中一个库就是一个目录，一个表则可能对应多份文件。

​ 存储引擎的作用就是与磁盘进行打交道，如何组织数据存储在磁盘上，如何从磁盘上读取数据就是其首要工作。

​ 存储引擎说白了其实就是相当于Linux的文件系统，在Oracle和SqlServer等数据库中仅支持一种存储引擎，但是在MySQL中则支持多种存储引擎，用户可以根据不同的需求为数据表选择不同的存储引擎，用户也可以根据自己的需要编写自己的存储引擎。

存储引擎一览

查看存储引擎

​ 使用命令查看目前MySQL所有支持的存储引擎：

M > SHOW ENGINES;
+--------------------+---------+----------------------------------------------------------------+--------------+------+------------+
| Engine             | Support | Comment                                                        | Transactions | XA   | Savepoints |
+--------------------+---------+----------------------------------------------------------------+--------------+------+------------+

| 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       |
+--------------------+---------+----------------------------------------------------------------+--------------+------+------------+

InnoDB

​ MySQL5.5版本后默认均采用该存储引擎。

​ 特点如下表所示：

功能	支持	功能	支持	功能	支持
存储限制	64TB	压缩数据	是	B树索引	是
MVCC多版本并发控制	是	加密数据	是	群集索引	是
外键	是	数据高速缓存	是	自适应散列索引	是
事务	是	索引高速缓存	是	地理空间索引	是
锁定粒度	行	查询高速缓存	是	全文搜索索引	是
复制	是	多个缓冲区池	是	地理空间数据类型	是
文件格式管理	是	更改缓冲	是	快速索引创建	是
更新数据字典	是	PERFORMANCE_SCHEMA	是
备份和恢复	是	群集数据库	是
自动故障恢复	是

​ InnoDB存储引擎是行锁设计、支持外键，并支持类似Oracle的非锁定读，即默认读取操作不会产生锁。

​ InnoDB存储引擎将数据放在一个逻辑的表空间中，这个表空间就像黑盒一样由InnoDB存储引擎自身来管理。

​ 从MySQL4.1(包括 4.1)版本开始，可以将每个InnoDB存储引擎的表单独存放到一个独立的ibd文件中。

​ InnoDB通过使用多版本并发控制(MVCC)来获得高并发性，并且实现了SQL标准 的4种隔离级别，默认为REPEATABLE级别，同时使用一种称为netx-key locking的策略来避免幻读(phantom)现象的产生。

​ 除了这些，InnoDB存储引擎还提供了插入缓冲(insert buffer)、二次写(double write)、自适应哈希索引(adaptive hash index)、预读(read ahead) 等高性能和高可用的功能。

​ 对于表中数据的存储，InnoDB存储引擎采用了聚集(clustered)的方式，每张表都是按主键的顺序进行存储的，如果没有显式地在表定义时指定主键，InnoDB存储引擎会为每一 行生成一个6字节的行ID(ROWID)，并以此作为主键。

​ InnoDB存储引擎是 MySQL数据库最为常用的一种引擎，Facebook、Google、Yahoo等 公司的成功应用已经证明了 InnoDB存储引擎具备高可用性、高性能以及高可扩展性。对其底层实现的掌握和理解也需要时间和技术的积累。

​ 如果想深入了解 InnoDB存储引擎的工作原理、实现和应用可以参考《MySQL 技术内幕:InnoDB存储引擎》一书。

MyISAM

​ MyISAM存储引擎是MySQL5.5.8之前默认的存储引擎。

​ 不支持事务、表锁设计、但支持全文索引。

​ 数据库系统与文件系统一个很大的不同在于对事务的支持，MyISAM存储引擎是不支持事务的。

​ MyISAM存储引擎的另一个与众不同的地方是，它的缓冲池只缓存(cache)索引文件，而不缓存数据文件，这与大多数的数据库都不相同。

Memory

​ 正如其名，Memory存储引擎中的数据都存放在内存中。

​ 数据库重启或发生崩溃，表中的数据都将消失，它非常适合于存储OLTP数据库应用中临时数据的临时表，也可以作为OLAP数据库应用中数据仓库的维度表。

​ Memory存储引擎默认使用哈希索引,而不是通常熟悉的B+树索引。

BLACKHOLE

​ 黑洞存储引擎，可以应用于主备复制中的分发主库。

其他的存储引擎

​ NTSE

​ 网易公司开发的面向其内部使用的存储引擎。

​ 目前的版本不支持事务，但提供压缩、行级缓存等特性，不久的将来会实现面向内存的事务支持。

​ Infobright

​ 第三方的存储引擎。

​ 其特点是存储是按照列而非行的，因此非常适合OLAP的数据库应用。

​ 其官方网站是 http://www.infobright.org/，上面有不少成功的数据 仓库案例可供分析。

​ NDB

​ 2003年，MysqlAB公司从SonyEricsson公司收购了NDB存储引擎。

​ NDB存储引擎是一个集群存储引擎，类似于Oracle的RAC集群，不过与Oracle RAC的share everythin结构不同的是，其结构是share nothing的集群架构，因此能提供更高级别的高可用性。

​ NDB存储引擎的特点是数据全部放在内存中(从 5.1 版本开始，可以将非索引数据放在磁盘上)，因此主键查找(primary key lookups)的速度极快，并且能够在线添加 NDB数据存储节点(data node)以便线性地提高数据库性能。

​ 由此可见，NDB存储引擎是高可用、 高性能、高可扩展性的数据库集群系统，其面向的也是OLTP的数据库应用类型。

​ RocksDB、MyRocks、TokuDB

​ 这三个的都是第三方存储引擎，压缩和数据的插入性能较高，其他功能和InnoDB没差.

存储引擎配置

检查存储引擎

​ 使用SELECT语句来检查当前使用MySQL实例所使用的默认存储引擎：

SELECT @@default_storage_engine;

​ 使用SHOW语句来检查某个表的存储引擎信息：

SHOW TABLE STATUS LIKE "表名"\G;
SHOW CREATE TABLE 表名;

​ 通过INFOMATION_SCHEMA检查每个非内置库表的存储引擎信息：

SELECT table_schema,table_name ,engine FROM information_schema.tables
	WHERE table_schema NOT IN ('sys','mysql','information_schema','performance_schema');

修改存储引擎

​ 修改当前会话级别的存储引擎，当前会话终端窗口关闭后失效：

SET default_storage_engine=InnoDB;

​ 修改当前全局级别的存储引擎，当前mysqld.service服务重启后失效：

SET GLOBAL default_storage_engine=InnoDB;

​ 在配置文件中对默认存储引擎进行修改，永久生效：

[mysqld]
default-storage-engine=INNODB

​ 修改指定表的存储引擎：

ALTER TABLE 表名 ENGINE = 存储引擎;

​ 通过INFOMATION_SCHEMA将某个非内置库的下所有的表存储引擎进行修改：

SELECT CONCAT("alter table 库名.",table_name," engine tokudb;") FROM
	information_schema.tables
WHERE table_schema='库名' INTO OUTFILE '/tmp/tokudb.sql';
# 通过source命令进行导入tokudb.sql文件即可