我注意到SQLAlchemy在获取(和ORMing(一些数据时速度很慢,而使用裸骨SQL获取数据的速度相当快。首先,我创建了一个包含一百万条记录的数据库:
mysql> use foo
mysql> describe Foo;
+-------+---------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+---------+------+-----+---------+-------+
| id | int(11) | NO | PRI | NULL | |
| A | int(11) | NO | | NULL | |
| B | int(11) | NO | | NULL | |
| C | int(11) | NO | | NULL | |
+-------+---------+------+-----+---------+-------+
mysql> SELECT COUNT(*) FROM Foo;
+----------+
| COUNT(*) |
+----------+
| 1000000 |
+----------+
mysql>
作为一个粗略的测试,查询所有 Foo 大约需要 2 秒:
herbert@dev0 ~ $ date; echo 'use foo; select * from Foo;' | mysql -uroot -pxxx > /dev/null; date
zo apr 20 18:48:49 CEST 2014
zo apr 20 18:48:51 CEST 2014
如果我使用 MySQLdb 在 python 中执行此操作,这大约需要 3 秒,包括 Foo 对象的构造:
herbert@dev0 ~ $ python BareORM.py
query execution time: 0:00:02.198986
total time: 0:00:03.403084
这是以下的输出:
#!/usr/bin/python
# -*- coding: utf-8 -*-
import MySQLdb
import sys
import time
import datetime
class Foo:
def __init__(self, a, b, c):
self.a=a; self.b=b; self.c=c;
try:
start = datetime.datetime.now()
con = MySQLdb.connect('localhost', 'root', 'xxx', 'foo')
cur = con.cursor();
cur.execute("""SELECT * FROM Foo LIMIT 1000000""")
print "query execution time: ", datetime.datetime.now()-start
foos = [];
for elem in cur:
foos.append(Foo(elem[1], elem[2], elem[3]))
con.commit()
except MySQLdb.Error, e:
print "Error %d: %s" % (e.args[0], e.args[1])
sys.exit(1)
finally:
if con: con.close()
print "total time: ", datetime.datetime.now()-start
但是,使用 SQLAlchemy 减少样板代码,它大约需要 25 秒才能完成相同的工作:
herbert@dev0 ~ $ python AlchemyORM.py
total time: 0:00:24.649279
使用此代码:
import sqlalchemy
import datetime
import MySQLdb
from sqlalchemy import Column, Integer, create_engine
from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy.orm import sessionmaker, relationship, backref
Base = declarative_base()
class Foo(Base):
__tablename__ = 'Foo'
id = Column(Integer, primary_key=True)
A = Column(Integer(unsigned=False), nullable=False)
B = Column(Integer(unsigned=False), nullable=False)
C = Column(Integer(unsigned=False), nullable=False)
engine = create_engine('mysql+mysqldb://root:xxx@localhost/foo')
Session = sessionmaker(bind=engine)
session = Session()
start = datetime.datetime.now()
foos = session.query(Foo).limit(1000000).all()
print "total time: ", datetime.datetime.now()-start
为什么SQLAlchemy的运行速度比裸SQL解决方案慢~10倍,假设SQLAlchemy应该做大致相同的事情?我可以以某种方式加快速度吗?
这是一个更复杂的查询的最小工作示例,该查询使用预先加载连接多个表。我考虑只对单个表进行简单的查询,然后使用字典创建 id>对象映射并整理一对一关系。但在这样做之前,我想确定SQLAlchemy无法更好地执行,因为从软件设计的角度来看,编写自己的ORM是一个坏主意。恕我直言,减速 2 倍是可以接受的(也许(。
如果您知道其他(更快的(python-SQL ORM,或者类似BigTable的解决方案(已经是ORM(,请随时提及它们作为评论。
编辑:也用Peewee尝试过这个,结果是~15秒。
from peewee import *
import datetime;
database = MySQLDatabase("foo", host="localhost", port=3306, user="root", passwd="xxx")
class Foo(Model):
id = IntegerField()
A = IntegerField()
B = IntegerField()
C = IntegerField()
class Meta:
db_table = 'Foo'
database = database
start = datetime.datetime.now()
foos = Foo.select()
cnt=0;
for i in foos: cnt=cnt+1
print "total time: ", datetime.datetime.now() - start
编辑:作为对Matthias的回应,我试图用Hibernate在Java中做同样的事情,结果大约是8到10秒,不是很快,但比25秒快得多。代码,从一些类开始,以一些配置结束:
package herbert.hibernateorm;
import java.util.List;
import org.hibernate.Session;
import org.hibernate.Transaction;
import org.hibernate.SessionFactory;
import org.hibernate.cfg.Configuration;
public class App {
public static void main(String[] args) throws Exception {
SessionFactory factory = new Configuration().configure().buildSessionFactory();
Session session = factory.openSession();
Transaction tx = session.beginTransaction();
long start = System.currentTimeMillis();
List foos = session.createQuery("FROM Foo").list();
System.out.println(foos.size());
System.out.printf("total time: %dn", System.currentTimeMillis() - start);
session.close();
}
}
package herbert.hibernateorm;
public class Foo {
private int id, a, b, c;
public Foo() {}
public Foo(int A, int B, int C) { this.a=A; this.b=B; this.c=C; }
public int getId() { return id; }
public void setId(int id) { this.id = id; }
public int getA() { return a; }
public void setA(int a) { this.a = a; }
public int getB() { return b; }
public void setB(int b) { this.b = b; }
public int getC() { return c; }
public void setC(int c) { this.c = c; }
}
配置(分别为休眠.cfg.xml和休眠.hbm.xml(
<?xml version="1.0" encoding="UTF-8"?>
<!DOCTYPE hibernate-configuration PUBLIC "-//Hibernate/Hibernate Configuration DTD 3.0//EN" "http://hibernate.sourceforge.net/hibernate-configuration-3.0.dtd">
<hibernate-configuration>
<session-factory>
<property name="hibernate.dialect">org.hibernate.dialect.MySQLDialect</property>
<property name="hibernate.connection.driver_class">com.mysql.jdbc.Driver</property>
<property name="hibernate.connection.url">jdbc:mysql://localhost:3306/foo?zeroDateTimeBehavior=convertToNull</property>
<property name="hibernate.connection.username">root</property>
<property name="hibernate.connection.password">xxx</property>
<mapping resource="hibernate.hbm.xml"/>
</session-factory>
</hibernate-configuration>
<?xml version="1.0" encoding="UTF-8"?>
<!DOCTYPE hibernate-mapping PUBLIC "-//Hibernate/Hibernate Mapping DTD 3.0//EN" "http://hibernate.sourceforge.net/hibernate-mapping-3.0.dtd">
<hibernate-mapping>
<class name="herbert.hibernateorm.Foo" table="Foo" catalog="foo">
<id name="id" type="int">
<column name="id" />
<generator class="assigned" />
</id>
<property name="a" type="int">
<column name="A" not-null="true" />
</property>
<property name="b" type="int">
<column name="B" not-null="true" />
</property>
<property name="c" type="int">
<column name="C" not-null="true" />
</property>
</class>
</hibernate-mapping>
最后是pom文件在maven中运行它:
<project xmlns="http://maven.apache.org/POM/4.0.0" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
xsi:schemaLocation="http://maven.apache.org/POM/4.0.0 http://maven.apache.org/xsd/maven-4.0.0.xsd">
<modelVersion>4.0.0</modelVersion>
<groupId>herbert</groupId>
<artifactId>hibernateORM</artifactId>
<version>1.0-SNAPSHOT</version>
<packaging>jar</packaging>
<name>hibernateORM</name>
<url>http://maven.apache.org</url>
<repositories>
<repository>
<id>unknown-jars-temp-repo</id>
<name>A temporary repository created by NetBeans for libraries and jars it could not identify. Please replace the dependencies in this repository with correct ones and delete this repository.</name>
<url>file:${project.basedir}/lib</url>
</repository>
</repositories>
<properties>
<project.build.sourceEncoding>UTF-8</project.build.sourceEncoding>
</properties>
<dependencies>
<dependency>
<groupId>junit</groupId>
<artifactId>junit</artifactId>
<version>3.8.1</version>
<scope>test</scope>
</dependency>
<dependency>
<groupId>mysql</groupId>
<artifactId>mysql-connector-java</artifactId>
<version>5.1.21</version>
</dependency>
<dependency>
<groupId>org.hibernate</groupId>
<artifactId>hibernate-core</artifactId>
<version>4.0.1.Final</version>
</dependency>
<dependency>
<groupId>org.hibernate</groupId>
<artifactId>hibernate-entitymanager</artifactId>
<version>4.0.1.Final</version>
</dependency>
<dependency>
<groupId>org.hibernate.common</groupId>
<artifactId>hibernate-commons-annotations</artifactId>
<version>4.0.1.Final</version>
</dependency>
<dependency>
<groupId>nz.ac.waikato.cms.weka</groupId>
<artifactId>weka-dev</artifactId>
<version>3.7.10</version>
</dependency>
<dependency>
<groupId>commons-configuration</groupId>
<artifactId>commons-configuration</artifactId>
<version>1.9</version>
</dependency>
<dependency>
<groupId>commons-net</groupId>
<artifactId>commons-net</artifactId>
<version>3.1</version>
<classifier>examples</classifier>
</dependency>
<dependency>
<groupId>com.google.code.gson</groupId>
<artifactId>gson</artifactId>
<version>2.2.2</version>
</dependency>
<dependency>
<groupId>maven</groupId>
<artifactId>maven-jetty-plugin</artifactId>
<version>1.1</version>
<type>plugin</type>
</dependency>
<dependency>
<groupId>commons-io</groupId>
<artifactId>commons-io</artifactId>
<version>2.4</version>
</dependency>
<dependency>
<groupId>com.kenai.nbpwr</groupId>
<artifactId>org-slf4j-jdk14</artifactId>
<version>1.6.1-201106101300</version>
<type>nbm</type>
</dependency>
</dependencies>
</project>
以下是MySQL脚本的SQLAlchemy版本,它在四秒内执行,而MySQLdb则为三秒:
from sqlalchemy import Integer, Column, create_engine, MetaData, Table
import datetime
metadata = MetaData()
foo = Table(
'foo', metadata,
Column('id', Integer, primary_key=True),
Column('a', Integer(), nullable=False),
Column('b', Integer(), nullable=False),
Column('c', Integer(), nullable=False),
)
class Foo(object):
def __init__(self, a, b, c):
self.a = a
self.b = b
self.c = c
engine = create_engine('mysql+mysqldb://scott:tiger@localhost/test', echo=True)
start = datetime.datetime.now()
with engine.connect() as conn:
foos = [
Foo(row['a'], row['b'], row['c'])
for row in
conn.execute(foo.select().limit(1000000)).fetchall()
]
print "total time: ", datetime.datetime.now() - start
运行:
total time: 0:00:04.706010
下面是一个使用 ORM 完全加载对象行的脚本;通过避免使用 yield per 一次创建包含所有 1M 对象的固定列表,使用 SQLAlchemy master 在 13 秒内运行(rel 0.9 为 18 秒(:
import time
from sqlalchemy import Integer, Column, create_engine, Table
from sqlalchemy.orm import Session
from sqlalchemy.ext.declarative import declarative_base
Base = declarative_base()
class Foo(Base):
__table__ = Table(
'foo', Base.metadata,
Column('id', Integer, primary_key=True),
Column('a', Integer(), nullable=False),
Column('b', Integer(), nullable=False),
Column('c', Integer(), nullable=False),
)
engine = create_engine('mysql+mysqldb://scott:tiger@localhost/test', echo=True)
sess = Session(engine)
now = time.time()
# avoid using all() so that we don't have the overhead of building
# a large list of full objects in memory
for obj in sess.query(Foo).yield_per(100).limit(1000000):
pass
print("Total time: %d" % (time.time() - now))
然后,我们可以拆分这两种方法之间的差异,并使用ORM仅加载单个列:
for obj in sess.query(Foo.id, Foo.a, Foo.b, Foo.c).yield_per(100).limit(1000000):
pass
以上再次在 4 秒内运行。
SQLAlchemy Core的比较是与原始MySQLdb游标的更恰当的比较。 如果您使用 ORM 但查询单个列,则在最新版本中大约需要 4 秒。
在ORM级别,速度问题是因为在Python中创建对象很慢,SQLAlchemy ORM在获取这些对象时对这些对象应用了大量的簿记,这对于它履行使用合同是必要的,包括工作单元,身份映射,预先加载,集合等。
若要显著加快查询速度,请获取单个列而不是完整对象。 请参阅以下技术http://docs.sqlalchemy.org/en/latest/faq/performance.html#result-fetching-slowness-orm 描述这一点。
为了与PeeWee进行比较,PW是一个更简单的系统,功能要少得多,包括它不对身份映射做任何事情。 即使使用PeeWee,就像ORM一样简单,它仍然需要15秒,这证明cPython与直接C中的原始MySQLdb获取相比确实非常慢。
为了与Java相比,Java VM比cPython快得多。 Hibernate非常复杂,但由于JIT,Java VM非常快,甚至所有这些复杂性最终都运行得更快。 如果你想将Python与Java进行比较,请使用Pypy。
SQLAlchemy 很复杂。它必须处理将底层数据库本身不支持的类型转换为 Python、具有继承的表、JOIN、缓存对象、保持一致性、转换行、部分结果等等。看看sqlalchemy/orm/loading.py:instance_processor
- 这太疯狂了。
解决方案是拼凑并编译Python代码以处理特定查询的结果,就像Jinja2对模板所做的那样。到目前为止,还没有人做过这项工作,可能是因为常见的情况是几行(这种优化将是悲观的(,需要处理批量数据的人会像你一样手动完成。
这不是对我问题的回答,但可以帮助公众解决大型数据集的速度问题。我发现选择一百万条记录通常可以在大约 3 秒内完成,但是 JOINS 可能会减慢该过程。在这种情况下,一个人有大约 150k Foo 与 1M 柱线有 1-1 的关系,那么使用 JOIN 选择那些可能会很慢,因为每个 Foo 返回大约 6.5 次。我发现分别选择两个表并使用 python 中的字典连接它们大约比 SQLAlchemy 快 3 倍(约 25 秒(,比使用连接(大约 17 秒(的"裸"python 代码快 2 倍。在我的用例中,代码花了 8 秒。选择没有关系的 1M 条记录(如上面的条形图示例(需要 3 秒钟。我使用了以下代码:
#!/usr/bin/python
# -*- coding: utf-8 -*-
import MySQLdb
import sys
import time
import datetime
import inspect
from operator import itemgetter, attrgetter
# fetch all objects of class Class, where the fields are determined as the
# arguments of the __init__ constructor (not flexible, but fairly simple ;))
def fetch(Class, cursor, tablename, ids=["id"], where=None):
arguments = inspect.getargspec(Class.__init__).args; del arguments[0];
fields = ", ".join(["`" + tablename + "`.`" + column + "`" for column in arguments])
sql = "SELECT " + fields + " FROM `" + tablename + "`"
if where != None: sql = sql + " WHERE " + where
sql=sql+";"
getId = itemgetter(*[arguments.index(x) for x in ids])
elements = dict()
cursor.execute(sql)
for record in cursor:
elements[getId(record)] = Class(*record)
return elements
# attach the objects in dict2 to dict1, given a 1-many relation between both
def merge(dict1, fieldname, dict2, ids):
idExtractor = attrgetter(*ids)
for d in dict1: setattr(dict1[d], fieldname, list())
for d in dict2:
dd = dict2[d]
getattr(dict1[idExtractor(dd)], fieldname).append(dd)
# attach dict2 objects to dict1 objects, given a 1-1 relation
def attach(dict1, fieldname, dict2, ids):
idExtractor = attrgetter(*ids)
for d in dict1: dd=dict1[d]; setattr(dd, fieldname, dict2[idExtractor(dd)])
它帮助我加快了查询速度,但是我很高兴听到专家关于这种方法可能改进的信息。