[MySQL] 造成資料庫 Lock 的 JOIN SQL

這篇是寫給我自己看的。如果有興趣一起研究的人再來討論囉! ^^

昨天(4/13)去華X銀看系統，這二天很明顯都是因為下面這個 SQL 指令造成重要的 Table 都被 Lock 住。

<sql:query var="result" startRow="${param.startRow}" maxRows="${param.maxRows}" >
SELECT	branch_box.*,
box.name,
rent.customer_id,
customer.name AS customer_name,
rent.start_date,
rent.due_date
FROM box,branch_box
LEFT OUTER JOIN rent ON branch_box_id= branch_box.id
LEFT JOIN customer ON rent.customer_id = customer.id
WHERE branch_box.branch_id='${param.branch_id}'
AND box.id = branch_box.box_id
AND box.id = ${param.box_id}
<c:if test="${param.search_branch_box_number!=''}">
AND  branch_box.number = '${param.search_branch_box_number}'
</c:if>
ORDER BY box_id, CAST(number AS UNSIGNED)
</sql:query>

因為這支程式執行時間特別久，加上在 MySQL 的 Process 裡面看的很清楚，執行二個表單以上的 JOIN 指令時，MySQL 會 Copy 到一個 temp table，可能這段有 OUTER JOIN 的關係，造成 copy tmp table 的時間非常的長，此時 rent 、 customer 這二個重要的 table 也都因此被 lock，造成很多使用者要等這個指令執行完才能使用。

昨晚在華X銀在程式把上面那段SQL拆開來改寫，經測試速度上已達到感覺不出來有在執行這支程式了。等今天睡起來之後去華X銀再測試記憶體佔用的情形。