MySql/MariaDB：在查询中使用索引 "directly" ？

Question

我想知道是否有可能在MySql/MariaDB中直接使用查询中的索引。假设我们有一个带有时间戳/值对的简单未排序表:

CREATE TABLE simple (timestamp DATETIME, val INT);

通过为时间戳添加索引:

ALTER TABLE simple ADD INDEX ind_ts (timestamp);

我们有一个"快速访问"时间戳的排序顺序

让我们定义一个查询来传递连续值的差值:

SELECT 
c.timestamp AS currenttimestamp,
COALESCE(b.timestamp,'0000-00-00 00:00:00') AS timestampbefore,
c.val - COALESCE(b.val,0) AS difference
FROM simple c, 
simple b 
WHERE b.timestamp = (SELECT MAX(timestamp) FROM simple h WHERE h.timestamp < c.timestamp) 

很明显，这个查询是间接的和昂贵的。更方便的方法是在表中添加一个列myindex:

ALTER TABLE simple ADD COLUMN (myindex INT) AFTER timestamp;

并按时间戳顺序填充新列(例如，通过某些php代码)

新的查询将更简单和更便宜:

SELECT 
c.timestamp AS currenttimestamp,
COALESCE(b.timestamp,'0000-00-00 00:00:00') AS timestampbefore,
c.val - COALESCE(b.val,0) AS difference
FROM simple c
LEFT JOIN simple b ON c.myindex = b.myindex+1  

新列myindex在某种程度上类似于数据库的表索引ind_ts。(为什么)没有MySql结构使用ind_ts而不是myindex?

Answer 1

如果您使用的是MySQL 8.0或MariaDB 10.2(或更高版本)，LEAD()和LAG()提供了一种简单的方法来查看前后行

如果您使用的是旧版本，则执行"自连接"。也就是说，JOIN表本身。然后把两张"桌子"排好。偏移1。这可能需要用一个新的AUTO_INCREMENT生成一个临时表，以提供一种简单的方法来进行偏移。这可能比你关于"myindex"的想法稍微好一些。

CREATE TABLE new_table (
myindex INT UNSIGNED AUTO_INCREMENT,
PRIMARY KEY(myindex))
SELECT * FROM simple;

然后

SELECT a.ts - b.ts AS diff   -- (or whatever the math is)
FROM new_table AS a
JOIN new_table AS b  ON a.myindex = b.myindex - 1

(这并不关心表的第一行和最后一行。)

注意:不能使用TEMPORARY TABLE，因为它不能是JOINed。