Hibernate应用程序查询未使用数据库索引

Question

Hibernate应用程序查询未使用数据库索引

5

我正在尝试解决应用程序的性能问题。Hibernate生成的查询形式如下：

select * 
from ( 
    select this.a, this.b, this.state, this.id 
    from view_user this 
    where this.state=:1 order by this.a asc, this.b
) 
where rownum <= :2

问题：

上述查询执行 - 从SQLDeveloper快速 - 通过使用hibernate的小型Java应用程序快速 - 从使用hibernate的应用程序极慢（>100倍） - 绑定变量的值分别为2和30（rownum来源于分页） - hibernate查询“形式”如上所示。实际上，视图中有大约20个列。

当前分析状态

查询计划显示当查询来自SQlDeveloper或“小型Java应用程序”时使用索引。
查询计划显示如果查询来自hibernate应用程序，则执行完整表扫描。
DB跟踪显示仅存在两个差异：NLS设置（来自SQLDeveloper）和稍微不同的格式（空格）。其他所有内容似乎都是相同的...

=> 我很高兴有人能够提供有关此问题的任何提示。令我困扰的是DB跟踪没有显示任何差异... 是的，看起来它与hibernate有关。但是什么？如何检测？

Select * from ( 
    select this.USER_ID as USER_ID0_, this.CLIENT_ID as CLIENT_ID0_, 
    this.USER_NAME as USER_NAME0_, this.USER_FIRST_NAME as USER_FIR5_0_, this.USER_REMARKS as 
    USER_REM6_0_, this.USER_LOGIN_ID as USER_LOG7_0_, this.USER_TITLE as USER_TITLE0_, 
    this.user_language_code as user_lan9_0_, this.USER_SEX as USER_SEX0_, 
    this.USER_BIRTH_DATE as USER_BI11_0_, this.USER_TELEPHONE as USER_TE12_0_, 
    this.USER_TELEFAX as USER_TE13_0_, this.USER_MOBILE as USER_MO14_0_, 
    this.USER_EMAIL as USER_EMAIL0_, this.USER_ADDRESSLINE1 as USER_AD16_0_, 
    this.USER_ADDRESSLINE2 as USER_AD17_0_, this.USER_POSTALCODE as USER_PO18_0_, 
    this.USER_CITY as USER_CITY0_, this.USER_COUNTRY_CD as USER_CO20_0_, 
    this.USER_COUNTRY_NAME as USER_CO21_0_, this.USER_STATE_ID as USER_ST24_0_, 
    this.USER_STATE as USER_STATE0_, this.USER_TEMP_COLL_ID as USER_TE26_0_, 
    this.USER_TEMP_COLL_NAME as USER_TE27_0_, this.UNIT_ID as UNIT_ID0_, 
    this.CLIENT_NAME as CLIENT_38_0_, this.PROFILE_EXTID as PROFILE39_0_
    from VIEW_USER this
    where this.USER_STATE_ID=:1 order by this.USER_NAME asc, this.USER_FIRST_NAME asc
) 
where rownum <= :2

唯一索引是基于用户姓名、用户名字和用户ID的。

- Patrick Hammer

我怀疑您在此处输入查询时出现了拼写错误，因为内部选择中有两个 where。 where rownum 子句实际上是在外部选择中吗？ - rejj

1

你说“view_user有大约2百万条目”。请定义“mio”。此外，view_user.state可能的值是什么，每个可能值有多少行？最后，:1和:2提供了哪些值？谢谢。 - Bob Jarvis - Слава Україні

抱歉，mio是百万的意思。由于“this.state =：1”中：1的值为2，内部选择的完整返回集可能约占完整视图（view_user）的80％。 - Patrick Hammer

:2 是 30。我更新了初始问题描述。 - Patrick Hammer

是的，有。state_id是一个外键，指向一个表，其中state_id是主键。 - Patrick Hammer

5个回答

1

你提供的SQL语句看起来不像是Hibernate生成的查询语句。你确定它不是手写的查询语句吗？

如果你想使用Hibernate，你可以使用setMaxResults()方法来限制返回的行数。

如果你想使用手写的查询语句，我认为你需要这样做：

select *

从（从视图用户中选择this.a、this.b、this.state和this.id， where this.state = :1，按照this.a升序排列，然后按照this.b排列。）where rownum <= :2

- Todd Murray

抱歉打错字了。查询已经被简化，但从逻辑上讲仍然相同。由于在选择中使用了列清单，因此Hibernate看起来更加复杂。 - Patrick Hammer

np. VIEW_USER.USER_STATE_ID 上有索引吗？ - Todd Murray

是的，有。state_id 是一个外键，指向一个表，其中 state_id 是主键。 - Patrick Hammer

1

问题的最终解决方案是c3p0。我们无法追踪为什么选择了错误的查询计划。我们最终的假设是这与dbcp的连接初始化有关。但由于时间不足，我们尝试了c3p0。第一次使用的经验：

问题不再出现
更快，因为在使用池配置的maxConnections方面更加积极
由于上述原因，我们的整个应用程序现在更快了。这是基于使用应用程序时的总体印象以及我们的各种负载测试

因此，目前我们正在热切地测试各种c3p0场景，以便为生产系统做好准备。

感谢所有的输入！

- Patrick Hammer

0

经过大量的调试和尝试，我找到了解决问题的方法。然而，我目前还无法准确说明问题所在。

问题似乎出现在JDBC连接池层面。我的配置如下：

initialSize=0
minIdle=10

使用以上配置，我们遇到了与问题描述相符的症状。目前的解决方案似乎是将minIdle设置为initialSize的值。否则，连接似乎会以某种不同的方式初始化，导致性能下降。因此，我们将两者都设置为“10”。

我目前正在分析日志。在数据库服务器上，跟踪文件没有显示任何差异。到目前为止，JDBC跟踪也没有显示任何有趣的信息。

通过搜索，我发现很多示例配置都将minIdle设置为initialSize的值。此外，我还发现了两个引用（Spring文档、VMware文档）关于JDBC设置的内容：

minIdle：连接池中应始终保留的已建立连接的最小数量。如果验证查询失败，连接池可以缩小到此数字以下。默认值是从initialSize派生的。

请注意，minIdle的所有文档中并非都包含最后一句话“派生自initialSize”。大多数文档都将“0”作为默认值。

- Patrick Hammer

好的，这个解决方法不可行。所有在开始时初始化的连接（由于initialSize）都是正常的，并且将导致快速查询，即采用正确的查询计划。但是，一旦创建了一个新连接，因为启动时没有足够的连接，那些新创建的连接将会有性能问题。 - Patrick Hammer

0

关于SQL Server的nvarchars设置，上面/下面有一个答案 - 这里是Oracle的类似设置...

请检查是否有人设置了属性oracle.jdbc.defaultNChar=true

有时这样做可以解决Unicode问题，但这意味着所有列都被视为nvarchars。如果您在varchar列上有索引，则不会使用它，因为Oracle必须使用函数来转换字符编码。

- nevster

网页内容由stack overflow 提供, 点击上面的

可以查看英文原文，
原文链接

- Darryl Fryatt · Accepted Answer

我遇到过类似的情况，所以这可能与您有关。

JDBC驱动程序将您的参数转换为Unicode，因此当它到达数据库时，varchar变成了nvarchar。如果您很幸运，SQL 2008 SP1会捕获并转换回来。但是SQL 2000和SQL 2005不会这样做，查询优化器将执行全表扫描，忽略您的索引。

您可以在JDBC层通过向连接字符串添加连接参数sendStringParametersAsUnicode=FALSE来解决这个问题。