[ＳＱＬ查詢(xún)優(yōu)化技術(shù)] 數(shù)據(jù)庫(kù)sql實(shí)驗(yàn) 查詢(xún)優(yōu)化

　　摘要：數(shù)據(jù)查詢(xún)是數(shù)據(jù)庫(kù)應(yīng)用程序的重要組成部分，查詢(xún)語(yǔ)句的執(zhí)行速度已成為數(shù)據(jù)庫(kù)整體性能的瓶頸。本文闡述了影響查詢(xún)效率的因素，并根據(jù)具體情況，給出了優(yōu)化的方法，以提高查詢(xún)語(yǔ)句的執(zhí)行效率。?
　　關(guān)鍵詞：SQL；查詢(xún)優(yōu)化；查詢(xún)語(yǔ)句?
　　中圖分類(lèi)號(hào)：TP311.13 文獻(xiàn)標(biāo)識(shí)碼：A??
　　
　　1 引言?
　　
　　目前，隨著計(jì)算機(jī)技術(shù)的不斷發(fā)展，數(shù)據(jù)庫(kù)技術(shù)應(yīng)用越來(lái)越廣泛，特別是大型數(shù)據(jù)庫(kù)的廣泛使用。而基于數(shù)據(jù)庫(kù)的應(yīng)用程序在執(zhí)行時(shí)，應(yīng)用程序需要在數(shù)據(jù)庫(kù)中查詢(xún)相應(yīng)的信息。SQL（Structured Query Language）語(yǔ)言是關(guān)系數(shù)據(jù)庫(kù)的標(biāo)準(zhǔn)語(yǔ)言，集數(shù)據(jù)操縱、數(shù)據(jù)定義和數(shù)據(jù)控制等功能于一體，數(shù)據(jù)庫(kù)的查詢(xún)又是數(shù)據(jù)庫(kù)的核心操作，也是最常用的操作。但人們?cè)谑褂肧QL語(yǔ)言往往只注意結(jié)果的準(zhǔn)確，忽視了其執(zhí)行的效率，影響了系統(tǒng)的整體性能。據(jù)統(tǒng)計(jì)，90％的性能問(wèn)題主要來(lái)源于程序員或用戶(hù)使用了不恰當(dāng)?shù)腟QL查詢(xún)語(yǔ)句。由此可見(jiàn)，SQL查詢(xún)語(yǔ)句的質(zhì)量對(duì)于數(shù)據(jù)庫(kù)管理信息系統(tǒng)的質(zhì)量有較大的影響，如何保證在結(jié)果正確的條件下，提高查詢(xún)效率成為一個(gè)急需解決的問(wèn)題。?
　　
　　2 查詢(xún)語(yǔ)句效率的影響因素分析?
　　
　　查詢(xún)語(yǔ)句的效率既可以利用邏輯方法進(jìn)行查詢(xún)優(yōu)化，也可以采用物理的方法進(jìn)行查詢(xún)優(yōu)化，但在實(shí)際中，CPU的處理速度比從磁盤(pán)上讀寫(xiě)信息的速度快得多，要進(jìn)行查詢(xún)優(yōu)化，關(guān)鍵是要減少對(duì)磁盤(pán)的訪問(wèn)。RDBMS 優(yōu)化器的輸入是一條查詢(xún)語(yǔ)句，輸出是一個(gè)執(zhí)行策略。執(zhí)行策略是執(zhí)行查詢(xún)所需要的一系列步驟。優(yōu)化器的優(yōu)化算法影響數(shù)據(jù)庫(kù)的反應(yīng)速度，優(yōu)化器的優(yōu)化策略還與所查詢(xún)表的內(nèi)容和其他一些與服務(wù)器有關(guān)的因素有關(guān)，如cache大小、cache策略、I/O 大小等。通常硬盤(pán)訪問(wèn)是成本最高的操作，因此對(duì)查詢(xún)優(yōu)化來(lái)講，最為重要的選擇就是使用什么索引和采用何種表的連接手段。?
　　影響查詢(xún)效率的因素，主要有以下幾點(diǎn)：①索引是否合適；②查詢(xún)條件中的數(shù)據(jù)類(lèi)型是否兼容；③查詢(xún)條件中操作符使用是否得當(dāng)；④多個(gè)選擇條件查詢(xún)時(shí)，選擇條件的次序是否合理；⑤ 是否合理安排連接選擇運(yùn)算．⑥多表查詢(xún)是否合理等。?
　　
　　3 SQL語(yǔ)句的優(yōu)化方法?
　　
　　SQL 查詢(xún)語(yǔ)句執(zhí)行都涉及到存取用戶(hù)或系統(tǒng)對(duì)象的表頁(yè)或索引項(xiàng)。一般而言，系統(tǒng)存取頁(yè)面的速度越快，SQL 語(yǔ)句的性能就越高。因此對(duì)SQL查詢(xún)語(yǔ)句進(jìn)行優(yōu)化，應(yīng)盡量減少頁(yè)面存取，或使用內(nèi)存的頁(yè)面代替訪問(wèn)磁盤(pán)。本文以SQL SERVER 2000為例，給出了進(jìn)行查詢(xún)優(yōu)化的幾種方法。?
　　3.1 選取合適的索引字段?
　　對(duì)于查詢(xún)優(yōu)化來(lái)說(shuō)，提高速度最快的方法是建立索引。在數(shù)據(jù)庫(kù)中建立合適的索引能避免表掃描并減少因查詢(xún)而產(chǎn)生的輸入/輸出開(kāi)銷(xiāo)，使查詢(xún)速度大為提高。?
　　SELECT查詢(xún)中的WHERE語(yǔ)句是查詢(xún)優(yōu)化器處理的重點(diǎn)。WHERE語(yǔ)句中的每一列都是索引的候選列，但對(duì)每一列都建立索引會(huì)形成很多索引，增加系統(tǒng)的開(kāi)銷(xiāo)，故應(yīng)盡量分析出每一個(gè)重要查詢(xún)的使用頻度，將常查詢(xún)的列建立索引。?
　　盡量覆蓋一個(gè)查詢(xún)。例如：如果一個(gè)查詢(xún)只選擇列甲和列乙，把列甲和列乙作為索引鍵建立復(fù)合索引，這個(gè)查詢(xún)就是覆蓋的，這樣被提取的數(shù)據(jù)存放在索引鍵自身中，根本不需要讀取實(shí)際數(shù)據(jù)。因而會(huì)減少輸入/輸出總量，大大提高查詢(xún)速度。?
　　在經(jīng)常要進(jìn)行插入、刪除、修改、更新操作的表上不要建立太多不必要的索引。因?yàn)楸頂?shù)據(jù)修改后，所有的索引都必須做相應(yīng)的調(diào)整，所有的分頁(yè)操作都被記錄在系統(tǒng)日志中。這也會(huì)增加輸入/輸出操作，從而影響數(shù)據(jù)庫(kù)性能。?
　　3.2 避免使用不兼容的數(shù)據(jù)類(lèi)型?
　　在SQL中，float、int、varchar binary和varbinary是不兼容的。數(shù)據(jù)的不兼容使得無(wú)法使用預(yù)先定義好的索引，影響查詢(xún)速度，如語(yǔ)句SELECT NAME FROM CUSTOMERWHERE COST>1000在執(zhí)行時(shí)因COST是MONEY型數(shù)據(jù)，1000是整型，形成數(shù)據(jù)類(lèi)型不兼容，無(wú)法使用建立在COST上的索引，系統(tǒng)在執(zhí)行時(shí)要先將1000轉(zhuǎn)換成MONEY型，再與COST進(jìn)行比較。故應(yīng)當(dāng)在編成程序時(shí)，將1000轉(zhuǎn)化成MONEY型，而不是等到運(yùn)行時(shí)轉(zhuǎn)化。可將其轉(zhuǎn)化成SELECT NAME FROM CUSTOMERWHERE COST>$1000。?
　　3.3 避免對(duì)搜索參數(shù)使用其它操作符（如數(shù)學(xué)，字符串函數(shù)等）?
　　若在WHERE子句中，存在一個(gè)數(shù)學(xué)表達(dá)式，查詢(xún)優(yōu)化器就不能使用分布統(tǒng)計(jì)信息，影響查詢(xún)效率。如查詢(xún)語(yǔ)句SELECT NAME?ID FROMCUSTOMERWHERE COST *5>3000應(yīng)寫(xiě)成SELECT NAME?ID FROMCUSTOMERWHERE COST >600。?
　　例如：SELECT AU?LNAMEFROM AUTHORS WHERE SUBSTRING（STATE，1，1）= ‘A’ ?
　　應(yīng)寫(xiě)成SELECT AU?LNAMEFROM AUTHORSWHERESTATELIKE‘A%’。?
　　3.4 避免使用非聚合表達(dá)式?
　　非聚合表達(dá)式很難利用索引，故在執(zhí)行含有非聚合表達(dá)式的SQL語(yǔ)句時(shí)，必須進(jìn)行大規(guī)模的掃描，降低了執(zhí)行的速度，例如不等關(guān)系符號(hào)“<>”是一個(gè)排斥性的的操作符，故含有“<>”符號(hào)的SQL語(yǔ)句在執(zhí)行時(shí)，在掃描整個(gè)表之前無(wú)法確定子句的選擇范圍，必須掃描整個(gè)表。像“NOT”，“NOT IN”，“NOT LIKE”，“NOT EXIST”等都是非聚合表達(dá)式，應(yīng)避免使用。例如：SELECT SNAME FROM STUDENT WHERE AGE<>20可改寫(xiě)成SELECT SNAME FROM STUDENT WHERE AGE20。?
　　3.5避免通配符（%）在搜尋詞首出現(xiàn)?
　　通配符（%）出現(xiàn)在搜尋詞首，系統(tǒng)不使用索引，會(huì)降低SQL語(yǔ)句的執(zhí)行速度。然而當(dāng)通配符出現(xiàn)在字符串其他位置時(shí)，優(yōu)化器就能利用索引。在下面的查詢(xún)中索引得到了使用：?
　　SELECTAU?LNAMEFROMAUTHORSWHERESTATELIKE‘A%’；?
　　3.6 對(duì)于連接查詢(xún)，適當(dāng)冗余?
　　缺省情況下多于4個(gè)表的連接查詢(xún)，按每次4個(gè)處理。對(duì)每個(gè)表集找到并保存最佳的外表，其余的組合則用來(lái)估計(jì)下一個(gè)次外表。如果在查詢(xún)中應(yīng)當(dāng)提供一些冗余的搜索參數(shù)，使優(yōu)化器有更多的選擇余地，可提高查詢(xún)速度。?
　　如下面兩個(gè)查詢(xún)是相同的?
　　SELECTGRADEFROMST，KCWHEREST.SNO=KC.SNOANDST.NO=‘20060101’?
　　SELECTGRADEFROMST，KCWHEREST.SNO=KC.SNOANDST.NO=‘20060101’ ANDKC.NO=‘20060101’?
　　但后一個(gè)查詢(xún)的速度更快。?
　　3.7 注意選擇條件的排列順序?
　　選擇條件的排列順序不僅能影響索引的選取，還關(guān)系到臨時(shí)表的大小，對(duì)系統(tǒng)的性能有較大的影響。?
　　例如：比較SELECT*FROMAUTHORSWHERE STATE=‘CA’ANDAU?LNAME=‘WHITE’和SELECT*FROMAUTHORSWHEREAU?LNAME=‘WHITE’ ANDSTATE=‘CA’ 的查詢(xún)效率。假設(shè)AUTHORS表中共有20萬(wàn)條記錄，其中滿(mǎn)足STATE=‘CA’的記錄為12萬(wàn)條，滿(mǎn)足AU?LNAME=‘WHITE’的記錄為2萬(wàn)條。在SQL SERVER 2000中查詢(xún)條件的選取是從左至右的。第一條SQL語(yǔ)句在執(zhí)行時(shí)，首先返回一個(gè)12萬(wàn)條記錄的臨時(shí)表，然后在從中進(jìn)行選擇。第二條SQL語(yǔ)句在執(zhí)行時(shí)，首先返回一個(gè)2萬(wàn)條記錄的臨時(shí)表，然后在從中進(jìn)行選擇。顯然，第二條SQL語(yǔ)句的查詢(xún)效率比第一條要高。一般來(lái)說(shuō)，為提高查詢(xún)速度，將條件嚴(yán)格的寫(xiě)在前面。?
　　3.8 將多表的連接運(yùn)算改為嵌套SQL語(yǔ)句來(lái)實(shí)現(xiàn)?
　　連接運(yùn)算會(huì)產(chǎn)生很大的臨時(shí)表，特別是未優(yōu)化的多表連接查詢(xún)。未經(jīng)過(guò)優(yōu)化的SQL查詢(xún)語(yǔ)句會(huì)產(chǎn)生巨大的計(jì)算工作量，可將其改為嵌套SQL語(yǔ)句來(lái)提高查詢(xún)效率。例如：?
　　SELECT STUDENT.SNAME?
　　FROMSTUDENT， SC
　　WHERE STUDENT.SNO = SC.SNO?
　　AND SC.CNO= ‘ 2 ’?
　　可改為?
　　SELECT SNAME FROM STUDENT?
　　WHERE SNO IN （SELECT SNOFROM SC WHERE CNO= ‘ 2 ’）?
　　假設(shè)STUDENT表中有1000條記錄，SC表中有5000條記錄，其中CNO為2的有500條，在第一個(gè)SQL語(yǔ)句中，首先形成1000×5000＝5000000條記錄的臨時(shí)表，再進(jìn)行選擇運(yùn)算。若采用第二個(gè)SQL語(yǔ)句，先對(duì)SC表進(jìn)行選擇運(yùn)算，有500條記錄滿(mǎn)足條件，然后再與STUDENT表進(jìn)行連接運(yùn)算，很顯然，后者的計(jì)算量要比前者少很多，故對(duì)于多個(gè)數(shù)據(jù)表的連接選擇運(yùn)算過(guò)程，先做選擇運(yùn)算后做連接運(yùn)算，比先做連接運(yùn)算后做選擇運(yùn)算計(jì)算量小，查詢(xún)響應(yīng)時(shí)間短，查詢(xún)效率高。?
　　3.9 使用存儲(chǔ)過(guò)程?
　　存儲(chǔ)過(guò)程是編譯好、優(yōu)化過(guò)且存儲(chǔ)在數(shù)據(jù)庫(kù)中的SQL語(yǔ)句和控制流語(yǔ)言的集合，設(shè)計(jì)優(yōu)良的應(yīng)用程序都應(yīng)當(dāng)使用存儲(chǔ)過(guò)程。如果在程序中利用好存儲(chǔ)過(guò)程，可極大地增強(qiáng)SQL語(yǔ)言的效率、功能和靈活性。編譯進(jìn)存儲(chǔ)過(guò)程的SQL語(yǔ)句在執(zhí)行時(shí)可省去大量的處理時(shí)間。存儲(chǔ)過(guò)程在客戶(hù)端執(zhí)行請(qǐng)求使用網(wǎng)絡(luò)的效率往往比將等效的SQL語(yǔ)句發(fā)送到服務(wù)器高。?
　　
　　4 結(jié)束語(yǔ)?
　　
　　分析了查詢(xún)語(yǔ)句效率影響因素的基礎(chǔ)上，提出了具體的優(yōu)化方法。在系統(tǒng)開(kāi)發(fā)和維護(hù)過(guò)程中須針對(duì)具體的情況，綜合運(yùn)用多種優(yōu)化方法，不斷加以調(diào)整，可以提高SQL語(yǔ)句的執(zhí)行效率，取得滿(mǎn)意的運(yùn)行效果。?
　　
　　參考文獻(xiàn)?
　　
　　[1]薩師煊，珊著.庫(kù)系統(tǒng)概論[M].北京：高等教育出版社，2001.?
　　[2]章立民著.SQL SERVER 2000 TRANSACT-SQL 程序設(shè)計(jì)[M].北京：中國(guó)鐵道出版社，2002.?
　　[3]胡百敬著.MICROSOFT SQL SERVER性能調(diào)校[M].北京：電子工業(yè)出版社，2005.

相關(guān)熱詞搜索：優(yōu)化 查詢(xún) 技術(shù) ＳＱＬ查詢(xún)優(yōu)化技術(shù) mysql查詢(xún)優(yōu)化技術(shù) mysql分頁(yè)查詢(xún)優(yōu)化