MySql/MariaDB:'直接”在查询中使用索引?
P粉350036783
P粉350036783 2023-09-15 14:40:21
0
1
607

我想知道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?

P粉350036783
P粉350036783

全部回复(1)
P粉217629009

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

如果您使用的是旧版本,请执行“自加入”。也就是说,将表 JOIN 与其自身连接。然后将两个“桌子”对齐,偏移一。这可能需要生成一个带有新的AUTO_INCRMENT的临时表,以提供一种简单的方法来进行偏移。这可能比您关于“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到其自身。

热门教程
更多>
最新下载
更多>
网站特效
网站源码
网站素材
前端模板