为什么SQLAlchemy的count()比原始查询慢得多？

Question

我正在使用SQLAlchemy与MySQL数据库，并且我想要计算表中的行数（大约30万）。SQLAlchemy count函数的运行时间比直接在MySQL中编写相同查询语句要长50倍。我做错了什么吗？

# this takes over 3 seconds to return
session.query(Segment).count()

但是：

SELECT COUNT(*) FROM segments;
+----------+
| COUNT(*) |
+----------+
|   281992 |
+----------+
1 row in set (0.07 sec)

表格大小越大，速度差异越大（在少于100k行的情况下几乎不可察觉）。

更新

使用session.query(Segment.id).count()代替session.query(Segment).count()似乎可以解决问题并提高速度。但我仍然困惑为什么最初的查询速度较慢。

- mtth

---

我不了解那个SQLAlchemy的东西，但听起来它似乎会遍历结果而不是向后端发送计数(*)。 - user330315

文档似乎表明count函数只是发出一个计数语句。 - mtth

创建'engine'时使用echo=True参数启用日志记录，以查看实际生成的'SQL'语句。 - van

感谢您的建议。实际上，使用session.query(Segment).count()会发出一个count(*) FROM (SELECT segments.column_1 as segments_column_1, ... FROM segments)而不是一个简单的count(*) FROM segments。我猜这种减速来自于扩展所有列。 - mtth

只是提一下，这些查询在PostgreSQL上具有相同的性能。 - Taha Jahangir

Answer 1

很遗憾，MySQL对子查询的支持非常糟糕，这对我们产生了非常消极的影响。 SQLAlchemy文档指出可以使用query(func.count(Segment.id))实现“优化”的查询：

Return a count of rows this Query would return.

This generates the SQL for this Query as follows:

SELECT count(1) AS count_1 FROM (
     SELECT <rest of query follows...> ) AS anon_1

For fine grained control over specific columns to count, to skip the usage of a subquery or otherwise control of the FROM clause, or to use other aggregate functions, use func expressions in conjunction with query(), i.e.:

from sqlalchemy import func

# count User records, without
# using a subquery.
session.query(func.count(User.id))

# return count of user "id" grouped
# by "name"
session.query(func.count(User.id)).\
        group_by(User.name)

from sqlalchemy import distinct

# count distinct "name" values
session.query(func.count(distinct(User.name)))

- zzzeek

---

最佳的解释通过Google不幸的是这篇格式极差的博客文章，但能够传达其思想：http://www.mysqlperformanceblog.com/2010/10/25/mysql-limitations-part-3-subqueries/ - zzzeek

这篇较长的文章涉及MySQL规划器在优化子查询和连接方面的更多细节：http://www.xaprb.com/blog/2006/04/30/how-to-optimize-subqueries-and-joins-in-mysql/ - zzzeek

如何从查询中获取实际的int/long计数值？如果我在session.query(func.count(Table.id))上调用count，它会抛出异常... - dpb

session.query(func.count(Table.id)).scalar() 将会给你第一行的第一列。 - zzzeek

是的，你可以使用self.relationship.with_entities(func.count(User.id)).scalar()这个语句。 - zzzeek

Answer 2

由于SQLAlchemy的count()正在计算子查询的结果，而该子查询仍在执行检索要计数的行的全部工作。这种行为与底层数据库无关；这不是MySQL的问题。

SQLAlchemy的docs解释了如何通过从sqlalchemy导入func来发出无需子查询的计数。

session.query(func.count(User.id)).scalar()

>>>SELECT count(users.id) AS count_1 \nFROM users')

- aeb0

Answer 3

我花了很长时间才找到这个作为解决我的问题的方法。我一开始遇到了以下错误:

sqlalchemy.exc.DatabaseError: (mysql.connector.errors.DatabaseError) 126 (HY000): Incorrect key file for table '/tmp/#sql_40ab_0.MYI'; try to repair it

当我更改了以下内容时，问题得到了解决:

query = session.query(rumorClass).filter(rumorClass.exchangeDataState == state)
return query.count()

到这个：

query = session.query(func.count(rumorClass.id)).filter(rumorClass.exchangeDataState == state)
return query.scalar()

- Jerry K.

---

这与问题有什么关联？如果出现错误，情况看起来会完全不同于OP所描述的。 - Nico Haase

非常好奇为什么这样更快，以及它是否仍然是2022年的最佳选择。 - Matt