面试题|无索引如何删除亿级数据？

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

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='xxx' and id >=min_id and id <=max_id 
3 init_id = max_id

def get_current_max_id():    """    获取当前最大的id    :return:    """    get_max_id = """select max(a.id) max_id from a"""    try:        mydb = pymysql.connect(                    host=IP,                    port=int(PORT),                    user=USER,                    read_timeout=5, write_timeout=5,                    charset='utf8',  autocommit=True)        cursor = mydb.cursor(pymysql.cursors.DictCursor)        cursor.execute(get_max_id)        data = 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]['max_id'])    return data[0]['max_id']

def get_min_max_id(min_id):    """
    :param min_id:    :return:    """    get_ids = """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=min_id)
    try:        mydb = pymysql.connect(                    host=IP,                    port=int(PORT),                    user=USER,                    read_timeout=5, write_timeout=5,                    charset='utf8', database='test', autocommit=True)        cursor = mydb.cursor(pymysql.cursors.DictCursor)        cursor.execute(get_ids)        data = cursor.fetchall()    except Exception as e:        print traceback.format_exc(e)        exit(0)    finally:        mydb.close()
    return data[0]['min_id'], data[0]['max_id']

def del_tokens(min_id, max_id):    """    :param min_id:    :param max_id:    :return:    """    del_token = """delete from a                    where client_id in ('xxx','yyy') and id>=%s and id<=%s """    try:        mydb = pymysql.connect(                    host=IP,                    port=int(PORT),                    user=USER,                    read_timeout=5, write_timeout=5,                    charset='utf8', database='test', autocommit=True)        cursor = mydb.cursor(pymysql.cursors.DictCursor)        rows = 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=file_name)        exit(-1)
    with open(file_name, 'r') as fh:        del_id = fh.readline().strip()
    if not del_id.isdigit():        print "it is '{delid}', not a num , exit ".format(delid=del_id)        exit(-1)
    return del_id

def main():    file_name = '/tmp/del_aid.id'    rows_deleted = 0    maxid = get_current_max_id()    init_id = get_last_del_id(file_name)
    while True:        min_id, max_id = get_min_max_id(init_id)        if max_id > maxid:            with open('/tmp/del_aid.id', 'w') as f:                f.write(str(min_id))            print "delete end at : {end_id}".format(end_id=init_id)            exit(0)
        rows = del_tokens(int(min_id), int(max_id))        init_id = max_id        rows_deleted += rows        print "delete at  %d ,and we have  deleted %d  rows " % (max_id, rows_deleted)        time.sleep(0.3)  ### 可以控制每秒删除的速度

if __name__ == '__main__':    main()