【MySQL】加速MySQL中大表的删除

由于各种原因，只要大表出现在系统中，就是一种痛苦。不仅仅如此，删除它也是一项艰巨的任务。本文我们将了解为什么做这个操作很痛苦，以及我们能做些什么。

所以发生了什么？当一个表被删除(或截断)时，InnoDB必须扫描整个缓冲池的页并删除所有属于该表的页。对于较大的缓冲池，这种在缓冲池中抓取页和逐出的过程会比较慢。当我们说"扫描缓冲池"时，它主要查找"LRU"、"FLUSH"(脏页)和"AHI"条目。

LRU：缓冲池的页按使用顺序存储在页链表中。当数据到达列表的末尾时，它会被驱逐，以便为新数据腾出空间。当需要向缓冲池添加新页时，最近最少使用的页被逐出，并在列表中间添加一个新页。
AHI：这是哈希索引，包含经常访问的索引页。InnoDB有一个监控索引搜索的机制。如果InnoDB注意到查询可以从构建哈希索引中受益，它会自动这样做。

我们将要讨论的问题已经在MySQL 8.0.23(更高版本)中得到解决，但这仍然与许多低版本的MySQL部署相关。从MySQL 8.0.23开始，这个drop table的过程得到了改进，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]

这里的"Database pages"和"LRU len"，表示要扫描的页。

此外，删除文件的"球"，被甩给了底层的操作系统。取决与文件有多大，系统有多繁忙，IO操作需要花费时间的。

广义地说，删除表的代价被分成以下两个任务：
1.遍历buffer pool，驱除找到的页
2.从操作系统删除文件
任务1是MySQL级别的，而任务2取决与操作系统。

我们来看看这两个方面的改进。

 

一、遍历buffer pool，驱除找到的页
让我们看看我们是否能找到提高这项任务性能的方法。
构想1：buffer pool很大，且使用的是链表结构。我们是否可以临时将buffer pool变小，从而使得链表变小呢
步骤：保存当前的buffer pool-->减少buffer pool的大小-->删除表(扫描较少的页)-->重新设置buffer pool的大小-->恢复buffer pool的内容

    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';

这听起来很容易，但是并不能起作用。因为以下原因反倒是将事情搞得更糟糕：
·缓冲池调整大小是一个阻塞操作，它仍然需要扫描列表、碎片整理和调整缓冲池大小。
·在减少的缓冲池运行会降低性能。
·相同的阻塞操作将在缓冲池扩展时重复发生。

构想2：停止使用表(表上没有读写)
·可以通过撤销用户对表的授权或控制应用程序级别的读/写来避免表操作。
·这将导致缓冲池根据MySQL的繁忙程度缓慢地逐出页。
·驱逐过程将被简化，因为它遍历链表不会找到表的任何相关页。
·也就是说，仍将扫描大型的链表。

总之，对于MySQL级别，无法避免遍历缓冲池的链表，但可以通过等待并让缓冲池最终驱逐它来节省驱逐过程的一些时间。

 

二、删除操作系统的文件

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

构想1：磁盘上的文件越小，删除的速度就越快。
我们可以通过从表中清除数据来使磁盘上的数据更小。这就是我们可以使用pt-archiver做的事情。也就是说，除非执行OPTIMIZE TABLE或虚拟更改(ALTER TABLE ... ENGINE=InnoDB)，否则表将变得碎片化，并且大小仍将保持不变。
步骤：使用pt-archiver慢慢删除表记录-->减小表大小-->给LRU算法一些时间来从缓冲池中逐出这些页-->DROP操作

这种方法涉及需要更多时间的多项活动，因为存档过程之后的冷静期可能需要更长的时间，具体取决于表的大小。现在请注意，对于较大的表，pt-archiver将需要很长时间。在我们运行虚拟更改之前，我们仍然会在磁盘上拥有一个具有大表大小的碎片表。

构想2：不删除底层表空间文件(ibd)
如果我们完全跳过文件删除操作会怎样？ MySQL将在缓冲池扫描完成后立即返回该命令！但是我们可以吗？

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

什么是硬链接
硬链接是将名称与文件相关联的目录条目。你看到的每个文件名都是硬链接。让我们稍微探索一下：

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

这里的"percona.rocks"是一个硬链接。请注意，第一个值"20594511"是由"percona.rocks"文件名表示的文件inode。 现在让我们创建另一个硬链接。

$ ln percona.rocks new_hardlink
$ 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
$

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

无需深入了解有关操作系统的更多详细信息，请了解文件名是指向磁盘上实际数据的硬链接，因此每个文件必须至少有一个硬链接。因此，磁盘中的文件只要有一个与之关联的硬链接就不会被删除。

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

$ cat percona.rocks
Percona is great and hardlink is a fun concept.
$ cat new_hardlink
Percona is great and hardlink is a fun concept.
$

利用这个知识，如果我们创建一个到数据文件(ibd)的硬链接，DROP TABLE不会真正从操作系统中删除文件，而只是删除硬链接，这对磁盘IO来说是非常轻的。

我们可以使用以下步骤来加快drop table。

 

三、在MySQL中删除大表的步骤
假设我们必须删除表large_table。
1.在服务器上创建硬链接(需要提升权限)

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

2.执行删除表操作

set SQL_LOG_BIN=0;
drop table if exists large_table;

由于我们刚刚创建的硬链接，删除表只会删除large_table.ibd 链接。原始数据仍然存在，但MySQL不知道这些剩余数据。
3.慢慢truncate文件，直到小到可以运行

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

4.执行rm操作

rm large_table.ibd.hardlink

最好确保不查询表，以避免"eviction"过程，而MySQL只是做遍历链表。

假如你有一个复制环境，建议先在从库服务器上执行，然后再进行生产。也可以通过故障转移执行此任务：在副本上执行-->故障转移-->在旧主服务器上执行。

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