可以说每个系统在开发的时候都用到了sql语句,一些简单的还好说,怎么写都不会很慢,但是有一些涉及到好多表连起来的,或者表里有很多条数据的,如果sql语句查询的很慢,那将严重影响系统的使用。
这不,最近在项目中遇到了一个
-- sql优化1
SELECT
v_user.user_name AS op,
i.user_name AS applicant,
T_WarehousingLog.itemNo,
T_Item.itemName,
T_WarehousingLog.inDate,
T_WarehousingLog.inTime,
T_WarehousingLog.serial,
T_WarehousingLog.number,
T_WarehousingLog.positionID,
T_WarehousingLog.remark,
T_Category.categoryName,
T_Storage.storageName
FROM
T_WarehousingLog
LEFT JOIN v_user ON T_WarehousingLog.op = v_user.user_code AND v_user.is_delete = 0
LEFT JOIN v_user AS i ON T_WarehousingLog.applicant = i.user_code AND v_user.is_delete = 0
INNER JOIN T_Item ON T_WarehousingLog.itemNo = T_Item.itemNo
INNER JOIN T_Category ON T_Item.categoryID = T_Category.categoryID
INNER JOIN T_Storage ON T_WarehousingLog.storageID = T_Storage.storageID
GROUP BY
t_warehousinglog.serial
ORDER BY
T_WarehousingLog.inDate DESC
这条语句查询要30s,这不是一个正常的系统应该有的。
一开始我们了解到的优化有三种:①优化该sql语句②创建索引③在系统中改变接口的调用方式
最后,我们主要锁定前两种办法。
可是还是能力有限,通过上网查只知道了怎么看这条语句好不好,知道了确实应该加索引,但并没有解决实际问题,没办法只好请教大神了。
第一步:把视图改成了真正存在的表:
-- sql优化2
SELECT
u1.user_name AS op,
u2.user_name AS applicant,
T_WarehousingLog.itemNo,
T_Item.itemName,
T_WarehousingLog.inDate,
T_WarehousingLog.inTime,
T_WarehousingLog.serial,
T_WarehousingLog.number,
T_WarehousingLog.positionID,
T_WarehousingLog.remark,
T_Category.categoryName,
T_Storage.storageName
FROM
T_WarehousingLog
LEFT JOIN tc_allusers u1 ON T_WarehousingLog.op = u1.user_code AND u1.is_delete = 0
LEFT JOIN tc_allusers u2 ON T_WarehousingLog.applicant = u2.user_code AND u2.is_delete = 0
INNER JOIN T_Item ON T_WarehousingLog.itemNo = T_Item.itemNo
INNER JOIN T_Category ON T_Item.categoryID = T_Category.categoryID
INNER JOIN T_Storage ON T_WarehousingLog.storageID = T_Storage.storageID
GROUP BY
t_warehousinglog.serial
ORDER BY
T_WarehousingLog.inDate DESC
运行速度从30s变成了10s,有了很大的进步,但大神说了,还不够。
第二步:给各个表添加了索引
运行速度从10s变成了0.07s,果真是厉害到不行,从30s变成了0.07s!
详细的优化过程呢,请等待下一篇博客吧……
突然觉得自己需要学习的东西还是很多很多的呀,之前学的就只是一个皮毛。