[MySQL Reference Manual]15. 其他存储引擎-CSDN博客

InnoDB：MySQL 5.6默认的存储引擎。InnoDB是事务安全的存储引擎，有commit，rollback，crash-recovery功能来保护用户数据。InnoDB行级锁和Oracle样式的一致性无锁读，增加多用户并发和性能。InnoDB以聚集索引方式存储用户数据，对主键查询，可以减少IO。InnoDB也支持外键约束。

MyISAM:这些表footprint比较小，表级锁定限制了读写性能，一般用于只读或者读多写少的应用。

Memory:所有的数据都存放在RAM内，能够快速的访问数据。这个引擎就是以前的HEAP引擎。

CSV:这个表实际上是文本文件使用了逗号分隔值。CSV表一般用于导入导出，数据线存放在InnoDB，在需要导入导出的时候转为CSV表。

Archive：这个是紧密的非索引表，用来存储和获取大量数据。

Blackhole:这个引擎其实不保存数据，和linux的/dev/null一样。查询结果都是为空，这些表可以复制，DML语句会传到Slave，但是master不保存数据。

Merge:可以让DBA和开发逻辑的把一些MyISAM表当成一个对象。

Federated:连接到独立的MySQL服务，在这个逻辑数据库上为很多歌物理服务创建对象。

Example：这个是MySQL的例子，如何开始编写新的存储引擎。。

以下是不同存储引擎的基本特点：

Feature	MyISAM	Memory	InnoDB	Archive	NDB
Storage limits	256TB	RAM	64TB	None	384EB
Transactions	No	No	Yes	No	Yes
Locking granularity	Table	Table	Row	Row	Row
MVCC	No	No	Yes	No	No
Geospatial data type support	Yes	No	Yes	Yes	Yes
Geospatial indexing support	Yes	No	Yes ^[a]	No	No
B-tree indexes	Yes	Yes	Yes	No	No
T-tree indexes	No	No	No	No	Yes
Hash indexes	No	Yes	No ^[b]	No	Yes
Full-text search indexes	Yes	No	Yes ^[c]	No	No
Clustered indexes	No	No	Yes	No	No
Data caches	No	N/A	Yes	No	Yes
Index caches	Yes	N/A	Yes	No	Yes
Compressed data	Yes ^[d]	No	Yes ^[e]	Yes	No
Encrypted data ^[f]	Yes	Yes	Yes	Yes	Yes
Cluster database support	No	No	No	No	Yes
Replication support ^[g]	Yes	Yes	Yes	Yes	Yes
Foreign key support	No	No	Yes	No	No
Backup / point-in-time recovery ^[h]	Yes	Yes	Yes	Yes	Yes
Query cache support	Yes	Yes	Yes	Yes	Yes
Update statistics for data dictionary	Yes	Yes	Yes	Yes	Yes
^[a]InnoDB support for geospatial indexing is available in MySQL 5.7.5 and higher. ^[b]InnoDB utilizes hash indexes internally for its Adaptive Hash Index feature. ^[c]InnoDB support for FULLTEXT indexes is available in MySQL 5.6.4 and higher. ^[d]Compressed MyISAM tables are supported only when using the compressed row format. Tables using the compressed row format with MyISAM are read only. ^[e]Compressed InnoDB tables require the InnoDB Barracuda file format. ^[f]Implemented in the server (via encryption functions), rather than in the storage engine. ^[g]Implemented in the server, rather than in the storage engine. ^[h]Implemented in the server, rather than in the storage engine.

15.1 设置存储引擎

当你创建新表，你会指定存储引擎，在ENGINE选项上。

-- ENGINE=INNODB not needed unless you have set a different
-- default storage engine.
CREATE TABLE t1 (i INT) ENGINE = INNODB;
-- Simple table definitions can be switched from one to another.
CREATE TABLE t2 (i INT) ENGINE = CSV;
CREATE TABLE t3 (i INT) ENGINE = MEMORY;

当你忽略ENGINE选项，就是用默认存储引擎。目前默认存储引擎是InnoDB。你可以指定默认存储引擎通过—default-storage-engine启动参数，或者配置文件中指定default-storage-engine。也可以在runtime设置使用set命令设置变量：

SET default_storage_engine=NDBCLUSTER;

临时表的存储引擎，通过create temporary table来创建，默认存储引擎通过default_tmp_storage_engine在启动前或者runtime设置。

可以使用alter table语句来转化表的存储引擎

ALTER TABLE t ENGINE = InnoDB;

如果指定的存储引擎没有被编译或者已经编译了但是没有激活，MySQL会使用默认存储引擎来代替。为了防止被魔火，可以启动NO_ENGINE_SUBSTITUTUIN SQL模式。如果指定的引擎不可用，会产生一个错误，而不是一个警告，表不会被创建。

对于新表，frm文件用来保存表和列的定义。表的索引和数据可能被存放在其他一个或者多个文件中，根据存储引擎决定。

15.2 MyISAM存储引擎

MyISAM的基本特性：

*Storage limits*	256TB	*Transactions*	No	*Locking granularity*	Table
*MVCC*	No	*Geospatial data type support*	Yes	*Geospatial indexing support*	Yes
*B-tree indexes*	Yes	*T-tree indexes*	No	*Hash indexes*	No
*Full-text search indexes*	Yes	*Clustered indexes*	No	*Data caches*	No
*Index caches*	Yes	*Compressed data*	Yes ^[a]	*Encrypted data ^[b]*	Yes
*Cluster database support*	No	*Replication support ^[c]*	Yes	*Foreign key support*	No
*Backup / point-in-time recovery ^[d]*	Yes	*Query cache support*	Yes	*Update statistics for data dictionary*	Yes

每个MyISAM表被存储在3个文件。文件以表名开头，并且有一个扩展名。Frm文件保存了表格式。Myd文件包村了数据文件，myi保存了索引文件。

可以通过mysqlcheck客户端，或者myisamchk工具，来检查和修复myisam表。也可以使用myisampack来压缩myisam表。

MyISAM主要由以下几个特点：

1.所有数据低字节先存。

2.所有数值类型高字节先存。

3.可以支持large files，最大63bit文件长度。

4.MyISAM最大行数2^32^2

5.每个MyISAM表64个索引，每个列最多16个索引

6.最大key为1000个字节。

7.当行被顺序插入，比如使用了auto_increment列，提高空间使用率。

8.支持一个表一个auto_increment列。

9.动态行大小，减少碎片。

10.MyISAM支持并发插入，如果表没有空闲的blocks你可以插入一个新行的同事，其他表读取表。

11.可以把数据文件目录和索引文件目录放在不同的目录上，在create table的时候指定data directory和index directory。

12.BLOB,TEXT可以被索引。

13.NULL值可以在所有列中

14.每个字符列可以有不同的字符集

15.每个MyISAM的索引文件有个标记表示表是否给关闭。

16.myisamchk标记呗检查过的表，如果使用了update-state选项。如果使用—fast选项不会被标记

17.myisamchk –analyze保存了部分可以的统计信息

18.myisampack可以压缩blob和varchar列

其他支持的特性：

1.支持varchar类型

2.varchar列可能固定或者动态行大小

3.varchar，char的长度最多为64KB。

4.任意长度的unique约束

15.2.1 MyISAM启动选项

具体看：http://dev.mysql.com/doc/refman/5.7/en/myisam-start.html

15.2.2 Key的空间要求

MyISAM表使用B树索引。你初略的计算index文件的大小，（key_length+4）*0.67，覆盖所有key。不压缩的key评估的大小。

String索引空间可以被压缩。如果索引的第一个部分是string，也会使用前缀压缩。索引压缩会让索引文件变小。

在MyISAM表你可以前缀压缩个数通过PACK_KEYS=1来指定，在创建表的时候。个数代表钱几个字节。

15.2.3 MyISAM表存储格式

15.2.3.1 静态表特性

静态表也就是固定行长度的表。没有可变长字段。每个行使用固定长度字节。

静态格式是最简单，醉汉全的模式。也是最快的，因为可以快速的定位到某一行。通过行号乘以行的长度。

如果电脑crash但是mysql还在运行。Myisamchk可以简单的发现那个是行的开始和结束，所以通常可以全部回收上除非被部分写入。

静态表的一些特性：

1.char和varchar使用空白填充。Binary和varbinary使用0x00填充。

2.快速

3.cache简单

4.crash重构很简单，因为行位置是固定的。

5.不需要重组除非删除了大量的行需要归还空间给OS。

6.空间比动态表消耗的多。

15.2.3.2 动态表特性

动态表是表中包含了可变长的列，或者表使用ROW_FORMATE_DYNAMIC创建。

动态格式比静态的有一点复杂，每个行的头都包含了行的长度。行也可以被碎片化。因为没有连续的空间存放。

你可以使用OPTIMIZE_TABLE或者myisamchk –r来整理表的碎片。如果是固定长度的列也包含了可变长的列。那么如果把变长列移动到其他地方那么固定长度的就不需要整理了。

动态格式有以下特性：

1.所有的字符创都是动态的，除非长度超过4

2.每个行前面都有一个bitmap，用来表示那些列时空字符串或者是0。但是不包含为null的值。如果字符串列的长度是0，或者数值型值是0，空间被删除只是在bitmap上面标记。

3.空间比静态的少。

4.每个行只用要求的空间。如果行变大，会分为很多碎片，比如，你更行了一行行变长了，但是变得碎片。

5.和静态表不同，crash之后要重组，因为行是碎片的，可能会丢失。

6.行长度计算公式如下：

3
+ (number of columns + 7) / 8
+ (number of char columns)
+ (packed size of numeric columns)
+ (length of strings)
+ (number of NULL columns + 7) / 8

可以使用myisamchk –ed查看连接碎片的连接，这些连接可以通过optimize table或者myisamchk –r移除。

15.2.3.3 压缩表特性

标错存储米欧式是只读的，使用myisampack工具生成。亚水表可以使用myisampack解压。

压缩表有以下特性：

1.压缩表空间占用少。

2.每行独立的被压缩，行头被压缩成1到3个字节，根据表中的最大行来决定。每个列压缩也不同。压缩类型：

a.后缀压缩

b.前缀压缩

c.数值列使用bit来代替0

d.如果int型，使用最小的长度来存储值。比如bigint的列，如果值在-128到127 可以使用smallint。

e.如果表只有很少的值，表类型就会被转为ENUM。

f.列可以使用很多上面组合的压缩类型。

3.可以是静态表或者动态表。

15.2.4 MyISAM表问题

15.2.4.1 MyISAM表损坏

出现以下情况MyISAM表就有可能被损坏：

1.mysqld在写入时被kill

2.异常的电脑关闭。

3.硬件错误。

3.使用外部程序比如myisamchk来修改表，但是服务也同时在修改。

4.软件bug，可能是mysql的或者是MyISAM的

通常表损坏的症状：

1.可能会在select数据的时候出现以下错误。

Incorrect key file for table: '...'. Try to repair it

2.查询没有完成，返回了一个未完成错误。

你可以通过CHECK TABLE检查MyISAM的表，并且听过REPAIR TABLE修复损坏的MyISAM表。当服务没有启动可以通过myisamchk来修复和检查损坏表。

如果表损坏很频繁，你可以尝试确定为什么会出现。最重要的损坏是服务crash。你可以通过error log的restarted mysqld信息来验证，是否频繁crash。如果不是crash那么就有可能是bug。

15.2.4.2 表没有被正确关闭

每个MyISAM表包含索引文件，上面有个计数器用来检查表是否被正确关闭。如果在check table或者myisamchk的时候有以下信息：

clients are using or haven't closed the table properly

这个信息表示表已经损坏，至少要检查该表。

计数器工作如下：

1.第一次表被MySQL更新，计数器自增1

2.在被更新的时候，计数器不变化。

3.当表的最后一个实例被关闭，计数器自减。

4.当你修复表或者检查表并且发现是没问题的，计数器会被重置为0.

5.为了避免问题，和其他操作交互的时候会检查表，如果计数器是0就不会被减。

也就是说，计数器只有在以下条件会变不正确：

1.MyISAM表被复制没有使用LOCK TABLES并且FLUSH TABLES.

2.MySQL在update和最后关闭之前crash。

3.表使用myisamchk –recover或者myisam –update-state和mysqld同时运行。

4.多个mysqld服务使用了这个表并且一个服务商在执行REPAIR TABLE或者CHECK TABLE,另外一份服务在运行。CHECK TABLE是安全的，你可以冲其他服务上得到警告。但是REPAIR TABLE应该避免因为当一个服务覆盖了数据文件，另外一个服务是不知道的。

一般不使用多个服务共享数据文件。

15.3 MEMORY存储引擎

MEMORY存储引擎创建特别目的的表，表内容是存放在内存中的。用户只能用来做临时的工作区或者只读cache。MEMORY存储引擎特性。

*Storage limits*	RAM	*Transactions*	No	*Locking granularity*	Table
*MVCC*	No	*Geospatial data type support*	No	*Geospatial indexing support*	No
*B-tree indexes*	Yes	*T-tree indexes*	No	*Hash indexes*	Yes
*Full-text search indexes*	No	*Clustered indexes*	No	*Data caches*	N/A
*Index caches*	N/A	*Compressed data*	No	*Encrypted data ^[a]*	Yes
*Cluster database support*	No	*Replication support ^[b]*	Yes	*Foreign key support*	No
*Backup / point-in-time recovery ^[c]*	Yes	*Query cache support*	Yes	*Update statistics for data dictionary*	Yes
^[a]Implemented in the server (via encryption functions), rather than in the storage engine. ^[b]Implemented in the server, rather than in the storage engine. ^[c]Implemented in the server, rather than in the storage engine.

什么时候使用MEMORY或者MySQL Cluster。开发想要部署使用了MEMORY的应用程序，高可用或者频繁更新数据要考虑MySQL Cluster是不是最好的选择。通常使用MEMORY涉及到以下特性：

1.操作相关的短暂的，非重要数据的管理或者cache。当MySQL服务夯住或者重启，MEMORY表的数据就会丢失。

2.内存存储访问快速，并且延迟低。数据可以填充到内存不会导致操作系统swap到虚拟内存。

3.只读或者读多的数据访问模式。

MySQL Cluster提供和MEMORY相似的功能提高性能，提供额外的特性MEMORY不支持的：

1.行级锁并且多线程操作，client减低争用。

2.可扩展性，甚至语句和写入混合。

3.数据持久性的后台操作

4.shared-nothing的结构，多host操作不会有单点错误。

5.自动数据分布，应用程序不需要参与用户sharding或者分区解决方案。

6.支持可变长字段，MEMORY不支持。

15.3.1 性能特点

MEMORY性能受限于单线程执行结果和更新的时候表锁。当实例负荷增加，语句混合了写入会限制可扩展性。

尽管MEMORY表，可能没有InnoDB速度快，对于繁忙的系统。特殊情况下，更新的表级锁可能会影响其他线程对MEMORY表的操作。

根据查询的不同，你可以创建索引，比如默认的hash数据结构，或者b树结构。

15.3.2 MEMORY表的物理特性

MEMORY存储引擎都有个相关的磁盘文件，用来保存表定义。表名.frm。

MEMORY表有以下几个特性：

1.MEMORY表使用小的block，表使用动态hash存储，没有一处或者额外的key空间。删除行把空间放到一个链表。当你插入的时候会重新使用。MEMORY表在插入和删除混合没有什么问题。

2.内存表固定长度。可变长度类型比如varchar也以固定长度保存。

3.内存表不能包含BLOB,TEXT裂隙

4.MEMORY支持AUTO_INCREMENT列

5.没有零食内存表可以被所有客户端共享，可其他临时表一样。

15.2.3 MEMORY的DDL操作

创建MEMORY表只要指定ENGINE=MEMORY。表被存放在内存，默认使用hash所有，hash索引可以让单个值更快的被查找，可以创建零食表。当服务关闭，所有MEMORY中的数据就会丢失。表会一直存在，因为表定义文件是存放在磁盘的。

MEMORY表的由系统变量,max_heap_table_size决定，默认是16MB，强制不同的大小，可以修改这个变量。这个值可以在CREATE TABLE,ALTER TABLE,TRUNCATE TABLE上被定义。服务重启也可以设置MEMORY表的最大大小到max_heap_table_size。

15.2.4 索引

MEMORY存储引擎支持hash和b树索引。你可以通过using子句来设置：

CREATE TABLE lookup
    (id INT, INDEX USING HASH (id))
    ENGINE = MEMORY;
CREATE TABLE lookup
    (id INT, INDEX USING BTREE (id))
    ENGINE = MEMORY;

MEMORY表最多可以有64个索引，每个索引16个列。最多key长度为3072个字节。

如果MEMORY表hash索引的列包含在很多索引中，yodate表影响字段的值会影响性能。性能影响和被几个索引引用有关。你可以使用b树索引来避免问题。

MEMORY表可以是非唯一的key。

索引列的值可以是null

15.2.5 用户创建和临时表

MEMORY表内容存储在内存中，这个是MEMORY表共享内部临时表的目的。有2种表类型不同，MEMORY表的目的不是为了存数据方便，但是内部临时表却是：

1.内部临时表变的太大，服务自动会转化为磁盘表。

2.用户创建的MEMORY表不会存到磁盘。

15.2.6 导入数据

为了MEMORY表，当服务启动你可以使用—init-file。你也可以使用INSERT INTO … SELECT或者LOAD DATA INFILE到文件冲持久性表里面导入数据。

15.2.7 MEMORY表和复制

当服务的MEMORY表在shutdown的时候就丢失。如果服务是master，slave不会在意这些表是不是变空了，所以可以从slave中看到以前的数据。为了同步master和slave，当master使用memory表，先使用delete语句删除所有记录，这样slave也会删除所有记录。Slave在master重启和第一次使用表之间还是会有过期数据。因此为了避免可以在master上使用—init-file选项。

15.2.8 管理内存使用

服务需要有足够的内存来维护所有MEMORY表。

内存并不会因为删除个别的行而使用减少，内存只有在整个表删除的时候才会释放。删除行的内存会被新增的行重用。为了缩放所有的内存你可以执行delete或者truncate table来删除所有的行。或者直接drop table。为了释放删除行的内存，可以通过alter table engine=memory来重建表。

每行的内存使用公式如下：

SUM_OVER_ALL_BTREE_KEYS(max_length_of_key + sizeof(char*) * 4)

+ SUM_OVER_ALL_HASH_KEYS(sizeof(char*) * 2)

+ ALIGN(length_of_row+1, sizeof(char*))

ALIGN()表示向上取证，因为sizeof(char*) 在32位是4个字节，在64位是8个字节。

Max_heap_table_size表示最大的MEMORY表的大小。在创建表的时候。可以设置session的值，如，以下脚本创建了2个MEMEORY表，一个是1MB一个是2MB。

mysql> SET max_heap_table_size = 1024*1024;

Query OK, 0 rows affected (0.00 sec)

mysql> CREATE TABLE t1 (id INT, UNIQUE(id)) ENGINE = MEMORY;

Query OK, 0 rows affected (0.01 sec)

mysql> SET max_heap_table_size = 1024*1024*2;

Query OK, 0 rows affected (0.00 sec)

mysql> CREATE TABLE t2 (id INT, UNIQUE(id)) ENGINE = MEMORY;

Query OK, 0 rows affected (0.00 sec)

如果服务重启后，2个表都是从全局的max_heap_table_size里面获取最大值。

你也可以在创建MEMORY的表上设置max_rows。

15.4 CSV存储引擎

15.4.1 CSV表的检查和修复

CSV存储引擎支持CHECK,REPAIR语句来检查和修复损坏的CSV表。

当运行CHECK语句，CSV文件会检查字段的分隔符是否正确。发现一个不可用的行就会报错。检查表如下：

mysql> check table csvtest;

+--------------+-------+----------+----------+

+--------------+-------+----------+----------+

+--------------+-------+----------+----------+

1 row in set (0.00 sec)

如果检查错误，表会被标记为crashed。一旦表被标记为损坏，当你check或者执行select 的时候会自动修复。相关的错误状态和形状带会在运行check的时候被显示：

mysql> check table csvtest;

+--------------+-------+----------+----------------------------+

+--------------+-------+----------+----------------------------+

+--------------+-------+----------+----------------------------+

2 rows in set (0.08 sec)

为了修复表可以运行REPAIR，这个副本尽量复制所有存在的数据，并且替换现在的CSV文件。损坏的记录就会被丢失。

15.4.2 CSV的限制

CSV存储引擎不能定义索引。

分区也不支持。

使用CSV创建的表必须要有not null属性。如果用了之前的MySQL创建的nullable列可以继续使用。

15.5 ARCHIVE存储引擎

ARCHIVE存储引擎是特殊目的的，用来保存大量的非索引数据。

ARCHIVE特性如下：

*Storage limits*	None	*Transactions*	No	*Locking granularity*	Row
*MVCC*	No	*Geospatial data type support*	Yes	*Geospatial indexing support*	No
*B-tree indexes*	No	*T-tree indexes*	No	*Hash indexes*	No
*Full-text search indexes*	No	*Clustered indexes*	No	*Data caches*	No
*Index caches*	No	*Compressed data*	Yes	*Encrypted data ^[a]*	Yes
*Cluster database support*	No	*Replication support ^[b]*	Yes	*Foreign key support*	No
*Backup / point-in-time recovery ^[c]*	Yes	*Query cache support*	Yes	*Update statistics for data dictionary*	Yes
^[a]Implemented in the server (via encryption functions), rather than in the storage engine. ^[b]Implemented in the server, rather than in the storage engine. ^[c]Implemented in the server, rather than in the storage engine.

ARCHIVE存储引擎包含MySQL binary发布中。如果是源代码安装，为了启动ARCHIVE存储引擎，可以使用CMake –DWITH_ARCHIVE_STORAGE_ENGINE选项。

可以在storage/archive查看ARCHIVE源代码。

当创建一个ARCHIVE表，服务创建一个表的定义在数据库目录。文件名为表名.frm。存储引擎创建其他文件，所有的都是以表名开头。数据文件的扩展名是ARZ，ARN文件会在优化操作的时候会出现。

ARCHIVE存储引擎，包括insert，select，但是没有delete，replace，update操作。使用order by排序，BLOB字段和其他所有基础类型但是不能排序spatial数据类型。

ARCHIVE存储引擎自持AUTO_INCREMENT类型属性。自增类型可以是唯一或者不唯一的索引。

试图在其他字段上创建创建索引都会报错。ARCHIVE存储引擎也支持对自增字段在创建表的时候设置初始化值。或者reset已经有的自增字段。

ARCHIVE不支持插入到自增字段小于当前字段最大值。如果尝试，就报键重复错误。

如果不需要BLOB字段在读取的时候会被忽略掉。

存储：行被压缩插入，ARCHIVE存储引擎使用zlib无损压缩方式。你可以使用OPTIMIZE TABLE来分析白哦并且打包到更小的格式。存储引擎也支持CHECK TABLE 。有一些被使用的类型：

1.INSERT语句只是把行存到压缩buffer中，buffer根据需要被刷新。Insert到buffer被锁保护。Select会强制buffer刷新。

2.bulk insert只有在完成后才可见，除非其他插入同时发生，可以部分可见。Select不会导致bulk insert刷新，除非正常的插入也同时发生。

获取：为了获取，行被解压。没有行cache。Select操作在压缩表上扫描：当select发生，查找有多上行会被读取。Select执行读一致性操作。大量在插入的时候有select语句会恶化，除非只有bulk或者延迟插入被使用。为了归档更好的压缩，你可以使用OPTIMIZE TABLE或者REPAIR TABLE。Show table status可以查看archive表的行数。

15.6 BLACKHOLE 存储引擎

BLACKHOLE存储引擎就是black hole，接受数据，但是不保存。获取总是为空。

mysql> CREATE TABLE test(i INT, c CHAR(10)) ENGINE = BLACKHOLE;

Query OK, 0 rows affected (0.03 sec)

mysql> INSERT INTO test VALUES(1,'record one'),(2,'record two');

Query OK, 2 rows affected (0.00 sec)

Records: 2 Duplicates: 0 Warnings: 0

mysql> SELECT * FROM test;

Empty set (0.00 sec)

如果要使用BLACKHOLE存储引擎，在编译源代码的时候，调用CMAKE –DWITH_BLACKHOLE_STORAGE_ENGINE。参数。

为了检查BLACKHOLE的源代码，可以在sql目录先查看发布的源代码。

当你创建BLACKHOLE表，服务创建一个表类型文件。表名.frm没有其他的文件。

BLACKHOLE存储引擎支持所有类型的索引。你可以在表定义的时候包含索引定义。

你可以使用show engine查看BLACKHOLE存储引擎是否可用。

插入到BLACKHOLE表，不保存任何数据，但是binlog会生成，SQL语句会被发送到slave上。

假设你的程序需要slave段的过滤，如果传输所有bin log数据传输量太大。这个时候可以使用假的master host，默认存储引擎是BLACKHOLE，描述如下：

Master写入bin log。假的mysqld进程就是slave，使用根据replicae-do-*,replicate-ignore-*规则写入一个新的过滤的binary log。被过滤的binary log提供给slave。

假进程并不提供任何数据存储，所以进程负荷几乎没有。

BLACKHOLE的INSERT触发器是一个例外，因为BLACKHOLE不保存任何数据，UPDATE和DELETE触发器没有被激活：FOR EACH ROW定义不会起作用因为没有数据。

其他可能使用BLACKHOLE的场景：

1.验证dump文件格式。

2.测量binary log 的负荷，通过使用BLACKHOLE不启动binary log的进行比较。

3.BLACKHOLE本质是no-op存储引擎，所以可以用来查找和存储引擎无关的性能问题。

BLACKHOLE存储引擎是事务的，提交事务被雪茹到binary log并且rollback并不会写入。

Blackhole存储引擎和自增字段

Blackhole是no-op引擎。任何表上的操作都没有响应。引擎并不会自动增加自增字段的值，也不会保留自增字段的状态。

考虑一下复制场景：

1.在master上，有一个blackhole表有一个自增字段并且是主键。

2.slave上有个一样表名的myisam表

3.插入操作执行没有显示的分配自增值。

在这个场景下复制就会报错，重复键错误。

基于语句的复制，自增值在context事件都是一样的。复制会因此报错。基于行的复制，每次插入的值都是一样的。也会报重复键错误。

列过滤

当使用基于行的binlog，slave少了最后一列是被支持的。具体可以看：

Section 17.4.1.10, “Replication with Differing Table Definitions on Master and Slave”.

过滤工作在slave段，列在过滤之前就被复制到slave。至少有2种情况需要过滤：

1.数据机密，所以slave不能访问。

2.master有很多slave，在发送前过滤可以减少网络负荷。

Master列过滤可以使用BLACKHOLE存储引擎。和master表的过滤类似，使用BLACKHOLE引擎和—replicate-do-table或者—replicate-ignore-table。

Master的部署：

CREATE TABLE t1 (public_col_1, ..., public_col_N,

secret_col_1, ..., secret_col_M) ENGINE=MyISAM;

信任slave部署：

CREATE TABLE t1 (public_col_1, ..., public_col_N) ENGINE=BLACKHOLE;

不信任slave部署：

CREATE TABLE t1 (public_col_1, ..., public_col_N) ENGINE=MyISAM;

15.7 MERGE存储引擎

MERGE存储引擎也就是MRG_MyISAM存储引擎，可以把一组一致的MyISAM表当成一个表。一致的意思是表结构和索引信息都一样。如果列顺序不同也不行。替代MERGE的方法是使用分区表，把单个表的不同分区存放在不同的文件上。分区可以让一些操作更加高效，不会受MyISAM存储引擎限制。

当你创建了MERGE表，MySQL创建2个文件在磁盘上。.frm和.mrg文件。

你可以使用select，delete，update，insert在MERGE表中。你必须有在MyISAM表上的select，delete，update权限。

使用DROP TABLE，只会删除MERGE设置，并不会影响底层表。

为了创建MERGE你必须指定，UNION操作说明那些MyISAM表被使用。你可以使用INSERT_METHOD选项来控制如何插入到MERGE表。使用FIRST或者LAST来决定插入到第一个表还是最后一个表。如果没有指定INSERT_METHOD或者指定了NO，插入MERGE不被允许操作，会报错。

创建MERGE的例子：

mysql> CREATE TABLE t1 (

-> a INT NOT NULL AUTO_INCREMENT PRIMARY KEY,

-> message CHAR(20)) ENGINE=MyISAM;

mysql> CREATE TABLE t2 (

-> a INT NOT NULL AUTO_INCREMENT PRIMARY KEY,

-> message CHAR(20)) ENGINE=MyISAM;

mysql> INSERT INTO t1 (message) VALUES ('Testing'),('table'),('t1');

mysql> INSERT INTO t2 (message) VALUES ('Testing'),('table'),('t2');

mysql> CREATE TABLE total (

-> a INT NOT NULL AUTO_INCREMENT,

-> message CHAR(20), INDEX(a))

-> ENGINE=MERGE UNION=(t1,t2) INSERT_METHOD=LAST;

在底层MyISAM表上，指定了主键，但是没有再MERGE表上，因为MERGE不能强制底层表的唯一性。

创建之后，你可以使用查询MERGE表：

重新映射MERGE表的底层表：

1.删除重新创建。

2.使用alter table tbl_name union=(…)来修改。也可以使用alter table tbl_name union()来删除所有底层表。这样表就是空的，插入就会失败，因为没有底层表。

低沉表定义和索引必须和MERGE一直。当表被打开的时候进行一致性检查。如果任意表一致性检查失败，触发打开表的操作就会失败。也就是说当MERGE被访问的时候修改MERGE内表的定义就会导致访问失败。一致性检查会应用到所有表：

1.底层表和MERGE表必须列个数一样

2.底层表和MERGE表列顺序必须一样。

3.列类型必须一致。

4.类长度必须一致

5.列可以为null

6.底层表索引个数至少和MERGE一样多。底层表可以更多但是不能少于MERGE表。

每个索引必须满足一下条件：

1.索引类型必须一样。

2.index part必须一样。

3.对于每个索引，index part一样，类型一样，语言一样，检查index part是否可以为null。

如果MERGE不能被打开或者使用因为底层表的问题，CHECK TABLE检查什么表的问题。

15.7.1 MERGE表的优缺点

MERGE表优点：

1.简单管理日志表，比如你可以把不同的月份的数据放在不同的表上，使用myisampacl压缩。然后创建MERGE来使用它们。

2.获取更快的速度。你可以根据一些关键点拆分大的只读表，然后分开的多个表中，并且位于不同的磁盘。MERGE表结构可以比使用单个大表速度更快。

3.执行查询更加高效。如果你准确的知道你要获取什么，你可以在底层表执行查询，然后多其他表使用merge。在一组表上可以有很多个merge。

4.修复更加有效。修复小表比修复单个达标速度更快。

5.及时的把表映射到一个。MERGE表不需要维护所有因为索引属于个别的表。MERGE可以快速的创建或者重新映射。

6.如果有一组表，你先要创建一个大表，现在可以使用MERGE表来代替。

7.可以超过系统限制的文件大小，因为MERGE由多个表组成。

8.你可以创建一个别名，通过映射到MERGE表

MERGE缺点：

1.只能使用MyISAM作为底层表。

2.一些MyISAM表的特性不可用。比如不能创建全文索引。

3.如果MERGE表不是临时的，所有低沉MyISAM表必须是非临时的。如果MERGE表是临时的，底层表可以是临时的也可以不是临时的。

4.MERGE表比MyISAM的文件描述多。如果10个客户端使用MERGE表映射了10个MyISAM表。服务使用(10*10)+10个文件描述。

5.索引读取很慢。当你读取索引，MERGE存储引擎需要在所有底层表上执行一个读来检查那个最匹配给定的index值。为了读取下一个值，MERGE存储引擎需要查询read buffer来查找下一个值。MERGE索引在eq_ref上很慢，但是在ref上并不慢。

15.7.2 MERGE表问题

1.直到MySQL 5.1.23 为止都可以使用非临时的MyISAM创建临时MERGE表。

2.如果使用ALTER TABLE来修改MERGE到另外一个存储引擎，底层表消失，底层表的行被复制到alter table表上，使用了指定存储引擎。

3.INSERT_METHOD表选项表示那个MyISAM表用于MERGE的insert into。如果底层表使用了自增字段，insert into MERGE表不会有反应

4.MERGE表不能维护唯一约束。

5.因为MERGE存储引擎不能强制唯一性约束，REPLIACE就不可能和预期的一样运行。主要有2个方面：

a.REPLACE只有在底层表的写入上才能发现唯一性冲突。

b.如果REPLACE发现唯一性冲突，只能改变写入的底层表。

6.MERGE表不支持分区。

7.你不能使用ANALYZE TABLE,REPAIR TABLE,OPTIMIZE TABLE,ALTER TABLE,DROP TABLE,DELETE没有where子句的。

8.DROP table在MERGE 表上在windows 上不起作用因为MERGE表被映射在底层表上。Windows不允许打开的文件被删除。所以你先要flush 所有merge表。

9.当文芳表的时候会检查MERGE和MyISAM表的定义。

10.MERGE表的索引顺序要和底层表一样。

11.如果发生错误比如Error 1017不能查看文件。通常表示底层表没有使用MyISAM存储引擎。

12.MERGE表最大的行数是2^64,就算映射的表在多也无法比这个行数多。

13.底层MyISAM表和MERGE表不同就会报错。

14.当LOCK tables的时候，不能修改union list。

15.不能通过create select创建MERGE表。

16.在一些情况下不同的PACK_KEYS表选项会导致一些想不到的问题。

15.8 FEDERATED存储引擎

FEDERATED存储引擎可以让你访问远程数据不需要通过复制或者集群技术。查询本地FEDERATED表自动从远程获取数据。本地不保存数据。

为了有FEDERATED存储引擎，可以在编译的时候CMake –DWITH_FEDERATED_STORAGE_ENGINE选项。

FEDERATED存储引擎默认不启动，可以通过-federated选项来启动。存储引擎源代码在storage/federated目录下。

15.8.1 FEDERATED存储引擎概述

当你创建表使用了标准的存储引擎(如，MyISAM，CSV，Innodb)表由表定义和相关数据组成。当你创建FEDERATED表，表定义是一样的，但是数据存储是在远程的。

FEDERATED表由以下2个要素组成：

1.远程服务的数据库表，包含了表定义，和相关数据。

2.本地服务的表，表定义和远程服务的要一样。表定义保存在frm文件，但是没有数据文件，只是有一个连接字符串指向远程表。

当在FEDERATED表执行查询和语句，草错可以正常执行，insert，update，delete都会被发送到远程去执行，或者从远程返回行。

FEDERATED结构如下：

当客户端执行SQL语句引用了FEDERATED表，客户端和服务间的信息流如下：

1.存储引擎查看，FEDERATED表相关的每个列，构建合适的SQL发送到远程表。

2.语句通过MySQL Client API发送到远程

3.远程服务处理语句并且本地服务获取语句处理的结果。

4.如果语句生成的结果，每个列都被转化为FEDERATED要的内部存储过程格式。并且结果发送到语句发生的最初地方。

本地服务和远程服务通过MySQL Client API交互使用mysql_real_query()发送语句，mysql_store_result()来获取结果，使用mysql_fetch_row()一次获取一行。

15.8.2 如何创建FEDERATED表

你可以使用以下步骤创建FEDERATED表：

1.在远程服务商创建表，如果表已经存在，使用show create table获取语句。

2.在本地创建表定义，添加链接信息到远程。

创建本地表关联到远程，有2个可用的选项。要不创建一个本地表并且制定连接字符串，或者使用已经存在的连接，通过create server创建的连接。

15.8.2.1 使用CONNECTION创建FEDERATED

使用第一种方法创建，你必须指定CONNECTION选项，例如：

CREATE TABLE federated_table (
    id     INT(20) NOT NULL AUTO_INCREMENT,
    name   VARCHAR(32) NOT NULL DEFAULT '',
    other INT(20) NOT NULL DEFAULT '0',
    PRIMARY KEY (id),
    INDEX name (name),
    INDEX other_key (other)
)
ENGINE=FEDERATED
DEFAULT CHARSET=latin1
CONNECTION='mysql://fed_user@remote_host:9306/federated/test_table';

CONNECTION字符串包含了连接到远程服务的信息包括存储数据的物理表。连接支付穿指定了服务名，登录凭据，端口，数据库/表名。格式如下：

scheme://user_name[:password]@host_name[:port_num]/db_name/tbl_name

schema:表示可以识别的协议，mysql是唯一支持的协议。

User_name:连接的用户名，用户名必须在remote上被创建，并且有正确的权限。

Password：可选用户名对应的密码

Host_name:远程服务的主机名或者ip地址

Port_num：可选，远程服务的端口

Db_name:数据库名

Tbl_name:远程的表名。

如：

CONNECTION='mysql://username:password@hostname:port/database/tablename'
CONNECTION='mysql://username@hostname/database/tablename'
CONNECTION='mysql://username:password@hostname/database/tablename'

15.8.2.2 创建FEDERATED表使用CREATE SERVER

如果你创建一批FEDERATED表，你可以先使用create server创建，如：

CREATE SERVER server_name
FOREIGN DATA WRAPPER wrapper_name
OPTIONS (option [, option] ...)

如：

CREATE SERVER fedlink
FOREIGN DATA WRAPPER mysql
OPTIONS (USER 'fed_user', HOST 'remote_host', PORT 9306, DATABASE 'federated');

创建FEDERATED表的使用，如下：

CREATE TABLE test_table (
    id     INT(20) NOT NULL AUTO_INCREMENT,
    name   VARCHAR(32) NOT NULL DEFAULT '',
    other INT(20) NOT NULL DEFAULT '0',
    PRIMARY KEY (id),
    INDEX name (name),
    INDEX other_key (other)
)
ENGINE=FEDERATED
DEFAULT CHARSET=latin1
CONNECTION='fedlink/test_table';

这个CONNECTION中包含了fedlink就是之前创建的server。

Description	CONNECTION string	CREATE SERVER option	mysql.servers column
Connection scheme	*scheme*	wrapper_name	Wrapper
Remote user	*user_name*	USER	Username
Remote password	*password*	PASSWORD	Password
Remote host	*host_name*	HOST	Host
Remote port	*port_num*	PORT	Port
Remote database	*db_name*	DATABASE	Db

15.8.3 FEDERATED存储引擎注意点

在使用FEDERATED的时候要注意：

1.FEDERATED表可能被复制到其他slave，但是你必须保证slave服务的可以使用用户密码访问远程服务，根据connection的定义。

后面是FEDERATED表支持的或不支持的特性：

1.远程服务必须是MySQL

2.远程表在访问前必须存在

3.很有可能一个FEDERATED表指向到另外一个FEDERATED表，但是注意不能创建出一个循环。

4.通常意义上FEDERATED表不支持索引，因为访问表数据是在远程被处理的，司机远程表是使用索引的。也就是说一个查询不能使用任何索引并且要表扫描，服务从远程获取所有记录并且过滤。不管使用了任何where，limit子句，这些子句都是在本地被使用的。

如果使用索引失败，或导致性能问题和网络负荷问题。另外会导致内存短缺。

5.注意当创建FEDERATED表因为和其他表一样的索引定义可能不被支持。比如在varchar，text，blob上创建前缀索引就会失败。

如：

CREATE TABLE `T1`(`A` VARCHAR(100),UNIQUE KEY(`A`(30))) ENGINE=FEDERATED
CONNECTION='MYSQL://127.0.0.1:3306/TEST/T1';

6.内部实现， select，insert，update，但是没有实现handle

7.FEDERATED存储引擎支持，select，insert，update，delete，truncate table和索引。但是不支持alter table或者任何其他DDL语句除了drop table。

8.FEDERATED使用insert … on duplicate key update语句，但是如果出现重复键错误，语句错误。

9.FEDERATED表在bulk insert的时候比其他表类型要慢，因为每个查询行都被分为独立的insert，insert到FEDERATED。

10.不支持事务。

11.FEDERATED执行bulk-insert处理多行被批量发送到远程表，性能得到提高并且远程的执行性能提高。如果远程是事务的，可以让远程引擎在错误发生的时候回滚：

a.行的大小不能超过服务器间的包大小，如果超过，就会被分为多个包。并且回滚就会出错。
b.bulk insert不支持insert … on duplicate key update。

12.FEDERATED无法知道远程表是不是被修改。因为文件必须和数据文件一样运行不能被其他服务写入。如果任何修改远程数据库本地表的一致性就可能会被破坏。

13.当使用CONNECTION连接的时候，不能在密码中使用@。但是在create server中可以使用。

14.insert_id和timestamp不能传播到data provider上。

15.任何drop table语句在FEDERATED表上只会删除本地表，不会删除远程表。

16.FEDERATED表不能使用query cache。

17.FEDERATED不支持用户定义分区。

15.8.4 FEDERATED存储引擎资源

其他的资源： http://forums.mysql.com/list.php?105.

15.9 EXAMPLE存储引擎

EXAMPLE存储引擎是为了给开发存储引擎的使用的例子，略。

15.10 其他存储引擎

具体看：http://dev.mysql.com/doc/refman/5.7/en/storage-engines-other.html

15.11 MySQL存储引擎概述

MySQL插件式存储引擎体系结构，可以让数据库根据不同的程序选择不同的存储引擎。MySQL服务体系结构隔离了应用程序和DBA对底层存储的实现细节，提供一致的简单的程序模型和API。因此尽管不同的存储引擎能力不同，这些不同对应用程序不可见。

插件式存储引擎结构提供了标准的服务管理和支持。存储引擎自己是数据库服务的组件实现了数据库底层的数据保存。

有效的和模块化的体系结构提供了大量的好处，可以为程序定制不同的存储。比如数据长裤，事务处理和高可用。

15.11.1 插件是存储引擎结构

MySQL服务使用插入是存储引擎结构体系可以让存储引擎加载和卸载。

加载存储引擎：

在存储引擎被使用前，存储引擎的共享library必须被加载到MySQL，使用INSTALL PLUGIN语句。比如：example存储引擎plugin称为example，shard library是ha_example。so.

mysql> INSTALL PLUGIN example SONAME 'ha_example.so';

为了能够加载存储引擎，插件文件的路径必须在MySQL plugin目录，并且执行INSTALL PLUGIN用户要有对mysql.plugin表有插入权限。

Shared library目录可以查看plugin_dir。

卸载存储引擎

为了卸载存储引擎可以使用UNINSTALL PLUGIN语句：

mysql> UNINSTALL PLUGIN example;

如果被卸载的存储引擎已经有表存在，那么这些表就不能被访问了，但是依然存在在磁盘上。保证在卸载之前已经没有表使用这个存储过程了。

posted on 2016-01-28 10:55 Fanr_Zh 阅读( ...) 评论( ...) 编辑收藏

转载于:https://www.cnblogs.com/Amaranthus/p/5165492.html