VFP 愛用者社區 首頁 VFP 愛用者社區
本討論區為 Visual Foxpro 愛用者經驗交流的地方, 請多多利用"搜尋"的功能, 先查看看有無前例可循, 如果還有不懂的再發問. 部份主題有附加檔案, 須先註冊成為社區居民才可以下載.
 
 常見問題常見問題   搜尋搜尋   會員列表會員列表   會員群組會員群組   會員註冊會員註冊 
 個人資料個人資料   登入檢查您的私人訊息登入檢查您的私人訊息   登入登入 

讓你的SQL運行得更快

 
發表新主題   回覆主題    VFP 愛用者社區 首頁 -> SQL 討論區
上一篇主題 :: 下一篇主題  
發表人 內容
Ruey



註冊時間: 2003-03-12
文章: 1698
來自: tunglo

第 1 樓

發表發表於: 星期二 九月 23, 2003 12:25 am    文章主題: 讓你的SQL運行得更快 引言回覆

讓你的SQL運行得更快
來源:php link http://phplink.net/article.php?id=81

---- 人 們 在 使 用 SQL 時 往 往 會 陷 入 一 個 誤 區,即 太 關 注 於 所 得 的 結果 是 否 正 確,而 忽 略 了 不 同 的 實 現 方 法 之 間 可 能 存 在 的 性 能 差 異,這 種 性 能 差 異 在 大 型的 或 是 複 雜 的 資 料 庫 環 境 中 ( 如 聯 機 事 務 處 理 OLTP 或 決 策 支 援 系 統 DSS ) 中 表 現得 尤 為 明 顯。筆 者 在 工 作 實 踐 中 發 現,不 良 的 SQL 往 往 來 自 於 不 恰 當 的 索 引 設 計、不 充 份的 連 接 條 件 和 不 可 優 化 的 where 子 句。在 對 它 們 進 行 適 當 的 優 化 後,其 運 行 速 度 有 了 明顯 地 提 高!下 面 我 將 從 這 三 個 方 面 分 別 進 行 總 結:

---- 為 了 更 直 觀 地 說 明 問 題,所 有 實 例 中 的 SQL 運 行 時 間 均 經 過 測 試,不 超 過 1 秒 的 均 表 示 為 ( < 1 秒 )。

---- 測 試 環 境 --
---- 主 機:HP LH II
---- 主 頻:330MHZ
---- 記 憶 體:128 兆
---- 作 業 系 統:Operserver5.0.4
---- 資 料 庫:Sybase11.0.3

一、不 合 理 的 索 引 設 計
---- 例:表 record 有 620000 行,試 看 在 不 同 的 索 引 下,下 面 幾 個 SQL 的 運 行 情 況:
---- 1. 在 date 上 建 有 一 非 個 群 集 索 引

select count(*) from record where date >
''19991201'' and date < ''19991214''and amount >
2000 (25 秒 )
select date,sum(amount) from record group by date
(55 秒 )
select count(*) from record where date >
''19990901'' and place in (''BJ'',''SH'') (27 秒 )

---- 分 析:
----date 上 有 大 量 的 重 復 值,在 非 群 集 索 引 下,資 料 在 物 理 上 隨 機 存 放 在 資 料 頁 上,在 範 圍 查 找 時,必 須 執 行 一 次 表 掃 描 才 能 找 到 這 一 範 圍 內 的 全 部 行。

---- 2. 在 date 上 的 一 個 群 集 索 引

select count(*) from record where date >
''19991201'' and date < ''19991214'' and amount >
2000 ( 14 秒 )
select date,sum(amount) from record group by date
( 28 秒 )
select count(*) from record where date >
''19990901'' and place in (''BJ'',''SH'') ( 14 秒 )

---- 分 析:
---- 在 群 集 索 引 下,資 料 在 物 理 上 按 順 序 在 資 料頁 上,重 復 值 也 排 列 在 一 起,因 而 在 範 圍 查 找 時,可 以 先 找 到 這 個 範 圍 的 起 末 點,且 只 在 這個 範 圍 內 掃 描 資 料 頁,避 免 了 大 範 圍 掃 描,提 高 了 查 詢 速 度。

---- 3. 在 place,date,amount 上 的 組 合 索 引

select count(*) from record where date >
''19991201'' and date < ''19991214'' and amount >
2000 ( 26 秒 )
select date,sum(amount) from record group by date
( 27 秒 )
select count(*) from record where date >
''19990901'' and place in (''BJ, ''SH'') ( < 1 秒 )

---- 分 析:
---- 這 是 一 個 不 很 合 理 的 組 合 索 引,因 為 它 的 前導 列 是 place,第 一 和 第 二 條 SQL 沒 有 引 用 place,因 此 也 沒 有 利 用 上 索 引;第 三 個 SQL 使 用 了 place,且 引 用 的 所 有 列 都 包 含 在 組 合 索 引 中,形 成 了 索 引 覆 蓋,所 以 它 的 速 度 是非 常 快 的。

---- 4. 在 date,place,amount 上 的 組 合 索 引

select count(*) from record where date >
''19991201'' and date < ''19991214'' and amount >
2000(< 1 秒 )
select date,sum(amount) from record group by date
( 11 秒 )
select count(*) from record where date >
''19990901'' and place in (''BJ'',''SH'') ( < 1 秒 )

---- 分 析:
---- 這 是 一 個 合 理 的 組 合 索 引。它 將 date 作 為 前 導 列,使 每 個 SQL 都 可 以 利 用 索 引,並 且 在 第 一 和 第 三 個 SQL 中 形 成 了 索 引 覆 蓋,因 而 性 能 達 到 了 最 優。

---- 5. 總 結:

---- 缺 省 情 況 下 建 立 的 索 引 是 非 群 集 索 引,但 有 時 它 並 不 是 最 佳 的;合 理 的 索 引 設 計 要 建 立 在 對 各 種 查 詢 的 分 析 和 預 測 上。一 般 來 說:

---- 有 大 量 重 復 值、且 經 常 有 範 圍 查 詢

( between, >,< ,>=,< = ) 和 order by、group by 發 生 的 列,可 考 慮 建 立 群 集 索 引;

---- 經 常 同 時 存 取 多 列,且 每 列 都 含 有 重 復 值 可 考 慮 建 立 組 合 索 引;

---- 組 合 索 引 要 儘 量 使 關 鍵 查 詢 形 成 索 引 覆 蓋,其 前 導 列 一 定 是 使 用 最 頻 繁 的 列。

二、不 充 份 的 連 接 條 件:
---- 例:表 card 有 7896 行,在 card_no 上 有 一 個 非 聚 集 索 引,表 account 有 191122 行,在 account_no 上 有 一 個 非 聚集 索 引,試 看 在 不 同 的 表 連 接 條 件 下,兩 個 SQL 的 執 行 情 況:

select sum(a.amount) from account a,
card b where a.card_no = b.card_no ( 20 秒 )

---- 將 SQL 改 為:
select sum(a.amount) from account a,
card b where a.card_no = b.card_no and a.
account_no=b.account_no ( < 1 秒 )

---- 分 析:
---- 在 第 一 個 連 接 條 件 下,最 佳 查 詢 方 案 是 將 account 作 外 層 表,card 作 內 層 表,利 用 card 上 的 索 引,其 I/O 次 數 可 由 以 下 公 式 估 算 為:

---- 外 層 表 account 上 的 22541 頁 + ( 外 層 表 account 的 191122 行 * 內 層 表 card 上 對 應 外 層 表 第 一 行 所 要 查 找 的 3 頁 ) =595907 次 I/O

---- 在 第 二 個 連 接 條 件 下,最 佳 查 詢 方 案 是 將 card 作 外 層 表,account 作 內 層 表,利 用 account 上 的 索 引,其 I/O 次 數 可 由 以 下 公 式 估 算 為:

---- 外 層 表 card 上 的 1944 頁 + ( 外 層 表 card 的 7896 行 * 內 層 表 account 上 對 應 外 層 表 每 一 行 所 要 查 找 的 4 頁 ) = 33528 次 I/O

---- 可 見,只 有 充 份 的 連 接 條 件,真 正 的 最 佳 方 案 才 會 被 執 行。

---- 總 結:

---- 1. 多 表 操 作 在 被 實 際 執 行 前,查 詢 優 化 器 會 根 據 連 接 條件,列 出 幾 組 可 能 的 連 接 方 案 並 從 中 找 出 系 統 開 銷 最 小 的 最 佳 方 案。連 接 條 件 要 充 份 考慮 帶 有 索 引 的 表、行 數 多 的 表;內 外 表 的 選 擇 可 由 公 式:外 層 表 中 的 匹 配 行 數 * 內 層 表 中每 一 次 查 找 的 次 數 確 定,乘 積 最 小 為 最 佳 方 案。

---- 2. 查 看 執 行 方 案 的 方 法 -- 用 set showplanon,打 開 showplan 選 項,就 可 以 看 到 連 接 順 序、使 用 何 種 索 引 的 資 訊;想 看 更 詳 細 的 資 訊,需 用 sa 角 色 執 行 dbcc(3604,310,302)。

三、不 可 優 化 的 where 子 句
---- 1. 例:下 列 SQL 條 件 語 句 中 的 列 都 建 有 恰 當 的 索 引,但 執 行 速 度 卻 非 常 慢:

select * from record where
substring(card_no,1,4)=''5378''(13 秒 )
select * from record where
amount/30< 1000 ( 11 秒 )
select * from record where
convert(char(10),date,112)=''19991201'' ( 10 秒 )

---- 分 析:
---- where 子 句 中 對 列 的 任 何 操 作 結 果 都 是 在 SQL 運 行 時 逐 列 計 算 得 到 的,因 此 它 不 得 不 進 行 表 搜 索,而 沒 有 使 用 該 列 上 面 的 索 引;如果 這 些 結 果 在 查 詢 編 譯 時 就 能 得 到,那 麼 就 可 以 被 SQL 優 化 器 優 化,使 用 索 引,避 免 表 搜索,因 此 將 SQL 重 寫 成 下 面 這 樣:

select * from record where card_no like
''5378%'' ( < 1 秒 )
select * from record where amount
< 1000*30 ( < 1 秒 )
select * from record where date= ''1999/12/01''
( < 1 秒 )

---- 你 會 發 現 SQL 明 顯 快 起 來!

---- 2. 例:表 stuff 有 200000 行,id_no 上 有 非 群 集 索 引,請 看 下 面 這 個 SQL:

select count(*) from stuff where id_no in(''0'',''1'')
( 23 秒 )

---- 分 析:
---- where 條 件 中 的 ''in'' 在 邏 輯 上 相 當 於 ''or'',所 以 語 法 分 析 器 會 將 in (''0'',''1'') 轉 化 為 id_no =''0'' or id_no=''1'' 來 執 行。我 們 期 望 它 會 根 據 每 個 or 子 句 分 別 查 找,再 將 結 果 相 加,這 樣 可以 利 用 id_no 上 的 索 引;但 實 際 上 ( 根 據 showplan ) , 它 卻 採 用 了 "OR 策 略 ",即 先取 出 滿 足 每 個 or 子 句 的 行,存 入 臨 時 資 料 庫 的 工 作 表 中,再 建 立 唯 一 索 引 以 去 掉 重 復行,最 後 從 這 個 臨 時 表 中 計 算 結 果。因 此,實 際 過 程 沒 有 利 用 id_no 上 索 引,並 且 完 成 時 間還 要 受 tempdb 資 料 庫 性 能 的 影 響。

---- 實 踐 證 明,表 的 行 數 越 多,工 作 表 的 性 能 就 越 差,當 stuff 有 620000 行 時,執 行 時 間 竟 達 到 220 秒!還 不 如 將 or 子 句 分 開:

select count(*) from stuff where id_no=''0''
select count(*) from stuff where id_no=''1''

---- 得 到 兩 個 結 果,再 作 一 次 加 法 合 算。因 為 每 句 都 使 用 了 索引,執 行 時 間 只 有 3 秒,在 620000 行 下,時 間 也 只 有 4 秒。或 者,用 更 好 的 方 法,寫 一 個 簡 單的 存 儲 過 程:
create proc count_stuff as
declare @a int
declare @b int
declare @c int
declare @d char(10)
begin
select @a=count(*) from stuff where id_no=''0''
select @b=count(*) from stuff where id_no=''1''
end
select @c=@a+@b
select @d=convert(char(10),@c)
print @d

---- 直 接 算 出 結 果,執 行 時 間 同 上 面 一 樣 快!
---- 總 結:

---- 可 見,所 謂 優 化 即 where 子 句 利 用 了 索 引,不 可 優 化 即 發 生 了 表 掃 描 或 額 外 開 銷。

---- 1. 任 何 對 列 的 操 作 都 將 導 致 表 掃 描,它 包 括 資 料 庫 函 數、計 算 運 算 式 等 等,查 詢 時 要 盡 可 能 將 操 作 移 至 等 號 右 邊。

---- 2.in、or 子 句 常 會 使 用 工 作 表,使 索 引 失 效;如 果 不 產 生 大 量 重 復 值,可 以 考 慮 把 子 句 拆 開;拆 開 的 子 句 中 應 該 包 含 索 引。

---- 3. 要 善 於 使 用 存 儲 過 程,它 使 SQL 變 得 更 加 靈 活 和 高 效。

---- 從 以 上 這 些 例 子 可 以 看 出,SQL 優 化 的 實 質 就 是 在 結 果 正確 的 前 提 下,用 優 化 器 可 以 識 別 的 語 句,充 份 利 用 索 引,減 少 表 掃 描 的 I/O 次 數,儘 量 避 免表 搜 索 的 發 生。其 實 SQL 的 性 能 優 化 是 一 個 複 雜 的 過 程,上 述 這 些 只 是 在 應 用 層 次 的 一種 體 現,深 入 研 究 還 會 涉 及 資 料 庫 層 的 資 源 配 置、網 路 層 的 流 量 控 制 以 及 作 業 系 統 層 的總 體 設 計。

_________________
#############################
快樂媽咪系列幸福宅配,喝十全雞湯~原來幸福那麼簡單!!

學會VFP使用者社區的搜尋,Code才會更有趣~
#############################
回頂端
檢視會員個人資料 發送私人訊息
TIM001



註冊時間: 2004-04-29
文章: 4


第 2 樓

發表發表於: 星期四 四月 29, 2004 10:47 am    文章主題: 引言回覆

好文,我學習,謝謝提供
回頂端
檢視會員個人資料 發送私人訊息
從之前的文章開始顯示:   
發表新主題   回覆主題    VFP 愛用者社區 首頁 -> SQL 討論區 所有的時間均為 台北時間 (GMT + 8 小時)
1頁(共1頁)

 
前往:  
無法 在這個版面發表文章
無法 在這個版面回覆文章
無法 在這個版面編輯文章
無法 在這個版面刪除文章
無法 在這個版面進行投票
無法 在這個版面附加檔案
無法 在這個版面下載檔案


Powered by phpBB © 2001, 2005 phpBB Group
正體中文語系由 phpbb-tw 維護製作