MySQL associated query optimization instance

The project finds that the query response is very slow, takes time to analyze and optimize, and is specially recorded.

(1) Background

The project adopts the MySQL database and Ibatis;

(2) query description

This query is used to query a certain number of users each time. Multi-Table Association is involved in the query. The specific query SQL statement is as follows:

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 ONS. ID=A. USER_ID
13. LEFT JOIN
14. (SELECT RESOURCE_ID, contact from company_resource_info
15. WHERE UPDTIME>= # UPDResourceTime: TIMESTAMP #) R
16. ONS. ID=R. CONTACT,
17. Company_info C
18. WHERES. COMPANY_ID= C. ID
19. Group by s. ID
20. LIMIT 15
21. </Select>

Note that subqueries are used, involving temporary tables.

(3) analysis process

The analysis result of using the profiler provided by MySQL is as follows:

It can be observed that 99% of the time spent copying data to a temporary table is related to its subquery.

To be surprised, if only 15 pieces of data are LIMIT, the temporary data should not spend so much time. I personally think the problem should be caused by LIMIT's invalid subquery, that is to say, the subquery in it involves all the data, resulting in a large time consumption on the temporary table, which is not difficult to understand.

(4) Optimization

After thinking about the above reasons, the solution is clear, as long as the subqueries involved in it only query the data corresponding to the LIMIT.

The implementation method is as follows:

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>
15. <Select Id="SelectUpdResourceSize" ParameterClass="Java. util. HashMap"
16. ResultClass="Int">
17. Select count (1) FROM company_resource_info
18. WHERECONTACT= # CONTACT #
19. And UPDTIME>= # UPDResourceTime: TIMESTAMP #
20. </Select>
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 ONS. ID=A. USER_ID
35. Left join company_info C ONS. COMPANY_ID= C. ID
36. LIMIT 15
37. </Select>

The main method is to first identify 15 pieces of LIMIT data that does not contain the subquery results and define a resultMap result set, call a query for each record to obtain the desired result.

1 2 Next Page