引擎mysql触发器_基于MySQL存储引擎加触发器的应用场景

需求解读：某限时特卖B2C网站技术总负责人想采用一种简单、开发成本极低且成熟可靠，能满足前期针对特卖信息快速处理的支持，且要求不采用第三方MemCache或者编写程序操作内存的方案，考虑到其业务特点：特卖期限高峰，不超过5000个特卖信息，且需要频繁、快速修改的数据全为数值类型，且每条记录可控制在100字节以内，为此建议其前期考虑使用MySQL支持的Memory引擎解决响应速度，外加触发器的模式解决数据的安全性。基于此需求而编写此篇技术文章，以帮助更多开发者，了解Memory引擎及触发器的功效，多一种解决方案。

一.知识点：

1.存储引擎MEMORY知识点

1.1 MEMORY支持的特性

引擎限制事务不支持锁类型表级别

多版本控制不支持HASH索引支持全文索引不支持

B树索引支持数据缓存N/A索引缓存N/A

族索引不支持数据加密支持数据库集群不支持

数据压缩不支持外键布支持备份/基于时间点恢复支持

复制不支持统计信息更新支持

查询缓存支持

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 索引的支持

支持B-tree和HASH索引，每张MEMORY引擎表支持最多创建32个索引，每个索引允许最多包含16个字段，索引最大长度：500字节;

1.5 存储空间范围

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

二.触发器的知识点

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服务直接KILL掉操作系统级别的进程及保护进程(备注：先KILL掉保护进程)

8>.重新启动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引擎;

我们一直都在努力坚持原创.......请不要一声不吭，就悄悄拿走。

我原创，你原创，我们的内容世界才会更加精彩！

【所有原创内容版权均属TechTarget，欢迎大家转发分享。但未经授权，严禁任何媒体(平面媒体、网络媒体、自媒体等)以及微信公众号复制、转载、摘编或以其他方式进行使用。】

微信公众号

TechTarget

官方微博

TechTarget中国