MySQL 快速删除大量数据（千万级别）的几种实践方案详解_Mysql

MySQL 快速删除大量数据（千万级别）的几种实践方案详解

笔者最近工作中遇见一个性能瓶颈问题，mysql表，每天大概新增776万条记录，存储周期为7天，超过7天的数据需要在新增记录前老化。连续运行9天以后，删除一天的数据大概需要3个半小时（环境：128g, 32核，4t硬盘），而这是不能接受的。当然如果要整个表删除，毋庸置疑用

truncate table就好。

最初的方案（因为未预料到删除会如此慢），代码如下（最简单和朴素的方法）:

1	`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

1	`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分区更符合此处场景

（1）分区表定义，sql语句如下：

1	`alter` `table` `table_name partition` `by` `hash(to_days(cnt_date)) partitions 7;`

to_days将日期（必须为日期类型，否则会报错:constant, random or timezone-dependent expressions in (sub)partitioning function are not allowed）转换为天数（年月日总共的天数），然后hash；建立7个分区。实际上，就是 days mod 7。

（2）查询出需要老化的日期所在的分区,sql语句如下：

1	`"explain partitions select * from g_visit_relation_asset where cnt_date = '%s'"` `% expired_day`

执行结果如下（partitions列即为所在分区）：

+----+-------------+------------------+------------+------+----------------+------+---------+------+---------+----------+-------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | extra |
+----+-------------+------------------+------------+------+----------------+------+---------+------+---------+----------+-------------+
| 1 | simple | table_name | p1 | all | cnt_date_index | null | null | null | 1325238 | 100.00 | using where |
+----+-------------+------------------+------------+------+----------------+------+---------+------+---------+----------+-------------+
1 row in set, 2 warnings (0.00 sec)

（3）optimize or rebuild partition,sql语句如下：

1	`"alter table g_visit_relation_asset optimize partition '%s'"` `% partition`

完整代码如下【python实现】，循环删除小于指定日期的数据：

									def clear_partition_data(mysqlconn, day):

									 mysqlcur = mysqlconn.cursor()

									 expired_day = day

									 query_partition_sql = "explain partitions select * from table_name where cnt_date = '%s'" % expired_day

									 # optimize or rebuild after truncate partition

									 try: 

									  while true:

									   df = pd.read_sql(query_partition_sql, mysqlconn)

									   if df is none or df.empty:

									    break

									   partition = df.loc[0, 'partitions']

									   if partition is not none:

									    clear_partition_sql = "alter table table_name truncate partition %s" % partition

									    mysqlcur.execute(clear_partition_sql)

									    mysqlconn.commit()

									    optimize_partition_sql = "alter table table_name optimize partition %s" % partition

									    mysqlcur.execute(optimize_partition_sql)

									    mysqlconn.commit()

									   expired_day = (expired_day - timedelta(days = 1)).strftime("%y-%m-%d")

									   df = pd.read_sql(query_partition_sql, mysqlconn)

									 except:

									  mysqlconn.rollback()

其它

如果删除的数据超过表数据的百分之50，建议拷贝所需数据到临时表，然后删除原表，再重命名临时表为原表，附mysql如下：

									insert into new

									 select * from main

									  where ...; -- just the rows you want to keep

									rename table main to old, new to main;

									drop table old; -- space freed up here