數(shù)據(jù)庫系統(tǒng)概論——查詢優(yōu)化實驗報告

1、數(shù)據(jù)庫實驗報告 題目：查詢優(yōu)化 姓名： 李軍毅 日期：2016-5-14實驗?zāi)康?. 明確查詢優(yōu)化的重要性；2. 理解代數(shù)優(yōu)化與物理優(yōu)化方法；3. 學(xué)習在查詢中使用較優(yōu)的方法。實驗平臺1. OS：Windows XP2. DBMS：SQLServer2008、VC6.0（或者visio studio）3. IDE：Eclipse實驗用時：兩次上機實驗內(nèi)容1、 數(shù)據(jù)庫的恢復(fù)操作（導(dǎo)入數(shù)據(jù)）1. 在【程序】中打開Microsoft SQL Server Management Studio。新建數(shù)據(jù)庫“FoodmartII”2. 在數(shù)據(jù)庫FoodmartII 上右鍵單擊，選擇【任務(wù)】【導(dǎo)入數(shù)據(jù)】。3

2、. 在“導(dǎo)入和導(dǎo)出向?qū)А睂υ捒蛑?，?shù)據(jù)源選擇“Microsoft Access”，單擊“文件名”后面的【瀏覽】按鈕，按你的存儲路徑找到Foodmart.mdb 文件。單擊【下一步】。4.在“選擇目標”部分，注意目標數(shù)據(jù)庫的名稱應(yīng)為剛才建立的“FoodmartII”。5.選擇復(fù)制一個或多個數(shù)據(jù)庫表。6.在接下來的對話框中選擇可能用到的數(shù)據(jù)表，根據(jù)需要勾選。單擊【下一步】并“立即執(zhí)行”，成功導(dǎo)入數(shù)據(jù)后可以看到如下對話框。單擊【關(guān)閉】按鈕。觀察數(shù)據(jù)庫引擎中的FoodmartII，看一看數(shù)據(jù)庫中有哪些表，表中有哪些數(shù)據(jù)，是否包含索引，是否建立了視圖？二、理解索引對查詢的影響1.新建查詢，在查詢窗口中輸

3、入一個查詢命令。2.在【查詢】菜單中選擇【顯示估計的查詢計劃】，注意觀察查詢窗口下面的執(zhí)行計劃窗口。執(zhí)行該查詢（使用工具欄上的“執(zhí)行”按鈕或者【查詢】菜單上的“執(zhí)行”命令），觀察右側(cè)【屬性】窗口中“返回的行數(shù)”“占用時間”等關(guān)鍵信息。3.為Customer 表建立索引。建立Customer_id 列的非聚集索引。執(zhí)行查詢，在【屬性】窗口中觀察查詢時間。3、 分析查詢條件對查詢執(zhí)行的影響1.新建查詢，輸入查詢命令，再按上面的步驟，觀察“估計的查詢計劃”和“占用時間”時間等信息，比較查詢條件對查詢執(zhí)行的影響。2.觀察查詢命令，在emplyee 表建立salary 列的非聚集索引。再次觀察上面這個查

4、詢命令的查詢計劃和執(zhí)行情況。4、 分析連接條件對連接操作的影響1.對比下面查詢的查詢計劃和查詢執(zhí)行情況2.在employee 表上對employee_id 列建立聚集索引.觀察查詢計劃和執(zhí)行情況的變化.5、 視圖的使用1. 執(zhí)行下面的查詢命令，觀察查詢計劃和執(zhí)行情況。2. 建立視圖“cust_prod_sales”，由product，customer ， sales_fact_1998 三個表組成，其中包含查詢常用的列（選取的列可以多于查詢Q51），再執(zhí)行下面的查詢，比較兩個查詢的執(zhí)行情況。6、 查詢優(yōu)化測試1. 數(shù)據(jù)準備，導(dǎo)入TPCH 數(shù)據(jù)集。數(shù)據(jù)導(dǎo)入方法同前面Footmark 的導(dǎo)入類似。

5、2. 對以下查詢進行優(yōu)化,寫出你的優(yōu)化方法. 實際執(zhí)行這個查詢, 記錄你的執(zhí)行時間(毫秒). 實驗中出現(xiàn)的問題實驗內(nèi)容一、數(shù)據(jù)庫的恢復(fù)操作（導(dǎo)入數(shù)據(jù)）1.在【程序】中打開Microsoft SQL Server Management Studio。新建數(shù)據(jù)庫“FoodmartII”打開Microsoft SQL Server Management Studio，如圖：新建數(shù)據(jù)庫“FoodmartII”，如圖：2. 在數(shù)據(jù)庫FoodmartII 上右鍵單擊，選擇【任務(wù)】【導(dǎo)入數(shù)據(jù)】。 如圖：3. 在“導(dǎo)入和導(dǎo)出向?qū)А睂υ捒蛑?，?shù)據(jù)源選擇“Microsoft Access”，單擊“文件名”后面的【

6、瀏覽】按鈕，按你的存儲路徑找到Foodmart.mdb 文件。單擊【下一步】。 如圖，選擇“Microsoft Access”，找到Foodmart.mdb 文件：4. 在“選擇目標”部分，注意目標數(shù)據(jù)庫的名稱應(yīng)為剛才建立的“FoodmartII”。如圖，選擇我剛剛建立的“FoodmartII”數(shù)據(jù)庫：5. 選擇復(fù)制一個或多個數(shù)據(jù)庫表。 如圖，勾選“復(fù)制一個或多個數(shù)據(jù)庫表”：在接下來的對話框中選擇可能用到的數(shù)據(jù)表，根據(jù)需要勾選。我選擇了全部的數(shù)據(jù)表，并單擊下一步，如圖：單擊【下一步】后，選擇“立即執(zhí)行”，如圖：如下圖，可看到導(dǎo)入成功，單擊【關(guān)閉】按鈕： 觀察數(shù)據(jù)庫引擎中的FoodmartII，

7、我們可以看到數(shù)據(jù)庫中有哪些表，例如account表,category表，currency表等，如圖：我們點擊cureency表中的索引，可以看到初始時并沒有任何索引，如圖：右鍵cuurency表，選擇“編輯前200行”，可以看到表中的數(shù)據(jù)，如圖：二、理解索引對查詢的影響1.新建查詢，在查詢窗口中輸入一個查詢命令。select customer_idfrom customerwhere customer_id>60002. 在【查詢】菜單中選擇【顯示估計的查詢計劃】，注意觀察查詢窗口下面的執(zhí)行計劃窗口。如圖，表掃描占100%：執(zhí)行該查詢（使用工具欄上的“執(zhí)行”按鈕或者【查詢】菜單上的“執(zhí)行

8、”命令），觀察右側(cè)【屬性】窗口中“返回的行數(shù)”“占用時間”等關(guān)鍵信息。如圖，我們可以看到返回的行數(shù)為4281行，占用的時間大約為2秒多：3. 為Customer 表建立索引。建立Customer_id 列的非聚集索引，如下圖所示。輸入命令： create index ID_nonclus on customer(customer_id);建立非聚集索引：在customer表中查看索引，可以看到我們已經(jīng)建立好的非聚集索引，如圖：建立好索引后，仍使用如下查詢命令：select customer_idfrom customerwhere customer_id>6000在菜單欄中的“查詢”下點

9、擊“顯示估計的執(zhí)行計劃”，觀察新的查詢計劃，如圖，新的執(zhí)行計劃索引查找占100%：執(zhí)行該查詢，在【屬性】窗口中觀察查詢時間。如圖，我們可以看到，建立好索引再進行查詢，占用時間減少到不足1秒：三、分析查詢條件對查詢執(zhí)行的影響1.新建查詢，輸入查詢命令，再按上面的步驟，觀察“估計的查詢計劃”和“占用時間”時間等信息，比較查詢條件對查詢執(zhí)行的影響。Q1： select customer_id from customer where customer_id=2621;初始情況下未建立索引，輸入命令后，在菜單欄中的“查詢”項下選擇“顯示估計的執(zhí)行計劃”，表掃描占100%：然后點擊執(zhí)行，在屬性欄中可以看到

10、，返回的行數(shù)為1，占用的時間為7秒多，如圖：然后建立非聚集索引，在新建查詢中輸入上述命令，選擇“顯示估計的執(zhí)行計劃”，如圖，索引查找占100%：點擊“執(zhí)行”，在屬性欄中可以看到，返回的行數(shù)為1，占用的時間為2秒多，如圖：再把where 條件分別改寫為：customer_id>2621 和 customer_id<>2621，觀察他們有什么異同?？偨Y(jié)查詢命令書寫的經(jīng)驗。 Q2： select customer_id from customer where customer_id>2621;顯示估計的執(zhí)行計劃，表掃描占100%：點擊“執(zhí)行”，在屬性欄中可以看到，返回的行數(shù)為

11、7650行，占用的時間為3秒多，如圖：建立非聚集索引后，顯示估計的執(zhí)行計劃，可以看到，索引查找占100%：點擊“執(zhí)行”后，在屬性欄中可以看到返回的行數(shù)為7650行，占用的時間為2秒多，如圖： Q3: select customer_id from customer where customer_id!=2621;這里我使用的是!=而不是<>，顯示估計的執(zhí)行計劃，表掃描占100%，如圖：點擊“執(zhí)行”，在屬性欄中可以看到，返回的行數(shù)為10260行，占用時間為3秒多，如圖：建立索引后，顯示估計的執(zhí)行計劃，可以看到，索引掃描占100%：點擊“執(zhí)行”，屬性欄中可以看到，返回的行數(shù)為10260

12、行，占用的時間為2秒多，如圖：可以知道，不等于操作符是永遠用不到索引的，索引只能告訴什么存在于表中，而不能告訴什么不存在于表中，當數(shù)據(jù)庫遇到“！=”，“<>”時，會轉(zhuǎn)而用全表掃描，對a<>0的條件應(yīng)寫為a<0 or a>0.2. 觀察下面的查詢命令： select full_name,salary from employee where salary>30000;在未建立索引的情況顯示估計的執(zhí)行計劃，表掃描占100%，如圖：返回行數(shù)為8行，時間大約3秒多，如圖：在emplyee 表建立salary 列的非聚集索引。再次觀察上面這個查詢命令的查詢計劃和執(zhí)

13、行情況。RID查找占87%，索引查找占13%，如圖：執(zhí)行后，返回行數(shù)為8，占用時間為2秒多，如圖：（1） 請寫出你對以上內(nèi)容的分析或得到的經(jīng)驗。 盡量少用不等于查詢條件 當需要查找的數(shù)據(jù)特別多時，使用全表掃描或許比索引掃描還要好（2）試一試, 你還能得到哪些查詢命令書寫的經(jīng)驗? (不同查詢語句導(dǎo)致不同查詢計劃) 當插入的數(shù)據(jù)為數(shù)據(jù)表的記錄數(shù)量10%以上時，首先需要刪除該表的索引來提高數(shù)據(jù)的插入效率，當數(shù)據(jù)全部插入后再建立索引。避免在索引列上使用函數(shù)或計算，在where子句中，如果索引列是函數(shù)的一部分，優(yōu)化器將不使用索引而使用全表掃描，舉例：低效：select * from table wher

14、e salary*12>25000高效：select * from table where salary>25000/12索引列上用>=替代>，舉例：高效：select * from table where Deptno>=4低效：select * from table where Deptno>3四、分析連接條件對連接操作的影響1.對比下面查詢的查詢計劃和查詢執(zhí)行情況Q41:Select employee.employee_id,full_name,employee.salary,pay_date, salary_paidfrom employee,sal

15、ary顯示估計的執(zhí)行計劃，如圖，嵌套循環(huán)96%,表假脫機4%：Q42:select employee.employee_id,full_name,employee.salary,pay_date,salary_paidfrom employee,salarywhere employee.employee_id=salary.employee_id顯示估計的執(zhí)行計劃，哈希匹配50%，表掃描各占41%和9%：點擊“執(zhí)行”，返回行數(shù)為21252行，占用時間3秒多：Q43：Select employee.employee_id,full_name,employee.salary,pay_date,sa

16、lary_paidfrom employee,salarywhere employee.employee_id>salary.employee_id顯示估計的執(zhí)行計劃，嵌套循環(huán)占73%，索引假脫機27%：但是，點擊“執(zhí)行”，因為數(shù)據(jù)溢出，無法完成。2. 在employee 表上對employee_id 列建立聚集索引.觀察查詢計劃和執(zhí)行情況的變化.create CLUSTERED index ID_cluson employee(employee_id);如圖：Q41:select employee.employee_id,full_name,employee.salary,pay_da

17、te,salary_paidfrom employee,salary顯示估計的執(zhí)行計劃，嵌套循環(huán)占96%，表假脫機4%：Q42:select employee.employee_id,full_name,employee.salary,pay_date,salary_paidfrom employee,salarywhere employee.employee_id=salary.employee_id顯示估計的執(zhí)行計劃，哈希匹配50%，聚集索引掃描9%，表掃描41%：點擊“執(zhí)行”，返回行數(shù)為21252行，占用時間為0.320秒：Q43:select employee.employee_id,

18、full_name,employee.salary,pay_date,salary_paidfrom employee,salarywhere employee.employee_id>salary.employee_id顯示估計的執(zhí)行計劃，嵌套循環(huán)73%，索引假脫機27%：同樣因為數(shù)據(jù)溢出無法完成執(zhí)行。分析以上內(nèi)容，總結(jié)你的查詢優(yōu)化經(jīng)驗。索引分為聚集索引和非聚集索引兩種。聚集索引就是物理索引，也就是數(shù)據(jù)的物理存儲順序，聚集索引的葉子節(jié)點就是數(shù)據(jù)行本身，含有聚集索引的表，它的數(shù)據(jù)行的組織方式，是跟聚集索引的順序是一致的，一張表里，只能有一個聚集索引，決定著數(shù)據(jù)行的組織方式。非聚集索引是邏

19、輯索引，它跟數(shù)據(jù)的組織順序是毫無關(guān)系的，用一系列指針來指向數(shù)據(jù)行，從而描述數(shù)據(jù)行的位置。聚集索引的最大優(yōu)勢就是大范圍數(shù)據(jù)查詢有著較高的速率，能以最快的速度縮小查詢范圍，以最快的速度進行字段排序。聚集索引字段選擇優(yōu)先級：時間字段>>會進行大范圍查詢的列>>具有唯一值的有實際意義的字段>>自增列ID。1.時間字段：若表里面有時間列，并且時間是按照數(shù)據(jù)插入順序增長時（時間無需唯一即可有重復(fù)值，哪怕是大范圍重復(fù)），建議采用時間列作為聚集索引的第一選擇。理由：聚集索引有一個巨大的優(yōu)勢就是進行大范圍數(shù)據(jù)查找，而且這個優(yōu)勢會隨著數(shù)據(jù)量的增加而越來越明顯，一般來說我們需要進

20、行大數(shù)據(jù)量范圍查詢時都會用時間列圍作為篩選條件，由于聚集索引不存在書簽查找而且可以進行連續(xù)掃描，因此查詢速度會非?？?。時間列數(shù)據(jù)最好是順序插入的這樣可以盡量減少磁盤碎片，是數(shù)據(jù)存儲相對集中，便于連續(xù)數(shù)據(jù)讀取。2.會進行大范圍查詢的列：若表里面沒有時間字段或者時間字段不適合做聚集索引，可以選擇那些在建表時就明確知道會經(jīng)常進行大范圍數(shù)據(jù)篩選的列，而且最好是選擇性較低的列(即有較多重復(fù)值的列，性別這種列不算啦)，如有必要可以使用組合索引。理由：聚集索引在數(shù)據(jù)查詢的優(yōu)勢主要在于范圍數(shù)據(jù)查找，把聚集索引弄成唯一的把這個大好優(yōu)勢給白白浪費了。3.具有唯一值的有實際意義的字段：若找不到適合條件1、2的列，那

21、還是乖乖的把聚集索引列建立在唯一列上吧，最好找那種有實際意義的具有唯一性的列，比如訂單表可以用訂單號作聚集索引，訂單明細表使用訂單號和產(chǎn)品編號做聯(lián)合聚集索引。理由：找不到合適的時間字段和較低選擇性字段的話，把主鍵建成聚集索引是我們大多情況下的選擇。這里建議把唯一性的聚集索引順便建成主鍵，和編碼時方法、變量命名一樣，推薦列名自解釋，即看到列名就知道它就是主鍵，省得你再去猜，比如訂單表你來個自增ID列做主鍵，再建一個OrderCode列做訂單號，用這個表時你得懷疑這個OrderCode是不是唯一的呢，有沒有建立唯一約束呢，同理在訂單明細表來個自增列ID也會產(chǎn)生如此疑問，產(chǎn)生疑問還是小事，若是你忘記

22、了在應(yīng)該唯一的列上建立約束，沒準哪天程序控制不好給你個巨大的驚喜。4. 自增列ID：前面3中條件都找不到合適的列了還是使用我們的神器自增列ID吧，自增列ID也是我們使用最多的主鍵(順便也就成聚集索引了)，而且能較好滿足我們大多數(shù)需求。自增ID列堪稱無所不能，int類型只占用4個字節(jié)完全滿足窄索引要求，絕對的順序存儲可以有效降低索引碎片，完全符合我們的見表習慣，有用沒用來個自增ID列做主鍵總是沒錯的。 與聚集索引不同，非聚集索引可以建立多個，這也給我們帶來了很大的靈活，畢竟聚集索引就那么一個不可能靠它滿足所有需求，更多的我們得依賴非聚集索引。但是，建立索引是有代價的，任何涉及到索引列的數(shù)據(jù)修改都

23、會導(dǎo)致索引的修改，索引越多數(shù)據(jù)的曾、刪、改的額外代價也就越大。對于非聚集索引來說，我們的目標是用盡可能少的索引覆蓋盡可能多的查詢。 非聚集索引的列選擇順序(組合索引):經(jīng)常被使用為查詢條件列>>具有較高選擇性的列(選擇性越高越好，唯一最好)>>經(jīng)常排序的列1.經(jīng)常被使用為查詢條件列：我們的查詢千變?nèi)f化，建立索引時要首先考慮有哪些列被經(jīng)常性的用于各種查詢，把使用頻率較高的列作為組合索引的第一列(先導(dǎo)列)，若一個查詢中沒有用到組合索引中的先導(dǎo)列，多數(shù)情況下這個索引就不會被使用，因此為了盡可能多的復(fù)用組合索引把使用較多的查詢列作為組合索引的第一列吧。(關(guān)于這點對于聚集索引的組

24、合索引同樣適用)2.具有較高選擇性的列：這點很簡單盡量使用高選擇性列作為先導(dǎo)列，如果可以通過第一個條件過濾(隨便什么判定邏輯=、>、<、like)，只要能大幅減少數(shù)據(jù)范圍，就把它作為先導(dǎo)列。3.條件1、2、3都確定不了時那就用經(jīng)常被排序的列吧，我們的很多操作都需要先進行排序才可以進行進一步查詢，比如group by，like等操作都是要先進行排序操作才可以完成下一步查詢。五、視圖的使用1.執(zhí)行下面的查詢命令，觀察查詢計劃和執(zhí)行情況。Q51:select lname,fname,brand_name,product_namefrom sales_fact_1998,product,c

25、ustomerwhere customer.customer_id=sales_fact_1998.customer_idand duct_id=sales_fact_1998.product_idand sales_fact_1998.customer_id=9143顯示估計的執(zhí)行計劃，哈希匹配7%，表掃描67%，嵌套循環(huán)1%，表掃描23%，表掃描2%：點擊“執(zhí)行”，返回的行數(shù)為147行，占用時間為2秒多：2. 建立視圖“cust_prod_sales”，由product，customer ， sales_fact_1998 三個表組成，其中包含查詢常用的列（選取的列可

26、以多于查詢Q51），再執(zhí)行下面的查詢。建立視圖：create view cust_prod_salesasselect lname,fname,brand_name,product_name,customer.customer_idfrom sales_fact_1998,product,customer;輸入查詢命令：Q52:select lname,fname,brand_name,product_namefrom cust_prod_saleswhere customer_id=9143顯示估計的執(zhí)行計劃，嵌套循環(huán)98%，行計數(shù)假脫機2%：同樣因為數(shù)據(jù)溢出，無法完成執(zhí)行。請寫出你對以上內(nèi)

27、容的分析和得到的經(jīng)驗。建立普通的視圖對查詢并沒有太大的作用，因為對視圖的查詢最終也要轉(zhuǎn)化為對基本表的查詢，視圖的使用只是可以把表隱藏起來，但是，在視圖上建立索引卻可以加快查詢速度，但會增加開銷。六、查詢優(yōu)化測試1.數(shù)據(jù)準備，導(dǎo)入TPCH 數(shù)據(jù)集。數(shù)據(jù)導(dǎo)入方法同前面Footmark 的導(dǎo)入類似。建立TPCH數(shù)據(jù)庫，如圖：右鍵單擊TPCH數(shù)據(jù)庫，選擇任務(wù)中的導(dǎo)入數(shù)據(jù)庫：導(dǎo)入數(shù)據(jù)時，“數(shù)據(jù)源”選擇“平面文件”，通過瀏覽指定文件夾和文件名(類型選擇”所有文件”)，如圖：單擊左側(cè)“數(shù)據(jù)源”列表中“列”項目，指定” 列分隔符”為“豎線”，單擊”重置列”按鈕，觀察”預(yù)覽行”窗口顯示的數(shù)據(jù)格式是否正確。如下圖

28、：如下圖，導(dǎo)入CUSTOMER表：導(dǎo)入成功：在管理欄中可以看到CUSTOMER表的各列名及其屬性：導(dǎo)入LINEITEM表：導(dǎo)入成功：在管理欄中可以看到LINEITEM表的各列名及其屬性：導(dǎo)入NATION表：導(dǎo)入成功：在管理欄中可以看到NATION表的各列名及其屬性：導(dǎo)入ORDER表：導(dǎo)入成功：在管理欄中可以看到ORDER表的各列名及其屬性：導(dǎo)入PART表：導(dǎo)入成功：在管理欄中可以看到PART表的各列名及其屬性：導(dǎo)入PARTSUPP表：導(dǎo)入成功：在管理欄中可以看到PARTSUPP表的各列名及其屬性：導(dǎo)入REGION表：導(dǎo)入成功：在管理欄中可以看到REGION表的各列名及其屬性：導(dǎo)入SUPPLIE

29、R表：導(dǎo)入成功：在管理欄中可以看到SUPPLIER表的各列名及其屬性：2. 對以下查詢進行優(yōu)化,寫出你的優(yōu)化方法. 實際執(zhí)行這個查詢, 記錄你的執(zhí)行時間(毫秒).Q1:selectl_returnflag,l_linestatus,sum(l_quantity) as sum_qty,sum(l_extendedprice) as sum_base_price,sum(l_extendedprice*(1-l_discount) as sum_disc_price,sum(l_extendedprice*(1-l_discount)*(1+l_tax) as sum_charge,avg(l_

30、quantity) as avg_qty,avg(l_extendedprice) as avg_price,avg(l_discount) as avg_disc,count(*) as count_orderfromlineitemwherel_shipdate <= '1998-12-01'group byl_returnflag,l_linestatusorder byl_returnflag,l_linestatus;首先在未對表進行任何操作的情況下執(zhí)行，返回行數(shù)為4行，占用時間為6秒多：然后，在lineitem表的l_returnflag,l_linestat

31、us列上建立非聚集索引：create index lndex_lon lineitem(l_returnflag,l_linestatus);執(zhí)行查詢，返回行數(shù)為4列，占用時間為5秒多：對這個查詢，我嘗試了建立臨時表，建立聚集索引的方法，均會導(dǎo)致總時間更多。Q2:select n_name, sum(l_extendedprice * (1 - l_discount) as revenuefrom customer,orders,lineitem,supplier,nation,regionwhere c_custkey = o_custkeyand l_orderkey = o_orderkeyand l_suppkey = s_suppkeyand c_nationkey = s_nationkeyand s_nationkey = n_nationkeyand n_regionkey = r_regionkeyand r_name = 'ASIA'and o_orderdate >=