我有表在sqlite大小为649876358行。我需要得到列中重复值的所有行,并在另一个文件中写入每个组,但sqlite工作非常慢(10分钟后没有一个组未写入)。我怎样才能更快地完成工作?
我使用python3 + sqlite
db = sql.connect(path_to_db)
with db:
db_cursor = db.cursor()
countLine = 649876358 #db_cursor.execute("SELECT COUNT(*) FROM 'T'").fetchall()[0][0]
for i in range(1, countLine + 1):
line = db_cursor.execute(f"SELECT * FROM 'T' WHERE ROWID = {i}", ).fetchall()[0]
value = line[0]
fileName = f"{path_to_save}{value}.json"
if os.path.exists(fileName):
continue
allValue = db_cursor.execute(f"SELECT * FROM 'T' WHERE value = '{value}'").fetchall()
if len(allValue) > 1:
with open(fileName, "w") as file:
json.dump(allValue, file)
有一些事情会有所帮助,但首先我会添加一个索引到您的数据库:
create index your_index_name_here on your_table_name_here (your_column_name_here);
我刚刚创建了一个数据库,有3列id, name, number,像这样,有100万行(你有6亿多行):
1 R7N47UCYNE 363233
2 BFDXTROHVZ 273710
3 AVN6B8RCS7 949561
4 OHWTUCWDQW 863241
5 7F0B7UDJI4 791392
6 CV5TGBAQBJ 919640
7 PP8UF35C8J 897746
8 CN2J9ZAO63 671596
9 ZPM338I8QN 601521
10 12BFX9LXGE 845189
11 2NFP6DG299 858336
12 T5GH1V609M 692232
在索引之前在数据库中搜索10个名字:
# (1999841, 'K6IWJK0MNP', 500224)
# (1715030, 'RY0N5VO9UM', 148000)
# (1999694, '474R4EMI3T', 705184)
# (1999539, 'L2YYGSLMUH', 943698)
# (1999425, 'UT352CDTQH', 828853)
# (1714958, 'KKAQ36DZGV', 279517)
# (1714905, 'BZ9CNWL5LS', 320070)
# (1714877, '6X7WCBBIZF', 247905)
# (1714839, '4MSV61HY9I', 263813)
# (1714806, '7NSRANTWEP', 476828)
# Finished in 1.3834 seconds
创建索引后:
# (1999841, 'K6IWJK0MNP', 500224)
# (1715030, 'RY0N5VO9UM', 148000)
# (1999694, '474R4EMI3T', 705184)
# (1999539, 'L2YYGSLMUH', 943698)
# (1999425, 'UT352CDTQH', 828853)
# (1714958, 'KKAQ36DZGV', 279517)
# (1714905, 'BZ9CNWL5LS', 320070)
# (1714877, '6X7WCBBIZF', 247905)
# (1714839, '4MSV61HY9I', 263813)
# (1714806, '7NSRANTWEP', 476828)
# Finished in 0.0006 seconds
您还可以设置多个游标来执行第二次搜索,而不是执行计数/范围。您的第一个查询可以是:
select_all = "SELECT * FROM your_table"
rows = cursor.execute(select_all).fetchall()
for row in rows:
value = row[0]
.....etc etc
然后使用结果生成第二个查询:
allValue = cursor2.execute(f"SELECT * FROM 'T' WHERE value = '{value}'").fetchall()
最后,SQLite可能不是处理这种行量的最佳数据库。您可以使用性能更高的数据库,并将SQLite转储到MariaDB:
sqlite3 sample.db .dump > dump.sql
然后使用如下命令进入新数据库:
mysql -p -u root -h 127.0.0.1 newdb < dump.sql