mysql 的 memory 引擎介绍

Mysql 数据库 memory引擎简介

 

使用场景：需要高速的动态的查询数据库，相比于memcached它的动态性更强。

 

如何查看自己当前的数据库的引擎。

mysql> show engines；

结果在附件中.

 

  HEAP简介：(heap 其实就是memory引擎。)

HEAP允许只驻留在内存里的临时表格。驻留在内存里让HEAP要比MYISAM快，但是它所管理的数据是不稳定的，而且如果在关机之前没有进行保存，那么所有的数据都会丢失。在数据行被删除的时候，HEAP也不会浪费大量的空间。HEAP表格在你需要使用SELECT表达式来选择和操控数据的时候非常有用。

                  

特性：

         1.2 MEMORY引擎的存储特性

MEMORY引擎的数据及索引数据都存储于内存中，为此文件系统只会有一个单独的表定义文件，例如: MEMMORY引擎的表：t_memory，在数据库目录下只有：t_memory.frm文件，正是由于其所有数据都存储于内存中，除表定义信息有对应的实体文件存储于磁盘上外，只要mysqld服务不存在，则表中的数据全部丢失。

　　对MEMORY引擎表做DELETE删除数据的操作，并不是真正删除，而是标记为删除状态，只有新记录INSERT且写入同一张表才可重用，另外重建、重置、删除表、mysqld服务重启，才会释放掉被删除数据所占的内存。

　　1.3 数据类型的支持

　　1>.除BLOB、TEXT及二者的变种外，其他类型都支持;

　　2>.采用固定长度字段类型，即VARCHAR(50) 等同于CHAR(50);

　　3>.支持创建自增序列的字段;

　　4>.字段允许为NULL，同时索引也允许包含为NULL类型的字段;

　　1.4 索引的支持

　　1.5 存储空间范围   MEMORY存储引擎拥有的存储空间，取决于设置全局变量：max_heap_table_size的值为多大。

 使用：

CREATE TABLE t (i INT) ENGINE = MYISAM;
注释：老版本的MySQL使用TYPE而不是ENGINE（例如，TYPE = MYISAM）。MySQL 5.1为向下兼容而支持这个语法，但TYPE现在被轻视，而ENGINE是首先的用法。

 测试：

二.触发器的知识点

　　2.1 语法

　　当通过SQL(INSERT、UPDATE、DELETE)语句使表中的数据发生变化，能捕获到此变化的程序，即是我们常用到的触发器，是一种类似于存储过程，而具有特殊作用的程序。

　　2.1.1创建

　　CREATE [DEFINER = { user | CURRENT_USER }]

　　TRIGGER trigger_name trigger_time trigger_event

　　ON tbl_name FOR EACH ROW trigger_stmt

　　2.1.2 删除

　　DROP TRIGGER [IF EXISTS] [schema_name.]trigger_name

　　2.2 限制

　　1>.一个数据库中不能有同名的触发器程序;

　　2>.同一表上，对同一个事件的响应处理不能有二个及以上的触发器;

　　3>.触发器无法显示调用执行，也无法像函数或存储过程一样显示地传递参数;

　　4>.通过关键字OLD.column_name获得的值不能通过SET命令修改，但是关键字NEW获得的值能通过SET NEW.column_name=VALUE方式修改;

　　5>.触发器的处理部分不能含有事务的关键字，例如：ROLLBACK等;

　　6>.创建了触发器的表，若支持事务，则触发器也会受事务执行成功还是失败的影响，且触发器程序执行成功还是失败，也会影响事务的执行是成功还是失败;若不支持事务，则也无法支持事务的回滚操作;

　　三.模拟的业务场景

　　1.对某表中部分符合规定要求的数据UPDATE业务操作量大，且要求响应时间短;

　　2.确保数据的安全性;

　　3.为满足上述要求，且不增加额外的开发成本，需要从非MEMORY引擎表中读取数据到MEMORY引擎表中;

　　4.对MEMORY引擎表中的数据，只有UPDATE业务操作;

　　5.通过触发器的方式，更新InnoDB引擎表中的数据;

　　测试过程如下：

　　1>.创建用于测试2张表，存储引擎分别为：MEMORY、InnoDB

CREATE TABLE `t_memory` (

  `id` INT UNSIGNED NOT NULL AUTO_INCREMENT,

  `username` VARCHAR(40) NOT NULL,

  PRIMARY KEY(`id`),

  UNIQUE KEY `idx_username` (`username`)

) ENGINE=MEMORY DEFAULT CHARSET=utf8  COLLATE 'utf8_general_ci';

 

 CREATE TABLE `t_innodb` (

  `id` INT UNSIGNED NOT NULL AUTO_INCREMENT,

  `username` VARCHAR(40) NOT NULL,

  PRIMARY KEY  (`id`),

  UNIQUE KEY `idx_username` (`username`)

) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE 'utf8_general_ci';

　　2>.添加几条测试数据

INSERT INTO t_innodb(username) value('a'),('asdfasdf'),('q235423573sdf'),('afdhfhswqertqw');

INSERT INTO t_memory SELECT * from t_innodb;

　　3>.创建基于MEMORY引擎的表上的更新触发器

DELIMITER &&

CREATE TRIGGER tri_memory_update AFTER UPDATE ON t_memory FOR EACH ROW

BEGIN 

    UPDATE t_innodb SET username=NEW.username WHERE username=OLD.username;

END &&

DELIMITER ;

　　4>.对比2张表中的数据

root@localhost : test 03:58:25> select * from t_memory;

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

| id | username       |

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

|  1 | a              | 

|  2 | asdfasdf       | 

|  3 | q235423573sdf  | 

|  4 | afdhfhswqertqw | 

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

4 rows in set (0.00 sec)

 

root@localhost : test 03:58:32> select * from t_innodb;

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

| id | username       |

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

|  1 | a              | 

|  4 | afdhfhswqertqw | 

|  2 | asdfasdf       | 

|  3 | q235423573sdf  | 

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

4 rows in set (0.00 sec)

　　5>.对MEMORY引擎表执行更新模拟操作

　　UPDATE t_memory SET username='769067806dfgh' WHERE ID=1;

　　6>.更新之后的数据对比

root@localhost : test 03:58:25> select * from t_memory;

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

| id | username       |

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

|  1 | 769067806dfgh  | 

|  2 | asdfasdf       | 

|  3 | q235423573sdf  | 

|  4 | afdhfhswqertqw | 

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

4 rows in set (0.00 sec)

root@localhost : test 03:58:32> select * from t_innodb;

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

| id | username       |

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

|  1 | 769067806dfgh  | 

|  4 | afdhfhswqertqw | 

|  2 | asdfasdf       | 

|  3 | q235423573sdf  | 

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

4 rows in set (0.00 sec)

 

　　7>.重新启动mysqld服务，然后查看对比2张表中的数据

root@localhost : test 04:10:05> select * from t_innodb;

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

| id | username       |

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

|  1 | 769067806dfgh  | 

|  4 | afdhfhswqertqw | 

|  2 | asdfasdf       | 

|  3 | q235423573sdf  | 

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

4 rows in set (0.02 sec)

root@localhost : test 04:10:12> select * from t_memory;

Empty set (0.00 sec)

　　若是采用MEMORY引擎表支持在线业务，另外再使用触发器或者类似触发器的其他程序完成数据同步到实体表，那么从MEMORY引擎特点、业务等角度提出以下建议：

　　1>.MEMORY引擎存储表中，只使用数值类型、日期类型的字段且为TIMESTAMP类型，不要使用字符串类型;

　　2>.删除MEMORY引擎表中的数据之后，使用语句ALTER TABLE tablename ENGINE=MEMORY释放掉被删除数据而占用的内存;

　　3>.只使用MEMORY引擎表支持UPDATE操作业务;

　　4>.对MEMORY引擎表的数据操作，最好是根据主键去完成，以便最快速度完成操作，而不影响其他线程的操作;

　　5>.不要是用MEMORY引擎表支持数据量过大的业务，比如数据量1G以上;

　　6>.要定期清理MEMORY引擎表中不需要的数据，以便腾出内存;

　　7>.参数max_heap_table_size的值要设置合理，考虑系统资源及数据量可能有多大;

　　8>.MEMORY引擎不支持事务，为此使用触发器完成数据同步的工作，要考虑响应操作的时间点(BEFOR/AFTER)，由于操作可能会很频繁，实体表建议使用支持事务的引擎，比如：InnoDB引擎;