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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

延伸閱讀：

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

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

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

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

1） 減少開銷

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

2）覆蓋索引

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

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

那么 MySQL 可以直接通過遍歷索引取得數(shù)據(jù)，而無需回表，這減少了很多的隨機(jī) io 操作。減少 io 操作，特別是隨機(jī) io 其實(shí)是 DBA 主要的優(yōu)化策略。所以，在真正的實(shí)際應(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è)每個(gè)條件可以篩選出 10% 的數(shù)據(jù)。