作者&＃xff1a;杨奇龙
标签&＃xff1a;MySQL、无索引、删除亿级数据
转自&＃xff1a;yangyidba(yangyidba)
一 业务需求
某业务表a 数据量大约4.7亿&＃xff0c;单表物理大小为370G&＃xff0c;其中某些指定xxid&＃61;&＃39;xxx&＃39;值的记录大约2亿。受限于磁盘空间比较紧张&＃xff0c;需要对在无索引的情况下删除无效数据。如何优雅的删除呢&＃xff1f;
二 思路
2.1 xxid本身有索引
存在索引的情况下就比较简单&＃xff0c;直接利用索引进行删除&＃xff0c;写一个for 循环语句 每次删除500行&＃xff0c;每次判断delete 影响的行数可以累加计算删除了多少行&＃xff0c;直到删除结果为0行。
delete from a where xxid&＃61;&＃39;xxx&＃39; limit 500 ;
那么问题来了 &＃xff0c;如果要求不能创建索引怎么处理&＃xff1f;
2.2 xxid 字段无索引
因为表占用的空间已经比较大 370G &＃xff0c;再添加索引会更大。因为没有索引&＃xff0c;故我们不能直接像方法一 那样 根据 where xxxid&＃61;&＃39;xxx&＃39; 删除数据&＃xff0c;那样更慢&＃xff0c;可能会引发故障。
我们采取分而治之的方式&＃xff0c;基于主键把表的数据分段&＃xff0c;比如每段1000行-2000行(如果主键id不连续 则实际数据量会小于指定分段数据)。然后在这1000行里面删除指定的数据&＃xff0c;这样delete的执行效率会比直接依赖 xxxid&＃61;&＃39;xxx&＃39; 好很多。
1 select min(a.id) min_id,max(a.id) max_id
from (select id from a where id>{init_id} order by id limit 1000) a
2 delete from a where xxid&＃61;&＃39;xxx&＃39; and id >&＃61;min_id and id <&＃61;max_id
3 init_id &＃61; max_id
代码如下:
def get_current_max_id():
"""
获取当前最大的id
:return:
"""
get_max_id &＃61; """select max(a.id) max_id from a"""
try:
mydb &＃61; pymysql.connect(
host&＃61;IP,
port&＃61;int(PORT),
user&＃61;USER,
read_timeout&＃61;5, write_timeout&＃61;5,
charset&＃61;&＃39;utf8&＃39;, autocommit&＃61;True)
cursor &＃61; mydb.cursor(pymysql.cursors.DictCursor)
cursor.execute(get_max_id)
data &＃61; cursor.fetchall()
except Exception as e:
print traceback.format_exc(e)
exit(0)
finally:
mydb.close()
print "we get max id of table : %s" % (data[0][&＃39;max_id&＃39;])
return data[0][&＃39;max_id&＃39;]
def get_min_max_id(min_id):
"""
:param min_id:
:return:
"""
get_ids &＃61; """select min(a.id) min_id,max(a.id) max_id from
(select id from a where id>{init_id} order by id limit 2000) a
""".format(init_id&＃61;min_id)
try:
mydb &＃61; pymysql.connect(
host&＃61;IP,
port&＃61;int(PORT),
user&＃61;USER,
read_timeout&＃61;5, write_timeout&＃61;5,
charset&＃61;&＃39;utf8&＃39;, database&＃61;&＃39;test&＃39;, autocommit&＃61;True)
cursor &＃61; mydb.cursor(pymysql.cursors.DictCursor)
cursor.execute(get_ids)
data &＃61; cursor.fetchall()
except Exception as e:
print traceback.format_exc(e)
exit(0)
finally:
mydb.close()
return data[0][&＃39;min_id&＃39;], data[0][&＃39;max_id&＃39;]
def del_tokens(min_id, max_id):
"""
:param min_id:
:param max_id:
:return:
"""
del_token &＃61; """delete from a
where client_id in (&＃39;xxx&＃39;,&＃39;yyy&＃39;) and id>&＃61;%s and id<&＃61;%s """
try:
mydb &＃61; pymysql.connect(
host&＃61;IP,
port&＃61;int(PORT),
user&＃61;USER,
read_timeout&＃61;5, write_timeout&＃61;5,
charset&＃61;&＃39;utf8&＃39;, database&＃61;&＃39;test&＃39;, autocommit&＃61;True)
cursor &＃61; mydb.cursor(pymysql.cursors.DictCursor)
rows &＃61; cursor.execute(del_token, (min_id, max_id))
except Exception as e:
print traceback.format_exc(e)
exit(0)
finally:
mydb.close()
return rows
def get_last_del_id(file_name):
if not os.path.exists(file_name):
print "{file} is not exist ,exit .".format(file&＃61;file_name)
exit(-1)
with open(file_name, &＃39;r&＃39;) as fh:
del_id &＃61; fh.readline().strip()
if not del_id.isdigit():
print "it is &＃39;{delid}&＃39;, not a num , exit ".format(delid&＃61;del_id)
exit(-1)
return del_id
def main():
file_name &＃61; &＃39;/tmp/del_aid.id&＃39;
rows_deleted &＃61; 0
maxid &＃61; get_current_max_id()
init_id &＃61; get_last_del_id(file_name)
while True:
min_id, max_id &＃61; get_min_max_id(init_id)
if max_id > maxid:
with open(&＃39;/tmp/del_aid.id&＃39;, &＃39;w&＃39;) as f:
f.write(str(min_id))
print "delete end at : {end_id}".format(end_id&＃61;init_id)
exit(0)
rows &＃61; del_tokens(int(min_id), int(max_id))
init_id &＃61; max_id
rows_deleted &＃43;&＃61; rows
print "delete at %d ,and we have deleted %d rows " % (max_id, rows_deleted)
time.sleep(0.3) ### 可以控制每秒删除的速度
if __name__ &＃61;&＃61; &＃39;__main__&＃39;:
main()
这个脚本可以记录上一次的id&＃xff0c;用上一次id 作为 init_id进行删除。第一次使用的时候需要手工初始化/tmp/del_aid.id 比如写入 0 或者符合条件的最小主键 id。
2.3 如何更快速的删除
这个环节就当做思考题吧&＃xff0c;可以不考虑从库的延迟。大家有什么好的思路&＃xff0c;可以分享一下。
推荐文章
工具|Explain 使用分析
哪些因素会导致慢查询&＃xff1f;
关于「3306π」社区
围绕 MySQL 核心技术&＃xff0c;将互联网行业中最重要的数据化解决方案带到传统行业中&＃xff1b;囊括其他开源技术Redis、MongoDB、Hbase、Hadoop、ElasticSearch、Storm、Spark等&＃xff1b;分享干货知识&＃xff0c;即便是赞助商&＃xff0c;也要求如此&＃xff0c;拒绝放水。
京公网安备 11010802041100号