———《數(shù)據(jù)庫(kù)原理與應(yīng)用》課程教學(xué)研究
摘要：優(yōu)化查詢有助于提高SQL的檢索速度，是一種提高應(yīng)用系統(tǒng)性能的重要方式。對(duì)SQL查詢的優(yōu)化方案主要從優(yōu)化原理、視圖、WHERE 子句、存儲(chǔ)過程等，并結(jié)合實(shí)例闡明和驗(yàn)證。對(duì)優(yōu)化查詢的掌握，可使學(xué)生在數(shù)據(jù)庫(kù)應(yīng)用程序設(shè)計(jì)方面的課程中更能得心應(yīng)手。
關(guān)鍵字：優(yōu)化查詢；SQL查詢；關(guān)系數(shù)據(jù)庫(kù)
數(shù)據(jù)庫(kù)查詢操作是數(shù)據(jù)庫(kù)操作的核心操作，對(duì)數(shù)據(jù)的查詢效率及處理速度已成為衡量數(shù)據(jù)庫(kù)性能的一個(gè)重要指標(biāo)。數(shù)據(jù)庫(kù)查詢技術(shù)也是學(xué)生對(duì)《數(shù)據(jù)庫(kù)原理與應(yīng)用》這門課程所必須掌握的知識(shí)和技能，數(shù)據(jù)庫(kù)查詢技術(shù)的精通也為以后課程的學(xué)習(xí)（如：C#.NET程序設(shè)計(jì)）中的數(shù)據(jù)庫(kù)應(yīng)用程序的設(shè)計(jì)打下良好的基礎(chǔ)。為了優(yōu)化數(shù)據(jù)庫(kù)的性能，簡(jiǎn)單、有效的方法就是優(yōu)化查詢。
1優(yōu)化原理和實(shí)質(zhì)數(shù)據(jù)查詢處理的代價(jià)通常取決于對(duì)磁盤的訪問，因?yàn)榇疟P比內(nèi)存的訪問速度要慢。對(duì)于一個(gè)給定的查詢，有許多可能的處理策略，復(fù)雜查詢更是如此[1]。查詢優(yōu)化的實(shí)質(zhì)是在結(jié)果正確的前提下，找出與已有表達(dá)式等價(jià)，但查詢效率更高的表達(dá)式。
2在教學(xué)中要注重的幾種典型的優(yōu)化方法
2.1 建立視圖
視圖可簡(jiǎn)化用戶操作數(shù)據(jù)的方式，使用戶能著重于所感興趣的特定數(shù)據(jù)和所負(fù)責(zé)的特定任務(wù)。如果該視圖數(shù)據(jù)規(guī)模小于定義的基本表，則基于視圖的查詢比原查詢的速會(huì)快得多。例：要查詢每門課程成績(jī)最高的學(xué)生的學(xué)號(hào)SELECT 學(xué)號(hào)，課程號(hào)，成績(jī)FROM 成績(jī)表AWHERE 成績(jī)=（SELECT MAX（成績(jī)）FROM 成績(jī)表BWHERE A..課程號(hào)=B.課程號(hào)）語(yǔ)句缺點(diǎn)：這屬嵌套查詢，子查詢的結(jié)果集將作為外層查詢的條件，查詢效率不高。解決方案：可先建立視圖，求得每門課程的最高分，再與成績(jī)表中比較，獲取與視圖中最高分?jǐn)?shù)據(jù)相等的記錄，這就減少了內(nèi)層連接的數(shù)據(jù)量，從而提高了檢索速度。用SQL語(yǔ)句優(yōu)化的步驟為：
首先，創(chuàng)建視圖GREATE VIEW V_max_cnoscore ASSELECT 課程號(hào)，Max(成績(jī)) AS 最高分?jǐn)?shù)FROM 成績(jī)表GROUP BY 課程號(hào)其次，利用視圖檢索數(shù)據(jù)SELECT 學(xué)號(hào)，A.課程號(hào)，A.成績(jī)FROM 成績(jī)表A， V_max_cnoscore BWHERE A.成績(jī)=B.最高分?jǐn)?shù)AND A.課程號(hào)=B.課程號(hào)
2.2 查詢條件Where 子句的優(yōu)化
2.2.1 合理設(shè)置WHERE 條件的先后順序。多數(shù)數(shù)據(jù)庫(kù)都是按從左到右的順序處理?xiàng)l件，把能過濾更多數(shù)據(jù)的條件放在前面，過濾少的放后面，便可提高檢索速度。例：SELECT * FROM studentWHERE ssex=’女’ -- 條件1過濾的數(shù)據(jù)較少AND sdept=’計(jì)算機(jī)系’ -- 條件2 過濾數(shù)據(jù)比條件1 多上面的SQL就不符合上述原則，可改為：SELECT * FROM studentWHERE sdept=’計(jì)算機(jī)系’AND ssex=’女’對(duì)同一表格進(jìn)行多個(gè)選擇運(yùn)算，選擇條件的排序?qū)π阅苡休^大影響，不僅影響索引的選取，而且關(guān)系到臨時(shí)表的大小[2]。因此，要提高查詢速度，可將較嚴(yán)格的條件寫在前面，較弱的放在后面。
2.2.2 避免使用“< >”或“NOT”操作符。
“< >”或“NOT”均屬于排斥性操作符，不是包括性操作符，這會(huì)使系統(tǒng)在搜索數(shù)據(jù)時(shí)無(wú)法使用索引，只能直接搜索表中的數(shù)據(jù)，例：
（1）SELECT 學(xué)號(hào)FROM 成績(jī)表WHERE 成績(jī)< >80
（2)SELECT 學(xué)號(hào)FROM 成績(jī)表WHERE NOT(成績(jī)<>80)
（3)SELECT 學(xué)號(hào)FROM 成績(jī)表WHERE 成績(jī)< 80 OR成績(jī)>80比較以上三條語(yǔ)句，可知第3 條可使用索引查詢，它的查詢速度最快。
2.2.3恰當(dāng)運(yùn)用“OR 擴(kuò)展”技術(shù)。該技術(shù)是把在Where 子句中帶有OR的查詢轉(zhuǎn)換成包含多個(gè)UNION ALL的查詢。例：查詢計(jì)算機(jī)系縣年齡不大于20 歲的學(xué)生。
常規(guī)的SQL語(yǔ)句：SELECT 學(xué)號(hào)，姓名FROM 學(xué)生表WHERE 系名=’計(jì)算機(jī)系’ OR 年齡<=20假定表中“系名”和“年齡”兩個(gè)字段上創(chuàng)建了索引，以上的查詢可能不會(huì)使查詢優(yōu)化器使用索引，從而查詢效率降低，可改進(jìn)為：SELECT 學(xué)號(hào)，姓名FROM 學(xué)生表WHERE 系名=' 計(jì)算機(jī)系'UNION ALL SELECT 學(xué)號(hào)，姓名FROM 學(xué)生表WHERE 年齡<=20
2.2.4 合理減少LIKE條件的使用。在SQL查詢中，LIKE 條件使用的神奇之處在于其與通配符匹配所提供的模糊查詢功能。但在字符串的比較中，有時(shí)大量字符的逐個(gè)比較，也會(huì)大大降低查找效率。
例：圖書按26 個(gè)英文字母分為26 類，查找所有屬于'A’類圖書的銷售金額。SELECT 圖書類號(hào), SUM (數(shù)量*單價(jià)) FROM 銷售WHERE 圖書類號(hào)LIKE′A%′GROUP BY圖書類號(hào)優(yōu)化為精確查詢：SELECT 圖書類號(hào)，SUM(數(shù)量*單價(jià)) FROM銷售WHERE 圖書類號(hào)>=′A′AND 圖書類號(hào)<′B′GROUP BY圖書類號(hào)
2.3 正確使用子查詢“展平”技術(shù)子查詢“展平”技術(shù)就是指將子查詢轉(zhuǎn)變?yōu)榘脒B接，連接或者反連接，從而將查詢優(yōu)化。例：找出成績(jī)大于等于90 分的學(xué)生的姓名。SELECT 姓名FROM 學(xué)生表WHERE 學(xué)號(hào)IN(SELECT 學(xué)號(hào)FROM 成績(jī)表WHERE 成績(jī)>=90)語(yǔ)句缺點(diǎn)：需要搜索學(xué)生表的每一行，來(lái)查找所有滿足子查詢條件的記錄。
解決方案：將學(xué)生表作為連接的內(nèi)表，查詢將作為通常的連接來(lái)執(zhí)行。
優(yōu)化后的SQL語(yǔ)句：SELECT 姓名FROM (SELECT 學(xué)號(hào)FROM 成績(jī)表WHERE 成績(jī)>=90) A，學(xué)生表B WHERE A. 學(xué)號(hào)=B.學(xué)號(hào)
2.4 善于使用存儲(chǔ)過程
存儲(chǔ)過程是SQL 語(yǔ)句和可選控制流語(yǔ)句的預(yù)編譯集合，一個(gè)名稱存儲(chǔ)并作為一個(gè)單元處理。善于使用存儲(chǔ)過程，將使SQL變得更加靈活和高效。
例：查詢某個(gè)學(xué)生某門課程的考試成績(jī)，列出學(xué)生的姓名、
課程名及成績(jī)CREATE PROC pro_student_degree@stu_name char(12)，@course_name char(18) ASSELECT sname，cname，degreeFROM student A INNER JOIN sc B ON A.sno=B.snoINNER JOIN course C ON C.cno=B.cnoWHERE sname=@stu_name AND cname=@course_name以上是一個(gè)帶有兩個(gè)輸入?yún)?shù)的簡(jiǎn)單存儲(chǔ)過程，只需執(zhí)行一個(gè)簡(jiǎn)單的SQL語(yǔ)句———“EXEC pro_student_degre'e 劉娜’,’數(shù)據(jù)庫(kù)原理’”就可完成查詢。
編寫性能優(yōu)化的SQL 語(yǔ)句是提高數(shù)據(jù)庫(kù)系統(tǒng)的重要因素，隨著數(shù)據(jù)庫(kù)管理系統(tǒng)中數(shù)據(jù)量的增大，如何提高查詢速度與效率也是計(jì)算機(jī)軟件開發(fā)的一個(gè)時(shí)代要求。實(shí)現(xiàn)優(yōu)化查詢的方法很多，在使用中，要根據(jù)具體情況權(quán)衡利弊，使數(shù)據(jù)庫(kù)查詢性能最優(yōu)。
聯(lián)系客服
