求主键MAX(id)时已有class_id+student_id联合索引可否省略datetime过滤条件

为什么仅用到class_id作为索引键？ #

联合索引遵循最左前缀匹配原则，要使用索引中后续的列，必须保证前面的所有列都是等值匹配：

• 你的查询WHERE条件里只有class_id = 1是等值匹配，student_id没有出现在WHERE的等值条件里，仅出现在GROUP BY子句中，还额外加了created_at的范围过滤。
• 对于你用的(class_id, student_id, created_at)索引，同一个class_id下，数据是先按student_id排序，每个student_id下再按created_at排序，跨student_id的created_at不是全局有序的。数据库没办法直接通过索引快速定位所有符合created_at >= xxx的行，只能先把所有class_id=1的行从索引里捞出来，再逐条判断created_at的条件，所以最终只用了索引的第一个列class_id。
• 你改成(class_id, student_id, id)索引且不带datetime条件时，不需要额外的字段判断，同一个class_id下student_id有序，每个student_id对应的id也有序，要取每个student_id的最大id可以直接通过索引顺序扫描拿到结果，不需要回表，所以能用到全部三个索引列，走覆盖索引查询。

是否可以安全删除datetime过滤条件？ #

能不能删完全取决于你的业务逻辑，和性能无关：

• 如果你的业务需求就是要获取指定时间之后每个学生对应的最大id，那绝对不能删，删除后查询结果会包含所有时间的记录，和你原本的业务预期不符。
• 如果你的业务本身不需要时间范围限制，就是要获取该班级下每个学生所有历史记录的最大id，那可以安全删除，性能也会更优：
  1. 从执行计划可以看到，删除后查询成本从940降到了854，没有额外的created_at条件判断开销。
  2. 可以走覆盖索引扫描，不需要回表查询额外字段，海量数据下性能差距会进一步拉大。
• 如果你后续仍然需要保留datetime过滤条件又想提升性能，可以调整索引顺序为(class_id, created_at, student_id, id)，这样可以先用前两个列过滤掉不符合时间条件的行，再直接用后面两个索引列完成分组取max的操作，能用到更多的索引能力，性能会比现在好很多。

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