MySQL/MariaDB能否直接在查询中使用索引？

核心结论 #

在MySQL/MariaDB中无法直接使用索引（比如你创建的ind_ts）替代手动维护的myindex列，原因和索引的本质、设计目标密切相关，同时有更高效的替代方案可以实现你的需求。

为什么索引不能替代myindex？ #

• 索引是逻辑排序的辅助结构，而非连续序号载体：ind_ts作为基于timestamp的二级索引，本质是B+树结构，它只负责按timestamp的值排序存储，并关联到对应的行数据，但不会为每个条目分配连续的整数序号。即使timestamp是唯一的，索引里的条目也只是逻辑有序，不存在“c.myindex = b.myindex+1”这种连续的数值关联关系——比如如果有timestamp的间隙（比如删除了某条数据），索引只会保留现有值的排序，不会自动填补序号。
• 索引顺序≠物理行顺序：InnoDB的聚簇索引是按主键排序的物理存储，二级索引（比如ind_ts）的顺序是逻辑排序，和表的物理行顺序不一定一致。而你手动维护的myindex是绑定到物理行的连续序号，两者的逻辑完全不同，数据库无法直接将索引映射为这种连续序号。

更高效的替代方案：窗口函数LAG() #

你不需要手动维护myindex列，MySQL 8.0及MariaDB 10.2+版本支持的**窗口函数LAG()**可以直接高效获取前一行的timestamp和val值，性能远优于你之前的子查询写法，也无需额外维护列：

SELECT
    timestamp AS currenttimestamp,
    COALESCE(LAG(timestamp) OVER (ORDER BY timestamp), '0000-00-00 00:00:00') AS timestampbefore,
    val - COALESCE(LAG(val) OVER (ORDER BY timestamp), 0) AS difference
FROM simple
ORDER BY timestamp;

这个查询会直接利用ind_ts索引来排序并获取前一行数据，执行效率最高，逻辑也最简洁。

为什么MySQL不提供这类语法？ #

索引的核心设计目标是加速数据查找、排序和范围查询，而非承担业务层面的连续序号维护职责。如果强行让索引支持这种“连续序号关联”的逻辑，会带来两个问题：

1. 索引维护成本剧增：当插入、删除、更新数据时，需要重新计算并调整所有相关条目的序号，导致索引更新性能严重下降；
2. 违背数据库设计原则：业务层面的序号逻辑（比如你的myindex）属于业务需求，应该由业务代码或专门的列来维护，数据库索引只负责底层的性能优化，不应该耦合业务逻辑。

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