mysql 开发基础系列11 存储引擎memory和merge介绍

二. merge 存储引擎 

  merge 引擎是一组MyISAM表的组合,这些MYISAM表必须结构完全相同,merge表本身并没有数据,对表的增删改查 实际是对内部的myisam表进行操作。
对于merge类型表的插入操作有三种类型:first是插入在第一个表,last是插入到最后一个表,不定义或为NO表示不能对merge表执行插入操作,对于merge表的drop操作,内部的表没有任何影响。merge 在磁盘上保留两个文件,一个是.frm文件存储表定义,另一个是.mrg文件包含组合表的信息。

-- 下面来测试下,创建三个结构相同的表 payment_2006,payment_2007,payment_all(merge类型)。
CREATE TABLE payment_2006(
 country_id SMALLINT,
 payment_date DATETIME,
 amount DECIMAL(15,2),
 KEY inx_fx_country_id (country_id)
)ENGINE =MYISAM

CREATE TABLE payment_2007(
 country_id SMALLINT,
 payment_date DATETIME,
 amount DECIMAL(15,2),
 KEY inx_fx_country_id (country_id)
)ENGINE =MYISAM

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;

-- 分别向payment_2006和payment_2007表插入数据
INSERT INTO payment_2006 VALUES(1,'2006-05-01',100000),(2,'2006-08-01',150000);
INSERT INTO payment_2007 VALUES(1,'2007-05-01',200000),(2,'2007-08-01',350000);

-- 查询payment_all
SELECT * FROM payment_all; 

  下图发现该paymnet_all表合并了二表的结果集:

图片 1

-- 下面向payment_all表插入数据 表定义是INSERT_METHOD=LAST;
INSERT INTO payment_all VALUES(3,'2006-05-01',112000);
-- 查询
SELECT * FROM payment_2007;

图片 2

  总结: MERGE表并不能智能地将记录写到对应的表中,而分区表是可以的,通常我们使用merge表来透明地对多个表进行查询和更新操作。

InnoDB

  1. InnoDB支持事务安全,对比MyISAM引擎,InnoDB写的效率差一些,并且会占据更多的磁盘空间。
  2. InnoDB自动增长列可以手工插入,但是插入的值是空或者0,则实际插入的将是自动增长后的值。可以使用last_insert_id()查询当前线程最后插入记录使用的值。可以通过alert table *** auto_increment=n;语句强制设置自动增长值。
  3. 对于InnoDB表,自动增长列必须是索引。如果是组合索引,也必须是组合索引的第一列,但是对于MyISAM表,自动增长列可以是组合索引的其他列,这样插入记录后,自动增长列是按照组合索引到前面几列排序后递增的。
  4. MySQL支持外键的存储引擎只有InnoDB,在创建外键的时候,父表必须有对应的索引,子表在创建外键的时候也会自动创建对应的索引。在创建索引的时候,可以指定在删除、更新父表时,对子表进行的相应操作,包括restrict、cascade、set null和no action。其中restrict和no action相同,是指限制在子表有关联的情况下,父表不能更新;casecade表示父表在更新或删除时,更新或者删除子表对应的记录;set null 则表示父表在更新或者删除的时候,子表对应的字段被set null。当某个表被其它表创建了外键参照,那么该表对应的索引或主键被禁止删除。可以使用set foreign_key_checks=0;临时关闭外键约束,set foreign_key_checks=1;打开约束。
  5. InnoDB存储表和索引有如下两种方式:
    • 使用共享表空间存储。
    • 使用多表空间存储。

三..如何选择合适的存储引擎  

  myisam: 如果应用是以读操作和插入操作为主,只有很少的更新和删除操作,并且对事务的完整性,并发性要求不是很高,例如数据仓储。

  innodb: 用于事务处理应用程序,支持外键,对事务的完整性较高,并发条件下数据一致性,包括很多的更新和删除操作,它能避免删除和更新导致的锁定,还提供了提交和回滚,例如计算费用对数据准确性要求高的。

  memory: 数据保存在ram(内存)中,访问速度快,但对表的大小有限制,要确保数据是可以恢复的,常用于更新不太频繁的小表,用以快速访问。

  merge: 它是myisam表以逻辑方式组合的引擎,将myisam表分布在多个磁盘上,可以有效改善merge表的访问效率。例如数据仓储等。

 

MyISAM

  1. 它不支持事务,也不支持外键,其优势是访问的速度快,对事务完整性没有要求的或者以select/insert为主的应用基本上可以使用这个引擎来创建表。

  2. 每个MyISAM在磁盘上都有3个文件,其文件名都与表名相同,但扩展名是:

    • .frm(表定义)
    • .MYD(MYDate:存储数据)
    • .MYI(MYIndex:存储索引)
  3. .MYD文件和.MYI文件可以放置在不同的目录中,通过 data directory 和index directory语句指定。

  4. MyISAM类型的表可能会损坏,可以使用CHECK TABLE语句来检查MyISAM表的健康,并用REPAIR TABLE语句修复一个损坏到MyISAM表。

  5. MyISAM支持3种不同的存储格式:

    • 静态(固定长度)表
    • 动态表
    • 压缩表

    在静态表中,如果需要保存的内容后面本来就带有空格,那么在返回结果的时候会去掉公共的。

    在动态表中,记录不固定,优点是占用空间相对比较少,缺点是频繁的更新和删除记录会产生碎片,需要定期执行optimize table 来改善性能。

    在压缩表中,由myisampack工具创建,占据非常小的磁盘空间。因为每个记录都被单独压缩的。

一. memory存储引擎

  memoery存储引擎是在内存中来创建表,每个memory表只实际对应一个磁盘文件格式是.frm.   该引擎的表访问非常得快,因为数据是放在内存中,且默认是hash索引,但服务关闭,表中的数据就会丢失掉。       

-- 下面创建一个memory表,并从city表获得记录
CREATE TABLE tab_memory ENGINE=MEMORY
SELECT city_id,country_id FROM city GROUP BY city_id

图片 3

-- 给momory 表创建索引时,可以指定是hash索引还是btree索引
CREATE INDEX mem_hash USING HASH ON tab_memory(city_id);
SHOW INDEX FROM tab_memory

图片 4

DROP INDEX mem_hash ON tab_memory;
CREATE INDEX mem_hash USING BTREE ON tab_memory(city_id)
SHOW INDEX FROM tab_memory

图片 5

  总结:服务器需要足够的内存来维护所有在同一时间使用的memory表,当不再需要时,要释放,应执行 delete from 或 truncate table 或删除表drop table。
  每个memory表放置的数据量大小,受到max_heap_table_size系统变量的约束,初始值是16MB. 通过max_rows 子句指定表的最大行数。
  memory类型 一般应用于临时表,如统计操作的中间结果表。

MEMORY

  1. memory使用存在内存中的内容来创建表。每个MEMORY表实际对应一个磁盘文件,格式是.frm。MEMORY类型的表访问非常快,因为它到数据是放在内存中的,并且默认使用HASH索引,但是一旦服务器关闭,表中的数据就会丢失,但表还会继续存在。
  2. 每个MEMORY表中放置到数据量的大小,受到max_heap_table_size系统变量的约束,这个系统变量的初始值是16M,同时在创建MEMORY表时可以使用MAX_ROWS子句来指定表中的最大行数。
  3. memory主要用于那些内容变化不频繁的代码表,或作为统计操作的中间结果表。

总结

仍一张表作为总结:

图片 6

mysql常用存储引擎

概述

mysql5.5之前默认存储引擎是MyISAM,5.5之后改为InnoDB。若要修改默认引擎,可以修改配置文件中的default-storage-engine。可以通过show engines来查看当前数据库支持的引擎。使用select engine from information_schema.engines where transactions = 'yes';来查看哪些引擎支持事务。在创建表到时候通过engine=...或type=...来指定所要使用到引擎。

MERGE

  1. merge存储引擎是一组MyISAM表的组合,这些MyISAM表结构必须完全相同,MERGE表中并没有数据,对MERGE类型的表可以进行查询、更新、删除的操作,这些操作实际上是对内部的MyISAM表进行操作。
  2. 对于对MERGE表进行的插入操作,是根据INSERT_METHOD子句定义的插入的表,可以有3个不同的值,first和last值使得插入操作被相应的作用在第一个或最后一个表上,不定义这个子句或者为NO,表示不能对这个MERGE表进行插入操作。
  3. 可以对MERGE表进行drop操作,这个操作只是删除MERGE表的定义,对内部的表没有任何影响。
  4. MERGE在磁盘上保留2个以MERGE表名开头文件:.frm文件存储表的定义;.MRG文件包含组合表的信息,包括MERGE表由哪些表组成,插入数据时的依据。可以通过修改.MRG文件来修改MERGE表,但是修改后要通过flush table刷新。
  5. merge表与分区表的区别是:merge表不会智能的将记录写到对应的表中,而分区表可以的。

本文由上海时时乐走势图发布于上海时时乐走势图官网,转载请注明出处:mysql 开发基础系列11 存储引擎memory和merge介绍

您可能还会对下面的文章感兴趣: