mysql内部存储_MySQL内部存储代码

MySQL允许通过触发器、存储过程、函数的形式来存储代码。从MySQL5.1开始，还可以在定时任务中存放代码，这个定时任务也被称为“事件”。存储过程和存储函数都被统称为“存储程序”

介绍

这四种存储代码都使用特殊的SQL语句拓展，不同类型的存储代码的主要区别在于其执行的上下文——也就是其输入和输出。存储过程和存储函数都可以接收参数然后返回值，但是触发器和事件不行。

在MySQL中使用存储代码的优点如下：

它在服务器内部执行，离数据近，可以节省带宽并避免网络延迟

这是一种代码重用。它可以很方便的统一业务规则，保证某些行为总是一致，所以也可以为应用提供一定的安全性

它可以简化代码的维护和版本更新

帮助提升安全，比如提供更细粒度的权限控制

服务器端可以缓存存储过程的执行计划，这对于需要反复调用的过程，会大大降低消耗

因为是在服务器端部署的，所以备份、维护都可以在服务器端完成

在MySQL中使用存储代码的缺点如下：

MySQL本身没有提供好用的开发和调试工具，所以编写MySQL的存储代码会比其他的数据库更难

较之应用程序的代码，存储代码效率要稍微差些

存储代码可能会给应用程序代码的部署带来额外的复杂性

因为存储程序都部署在服务器内，所以可能存在安全隐患

存储过程会给数据库服务器带来额外的压力，而数据库服务器的扩展性相比应用服务器要差很多

MySQL并没有什么选项可以控制存储程序的资源消耗，所以存储过程的一个小错误，可能直接把服务器拖死

调试MySQL的存储过程是一件很困难的事情

存储代码是一种帮助应用隐藏复杂性，使得应用开发更简单的方法。不过，它的性能更低，而且可能会给MySQL的复制等增加潜在的危险。

存储过程和函数

MySQL的架构本身和优化器的特性使得存储代码有一些天然的限制，它的性能也一定程度受限于此：

优化器无法使用关键字 DETERMINISTIC 来优化单个查询中多次调用存储函数的情况

优化器无法评估存储函数的执行成本

每个链接都有独立的存储过程执行计划缓存。如果有多个连接需要调用同一个存储过程，将会浪费缓存空间来反复缓存同样的执行计划

存储程序和复制是一组诡异的组合。如果可以，最好不要复制对存储程序的调用。直接复制由存储程序改变的数据会更好。

我们通常会希望存储程序越小、越简单越好。将更加复杂的逻辑交给上层的应用实现，这会使得代码更加易读、易维护也更加灵活。

不过，对于某些操作，存储过程比其他的实现要快得多，特别是当一个存储过程调用可以代替很多小查询的时候。如果查询很小，相比这个查询执行的成本，解析和网络开销就变得非常明显。

我们可以创建一个简单的向数据表中写入数据的存储过程，并进行比较：

对该存储过程执行基准测试，看插入一百万条记录的时间，并和通过客户端程序逐行插入以及使用了MySQL Proxy 连接 MySQL 执行插入操作进行对比：

写入方式总消耗时间(秒)存储过程101

客户端程序279

使用MySQL Proxy 的客户端程序307

可以看到存储过程要快很多，很大程度因为它无需网络通信开销、解析开销和优化器开销等。

触发器

触发器可以让你在执行INSERT、UPDATE或者DELETE的时候，执行一些特定的操作。

触发器本身没有返回值，但是它们可以读取或者改变出发SQL语句所影响的的数据。所以，可以使用触发器实现一些强制限制，或者某些业务逻辑，否则就需要在应用程序中实现这些逻辑。

MySQL触发器的实现非常简单，所以功能也有限。在使用MySQL的时候需要注意，很多时候MySQL触发器的表现和预想的并不一样，需要注意以下几点：

对于每一个表的每一个事件，最多只能定义一个触发器。换句话说，不能在 AFTER INSERT 上定义两个触发器。

MySQL只支持“基于行的触发器”。也就是说，触发器使用都是针对一条记录的，而不是针对整个SQL语句的。如果变更的数据集非常大的话，效率会非常低。

触发器可以掩盖服务器背后的工作，一个简单的SQL语句背后，因为触发器，可能包含了很多看不见的工作。

触发器的问题也很难排查，如果某个性能问题和触发器相关，会很难分析和定位。

触发器可能会导致死锁和锁等待。如果触发器失败，那么原来的SQL语句也会失败。

事件

事件是MySQL5.1 引入的一种新的存储代码的方式。它类似于Linux的定时任务，不过完全是在MySQL内部实现的。我们可以创建事件，指定MySQL在某个时候执行一段SQL代码，或者每隔一个时间间隔执行一段SQL代码。通常会将复杂的SQL封装到一个存储过程中，这样事件在执行的时候只需要做一个简单的CALL调用即可。