首頁  >  問答  >  主體

MySql/MariaDB:「直接」在查詢中使用索引?

我想知道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粉350036783P粉350036783421 天前592

全部回覆(1)我來回復

  • P粉217629009

    P粉2176290092023-09-16 12:23:26

    如果您使用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到其自身。

    回覆
    0
  • 取消回覆