sql语句优化

　　可以说每个系统在开发的时候都用到了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！

详细的优化过程呢，请等待下一篇博客吧……

突然觉得自己需要学习的东西还是很多很多的呀，之前学的就只是一个皮毛。