Mysql
正確的複合索引索引順序
我有 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 不支持索引包含,但即便如此,它也將是表的事實上的重新排序副本,因此聚集索引是有意義的。
至於順序是否重要……嗯,是的,也不是。鑑於您的數據類型和表的大小,差異將是最小的。