Mysql

正確的複合索引索引順序

  • May 24, 2021

我有 MySQL (MariaDB) 數據庫。我有一個表“感測器”,它從物聯網設備收集數據。

每個設備可以記錄 4-6 個參數,例如溫度、濕度、空氣質量等。每個設備每分鐘發送一次測量值。

*有 10-15 個這樣的設備。*每個設備都有其deviceid

六欄:

`autoid`(INT,AUTOICREMENT)
`deviceid`(varchar)
`pname`(varchar)         /* name of parameter like temperature,humidty */
`pcode`(INT)             /* code for each parameter like for temperature its 11,humidty its 12 etc */
`datavalue`(double)      /* value of parameter */
`rectime`(INT)           /* UNIX timestamp */

以下是表數據範例:

感測器表中有近4-5 百萬行。

我的查詢要求:我必須每天為每個設備和參數獲取一些任意時間值的數據。

這是使用的查詢:

SELECT * from sensors where deviceid =? AND  pcode = ? AND rectime =?

這保證只給我一個結果。問題是我需要在嵌套循環中執行這個查詢,最壞的情況是 500 次。為什麼我需要循環?我需要在兩個日期之間為每個設備、參數以及兩個日期之間的給定時間段創建一個報告。我必須循環獲取時隙的值。

我有一個綜合索引(deviceid,rectime,pcode)

如果我將此索引更改為 有什麼區別(rectime,deviceid,pcode)

一般來說,如果我的查詢使用 where 子句中的所有索引列,那麼列順序在復合索引中是否重要?

我認為您使用嵌套循環的方法不是最理想的。

為什麼你不能這樣做:

select * from sensors where deviceid =? AND  pcode = ? AND rectime between ? and ?

這將返回整個數據集,您可以在本地處理它。在一次正確的選擇中選擇 500 甚至更多行優於 500 單行選擇。

在這種情況下,我會將聚集索引更改為

(deviceid,pcode,rectime)

您仍然可以保留 autoId 主鍵,只需將其設為非集群即可。

此外,您的選擇的工作方式,它必須在之後進行鍵查找才能獲得一個額外的行。MySQL AFAIK 不支持索引包含,但即便如此,它也將是表的事實上的重新排序副本,因此聚集索引是有意義的。

至於順序是否重要……嗯,是的,也不是。鑑於您的數據類型和表的大小,差異將是最小的。

引用自:https://dba.stackexchange.com/questions/292087