MySql/MariaDB: Menggunakan indeks "secara langsung" dalam pertanyaan?

Question

Saya ingin tahu sama ada mungkin dengan MySql/MariaDB menggunakan indeks secara langsung dalam pertanyaan. Katakan kita mempunyai jadual tidak diisih ringkas dengan pasangan cap masa/nilai:

CREATE TABLE simple (timestamp DATETIME, val INT);

Indeks dengan menambah cap masa:

ALTER TABLE simple ADD INDEX ind_ts (timestamp);

Kami boleh "mengakses dengan cepat" beberapa susunan cap masa yang disusun.

Mari kita tentukan pertanyaan untuk memberikan perbezaan dalam nilai untuk nilai berturut-turut:

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)

Jelas sekali, pertanyaan ini bersyarat dan mahal. Cara yang lebih mudah ialah menambahkan lajur myindex pada jadual:

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

dan isikan lajur baharu dengan susunan kronologi cap waktu (cth. melalui beberapa kod php)

Pertanyaan baharu akan menjadi lebih mudah dan lebih murah:

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

Lajur myindex baharu agak serupa dengan indeks jadual pangkalan data ind_ts. (Mengapa) Adakah tiada binaan MySql untuk menggunakan ind_ts dan bukannya myindex?

Answer 1

Jika anda menggunakan MySQL 8.0 atau MariaDB 10.2 (atau lebih tinggi), LEAD() 和 LAG() menyediakan cara mudah untuk melihat baris sebelum atau selepas.

Jika anda menggunakan versi lama, sila lakukan "self-join". Iaitu, jadikan jadual JOIN 与其自身连接。然后将两个“桌子”对齐，偏移一。这可能需要生成一个带有新的AUTO_INCRMENT sebagai jadual sementara untuk menyediakan cara mudah untuk mengimbanginya. Ini mungkin lebih baik sedikit daripada idea anda tentang "myindex".

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

Kemudian

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

(Ini tidak akan memproses baris pertama dan terakhir jadual.)

Nota: Anda tidak boleh menggunakan TEMPORARY TABLE，因为它不能JOINed pada dirinya sendiri.