MySQL 学习笔记(九 )--大表的删除
1.Time Zone Variables (The server current time zone)
The global time_zone system variable indicates the time zone the server currently is operating in. The initial time_zone value is 'SYSTEM', which indicates that the server time zone is the same as the system time zone.
If set to SYSTEM, every MySQL function call that requires a time zone calculation makes a system library call to determine the current system time zone. This call may be protected by a global mutex, resulting in contention.
2.Time Zone Variables (Per-session time zones)
Each client that connects has its own session time zone setting, given by the session time_zone variable. Initially, the session variable takes its value from the global time_zone variable, but the client can change its own time zone with this statement.
The session time zone setting affects display and storage of time values that are zone-sensitive. This includes the values displayed by functions such as NOW() or CURTIME(), and values stored in and retrieved from TIMESTAMP columns. Values for TIMESTAMP columns are converted from the session time zone to UTC for storage, and from UTC to the session time zone for retrieval.
The session time zone setting does not affect values displayed by functions such as UTC_TIMESTAMP() or values in DATE, TIME, or DATETIME columns. Nor are values in those data types stored in UTC; the time zone applies for them only when converting from TIMESTAMP values. If you want locale-specific arithmetic for DATE, TIME, or DATETIME values, convert them to UTC, perform the arithmetic, and then convert back.
3.MySQL大表的删除
背景
为了减少磁盘空间大小的消耗,有时要求将部分表删除,但是如果表比较大,例如 400G+,执行drop table的操作,消耗的时间可能达到几十秒,并且实例容易被夯住,通过监控发现,数据库的QPS瞬间下来很多。如果有配置高可用(例如MHA),此时还容易主从切换。
删除过程
主要分为以下两个过程
1.Buffer Pool页面清除过程。在删除表的时候,Innodb会将文件在Buffer Pool中对应的页面清除。对于删除表的页面清除,只需要将页面从flush队列中删除即可,而不需要去做flush操作,这样可以减小对系统的冲击。
2.删除ibd磁盘文件的过程。具体到对buffer pool 的影响,删除线程首先会根据要删除表的space id ,从buffer pool 中每一个buffer pool 实例的flush list 中找到属于被删除表的页面。在每一个实例中搜索页面时,会持有各自buffer pool实例的锁,然后遍历搜索这个buffer pool实例,如果找到了对应的页面,就会将这个页面从flush list中删除,并且将其oldest_modification设置为0,用来表示这个页面已经失效。不过,这个操作只是将其从flush list 中删除了,它还会在buffer pool 的空闲池中存在,以便重新使用。
存在的问题是,如果buffer pool 很大,或者是在buffer pool中有很多需要被flush 的页面,那么此时遍历扫描页面就会占用比较长的时间,导致其他事务在用到相应buffer pool 实例时被阻塞,从而影响整个数据库的性能。此外,在删除ibd文件时,如果文件过大也会带来大量的I/O操作,并且耗时。
优化方案
第一个过程,涉及源码部分,优化相对困难,所以将重点放在了第二个过程中的删除ibd文件上。
可以在删除ibd文件之前,对ibd文件做一个硬链接来加速删除,减少对数据库造成的影响。
# 创建硬链接 # ln /data/mysql/testdb/qqpay_bills.ibd /data/mysql/testdb/qqpay_bills.ibd_hdlk
上面的命令执行后,会发现多了一个qqpay_bills.ibd_hdlk文件。通过 ls -lh 可以看到 qqpay_bills.ibd_hdlk 和 qqpay_bills.ibd的node数均为2。我们知道,一个磁盘上的文件,可以有多个文件系统的文件引用,这多个文件是完全相同的,都指向同一个磁盘上的文件,当我们删除任何一个文件的时候,都不会影响真实的文件,只是将其被引用数目减1,只有当被引用数目为1的时候,再次删除文件,才会被真正删除。删除时,这两种情况的区别很大,一个是在减少被引用数目,一个是真正做IO来删除。我们正是利用了这个特点,将由MySQL来删除大文件的操作转换为一个简单的操作系统级的文件删除,从而减少对MySQL的影响。
建立硬链接,再执行drop table,如果还是担心删除大文件对操作系统有影响,可以借由操作系统的truncate 命令。大多数Linux发行版附带truncate命令。Truncate命令通常用于将文件缩小或扩展到指定的大小。
可以通过一个脚本循环分块删除,慢慢清理文件。编写truncatebigfile_qqpay_bills.sh文件
#! /usr/bin/bash # author dba TRUNCATE=/usr/bin/truncate filename=/data/mysql/testdb/qqpay_bills.ibd_hdlk size=`du -sm ${filename}|awk '{print $1}'` echo ${size} for i in `seq ${size} -1000 1000`; do sleep 2 echo "${TRUNCATE} -s ${i}M $filename" $TRUNCATE -s ${i}M $filename done rm -rf ${filename}
执行sh文件,循环小批量删除,文件小于1G时,再执行rm 。
注意
此方法不适用Galera Cluster。
文档学习
1.https://www.jb51.net/article/260544.htm
【推荐】编程新体验,更懂你的AI,立即体验豆包MarsCode编程助手
【推荐】凌霞软件回馈社区,博客园 & 1Panel & Halo 联合会员上线
【推荐】抖音旗下AI助手豆包,你的智能百科全书,全免费不限次数
【推荐】博客园社区专享云产品让利特惠,阿里云新客6.5折上折
【推荐】轻量又高性能的 SSH 工具 IShell:AI 加持,快人一步
· 一个费力不讨好的项目,让我损失了近一半的绩效!
· 清华大学推出第四讲使用 DeepSeek + DeepResearch 让科研像聊天一样简单!
· 实操Deepseek接入个人知识库
· CSnakes vs Python.NET:高效嵌入与灵活互通的跨语言方案对比
· Plotly.NET 一个为 .NET 打造的强大开源交互式图表库