第十章基本的询处理与最佳化.ppt

Copyright 黃三益 2003 資料庫核心理論與實務 第十章基本的查詢處理與最佳化 資料庫程式的執行 SQL敘述的處理流程 SQL查詢樹 基本關聯代數運算子的處理 SELECT的處理方式和成本 外部排序的處理方式和成本 資料庫程式的執行 通常SQL的敘述都是由程式執行所產生，但交由DBMS來處理 DBMS看到的是一串SQL敘述 資料庫程式的部分程式碼 建立資料庫連結物件 set conn = Server.CreateObject(ADODB.Connection) 開啟資料庫連結 conn.Open onlinedb query = SELECT * FROM product Set rs = conn.Execute(query) while not rs.EOF SQL敘述的處理流程 練習10-1 考慮圖10-2，如果第4行的SQL指令在檢查時發現錯誤，會有什麼後果？ Ans: 此時該SQL指令便不會執行，也因此rs裡不會有值。所以不會執行WHILE迴圈 SQL查詢樹 一顆SQL查詢樹是用來表達一種執行方案 每一葉節點記錄查詢所用到的每一資料表 每一中間節點記載處理的動作。標準的處理動作如關聯代數裡的運算子 SELECT name FROM Product, Author WHERE pName = ‘系統分析理論與實務’ AND Product.pNo = Author.pNo; SQL查詢樹（Cont.) 查詢樹的執行次序是由下而上、由左至右 上例的執行方式如下 Temp1=?pName=’系統分析理論與實務’ (Product); Temp2= Temp1 ?Temp1.pNo=Author.pNo Author; Result= ? name(Temp2); SQL剖析器所產生的查詢樹是最簡單的查詢樹，稱為初始查詢樹 查詢最佳化模組會將初始查詢樹轉換成較有效率的查詢樹 SQL查詢樹（Cont.) 從SQL查詢句建立初始查詢樹 FROM子句裡的每一個資料表是一個葉節點。 葉節點用集合乘法（卡迪森乘積）當中間節點兩兩串連起來。 加上一個SELECT(?)的中間節點，以WHERE子句當作其運算。 加上一個PROJECT(?)的根節點，以SELECT子句當作其運算 初始查詢樹 SQL查詢樹（Cont.) 初始查詢樹最佳化的轉換步驟 將SELECT的動作往下移，盡量接近葉節點 SQL查詢樹（Cont.) 將條件較嚴格的SELECT中間節點盡量往左邊移 SQL查詢樹（Cont.) 將相鄰的×中間節點 和 σ中間節點合併成一個 ?中間節點 SQL查詢樹（Cont.) 將PROJECT的動作往下移，盡量接近葉節點 練習10-2 找出「黃三益」所瀏覽過單價超過500元的商品資訊的最佳化查詢 查詢成本的預估指標 查詢樹的每一中間節點，實作的方式可能有數種，到底該採取哪一種？ 沒有哪一種處理方式必然可以得到比較有效率的運算 對於一個查詢句，現代的DBMS於是採用成本預估（Cost estimate）的方式來決定該採取哪一種處理方式 查詢最佳化模組試圖計算出和比較它們的「成本」 何謂運算成本 何謂成本：執行時間 硬碟的存取成本 大部分DBMS的瓶頸 CPU的計算成本 主記憶體DBMS的瓶頸 網路的通訊成本 分散式DBMS的成本 資料表和索引的相關符號 資料表的相關數據 r：資料表裡的記錄筆數 r Product = 100,000 b：資料表所佔的資料頁數 bProduct = 5000 bfr：每一資料頁可容納幾筆記錄 rProduct／bProduct = bfrProduct = 20 索引的相關數據 x：B+-tree的層數 xunitPrice= 3 bI1：B+-tree裡葉節點的個數 bI1unitPrice= 500 d：不同索引值的個數 dcatalog= 100 , dSEX = 2 參考下頁圖9-6 基本SELECT的處理方式 SELECT條件裡只有單一屬性 ?catalog=’Book’ Product ?unitPrice500 Product ?pNo=’b30999’ Product 三種處理方式 （SL）資料頁循序搜尋 （SI）利用索引結構 （參考圖9-6） （SIC）利用群聚索引結構 假設有unitPrice的群聚索引，參考下頁圖10-7 CREATE INDEX I3 ON Product(unitPrice) CLUSTER; 利用該索引結構來處理?unitPrice500 Product 非常有效率 利用該索引結構找到包含unitPrice500 的資料頁指標