使用Python的oursql存储数据到MySQL数据库为何缓慢？
我正在解析一个非常非常大的 XML 文件。里面有几百万条像这样的文章条目：
<article key="journals/cgf/HaeglerWAGM10" mdate="2010-11-12">
<author>Simon Haegler</author>
<author>Peter Wonka</author>
<author>Stefan Müller Arisona</author>
<author>Luc J. Van Gool</author>
<author>Pascal Müller</author>
<title>Grammar-based Encoding of Facades.</title>
<pages>1479-1487</pages>
<year>2010</year>
<volume>29</volume>
<journal>Comput. Graph. Forum</journal>
<number>4</number>
<ee>http://dx.doi.org/10.1111/j.1467-8659.2010.01745.x</ee>
<url>db/journals/cgf/cgf29.html#HaeglerWAGM10</url>
</article>
我逐步读取这个文件，并用 lxml 来解析这些文章。如果我运行代码但不把数据存入我的数据库（注释掉 populate_database()），大约 3 秒钟能处理 1000 条。但如果我启用存储（取消注释 populate_database()），速度就变成每秒大约 10 条。这正常吗？我记得以前解析这个文件时，数据库并没有这么慢。但那时我用的是不同的方法……（我正在翻找我的文件以找出那个方法……）
这是让我头疼的函数。我注释掉了那三条 cursor.executes，代码又快了起来。所以看起来是 MySQL 有问题，或者是这些执行语句有问题（我这个菜鸟的猜测）。有什么建议吗？
def add_paper(paper, cursor):
questionmarks = str(('?',)*len(paper)).replace("'", "")
# The line above: produces (?, ?, ?, ... ,?) for oursql query
keys, values = paper.keys(), paper.values()
keys = str(tuple(keys)).replace("'", "")
# The line above: produces (mdate, title, ... date, some_key)
query_paper = '''INSERT INTO dblp2.papers {0} VALUES {1};'''.\
format(keys, questionmarks)
values = tuple(v.encode('utf8') for v in values)
cursor.execute(query_paper, values)
paper_id = cursor.lastrowid
return paper_id
def populate_database(paper, authors, cursor):
paper_id = add_paper(paper, cursor)
query_author ="""INSERT INTO dblp2.authors
(name) VALUES (?) ON DUPLICATE KEY UPDATE
id=LAST_INSERT_ID(id)"""
query_link_table = "INSERT INTO dblp2.author_paper
(author_id, paper_id) VALUES (?, ?)"
for author in authors:
cursor.execute(query_author, (author.encode('utf8'),))
author_id = cursor.lastrowid
cursor.execute(query_link_table, (author_id, paper_id))
我添加了来自 cProfile 的性能分析输出：
ncalls tottime percall cumtime percall filename:lineno(function)
1 0.001 0.001 15.666 15.666 <string>:1(<module>)
1 0.000 0.000 0.000 0.000 __init__.py:49(normalize_encoding)
1 0.000 0.000 0.000 0.000 __init__.py:71(search_function)
510 0.002 0.000 0.002 0.000 _elementpath.py:222(_build_path_iterator)
510 0.005 0.000 0.008 0.000 _elementpath.py:260(iterfind)
408 0.005 0.000 0.017 0.000 _elementpath.py:270(find)
102 0.003 0.000 0.011 0.000 _elementpath.py:285(findall)
10 0.000 0.000 0.000 0.000 _elementpath.py:70(xpath_tokenizer)
5 0.000 0.000 0.000 0.000 _elementpath.py:85(prepare_child)
987 0.009 0.000 0.013 0.000 _elementpath.py:87(select)
1 0.000 0.000 0.000 0.000 codecs.py:77(__new__)
1 0.000 0.000 0.000 0.000 utf_8.py:15(decode)
1 0.000 0.000 0.000 0.000 utf_8.py:33(getregentry)
102 0.008 0.000 5.601 0.055 xml2db.py:25(add_paper)
680 0.003 0.000 0.006 0.000 xml2db.py:31(<genexpr>)
102 0.005 0.000 15.468 0.152 xml2db.py:36(populate_database)
477 0.003 0.000 0.013 0.000 xml2db.py:45(clean_parse)
101 0.002 0.000 0.005 0.000 xml2db.py:52(clear_element)
103 0.019 0.000 0.024 0.000 xml2db.py:57(extract_paper_elements)
1 0.017 0.017 15.557 15.557 xml2db.py:63(fast_iter)
1 0.004 0.004 15.665 15.665 xml2db.py:89(main)
1 0.000 0.000 0.000 0.000 {__import__}
1 0.000 0.000 0.000 0.000 {_codecs.utf_8_decode}
1 0.000 0.000 0.000 0.000 {built-in method __new__ of type object at 0x8245fc0}
5 0.000 0.000 0.000 0.000 {built-in method findall}
1 0.000 0.000 0.000 0.000 {hasattr}
2 0.000 0.000 0.000 0.000 {isinstance}
515 0.001 0.000 0.001 0.000 {iter}
107 0.000 0.000 0.000 0.000 {len}
477 0.010 0.000 0.010 0.000 {lxml.etree.strip_tags}
5 0.000 0.000 0.000 0.000 {method 'append' of 'list' objects}
101 0.002 0.000 0.002 0.000 {method 'clear' of 'lxml.etree._Element' objects}
1 0.000 0.000 0.000 0.000 {method 'cursor' of 'oursql.Connection' objects}
1 0.000 0.000 0.000 0.000 {method 'disable' of '_lsprof.Profiler' objects}
778 0.007 0.000 0.007 0.000 {method 'encode' of 'str' objects}
5 0.000 0.000 0.000 0.000 {method 'encode' of 'unicode' objects}
516 15.544 0.030 15.544 0.030 {method 'execute' of 'oursql.Cursor' objects}
408 0.004 0.000 0.023 0.000 {method 'find' of 'lxml.etree._Element' objects}
102 0.001 0.000 0.012 0.000 {method 'findall' of 'lxml.etree._Element' objects}
103 0.001 0.000 0.001 0.000 {method 'format' of 'str' objects}
2 0.000 0.000 0.000 0.000 {method 'get' of 'dict' objects}
204 0.001 0.000 0.001 0.000 {method 'get' of 'lxml.etree._Element' objects}
100 0.000 0.000 0.000 0.000 {method 'getparent' of 'lxml.etree._Element' objects}
201 0.001 0.000 0.001 0.000 {method 'getprevious' of 'lxml.etree._Element' objects}
510 0.004 0.000 0.004 0.000 {method 'iterchildren' of 'lxml.etree._Element' objects}
1 0.000 0.000 0.000 0.000 {method 'join' of 'str' objects}
102 0.000 0.000 0.000 0.000 {method 'keys' of 'dict' objects}
204 0.001 0.000 0.001 0.000 {method 'replace' of 'str' objects}
1 0.000 0.000 0.000 0.000 {method 'split' of 'str' objects}
1 0.000 0.000 0.000 0.000 {method 'translate' of 'str' objects}
102 0.000 0.000 0.000 0.000 {method 'values' of 'dict' objects}
2 0.000 0.000 0.000 0.000 {time.time}
2 个回答
0
每个数据库都有一个叫做EXPLAIN的命令，用来帮助我们了解执行计划。
另外，Python也有一个性能分析工具，可以找出哪些代码运行得比较慢。
所以，首先你可以自己分析一下，如果还是搞不清楚，再回来问。
2
看起来你正在运行很多单独的 insert 语句。如果你在mysql数据库上开启日志功能，你会看到很多类似这样的语句：
....
INSERT INTO dblp2.authors (name) VALUES (a) ON DUPLICATE KEY UPDATE id=LAST_INSERT_ID(id)
INSERT INTO dblp2.authors (name) VALUES (b) ON DUPLICATE KEY UPDATE id=LAST_INSERT_ID(id)
INSERT INTO dblp2.authors (name) VALUES (c) ON DUPLICATE KEY UPDATE id=LAST_INSERT_ID(id)
INSERT INTO dblp2.authors (name) VALUES (d) ON DUPLICATE KEY UPDATE id=LAST_INSERT_ID(id)
INSERT INTO dblp2.authors (name) VALUES (e) ON DUPLICATE KEY UPDATE id=LAST_INSERT_ID(id)
INSERT INTO dblp2.authors (name) VALUES (f) ON DUPLICATE KEY UPDATE id=LAST_INSERT_ID(id)
....
你可以选择运行 参数化/多重插入 语句，或者把所有数据打包成一个csv文件，然后进行 批量插入。
批量插入的速度更快，而且它支持替换重复的行（查看文档）。如果你想要真正的更新，可以使用多重插入，或者试试这个 临时表的想法。
多重插入语句大概是这样的：
cursor.execute(query_author, [author.encode('utf8') for author in authors])
这样的话，你的日志中应该会出现类似这样的记录：
INSERT INTO dblp2.authors (name) VALUES (a,b,c,d,e,f) ON DUPLICATE KEY UPDATE id=LAST_INSERT_ID(id)
你可能在第二个表的id上会遇到一些问题。