MySQL关联查询优化实例


项目中发现有一个查询响应非常慢，花时间分析以及优化，特地记录。

（1）背景

项目采用MySQL数据库，操作使用Ibatis；

（2）查询说明

这个查询是每次查询一定数目的用户信息，查询中涉及到多表关联，具体查询SQL如下

  1. <select id="select"  parameterClass="UserCompany" resultClass="UserCompany">   
  2.                 SELECT S.NAME as name,  
  3.                     S.IMAGE as image,  
  4.                     S.ID as id,  
  5.                     C.NAME as companyName,  
  6.                     C.ID as companyId,  
  7.                     A.FILE_NAME AS resourceFileName,  
  8.                     A.FILE_PATH AS resourceFilePath,  
  9.                     A.FILE_ID AS resourceFileId,  
  10.                     COUNT(R.CONTACT) AS updResourceSize    
  11.                     from sys_user_info S   
  12.                     LEFT JOIN ATTACH_FILE_INFO A ON S.ID = A.USER_ID  
  13.             LEFT JOIN   
  14.                 (SELECT RESOURCE_ID,CONTACT FROM company_resource_info   
  15.                 WHERE UPDTIME >= #UPDResourceTime:TIMESTAMP# ) R   
  16.             ON  S.ID = R.CONTACT,  
  17.                     company_info C   
  18.                     WHERE S.COMPANY_ID = C.ID  
  19.                     GROUP BY S.ID  
  20.             LIMIT 15  
  21.     </select>  
注意其中用到了子查询，涉及到临时表

（3）分析过程

使用MySQL自带的profiler分析结果如下图


观察可得，99%的时间花在了拷贝数据到临时表上，也就是与其中的子查询有关系。

想来奇怪，如果只是LIMIT 15条数据，临时数据不应该花费这么多时间；个人觉得问题应该出在LIMIT对于里面的子查询无效，也就是里面的子查询会涉及到全部数据，从而导致临时表消耗很大的时间，这样就不难理解了。

（4）优化

想清楚了上面的原因，解决的思路也就比较清楚了，只要让里面涉及的子查询只查LIMIT对应的数据就可以了。

重新实行的方式如下：

  1. <resultMap class="cn.com.steel.wuyou.model.UserCompany" id="UserCompanyMap">  
  2.         <result property="name" column="name" />  
  3.         <result property="image" column="image" />  
  4.         <result property="id" column="id" />  
  5.         <result property="companyName" column="companyName" />  
  6.         <result property="companyId" column="companyId" />  
  7.         <result property="resourceFileName" column="resourceFileName" />  
  8.         <result property="resourceFilePath" column="resourceFilePath" />  
  9.         <result property="resourceFileId" column="resourceFileId" />  
  10.         <result property="UPDResourceTime" column="UPDResourceTime" />  
  11.         <result property="updResourceSize" column="{CONTACT=id,udpResourceTime=UPDResourceTime}"  
  12.             select="steel_userCompany.selectUpdResourceSize" />  
  13.     </resultMap>  
  14.   
  15.     <select id="selectUpdResourceSize" parameterClass="java.util.HashMap"  
  16.         resultClass="int">  
  17.         SELECT COUNT(1) FROM company_resource_info  
  18.         WHERE CONTACT = #CONTACT#  
  19.         and UPDTIME >= #UPDResourceTime:TIMESTAMP#  
  20.     </select>  
  21.   
  22.   
  23.     <select id="select" parameterClass="UserCompany" resultMap="UserCompanyMap">  
  24.         SELECT S.NAME as name,  
  25.         S.IMAGE as image,  
  26.         S.ID as id,  
  27.         C.NAME as companyName,  
  28.         C.ID as companyId,  
  29.         A.FILE_NAME AS resourceFileName,  
  30.         A.FILE_PATH AS resourceFilePath,  
  31.         A.FILE_ID AS resourceFileId,  
  32.         #UPDResourceTime:TIMESTAMP# as UPDResourceTime  
  33.         from sys_user_info S  
  34.         LEFT JOIN ATTACH_FILE_INFO A ON S.ID = A.USER_ID  
  35.         LEFT JOIN company_info C ON S.COMPANY_ID = C.ID  
  36.         LIMIT 15  
  37.     </select>  
主要的做法就是，每次先查出LIMIT 15条不含子查询结果的数据，定义一个resultMap映射结果集，针对每一条记录再去分别调用一次查询从而得到最后想要的结果。
  • 1
  • 2
  • 下一页

相关内容