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

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

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

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

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

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

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

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

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

集算器 SPL 支持序號引用，可以很方便的實現(xiàn)這個優(yōu)化方法。

1、 數(shù)據(jù)預(yù)處理，轉(zhuǎn)換為序號。

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

2、 對預(yù)處理好的新數(shù)據(jù)做 IN 過濾計算。

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

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

然后就可以用新方法過濾新數(shù)據(jù)了：

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

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

延伸閱讀：

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

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

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

聯(lián)合索引的優(yōu)勢

1） 減少開銷

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

2）覆蓋索引

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

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

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

3）效率高

索引列多，通過聯(lián)合索引篩選出的數(shù)據(jù)越少。比如有 1000W 條數(shù)據(jù)的表，有如下SQL:

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

復(fù)制

假設(shè)：假設(shè)每個條件可以篩選出 10% 的數(shù)據(jù)。