abatis學習筆記(1)----避免N+1 Select

(一)什麼是N+1Select問題

通常的N+1查詢(1:1和1:M) <resultMap id="select-product-result" class="product"> <result property="id" column="PRD_ID"/> <result property="description" column="PRD_DESCRIPTION"/> <result property="category" column="PRD_CAT_ID" select="selectCategory"/> </resultMap> select id="selectCategory" parameterClass="int" resultMap="select-category-result"> select * from CATEGORY where CAT_ID = #value# </select> <statements> <select id="selectProduct" parameterClass="int" resultMap="select-product-result"> select * from PRODUCT where PRD_ID = #value# </select> 所謂N+1問題即在查詢N次父記錄的同時查詢N次子記錄

(二)解決N+1Select問題

第一種：建構多表連接配接sql語句 第二種：iBATIS.NET DataMapper 1.1中，添加了groupBy特性用來解決N+1問題                        <resultMap class="com.tj_zhongzhixin.entity.Store" id="storeMap" groupBy="sto_id">        <result property="sto_id" column="sto_id"/>        <result property="sto_name" column="sto_name"/>        <result property="sto_contactphone" column="sto_contactphone"/>        <result property="sto_time" column="sto_time" javaType="string" />        <result property="listQuote" resultMap="getListQuote" />     </resultMap>     <resultMap class="com.tj_zhongzhixin.entity.Quote" id="getListQuote">      <result property="quoId" column="quo_id" />      <result property="quoPrice" column="quo_price" />     </resultMap>       <statement id="selectListStore" resultClass="com.tj_zhongzhixin.entity.Store" resultMap="storeMap" parameterClass="com.tj_zhongzhixin.common.util.PageResult" >       select q.quo_id,q.quo_price,s.sto_id,s.sto_name,s.sto_contactphone,s.sto_time from store as s,quote as q where s.sto_id=q.quo_storeId limit #pageSize# offset #throwRows#       </statement> groupby屬性解決了查詢N次父記錄的同時查詢N次子記錄 他隻執行一次查詢（父查詢和子查詢同時執行） http://hi.baidu.com/zhifeichuan/