MySQL排序性能问题

Question

我有一个带有子查询的查询，但子查询表有大约500.000条记录，并且运行此查询需要很长时间。我怎样才能加快这个查询？任何建议：

SELECT ID, VehicleID, Plate, VehicleType, 
    COALESCE(
     (SELECT EngineState 
     FROM Locations 
     WHERE Locations.VehicleID = Clients.VehicleID ORDER BY ID DESC LIMIT 1), 
     0 
    ) EngineState 
FROM Clients 
WHERE ID IN (SELECT ClientID FROM UserClients WHERE [email protected]); 

还有3列，其查询最后一个记录从位置表：

COALESCE(
    (SELECT EngineState FROM Locations 
    WHERE Locations.VehicleID = Clients.VehicleID ORDER BY ID DESC LIMIT 1), 
    0 
) EngineState 

当我看到在地址表中排序的结果是性能的因素存在。地点表如果每分钟填充1000辆车的位置数据。 X，Y，速度...

Answer 1

索引需要所使用的连接中加用户名在UserClient的选择做出

的查询放缓的执行时间有很多的所有列。
以下查询会提高性能。

SELECT c.ID, c.VehicleID, c.Plate, c.VehicleType, 
IFNULL(l.enginestate, 0) AS enginestate 
FROM Clients c 
JOIN (
    SELECT MAX(ID) AS ID, vehicleID 
    FROM Locations 
    GROUP BY vehicleID 
) ll 
    ON ll.VehicleID = c.VehicleID 
JOIN Locations l 
    ON ll.ID = l.ID 
JOIN UserClients uc 
    ON uc.clientID = c.ID 
WHERE [email protected] 

另外，当只有一个字段的值时，我更喜欢IFNULL到COALESCE。

Answer 2

创建以下指标：

Locations (vehicleId, id) 
UserClients (UserID, ClientID) 

和重写查询一点：

SELECT ID, VehicleID, Plate, VehicleType, l.* 
FROM Clients 
LEFT JOIN 
     Locations l 
ON  l.id = 
     (
     SELECT id 
     FROM Locations li 
     WHERE li.VehicleID = Clients.VehicleID 
     ORDER BY 
       li.VehicleID DESC, li.id DESC 
     LIMIT 1 
     ) 
WHERE Clients.ID IN 
     (
     SELECT ClientID 
     FROM UserClients 
     WHERE UserID = @UserID 
     ) 

由于您的VehicleID是VARCHAR(64)，你应该确保Clients.VehicleID和Locations.VehicleID使用相同的字符集和相同的排序规则。