在many-to-many的中间表中配置索引
<set name="cards" inverse="true" cascade="all,delete-orphan" table="ACCOUNT_CARD" fetch="join"><key column="ACCOUNT_ID"/><many-to-many class="Card" foreign-key="fk_acctcard_cardid"><column name="CARD_ID" index="i_acctcard_cardid"></column></many-to-many></set><set name="accounts" inverse="true" cascade="all,delete-orphan" table="ACCOUNT_CARD"><key column="CARD_ID" /><many-to-many class="Account" foreign-key="fk_acctcard_accountid" ><column name="ACCOUNT_ID" index="i_acctcard_acctid"></column></many-to-many></set>customer(1)-(n)account(n)-card(n)
三张表分别：30万，60万，90万记录
Select * From card a,Account b,account_card c ,customer dWhere a.card_number = '4518100348128286' And a.Id=c.card_id And b.Id=c.account_id And d.Id=a.customer_idAnd b.customer_Id = d.Id
这个查询如果在中间表account_card上不加索引将需要10s钟才能完成，加上索引后0.046s就搞定了。当然card_number肯定是得有索引的。
页:
[1]