MySQL事件调度器的基本使用方法

  根据MySQL的官方文档,从MySQL的5.1.6版本开始,MySQL支持了事件调度器,用于处理事件的调度与执行。触发器用于根据DML操作来触发事件,而事件调度器则是定时触发事件,功能类似于Linux的crontab计划任务,但是控制更为精确。在MySQL支持这项功能之前,往往通过Linux的crontab来辅助进行定时任务的触发,而当我们对于任务有更高的定时要求时,或者考虑调用接口处的性能瓶颈时,就需要使用到事件调度器(Event Scheduler)。

  首先确保数据库选项event_scheduler处于开启状态。

 

show variables like 'event_scheduler';

  如果我们看到ON,就表示这个功能处于开启状态,如果为OFF,表示关闭,DISABLED表示禁用。请注意,在数据库实例启动的情况下,我们可以随时在on和off间进行切换,但是,无法从on或off切换到disabled,也无法从disabled切换到on或off。只有在数据库关闭的状态下,这种转换才会生效。(DISABLED状态是在5.1.12版本中引入的)

 

set @@global.event_scheduler=on;

  设置完成后,事件调度器就被开启了,实质上,是在MySQL实例中新建了一个事件调度器线程,通过show processlist\G命令,可以找到一个User为event_scheduler的线程,就表示事件调度器现在可以使用了。

  接着我们随便创建一个存储过程,比如往某个表插入一条记录,好让事件调度器进行调度。

 

delimiter //
create procedure `do_something`()
begin

insert into timelog values ();

end; //
delimiter ;

  创建完毕后,我们就开始创建事件,事件最重要的部分有以下几个:

  • 事件名称,这个很简单,就是给事件起个名字,加上IF NOT EXISTS的话,如果给定名称的事件已经存在了就不会再创建,并且只抛出一个Warning。
  • 调度时间(ON SCHEDULE),可以定义事件被触发的具体时间,也可以指定事件被触发的周期,如果指定周期性任务,还可以指定开始时间和结束时间。
  • 事件完成后的行为(ON COMPLETION),默认为NOT PRESERVED,即事件完成以后删除任务本身,注意,对于周期性的任务,不表示事件只做一次,而是指到达结束时间后,再删除这个事件。可以将它指定为PRESERVED,那么当事件完成以后,不会被删除。
  • 事件体,就是事件具体需要做什么,定义方式和存储过程类似。

 

CREATE EVENT IF NOT EXISTS `log_every_minute`
ON SCHEDULE EVERY 1 MINUTE
ON COMPLETION PRESERVE
DO
CALL do_something();

  在event_scheduler为on的情况下,事件一经创建就会生效。

  这样,我们就成功创建了一个事件,每分钟会在timelog表中插入一条记录,当然,我们可以根据自己的需要,让MySQL去做一些更为复杂的任务。

MySQL Truncate与外键限制

  从数据库的概念上说,Truncate操作是对于数据表的截断操作,即简单地调用文件系统的截断操作系统调用来实现表的清空操作。在基于MyISAM引擎的MySQL数据库上,Truncate始终如此操作,因此Truncate结果虽然从基本等效于无where限定的Delete语句(除了Truncate会将自增字段值重置为0以外),不过效率却高得多。

  但是由于InnoDB存储引擎支持外键,Truncate操作就不是那么简单,因为外键是数据完整性的限定,如果对数据文件进行简单的截断操作,将有可能破坏其外键约束完整性。

  因此,在MySQL 5.1(及以下版本)中Truncate一张InnoDB表时,如果该表存在外键约束,那么实际的操作也等同于无where限定的Delete语句,也就是说,MySQL将逐条删除记录,并检查该次删除操作是否可能违反外键操作,如果没有违反约束的情况出现,那么表被顺利清空,自增字段值被重置为0;一旦出现违反外键约束,那么分为两种情况:

  1. 该外键设定了ON DELETE CASCADE,那么MySQL会同时删除参照表中的相应记录。
  2. 如果该外键没有设定ON DELETE CASCADE,那么MySQL将停止Truncate操作并报错(Error 1451)。

  而在MySQL 5.5中,存在外键约束的InnoDB表,在任何情况下都不允许进行Truncate操作,并报错(Error 1701)。因此,为了向后兼容性的考虑,官方也建议,即使使用MySQL 5.1,也尽量在InnoDB引擎下使用Delete语句代替Truncate语句。

MySQL InnoDB等待锁超时错误

当一个事务在请求某个资源时,它将待这个资源被解锁后继续操作,或者停止等待并返回错误:

ERROR HY000: Lock wait timeout exceeded; try restarting transaction

事务将为一个资源解锁等待多长的时间,取决于innodb_lock_wait_timeout参数的设定,默认设置为50秒,最小允许值为1秒,如果将这个值设定为100000000以上的话,将禁用超时(无论等待多久都不会返回超时错误)。如果因为超时而返回了错误,那么当前SQL语句将会被回滚(在MySQL 5.0.12及以前的版本中,整个事务都将直接被回滚),用户程序可以再次尝试执行这条语句直到成功(通常会等一段时间再重试这个SQL语句),或者回滚整个事务并重新开始。

在InnoDB插件1.0.2之前,更改innodb_lock_wait_timeout选项的唯一方法是修改my.cnf或者my.ini配置文件中的相应选项,并重新启动数据库实例。

在InnoDB插件1.0.2及之后的版本中,还可以在MySQL实例运行时使用SET GLOBAL或者SET SESSION命令来更改innodb_lock_wait_timeout选项。更改GLOBAL设置需要SUPER权限,更改会影响所有在此更改之后新创建的的客户端Session,更改SESSION则只对当前客户端Session有效。

审慎而明智地使用SQL触发器

 

  触发器可以说是数据库中的常用工具,不过,这个功能是否被合理利用,也在一定程度上影响了最终成品的各方面性能。

 

  要想把说明触发器的适用范围,就得先说说触发器本身具有哪些特点:

  • 各种DBMS实现不同的触发器语法,方言之间的差异很大,表达能力上也不尽相同。
  • 触发器是对于一张表的监视,从属于某一张表(一个关系),但是被触发时可以作用于其他表。
  • 从逻辑上,触发器对于程序员应当是透明的。

  基于触发器的上述特点,并站在一个软件全局的角度来看,触发器应该保持对于开发人员的透明性,也就是说,开发人员应当始终不需要知道某个触发器正在对业务逻辑产生影响。

 

  最好的做法就是,使用触发器时,避免使其影响业务逻辑本身,曾经在教科书上看到这样一个例子:

  有一个学生选课情况表,字段有学号、课程号、平时成绩、考试成绩、总评成绩。需要设计一个触发器,当学生的平时成绩或考试成绩修改时,就自动按照平时成绩20%和考试成绩80%的比例计算出总评成绩。

  实际上这应该是个反面教材,这个使用情形就是刚才所说的触发器干涉了业务逻辑。那么为什么不要让触发器干涉业务逻辑呢?

 

  开发人员站在整个数据库的外侧,也就是关注到数据库的外模式,而不关心或无权过问概念模式和内模式,触发器属于概念模式范畴(甚至可能是更低的层次),不需要被开发人员所了解。那么,触发器对于业务逻辑的干涉将使开发人员对于结果产生困惑,为什么开发人员在看似不完整的数据操作之后(修改了平时成绩或考试成绩后没有修改总评成绩),却得到了完整的结果。这个说法建立在表“充当”外模式使用的前提下。

 

  另一方面,触发器带来了程序员无法控制和了解的业务逻辑,使得软件测试最终变得困难重重,如果有一个BUG出现在触发器中,那么它可能将最后才能被发现,此时已经带来了高额的测试和调试成本。对于数据库管理员也是如此,由于存储过程和触发器处于DBMS三层模型中的不同位置,所以当检查外模式的存储过程的业务逻辑时,也同样会产生不必要的困惑。更严重的情况是,一旦存储过程与相关的触发器单方面进行了改动,最坏的情况可以导致业务崩溃,因为不同层级之间的高耦合带来难以预料的维护上的灾难。

 

  对于上面这个简单的问题,其实使用视图就可以很容易地解决,总评成绩=平时成绩×20%+考试成绩×80%,这样的简单关系通过视图,也不会显著产生效率问题,因为计算不涉及多表。对于涉及许多表而可能影响效率的情况,可以采用存储过程来包装对于平时成绩或者考试成绩的变更,当然对于支持物化视图的DBMS,也可能是不错的选择。

 

  教材上还有一个例子,也针对上面那张表:

  设计一个触发器,当删除一条学生的选课记录时,自动在另一张与此表结构一致的备份表中插入这条被删除的记录。

  这个例子可以被认为是正确地使用了触发器,它与业务逻辑无关了,即使这个触发器不存在,业务也可以照常进行下去,因为备份表对于开发人员而言实际上也是透明的,只是基于数据可靠性和系统性能考虑而增加的关系,与触发器搭配使用再恰当不过。

 

  另一个使用触发器的场合是统计信息,例如记录用户登录失败的操作、记录用户的操作习惯等等。

 

  但是记得别再一张表上建立太多的触发器哦,否则这张被无数触发器监视起来的表,一旦需要执行DML操作,性能就严重受到影响了。