-- GPS查询性能优化SQL
|
-- 用于解决GPS分段里程计算任务查询超时问题
|
|
-- 1. 检查tb_vehicle_gps表的索引
|
SHOW INDEX FROM tb_vehicle_gps;
|
|
-- 2. 添加组合索引：vehicle_id + collect_time（如果不存在）
|
-- 这个索引可以大幅提升按车辆ID和时间范围查询的性能
|
ALTER TABLE tb_vehicle_gps
|
ADD INDEX idx_vehicle_collect_time (vehicle_id, collect_time);
|
|
-- 3. 添加单列索引：collect_time（如果不存在）
|
-- 这个索引用于优化查询活跃车辆ID的查询
|
ALTER TABLE tb_vehicle_gps
|
ADD INDEX idx_collect_time (collect_time);
|
|
-- 4. 检查tb_vehicle_gps_calculated表的索引
|
SHOW INDEX FROM tb_vehicle_gps_calculated;
|
|
-- 5. 优化tb_vehicle_gps_calculated表索引
|
-- 添加gps_id索引（如果不存在），用于LEFT JOIN查询优化
|
ALTER TABLE tb_vehicle_gps_calculated
|
ADD INDEX idx_gps_id (gps_id);
|
|
-- 6. 添加vehicle_id索引，用于按车辆查询已计算记录
|
ALTER TABLE tb_vehicle_gps_calculated
|
ADD INDEX idx_vehicle_id (vehicle_id);
|
|
-- 7. 查看表的统计信息
|
ANALYZE TABLE tb_vehicle_gps;
|
ANALYZE TABLE tb_vehicle_gps_calculated;
|
|
-- 8. 查看索引使用情况
|
-- 执行以下查询来验证索引是否被正确使用
|
EXPLAIN SELECT DISTINCT vehicle_id
|
FROM tb_vehicle_gps
|
WHERE collect_time >= DATE_SUB(NOW(), INTERVAL 7 DAY)
|
ORDER BY vehicle_id;
|
|
EXPLAIN SELECT g.gps_id, g.vehicle_id, g.device_id, g.longitude, g.latitude,
|
g.altitude, g.speed, g.direction, g.collect_time
|
FROM tb_vehicle_gps g
|
LEFT JOIN tb_vehicle_gps_calculated c ON g.gps_id = c.gps_id
|
WHERE g.vehicle_id = 1
|
AND g.collect_time >= DATE_SUB(NOW(), INTERVAL 1 DAY)
|
AND g.collect_time <= NOW()
|
AND c.gps_id IS NULL
|
ORDER BY g.collect_time;
|
|
-- 9. 性能优化建议
|
/*
|
执行结果说明：
|
- type应该是ref或range，不应该是ALL（全表扫描）
|
- key应该显示使用了相应的索引
|
- rows应该尽可能少
|
|
如果发现索引未被使用，可能需要：
|
1. 更新表统计信息：ANALYZE TABLE
|
2. 检查MySQL版本是否支持索引优化
|
3. 考虑增加MySQL的innodb_buffer_pool_size配置
|
*/
|
|
-- 10. 清理历史GPS数据（可选，定期执行）
|
-- 建议保留最近30-90天的数据，避免表过大影响查询性能
|
/*
|
DELETE FROM tb_vehicle_gps
|
WHERE collect_time < DATE_SUB(NOW(), INTERVAL 90 DAY);
|
|
-- 优化表空间
|
OPTIMIZE TABLE tb_vehicle_gps;
|
*/
|
