在100,000,000行上加速查询

Question

我有两个脚本。一个用于向SQLite数据库添加数据，一个用于使用3个不同的查询。数据库将有1亿行和2列(平均每条记录50个字符)。

问题1:一旦我添加了大约1000万个CSV文件，我添加的每个CSV文件都需要几个小时才能导入。

问题2:当我运行查询脚本时，它需要超过30分钟才能加载。实际的查询只需要几秒钟。还有更好的办法吗?

输入脚本:

import sqlite3
conn = sqlite3.connect('Main_Database.db')
c = conn.cursor()
# c.execute('SELECT * FROM user_data')
# result = c.fetchall()
with open('my_list_2.txt') as f:
for line in f:
try:
l = line.split('n')[0].split(':')
# print(l[0])
# print(l[1])
c.execute("INSERT INTO user_data VALUES (?,?)", (l[0], l[1]))
except IndexError:
pass
except sqlite3.IntegrityError:
pass
conn.commit()
conn.close()

查询脚本:

import sqlite3
from datetime import datetime
from datetime import date
import re
import csv
import numpy as np
conn = sqlite3.connect('Main_Database.db')
c = conn.cursor()
c.execute("SELECT * FROM user_data")
items = c.fetchall()
exact_match_mail = 'abc123'
#method = 'method 1'
word_match_mail = 'hot'
#method = 'method 2'
method = 'method 3'
word_match_mail_one = 'hot'
word_match_mail_two = 'chocolate'
L = []
if method == 'method 1':
for x, y in items:
if x == exact_match_mail:
print('there is an exact match to the email: ', x)
L.append(x)
elif method == 'method 2':
for x, y in items:
if word_match_mail in re.split("@|_", x):
print('Partial match based on one word found in the email: ', x)
L.append(x)
elif method == 'method 3':
for x, y in items:
if word_match_mail_one in re.split("@|_", x) and word_match_mail_two in re.split("@|_", x):
print('Partial match based on two words found in the email: ', x)
L.append(x)
a = input('press any key to export result to a csv file')
if a != np.nan:
f = open('saved_emails.csv', 'a', encoding='UTF8')
writer = csv.writer(f)
now = datetime.now()
current_time = now.strftime("%H:%M:%S")
today = date.today()
d1 = today.strftime("%d/%m/%Y")
row = "********* report "+d1+" "+current_time+" **************"
writer.writerow([row])
row = "method used for this query is: " + method
writer.writerow([row])
row = "emails obtained are: "
writer.writerow([row])
for x in L:
writer.writerow([x])
f.close()
conn.commit()
conn.close()

Answer 1

看起来你使用了一个数据库，但是没有从它提供的特性中受益。

1. 一个接一个地插入大量记录是低效的。数据库总是提供import和dump特性。在这里，您可以使用import并提供平面文件:例如文本或csv。

2. 数据库是用来存储和搜索数据的。这里看起来你只是把它当作一个虚拟存储。你需要所有的数据，然后手工选择。您有SQL(结构化查询语言)，它允许对数据执行简单和复杂的选择和各种操作。SELECT * FROM user_data WHERE ...

数据库非常强大。您甚至可以在列上创建索引，以提高性能。

别担心。100万行不算多。特别是当行仅由两列组成时(如示例中)。它应该仍然可以在单个机器上运行。