MySQL 服务器性能有时可能令人困惑，如果您曾经想知道触发器在影响 MySQL 服务器内存分配方面的作用，那么这篇文章适合您。MySQL 触发器是数据库管理员和开发人员的强大工具，使他们能够自动执行任务、强制数据一致性并无缝响应数据库内的事件。
在这里，我们首先介绍 MySQL 触发器的基础知识，然后深入探讨它们对内存使用的影响，并提供优化 MySQL 服务器性能的策略。
MySQL 中什么是触发器？
MySQL中的触发器是一种数据库对象，在数据库管理中起着关键作用。它本质上是响应数据库中发生的特定事件或操作而自动执行的一组指令或程序。这些事件称为触发事件，可以包括在数据库表中插入、更新或删除记录等操作。
触发器的重要性在于它们能够强制执行数据完整性并自动执行各种与数据库相关的任务。当触发事件发生时，将执行关联的触发器，从而允许您执行验证数据、记录更改或维护引用完整性等操作。触发器充当保障措施，确保满足或一致执行某些条件或操作，从而增强数据库的可靠性和一致性。
触发器执行是该机制的一个重要方面。当触发事件发生时，触发器的逻辑被自动调用，使其能够迅速响应该事件。这种实时响应能力可确保数据保持准确并符合预定义的规则或操作。
MySQL 触发器的不同类型
MySQL 支持各种类型的触发器，主要根据触发器执行触发事件的时间进行分类。了解这些触发器类型之间的差异对于在数据库管理中有效利用它们至关重要，因为它们服务于不同的目的并在数据库操作的不同阶段执行。
行级触发器
MySQL 中的行级触发器是一种响应表中各个行更改的触发器。这些触发器针对每个受影响的行执行，使其成为根据特定数据修改自定义操作的强大工具。以下是行级触发器如何为每个受影响的行工作的分步说明：
触发器激活：当具有关联行级触发器的表上发生触发事件（例如 UPDATE 语句）时，触发器将被激活。
受影响的行：如果触发事件影响多行（例如，更新表中的多行），则针对每个受影响的行单独执行行级触发器。
每行执行：对于每个受影响的行，都会执行触发器的代码。该代码可以包括 SQL 语句、过程或触发器内定义的其他操作。
访问旧值和新值：在触发器代码中，您可以访问当前行每列的旧值和新值。OLD 值代表修改前的数据，NEW 值代表修改后的数据。这允许您根据对每行所做的特定更改来比较和操作数据。
条件逻辑：行级触发器通常包含条件逻辑，用于根据 OLD 和 NEW 数据集中的值确定要采取的操作。此逻辑可用于强制执行数据完整性规则、生成审核日志或实现自定义业务逻辑。
定制操作：触发器的操作是根据每行的具体要求定制的。这种对各个行的细粒度控制允许对数据库中的更改进行定制响应。
触发器的完成：一旦对一行执行了触发器的代码，它将继续处理下一个受影响的行（如果有）并重复该过程。这将持续下去，直到对每个受影响的行都执行了触发器。
提交或回滚：根据触发器和关联的 SQL 语句的成功或失败，对行所做的更改可能会提交到数据库或回滚以保持数据一致性。
语句级触发器
MySQL 中的语句级触发器是为了响应触发它们的单个 SQL 语句而执行一次的数据库对象。这些触发器不关心受语句影响的各个行，而是关心整个语句。以下是语句级触发器的说明以及它们与行级触发器之间的主要区别：
语句级触发器：
执行次数：语句级触发器为每个触发 SQL 语句执行一次，无论受影响的行数是多少。例如，如果 UPDATE 语句修改了 100 行，则与该语句相关的语句级触发器只会对整个语句执行一次。
数据访问：与可以访问各个行的 OLD 和 NEW 值的行级触发器不同，语句级触发器通常不访问特定的行数据。他们的运作层面更高，更关心声明的整体影响。
使用案例：语句级触发器通常用于涉及整个语句的任务，例如维护摘要或聚合数据表、记录高级更改或应用语句级约束。
与行级触发器的主要区别：
执行频率：行级触发器对每个受影响的行执行一次，而语句级触发器对每个触发语句执行一次。
数据范围：行级触发器处理单独的行数据，使其适合需要行级处理的任务。语句级触发器在更高级别上运行，不访问行级数据。
使用案例：行级触发器通常用于数据验证、审核对特定行的更改以及强制执行行级约束等任务。语句级触发器用于涉及整个语句结果或行为的任务。
MySQL 触发器有哪些优点？
MySQL 触发器为数据库管理工作带来了显着的好处。它们通过自动执行预定义的操作来响应特定事件或数据变化来实现自动化，从而减少手动干预的需要。触发器还有助于通过执行规则和约束来维护数据完整性，确保只允许有效且一致的数据修改。此外，它们还可以在数据库中实施复杂的业务逻辑，简化重复性任务并提高整体效率。
MySQL 触发器有哪些限制？
虽然 MySQL 触发器在自动化和数据完整性实施方面可以提供显着的优势，但了解其局限性和注意事项也很重要：
性能影响：触发器会带来性能开销，特别是当它们涉及复杂操作或频繁触发时。每次发生激活触发器的事件（例如，INSERT、UPDATE、DELETE）时，都必须执行触发器逻辑。这可能会影响数据库系统的整体性能，可能导致响应时间变慢。仔细优化触发逻辑对于减轻这种影响至关重要。
复杂性：随着数据库中触发器数量的增加，管理和排除它们的故障可能变得具有挑战性。随着时间的推移，复杂的触发器也可能更难维护和修改。
维护开销：当对具有关联触发器的表进行架构更改时，这些触发器可能也需要更新。这会带来维护开销，特别是在大型且复杂的数据库系统中。必要时定期检查和更新触发器至关重要。
嵌套触发器：MySQL 允许触发器调用其他触发器，创建嵌套触发器级联。虽然这可能是一个强大的工具，但如果管理不当，可能会导致意外行为。
有限的错误处理：MySQL 触发器的错误处理能力有限。当触发器内发生错误时，处理起来可能会很困难，并且错误消息可能无法提供足够的详细信息以进行调试。
隔离级别：触发器可以影响事务的隔离级别。了解触发器如何与事务交互以及对数据一致性的潜在影响非常重要。
测试和验证：使用触发器时，尤其是在生产环境中，彻底的测试和验证至关重要。应仔细审查和测试触发器的更改，以确保它们不会带来意想不到的后果。
通过了解这些挑战并遵循最佳实践，您可以充分利用触发因素，同时最大限度地减少潜在的缺点。
MySQL 触发器示例和用例
MySQL触发器对各种现实场景中的数据管理产生重大影响，带来自动化并确保数据的一致性和完整性。以下是一些示例：审计日志记录：在金融或医疗保健等合规性驱动的行业中，维护数据库更改的审计跟踪至关重要。触发器可以自动记录所有修改，包括谁进行了更改、更改了什么以及何时更改。
数据验证：MySQL 触发器对于执行数据验证规则至关重要，确保传入数据符合预定义的标准。例如，电子商务平台可以利用触发器来验证产品订单，检查总价值是否满足指定条件。
业务规则执行：可以使用触发器在数据库级别无缝执行复杂的业务规则。例如，在基于订阅的服务中，触发器可以自动执行操作，例如发送续订提醒或在付款失败时暂停帐户。
安全性：触发器可以根据预定义的条件自动锁定或限制对某些记录或数据的访问，从而增强安全性。例如，在医疗保健数据库中，触发器可以限制只有授权人员才能访问敏感患者数据。
在这些示例场景中，MySQL触发器简化了流程，减少了人工干预，并确保了数据一致性，维护了可靠且安全的数据库环境。
MySQL触发器对数据库性能的影响
MySQL 触发器可以显着影响数据库性能，无论是积极还是消极。如果使用得当，触发器可以增强自动化和数据完整性。然而，它们也可能引入性能瓶颈，特别是如果设计不当的话。触发器执行会消耗资源，包括 CPU 和内存，这会减慢数据库操作的速度。
此外，如果触发器设置为响应频繁发生的事件，它们可能会增加对数据库资源的争用。对于数据库管理员来说，在触发器的好处与其潜在的性能影响之间取得平衡非常重要，并仔细地使用它们来保持最佳的数据库性能。
缓解性能问题
优化触发器性能并减轻 MySQL 中的潜在瓶颈涉及多种策略：
选择性触发器使用：将触发器的使用限制在基本任务上。避免过度使用可以在应用程序代码中更有效地处理的例行操作的触发器。
索引：确保触发器涉及的表被适当索引。精心设计的索引可以显着提高触发器内的查询性能。
编写高效的代码：高效的触发代码可以最大限度地减少执行时间。这包括使用优化的 SQL 语句并避免触发器内的资源密集型操作。
批处理：如果可能，批处理触发器操作以减少触发器执行次数，这有助于最大限度地减少为受影响的每行调用触发器的开销。
避免递归触发器：谨慎使用递归触发器，这可能会导致意外的性能问题。如果需要，实施控制和限制递归的机制。
监控和分析：持续监控触发器性能，以及时识别和解决性能缓慢的触发器。
事务管理：注意触发器内的事务管理。仔细设计事务以避免不必要的锁定和争用。
测试和基准测试：在临时环境中彻底测试触发器，以评估它们对性能的影响。对不同的触发器实现进行基准测试以确定最有效的选项。
定期维护：随着数据和使用模式的变化，相应地调整触发器以保持最佳性能。
数据库设计：考虑整体数据库设计和架构。结构良好的数据库可以最大限度地减少对复杂触发器的需求。
通过遵循这些策略并定期检查触发器性能，您可以确保触发器增强数据库功能，而不会引入严重的性能瓶颈。
探索触发器如何影响 MySQL 内存分配
MySQL 将活动表描述符存储在称为表打开缓存的特殊内存缓冲区中。该缓冲区由配置变量控制table_open_cache，这些变量保存 MySQL 应在缓存中存储的表描述符的最大数量以及table_open_cache_instances存储表缓存实例的数量。使用默认值table_open_cache=4000和table_open_cache_instances=16，MySQL 将创建 16 个独立的内存缓冲区，每个缓冲区存储 250 个表描述符。这些表缓存实例可以同时访问，允许 DML 使用缓存的表描述符而无需相互锁定。
如果只使用表，表缓存不需要大量内存，因为描述符是轻量级的，即使大幅增加 的值table_open_cache，所需的内存量也不会那么高。例如，4,000 个表将在缓存中占用最多 4,000 x 4K = 16MB，而100,000 个表将最多占用 390MB。对于这个数量的表来说，这也不是一个很大的数字。
然而，如果你的桌子有触发器，它就会改变游戏规则。
为了进行测试，我创建了一个包含单列的表，并向其中插入了一行：
|
1
2
3
4
5
|
mysql> CREATE TABLE tc_test( f1 INT);
Query OK, 0 rows affected (0,03 sec)
mysql> INSERT INTO tc_test VALUES(1);
Query OK, 1 row affected (0,01 sec)
|
然后我刷新表缓存并测量它使用了多少内存：
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
|
mysql> FLUSH TABLES;
Query OK, 0 rows affected (0,02 sec)mysql> SHOW GLOBAL STATUS LIKE 'Open_tables';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| Open_tables | 2 |
+---------------+-------+
1 row in set (0,00 sec)
mysql> SELECT current_alloc FROMsys.memory_global_by_current_bytes
-> WHERE event_name='memory/sql/TABLE_SHARE::mem_root';
+---------------+
| current_alloc |
+---------------+
| 60.50 KiB |
+---------------+
1 row in set (0,00 sec)
mysql> SELECT current_alloc FROMsys.memory_global_by_current_bytes
-> WHERE event_name='memory/sql/sp_head::main_mem_root';
Empty set (0,00 sec)
|
然后，我访问该表并将其放入缓存中。
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
|
$ for i in `seq 1 1 16`; do mysql test -e "SELECT * FROM tc_test"; done
...
mysql> SHOW GLOBAL STATUS LIKE 'Open_tables';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| Open_tables | 20 |
+---------------+-------+
1 row in set (0,00 sec)
mysql> SELECT current_alloc FROMsys.memory_global_by_current_bytes
-> WHERE event_name='memory/sql/TABLE_SHARE::mem_root';
+---------------+
| current_alloc |
+---------------+
| 75.17 KiB |
+---------------+
1 row in set (0,00 sec)
mysql> SELECT current_alloc FROMsys.memory_global_by_current_bytes
-> WHERE event_name='memory/sql/sp_head::main_mem_root';
Empty set (0,01 sec)
|
16 个表描述符在缓存中占用的空间不到 16 KiB。
现在，让我们尝试在此表上创建一些触发器，看看它是否会改变任何内容。
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
|
mysql> CREATE TRIGGER tc_test_ai AFTER INSERT ON tc_test FOR EACH ROW
-> BEGIN
-> SIGNAL SQLSTATE '45000' SET message_text='Very long string.
-> MySQL stores table descriptors in a special memory buffer, called table open cache.
-> This buffer could be controlled by configuration variables table_open_cache that
-> holds how many table descriptors MySQL should store in the cache and table_open_cache_instances
-> that stores the number of the table cache instances. So with default values of table_open_cache=4000
-> and table_open_cache_instances=16, you will have 16 independent memory buffers that will store 250
-> table descriptors each. These table cache instances could be accessed concurrently, allowing DML
-> to use cached table descriptors without locking each other. If you use only tables, the table cache
-> does not require a lot of memory, because descriptors are lightweight, and even if you significantly
-> increased the value of table_open_cache, it would not be so high. For example, 4000 tables will take
-> up to 4000 x 4K = 16MB in the cache, 100.000 tables will take up to 390MB that is also not quite a huge
-> number for this number of open tables. However, if your tables have triggers, it changes the game.';
-> END|
|
然后，让我们刷新表缓存并再次测试内存使用情况。
初始状态：
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
|
mysql> FLUSH TABLES;
Query OK, 0 rows affected (0,00 sec)
mysql> SHOW GLOBAL STATUS LIKE 'Open_tables';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| Open_tables | 2 |
+---------------+-------+
1 row in set (0,00 sec)
mysql> SELECT current_alloc FROMsys.memory_global_by_current_bytes
-> WHERE event_name='memory/sql/TABLE_SHARE::mem_root';
+---------------+
| current_alloc |
+---------------+
| 60.50 KiB |
+---------------+
1 row in set (0,00 sec)
mysql> SELECT current_alloc FROMsys.memory_global_by_current_bytes
-> WHERE event_name='memory/sql/sp_head::main_mem_root';
Empty set (0,00 sec)
|
将表放入缓存后：
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
|
$ for i in `seq 1 1 16`; do mysql test -e "SELECT * FROM tc_test"; done
...
mysql> SHOW GLOBAL STATUS LIKE 'Open_tables';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| Open_tables | 20 |
+---------------+-------+
1 row in set (0,00 sec)
mysql> SELECT current_alloc FROMsys.memory_global_by_current_bytes
-> WHERE event_name='memory/sql/TABLE_SHARE::mem_root';
+---------------+
| current_alloc |
+---------------+
| 75.17 KiB |
+---------------+
1 row in set (0,00 sec)
mysql> SELECT current_alloc FROMsys.memory_global_by_current_bytes
-> WHERE event_name='memory/sql/sp_head::main_mem_root';
+---------------+
| current_alloc |
+---------------+
| 611.12 KiB |
+---------------+
1 row in set (0,00 sec)
|
这样一来，除了表缓存中的 75.17 KiB 之外，还占用了 611.12 KiB memory/sql/sp_head::main_mem_root。那就是"Mem root for parsing and representation of stored programs."
这意味着每次将表放入表缓存时，所有关联的触发器都会放入内存缓冲区，存储它们的定义。
FLUSH TABLES命令清除存储程序缓存以及表缓存：
|
1
2
3
4
5
6
|
mysql> FLUSH TABLES;
Query OK, 0 rows affected (0,01 sec)
mysql> SELECT current_alloc FROMsys.memory_global_by_current_bytes
-> WHERE event_name='memory/sql/sp_head::main_mem_root';
Empty set (0,00 sec)
|
更多触发器放入缓存时会增加内存使用量。
例如，如果我们再创建五个触发器并重复测试，我们将看到以下数字：
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
|
mysql> d |
mysql> CREATE TRIGGER tc_test_bi BEFORE INSERT ON tc_test FOR EACH ROW BEGIN SIGNAL SQLSTATE '45000
' SET message_text='Very long string. MySQL stores table descriptors in a special memory buffer, calle
at holds how many table descriptors MySQL should store in the cache and table_open_cache_instances t
hat stores the number of the table cache instances. So with default values of table_open_cache=4000
and table_open_cache_instances=16, you will have 16 independent memory buffers that will store 250 t
able descriptors each. These table cache instances could be accessed concurrently, allowing DML to u
se cached table descriptors without locking each other. If you use only tables, the table cache doe
s not require a lot of memory, because descriptors are lightweight, and even if you significantly increased the value of table_open_cache, it would not be so high. For example, 4000 tables will take u
p to 4000 x 4K = 16MB in the cache, 100.000 tables will take up to 390MB that is also not quite a hu
ge number for this number of open tables. However, if your tables have triggers, it changes the gam
e.'; END|
Query OK, 0 rows affected (0,01 sec)
mysql> CREATE TRIGGER tc_test_bu BEFORE UPDATE ON tc_test FOR EACH ROW BEGIN SIGNAL SQLSTATE '45000
' SET message_text='Very long string. MySQL stores table descriptors in a special memory buffer, calle
at holds how many table descriptors MySQL should store in the cache and table_open_cache_instances t
hat stores the number of the table cache instances. So with default values of table_open_cache=4000
and table_open_cache_instances=16, you will have 16 independent memory buffers that will store 250 t
able descriptors each. These table cache instances could be accessed concurrently, allowing DML to u
se cached table descriptors without locking each other. If you use only tables, the table cache doe
s not require a lot of memory, because descriptors are lightweight, and even if you significantly increased the value of table_open_cache, it would not be so high. For example, 4000 tables will take u
p to 4000 x 4K = 16MB in the cache, 100.000 tables will take up to 390MB that is also not quite a hu
ge number for this number of open tables. However, if your tables have triggers, it changes the gam
e.'; END|
Query OK, 0 rows affected (0,02 sec)
mysql> CREATE TRIGGER tc_test_bd BEFORE DELETE ON tc_test FOR EACH ROW BEGIN SIGNAL SQLSTATE '45000' SET message_text='Very long string. MySQL stores table descriptors in a special memory buffer, calle
at holds how many table descriptors MySQL should store in the cache and table_open_cache_instances that stores the number of the table cache instances. So with default values of table_open_cache=4000
and table_open_cache_instances=16, you will have 16 independent memory buffers that will store 250 table descriptors each. These table cache instances could be accessed concurrently, allowing DML to use cached table descriptors without locking each other. If you use only tables, the table cache does not require a lot of memory, because descriptors are lightweight, and even if you significantly increased the value of table_open_cache, it would not be so high. For example, 4000 tables will take up to 4000 x 4K = 16MB in the cache, 100.000 tables will take up to 390MB that is also not quite a huge number for this number of open tables. However, if your tables have triggers, it changes the game.'; END|
Query OK, 0 rows affected (0,01 sec)
mysql> CREATE TRIGGER tc_test_au AFTER UPDATE ON tc_test FOR EACH ROW BEGIN SIGNAL SQLSTATE '45000'
SET message_text='Very long string. MySQL stores table descriptors in a special memory buffer, call
ed ta a
t holds how many table descriptors MySQL should store in the cache and table_open_cache_instances th
at stores the number of the table cache instances. So with default values of table_open_cache=4000 a
nd table_open_cache_instances=16, you will have 16 independent memory buffers that will store 250 ta
ble descriptors each. These table cache instances could be accessed concurrently, allowing DML to us
e cached table descriptors without locking each other. If you use only tables, the table cache does
not require a lot of memory, because descriptors are lightweight, and even if you significantly increased the value of table_open_cache, it would not be so high. For example, 4000 tables will take up
to 4000 x 4K = 16MB in the cache, 100.000 tables will take up to 390MB that is also not quite a hug
e number for this number of open tables. However, if your tables have triggers, it changes the game
.'; END|
Query OK, 0 rows affected (0,01 sec)
mysql> CREATE TRIGGER tc_test_ad AFTER DELETE ON tc_test FOR EACH ROW BEGIN SIGNAL SQLSTATE '45000'
SET message_text='Very long string. MySQL stores table descriptors in a special memory buffer, call
ed table open cache. This buffer could be controlled by configuration variables table_open_cache tha
t holds how many table descriptors MySQL should store in the cache and table_open_cache_instances th
at stores the number of the table cache instances. So with default values of table_open_cache=4000 a
nd table_open_cache_instances=16, you will have 16 independent memory buffers that will store 250 ta
ble descriptors each. These table cache instances could be accessed concurrently, allowing DML to us
e cached table descriptors without locking each other. If you use only tables, the table cache does
not require a lot of memory, because descriptors are lightweight, and even if you significantly increased the value of table_open_cache, it would not be so high. For example, 4000 tables will take up
to 4000 x 4K = 16MB in the cache, 100.000 tables will take up to 390MB that is also not quite a hug
e number for this number of open tables. However, if your tables have triggers, it changes the game
.'; END|
Query OK, 0 rows affected (0,01 sec)
mysql> SHOW GLOBAL STATUS LIKE 'Open_tables';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| Open_tables | 35 |
+---------------+-------+
1 row in set (0,00 sec)
mysql> SELECT current_alloc FROMsys.memory_global_by_current_bytes
-> WHERE event_name='memory/sql/TABLE_SHARE::mem_root';
+---------------+
| current_alloc |
+---------------+
| 446.23 KiB |
+---------------+
1 row in set (0,00 sec)
mysql> SELECT current_alloc FROMsys.memory_global_by_current_bytes
-> WHERE event_name='memory/sql/sp_head::main_mem_root';
+---------------+
| current_alloc |
+---------------+
| 3.58 MiB |
+---------------+
1 row in set (0,00 sec)
|
该事件的数字memory/sql/sp_head::main_mem_root相差六次：
|
1
2
3
4
5
6
7
|
mysql> SELECT 3.58*1024/611.12;
+------------------+
| 3.58*1024/611.12 |
+------------------+
| 5.998691 |
+------------------+
1 row in set (0,00 sec)
|
请注意，触发器定义的长度会影响memory/sql/sp_head::main_mem_root.
例如，如果我们将触发器定义如下：
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
|
mysql> DROP TABLE tc_test;
Query OK, 0 rows affected (0,02 sec)
mysql> CREATE TABLE tc_test( f1 INT);
Query OK, 0 rows affected (0,03 sec)
mysql> INSERT INTO tc_test VALUES(1);
Query OK, 1 row affected (0,01 sec)
mysql> d |
mysql> CREATE TRIGGER tc_test_ai AFTER INSERT ON tc_test FOR EACH ROW BEGIN SIGNAL SQLSTATE '45000'
SET message_text='Short string';end |
Query OK, 0 rows affected (0,01 sec)
mysql> CREATE TRIGGER tc_test_au AFTER UPDATE ON tc_test FOR EACH ROW BEGIN SIGNAL SQLSTATE '45000'
SET message_text='Short string';end |
Query OK, 0 rows affected (0,04 sec)
mysql> CREATE TRIGGER tc_test_ad AFTER DELETE ON tc_test FOR EACH ROW BEGIN SIGNAL SQLSTATE '45000'
SET message_text='Short string';end |
Query OK, 0 rows affected (0,01 sec)
mysql> CREATE TRIGGER tc_test_bi BEFORE INSERT ON tc_test FOR EACH ROW BEGIN SIGNAL SQLSTATE '45000'
SET message_text='Short string';end |
Query OK, 0 rows affected (0,01 sec)
mysql> CREATE TRIGGER tc_test_bu BEFORE UPDATE ON tc_test FOR EACH ROW BEGIN SIGNAL SQLSTATE '45000'
SET message_text='Short string';end |
Query OK, 0 rows affected (0,02 sec)
mysql> CREATE TRIGGER tc_test_bd BEFORE DELETE ON tc_test FOR EACH ROW BEGIN SIGNAL SQLSTATE '45000'
SET message_text='Short string';end |
Query OK, 0 rows affected (0,01 sec)
mysql> d ;
mysql> FLUSH TABLES;
Query OK, 0 rows affected (0,00 sec)
mysql> SHOW GLOBAL STATUS LIKE 'Open_tables';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| Open_tables | 2 |
+---------------+-------+
1 row in set (0,01 sec)
mysql> SELECT current_alloc FROMsys.memory_global_by_current_bytes
-> WHERE event_name='memory/sql/TABLE_SHARE::mem_root';
+---------------+
| current_alloc |
+---------------+
| 60.50 KiB |
+---------------+
1 row in set (0,00 sec)
mysql> SELECT current_alloc FROMsys.memory_global_by_current_bytes
-> WHERE event_name='memory/sql/sp_head::main_mem_root';
Empty set (0,00 sec)
$ for i in `seq 1 1 16`; do mysql test -e "select * from tc_test"; done
...
mysql> SHOW GLOBAL STATUS LIKE 'Open_tables';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| Open_tables | 35 |
+---------------+-------+
1 row in set (0,00 sec)
mysql> SELECT current_alloc FROMsys.memory_global_by_current_bytes
-> WHERE event_name='memory/sql/TABLE_SHARE::mem_root';
+---------------+
| current_alloc |
+---------------+
| 446.23 KiB |
+---------------+
1 row in set (0,00 sec)
mysql> SELECT current_alloc FROMsys.memory_global_by_current_bytes
-> WHERE event_name='memory/sql/sp_head::main_mem_root';
+---------------+
| current_alloc |
+---------------+
| 1.89 MiB |
+---------------+
1 row in set (0,00 sec)
|
对于较长的触发器定义，最终的内存量为 1.89 MiB，而不是 3.58 MiB。
请注意，拥有单个表缓存实例需要更少的内存来存储触发器定义。例如，对于我们的小六触发器，它将是 121.12 KiB，而不是 1.89 MiB：
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
|
mysql> SHOW GLOBAL VARIABLES LIKE 'table_open_cache_instances';
+----------------------------+-------+
| Variable_name | Value |
+----------------------------+-------+
| table_open_cache_instances | 1 |
+----------------------------+-------+
1 row in set (0,00 sec)
mysql> FLUSH TABLES;
Query OK, 0 rows affected (0,00 sec)
mysql> SHOW GLOBAL STATUS LIKE 'Open_tables';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| Open_tables | 2 |
+---------------+-------+
1 row in set (0,00 sec)
mysql> SELECT current_alloc FROMsys.memory_global_by_current_bytes
-> WHERE event_name='memory/sql/sp_head::main_mem_root';
Empty set (0,00 sec)
$ for i in `seq 1 1 16`; do mysql test -e "select * from tc_test"; done
...
mysql> SELECT current_alloc FROMsys.memory_global_by_current_bytes
-> WHERE event_name='memory/sql/sp_head::main_mem_root';
+---------------+
| current_alloc |
+---------------+
| 121.12 KiB |
+---------------+
1 row in set (0,00 sec)
|
结论：继续使用 MySQL 触发器
当您访问具有关联触发器的表时，即使未触发，它们的定义也会放入存储程序缓存中。这被报告为MySQL Bug #86821，并被 Oracle 关闭为“不是 Bug”。这当然不是一个bug，而是表和存储例程缓存设计的问题。不过，做好准备是件好事，这样当您比预期更快地耗尽内存时，您就不会感到惊讶，特别是当您有许多具有长定义的触发器时。
尽管 MySQL 触发器是自动化任务和确保数据库内数据完整性的重要工具，但值得注意的是它们可能会对内存使用产生影响。当调用触发器时，它们会消耗内存资源来执行其定义的操作，包括查询、更新或插入。当在数据库操作期间同时激活多个触发器时，内存开销变得更加显着。因此，数据库管理员和开发人员必须仔细设计触发器，考虑其特定逻辑和预期功能，以在实现所需的自动化和保持内存效率之间取得平衡。
