Sqlite分析大表

Question

我有表在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)

Answer 1

有一些事情会有所帮助，但首先我会添加一个索引到您的数据库:

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