全國最多中醫師線上諮詢網站-台灣中醫網
發文 回覆 瀏覽次數:1985
推到 Plurk!
推到 Facebook!

SQL查詢效率

答題得分者是:carstyc
dadai
一般會員


發表:29
回覆:31
積分:12
註冊:2009-01-17

發送簡訊給我
#1 引用回覆 回覆 發表時間:2010-06-10 08:49:49 IP:219.90.xxx.xxx 訂閱
最近遇到一些SQL查詢效率的問題
雖然想努力縮短查詢的時間
但是目前仍然找不到有效率的查詢方式

只是不知道這個問題在這邊詢問是否合適
版主們如果不恰當的話
我馬上刪除


我要搜尋的對象如下
TABLE A 約有 1000萬筆資料
TABLE B 約有 100萬筆資料
TABLE A 的 INDEX 為 INDEX_A
TABLE B 的 INDEX 為 INDEX_B

我想要搜尋的資料是先用某條件找到TABLE B符合的一串資料INDEX_B
然後用這些INDEX_B到INDEX_A中找資料


目前試了幾個方式
1. IN
SELECT * FROM A WHERE A.INDEX_B IN (SELECT INDEX_B FROM B WHERE ...)
2. EXISTS
SELECT * FROM A WHERE EXISTS(SELECT INDEX_B FROM B WHERE (某條件) AND A.INDEX_A = B.INDEX_B)
3. 同時搜尋兩張TABLE
SELECT A.* FROM A, B WHERE (某條件) AND A.INDEX_A = B.INDEX_B
4. INNER JOIN
SELECT A.* FROM A INNER JOIN B ON (某條件) AND A._INDEX_A = B.INDEX_B

效率都無法達到預期的目標

請問還有什麼方式可以更快速更有效率的搜尋嗎
或是網路上是否有工具可以對SQL查詢做最佳化處理

carstyc
資深會員


發表:16
回覆:254
積分:329
註冊:2003-07-18

發送簡訊給我
#2 引用回覆 回覆 發表時間:2010-06-10 12:18:11 IP:203.79.xxx.xxx 訂閱
你的問題,4個解法的關鍵都一樣

看來你兩個TABLE 各只建一個 index 分別是 INDEX_A 及 INDEX_B

但你的查詢條件中 (某條件) 裡面到底用了什麼條件?

是其它的欄位 FIELD_C or FIELD_D 嗎?


如果條件是根據 FIELD_C or FIELD_D 的話,就幫它們也建個 INDEX吧,否則還是等於要 full scan,百萬筆的資料當然就是慢囉。


舉例

SELECT A.* FROM A, B WHERE A.INDEX_A = B.INDEX_B AND A.FIELD_C='CCC' AND B.FIELD_D='DDD'

那就幫 TABLE A 建 FIELD_C 的 INDEX ,幫 TABLE B 建 FIELD_D 的 INDEX 。

如此一定有明顯的改善。




===================引 用 dadai 文 章===================
最近遇到一些SQL查詢效率的問題
雖然想努力縮短查詢的時間
但是目前仍然找不到有效率的查詢方式

只是不知道這個問題在這邊詢問是否合適
版主們如果不恰當的話
我馬上刪除


我要搜尋的對象如下
TABLE A 約有 1000萬筆資料
TABLE B 約有 100萬筆資料
TABLE A 的 INDEX 為 INDEX_A
TABLE B 的 INDEX 為 INDEX_B

我想要搜尋的資料是先用某條件找到TABLE B符合的一串資料INDEX_B
然後用這些INDEX_B到INDEX_A中找資料


目前試了幾個方式
1. IN
?? ?SELECT * FROM A WHERE A.INDEX_B IN (SELECT INDEX_B FROM B WHERE ...)
2. EXISTS
?? ?SELECT * FROM A WHERE EXISTS(SELECT INDEX_B FROM B WHERE (某條件) AND A.INDEX_A = B.INDEX_B)
3. 同時搜尋兩張TABLE
?? ?SELECT A.* FROM A, B WHERE (某條件) AND A.INDEX_A = B.INDEX_B
4. INNER JOIN
?? ?SELECT A.* FROM A INNER JOIN B ON (某條件) AND A._INDEX_A = B.INDEX_B

效率都無法達到預期的目標

請問還有什麼方式可以更快速更有效率的搜尋嗎
或是網路上是否有工具可以對SQL查詢做最佳化處理

dadai
一般會員


發表:29
回覆:31
積分:12
註冊:2009-01-17

發送簡訊給我
#3 引用回覆 回覆 發表時間:2010-06-10 20:51:40 IP:123.204.xxx.xxx 訂閱
carstyc 感謝您的建議
不過很遺憾的是我沒有權限修改資料庫
只有查詢的權限而已....Orz
所以才會請教是否有更有效率的查詢方式

另外我確認過 B.FIELD_D 也有設為 index 了
只是不知道為什麼最快我還是得花費90秒左右的查詢時間


===================引 用 carstyc 文 章===================
你的問題,4個解法的關鍵都一樣

看來你兩個TABLE 各只建一個 index 分別是 INDEX_A 及 INDEX_B

但你的查詢條件中 (某條件) 裡面到底用了什麼條件?

是其它的欄位 FIELD_C or FIELD_D 嗎?


如果條件是根據 FIELD_C or FIELD_D 的話,就幫它們也建個 INDEX吧,否則還是等於要 full scan,百萬筆的資料當然就是慢囉。


舉例

SELECT A.* FROM A, B WHEREA.INDEX_A = B.INDEX_B AND A.FIELD_C='CCC' AND B.FIELD_D='DDD'

那就幫 TABLE A 建 FIELD_C 的 INDEX ,幫 TABLEB 建 FIELD_D 的 INDEX 。

如此一定有明顯的改善。


carstyc
資深會員


發表:16
回覆:254
積分:329
註冊:2003-07-18

發送簡訊給我
#4 引用回覆 回覆 發表時間:2010-06-11 00:08:05 IP:219.84.xxx.xxx 訂閱
速度慢的原因非常多種,如你說的,若 B,FIELD_D有設index,那 A.FIELD_C呢...是不是也有index??


若查詢的條件其中一個沒有index,而該TABLE又大到一個程度時,是有可能拖累到整個執行結果。


另外,你查詢結果的筆數是多少? 如果百萬筆資料,你撈出來的結果是十萬筆。 幾十秒...應該算還好吧...你算算這幾十萬筆的資料量,從資料庫傳到你的Client端的電腦,封包在網路上傳遞,總該給它一點時間的。


其實最簡單的方法,看來貴公司的資訊規模不小,程式開發人員沒辦法建index,那一定有專職的DBA人員,簡單,把 SQL給他,請他調教調教。畢竟都是領薪水的,你寫寫程式...他調調SQL,應該是剛好而已。總不能讓DBA都沒事幹,只會在那裡備份資料庫而已吧。


===================引 用 dadai 文 章===================
carstyc 感謝您的建議
不過很遺憾的是我沒有權限修改資料庫
只有查詢的權限而已....Orz
所以才會請教是否有更有效率的查詢方式

另外我確認過 B.FIELD_D 也有設為 index 了
只是不知道為什麼最快我還是得花費90秒左右的查詢時間


===================引 用 carstyc 文 章===================
你的問題,4個解法的關鍵都一樣

看來你兩個TABLE 各只建一個 index 分別是 INDEX_A 及 INDEX_B

但你的查詢條件中 (某條件) 裡面到底用了什麼條件?

是其它的欄位 FIELD_C or FIELD_D 嗎?


如果條件是根據 FIELD_C or FIELD_D 的話,就幫它們也建個 INDEX吧,否則還是等於要 full scan,百萬筆的資料當然就是慢囉。


舉例

SELECT A.* FROM A, B WHEREA.INDEX_A = B.INDEX_B AND A.FIELD_C='CCC' AND B.FIELD_D='DDD'

那就幫 TABLE A 建 FIELD_C 的 INDEX ,幫 TABLEB 建 FIELD_D 的 INDEX 。

如此一定有明顯的改善。


dadai
一般會員


發表:29
回覆:31
積分:12
註冊:2009-01-17

發送簡訊給我
#5 引用回覆 回覆 發表時間:2010-06-11 07:21:46 IP:219.90.xxx.xxx 訂閱
carstyc 您真是說到我的心坎裡了
感謝您的建議和回覆

===================引 用 carstyc 文 章===================
速度慢的原因非常多種,如你說的,若 B,FIELD_D有設index,那 A.FIELD_C呢...是不是也有index??


若查詢的條件其中一個沒有index,而該TABLE又大到一個程度時,是有可能拖累到整個執行結果。


另外,你查詢結果的筆數是多少? 如果百萬筆資料,你撈出來的結果是十萬筆。 幾十秒...應該算還好吧...你算算這幾十萬筆的資料量,從資料庫傳到你的Client端的電腦,封包在網路上傳遞,總該給它一點時間的。


其實最簡單的方法,看來貴公司的資訊規模不小,程式開發人員沒辦法建index,那一定有專職的DBA人員,簡單,把 SQL給他,請他調教調教。畢竟都是領薪水的,你寫寫程式...他調調SQL,應該是剛好而已。總不能讓DBA都沒事幹,只會在那裡備份資料庫而已吧。


系統時間:2017-12-16 0:02:10
聯絡我們 | Delphi K.Top討論版
本站聲明
1. 本論壇為無營利行為之開放平台,所有文章都是由網友自行張貼,如牽涉到法律糾紛一切與本站無關。
2. 假如網友發表之內容涉及侵權,而損及您的利益,請立即通知版主刪除。
3. 請勿批評中華民國元首及政府或批評各政黨,是藍是綠本站無權干涉,但這裡不是政治性論壇!