聊聊between and的坑 和 神奇的解法

postgresql , 物聯網 , 智能dns , range , iprange , intrange , 排他限制 , gist索引

曾經一位社群的兄弟跟我抱怨mysql裡面查ip位址庫并發幾千每秒的查詢資料庫就抗不住了。

于是問他要來了他們的ip位址庫資料和查詢用的sql以及mysql裡面的表結構。

我也想把資料轉到postgresql裡面做一下相對應的壓力測試，看看postgresql的表現。

在其他的業務中，這樣的需求也是屢見不鮮，比如年齡範圍，收入範圍，頻繁活動的範圍，地理位置區塊，幾何區塊，線段等。都是用範圍來描述的，随着物聯網的發展，這類查詢需求會越來越旺盛。

如果沒有好的索引機制，查詢需要消耗大量的cpu，很容易出現性能瓶頸。

本文要給大家介紹的是postgresql 9.2引入的範圍類型，以及針對範圍類型的索引，大幅提升範圍查詢的性能。

mysql裡面的表結構如下 :

mysql裡面的查詢sql如下 :

資料量大概40w.

由于mysql裡面沒有ip位址類型, 是以需要把ip位址轉換為數值類型來存儲并用來做ip位址範圍的比對.

ip位址的轉換算法是32位的二進制ip位址轉10進制數字。

在postgresql9.2裡面新增了一種資料類型，range類型, 例如可以用來存儲ip位址範圍, int值的範圍.

range類型具體的應用可以參見

<a href="https://github.com/digoal/blog/blob/master/201205/20120517_01.md">《postgresql 9.2 new type, range》</a>

1. 和mysql裡面一樣, 使用兩個字段分别存儲起始的ip數字

2. 使用iprange, 直接存儲ip位址範圍

3. 使用int8range, 存儲轉換後的數字範圍

以下索引其實隻需要建一個就夠了, postgresql btree索引支援&gt;=,&gt;,&lt;=,&lt;,=等幾種操作符, 同時ip位址段也不存在交疊的情況.

如何避免交疊呢, 在并發的情況下插入和更新ip_address_pool表, 是沒有辦法避免交疊情況的發生的, 例如

1. 最low的方法，先查詢需要插入的ip位址段是否已經存在表裡面

2. 不存在則插入. 但是這裡存在一個問題, 并發的情況下, 多個程序都可能認為插入的資料不存在, 都插入了, 但是并發插入的資料中可能有交疊的.

3. 在mysql中也許隻能使用全表堵塞式鎖來避免這個問題

4. 在postgresql中則不需要全表鎖, 因為可以使用range類型, 建立range類型的 exclude 限制, 例如：

這個限制還可以用于地理位置資料，比如在資料庫中存儲的版圖有相交時，違反限制報錯。

從第一個表把資料轉換成range類型并存儲到這個表，并使用gist索引

測試腳本:

測試結果:

為什麼隻有300多呢？原因是建立的不是複合索引, 注意因為這裡使用的是範圍檢索, 不是= , 是以檢索速度和取值範圍關系很大, 分别取三個值, 從小到大. 來看看查詢耗時.

建立複合索引,

建完後還是分三個值來測試一下響應時間 :

那麼它的tps能達到多少呢?

有10倍提高, 但是還遠遠不夠.

使用prepared即綁定變量還能有提升(這是2010年左右購買的hp dl360 8核機器上的測試表現), 如下

使用range類型還是測試一下那三個值的耗時, 分布就比較均勻了.

1. postgresql支援函數索引，是以我們不需要改表結構就可以使用函數索引來達到加速的目的。

例如 :

1. 在postgresql中，使用range類型後，我們對它建立了gist的索引，這個索引可以快速的根據使用者提供的ip位址定位到包含它的行。效率直接提示了20多倍，qps從幾千達到了接近10萬。

2. postgresql的range類型除了可以很好的利用它的gist索引作為檢索之外, 還可以使用它來做排他限制, 也就是防止資料交疊.

如果沒有這種限制的話，需要鎖全表來搞定.

3. 使用postgresql存儲ip資料的話, 還可以使用掩碼, 這樣的話就不需要存儲兩個字段了, 直接存在一個字段就可以.

當然也可以加一個存儲比特位的字段, 使用bit函數來處理包含關系.

另一種用法是把這個比特運算放到記憶體中執行, 記憶體中存儲ip比特位以及對應到資料庫的記錄的id資訊, 擷取id後去資料庫查詢, 也就是把資料庫的範圍查詢變成主鍵查詢. 也可以提高效率.

4. 關于gist索引的原理，可以參考

<a href="https://github.com/digoal/blog/blob/master/201612/20161231_01.md">《從難纏的模糊查詢聊開 - postgresql獨門絕招之一 gin , gist , sp-gist , rum 索引原理與技術背景》</a>