当表很大并且有子表时清除数据

朋友们，你们好!<一个href="//m.doggingzone.com/services/managed-services">雷竞技下载官网Percona Managed Services，我们通常每天都做不同类型的事情，包括例行任务、监控，以及经常回答一些不容易回答的问题。raybet雷竞技竞猜在线官网

几天前，一位客户问了我们这样一个问题:嘿，Pe雷竞技下载官网rcona，我同时有一个问题和一个问题:我想从一个特定日期的表中删除行，因为数据是不必要的。我尝试运行DELETE命令，结果出现如下错误:不能删除或更新父行:外键约束失败．你能帮帮我吗?”

乍一看，错误消息很明显:要删除行的表有一个子表，这阻止了直接执行DELETE。

“别担心，我们会研究一下这个问题，相应地，我们会提出一个执行计划。”我们回答说。这就是这篇博客的起源，如果你发现自己处于同样的情况，也可以采取类似的做法，或者至少考虑一些用于实现目标的实践。

首先，让我们回顾一下表的结构、大小和约束。

让我们检查一下表的大小(表大小的数字是真实的，它们与表的结构不匹配，因为我已经删除了相关的列以保持整个事情的匿名性)。

壳牌

+--------------+------------------+------------+---------+----------+---------+----------+ | table_schema | table_name | table_rows | DATA_MB | INDEX_MB | FREE_MB | TOTAL_MB | +--------------+------------------+------------+---------+----------+---------+----------+ | cust |命令| 50990 | 101703010 | 25760 | 70 | 76820 | | cust异常| | | 640 | 2110 | 15251900 | 2800 | | cust |个人化操作| 9310 | 119541910 | 7090 | 16460 | |+--------------+------------------+------------+---------+----------+---------+----------+

1 2 3. 4 5 6 7

+--------------+------------------+------------+---------+----------+---------+----------+ | table_schema | table_name | table_rows | DATA_MB | INDEX_MB | FREE_MB | TOTAL_MB | +--------------+------------------+------------+---------+----------+---------+----------+ | cust | 订单 | 101703010 | 50990 | 25760 | 70 | 76820 | | cust | 异常 | 15251900 | 2110 | 640 | 50 | 2800 | | cust | 个人化操作 | 119541910 | 9310 | 7090 | 60 | 16460 | +--------------+------------------+------------+---------+----------+---------+----------+

约束:

MySQL

mysql> SELECT * FROM information_schemaREFERENTIAL_CONSTRAINTS在哪里REFERENCED_TABLE_NAME在（“订单”) \ G***************************1.行*************************** CONSTRAINT_CATALOG: def CONSTRAINT_SCHEMA: cust CONSTRAINT_NAME: FK_Exceptions_Orders_OrderId UNIQUE_CONSTRAINT_CATALOG: def UNIQUE_CONSTRAINT_SCHEMA: cust UNIQUE_CONSTRAINT_NAME: PRIMARY MATCH_OPTION: NONE UPDATE_RULE: NO ACTION DELETE_RULE: NO ACTION TABLE_NAME: exceptions REFERENCED_TABLE_NAME: orders *************************** row *************************** CONSTRAINT_CATALOG: def CONSTRAINT_SCHEMA: cust CONSTRAINT_NAME: OrderId UNIQUE_CONSTRAINT_CATALOG: def UNIQUE_CONSTRAINT_SCHEMA: cust UNIQUE_CONSTRAINT_NAME: PRIMARY MATCH_OPTION: NONE UPDATE_RULE: NO ACTION DELETE_RULE: NO ACTION TABLE_NAME: personalizations REFERENCED_TABLE_NAME: orders 2 rows in set (0.03 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

mysql> 选择 ＊ 从 information_schema。REFERENTIAL_CONSTRAINTS 在哪里 REFERENCED_TABLE_NAME 在 （ “订单” ) \ G ***************************1. 行 *************************** CONSTRAINT_CATALOG:def CONSTRAINT_SCHEMA:cust CONSTRAINT_NAME:FK_Exceptions_Orders_OrderId UNIQUE_CONSTRAINT_CATALOG:def UNIQUE_CONSTRAINT_SCHEMA:cust UNIQUE_CONSTRAINT_NAME: 主要的 MATCH_OPTION: 没有一个 UPDATE_RULE: 没有 行动 DELETE_RULE: 没有 行动 TABLE_NAME:异常 REFERENCED_TABLE_NAME:订单 ***************************2. 行 *************************** CONSTRAINT_CATALOG:def CONSTRAINT_SCHEMA:cust CONSTRAINT_NAME:OrderId UNIQUE_CONSTRAINT_CATALOG:def UNIQUE_CONSTRAINT_SCHEMA:cust UNIQUE_CONSTRAINT_NAME: 主要的 MATCH_OPTION: 没有一个 UPDATE_RULE: 没有 行动 DELETE_RULE: 没有 行动 TABLE_NAME:个人化操作 REFERENCED_TABLE_NAME:订单 2 行 在 集 (0.03sec)

最后，我们将在下面展示客户端所呈现的拓扑:

壳牌

1.mysqlserver01 = 127.0.0.2 (RW)(版本:8.0.31-23)| ___ 2。mysqlserver02=127.0.0.3 [SR] [ver: 8.0.31-23] (Slave_delay: 0)

1 2	1. mysqlserver01＝127.0.0.2 ［ RW ］ ［ 版本 ： 8.0.31-23 ］ | ___ 2. mysqlserver02＝127.0.0.3 ［ 老 ］ ［ 版本 ： 8.0.31-23 ］ （ Slave_delay ： 0 ）

这意味着:

• 主服务器，使用雷竞技下载官网用于MySQL的Percona服务器安装版本8.0.31-23。
• 一个软件版本相同的副本(只读和超只读模式)。

面对这种情况，我们能说些什么?

• 首先，这个表有两个子表，而不是只有一个。
• 其次，赌桌的大小很重要。
• 试图在生产环境中运行该程序的人没有做以下工作:
  • 运行要删除的行以前的一些备份。
  • 检查表是否具有引用完整性，子表。
  • 检查一下桌子的大小。
  • 检查数据库的拓扑结构，是否有一个或多个服务器正在复制雷竞技下载官网Percona XtraDB集群等。

这一切意味着什么?

• 如果他能够执行DELETE(让我们假设他搜索了一些东西，并通过执行找到了一种方法来做到这一点设置FOREIGN_KEY_CHECKS = 0)，之前没有备份，没有备份，这是个坏主意。
• 执行这样的DELETE命令后，考虑到要删除的行数非常多，副本/s中的复制将受到影响。
• 只执行了父表的DELETE设置FOREIGN_KEY_CHECKS = 0，将子表留给孤立的行，这样我们就违反了模型的引用完整性。
• 根据应用程序对表的使用程度，这样一个大容量的事务会生成一些锁，这会以某种方式导致应用程序停机。

在这种情况下我们应该怎么做呢?

当您需要执行这类涉及大量行且有约束条件的操作时，始终建议执行某些检查，在删除信息之前对信息进行备份，使用父表的条件(使用pt-archiver）.

很好。我们将为此执行必要的测试，为此我们将使用以下测试环境:

下面是测试拓扑(如您所见，我们使用的是相同版本的<一个href="//m.doggingzone.com/software/mysql-database/percona-server">雷竞技下载官网用于MySQL的Percona服务器）.

一旦创建并加载了表(您可以使用这个链接为了这样做)，我们有这样的东西:

现在，对于测试，让我们从表中删除大量的行。为此，我们将在test.orders并使用CreatedDate字段作为参考艾凡:

让我们尝试做客户做过的同样的事情来检查他是否正确:

这个过程

1. 备份:这是这个过程中最关键的一步。通过这种方式，我们可以确保如果由于某种原因再次需要数据，可以将其重新插入到同一个表或另一个模式中的另一个表中。

让我们创建/home/雷竞技下载官网percona/Backup目录，并检查是否有足够的磁盘空间用于这样的备份(根据表大小，它应该至少相同或更小)。为此，我们将使用副本，以便不影响主实例的操作。

对于备份，我们将使用mydumper，这允许我们使用以下:

• WHERE子句过滤行。
• 使用几个并行线程来读取数据(默认值是4)。
• 压缩备份文件。

接下来，这些是为备份执行的命令。我们验证每一步都完成了

让我们验证备份文件是否存在并且有数据:

2. 数据清除:在这里，我们将使用pt-archiver实用程序。为什么?

• 它允许我们以可控的方式消除数据块中的行。
• 我们可以使用WHERE子句(因为我们将需要它)。
• 它允许您控制副本中的延迟(非常重要)。
• 方法可以中断(但不能撤消)清除操作哨兵国旗。

让我们从test.exceptions第一个表。我们将在主服务器上运行所有的存档程序，如下所示:

我们验证没有满足清除条件的行，并验证表中还剩下多少行。

让我们继续test.personalizations表:

我们验证没有符合清除条件的行:

最后来结束test.orders表:

我们验证没有符合清除条件的行:

结论

当您需要执行涉及大量行的这种类型的操作时，在约束围栏的情况下，始终建议执行前面提到的所有检查，在删除信息之前对信息进行备份，使用父表的条件(使用pt-archiver)，并在复制过程中不断控制复制过程(实际上，工具不会允许延迟大于命令中规定的延迟-max-lag = 30）.

最后，从最初所需的表中删除数据。

这个过程可能比简单和传统的DELETE需要更长的时间。不过，我的朋友，最重要的是数据的可用性、完整性和良好的响应时间。总是这样。

附送的歌曲

如有必要，可使用备份文件恢复已删除的行，操作如下:

壳牌

[雷竞技下载官网percona@PS8_Replica备份]$ zcat test. personals .00000.sql.gz | mysql -h 192.168.0.70 test -A [percona@PS8_Replica备份]$ zcat test. .00000.sql.gz | mysql -h 192.168.0.70 test -A [percona@PS8_Replica备份]$ zcat test.orders.00000.sql.gz | mysql -h 192.168.0.70 test -A [percona@PS8_Replica备份]$ mysql -h 192.168.0.70 test -A -e "select count(1) from test. gz个人化操作 " +----------+ | 数(1 ) | +----------+ | 20000年 | +----------+ [ percona@PS雷竞技下载官网8_Replica备份mysql - h 192.168.0.70美元测试- a - e”从测试选择计数(1)。异常 " +----------+ | 数(1 ) | +----------+ | 20000年 | +----------+ [ percona雷竞技下载官网@PS8_Replica备份mysql - h 192.168.0.70美元测试- a - e”从测试选择计数(1)。订单 " +----------+ | 数(1 ) | +----------+ | 10000年 | +----------+

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

［ 雷竞技下载官网 @ PS8_Replica 备份 ］ ＄ zcat 测验 .personalizations ． 00000. sql.gz | mysql -h 192.168.0.70 测验 -一个 ［ 雷竞技下载官网 @ PS8_Replica 备份 ］ ＄ zcat 测验 .exceptions ． 00000. sql.gz | mysql -h 192.168.0.70 测验 -一个 ［ 雷竞技下载官网 @ PS8_Replica 备份 ］ ＄ zcat 测验 .orders ． 00000. sql.gz | mysql -h 192.168.0.70 测验 -一个 ［ 雷竞技下载官网 @ PS8_Replica 备份 ］ ＄ mysql -h 192.168.0.70 测验 -一个 -e "select count(1) from test. personizations " +----------+ | 数 （ 1 ） | +----------+ | 20000 | +----------+ ［ 雷竞技下载官网 @ PS8_Replica 备份 ］ ＄ mysql -h 192.168.0.70 测验 -一个 -e "select count(1) from test.exceptions" +----------+ | 数 （ 1 ） | +----------+ | 20000 | +----------+ ［ 雷竞技下载官网 @ PS8_Replica 备份 ］ ＄ mysql -h 192.168.0.70 测验 -一个 -e "select count(1) from test.orders" +----------+ | 数 （ 1 ） | +----------+ | 10000 | +----------+

雷竞技下载官网MySQL的Percona发行版是最完整、稳定、可扩展和安全的开源MySQL解决方案，为您最关键的业务应用程序提供企业级数据库环境……而且它是免费使用的!

现在就试雷竞技下载官网试MySQL的Percona发行版吧!

相关的

分享这篇文章!