mysql从头学一 1.1存储引擎 MEMORY 和MERGE

最新推荐文章于 2021-12-02 22:08:00 发布

Arno-wei

最新推荐文章于 2021-12-02 22:08:00 发布

阅读量212

点赞数

分类专栏： mysql

本文链接：https://blog.csdn.net/insis_mo/article/details/83652182

版权

mysql 专栏收录该内容

35 篇文章 1 订阅

订阅专栏

MEMORY 存储引擎使用存在内存中的内容来创建表。每个 MEMORY 表只实际对应一个磁盘文件，格式是.frm。MEMORY 类型的表访问非常得快，因为它的数据是放在内存中的，并且默认使用 HASH 索引，但是一旦服务关闭，表中的数据就会丢失掉。

下面例子创建了一个 MEMORY 的表，并从 city 表获得记录：

mysql> CREATE TABLE tab_memory ENGINE=MEMORY

-> SELECT city_id,city,country_id

-> FROM city GROUP BY city_id;

Query OK, 600 rows affected (0.06 sec)

Records: 600 Duplicates: 0 Warnings: 0

mysql> select count(*) from tab_memory;

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

| count(*) |

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

| 600 |

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

1 row in set (0.00 sec)

mysql> show table status like 'tab_memory' \G

*************************** 1. row ***************************

Name: tab_memory

Engine: MEMORY

Version: 10

Row_format: Fixed

Rows: 600

Avg_row_length: 155

Data_length: 127040

Max_data_length: 16252835

Index_length: 0

Data_free: 0

Auto_increment: NULL

Create_time: NULL

Update_time: NULL

Check_time: NULL

Collation: gbk_chinese_ci

Checksum: NULL Create_options:

Comment:

1 row in set (0.00 sec)

给 MEMORY 表创建索引的时候，可以指定使用 HASH 索引还是 BTREE 索引：

mysql> create index mem_hash USING HASH on tab_memory (city_id) ;

Query OK, 600 rows affected (0.04 sec)

Records: 600 Duplicates: 0 Warnings: 0

mysql> SHOW INDEX FROM tab_memory \G

*************************** 1. row ***************************

Table: tab_memory

Non_unique: 1

Key_name: mem_hash

Seq_in_index: 1

Column_name: city_id

Collation: NULL Cardinality: 300

Sub_part: NULL

Packed: NULL Null:

Index_type: HASH Comment:

1 row in set (0.01 sec)

mysql> drop index mem_hash on tab_memory; Query OK, 600 rows affected (0.04 sec)

Records: 600 Duplicates: 0 Warnings: 0

mysql> create index mem_hash USING BTREE on tab_memory (city_id) ;

Query OK, 600 rows affected (0.03 sec)

Records: 600 Duplicates: 0 Warnings: 0

mysql> SHOW INDEX FROM tab_memory \G

*************************** 1. row ***************************

Table: tab_memory

Non_unique: 1

Key_name: mem_hash

Seq_in_index: 1

Column_name: city_id Collation: A

Cardinality: NULL

Sub_part: NULL

Packed: NULL Null: Index_type: BTREE

Comment:

1 row in set (0.00 sec)

在启动 MySQL 服务的时候使用--init-file 选项，把 INSERT INTO ... SELECT 或 LOAD DATA

INFILE 这样的语句放入这个文件中，就可以在服务启动时从持久稳固的数据源装载表。服务器需要足够内存来维持所有在同一时间使用的 MEMORY 表，当不再需要 MEMORY

表的内容之时，要释放被 MEMORY 表使用的内存，应该执行 DELETE FROM 或 TRUNCATE TABLE，或者整个地删除表（使用 DROP TABLE 操作）。

每个 MEMORY 表中可以放置的数据量的大小，受到 max_heap_table_size 系统变量的约束，这个系统变量的初始值是 16MB，可以按照需要加大。此外，在定义 MEMORY 表的时候，可以通过 MAX_ROWS 子句指定表的最大行数。

MEMORY 类型的存储引擎主要用在那些内容变化不频繁的代码表，或者作为统计操作的中间结果表，便于高效地对中间结果进行分析并得到最终的统计结果。对 MEMORY 存储引擎的表进行更新操作要谨慎，因为数据并没有实际写入到磁盘中，所以一定要对下次重新启动服务后如何获得这些修改后的数据有所考虑。

7.2.4 MERGE

MERGE 存储引擎是一组 MyISAM 表的组合，这些 MyISAM 表必须结构完全相同，MERGE 表本身并没有数据，对 MERGE 类型的表可以进行查询、更新、删除的操作，这些操作实际上是对内部的实际的 MyISAM 表进行的。对于 MERGE 类型表的插入操作，是通过

INSERT_METHOD 子句定义插入的表，可以有 3 个不同的值，使用 FIRST 或 LAST 值使得插入操作被相应地作用在第一或最后一个表上，不定义这个子句或者定义为 NO，表示不能对这个 MERGE 表执行插入操作。

可以对 MERGE 表进行 DROP 操作，这个操作只是删除 MERGE 的定义，对内部的表没有

任何的影响。

MERGE 表在磁盘上保留两个文件，文件名以表的名字开始，一个.frm 文件存储表定义，另一个.MRG 文件包含组合表的信息，包括 MERGE 表由哪些表组成、插入新的数据时的依据。

可以通过修改.MRG 文件来修改 MERGE 表，但是修改后要通过 FLUSH TABLES 刷新。下面是一个创建和使用 MERGE 表的例子。

（1）创建 3 个测试表 payment_2006、payment_2007 和 payment_all，其中 payment_all 是前两个表的 MERGE 表：

mysql> create table payment_2006( -> country_id smallint,

-> payment_date datetime,

-> amount DECIMAL(15,2),

-> KEY idx_fk_country_id (country_id)

-> )engine=myisam;

Query OK, 0 rows affected (0.03 sec)

mysql> create table payment_2007( -> country_id smallint,

-> payment_date datetime,

-> amount DECIMAL(15,2),

-> KEY idx_fk_country_id (country_id)

-> )engine=myisam;

Query OK, 0 rows affected (0.02 sec)

mysql> CREATE TABLE payment_all( -> country_id smallint,

-> payment_date datetime,

-> amount DECIMAL(15,2),

-> INDEX(country_id)

-> )engine=merge union=(payment_2006,payment_2007) INSERT_METHOD=LAST;

Query OK, 0 rows affected (0.04 sec)

分别向 payment_2006 和 payment_2007 表中插入测试数据：

mysql> insert into payment_2006 values(1,'2006-05-01',100000),(2,'2006-08-15',150000);

Query OK, 2 rows affected (0.00 sec)

Records: 2 Duplicates: 0 Warnings: 0

mysql> insert into payment_2007 values(1,'2007-02-20',35000),(2,'2007-07-15',220000);

Query OK, 2 rows affected (0.00 sec)

Records: 2 Duplicates: 0 Warnings: 0

分别查看这 3 个表中的记录：

mysql> select * from payment_2006;

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

| country_id | payment_date | amount |

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

| 1 | 2006-05-01 00:00:00 | 100000.00 |

| 2 | 2006-08-15 00:00:00 | 150000.00 |

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

2 rows in set (0.00 sec)

mysql> select * from payment_2007;

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

| country_id | payment_date | amount |

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

| 1 | 2007-02-20 00:00:00 | 35000.00 |

| 2 | 2007-07-15 00:00:00 | 220000.00 |

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

2 rows in set (0.00 sec)

mysql> select * from payment_all;

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

| country_id | payment_date | amount |

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

| 1 | 2006-05-01 00:00:00 | 100000.00 |

| 2 | 2006-08-15 00:00:00 | 150000.00 |

| 1 | 2007-02-20 00:00:00 | 35000.00 |

| 2 | 2007-07-15 00:00:00 | 220000.00 |

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

4 rows in set (0.00 sec)

可以发现，payment_all 表中的数据是 payment_2006 和 payment_2007 表的记录合并后的结果集。

下面向 MERGE 表插入一条记录，由于 MERGE 表的定义是 INSERT_METHOD=LAST，就会向最后一个表中插入记录，所以虽然这里插入的记录是 2006 年的，但仍然会写到 payment_2007 表中。

mysql> insert into payment_all values(3,'2006-03-31',112200); Query OK, 1 row affected (0.00 sec)

mysql> select * from payment_all;

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

| country_id | payment_date | amount |

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

| 1 | 2006-05-01 00:00:00 | 100000.00 |

| 2 | 2006-08-15 00:00:00 | 150000.00 |

| 1 | 2007-02-20 00:00:00 | 35000.00 |

| 2 | 2007-07-15 00:00:00 | 220000.00 |

| 3 | 2006-03-31 00:00:00 | 112200.00 |

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

5 rows in set (0.00 sec)

mysql> select * from payment_2007;

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

| country_id | payment_date | amount |

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

| 1 | 2007-02-20 00:00:00 | 35000.00 |

| 2 | 2007-07-15 00:00:00 | 220000.00 |

| 3 | 2006-03-31 00:00:00 | 112200.00 |

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

3 rows in set (0.00 sec)

这也是 MERGE 表和分区表的区别，MERGE 表并不能智能地将记录写到对应的表中，而分区表是可以的（分区功能在 5.1 版中正式推出）。通常我们使用 MERGE 表来透明地对多个表进行查询和更新操作，而对这种按照时间记录的操作日志表则可以透明地进行插入操作。

7.3 如何选择合适的存储引擎

在选择存储引擎时，应根据应用特点选择合适的存储引擎，对于复杂的应用系统可以根据实际情况选择多种存储引擎进行组合。

下面是常用存储引擎的适用环境。

- - MyISAM：默认的 MySQL 插件式存储引擎。如果应用是以读操作和插入操作为主，只有很少的更新和删除操作，并且对事务的完整性、并发性要求不是很高，那么选择这个存储引擎是非常适合的。MyISAM 是在 Web、数据仓储和其他应用环境下最常使用的存储引擎之一。
  - InnoDB：用于事务处理应用程序，支持外键。如果应用对事务的完整性有比较高的要求，在并发条件下要求数据的一致性，数据操作除了插入和查询以外，还包括很多的更新、删除操作，那么 InnoDB 存储引擎应该是比较合适的选择。InnoDB 存储引擎除了有效地降低由于删除和更新导致的锁定，还可以确保事务的完整提交（Commit）和回滚（Rollback），对于类似计费系统或者财务系统等对数据准确性要求比较高的系统，InnoDB 都是合适的选择。
  - MEMORY：将所有数据保存在 RAM 中，在需要快速定位记录和其他类似数据的环境下，可提供极快的访问。MEMORY 的缺陷是对表的大小有限制，太大的表无法 CACHE 在内存中，其次是要确保表的数据可以恢复，数据库异常终止后表中的数据是可以恢复的。

MEMORY 表通常用于更新不太频繁的小表，用以快速得到访问结果。

- - MERGE：用于将一系列等同的 MyISAM 表以逻辑方式组合在一起，并作为一个对象引用它们。MERGE 表的优点在于可以突破对单个 MyISAM 表大小的限制，并且通过将不同的表分布在多个磁盘上，可以有效地改善MERGE表的访问效率。这对于诸如数据仓储等VLDB 环境十分适合。

注意：以上只是我们按照实施经验提出的关于存储引擎选择的一些建议，但是不同应用的特点是千差万别的，选择使用哪种存储引擎才是最佳方案也不是绝对的，这需要根据用户各自的应用进行测试，从而得到最适合自己的结果。

Arno-wei

关注

0
点赞
踩
0

收藏

觉得还不错? 一键收藏
0
评论
mysql从头学一 1.1存储引擎 MEMORY 和MERGE

MEMORY 存储引擎使用存在内存中的内容来创建表。每个 MEMORY 表只实际对应一个磁盘文件，格式是.frm。MEMORY 类型的表访问非常得快，因为它的数据是放在内存中的，并且默认使用 HASH 索引，但是一旦服务关闭，表中的数据就会丢失掉。下面例子创建了一个 MEMORY 的表，并从 city 表获得记录： mysql&gt; CREATE TABLE tab_memor...
复制链接

扫一扫

专栏目录