如何优化 MySQL 加入自身？

我需要优化 MySQL (5.7.12) 查询。我一直在阅读有关 MySQL 文档中优化的文档，但我很难理解它。我有两张桌子：

车辆

CREATE TABLE `vehicles` (
`id` int(10) unsigned NOT NULL AUTO_INCREMENT,
`name` varchar(100) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
`metadata` json DEFAULT NULL,
`created_at` timestamp NULL DEFAULT NULL,
`updated_at` timestamp NULL DEFAULT NULL,
`license_plate` varchar(50) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
`vehicle_type` varchar(205) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
`brand` varchar(100) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
`model` year(4) DEFAULT NULL,
`color` varchar(100) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
`fuel_type` varchar(20) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
`agency_id` int(10) unsigned DEFAULT NULL,
`deleted_at` timestamp NULL DEFAULT NULL,
`code` varchar(50) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
`vehicle_line` varchar(100) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
`initial_odometer` double DEFAULT NULL,
`reference` varchar(255) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
`fuel_chip` varchar(50) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
`engine_displacement` smallint(5) unsigned DEFAULT NULL,
`driver_data` varchar(255) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
PRIMARY KEY (`id`),
UNIQUE KEY `vehicles_code_unique` (`code`),
KEY `vehicles_agency_id_foreign` (`agency_id`),
CONSTRAINT `vehicles_agency_id_foreign` FOREIGN KEY (`agency_id`) REFERENCES `agencies` (`id`) ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB AUTO_INCREMENT=1561 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci

职位

CREATE TABLE `positions` (
`id` int(10) unsigned NOT NULL AUTO_INCREMENT,
`vehicle_id` int(10) unsigned NOT NULL,
`latitude` double NOT NULL,
`longitude` double NOT NULL,
`speed` decimal(8,2) DEFAULT NULL,
`created_at` timestamp NULL DEFAULT NULL,
`updated_at` timestamp NULL DEFAULT NULL,
`alarm` varchar(1000) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
`altitude` double DEFAULT NULL,
`direction` double DEFAULT NULL,
`metadata` json DEFAULT NULL,
`time` datetime DEFAULT NULL,
`deleted_at` timestamp NULL DEFAULT NULL,
`event_type` smallint(5) unsigned DEFAULT NULL,
`address` varchar(255) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
PRIMARY KEY (`id`),
KEY `positions_vehicle_id_time_deleted_at_index` (`vehicle_id`,`time`,`deleted_at`),
KEY `positions_time_index` (`time`),
KEY `speed` (`speed`),
CONSTRAINT `positions_vehicle_id_foreign` FOREIGN KEY (`vehicle_id`) REFERENCES `vehicles` (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=105581942 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci

如您所见，我有 1561 条记录vehicles和 105581942条记录positions。我需要在同一行中获取速度超过 5 的最新位置的车辆。这是由positions.time字段决定的，而不是由created_ator决定的id。我优化了下一个 SQL，它获取每辆车的最新位置：

SELECT a.*
FROM positions a
INNER JOIN
(
SELECT vehicle_id, MAX(time) mxdate
FROM positions
GROUP BY vehicle_id
) b
ON a.vehicle_id = b.vehicle_id
AND a.time = b.mxdate;

MySQL 对它的响应非常快，但是如果我添加speed到子查询中，“它永远不会结束”：

SELECT a.*
FROM positions a
INNER JOIN
(
SELECT vehicle_id, MAX(time) mxdate
FROM positions
where speed > 5
GROUP BY vehicle_id
) b
ON a.vehicle_id = b.vehicle_id
AND a.time = b.mxdate;

我在最后添加了这个：AND a.speed > 5但是我的老板告诉我最好在子查询中使用它。

最后，我需要上面显示的查询，但我认为优化最里面的查询就足够了。

select v.*,
       lastlocations.speed,
       lastlocations.latitude,
       lastlocations.longitude,
       lastlocations.time,
       lastlocations.event_type
from vehicles v,
     (
         SELECT a.*
         FROM positions a
                  INNER JOIN
              (
                  SELECT vehicle_id, MAX(time) mxdate
                  FROM positions
                  GROUP BY vehicle_id
              ) b ON a.vehicle_id = b.vehicle_id
                  AND a.time = b.mxdate
     ) lastlocations
where v.id = lastlocations.vehicle_id;

更新：创建这样的索引后：positions_speed_vehicle_id_time_index (speed, vehicle_id, time)，EXPLAIN输出看起来更好：