加快MySQL中的大型表删除速度

发表于： 2022-09-13 22:30:44
分类：数据库文章分享
浏览：浏览(88)

由于许多原因，一张大表是一种痛苦，只要它在一个系统中。似乎这还不够，摆脱它也是一项艰巨的任务。在这篇文章中，我们将理解为什么做这个操作很痛苦，以及我们能做些什么。

那么发生了什么？当一个表被删除（或截断）时，InnoDB必须扫描整个缓冲池中的页面，并删除属于该表的所有页面。对于大型缓冲池，缓冲池页面中的这种逐出过程将更慢。当我们说“扫描缓冲池”时，它主要查找“LRU”、“刷新”（脏页）和“AHI”条目。

LRU：缓冲池页面按使用顺序存储在页面链接列表中。当数据到达列表的末尾时，会将其逐出，以便为新数据腾出空间。当需要空间将新页面添加到缓冲池时，将删除最近使用最少的页面，并在列表中间添加新页面。

AHI：这是一个散列索引，包含经常访问的索引页。InnoDB有一种监视索引搜索的机制。如果InnoDB注意到查询可以从构建哈希索引中获益，它会自动这样做。

免责声明：
我们将要讨论的问题已经在MySQL 8.0.23（以后）中得到解决，尽管这仍然与低版本上的许多MySQL部署相关。从MySQL 8.0.23开始，删除表的过程得到了改进，MySQL不再等待收回。InnoDB为缓冲池中的各个页面实现了延迟逐出过程。

检查以下示例引擎INNODB状态：

BUFFER POOL AND MEMORY
----------------------
Total large memory allocated 3576902451200
Dictionary memory allocated 5414868
Internal hash tables (constant factor + variable factor)
    Adaptive hash index 419777277888    (54392761408 + 365384516480)
    Page hash           53118808 (buffer pool 0 only)
    Dictionary cache    13603605220     (13598190352 + 5414868)
    File system         1374872         (812272 + 562600)
    Lock system         9470361768      (9461541368 + 8820400)
    Recovery system     0       (0 + 0)
Buffer pool size   209689600
Buffer pool size, bytes 3435554406400
Free buffers       16263
Database pages     187372028
Old database pages 69165341
Modified db pages  2323769
Pending reads      0
Pending writes: LRU 0, flush list 0, single page 0
Pages made young 1831432982, not young 28737547535
0.00 youngs/s, 0.00 non-youngs/s
Pages read 1655886985, created 633099435, written 15361171213
0.00 reads/s, 0.00 creates/s, 0.00 writes/s
Buffer pool hit rate 1000 / 1000, young-making rate 0 / 1000 not 1 / 1000
Pages read ahead 0.00/s, evicted without access 0.00/s, Random read ahead 0.00/s
LRU len: 187372028, unzip_LRU len: 0
I/O sum[7845824]:cur[13760], unzip sum[0]:cur[0]

注意“数据库页面”和“LRU len”，表示需要扫描的页面数量。

接下来，将球传递到底层操作系统以删除文件。根据文件的大小和系统的繁忙程度，IO操作需要时间。

一般来说，下拉表操作的成本可归结为以下两项任务：

遍历缓冲池并逐出找到的页面

从操作系统磁盘删除文件

任务1特定于MySQL，而任务2依赖于操作系统（磁盘）。我们将在这两个方面寻求改进。

1.遍历缓冲池并清除找到的页面

让我们看看是否可以找到提高此任务性能的方法。

方法1：缓冲池很大，链表也很大；我们可以暂时减少缓冲池并使链接列表更小吗？

步骤如下：

保存当前缓冲池状态>减小缓冲池大小>删除表（扫描小列表）>重置缓冲池大小>“恢复缓冲池内容”

  SET GLOBAL innodb_buffer_pool_dump_now=ON;
    SHOW STATUS LIKE 'Innodb_buffer_pool_dump_status';
    SET GLOBAL innodb_buffer_pool_size=128M;
    DROP TABLE LARGE_TABLE;
    SET GLOBAL innodb_buffer_pool_size=1T;
    SET GLOBAL innodb_buffer_pool_load_now=ON;
    SHOW STATUS LIKE 'Innodb_buffer_pool_dump_status';

听起来很容易，但不起作用；事实上，由于以下原因，它使情况变得更糟。

缓冲池大小调整是一个阻塞操作，它仍然需要扫描列表、整理碎片和调整缓冲池大小。

在减少的缓冲池上运行流量将导致性能损失。

缓冲池扩展时将重复相同的阻塞操作。

参考：https://dev.mysql.com/doc/refman/5.7/en/innodb-buffer-pool-resize.html

方法2：停止使用表（不选择，不写入需要删除的表）

可以通过撤销用户对表的授权或控制应用程序级的读/写来避免表操作。

这将导致缓冲池根据MySQL的繁忙程度缓慢地逐出页面。

逐出过程将被简化，因为当它遍历链表时，它将找不到表的任何相关页面。

尽管如此，您仍将扫描大型链接列表。

总之，对于MySQL级别，您无法避免遍历缓冲池的链表，但您可以通过等待缓冲池最终将其逐出，从而节省逐出过程中的一些时间。

2.从操作系统磁盘删除文件

现在，这项任务取决于磁盘的繁忙程度和速度。文件删除操作越慢，MySQL返回“删除成功”所需的时间就越长。那么我们可以做些什么来改进这个删除操作呢？让我们看看。

方法1：磁盘上的文件越小，删除速度越快。

我们可以通过从表中清除数据来减小磁盘上的数据。这是我们可以使用pt archiver完成的。也就是说，除非执行优化表或伪alter（alter table…ENGINE=InnoDB），否则表将变得碎片化，并且大小仍然保持不变。

使用pt archiver缓慢删除表记录>降低表大小>给LRU算法一些时间从缓冲池中清除这些页面>删除

这种方法涉及需要更多时间的多个活动，因为根据表大小，归档过程之后的冷却期可能需要更长的时间。现在请注意，对于较大的表，pt archiver将花费很长时间。在运行虚拟alter之前，磁盘上仍然会有一个具有较大表大小的碎片表。

方法2：不要删除底层表空间文件（ibd）

如果我们完全跳过文件删除操作怎么办？一旦缓冲池扫描完成，MySQL将返回该命令！但我们能吗？

是的，部分是的。我们可以使用硬链接“欺骗MySQL”，使其相信表的数据文件已被删除，即使它仍然存在！

什么是硬链接？

（如果您已经知道这个概念，可以跳过本节。）

硬链接是将名称与文件关联的目录项。您看到的每个文件名都是硬链接。让我们探讨一下：

MacBook-Pro:~ kedarvaijanapurkar$ touch percona.rocks
MacBook-Pro:~ kedarvaijanapurkar$ echo "Test" > percona.rocks
MacBook-Pro:~ kedarvaijanapurkar$ ls -li percona.rocks
20594511 -rw-r--r--  1 kedarvaijanapurkar  staff  5 Aug 26 16:22 percona.rocks

这里的“percona.rocks”是一个硬链接。注意，第一个值“20594511”是由“percona.rocks”文件名表示的文件索引节点。现在让我们创建另一个硬链接。

MacBook-Pro:~ kedarvaijanapurkar$ ln percona.rocks new_hardlink
MacBook-Pro:~ kedarvaijanapurkar$ ls -li *hardlink*
20594511 -rw-r--r--  2 kedarvaijanapurkar  staff  5 Aug 26 16:22 percona.rocks
20594511 -rw-r--r--  2 kedarvaijanapurkar  staff  5 Aug 26 16:22 new_hardlink
MacBook-Pro:~ kedarvaijanapurkar$

注意，我们可以看到两个不同的文件，但inode仍然是相同的“20594511”，指向相同的物理位置/文件。

在不深入了解操作系统的更多细节的情况下，请理解文件名是磁盘上实际数据的硬链接，因此每个文件必须至少有一个硬链接。因此，只要磁盘上有一个与之关联的硬链接，就不会删除该文件。

这里我们有两个硬链接；即percona。rocks和new_hardlink具有相同的内容（因为它们指向相同的物理数据）。

MacBook-Pro:~ kedarvaijanapurkar$ cat percona.rocks
Percona is great and hardlink is a fun concept.
MacBook-Pro:~ kedarvaijanapurkar$ cat new_hardlink
Percona is great and hardlink is a fun concept.
MacBook-Pro:~ kedarvaijanapurkar$

利用这一知识，如果我们创建到数据文件（ibd）的硬链接，DROP表实际上不会从操作系统中删除该文件，而只会删除硬链接，这在磁盘IO上非常轻。

我们可以使用以下步骤加快drop表的速度。

在MySQL中删除大型表的步骤

假设我们必须删除large_table。

在服务器上创建硬链接（需要提升权限）

cd <datadir>/<database>
ln large_table.ibd large_table.ibd.hardlink

问题下拉表

set SQL_LOG_BIN=0;
drop table if exists large_table;

由于我们刚刚创建的硬链接，删除表只会删除large_table。ibd链接。原始数据仍然存在，但MySQL不知道这些剩余数据。

缓慢截断文件，直到文件足够小，可以运行

cd <datadir>/<database>
truncate  -s  -1GB large_table.ibd.hardlink

#您可以将截断大小从1G增加到10到100，这取决于它降低文件大小的速度。

执行rm命令

rm large_table.ibd.hardlink

最好确保不查询表，以避免“逐出”过程，MySQL只遍历链表。

考虑到您有一个复制的环境，我建议您先在（被动）复制服务器上执行，然后再进行生产，以便事先了解情况。您还可以使用故障切换执行此任务：在副本上执行>故障切换>在旧主服务器上执行。

涉及的风险：在较旧的MySQL版本中，这种加速删除表的方法涉及在操作系统级别处理数据库文件，并且人为错误（一如既往）可能是灾难性的。

关于作者

原文标题：Speed Up Your Large Table Drops in MySQL
原文作者：Kedar Vaijanapurkar
原文链接：https://www.percona.com/blog/speed-up-your-large-table-drops-in-mysql/

赞(0)
踩(0)

免责声明：

1、本站资源由自动抓取工具收集整理于网络。

2、本站不承担由于内容的合法性及真实性所引起的一切争议和法律责任。

3、电子书、小说等仅供网友预览使用，书籍版权归作者或出版社所有。

4、如作者、出版社认为资源涉及侵权，请联系本站，本站将在收到通知书后尽快删除您认为侵权的作品。

5、如果您喜欢本资源，请您支持作者，购买正版内容。

6、资源失效，请下方留言，欢迎分享资源链接

文章评论

共0条评论