Hibernate联合子类（每个具体类的表）映射发生器的“增量”非常慢？ [英] Hibernate union-subclass (table per concrete class) mapping generator of "increment" very slow?

问题描述

 < hibernate-mapping>我的Hibernate hbm文件看起来像这样： 
< class name =com.company.common.bo.position.Parenttable =Parents
 abstract =true> 
< id name =id> 
< generator class =increment/> 
< / id> 
< property name =datenot-null =true/> 
< property name =millisecondsnot-null =true/> 
< property name =shares> 
< column name =sharesprecision =19scale =6not-null =true/> 
< / property> 
   not-null =falsecascade =save-updatelazy =falsefetch =select/ > 
 
  table =SubclassAs> 
 <多对一名称=组合> 
< column name =portfolioIDnot-null =true/> 
< /多对一> 
<多对一名称=individualTrade> 
< column name =individualTradeIDnot-null =false/> 
< /多对一> 
  cascade =save-updatenot-null =false/> 
< / union-subclass> 
 
  name =com.company.common.bo.position.SubclassBtable =SubclassBs> 
<多对一名称=individualTrade> 
< column name =individualTradeIDnot-null =false/> 
< /多对一> 
   cascade =save-updatenot-null =false/> 
< / union-subclass> 
 
  table =SubclassCs> 
< / union-subclass> 
< / class> 
  

所以基本上我有一个摘要class Parent和3个子类（SubclassA，B，C）。在数据库中有3个表格（3个子类别）。 id生成器是增量，因为联合子类映射不允许我使用native。所以它看起来像增量一样，这个ID在3个表中是唯一的。当我查看hibernate sql时，它基本上从所有3个表中找到最大ID，并将其用作下一个ID。但是它使用的查询效率很低。这是我看到它做的：

  select（max）（ids_.id）from（从SubclassAs中选择id union从SubclassBs中选择id联盟从SubclassCs中选择id）ids_ 
  

这需要超过12秒的时间才能运行。每个表格都有超过一百万条记录。它将每一个ID联合在一起，然后选择最大值。

如果我这样做：

<从SubclassB中选择max（id）作为id union从SubclassBs中选择max（id）作为id union从SubclassC中选择max（id）作为id） ids_

速度要快得多，小于1毫秒，因为内部联合只会从每个表，然后我选择这3条记录中的最大值。

有没有办法让hibernate做到这一点，还是有更好的方式来使用在这3个表中使用ID的生成器？

谢谢 解决方案

如果 increment 不能满足你，你可以使用其他的生成器策略，并且由于MySQL不支持序列，下一个合适的选项是 hilo 策略。

My Hibernate hbm file looks something like this with a mysql DB:

<hibernate-mapping>
<class name="com.company.common.bo.position.Parent" table="Parents"
    abstract="true">
    <id name="id">
        <generator class="increment" />
    </id>
    <property name="date" not-null="true" />
    <property name="milliseconds" not-null="true" />
    <property name="shares">
        <column name="shares" precision="19" scale="6" not-null="true" />
    </property>
    <many-to-one name="ticker" column="tickerID" not-null="true" index="_tickerID_date_milliseconds_idx" />
    <many-to-one name="auditTrail" column="auditTrailID"
        not-null="false" cascade="save-update" lazy="false" fetch="select" />

    <union-subclass name="com.company.common.bo.position.SubclassA"
        table="SubclassAs">
        <many-to-one name="account" column="accountID" not-null="true" foreign-key="SubclassA_accountID_fk" />
        <many-to-one name="portfolio">
            <column name="portfolioID" not-null="true"/>
        </many-to-one>
        <many-to-one name="individualTrade">
            <column name="individualTradeID" not-null="false"/>
        </many-to-one>  
        <many-to-one name="positionTransfer" column="positionTransferID"
                cascade="save-update" not-null="false"/>
    </union-subclass>

    <union-subclass
            name="com.company.common.bo.position.SubclassB" table="SubclassBs">
        <many-to-one name="individualTrade">
            <column name="individualTradeID" not-null="false" />
        </many-to-one>  
        <many-to-one name="account" column="accountID" not-null="true" foreign-key="SubclassBs_accountID_fk"/>
        <many-to-one name="internalExecution" column="executionID"
                cascade="save-update" not-null="false" />
    </union-subclass>       

    <union-subclass name="com.company.common.bo.position.SubclassC"
        table="SubclassCs">
    </union-subclass>
</class>

So basically i have an abstract class Parent and 3 subclasses (SubclassA, B, C) that extend it. In the database there are 3 tables (for the 3 subclasses). The id generator is "increment" because the union subclass mapping doesn't allow me to use native. So it looks like with increment, the ID is unique among the 3 tables. When I look at the hibernate sql, it basically finds the max ID from all 3 tables, and uses that as the next ID. But the query it uses seems very inefficient. This is what I see it doing:

select max(ids_.id) from ( select id from SubclassAs union select id from SubclassBs union select id from SubclassCs ) ids_ 

Which takes over 12 seconds to run. Each of those tables has more than a million records each. It's unioning every single ID together and then selecting the max out of that.

If i do something like this:

select max(ids_.id) from ( select max(id) as id from SubclassAs union select max(id) as id from SubclassBs union select max(id) as id from SubclassCs ) ids_

It is much faster, less than one millisecond, because the inner union only gets the max from each table, and then i select just the max out of those 3 records.

Is there a way to tell hibernate to do this instead, or is there a better way of using a generator for the ID across these 3 tables?

Thanks

解决方案

If increment doesn't satisfy you, you can use some other generator strategy, and, since MySQL doesn't support sequences, the next suitable option is a hilo strategy.

这篇关于Hibernate联合子类（每个具体类的表）映射发生器的“增量”非常慢？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！