如何优化带单个WHERE条件的MySQL MAX主键值查询性能？

问题根因 #

• 你使用的单独modem_id二级索引基数仅300，平均每个modem_id对应约10万行数据。InnoDB二级索引默认会附带主键id值，但MySQL优化器未触发MAX(id)的快速查询路径，会全量扫描该modem_id对应的所有10万条索引条目计算最大值，是耗时的核心原因。
• phpMyAdmin显示的"Sending Data"耗时包含了存储引擎层读取索引条目的时间，并非实际网络传输数据的耗时，和当前场景特征匹配。

优化方案 #

方案1（优先级最高，效果最好） #

新增(modem_id, id)联合覆盖索引，执行DDL语句：

ALTER TABLE `messages` ADD INDEX `idx_modem_id_id` (`modem_id`, `id`);

该索引显式按modem_id排序，同一modem_id下的id按升序排列，查询MAX(id)时仅需要定位到modem_id=1234对应的索引段末尾，直接读取最大id值，仅需1次IO即可完成，耗时可降至毫秒级，且无需回表查询数据行。

方案2（临时优化，无需修改表结构） #

将原查询改写为等价的ORDER BY + LIMIT逻辑，引导优化器走快速取值路径：

SELECT `id` FROM `messages` WHERE `modem_id`=1234 ORDER BY `id` DESC LIMIT 1;

该SQL和原查询返回结果完全一致，不需要扫描全部匹配条目，在现有索引下即可获得数倍性能提升，适合暂时无法调整表结构的场景。

方案3（适合高频查询场景） #

如果该查询是业务高频调用的接口，可以新增一张汇总表存储每个modem_id的最新最大id，表结构参考：

CREATE TABLE `modem_max_id` (
  `modem_id` INT(10) PRIMARY KEY,
  `max_id` INT(10) NOT NULL DEFAULT 0
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

每次向messages表插入数据时同步更新对应modem_id的max_id值，查询时直接读取汇总表即可，耗时可降至微秒级。

内容的提问来源于stack exchange，提问作者David G