我想知道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?
如果您使用 MySQL 8.0 或 MariaDB 10.2(或更高版本),
LEAD()
和LAG()
提供了一种简单的方法来查看之前或之后的行。如果您使用的是旧版本,请执行“自加入”。也就是说,将表
JOIN
与其自身连接。然后将两个“桌子”对齐,偏移一。这可能需要生成一个带有新的AUTO_INCRMENT
的临时表,以提供一种简单的方法来进行偏移。这可能比您关于“myindex”的想法稍好一些。然后
(这不会处理表格的第一行和最后一行。)
注意:您不能使用
TEMPORARY TABLE
,因为它不能JOINed
到其自身。