mysql千万级表数据删除方式
背景：客户反应页面加载慢，半天无法刷新，经过排查发现是有张表的数据量达到了 5kw级别，因此需要删除旧的数据，保留最新半年的数据即可
删除方式：批量删除，具体操作方式如下
1 检查 su_num 表一共有多少数据
mysql> select count(*) from su_num; +----------+ | count(*) | +----------+ | 58296808 | +----------+
2 半年之内数据量检查 2021-11-07 10:06:08----2022-05-30 23:00:00
mysql> select count(*) 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'; +----------+ | count(*) | +----------+ | 9187486 | +----------+
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，经过测试发现 mysql 删除数据最保险的条数是每次删除 5000 条数据，过多会影响mysql性能
