如何優化 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輸出看起來更好：

好的，我一直在尋找一種方法。

創建這樣的索引後：positions_speed_vehicle_id_time_index (speed, vehicle_id, time)，EXPLAIN輸出看起來更好：

這是解釋（我在圖像中收到）：

我想要一個更優化的查詢，但這就是我現在得到的。

• 縮小表大小以提高性能。
• DOUBLE佔用 8 個字節；FLOAT4. FLOAT提供1.7米的解析度，對於車輛來說足夠了。
• 你使用created_at和updated_at嗎？
• 您是否填寫每個條目的地址？似乎是在浪費時間（和空間）。
• 考慮只儲存移動大於 5 的動作。（那是公里/小時嗎？）
• 考慮“目前”位置的第三張表；這會在每個輸入記錄上更新。它會非常小（1561 行）。
• 您的 105Mpositions可能正在快速增長。在不久的將來還會有其他性能問題，除了這個SELECT。
• 還有哪些查詢很快需要優化？
• 你positions.id有什麼用嗎？不管你有沒有，我可能有進一步的優化。

引用自：https://dba.stackexchange.com/questions/240738