一、場(chǎng)景描述

查詢(xún)機(jī)構(gòu)下的人員，要同時(shí)查詢(xún)出該機(jī)構(gòu)及其所有下級(jí)機(jī)構(gòu)的人員；比如查詢(xún)北京市，不僅要查詢(xún)出歸屬于北京市的人員，還要查詢(xún)歸屬于出朝陽(yáng)區(qū)、西城區(qū)、海淀區(qū)等等的人員。

對(duì)于這個(gè)需求，有以下幾種解決思路：

1、之前的系統(tǒng)，他們的機(jī)構(gòu)編碼本身有關(guān)聯(lián)，比如北京編碼是001的話，昌平區(qū)的編碼就是0012；這樣的話，他們查詢(xún)可以用like。這種方法的問(wèn)題是like查詢(xún)太慢，尤其在數(shù)據(jù)量大的時(shí)候，即使對(duì)編碼加唯一索引，也只有查最根部機(jī)構(gòu)時(shí)可以走索引（因?yàn)橹挥写藭r(shí)才符合最左前綴）。

2、我們的系統(tǒng)沒(méi)有機(jī)構(gòu)編碼，唯一標(biāo)識(shí)是雪花算法生成的19位id，沒(méi)有關(guān)聯(lián)；機(jī)構(gòu)關(guān)系是通過(guò)一張關(guān)聯(lián)表組合的起來(lái)的（適用于多業(yè)務(wù)系統(tǒng)，多機(jī)構(gòu)樹(shù)的情況）。這種情況，可以通過(guò)條件從關(guān)系表中查出所有下級(jí)機(jī)構(gòu)id，然后查詢(xún)時(shí)用in查詢(xún)；為了方便查詢(xún)，我們還建了一張機(jī)構(gòu)關(guān)系冗余表。
但是這種方法也有一個(gè)問(wèn)題，就是Oracle數(shù)據(jù)庫(kù)in查詢(xún)中元素，必須在1000以?xún)?nèi)。

二、解決方案

1、方案一：核心思路是，將集合拆分，使用or 連接。

select * from A where id in (1, 2, …, 1000) or id in (1001, …, 1999)

用mybatis的話就是這樣

select * from test_1
<where>
 <if test="list != null and list.size > 0">
    (id IN
    <!-- 處理in的集合超過(guò)1000條時(shí)Oracle不支持的情況 -->
    <trim suffixOverrides=" OR id IN()">
        <foreach collection="list " item="Id" index="index" open="(" close=")">
            <if test="index != 0">
                <choose>
                    <when test="index % 1000 == 999">) OR id IN (</when>
                    <otherwise>,</otherwise>
                </choose>
            </if>
            #{Id}
        </foreach>
    </trim>
    )
 </if>

但是這種方法不好用，實(shí)測(cè)中3萬(wàn)條左右的機(jī)構(gòu)，用這種查詢(xún)查了好久都沒(méi)出來(lái)，感覺(jué)數(shù)據(jù)庫(kù)都要奔潰了。

2、方案二：用子查詢(xún)（臨時(shí)表）+關(guān)聯(lián)查詢(xún)

一般來(lái)說(shuō)，超過(guò)1000多條的數(shù)據(jù)，肯定不是用戶(hù)填寫(xiě)的，而是從其他地方查詢(xún)出來(lái)的；我們可以將這些數(shù)據(jù)放到一個(gè)臨時(shí)表中（用子查詢(xún)實(shí)現(xiàn)），然后用內(nèi)連接關(guān)聯(lián)查詢(xún)。

該查詢(xún)中，機(jī)構(gòu)關(guān)系冗余表SEP_SYSTEM_ORG_RELATION中有20多萬(wàn)條數(shù)據(jù)，但子查詢(xún)走了我們創(chuàng)建的組合索引；整個(gè)查詢(xún)只用了0.367秒

總結(jié)

到此這篇關(guān)于sql in查詢(xún)?cè)爻^(guò)1000條的解決方案的文章就介紹到這了,更多相關(guān)sql in查詢(xún)?cè)爻^(guò)1000條內(nèi)容請(qǐng)搜索腳本之家以前的文章或繼續(xù)瀏覽下面的相關(guān)文章希望大家以后多多支持腳本之家！

最新評(píng)論