[转帖]MySQL存储引擎介绍及应用场景_MySQL, Oracle及数据库讨论区_Weblogic技术|Tuxedo技术|中间件技术|Oracle论坛|JAVA论坛|Linux/Unix技术|hadoop论坛_联动北方技术论坛  
网站首页 | 关于我们 | 服务中心 | 经验交流 | 公司荣誉 | 成功案例 | 合作伙伴 | 联系我们 |
联动北方-国内领先的云技术服务提供商
»  游客             当前位置:  论坛首页 »  自由讨论区 »  MySQL, Oracle及数据库讨论区 »
总帖数
1
每页帖数
101/1页1
返回列表
0
发起投票  发起投票 发新帖子
查看: 2483 | 回复: 0   主题: [转帖]MySQL存储引擎介绍及应用场景        下一篇 
bjly
注册用户
等级:新兵
经验:66
发帖:8
精华:0
注册:1970-1-1
状态:离线
发送短消息息给bjly 加好友    发送短消息息给bjly 发消息
发表于: IP:您无权察看 2014-12-30 11:22:20 | [全部帖] [楼主帖] 楼主

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

 

创建基表:

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

Query OK, 0 rows affected (0.05 sec)

mysql> CREATE TABLE t2(id INT PRIMARY KEY,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: 2Duplicates: 0Warnings: 0

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

Query OK, 2 rows affected (0.02 sec)

Records: 2Duplicates: 0Warnings: 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 INT PRIMARY 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]# cat merge_t1_t2.MRG

t1

t2

#INSERT_METHOD=LAST

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

 

创建表t3,插入2条数据:

 mysql> CREATE TABLE t3(id INT PRIMARY KEY,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: 2Duplicates: 0Warnings: 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 -uroot flush-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 -uroot flush-tables;

flush操作没有报错

试试看查询

 mysql> select * from merge_t1_t2;

ERROR 1168 (HY000): Unable to open underlying table which is differently defined or of non-MyISAM type or doesn't exist

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

 

注意事项:

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

·Merge表基于UNION运算,和分区表相比性能还是有差距的。 www.it165.net

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

 

创建Memory表:

 mysql> CREATE TABLE memory_test (id INT PRIMARY KEY,name VARCHAR(20)) ENGINE=MEMORY DEFAULT CHARSET=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,或者DROP TABLE

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

 

CSV存储引擎 将数据存储在文本文件中,CSV=comma-separated value 用逗号分隔各个列,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 DEFAULT CHARSET=utf8;

Query OK, 0 rows affected (0.00 sec)

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

Query OK, 3 rows affected (0.00 sec)

Records: 3Duplicates: 0Warnings: 0

查看.CSV文件,标准的CSV格式文件:

 [root@web01 engine]# 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;

Query OK, 0 rows affected (0.00 sec)

mysql> SELECT * FROM csv_test;

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

| id | name |

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

|1 | aaa|

|2 | bbb|

|3 | ccc|

|4 | ddd|

|5 | eee |

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

5 rows 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');

Query OK, 3 rows affected (0.00 sec)

Records: 3Duplicates: 0Warnings: 0

查询此表,果然什么都没有保存:

 mysql> SELECT * FROM black_hole_test;

Empty set (0.00 sec)

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

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

28Kmysql-bin.000028

28K,接着,向“黑洞”插入1000条数据:

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

Query OK, 1000 rows affected (0.01 sec)

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

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

48Kmysql-bin.000028

Archive存储引擎 Archive表占用的空间非常小,仅支持INSERT和SELECT操作,使用行锁定。

 

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

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

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

12Kitem_log_innodb.frm

581Mitem_log_innodb.ibd

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

12Kitem_log_myisam.frm

427Mitem_log_myisam.MYD

4.0Kitem_log_myisam.MYI

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

106Mitem_log_archive.ARZ

12Kitem_log_archive.frm

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

40.42 s

16.35 s

54.35 s

全表扫描

10.78 s

7.15 s

11.08 s

数据容量

106 MB

427 MB

581 MB

--转自 北京联动北方科技有限公司




赞(0)    操作        顶端 
总帖数
1
每页帖数
101/1页1
返回列表
发新帖子
请输入验证码: 点击刷新验证码
您需要登录后才可以回帖 登录 | 注册
技术讨论