mysql千万级表数据删除方式

背景：客户反应页面加载慢，半天无法刷新，经过排查发现是有张表的数据量达到了 5kw级别，因此需要删除旧的数据，保留最新半年的数据即可

删除方式：批量删除

1 检查表一共有多少数据

2 半年之内数据量检查 2021-11-07 10:06:08----2022-05-30 23:00:00

3 删除时如果使用delete无法全部删除这么多的数据，一次最多删除1w行数据即可

delete from su_num where st_time >= '2021-11-07 10:06:08' and st_time <= '2022-05-30 23:00:00' and num_id='1274' limit 10000;

4 如果手动删除比较慢，则可以使用脚本进行操作
(1) python脚本

 1 import pymysql
 2 import datetime
 3 
 4 """
 5 此方式需要安装：
 6 pip3 install pymysql==0.10.1 --trusted-host mirrors.aliyun.com
 7 
 8 说明：优化之后的删除方式
 9 1 分批删除，每次50W的数据
10 2 设置key_buffer_size从原来的8MB为目前的512MB，此参数的推荐值为 < 物理剩余内存*70%
11 
12 运行方式：/usr/bin/python3 脚本名称
13 10 18 * * * /usr/bin/python3 脚本名称.py >> ./test.logs 2>&1
14 """
15 
16 def read_file(start_time,end_time, at_num):
17     today_time = datetime.datetime.now()
18     conn = pymysql.connect(host='x.x.x.x', user='xxxx', password='xxxxx', port=xxxx, database='db_stu_num')
19     select_sql = "select * from su_num where st_time > '{0}' and st_time < '{1}' and act_id='{2}' limit 1".format(start_time, end_time, at_num)
20     delete_sql="delete from su_num where st_time > '{0}' and st_time < '{1}' and act_id='{2}' limit 500000".format(start_time, end_time, at_num)
21     number = 0
22     try:
23         with conn.cursor() as cur:
24             cur.execute("SET GLOBAL key_buffer_size = 536870912")
25             while True:
26                 comm_select = cur.execute(select_sql)
27                 print("符合条件的语句一共 {0} 条".format(comm_select))
28                 if (comm_select is None) or (comm_select == 0):
29                     break
30                 else:
31                     comm_delete = cur.execute(delete_sql)
32                     conn.commit()
33                     number += 1
34                     print("第 {0} 次删除的数据为 {1} 条".format(number, comm_delete))
35     except Exception as e:
36         conn.rollback()
37         print("报错内容为 {0}".format(e))
38     finally:
39         conn.close()
40     now_time = datetime.datetime.now()
41     print("执行操作的开始时间为: {0}".format(today_time))
42     print("执行操作的结束时间为: {0}".format(now_time))
43     print("执行操作耗时 {0} 秒".format((now_time - today_time).total_seconds()))
44 
45 if __name__ == "__main__":
46     read_file("2021-11-07 10:06:08", "2022-05-30 23:00:00", 1274)

(2) shell脚本

 1 #!/bin/bash
 2 
 3 dbuser='xxxx'
 4 passwd='xxxxxx'
 5 hostip='x.x.x.x'
 6 hostport='xxxx'
 7 sum_num=9187486
 8 dataname='db_stu_num'
 9 tablename='su_num '
10 mycmd="/usr/bin/mysql -u ${dbuser} -p${passwd} -h ${hostip} -P ${hostport}"
11 
12 for ((i=10000; i<=${sum_num}; i+=10000))
13 do
14     ${mycmd} -e "use ${dataname}; delete from ${tablename} where st_time >= '2021-11-07 10:06:08' and st_time <= '2022-05-30 23:00:00' and act_id='1274' limit 10000;"
15 done

(3) 小结：mysql删除10000条数据最快 30.65 sec  最慢 1 min 59.07 sec