在數據庫中用得最多的當屬btree索引,除了BTREE,一般的數據庫可能還支持hash, bitmap索引.

但是這些索引到了物聯網,會顯得太重,對性能的損耗太年夜.

為什么呢?

物聯網有大量的數據發生和入庫,入庫基本都是流式的.在使用這些數據時,基本是FIFO,或者范圍查詢的批量數據使用風格.

btree索引太重,因為索引必要存儲每條記錄的索引字段的值和尋址,使得索引非常龐大.

另一方面,物聯網的大量范圍查詢和批量處理用法決定了它不必要這么重的索引.

例子：

如下所示,btree索引的空間占比是非常年夜的.

除了年夜以外,btree索引同時也會影響數據的更新,刪除,或插入的性能.

例子：

有btree索引, 每秒入庫28.45萬行

無索引, 每秒入庫66.88萬行

從上面的介紹和測試數據,可以明顯的看出btree索引存在的問題：

體積年夜,影響性能.

接下來該讓PostgreSQL黑科技登場了：

范圍索引,術語brin, block range index.

范圍索引的原理,存儲持續相鄰的BLOCK的統計信息(min(val), max(val), has null? all null? left block id, right block id ).

例如一個表占用10000個BLOCK,創建brin index 時,指定統計每128個BLOCK的統計信息,那么這個索引只必要存儲79份統計信息.

空間占用非常的小.

辦理了空間的問題,還需要辦理性能的問題,我們測試一下,在創建了brin索引后,插入的性能有多少?

范圍索引, 每秒入庫62.84萬行

最后還必要對比一下 btree, brin 索引的大小,還有查詢的性能.

索引年夜小比拼：

表 4163MB

btree索引 2491 MB

brin索引 4608 kB

查詢性能比拼 :

范圍查詢

全表掃描 11 秒

范圍索引 64 毫秒

btree索引 24 毫秒

(因今日頭條字數限制,代碼無法全部展現,請移步德歌博客查看原文：https://yq.aliyun.com/articles/27860)

精確查詢

全表掃描 8 秒

范圍索引 39 毫秒

btree索引 0.03 毫秒

(因今日頭條字數限制,代碼無法全部展現,請移步德歌博客查看原文：https://yq.aliyun.com/articles/27860)

對比圖 :

小結：

.1. 范圍索引重點的使用場景是物聯網類型的,流式入庫,范圍查詢的場景. 不僅僅對插入的影響微乎其微,并且索引大小非常的小,范圍查詢的性能和BTREE差別微乎其微.

.2. 結合JSON和GIS功能,相信PostgreSQL會在物聯網年夜放異彩.

ps: oracle 也有與之類似的索引,名為storage index. 但是只有Exadata產物里有,貴得離譜,屌絲繞道.哈哈.

https://docs.oracle.com/cd/E50790_01/doc/doc.121/e50471/concepts.htm#SAGUG20984

DBA應該具備抓住各種數據庫的特性,而且將這種特性應用到適合的場景中去的能力.數據庫與DBA的角色用千里馬和伯樂來形容好像也不為過.

小伙伴們一起來玩PG吧,社區正在推Oracle DBA 7天速成PG的教程,敬請等待.