python - 为什么带有 psycopg2 use_native_unicode 的 SQLAlchemy 性能不佳？

Question

我很难弄清楚为什么一个简单的 SELECT 查询在使用原始 SQL 的 sqlalchemy 中花费了这么长时间(我得到 14600 行/秒，但是当在没有 sqlalchemy 的情况下通过 psycopg2 运行相同的查询时，我正在获得 38421 行/秒)。

经过一番探索，我意识到在 create_engine 调用中切换 sqlalchemy 的 use_native_unicode 参数实际上会产生巨大的差异。

此查询需要 0.5 秒来检索 7300 行:

from sqlalchemy import create_engine

engine = create_engine("postgresql+psycopg2://localhost...",
                       use_native_unicode=True)
r = engine.execute("SELECT * FROM logtable")
fetched_results = r.fetchall()

此查询需要 0.19 秒来检索相同的 7300 行:

engine = create_engine("postgresql+psycopg2://localhost...",
                       use_native_unicode=False)
r = engine.execute("SELECT * FROM logtable")
fetched_results = r.fetchall()

这两个查询之间的唯一区别是 use_native_unicode。但是 sqlalchemy 自己的文档声明最好保留 use_native_unicode=True (http://docs.sqlalchemy.org/en/latest/dialects/postgresql.html)。

有谁知道为什么 use_native_unicode 会产生如此大的性能差异？关闭 use_native_unicode 的后果是什么？

Answer 1

您需要根据要处理的非 ASCII 数据量来决定这个问题。假设未使用 SQLA 的 C 扩展，psycopg2 解码 unicode 的方法比 SQLAlchemy 的方法更快，但与不进行任何类型的 unicode 转换相比，仍然会增加结果集的延迟。在上面的代码中，没有使用 SQLAlchemy 的 unicode 工具；这些仅在列映射到 Unicode 或 String 类型时使用，这只有在您使用 text()、select() 或 ORM 级别的等效项时才会发生，其中 Unicode 类型映射到这些结果集列使用表元数据 text() 的“typemap”参数。

Psycopg2 的 native unicode 设施 OTOH 在光标级别生效，因此始终有效，并且显然总体上增加了一些延迟。

下面是一系列说明不同方法如何工作的插图。最后一个是与 SQLAlchemy 最相似的一个，尽管在使用 SQLAlchemy 的 C 扩展时我们可能只比 psycopg2 快:

import psycopg2
from psycopg2 import extensions

conn = psycopg2.connect(user='scott', password='tiger', host='localhost', database='test')

cursor = conn.cursor()
cursor.execute("""
create table data (
    id SERIAL primary key,
    data varchar(500)
)
""")

cursor.executemany("insert into data (data) values (%(data)s)", [
        {"data":"abcdefghij" * 50} for i in xrange(10000)
    ])
cursor.close()


def one(conn):
    cursor = conn.cursor()
    cursor.execute("SELECT data FROM data")
    for row in cursor:
        row[0]

def two(conn):
    cursor = conn.cursor()
    extensions.register_type(extensions.UNICODE, cursor)
    cursor.execute("SELECT data FROM data")
    for row in cursor:
        row[0]

def three(conn):
    cursor = conn.cursor()
    cursor.execute("SELECT data FROM data")
    for row in cursor:
        row[0].decode('utf-8')

def four(conn):
    cursor = conn.cursor()
    def conv_unicode(value):
        return value.decode('utf-8')
    cursor.execute("SELECT data FROM data")
    for row in cursor:
        conv_unicode(row[0])

import timeit

print "no unicode:", timeit.timeit("one(conn)", "from __main__ import conn, one", number=100)

print "native unicode:", timeit.timeit("two(conn)", "from __main__ import conn, two", number=100)

print "in Python unicode:", timeit.timeit("three(conn)", "from __main__ import conn, three", number=100)

print "more like SQLA's unicode:", timeit.timeit("four(conn)", "from __main__ import conn, four", number=100)

我得到的时间:

no unicode: 2.10434007645
native unicode: 4.52875208855
in Python unicode: 4.77912807465
more like SQLA's unicode: 4.88325881958

所以这里有趣的是，如果我们使用 C 扩展，SQLA 的方法实际上可能是比 psycopg2 的 native 方法更好的选择，如果实际上您没有大量使用 Unicode 类型和大多数您的字符串值只是纯 ASCII。