点击蓝色“程序猿DD”关注我哟

加个“星标”，不忘签到哦

来源：yangyidba

关注我，回复口令获取可获取独家整理的学习资料：

- 001：领取《Spring Boot基础教程》

- 002：领取《Spring Cloud基础教程》

- 003：领取《Java开发规范1.5》（最新版）

一、业务需求

某业务表a 数据量大约4.7亿，单表物理大小为370G，其中某些指定xxid='xxx'值的记录大约2亿。受限于磁盘空间比较紧张，需要对在无索引的情况下删除无效数据。如何优雅的删除呢？

二、思路

2.1 xxid本身有索引

存在索引的情况下就比较简单，直接利用索引进行删除，写一个for 循环语句 每次删除500行，每次判断delete 影响的行数可以累加计算删除了多少行，直到删除结果为0行。

1. delete from a where xxid='xxx' limit 500 ;

那么问题来了 ，如果要求不能创建索引怎么处理？

2.2 xxid 字段无索引

因为表占用的空间已经比较大 370G ，再添加索引会更大。因为没有索引，故我们不能直接像方法一 那样 根据 where xxxid='xxx' 删除数据，那样更慢，可能会引发故障。

我们采取分而治之的方式，基于主键把表的数据分段，比如每段1000行-2000行(如果主键id不连续 则实际数据量会小于指定分段数据)。然后在这1000行里面删除指定的数据，这样delete的执行效率会比直接依赖 xxxid='xxx' 好很多。

1. 1 select min(a.id) min_id,max(a.id) max_id

2. from (select id from a where id>{init_id} order by id limit 1000) a

3. 
   

4. 2 delete from a where xxid='xxx' and id >=min_id and id <=max_id

5. 
   

6. 3 init_id = max_id

代码如下:

1. def get_current_max_id():

2. """

3. 获取当前最大的id

4. :return:

5. """

6. get_max_id = """select max(a.id) max_id from a"""

7. try:

8. mydb = pymysql.connect(

9. host=IP,

10. port=int(PORT),

11. user=USER,

12. read_timeout=5, write_timeout=5,

13. charset='utf8', autocommit=True)

14. cursor = mydb.cursor(pymysql.cursors.DictCursor)

15. cursor.execute(get_max_id)

16. data = cursor.fetchall()

17. except Exception as e:

18. print traceback.format_exc(e)

19. exit(0)

20. finally:

21. mydb.close()

22. 
    

23. print "we get max id of table : %s" % (data[0]['max_id'])

24. return data[0]['max_id']

25. 
    

26. 
    

27. def get_min_max_id(min_id):

28. """

29. 
    

30. :param min_id:

31. :return:

32. """

33. get_ids = """select min(a.id) min_id,max(a.id) max_id from

34. (select id from a where id>{init_id} order by id limit 2000) a

35. """.format(init_id=min_id)

36. 
    

37. try:

38. mydb = pymysql.connect(

39. host=IP,

40. port=int(PORT),

41. user=USER,

42. read_timeout=5, write_timeout=5,

43. charset='utf8', database='test', autocommit=True)

44. cursor = mydb.cursor(pymysql.cursors.DictCursor)

45. cursor.execute(get_ids)

46. data = cursor.fetchall()

47. except Exception as e:

48. print traceback.format_exc(e)

49. exit(0)

50. finally:

51. mydb.close()

52. 
    

53. return data[0]['min_id'], data[0]['max_id']

54. 
    

55. 
    

56. def del_tokens(min_id, max_id):

57. """

58. :param min_id:

59. :param max_id:

60. :return:

61. """

62. del_token = """delete from a

63. where client_id in ('xxx','yyy') and id>=%s and id<=%s """

64. try:

65. mydb = pymysql.connect(

66. host=IP,

67. port=int(PORT),

68. user=USER,

69. read_timeout=5, write_timeout=5,

70. charset='utf8', database='test', autocommit=True)

71. cursor = mydb.cursor(pymysql.cursors.DictCursor)

72. rows = cursor.execute(del_token, (min_id, max_id))

73. except Exception as e:

74. print traceback.format_exc(e)

75. exit(0)

76. finally:

77. mydb.close()

78. 
    

79. return rows

80. 
    

81. 
    

82. def get_last_del_id(file_name):

83. if not os.path.exists(file_name):

84. print "{file} is not exist ,exit .".format(file=file_name)

85. exit(-1)

86. 
    

87. with open(file_name, 'r') as fh:

88. del_id = fh.readline().strip()

89. 
    

90. if not del_id.isdigit():

91. print "it is '{delid}', not a num , exit ".format(delid=del_id)

92. exit(-1)

93. 
    

94. return del_id

95. 
    

96. 
    

97. def main():

98. file_name = '/tmp/del_aid.id'

99. rows_deleted = 0

100. maxid = get_current_max_id()

101. init_id = get_last_del_id(file_name)

102. 
     

103. while True:

104. min_id, max_id = get_min_max_id(init_id)

105. if max_id > maxid:

106. with open('/tmp/del_aid.id', 'w') as f:

107. f.write(str(min_id))

108. print "delete end at : {end_id}".format(end_id=init_id)

109. exit(0)

110. 
     

111. rows = del_tokens(int(min_id), int(max_id))

112. init_id = max_id

113. rows_deleted += rows

114. print "delete at %d ,and we have deleted %d rows " % (max_id, rows_deleted)

115. time.sleep(0.3) ### 可以控制每秒删除的速度

116. 
     

117. 
     

118. if __name__ == '__main__':

119. main()

这个脚本可以记录上一次的id，用上一次id 作为 init_id进行删除。第一次使用的时候需要手工初始化/tmp/del_aid.id 比如写入 0 或者符合条件的最小主键 id。

2.3 如何更快速的删除

这个环节就当做思考题吧，可以不考虑从库的延迟。大家有什么好的思路，可以分享一下。

推荐关注

本文作者的个人公众号，长期关注于数据库技术以及性能优化，故障案例分析，数据库运维技术知识分享，个人成长和自我管理等主题，欢迎扫码关注。

推荐阅读

• 再见微服务，从100多个问题儿童到一个超级明星
  

• JDK 13 的五大新特性！你最中意哪个？
  

• MySQL百万级数据分页查询及优化
  

• 这个注解一次搞定限流与熔断降级
  

• 阿里云新版主页，我还以为打开了淘宝..

活动介绍：自律到极致-人生才精致：第9期

活动奖励：

• 一等奖：天猫精灵 * 1
  

• 二等奖：我的星球会员 * 5

扫描下面二维码签到参与

关注我，加个星标，不忘签到哦~

2019

与大家聊聊技术人的斜杠生活

点一点“阅读原文”小惊喜在等你