iT邦幫忙

0

SQL中的where in 有大量資料的話,要如何改善效能

sql
匿名 2012-10-12 16:29:4723386 瀏覽

想請教一下大大
在SQL中

select name from A where id in (1,5,7,1234.......)

由於in中的數量是動態的,可能多達上百個,且不連續
不知道有沒有其他更好的寫法可以改善效能
謝謝

看更多先前的討論...收起先前的討論...
1.不要用 IN
2.找個懂資料庫的人,重新設計資料庫結構
summertw iT邦好手 1 級 ‧ 2012-10-15 09:21:34 檢舉
看了四回答,一個討論,發現,只有討論這個回覆較有建設性..
其實,使用 Select * From a Where a.id in (......) 這種寫法是萬不得已的寫法,在SQL的查詢裡,in (...)這個用法,原本就是會消耗掉系統資源的指令..
通常,我只會在同事或某個主管有特殊需求時,才用到此一指令,在程式裡,不曾用過這種耗資源的指令,這種指令,不管是你是建置叢集索引或非叢集索引,對系統而言,它仍然是一個一個的分析,並將取得資料丟入它自定的暫存表中,完作後,再一次吐給你,所以,索引並不能增加速度..
若你真的非使用不可,可作兩次的作法,或許可快一些..
提資料庫端的查詢指令改成 Select * From a Where a.ID >= ba And a.ID <= bb
這樣資料庫可利用你所建置的索引,快速的取出你的資料...
然後,在前端利用程式的迴圈,再過濾一次你要的資料,如此,平均分擔資料的運算,即不占用你伺服器的資源,又可妥善使用你前端的效能,這可能會是一個較妥善的辦法...
結論
如同前一篇的討論,你應重新檢視你的資料庫規劃,因為正式程式中會使用到 Where in (...)的功能,並非善事...
最後,祝你成功...
charmmih iT邦研究生 5 級 ‧ 2012-10-16 11:44:30 檢舉
奇怪.....

若你真的非使用不可,可作兩次的作法,或許可快一些..
提資料庫端的查詢指令改成 Select * From a Where a.ID >= ba And a.ID <= bb
這樣資料庫可利用你所建置的索引,快速的取出你的資料...

1. name --> * ==> 變成全部欄位, 對效能有幫助嗎?
2. 一個索引可以解決問題, 就乭以快速精準取出你的資料, 何需大費周章...


然後,在前端利用程式的迴圈,再過濾一次你要的資料,如此,平均分擔資料的運算,即不占用你伺服器的資源,又可妥善使用你前端的效能,這可能會是一個較妥善的辦法...

3. 資料在前端ap處理會比後端資料庫更快嗎....
4. ?? 平均分擔資料運算 ?? AP拿到資料就處理前端UI
carl830 iT邦研究生 5 級 ‧ 2012-10-21 16:59:13 檢舉
網路流量也是問題吧
你抓那麼多資料到前端也沒有想到這些資料會佔多少頻寬??
居然會想用前端來過濾是哪招

用in的寫法 如果建好索引 用explain來看明明就OK
誤人子弟也別這樣......
summertw iT邦好手 1 級 ‧ 2012-10-23 10:39:53 檢舉
第一點答覆.
Select * From TableName ....
這是SQL語法在說明或檢討時,在無交易對象()的情況下的概括說明,請讀者勿誤會..
參考者應該不致如此不知變通才是...

第二點回覆..
索引的使用,不能只看只一資料表的狀況,因為一般的資料表通常會有關聯,所以必須看它的關聯參考等問題才能決定,因此,在這裡只能假設其索引是OK的情況下去說明..

第三、四點回覆....
有關資料庫的程式撰寫,應注意所有的工作分配,伺服器的資源是很昂貴的,所有的前端程式,有90%以上的時間是機器在等人,但伺服器的工作量,若設計的洽當,可達到80%以上的工作能量,所以,適度的做出分配是有其合理性的...
如果在最省的資源下榨出更多的工作量出來,那是資料庫規劃人員與程式設計人員的工作,台灣仍不比美國,機器的購買總是以最省的方式去購買,但確要做出比美國人更多的工作量,這是不爭的事實,所以,合理的做出分配是必要的
9
wiseguy
iT邦超人 1 級 ‧ 2012-10-12 16:39:35

把 A table 的 id 這個欄位建立索引 (unique index),就會有可觀的效能改善了。
PS. 如果 id 就是 primary key,就等同 unique index 效果了。

10
charmmih
iT邦研究生 5 級 ‧ 2012-10-12 18:15:44

select name from A where id in (1,5,7,1234.......)

欄位少少.....就用covering index

CREATE INDEX IX_A_ID ON A(ID,NAME);

SQL SERVER 則可考慮建非叢集索引:
CREATE NONCLUSTERED INDEX IX_A_ID ON A(ID,NAME);

charmmih iT邦研究生 5 級 ‧ 2012-10-12 18:41:05 檢舉

可能多達上百個

才上百個, 那就建有ID 欄位開頭索引, 就可以了....

4
player
iT邦大師 1 級 ‧ 2012-10-12 18:26:56

由於in中的數量是動態的,

開個暫存資料表放這些呢?
再者, 就如同上述其他人說的一樣, 建立索引

7
Albert
iT邦高手 1 級 ‧ 2012-10-14 01:37:01

想請教一下大大
在SQL中
檢視原始檔複製到剪貼簿列印關於
select name from A where id in (1,5,7,1234.......)

由於in中的數量是動態的,可能多達上百個,且不連續
不知道有沒有其他更好的寫法可以改善效能
謝謝

這種問題很正常
不需要蒙面
Oracle 印度團隊都會亂寫
你不了也是正常囉!!!!

改成 EXISTS (SELECT * FROM xx_list_table WHERE xx.ID=oo.ID)

這樣比 IN 速度快 100倍

charmmih iT邦研究生 5 級 ‧ 2012-10-14 11:59:26 檢舉

in (1,5,7,1234.......)

在調校SQL中, 還要考量到條件是已知的資料, 還是表格.......
而且最重要的事....索引一定要建好....

條件若是已知資料, 通常是放在IN (.....);

條件若是表格中的大量資料, 確實將 IN 改成 EXISTS,
採用直接代入驗證, 確實比挑出大量資料後再一一比對快許多.....

至於暫存檔應是不用如此大費周章......

總之, 這個題目根本原因就是....沒建 ID 開頭的索引......

charmmih iT邦研究生 5 級 ‧ 2012-10-14 12:05:42 檢舉

charmmih提到:
採用直接代入驗證, 確實比挑出大量資料後再一一比對快許多.....

EXISTS: 直接代入驗證, true/false
IN: 挑出大量資料後, 再一一比對true/false

我要發表回答

立即登入回答