數(shù)據(jù)庫(kù)實(shí)驗(yàn)報(bào)告

　題目: 查詢(xún)優(yōu)化 姓名: 李軍毅 日期:2016-5-14 實(shí)驗(yàn)?zāi)康?1. 明確查詢(xún)優(yōu)化的重要性; 2. 理解代數(shù)優(yōu)化與物理優(yōu)化方法; 3. 學(xué)習(xí)在查詢(xún)中使用較優(yōu)的方法。

　實(shí)驗(yàn)平臺(tái) 1. OS: Windows XP 2. DBMS: SQLServer2008、VC6、0(或者 visio studio) 3. IDE: Eclipse 實(shí)驗(yàn)用時(shí): 兩次上機(jī) 實(shí)驗(yàn)內(nèi)容

　 一、 數(shù)據(jù)庫(kù)的恢復(fù)操作( 導(dǎo)入數(shù)據(jù))

　 1. 在【程序】中打開(kāi) Microsoft SQL Server Management Studio 。新建數(shù)據(jù)庫(kù)“ Foodma rtII ”

　2. 在數(shù)據(jù)庫(kù) FoodmartII 上右鍵單擊, , 選擇【任務(wù)】【導(dǎo)入數(shù)據(jù)】。

　3. 在“導(dǎo)入與導(dǎo)出向?qū)?rdquo;對(duì)話(huà)框中, , 數(shù)據(jù)源選擇“ Microsoft Access ”, , 單擊“文

　件名”后面的【瀏覽】按鈕, , 按您的存儲(chǔ)路徑找到 Foodmart 、 mdb 文件。單擊【下一步】。

　4 4 、在“選擇目標(biāo)”部分, , 注意目標(biāo)數(shù)據(jù)庫(kù)的名稱(chēng)應(yīng)為剛才建立的“ FoodmartII。

　”。

　5 5 、選擇復(fù)制一個(gè)或多個(gè)數(shù)據(jù)庫(kù)表。

　6 6 、在接下來(lái)的對(duì)話(huà)框中選擇可能用到的數(shù)據(jù)表, , 根據(jù)需要勾選。單擊【下一步】并“立即執(zhí)行”, , 成功導(dǎo)入數(shù)據(jù)后可以瞧到如下對(duì)話(huà)框。單擊【關(guān) 閉】按鈕。的 觀(guān)察數(shù)據(jù)庫(kù)引擎中的 FoodmartII, 瞧一瞧數(shù)據(jù)庫(kù)中有哪些表, , 表中有哪些數(shù)據(jù), , 就是否包含索引, , 就是否建立了視圖？

　二、理解索引對(duì)查詢(xún)的影響 1 1 、新建查詢(xún), , 在查詢(xún)窗口中輸入一個(gè)查詢(xún)命令。

　2 2 、在【查詢(xún)】菜單中選擇【顯示估計(jì)的查詢(xún)計(jì)劃】, , 注意觀(guān)察查詢(xún)窗口下面的執(zhí)行計(jì)劃窗口。執(zhí)行該查詢(xún)( ( 使用工具欄上的“執(zhí)行”按鈕或者【查詢(xún)】菜單上的“執(zhí)行”命令 ), 觀(guān)察右側(cè)【屬性】窗口中“返回的行數(shù)”“占用時(shí)間”等關(guān)鍵信息。

　3 3為 、為 Customer 立 表建立索引。建立 Customer_id 列的非聚集索引。執(zhí)行查詢(xún), ,在【 屬性】窗口中觀(guān)察查詢(xún)時(shí)間。

　三、 分析查詢(xún)條件對(duì)查詢(xún)執(zhí)行的影響 1 1 、新建查詢(xún), , 輸入查詢(xún)命令, , 再按上面的步驟, , 觀(guān)察“估計(jì)的查詢(xún)計(jì)劃”與“占用時(shí)間”時(shí)間等信息, , 比較查詢(xún)條件對(duì)查詢(xún)執(zhí)行的影響。

　2 2 、觀(guān)察查詢(xún)命令, ,在 在 emplyee 立 表建立 salary 列的非聚集索引。再次觀(guān)察上面這個(gè)查詢(xún)命令的查詢(xún)計(jì)劃與執(zhí)行情況。

　四、 分析連接條件對(duì)連接操作的影響 1 1 、對(duì)比下面查詢(xún)的查詢(xún)計(jì)劃與查詢(xún)執(zhí)行情況

　2 2 、在 employee 表上對(duì) employee_id 列建立聚集索引、觀(guān)察查詢(xún)計(jì)劃與執(zhí)行情況的變化、

　五、 視圖的使用 1. 執(zhí)行下面的查詢(xún)命令, , 觀(guān)察 查詢(xún)計(jì)劃與執(zhí)行情況。

　2. 建立視圖“ cust_prod_sales ”, ,由 由 product,customer , sales_fact_1998 三

　個(gè)表組成, , 其中包含查詢(xún)常用的列( (詢(xún) 選取的列可以多于查詢(xún) Q51), 再執(zhí)行下面的查詢(xún), , 比較兩個(gè)查詢(xún)的執(zhí)行情況。

　六、 查詢(xún)優(yōu)化測(cè)試 1. 數(shù)據(jù)準(zhǔn)備, , 導(dǎo)入 TPCH 數(shù)據(jù)集。數(shù)據(jù)導(dǎo)入方法同前面 Footmark 的導(dǎo)入類(lèi)似。

　2. 對(duì)以下查詢(xún)進(jìn)行優(yōu)化, , 寫(xiě)出您的優(yōu)化方法 、

　實(shí)際執(zhí)行這個(gè)查詢(xún) , 記錄您的執(zhí)行時(shí)間( ( 毫秒) ) 、

　實(shí)驗(yàn)中出現(xiàn)的問(wèn)題 實(shí)驗(yàn)內(nèi)容

　 一、數(shù)據(jù)庫(kù)的恢復(fù)操作( 導(dǎo)入數(shù)據(jù))

　 1 1 、在【程序】中打開(kāi) Microsoft SQL Server Management Studio 。新建數(shù)據(jù)庫(kù)“ FoodmartII ”

　打開(kāi) Microsoft SQL Server Management Studio, 如圖: :

　 新建數(shù)據(jù)庫(kù)“ FoodmartII ”, , 如圖: :

　 2. 在數(shù)據(jù)庫(kù) FoodmartII 上右鍵單擊, , 選擇【任務(wù)】【導(dǎo)入數(shù)據(jù)】。

　如圖: :

　 3. 在“導(dǎo)入與導(dǎo)出向?qū)?rdquo;對(duì)話(huà)框中, , 數(shù)據(jù)源選擇“ Microsoft Access ”, , 單擊“文件名”后面的【瀏覽】按鈕, , 按您的存儲(chǔ)路徑找到 Foodmart、 、 mdb 文件。單擊【下一步】。

　如圖, , 選擇“ Microsoft Access ”, , 找到 Foodmart 、 mdb 文件: :

　 4. 在“選擇目標(biāo)”部分, , 注意目標(biāo)數(shù)據(jù)庫(kù)的名稱(chēng)應(yīng)為剛才建立的“ FoodmartII。

　”。

　如圖, , 選擇我剛剛建立的“ FoodmartII ”數(shù)據(jù)庫(kù): :

　 5. 選擇復(fù)制一個(gè)或多個(gè)數(shù)據(jù)庫(kù)表。

　如圖, , 勾選“復(fù)制一個(gè)或多個(gè)數(shù)據(jù)庫(kù)表”: :

　 在接下來(lái)的對(duì)話(huà)框中選擇可能用到的數(shù)據(jù)表, , 根據(jù)需要勾選。我選擇了全部的數(shù)據(jù)表, , 并單擊下一步, , 如圖: :

　 單擊【下一步】后, , 選擇“立即執(zhí)行”, , 如圖: :

　 如下圖, 可瞧到導(dǎo)入成功, 單擊【關(guān)閉】按鈕:

　觀(guān)察數(shù)據(jù)庫(kù)引擎中的 FoodmartII, 我們可以瞧到數(shù)據(jù)庫(kù)中有哪些表, , 例如t account 表y ,category 表y ,currency 表等, , 如圖: :

　 我們點(diǎn)擊 y cureency 表中的索引, , 可以瞧到初始時(shí)并沒(méi)有任何索引, , 如圖: :

　 右鍵 y cuurency 表, , 選擇“編輯前 0 200 行”, , 可以瞧到表中的數(shù)據(jù), , 如圖: :

　 二、理解索引對(duì)查詢(xún)的影響 1 1 、新建查詢(xún), , 在查詢(xún)窗口中輸入一個(gè)查詢(xún)命令。

　select customer_id from customer where customer_id>6000 2. 在【查詢(xún)】菜單 中選擇【顯示估計(jì)的查詢(xún)計(jì)劃】, , 注意觀(guān)察查詢(xún)窗口下面的執(zhí)行計(jì)劃窗口。

　如圖, 表掃描占100%:

　 執(zhí)行該查詢(xún)( ( 使用工具欄上的“執(zhí)行”按鈕或者【查詢(xún)】菜單上的“執(zhí)行”命令 ),觀(guān)察右側(cè)【屬性】窗口中“返回的行數(shù)”“占用時(shí)間”等關(guān)鍵信息。

　如圖, , 我們可以瞧到返回的行數(shù)為 1 4281 行, , 占用的時(shí)間大約為 2 2 秒多: :

　 3.為 為 Customer 表建立索引。建立 Customer_id 列的非聚集索引, , 如下圖所示。

　輸入命令: :

　create index ID_nonclus

　 on customer(customer_id); 建立非聚集索引: : 在 在 r customer 表中查瞧索引, , 可以瞧到我們已經(jīng)建立好的非聚集索引, , 如圖: :

　 建立好索引后, , 仍使用如下查詢(xún)命令: :

　select customer_id

　from customer where customer_id>6000 在菜單欄中的“查詢(xún)”下點(diǎn)擊“顯示估計(jì)的執(zhí)行計(jì)劃”, , 觀(guān)察新的查詢(xún)計(jì)劃, , 如圖, , 新的執(zhí)行計(jì)劃索引查找占 100%:

　 執(zhí)行該查詢(xún), , 在【屬性】窗口中觀(guān)察查詢(xún)時(shí)間。如圖, , 我們可以瞧到, , 建立好索引再進(jìn)行查詢(xún), , 占用時(shí)間減少到不足 1 1 秒: :

　三、分析查詢(xún)條件對(duì)查詢(xún)執(zhí)行的影 響 1 1 、新建查詢(xún), , 輸入查詢(xún)命令, , 再按上面的步驟, , 觀(guān)察“估計(jì)的查詢(xún)計(jì)劃”與“占用時(shí)間”時(shí)間等信息, , 比較查詢(xún)條件對(duì)查詢(xún)執(zhí)行的影響。

　Q1:

　 select customer_id

　 from customer

　 where customer_id=2621; 初始情況下未建立索引, , 輸入命令后, , 在菜單欄中的“查詢(xún)”項(xiàng)下選擇“顯示估計(jì)的執(zhí)行計(jì)劃”, , 表掃描占 100%:

　 然后點(diǎn)擊執(zhí)行, , 在屬性欄中可以瞧到, ,為 返回的行數(shù)為 1,為 占用的時(shí)間為 7 7 秒多, , 如圖: :

　 然后建立非聚集索引, , 在新建查詢(xún)中輸入上述命令, , 選擇“顯示估 計(jì)的執(zhí)行計(jì)劃”, ,如圖, , 索引查找占 100%:

　 ” 點(diǎn)擊“執(zhí)行”, , 在屬性欄中可以瞧到, , 返回的行數(shù)為 1, 占用的時(shí)間為 2 2 秒多, , 如圖: :

　 再把 where 條件分別改寫(xiě)為: : customer_id> 2621 與

　customer_id<> 2621, 觀(guān)察她們有什么異同。總結(jié)查詢(xún)命令書(shū)寫(xiě)的經(jīng)驗(yàn)。

　Q2:

　 select customer_id

　from customer

　where customer_id>2621; 顯示估計(jì)的執(zhí)行計(jì)劃, , 表掃描占 100%:

　 點(diǎn)擊“執(zhí)行”, , 在屬性 欄中可以瞧到, , 返回的行數(shù)為 0 7650 行, , 占用的時(shí)間為 3 3 秒多, , 如圖: :

　 建立非聚集索引后, , 顯示估計(jì)的執(zhí)行計(jì)劃, , 可以瞧到, , 索引查找占 100%:

　 點(diǎn)擊“執(zhí)行”后, , 在屬性欄中可以瞧到返回的行數(shù)為 0 7650 行, , 占用的時(shí)間為 2 2 秒多, , 如圖: :

　 Q3:

　select customer_id

　from customer

　 where customer_id!=2621; 這里我使用的就是 != 而不就是 <>, 顯示估計(jì)的執(zhí)行計(jì)劃, , 表掃描占 100%, 如圖: :

　 點(diǎn)擊“執(zhí)行”, , 在屬性欄中可以瞧到, , 返 回的行數(shù)為 0 10260 行, , 占用時(shí)間為 3 3 秒多, ,如圖: :

　建立索引后, , 顯示估計(jì)的執(zhí)行計(jì)劃, , 可以瞧到, , 索引掃描占100%:

　點(diǎn)擊“執(zhí)行”, , 屬性欄中可以瞧到, , 返回的行數(shù)為 0 10260 行, , 占用的時(shí)間為 2 2 秒多, ,如圖: :

　可以知道, , 不等于操作符就是永遠(yuǎn)用不到索引的, , 索引只能告訴什么存在于表中, ,而不能告訴什么不存在于表中, , 當(dāng)數(shù)據(jù)庫(kù)遇到“！= = ”, , “ <> ”時(shí), , 會(huì)轉(zhuǎn)而用全表掃描, ,對(duì) 對(duì) 0 a<>0 的條件應(yīng)寫(xiě)為 a<0 or a>0 、

　2. 觀(guān)察下面的查詢(xún)命令: :

　 select full_name,salary

　from employee

　where salary>30000; 在未建立索引的情況顯示估計(jì)的執(zhí)行計(jì)劃, , 表掃描占 100%, 如圖: :

　 返回行數(shù)為 8 8 行, , 時(shí)間大約 3 3 秒多, , 如圖: :

　 在 在 emplyee 立 表建立 salary 列的非聚集索引。再次觀(guān)察上面這個(gè)查詢(xún)命令的查詢(xún)計(jì)劃與執(zhí)行情況。D RID 查找占 87%, 索引查找占 13%, 如圖: :

　 執(zhí)行后, , 返回行數(shù)為 8, 占用時(shí)間為 2 2 秒多, , 如圖: :

　 （1 1 ）

　請(qǐng)寫(xiě)出您對(duì)以上內(nèi)容的分析或得到的經(jīng)驗(yàn)。

　盡量少用不等于查詢(xún)條件

　當(dāng)需要查找的數(shù)據(jù)特別多時(shí), , 使用全表掃描或許比索引掃描還要好

　( ( 2) 試一試 , 您還能得到哪些查詢(xún)命令書(shū)寫(xiě)的經(jīng)驗(yàn) ? ( 不同查詢(xún)語(yǔ)句導(dǎo)致不同查詢(xún)計(jì)劃) )

　 量 當(dāng)插入的數(shù)據(jù)為數(shù)據(jù)表的記錄數(shù)量 10% 以上時(shí), 首先需要?jiǎng)h除該表的索引來(lái)提高數(shù)據(jù)的插入效率, 當(dāng)數(shù)據(jù)全部插入后再建立索引。

　避免在索引列上使用函數(shù)或計(jì)算, 在where 子句中, 如果索引列就是函數(shù)的一部分, 優(yōu)化器將不使用索引而使用全表掃描, 舉例: 低效:select * from table where salary*12>25000 高效:select * from table where salary>25000/12

　索引列上用>= 替代>, 舉例: 高效:select * from table where Deptno>=4 低效:select * from table where Deptno>3 四、分析連接條件對(duì)連接操作的影響 1 1 、對(duì)比下面查詢(xún)的查詢(xún)計(jì)劃與查詢(xún)執(zhí)行情況

　Q41: Select employee、employee_id,full_name,employee、salary,pay_date,

　 salary_paid from employee,salary 顯示估計(jì)的執(zhí)行計(jì)劃, , 如圖, , 嵌套循環(huán) 96%, , 表假脫機(jī) 4%:

　 Q42: select employee、employee_id,full_name,employee、salary,pay_date, salary_paid

　from employee,salary where employee、employee_id=salary、employee_id 顯示估計(jì)的執(zhí)行計(jì)劃, , 哈希匹配 50%, 表掃描各占 41%與 與 9%:

　 點(diǎn)擊“執(zhí)行”, , 返回行數(shù)為 2 21252 行, , 占用時(shí)間 3 3 秒多: :

　 Q43: Select employee、employee_id,full_name,employee、salary,pay_date,salary_paid from employee,salary where employee、employee_id>salary、employee_id 顯示估計(jì)的執(zhí)行計(jì)劃, , 嵌套循環(huán)占 73%, 索引假脫機(jī) 27%:

　 但就是, 點(diǎn)擊“執(zhí)行”, 因?yàn)閿?shù)據(jù)溢出, 無(wú)法完成。

　2.在 在 employee 對(duì) 表上對(duì) employee_id 列建立聚集索引、觀(guān)察查詢(xún)計(jì)劃與執(zhí)行情況的變化、

　create CLUSTERED index ID_clus on employee(employee_id); 如圖: :

　Q41: select employee、employee_id,full_name,employee、salary,pay_date, salary_paid from employee,salary 顯示估計(jì)的執(zhí)行計(jì)劃, , 嵌套循環(huán)占 96%, 表假脫機(jī) 4%:

　 Q42: select employee、employee_id,full_name,employee、salary,pay_date, salary_paid from employee,salary where employee、employee_id=salary、employee_id 顯示估計(jì)的執(zhí)行計(jì)劃, , 哈希匹配 50%, 聚集索引掃描 9%, 表掃描 41%:

　 點(diǎn)擊“執(zhí)行”, , 返回行數(shù)為 2 21252 行, , 占用時(shí)間為 0 0 、0 320 秒: :

　 Q43: select employee、employee_id,full_name,employee、salary,pay_date,salary_paid

　from employee,salary where employee、employee_id>salary、employee_id 顯示估計(jì)的執(zhí)行計(jì)劃, , 嵌套循環(huán) 73%, 索引假脫機(jī) 27%:

　 同樣因?yàn)閿?shù)據(jù)溢出無(wú)法完成執(zhí)行。

　分析以上內(nèi)容, , 總結(jié)您的查詢(xún)優(yōu)化經(jīng)驗(yàn)。

　索引分為聚集索引與非聚集索引兩種。

　聚集索引就就是物理索引, , 也就就是數(shù)據(jù)的物理存儲(chǔ)順序, , 聚集索引的葉子節(jié)點(diǎn)就就是數(shù)據(jù)行本身, , 含有聚集索引的表, , 它的數(shù)據(jù)行的組織方式, , 就是跟聚集索引的順序就是一致的, , 一張表里, , 只能有一個(gè)聚集索引, , 決定著數(shù)據(jù)行的組織方式。

　非聚集索引就是邏輯索引, , 它跟數(shù)據(jù)的組織順序就是毫無(wú)關(guān)系的, , 用一系列指針來(lái)指向數(shù)據(jù)行, , 從而描述數(shù)據(jù)行的位置。

　聚集 索引的最大優(yōu)勢(shì)就就是大范圍數(shù)據(jù)查詢(xún)有著較高的速率, , 能以最快的速度縮小查詢(xún)范圍, , 以最快的速度進(jìn)行字段排序。聚集索引字段選擇優(yōu)先級(jí): : 時(shí)間字段 >> 會(huì)進(jìn)行大范圍查詢(xún)的列 >> 具有唯一值的有實(shí)際意義的字段 >> 自增列ID 。

　1 1 、時(shí)間字段: : 若表里面有時(shí)間列, , 并且時(shí)間就是按照數(shù)據(jù)插入順序增長(zhǎng)時(shí)

　( ( 時(shí)間無(wú)需唯一即可有重復(fù)值, , 哪怕就是大范圍重復(fù) ), 建議采用時(shí)間列作為聚集索引的第一選擇。理由: : 聚集索引有一個(gè)巨大的優(yōu)勢(shì)就就是進(jìn)行大范圍數(shù)據(jù)查找, ,而且這個(gè)優(yōu)勢(shì)會(huì)隨著數(shù)據(jù)量的增加而越來(lái)越明顯, , 一般來(lái)說(shuō)我們需要進(jìn)行大數(shù)據(jù)量范圍查詢(xún)時(shí)都會(huì)用時(shí)間 列圍作為篩選條件, , 由于聚集索引不存在書(shū)簽查找而且可以進(jìn)行連續(xù)掃描, , 因此查詢(xún)速度會(huì)非常快。時(shí)間列數(shù)據(jù)最好就是順序插入的這樣可以盡量減少磁盤(pán)碎片, , 就是數(shù)據(jù)存儲(chǔ)相對(duì)集中, , 便于連續(xù)數(shù)據(jù)讀取。

　2 2 、會(huì)進(jìn)行大范圍查詢(xún)的列: : 若表里面沒(méi)有時(shí)間字段或者時(shí)間字段不適合做聚集索引, , 可以選擇那些在建表時(shí)就明確知道會(huì)經(jīng)常進(jìn)行大范圍數(shù)據(jù)篩選的列, ,而且最好就是選擇性較低的列( ( 即有較多重復(fù)值的列, , 性別這種列不算啦 ), 如有必要可以使用組合索引。理由: : 聚集索引在數(shù)據(jù)查詢(xún)的優(yōu)勢(shì)主要在于范圍數(shù)據(jù)查找, , 把聚集索引弄成唯一的把這個(gè)大好優(yōu)勢(shì)給白白浪費(fèi)了 。

　3 3 、具有唯一值的有實(shí)際意義的字段: :件 若找不到適合條件 1 1 、2 2 的列, , 那還就是乖乖的把聚集索引列建立在唯一列上吧, , 最好找那種有實(shí)際意義的具有唯一性的列, , 比如訂單表可以用訂單號(hào)作聚集索引, , 訂單明細(xì)表使用訂單號(hào)與產(chǎn)品編號(hào)做聯(lián)合聚集索引。理由: : 找不到合適的時(shí)間字段與較低選擇性字段的話(huà), , 把主鍵建成聚集索引就是我們大多情況下的選擇。

　這里建議把唯一性的聚集索引順便建成主鍵, , 與編碼時(shí)方法、變量命名一樣, ,推薦列名自解釋, , 即瞧到列名就知道它就就是主鍵, , 省得您再去猜, , 比如訂單表您來(lái)個(gè)自增 D ID 列做主鍵, , 再建一個(gè) e OrderCode 列 做訂單號(hào), , 用這個(gè)表時(shí)您得懷疑個(gè) 這個(gè) e OrderCode 就是不就是唯一的呢, , 有沒(méi)有建立唯一約束呢, , 同理在訂單明細(xì)表來(lái)個(gè)自增列 D ID 也會(huì)產(chǎn)生如此疑問(wèn), , 產(chǎn)生疑問(wèn)還就是小事, , 若就是您忘記了在應(yīng)該唯一的列上建立約束, , 沒(méi)準(zhǔn)哪天程序控制不好給您個(gè)巨大的驚喜。

　4. 自增列 ID: 前面3 3 中條件都找不到合適的列了還就是使用我們的神器自增列 列 D ID 吧, , 自增列 D ID 也就是我們使用最多的主鍵( ( 順便也就成聚集索引了 ), 而且能較好滿(mǎn)足我們大多數(shù)需求。自增 D ID 列堪稱(chēng)無(wú)所不能t ,int 類(lèi)型只占用 4 4 個(gè)字節(jié)完全滿(mǎn)足窄索引要求, , 絕對(duì)的順序存儲(chǔ)可以有效降低索引碎片, , 完 全符合我們的見(jiàn)表習(xí)慣, , 有用沒(méi)用來(lái)個(gè)自增 D ID 列做主鍵總就是沒(méi)錯(cuò)的。

　與聚集索引不同, , 非聚集索引可以建立多個(gè), , 這也給我們帶來(lái)了很大的靈活, ,畢竟聚集索引就那么一個(gè)不可能靠它滿(mǎn)足所有需求, , 更多的我們得依賴(lài)非聚集索引。但就是, , 建立索引就是有代價(jià)的, , 任何涉及到索引列的數(shù)據(jù)修改都會(huì)導(dǎo)致索引的修改, , 索引越多數(shù)據(jù)的曾、刪、改的額外代價(jià)也就越大。對(duì)于非聚集索引來(lái)說(shuō), , 我們的目標(biāo)就是用盡可能少的索引覆蓋盡可能多的查詢(xún)。

　非聚集索引的列選擇順序( ( 組合索引 ): 經(jīng)常被使用為查詢(xún)條件列 >> 具有較高選擇性的列( ( 選擇性越高越好, , 唯一最好 )>> 經(jīng)常排序的列

　1 1 、經(jīng)常被使用為查詢(xún)條件列: : 我們的查詢(xún)千變?nèi)f化, , 建立索引時(shí)要首先考慮有哪些列被經(jīng)常性的用于各種查詢(xún), , 把使用頻率較高的列作為組合索引的第一列( ( 先導(dǎo)列 ), 若一個(gè)查詢(xún)中沒(méi)有用到組合索引中的先導(dǎo)列, , 多數(shù)情況下這個(gè)索引就不會(huì)被使用, , 因此為了盡可能多的復(fù)用組合索引把使用較多的查詢(xún)列作為組合索引的第一列吧。( ( 關(guān)于這點(diǎn)對(duì)于聚集索引的組合索引同樣適用) )

　2 2 、具有較高選擇性的列: : 這點(diǎn)很簡(jiǎn)單盡量使用高選擇性列作為先導(dǎo)列, , 如果可以通過(guò)第一個(gè)條件過(guò)濾( ( 隨便什么判定邏輯= = 、> > 、< < 、 like), 只要 能大幅減少數(shù)據(jù)范圍, , 就把它作為先導(dǎo)列。

　3 3件 、條件 1 1 、2 2 、3 3 都確定不了時(shí)那就用經(jīng)常被排序的列吧, , 我們的很多操作都需要先進(jìn)行排序才可以進(jìn)行進(jìn)一步查詢(xún), , 比如 e group by,like 等操作都就是要先進(jìn)行排序操作才可以完成下一步查詢(xún)。

　五、視圖的使用 1 1 、執(zhí)行下面的查詢(xún)命令, , 觀(guān)察查詢(xún)計(jì)劃與執(zhí)行情況。

　Q51: select lname,fname,brand_name,product_name from sales_fact_1998,product,customer where customer、customer_id=sales_fact_1998、customer_id and product、product_id=sales_fact_1998、product_id and sales_fact_1998、customer_id=9143 顯示估計(jì)的執(zhí)行計(jì)劃, , 哈希匹配 7%, 表掃描 67%, 嵌套循環(huán) 1%, 表掃描 23%, 表掃描2%:

　 點(diǎn)擊“執(zhí)行”, , 返回的行數(shù)為 7 147 行, , 占用時(shí)間為 2 2 秒多: :

　 2. 建立視圖“ cust_prod_sales ”, ,由 由 product,customer , sales_fact_1998 三個(gè) 表組成, , 其中包含查詢(xún)常用的列( ( 選取的列可以多于查詢(xún) Q51), 再執(zhí)行下面的查詢(xún)。

　建立視圖: :

　create view cust_prod_sales

　as select lname,fname,brand_name,product_name,customer、customer_id from sales_fact_1998,product,customer; 輸入查詢(xún)命令: Q52: select lname,fname,brand_name,product_name from cust_prod_sales where customer_id=9143 顯示估計(jì)的執(zhí)行計(jì)劃, , 嵌套循環(huán) 98%, 行計(jì)數(shù)假脫機(jī) 2%:

　 同樣因?yàn)閿?shù)據(jù)溢出, 無(wú)法完成執(zhí)行。

　請(qǐng)寫(xiě)出您對(duì)以上內(nèi)容的分析與得到的經(jīng)驗(yàn)。

　建立普通的視圖對(duì)查詢(xún)并沒(méi)有太大的作用, 因?yàn)閷?duì)視圖的查詢(xún)最終也要轉(zhuǎn)化為對(duì)基本表的查詢(xún), 視圖的使用只就是可以把表隱藏起來(lái), 但就是, 在視圖上建立索引卻可以加快查詢(xún)速度, 但會(huì)增加開(kāi)銷(xiāo)。

　六、查詢(xún)優(yōu)化測(cè)試 1 1 、數(shù)據(jù)準(zhǔn)備, , 導(dǎo)入 TPCH 數(shù)據(jù)集。數(shù)據(jù)導(dǎo)入方法同前面 Footmark 的導(dǎo)入類(lèi)似。

　立 建立 TPCH 數(shù)據(jù)庫(kù), 如圖:

　右鍵單擊 H TPCH 數(shù)據(jù)庫(kù), , 選擇任務(wù)中的導(dǎo)入數(shù)據(jù)庫(kù): :

　 導(dǎo)入數(shù)據(jù)時(shí), , “數(shù)據(jù)源”選擇“平面文件”, , 通過(guò)瀏覽指定文件夾與文件名( ( 類(lèi)型選擇”所有文件” ), 如圖: :

　 單擊左側(cè)“數(shù)據(jù)源”列表中“列”項(xiàng)目, , 指定”

　列分隔符”為“豎線(xiàn)”, , 單擊”重置列”按鈕, , 觀(guān)察”預(yù)覽行”窗口顯示的數(shù)據(jù)格式就是否正確。如下圖: :

　 如下圖, , 導(dǎo)入 R CUSTOMER 表: :

　 導(dǎo)入成功: 在管理欄中可以瞧到 R CUSTOMER 表的各列名及其屬性: :

　 導(dǎo)入 M LINEITEM 表: :

　 導(dǎo)入成功: :

　 在管理欄中可以瞧到 M LINEITEM 表的各列名及其屬性: :

　 導(dǎo)入 NAN TION 表: :

　 導(dǎo)入成功:

　在管理欄中可以瞧到 N NATION 表的各列名及其屬性: :

　 導(dǎo)入 R ORDER 表: :

　 導(dǎo)入成功:

　 在管理欄中可以瞧到 R ORDER 表的各列名及其屬性: :

　 導(dǎo)入 T PART 表: :

　 導(dǎo)入成功:

　在管理欄中可以瞧到 T PART 表的各列名及其屬性: :

　 導(dǎo)入 P PARTSUPP 表: :

　 導(dǎo)入成功:

　 在管理欄中可以瞧到 P PARTSUPP 表的各列名及其屬性: :

　 導(dǎo)入 N REGION 表: :

　 導(dǎo)入成功:

　在管理欄中可以瞧到 N REGION 表的各列名及其屬性: :

　 導(dǎo)入 R SUPPLIER 表: :

　 導(dǎo)入 成功:

　 在管理欄中可以瞧到 R SUPPLIER 表的各列名及其屬性: :

　 2. 對(duì)以下查詢(xún)進(jìn)行優(yōu)化, , 寫(xiě)出您的優(yōu)化方法、

　實(shí)際執(zhí)行這個(gè)查詢(xún) , 記錄您的執(zhí)行時(shí)間( ( 毫秒) ) 、

　Q1: select l_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_quantity) as avg_qty, avg(l_extendedprice) as avg_price, avg(l_discount) as avg_disc, count(*) as count_order from lineitem where l_shipdate <= "1998-12-01" group by l_returnflag, l_linestatus order by l_returnflag, l_linestatus; 首先在未對(duì)表進(jìn)行任何操作的情況下執(zhí)行,為 返回行數(shù)為 4 行,為 占用時(shí)間為 6 秒多:

　 然后, ,在 在 m lineitem 表的 s l_returnflag,l_linestatus 列上建立非聚集索引: :

　create index lndex_l on lineitem(l_returnflag,l_linestatus); 執(zhí)行查詢(xún), , 返回行數(shù)為4 4 列, , 占用時(shí)間為5 5 秒多: :

　對(duì)這個(gè)查詢(xún), 我嘗試了建立臨時(shí)表, 建立聚集索引的方法,。

　均會(huì)導(dǎo)致總時(shí)間更多。

　Q2:

　select n_name, sum(l_extendedprice * (1 - l_discount)) as revenue from customer,orders,lineitem,supplier,nation,region where c_custkey = o_custkey and l_orderkey = o_orderkey and l_suppkey = s_suppkey and c_nationkey = s_nationkey and s_nationkey = n_nationkey and n_regionkey = r_regionkey and r_name = "ASIA" and o_orderdate >= "1994-01-01" and o_orderdate < "1995-01-01" group by n_name order by revenue desc; 執(zhí)行查詢(xún), , 返回行數(shù)為5 5 行, , 占用時(shí)間為3 3 秒多: :

　 然后在各表上建立索引: create index index_c on customer(c_custkey,c_nationkey); create index index_o on orders(o_custkey,o_orderkey,o_orderdate); create index index_l on lineitem(l_orderkey,l_suppkey); create index index_s on supplier(s_suppkey,s_nationkey); create index index_n on nation(n_nationkey,n_regionkey); create index index_r on region(r_regionkey,r_name);

　執(zhí)行查詢(xún), , 返回行數(shù)為5 5 行, , 占用時(shí)間為1 1 秒多: :

　 Q3: select 100、00 * sum(case when p_type like "PROMO%" then l_extendedprice*(1-l_discount) else 0 end) / sum(l_extendedprice * (1 - l_discount)) as promo_revenue from lineitem,part where l_partkey = p_partkey

　and l_shipdate >= "1995-09-01" and l_shipdate < "1995-10-1" 執(zhí)行查詢(xún), , 返回行數(shù)為1 1 行, , 占用時(shí)間為2 2 秒多: :

　 然后在各表的相應(yīng)列上建立索引: create index index_l On lineitem(l_extendedprice,

　l_discount,

　l_partkey,

　l_shipdate

　) create index index_p On part(p_type,

　p_partkey

　) 執(zhí)行查詢(xún), , 返回行數(shù)為1 1 行, , 占用時(shí)間不到1 1秒: :

　實(shí)驗(yàn)中出 現(xiàn)的問(wèn)題 1. 在導(dǎo)入數(shù)據(jù)表, , 修改列名及屬性時(shí), , 字符串類(lèi)型默認(rèn)為寬度 50, 忘記修改, , 導(dǎo)致數(shù)據(jù)導(dǎo)入失敗

　2. 不知道如何建立臨時(shí)表, , 后經(jīng)過(guò)查詢(xún)得知

　3. 有幾個(gè)查詢(xún)因?yàn)閿?shù)據(jù)溢出導(dǎo)致執(zhí)行無(wú)法完成

推薦訪(fǎng)問(wèn)： 概論 優(yōu)化 實(shí)驗(yàn)