python - 分析 SQL 查询

Question

我在 MySQL 中使用 sqlalchemy(表达式语言，不是完整的 ORM)并遇到一些意外的缓慢。特别是，通过 sqlalchemy 执行选择查询所花费的时间是从 mysql 命令行执行相同查询所花费时间的十倍。

cprofile 的输出:

ncalls  tottime  percall  cumtime  percall filename:lineno(function)
100  206.703    2.067  206.703    2.067 {method 'query' of '_mysql.connection' objects}

MySQL 时间:0.26 秒

共识似乎是使用 sqlalchemy 有一些开销，但几乎没有那么多。关于什么可能导致这种行为有什么建议吗？

查询通常采用以下形式:

SELECT fieldnames.minage, fieldnames.maxage, fieldnames.race,    
fieldnames.sex, sum( pop.population ) AS pop, pop.zip5
FROM pop
INNER JOIN fieldnames ON fieldnames.fieldname = pop.fieldname_id
WHERE fieldnames.race IN ("White alone")
AND fieldnames.sex IN ("Female")
AND fieldnames.maxage >=101
AND fieldnames.minage <=107
GROUP BY fieldnames.minage, fieldnames.maxage

Answer 1

缓慢的一个可能原因 - sql alchemy 是否使用准备好的语句？如果是，那么您遇到性能差异的原因可能是因为 mysql 优化器在创建两个查询计划时具有不同的信息。

当您从命令行运行查询时，mysql 优化器具有完整的查询，其中填充了所有 where 子句值(如上所示3)，因此可以针对这些值进行显式优化。

当你从 sql alchemy 运行时，mysql 优化器可能只会看到这个(可能 fieldnames.race 和 fieldnames.sex 也被参数化):

SELECT fieldnames.minage, fieldnames.maxage, fieldnames.race,    
fieldnames.sex, sum( pop.population ) AS pop, pop.zip5
FROM pop
INNER JOIN fieldnames ON fieldnames.fieldname = pop.fieldname_id
WHERE fieldnames.race IN ("White alone")
AND fieldnames.sex IN ("Female")
AND fieldnames.maxage >= ?
AND fieldnames.minage <= ?
GROUP BY fieldnames.minage, fieldnames.maxage

因此优化器必须猜测您可能使用的值然后围绕它进行优化。不幸的是，它可能会做出错误的猜测，因此在最坏的情况下会创建一个查询计划，使查询运行速度比您预期的要慢得多。