MySQL關聯查詢優化執行個體

項目中發現有一個查詢響應非常慢，花時間分析以及優化，特地記錄。

（1）背景

項目采用MySQL資料庫，操作使用Ibatis；

（2）查詢說明

這個查詢是每次查詢一定數目的使用者資訊，查詢中涉及到多表關聯，具體查詢SQL如下

<select id="select"  parameterClass="UserCompany" resultClass="UserCompany"> 
                SELECT S.NAME as name,
                	S.IMAGE as image,
                	S.ID as id,
                	C.NAME as companyName,
                	C.ID as companyId,
                	A.FILE_NAME AS resourceFileName,
                	A.FILE_PATH AS resourceFilePath,
                	A.FILE_ID AS resourceFileId,
                	COUNT(R.CONTACT) AS updResourceSize  
                	from sys_user_info S 
                	LEFT JOIN ATTACH_FILE_INFO A ON S.ID = A.USER_ID
			LEFT JOIN 
				(SELECT RESOURCE_ID,CONTACT FROM company_resource_info 
				WHERE UPDTIME >= #UPDResourceTime:TIMESTAMP# ) R 
			ON  S.ID = R.CONTACT,
                	company_info C 
                	WHERE S.COMPANY_ID = C.ID
                	GROUP BY S.ID
			LIMIT 15
	</select>           

注意其中用到了子查詢，涉及到臨時表

（3）分析過程

使用MySQL自帶的profiler分析結果如下圖

觀察可得，99%的時間花在了拷貝資料到臨時表上，也就是與其中的子查詢有關系。

想來奇怪，如果隻是LIMIT 15條資料，臨時資料不應該花費這麼多時間；個人覺得問題應該出在LIMIT對于裡面的子查詢無效，也就是裡面的子查詢會涉及到全部資料，進而導緻臨時表消耗很大的時間，這樣就不難了解了。

（4）優化

想清楚了上面的原因，解決的思路也就比較清楚了，隻要讓裡面涉及的子查詢隻查LIMIT對應的資料就可以了。

重新實行的方式如下：

<resultMap class="cn.com.steel.wuyou.model.UserCompany" id="UserCompanyMap">
		<result property="name" column="name" />
		<result property="image" column="image" />
		<result property="id" column="id" />
		<result property="companyName" column="companyName" />
		<result property="companyId" column="companyId" />
		<result property="resourceFileName" column="resourceFileName" />
		<result property="resourceFilePath" column="resourceFilePath" />
		<result property="resourceFileId" column="resourceFileId" />
		<result property="UPDResourceTime" column="UPDResourceTime" />
		<result property="updResourceSize" column="{CONTACT=id,udpResourceTime=UPDResourceTime}"
			select="steel_userCompany.selectUpdResourceSize" />
	</resultMap>

	<select id="selectUpdResourceSize" parameterClass="java.util.HashMap"
		resultClass="int">
		SELECT COUNT(1) FROM company_resource_info
		WHERE CONTACT = #CONTACT#
		and UPDTIME >= #UPDResourceTime:TIMESTAMP#
	</select>


	<select id="select" parameterClass="UserCompany" resultMap="UserCompanyMap">
		SELECT S.NAME as name,
		S.IMAGE as image,
		S.ID as id,
		C.NAME as companyName,
		C.ID as companyId,
		A.FILE_NAME AS resourceFileName,
		A.FILE_PATH AS resourceFilePath,
		A.FILE_ID AS resourceFileId,
		#UPDResourceTime:TIMESTAMP# as UPDResourceTime
		from sys_user_info S
		LEFT JOIN ATTACH_FILE_INFO A ON S.ID = A.USER_ID
		LEFT JOIN company_info C ON S.COMPANY_ID = C.ID
		LIMIT 15
	</select>
           

主要的做法就是，每次先查出LIMIT 15條不含子查詢結果的資料，定義一個resultMap映射結果集，針對每一條記錄再去分别調用一次查詢進而得到最後想要的結果。