| 失效链接处理 |
|
MySQL 快速删除大量数据（千万级别）的几种实践方案详解 PDF 下载
本站整理下载：
相关截图：
主要内容：
笔者最近工作中遇见一个性能瓶颈问题，MySQL表，每天大概新增776万条记录，存储周期为7天，超过7天的数据需要在新
增记录前老化。连续运行9天以后，删除一天的数据大概需要3个半小时（环境：128G, 32核，4T硬盘），而这是不能接受
的。当然如果要整个表删除，毋庸置疑用
TRUNCATE TABLE就好。
最初的方案（因为未预料到删除会如此慢），代码如下（最简单和朴素的方法）:
delete from table_name where cnt_date <= target_date
后经过研究，最终实现了飞一般(1秒左右)的速度删除770多万条数据，单张表总数据量在4600万上下，优化过程的方案层层
递进，详细记录如下：
批量删除（每次限定一定数量），然后循环删除直到全部数据删除完毕；同时key_buffer_size 由默认的8M提高到512M
运行效果：删除时间大概从3个半小时提高到了3小时
（1）通过limit(具体size 请酌情设置）限制一次删除的数据量，然后判断数据是否删除完，附源码如下（Python实现）：
def delete_expired_data(mysqlconn, day):
mysqlcur = mysqlconn.cursor()
delete_sql = "DELETE from table_name where cnt_date<='%s' limit 50000" % day
query_sql = "select srcip from table_name where cnt_date <= '%s' limit 1" % day
try:
df = pd.read_sql(query_sql, mysqlconn)
while True:
if df is None or df.empty:
break
mysqlcur.execute(delete_sql)
mysqlconn.commit()
df = pd.read_sql(query_sql, mysqlconn)
except:
mysqlconn.rollback()
（2）增加key_buffer_size
mysqlcur.execute("SET GLOBAL key_buffer_size = 536870912")
key_buffer_size是global变量，详情参见Mysql官方文档：https://dev.mysql.com/doc/refman/5.7/en/server-configuration.html
DELETE QUICK + OPTIMIZETABLE
适用场景：MyISAM Tables
Why: MyISAM删除的数据维护在一个链表中，这些空间和行的位置接下来会被Insert的数据复用。 直接的delete后，mysql会
合并索引块，涉及大量内存的拷贝移动；而OPTIMIZE TABLE直接重建索引，即直接把数据块情况，再重新搞一份（联想
JVM垃圾回收算法）。
运行效果：删除时间大3个半小时提高到了1小时40分
具体代码如下：
def delete_expired_data(mysqlconn, day):
mysqlcur = mysqlconn.cursor()
delete_sql = "DELETE QUICK from table_name where cnt_date<='%s' limit 50000" % day
query_sql = "select srcip from table_name where cnt_date <= '%s' limit 1" % day
optimize_sql = "OPTIMIZE TABLE g_visit_relation_asset"
try:
df = pd.read_sql(query_sql, mysqlconn)
while True:
if df is None or df.empty:
break
mysqlcur.execute(delete_sql)
mysqlconn.commit()
df = pd.read_sql(query_sql, mysqlconn)
mysqlcur.execute(optimize_sql)
mysqlconn.commit()
except:
mysqlconn.rollback()
表分区，直接删除过期日期所在的分区（最终方案—秒杀）
MySQL表分区有几种方式，包括RANGE、KEY、LIST、HASH，具体参见官方文档。因为这里的应用场景日期在变化，所以
不适合用RANGE设置固定的分区名称，HASH分区更符合此处场景
|
| Java1234官方群25： | 
|
| Java1234官方群25： | 838462530 |
苏公网安备 32061202001004号
