使用python3和SQLi的大容量插入性能较差

Question

我有几个包含网址的文本文件。我正在尝试创建一个SQLite数据库来将这些url存储在一个表中。URL表有两列，即主键（INTEGER）和URL（TEXT）。在

我尝试在一个insert命令和循环中插入100000个条目，直到我完成URL列表。基本上，读取所有的文本文件内容并保存在列表中，然后我使用createsmall list of 100000 entries and insert in table。在

文本文件中的总URL为4591415，文本文件的总大小约为97.5MB。在

问题：

1. 当我选择文件数据库时，插入大约需要7-7.5分钟。我觉得这不是一个非常快的插入，因为我有固态硬盘，读/写速度更快。除此之外，我还有大约10GB的RAM可用，如TaskManager中所示。处理器为i5-6300U 2.4Ghz。

2. 总的文本文件约为97.5 MB。但在我将url插入SQLite之后，SQLite数据库大约为350MB，即几乎是原始数据大小的3.5倍。因为数据库不包含任何其他表、索引等，所以这个数据库大小看起来有点奇怪。

对于问题1，我尝试使用参数，并根据不同参数的测试运行得出最佳参数。在

table, th, td { border: 1px solid black; border-collapse: collapse; } th, td { padding: 15px; text-align: left; } ^{pr2}$

Answer 1

列“url”的唯一约束是在url上创建隐式索引。这就解释了尺寸的增加。在

我不认为您可以填充表，然后添加unique约束。在

你的瓶颈肯定是CPU。尝试以下操作：

1. 安装工具z:pip install toolz

2. 使用此方法：

   from toolz import partition_all
   
   def add_blacklist_url(self, urls):
       # print('add_blacklist_url:: entries = {}'.format(len(urls)))
       start_time = datetime.now()
       for batch in partition_all(100000, urls):
           try:
               start_commit = datetime.now()
               self.cursor.executemany('''INSERT OR IGNORE INTO blacklist(url) VALUES(:url)''', batch)
               end_commit = datetime.now() - start_commit
               print('add_blacklist_url:: total time for INSERT OR IGNORE INTO blacklist {} entries = {}'.format(len(templist), end_commit))
           except sqlite3.Error as e:
               print("add_blacklist_url:: Database error: %s" % e)
           except Exception as e:
               print("add_blacklist_url:: Exception in _query: %s" % e)
       self.db.commit()
       time_elapsed = datetime.now() - start_time
       print('add_blacklist_url:: total time for {} entries = {}'.format(records, time_elapsed))
   

代码未经测试。在

Answer 2

默认情况下，SQLite使用自动提交模式。这允许省略begin transaction。但是这里我们希望所有的插入都在一个事务中，唯一的方法就是用begin transaction启动一个事务，这样所有要运行的语句都在该事务中。在

方法executemany只是在Python外部对execute执行的循环，它只调用SQLite prepare语句函数一次。在

以下是从列表中删除最后N项的一种非常糟糕的方法：

    templist = []
    i = 0
    while i < self.bulk_insert_entries and len(urls) > 0:
        templist.append(urls.pop())
        i += 1

最好这样做：

^{pr2}$

切片和del切片甚至可以在空列表上工作。在

两者的复杂度可能相同，但100K次append和pop调用的成本远高于让Python在解释器之外执行。在