MySQL 之 其他存储引擎

Merge存储引擎

       也叫MRG_MyISAM，为那些完全一致的MyISAM表提供统一的访问接口，使得它们好像是合并了一样，实际上Merge并不存储数据。合并的MyISAM表要求具有完全相同的列和索引信息。以下为做的一些小测试

 

创建基表：

mysql> CREATE TABLE t1(id INT PRIMARYKEY,name VARCHAR(20));

Query OK, 0 rows affected (0.05 sec)

 

mysql> CREATE TABLE t2(id INT PRIMARYKEY,name VARCHAR(20));

Query OK, 0 rows affected (0.01 sec)

 

各插入2条实验数据

mysql> INSERT INTO t1 VALUES(1,'a'),(2,'b');

Query OK, 2 rows affected (0.09 sec)

Records: 2 Duplicates: 0  Warnings: 0

 

mysql> INSERT INTO t2 VALUES(3,'c'),(4,'d');

Query OK, 2 rows affected (0.02 sec)

Records: 2 Duplicates: 0  Warnings: 0

 

mysql> SELECT * FROM t1;

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

| id | name |

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

|  1| a    |

|  2| b    |

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

2 rows in set (0.00 sec)

 

mysql> SELECT * FROM t2;

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

| id | name |

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

|  3| c    |

|  4| d    |

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

2 rows in set (0.00 sec)

 

创建Merge表：

    union选项指定合并的基表，insert_method选项指明了当向merge表插入数据时，实际插入的表，如不指定，插入时会报错。

mysql> CREATE TABLE merge_t1_t2 (id INTPRIMARY KEY, name VARCHAR(20)) ENGINE=MERGE UNION=(t1,t2) INSERT_METHOD=LAST ;

Query OK, 0 rows affected (0.01 sec)

 

查询Merge表结果为t1和t2的并集：

mysql> SELECT * FROM merge_t1_t2;

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

| id | name |

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

|  1| a    |

|  2| b    |

|  3| c    |

|  4| d    |

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

4 rows in set (0.00 sec)

 

向Merge表插入数据，由于指定了INSERT_METHOD=LAST，实际插入t2表：

mysql> insert into merge_t1_t2 values(5,'e');

Query OK, 1 row affected (0.03 sec)

 

mysql> SELECT * FROM merge_t1_t2;

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

| id | name |

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

|  1| a    |

|  2| b    |

|  3| c    |

|  4| d    |

|  5| e    |

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

5 rows in set (0.00 sec)

 

mysql> SELECT * FROM t2;

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

| id | name |

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

|  3| c    |

|  4| d    |

|  5| e    |

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

3 rows in set (0.00 sec)

 

此时，数据目录中也生成了Merge表的文件：merge_t1_t2.frm和merge_t1_t2.MRG

我们来看一下.MGR文件的内容：

[root@web01 engine]# catmerge_t1_t2.MRG

t1

t2

#INSERT_METHOD=LAST

 

发现.MGR简单地保存了基表的表名，没有保存实际数据，那么是不是可以通过修改.MGR文件来修改Merge表呢？

 

创建表t3，插入2条数据：

mysql> CREATE TABLE t3(id INT PRIMARYKEY,name VARCHAR(20));

Query OK, 0 rows affected (0.00 sec)

 

mysql> INSERT INTO t3 VALUES(5,'f'),(6,'g');

Query OK, 2 rows affected (0.01 sec)

Records: 2 Duplicates: 0  Warnings: 0

 

修改.MGR文件，加入t3

t1

t2

t3

#INSERT_METHOD=LAST

 

看看Merge表是否合并了t3：

mysql> SELECT * FROM merge_t1_t2;

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

| id | name |

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

|  1| a    |

|  2| b    |

|  3| c    |

|  4| d    |

|  5| e    |

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

5 rows in set (0.00 sec)

 

没有合并进来，思考一下，是不是需要flush一下呢？

[root@web01 engine]# mysqladmin -urootflush-tables;

 

mysql> SELECT * FROM merge_t1_t2;

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

| id | name |

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

|  1| a    |

|  2| b    |

|  3| c    |

|  4| d    |

|  5 | e   |

|  5 | f   |

|  6| g    |

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

7 rows in set (0.00 sec)

合并成功，看到id列出现了相同的值而没有出错，这是因为约束是由基表来控制的，MERGE表上的约束没有作用，只要符合基表的约束，就可以插入、更新重复的键值

 

而且此时对merge表的插入行，行实际会插到t3表中，说明INSERT_METHOD中指定的LAST是.MGR文件中的列出的最后一张表（后面将文件中的t2和t3交换位置，得出了预料中的结果）

 

那如果在.MGR中加入不存在表呢？

t1

t2

t3

t4

#INSERT_METHOD=LAST

[root@web01 engine]# mysqladmin -urootflush-tables;

 

flush操作没有报错

试试看查询

mysql> select * from merge_t1_t2;

ERROR 1168 (HY000): Unable to openunderlying table which is differently defined or of non-MyISAM type or doesn'texist

 

出错了，由此可以看出，访问Merge表后，Merge存储引擎会去查找.MGR文件，找到该文件中列出的表名，再对这些表分别执行查询，将结果UNION再返回。因为是UNION操作，所以Merge存储引擎要求列和索引的内容、顺序完全一致。

 

注意事项：

    ·修改.MGR文件的方式来修改merge表之后，一定要flush table

    ·Merge表基于UNION运算，和分区表相比性能还是有差距的。

Memory存储引擎

       存储在内存中，默认使用哈希索引（InnoDB为B数索引）。速度很快，但服务器关闭后数据全部丢失，仅保存.frm表定义文件。以下为Memory存储引擎的简单测试：

 

       创建Memory表：

mysql> CREATE TABLE memory_test (id INTPRIMARY KEY,name VARCHAR(20)) ENGINE=MEMORY DEFAULTCHARSET=utf8;;

Query OK, 0 rows affected (0.03 sec)

 

在文件系统中，仅保存了表定义文件：

[root@web01 engine]# ls memory*

memory_test.frm

 

在一万条数据的情况下，针对MyISAM和Memory粗略地进行测试，MyISAM全表扫描花了360ms，Memory则只需要10ms。

 

重启服务器后，memory表数据全部丢失，但是表尚且存在：

mysql> select * from item_log_memory;

Empty set (0.00 sec)

 

注意事项：

       ·不支持BLOB和TEXT类型

       ·使用固定长度行存储格式，因此varchar会被转换成char

       ·max_heap_table_size参数决定了Memory表的最大占用内存。

       ·删除Memory表的某几行并不会减少已经分配的内存空间，要想释放内存表占用的空间，需使用DELETE FROM或TRUNCATE，或者DROPTABLE

       ·Memory表不像临时表，它是可以被客户端共享的。

 

CSV存储引擎

       将数据存储在文本文件中，CSV=comma-separatedvalue 用逗号分隔各个列，CSV本质上就是一个CSV文件。创建CSV表时，所有列必须是NOT NULL，不支持索引，不支持分区。除了.frm文件外，CSV还会为表创建.CSV存储数据，.CSM存储表的元数据。以下为CSV存储引擎的简单测试：

 

       创建表，插入数据：

mysql>CREATE TABLE csv_test (id INT NOT NULL,name varchar(20) NOT NULL) ENGINE=CSV DEFAULTCHARSET=utf8;

QueryOK, 0 rows affected (0.00 sec)

 

mysql>INSERT INTO csv_test VALUES (1,'aaa'),(2,'bbb'),(3,'ccc');

QueryOK, 3 rows affected (0.00 sec)

Records:3  Duplicates: 0  Warnings: 0

 

查看.CSV文件，标准的CSV格式文件：

[root@web01engine]# cat csv_test.CSV

1,"aaa"

2,"bbb"

3,"ccc"

 

用vi修改.CSV文件，插入2行数据：

1,"aaa"

2,"bbb"

3,"ccc"

4,"ddd"

5,"eee"

 

MySQL中FLUSH表之后，可以看到刚才插入的数据：

mysql>FLUSH TABLES;

QueryOK, 0 rows affected (0.00 sec)

 

mysql>SELECT * FROM csv_test;

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

|id | name |

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

|  1 | aaa |

|  2 | bbb |

|  3 | ccc |

|  4 | ddd  |

|  5 | eee  |

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

5rows in set (0.00 sec)

Blackhole存储引擎

       就像它的名字——黑洞，所有插入里面的数据都不会保存，和/dev/null一样。虽然不会保存数据，但是会在bin log中记录操作，如果存在slave的话也会同步。因此很适合在主从复制环境下充当一个过滤器的角色。它除了.frm表定义文件之外，不会建立任何文件。

 

       创建测试表，插入数据：

       CREATE TABLE`black_hole_test` (

      `id`int(11) NOT NULL,

      `name`varchar(20) DEFAULT NULL,

   PRIMARY KEY (`id`)

)ENGINE=BLACKHOLE DEFAULT CHARSET=utf8;

 

mysql>INSERT INTO black_hole_test VALUES(1,'first'),(2,'second'),(3,'third');

QueryOK, 3 rows affected (0.00 sec)

Records:3  Duplicates: 0  Warnings: 0

 

查询此表，果然什么都没有保存：

mysql>SELECT * FROM black_hole_test;

Emptyset (0.00 sec)

 

接下来测试是否真的生成了bin log，先看看当前正在使用的日志大小：

[root@web01 var]# du -h mysql-bin.000028

28K     mysql-bin.000028

 

28K，接着，向“黑洞”插入1000条数据：

mysql>INSERT INTO black_hole_test SELECT id,item_id FROM xcb_item_log LIMIT 1000;

QueryOK, 1000 rows affected (0.01 sec)

 

再次查看日志的大小，增加了20k的容量，既然能够生成bin log，就一定可以使slave端同步：

[root@web01 var]# du -h mysql-bin.000028

48K     mysql-bin.000028

Archive存储引擎

       Archive表占用的空间非常小，仅支持INSERT和SELECT操作，使用行锁定。

 

       创建500万行的测试表，分别使用InnoDB、MyISAM和Archive存储引擎，

       在文件系统中查看它们的大小：

[root@web01 engine]# du -shitem_log_innodb.*

12K    item_log_innodb.frm

581M   item_log_innodb.ibd

[root@web01 engine]# du -sh item_log_myisam.*

12K    item_log_myisam.frm

427M   item_log_myisam.MYD

4.0K   item_log_myisam.MYI

[root@web01 engine]# du -shitem_log_archive.*

106M   item_log_archive.ARZ

12K    item_log_archive.frm

 

Archive相比MyISAM和InnoDB分别可以节约75.2%和81.6%的磁盘空间

 

 

Archive

MyISAM

InnoDB

插入速度

40.42 s

16.35 s

54.35 s

全表扫描

10.78 s

7.15 s

11.08 s

数据容量

106 MB

427 MB

581 MB