使用sqlalchemy会话执行sql会大大降低执行时间

Question

我有一个相当长的查询（以前是7个连接，现在是7个子选择，因为在原始的sql中，7个子选择要快得多——我甚至不知道如果让它运行，7个连接什么时候会完成，但比使用子选择的.05-.1秒还要长）

当我在数据库上运行它时，如我所说，需要0.05-.1秒来执行。只需使用session.execute()就可以将速度减慢到一分钟以上！

我能做点什么吗？

如果你需要更多的信息，请告诉我——我有点怀疑这是一个普通的sqlalchemy——比如sqlalchemy是在建立一个查询计划，而不是让mysql来做？或者。。。？

EDIT:ran explain对这两个元素都进行了解释，它们看起来完全相同，只是sqlalchemy在extra列中添加了一个“using temporary；using filesort”。这就是减慢速度的原因吗？我该如何阻止它这么做？

编辑二：绝对是炼金术。我尝试使用MySQL游标来执行，而不是SA会话，得到了相同的.05秒运行时间。

编辑3：

创建引擎的代码：

engine_ro = create_engine(
    config.ro_database_url, #string with username, password, db
    pool_size=config.database_pool_size, #int
    max_overflow=config.database_max_overflow, #int
    pool_timeout=config.database_timeout, # int
    echo=config.database_echo, #False
    echo_pool=config.database_echo, #same as echo #False
    listeners=[GoneAway()] if config.database_use_listeners else None)

其中GoneAway()是执行SELECT 1来检查连接的方法。

要创建会话对象，请执行以下操作：

SessionRO = scoped_session(sessionmaker(bind=engine_ro, autocommit=False))

其中scoped_session和sessionmaker是sqlalchemy函数。

然后，执行查询的代码：

session = SessionRO()
results = session.execute(sql, params)

编辑4：如果有人想知道，如果我注释掉listeners位，它仍然很慢。如果我只使用sessionmaker而不使用作用域会话。

Answer 1

sqlalchemy不设置查询计划或任何其他花哨的设置。它只是生成SQL并通过DB-API-2.0连接发送它。因此，如果使用sqlalchemy生成的语句显式调用execute，它将以完全相同的方式运行。*

查看查询sqlalchemy生成的最简单方法是将echo=True作为create_engine调用的额外参数传递。

在您的例子中，由sqlalchemy生成的查询实际上与手动查询不同，因为它使用字符串而不是int测试整数参数

---

*这不是100%保证的；您必须确保DB-API-2.0connect函数中的任何连接参数都是相同的，并且您和sqlalchemy都没有执行任何PRAGMA语句。但是，您可以用与测试查询本身几乎相同的方式测试它们。

Answer 2

你在用哪个DBAPI？也许试着把它换成别的东西。我现在正在使用PostgreSQL，我体验到pypostgresql和psycopg2在性能上的巨大差异（后者要快得多）。

有关MySQL可用dbapi的列表，请参阅SQLAchemy文档：第4.1.5章。

Answer 3

这里是一个真正的测试套件，可以将MySQL游标与SQLAlchemy引擎和会话进行比较。请在底部替换连接信息和SQL，然后运行它。告诉我们时间安排是什么。

import time

def time_thing(fn, description):
    print "Running %s" % description
    now = time.time()
    try:
        ret = fn()
        return ret
    finally:
        spent = time.time() - now
        print "Finished %s, took %d seconds" % (description, spent)

def with_mysqldb(sql):
    import MySQLdb

    conn = MySQLdb.connect(db=DBNAME, user=USERNAME, passwd=PASSWORD, host=HOST)

    def go():
        cursor = conn.cursor()
        cursor.execute(sql)

        # if result fetching is the issue:
        # cursor.fetchall()

        cursor.close()

    time_thing(go, "Executing SQL with MySQLdb cursor")

def _sqla_engine_w_test_connection():
    from sqlalchemy import create_engine
    eng = create_engine(SQLALCHEMY_URL)

    def test():
        conn = eng.connect()
        result = conn.execute("select 1")
        assert result.fetchall()[0] == (1, )

    time_thing(test, "Making a test connection...")

    return eng

def with_sqlalchemy(sql):
    eng = _sqla_engine_w_test_connection()

    def go():
        result = eng.execute(sql)

        # if result fetching is the issue:
        # result.fetchall()

        result.close()
    time_thing(go, "Executing SQL with SQLA engine")

def with_sqlalchemy_session(sql):
    from sqlalchemy.orm import Session

    eng = _sqla_engine_w_test_connection()

    def go():
        sess = Session(eng)

        result = sess.execute(sql)

        # if result fetching is the issue:
        # result.fetchall()

        result.close()

    time_thing(go, "Executing SQL SQLA session")

SQLALCHEMY_URL = "mysql://scott:tiger@localhost/test"
DBNAME = "test"
HOST = "localhost"
USERNAME = "scott"
PASSWORD = "tiger"
SQL = "SELECT 1"

with_mysqldb(SQL)
with_sqlalchemy(SQL)
with_sqlalchemy_session(SQL)