echarts无数据时显示无数据_面试题|无索引如何删除亿级数据？

作者&＃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;拒绝放水。