为什么MYSQL使用视图查询2w条数据中其中1天的数据都要1S呢？

[pfworld]

select * FROM V_入库流水 where 1=1 AND 过磅时间 BETWEEN '13-08-28 00:00:00' AND '13-08-28 23:59:59' LIMIT 0,100


其中视图“V_入库流水”中连接5个外键【字典数据】，主键和创建时间都加了索引，查询还是很慢。

比如说我28日这天只有1条数据，但是查询速度要1S多，很慢！请问如何优化查询速度！

---------

如果直接读取数据

select * FROM S_In where 1=1 AND CreateDate BETWEEN '13-08-28 00:00:00' AND '13-08-28 23:59:59' LIMIT 0,100

不带外键，速度连0.0001秒都没有用！

-------------------

测试了一下，直接使用查询语句加上条件"where 1=1 AND 过磅时间 BETWEEN '13-08-28 00:00:00' AND '13-08-28 23:59:59' LIMIT 0,100 ",查询速度连0.1秒都没有！使用视图为什么速度降低了？


----------------------

视图代码：

select
`S_In`.`inID` AS `inID`,
`S_In`.`inStatus` AS `状态`,
`S_In`.`inDataSource` AS `数据来源`,
`S_In`.`inType` AS `类型`,
`S_In`.`inNo` AS `流水编号`,
`S_In`.`CreateDate` AS `过磅时间`,
`S_Supplier`.`supplierName` AS `供应商`,
`Z_FoodType`.`foodTypeName` AS `粮食类型`,
`Z_Warehouse`.`warehouseName` AS `库房`,
`S_In`.`inPlateNumber` AS `车牌号`,
`S_In`.`inFinalPrice` AS `最终单价`,
`S_In`.`inGW` AS `毛重`,
`S_In`.`inTW` AS `皮重`,
`S_In`.`inNW` AS `净重`,
`S_In`.`inBuckleRate` AS `总扣杂率`,
`S_In`.`inBuckleWeight` AS `总扣杂`,
`S_In`.`inNW_Real` AS `实重`,
`S_In`.`inMoney_SP` AS `商品金额`,
`e1`.`employeeName` AS `质检人员`,
`e2`.`employeeName` AS `司磅人员`,
`e3`.`employeeName` AS `库管人员`,
`S_In`.`inRemark` AS `备注`,
`S_In`.`fkInPaysID` AS `fkInPaysID`
FROM
((((((S_In
LEFT JOIN S_Supplier ON ((S_In.fkSupplierID = S_Supplier.supplierID)))
LEFT JOIN Z_FoodType ON ((S_In.fkFoodTypeID = Z_FoodType.foodTypeID)))
LEFT JOIN Z_Warehouse ON ((S_In.fkWarehouseID = Z_Warehouse.warehouseID)))
LEFT JOIN Z_Employee AS e1 ON ((S_In.fkEmployeeID_ZJ = e1.employeeID)))
LEFT JOIN Z_Employee AS e2 ON ((S_In.fkEmployeeID_SB = e2.employeeID)))
LEFT JOIN Z_Employee AS e3 ON ((S_In.fkEmployeeID_KG = e3.employeeID)))
order by `S_In`.`CreateDate` desc

如何优化呢？

[pfworld]

我的主键都是GUID！

[ACMAIN_CHM]

贴出 explain select `S_In`.`inID` AS `inID`, `S_In`.`inStatus` AS `状态`, `S_In`.`inDataSource` AS `数据来源`, `S_In`.`inType` AS `类型`, `S_In`.`inNo` AS `流水编号`,... 及 show index from 所有的表以供分析。

[rucypli]

不要用视图 试试把条件写进视图的sql select `S_In`.`inID` AS `inID`, `S_In`.`inStatus` AS `状态`, `S_In`.`inDataSource` AS `数据来源`, `S_In`.`inType` AS `类型`, `S_In`.`inNo` AS `流水编号`, `S_In`.`CreateDate` AS `过磅时间`, `S_Supplier`.`supplierName` AS `供应商`, `Z_FoodType`.`foodTypeName` AS `粮食类型`, `Z_Warehouse`.`warehouseName` AS `库房`, `S_In`.`inPlateNumber` AS `车牌号`, `S_In`.`inFinalPrice` AS `最终单价`, `S_In`.`inGW` AS `毛重`, `S_In`.`inTW` AS `皮重`, `S_In`.`inNW` AS `净重`, `S_In`.`inBuckleRate` AS `总扣杂率`, `S_In`.`inBuckleWeight` AS `总扣杂`, `S_In`.`inNW_Real` AS `实重`, `S_In`.`inMoney_SP` AS `商品金额`, `e1`.`employeeName` AS `质检人员`, `e2`.`employeeName` AS `司磅人员`, `e3`.`employeeName` AS `库管人员`, `S_In`.`inRemark` AS `备注`, `S_In`.`fkInPaysID` AS `fkInPaysID` FROM ((((((S_In LEFT JOIN S_Supplier ON ((S_In.fkSupplierID = S_Supplier.supplierID))) LEFT JOIN Z_FoodType ON ((S_In.fkFoodTypeID = Z_FoodType.foodTypeID))) LEFT JOIN Z_Warehouse ON ((S_In.fkWarehouseID = Z_Warehouse.warehouseID))) LEFT JOIN Z_Employee AS e1 ON ((S_In.fkEmployeeID_ZJ = e1.employeeID))) LEFT JOIN Z_Employee AS e2 ON ((S_In.fkEmployeeID_SB = e2.employeeID))) LEFT JOIN Z_Employee AS e3 ON ((S_In.fkEmployeeID_KG = e3.employeeID))) where `S_In`.`CreateDate` BETWEEN '13-08-28 00:00:00' AND '13-08-28 23:59:59' order by `S_In`.`CreateDate` desc LIMIT 0,100

[栗华山]

直接查询时没有使用关联表，是吧？ 而你的视图关联了很多很多表，是吧？

[wwwwb]

不用视图，直接连接基表