MySQL/MariaDB：在查询中“直接”使用索引？

0qx6xfy6 于 2022-12-10 发布在 Mysql

关注(0)|答案(1)|浏览(179)

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

来源：https://stackoverflow.com/questions/74742119/mysql-mariadb-using-indexes-in-queries-directly

1条答案

按热度按时间

qeeaahzv1#

如果您使用的是MySQL8.0或MariaDB 10.2(或更高版本)，LEAD()和LAG()提供了一种查看前后行的简单方法。
如果您使用的是较旧的版本，则执行“自联接”。也就是说，JOIN表自身。然后把两张“table”排成一排。这可能需要使用新的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本身不能是JOINed。

赞(0）回复(0）举报 2022-12-10

我来回答

MySQL/MariaDB：在查询中“直接”使用索引？

1条答案

相关问题

热门标签

最新问答