MariaDB车辆位置表vehicleId+date联合索引查询缓慢问题排查

• 你当前的EXPLAIN结果虽然已经命中了(vehicleId, date)联合索引，但因为查询用了SELECT *，需要回表到聚簇索引读取完整行数据，额外产生大量IO开销：
  • 主键使用无序的char(36)类型UUID，InnoDB聚簇索引按主键排序存储，导致符合条件的行在磁盘上离散分布，回表产生大量随机IO
  • 表中包含longtext类型的attributes字段，大文本数据会存储在独立的溢出页，回表时还需要额外读取溢出页，进一步放大IO开销
• 每月定时删除旧数据会产生表碎片，存储不连续也会降低IO读取效率

1. 索引优化 #

如果业务不需要返回所有字段，不要使用SELECT *，明确指定需要查询的字段，再建立对应覆盖索引避免回表，示例：

-- 假设业务需要返回time、lat、lng、speed、attributes五个字段，建立覆盖索引
ALTER TABLE positions ADD INDEX idx_vehicle_date_info (`vehicleId`, `date`, `time`, `lat`, `lng`, `speed`);

如果必须返回全字段，优先调整主键结构。

2. 主键结构优化 #

将主键从无序UUID改为自增整数，原有UUID作为业务唯一键单独存储：

ALTER TABLE positions 
DROP PRIMARY KEY,
ADD COLUMN id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY FIRST,
ADD COLUMN biz_id CHAR(36) NOT NULL UNIQUE COMMENT '原业务UUID',
DROP COLUMN `id`;

调整后聚簇索引按自增ID顺序存储，同车辆同日期的行存储位置更集中，回表IO开销可降低80%以上。

3. 表碎片整理 #

每月删除旧数据后执行碎片整理，释放冗余空间，提升存储连续性：

OPTIMIZE TABLE positions;

4. 缓存优化 #

车辆历史位置数据属于不可变更数据，查询后将结果存入Redis，设置合理过期时间，后续相同请求直接走缓存，完全避免数据库查询，性能提升最明显。

内容的提问来源于stack exchange，提问作者Felipe Angelo Sgarbi