mysql 触发器 二进制_MySQL中触发器与存储过程的性能

上下文是我们要在表中为每个更新的行插入记录.在更新行之前,我们希望存储先前的值,然后递增其中一列(“版本”列).

如果我们在触发器内执行此操作,则可以很好地完成.对于MySQL,触发器是row by row,因此它将是一个简单的解决方案.选择表中当前的数据,将其插入日志记录表,然后更新新数据中的“version”列.

但是,可以将此逻辑移动到存储过程.如果这样做,则执行插入,然后递增表中的“版本”列.整个事情将基于设置.

那么,当涉及到执行此插入时,使用基于集合的存储过程方法或基于触发器的方法会更高效吗？

这个问题适用于MySQL(因为它具有逐行触发器),尽管它可以应用于其他逐行触发DBMS.

解决方法:

为简单起见,触发器是实现任何类型的数据库更改跟踪的方法.但是,您需要了解使用触发器时引擎盖下发生的情况.

根据MySQL Stored Procedure Programming,头部“Trigger Overhead”下的第256页说明如下：

It is important to remember that, by necessity, triggers add overhead

to the DML statement to which they apply. the actual amount of overhead

will depend upon the nature of the trigger, but — as all MySQL

triggers execute FOR EACH ROW — the overhead can rapidly accumulate

for statements that process large numbers of rows. You should

therefore avoid placing any expensive SQL statements or procedural

code in triggers.

有关触发开销的扩展说明,请参见第529-531页.该部分的结论点如下：

The lesson here is this: since the trigger code will execute once

for every row affected by a DML statement, the trigger can easily

become the most significant factor in DML performance. Code inside the

trigger body needs to be as lightweight as possible and — in

particular — any SQL statements in the trigger should be supported by

indexes whenever possible.

本书中未提及使用触发器时的另一个因素：在审计日志记录方面,请注意您将数据记录到哪些内容.我这样说是因为如果你选择登录到MyISAM表,每次INSERT进入MyISAM表会在INSERT期间产生一个完整的表锁.这可能成为高流量,高交易环境中的严重瓶颈.此外,如果触发器针对InnoDB表,并且您在触发器内记录MyISAM中的更改,则会秘密禁用ACID合规性(即,将块事务减少为自动提交行为),这是无法回滚的.

在InnoDB表上使用触发器并记录更改时

>您登录的表也是InnoDB

>您已关闭自动提交

>您彻底设置了START TRANSACTION … COMMIT / ROLLBACK块

通过这种方式,审计日志可以像主表一样受益于COMMIT / ROLLBACK.

关于使用存储过程,您必须在DML的每个点上针对被跟踪的表精心调用存储过程.人们可能很容易错过面对成千上万行应用程序代码的日志记录更改.将这些代码放在触发器中会消除查找所有这些DML语句的麻烦.

警告

根据触发器的复杂程度,它仍然可能成为瓶颈.如果您想减少审计日志记录中的瓶颈,您可以执行某些操作.但是,它需要进行一些基础设施变更.

使用商用硬件,再创建两个数据库服务器

这将服务器减少由于审计日志记录而在主数据库(MD)上的写入I / O.以下是如何完成它：

步骤01)打开主数据库中的二进制日志记录.

步骤02)使用廉价的服务器,设置MySQL(与MD相同的版本),启用二进制日志记录.这将是DM.设置从MD到DM的复制.

步骤03)使用第二个便宜的服务器,设置MySQL(与MD相同的版本),禁用二进制日志记录.将每个审计表设置为使用–replicate-do-table.这将是AU.设置从DM到AU的复制.

步骤04)mysq从MD下载表结构并将其加载到DM和AU中.

步骤05)转换MD中的所有审计表以使用BLACKHOLE存储引擎

步骤06)转换DM和AU中的所有表以使用BLACKHOLE存储引擎

步骤07)转换AU中的所有审计表以使用MyISAM存储引擎

完成后

> DM将从MD复制并仅在其二进制日志中记录内容

>在所有审计表上使用–replicate-do-table过滤器,AU将从DM复制

这样做是将审计信息存储在单独的数据库服务器上,还可以减少MD通常具有的任何写入I / O降级.

标签：performance,mysql,stored-procedures,trigger

来源： https://codeday.me/bug/20190805/1589679.html