一、WHERE中有很多IN判斷怎么提速

WHERE 中的 IN 是要過濾出某字段值包含在給定枚舉值集合中的記錄，比如：查出某幾個城市的客戶、某些類型的訂單等等。

數據庫做 IN 過濾時，要用字段值和值集合的成員作比較計算。若采用順序查找，要比較 1 到 n 次（n 是值集合大小）。即使在值集合有序的情況下用二分法查找，也要比較數次。數據量較大時比較次數會非常多，IN 的速度就會很慢，而且值集合越大速度越慢。

如果在過濾時不再做比較計算，性能就能得到大幅提高！

首先，確定 IN 可能取值的列表。可能值通常不會太多，一般都保存在一個選項表中。如果沒有現成的選項表，要遍歷原數據得到所有可能值，保存成一個選項表。然后轉換原數據，把 IN 字段值替換為選項表中對應記錄的序號（位置），另存成一份新數據。

對替換后的新數據做 IN 判斷時，先生成一個與選項表等長的布爾值集合，其第 i 個值由選項表的第 i 個成員是否在 IN 的值集合中決定，在就是 true，不在就是 false。

然后遍歷新數據，用 IN 字段值（也就是選項表的序號）去取布爾值集合中的成員，是 true 就符合過濾條件，否則就不符合。

這種方法本質上是將“集合值比較”轉換為“序號引用”，省去了比較計算，性能會大幅提升。而且計算時間和值集合大小無關，不會隨著 IN 枚舉值的增多而增加。

但是，SQL 不支持通過序號（位置）直接取集合中的成員，無法實現這種優化方法。

集算器 SPL 支持序號引用，可以很方便的實現這個優化方法。

1、 數據預處理，轉換為序號。

=cs.run(dim1.pos@b(f1):f1)，遍歷原數據，使用 pos 函數查到原數據 f1 字段在選項表 dim1 中對應記錄的序號，用這個序號代替原來的 f1 字段值，另存一份新數據。dim1 預先按照 f1 對應值排好序了，所以這里采用二分法查找，預處理的速度更快。

2、 對預處理好的新數據做 IN 過濾計算。

假設傳入值集合為 arg_F1，生成布爾值集合的代碼是：

b1=dim1.(arg_F1.contain@b(~))，arg_F1 有序，所以這里也是二分法查找。

然后就可以用新方法過濾新數據了：

=file(“T.ctx”).open().cursor(…;b1(f1) && …)，用 f1 中的序號，直接去取布爾值集合中的成員，成員是 true 則滿足過濾條件，否則就不滿足。這時不需要再做比較計算，性能會大幅提高。

實測表明，在同等硬件環境下，用 SPL 實現的這種方案比 Spark 上的 SQL 快了上百倍。

延伸閱讀：

二、聯合索引 (a,b,c)

聯合索引 (a,b,c) 實際建立了 (a)、(a,b)、(a,b,c) 三個索引。

我們可以將組合索引想成書的一級目錄、二級目錄、三級目錄，如index(a,b,c)，相當于a是一級目錄，b是一級目錄下的二級目錄，c是二級目錄下的三級目錄。要使用某一目錄，必須先使用其上級目錄，一級目錄除外。

聯合索引的優勢

1） 減少開銷

建一個聯合索引 (a,b,c) ，實際相當于建了 (a)、(a,b)、(a,b,c) 三個索引。每多一個索引，都會增加寫操作的開銷和磁盤空間的開銷。對于大量數據的表，使用聯合索引會大大的減少開銷！

2）覆蓋索引

對聯合索引 (a,b,c)，如果有如下 sql 的，

SELECT a,b,c from table where a=’xx’ and b = ‘xx’;

那么 MySQL 可以直接通過遍歷索引取得數據，而無需回表，這減少了很多的隨機 io 操作。減少 io 操作，特別是隨機 io 其實是 DBA 主要的優化策略。所以，在真正的實際應用中，覆蓋索引是主要的提升性能的優化手段之一。

3）效率高

索引列多，通過聯合索引篩選出的數據越少。比如有 1000W 條數據的表，有如下SQL:

select col1,col2,col3 from table where col1=1 and col2=2 and col3=3;

復制

假設：假設每個條件可以篩選出 10% 的數據。

上一篇

軟件開發要遵循哪些事項?

下一篇

mysql怎么查看連接池是否已滿?