徐新靜（天津天鐵冶金集團(tuán)有限公司計(jì)控電訊廠，河北省涉縣 056404）

SQL優(yōu)化技術(shù)及應(yīng)用

徐新靜（天津天鐵冶金集團(tuán)有限公司計(jì)控電訊廠，河北省涉縣 056404）

隨著信息化進(jìn)程的加快，管理工作的逐步細(xì)化，SQL的優(yōu)化直接影響著系統(tǒng)的運(yùn)行狀態(tài)和速度。根據(jù)軟件設(shè)計(jì)開發(fā)過程中數(shù)據(jù)庫設(shè)計(jì)和程序開發(fā)的技術(shù)和經(jīng)驗(yàn)，通過實(shí)例總結(jié)了數(shù)據(jù)庫訪問SQL優(yōu)化的方法和技巧，及在項(xiàng)目中的應(yīng)用。通過優(yōu)化，提高了系統(tǒng)運(yùn)行速度，減少了系統(tǒng)故障，用戶反饋良好。

數(shù)據(jù)庫 主鍵 外鍵 索引 關(guān)聯(lián) 優(yōu)化

1 前言

隨著信息化技術(shù)在天鐵的逐步推廣應(yīng)用，信息化已經(jīng)覆蓋了包括進(jìn)出廠物資的計(jì)量、設(shè)備材料及備品備件的采購發(fā)放、生產(chǎn)過程的控制、產(chǎn)成品的銷售等產(chǎn)、供、銷的各個(gè)環(huán)節(jié)。隨著管理的進(jìn)一步細(xì)化，數(shù)據(jù)量逐步增加，要快速有效地訪問這些數(shù)據(jù)，對(duì)這些數(shù)據(jù)進(jìn)行綜合的分析，并且做到并發(fā)共享使用這些數(shù)據(jù)，就要想方設(shè)法優(yōu)化數(shù)據(jù)結(jié)構(gòu)和訪問的SQL語句，提高其運(yùn)行效率。

2 SQL優(yōu)化方法及應(yīng)用

拋卻數(shù)據(jù)庫系統(tǒng)的因素來優(yōu)化SQL，不外乎從數(shù)據(jù)庫設(shè)計(jì)和程序?qū)崿F(xiàn)兩個(gè)方面進(jìn)行優(yōu)化。在工作中同樣的數(shù)據(jù)庫服務(wù)器，由于不同的設(shè)計(jì)理念造成了不同的數(shù)據(jù)庫設(shè)計(jì)、不同的訪問速度，結(jié)果是大相徑庭。怎樣提高數(shù)據(jù)訪問速度對(duì)一個(gè)系統(tǒng)程序員來說至關(guān)重要，也是一個(gè)應(yīng)用系統(tǒng)成敗的關(guān)鍵。

2.1 數(shù)據(jù)庫設(shè)計(jì)階段的優(yōu)化

2.1.1 建表

建表時(shí)要基本滿足三個(gè)范式。即：表的記錄要求有惟一標(biāo)識(shí)，以確定實(shí)體的惟一性；表的字段要求屬性具有原子性，不可再分解；對(duì)字段冗余的約束，即表里的每一個(gè)字段都不能由別的字段衍生和計(jì)算得出。但在實(shí)際工作中可根據(jù)實(shí)際情況設(shè)置字段冗余，以達(dá)到空間換時(shí)間的效果。如稱重表中有毛重、皮重、凈重三個(gè)字段，凈重可由毛重減去皮重獲得，在大多數(shù)情況下我們關(guān)心的是進(jìn)出廠貨物的凈重，如果每次查詢、數(shù)據(jù)分析都由計(jì)算得出凈重，必然耗費(fèi)時(shí)間，所以增加了凈重這個(gè)冗余字段以提高速度，同時(shí)也降低了SQL語句的復(fù)雜度。

例：select車號(hào)，日期，凈重from＜稱重表＞where凈重＞60

Select車號(hào)，日期，毛重–皮重from＜稱重表＞where毛重–皮重＞60

2.1.2 主鍵的選擇

一個(gè)表只有一個(gè)主鍵，主鍵能唯一確定一個(gè)實(shí)體，其他字段就是主鍵所確定實(shí)體的一些屬性、特點(diǎn)，它們之間沒有相互依賴關(guān)系。主鍵將頻繁地用于增、刪、改、查詢等SQL語句，主鍵的選擇至關(guān)重要。主鍵一般以常用的列作為主鍵，主鍵盡可能短；對(duì)于復(fù)合主鍵，需要幾個(gè)字段聯(lián)合做主鍵時(shí)，主鍵列要盡可能地少，實(shí)在不行就人為增加一單一的主鍵列，因?yàn)橹麈I的長短將影響索引的大小和外建表的大小，導(dǎo)致增加磁盤的I/O和表關(guān)聯(lián)的難度。

2.1.3 外鍵

外鍵的作用是建立和加強(qiáng)兩個(gè)表數(shù)據(jù)之間的鏈接，即關(guān)系型數(shù)據(jù)庫中的表間關(guān)系,它實(shí)現(xiàn)了數(shù)據(jù)庫表間的參照完整性約束，外鍵約束不僅控制存儲(chǔ)在外鍵表中的數(shù)據(jù)，還可以控制對(duì)主鍵表中數(shù)據(jù)的修改。如果不通過外鍵建立這種表間關(guān)系，那么在表上進(jìn)行記錄的增刪改時(shí)就會(huì)使數(shù)據(jù)的完整性遭到破壞。

螺紋緊固件是將汽車的部件連接起來的裝置，是汽車維修技術(shù)人員接觸最多的零件，現(xiàn)代汽車中使用了數(shù)百種緊固件。常見的緊固件如圖1所示，擰緊螺栓時(shí)在螺栓上引起的力如圖2所示，作用在被緊固件上的力為夾緊力。常用螺紋標(biāo)準(zhǔn)是米(公)制螺紋，少部分從美國、英國來的進(jìn)口車可能用寸制螺紋(UNC，UNF)，此外車上部分傳感器和螺塞還使用圓柱管螺紋和圓錐管螺紋。

例：銷售系統(tǒng)訂單表中有訂單號(hào)、訂貨品種、訂貨量、訂貨用戶編號(hào)……客戶表中有訂貨用戶編號(hào)、地址、稅號(hào)、銀行賬號(hào)、聯(lián)系方式……在客戶表中訂貨用戶編號(hào)是主鍵，而在訂單表中訂貨用戶編號(hào)是外鍵。如果刪除客戶表中的某個(gè)客戶，必須保證訂單表中沒有該客戶的訂單，否則破壞了數(shù)據(jù)的完整性，找不到該訂單的客戶信息，系統(tǒng)無法進(jìn)行下一步的發(fā)貨、結(jié)算等一系列的工作。

2.1.4 索引

索引就像字典的目錄，利用索引能提高數(shù)據(jù)訪問的速度就不難理解了。建立索引的目的是加快對(duì)表中記錄的查找或排序。但是為表設(shè)置索引是要付出代價(jià)的，它不但增加了數(shù)據(jù)庫的存儲(chǔ)空間，而且在插入和修改數(shù)據(jù)時(shí)要花費(fèi)時(shí)間來維護(hù)索引，所以并不是索引越多越好，弄不好會(huì)事倍功半。這里建議在經(jīng)常需要用于where子句條件、排序、分組以及多表關(guān)聯(lián)的列上建立索引。而對(duì)于查詢幾率小、數(shù)據(jù)值少（性別取值只有男女兩個(gè)）、字段數(shù)據(jù)量大（text、image等數(shù)據(jù)類型）的列則不適合建立索引。

在使用索引時(shí)要注意查詢條件列的順序和索引的關(guān)系。聚合索引條件放在前面，其次是非聚合索引，最后是普通字段條件，條件語句中用到復(fù)合索引中的起始字段才能顯著提高速度，如果僅用復(fù)合索引的非起始列則沒什么效果。

2.1.5 表的分割

表的字段數(shù)目過多或者根據(jù)實(shí)際情況預(yù)測表的記錄數(shù)非常大，那么要考慮表的分割。對(duì)于前者可按照字段使用的頻度進(jìn)行垂直分割，后者按時(shí)間或記錄的完整與否等規(guī)則實(shí)施水平分割。

公司產(chǎn)銷系統(tǒng)在運(yùn)行半年以后，一些履歷表中的記錄數(shù)已達(dá)到相當(dāng)?shù)囊?guī)模，有數(shù)百萬條之多，而且還會(huì)逐日增加，如果有多用戶并發(fā)操作，同時(shí)進(jìn)行增、改、查詢等操作，這時(shí)就出現(xiàn)速度超慢或干脆服務(wù)吊死現(xiàn)象。為了解決這個(gè)問題，對(duì)履歷表按照一定規(guī)則進(jìn)行了水平分割，運(yùn)行表只保留最近N天的數(shù)據(jù)，而把N天以前的數(shù)據(jù)放到歷史表中，一般情況下只對(duì)運(yùn)行表進(jìn)行各種操作，速度會(huì)有很大提高，如果需要查詢歷史數(shù)據(jù)時(shí)從歷史表中查詢即可，不會(huì)影響運(yùn)行表而影響現(xiàn)場工作。

2.2 程序設(shè)計(jì)階段的優(yōu)化

我們?cè)谑褂肧QL語句時(shí)往往關(guān)注于結(jié)果的正確與否，而忽略了其速度和成本。在一些小的應(yīng)用系統(tǒng)中，由于數(shù)據(jù)量小、數(shù)據(jù)訪問不是很頻繁、并發(fā)用戶少等原因，SQL語句的優(yōu)劣基本體現(xiàn)不出來，而對(duì)于數(shù)據(jù)量大、數(shù)據(jù)關(guān)系復(fù)雜的數(shù)據(jù)庫環(huán)境，優(yōu)劣立現(xiàn)，尤其是Where子句優(yōu)化，它的優(yōu)化在很大程度上就是盡量減少全表掃描。我在工作實(shí)踐中總結(jié)出來，按照下面的一些規(guī)則對(duì)SQL進(jìn)行適當(dāng)?shù)膬?yōu)化后，其運(yùn)行速度有了明顯地提高。

在select語句中一定要明確選取的字段，不要把沒用的字段列入字段表，更不能用“select*from表名”這樣的語句。

在公司產(chǎn)銷系統(tǒng)中，有幾個(gè)畫面總提示數(shù)據(jù)字段總數(shù)超出界定范圍，但實(shí)際并沒有超出，經(jīng)過分析SQL語句發(fā)現(xiàn)，這些畫面所涉及的表其字段多達(dá)100多個(gè)，有的甚至上200個(gè)，而畫面所取的字段不過一二十個(gè)，這時(shí)如果用select*from表名where＜條件＞這樣不但浪費(fèi)了大量的I/O資源、服務(wù)器資源、數(shù)據(jù)查詢的速度也大打折扣，并且因字段太多而前臺(tái)畫面報(bào)錯(cuò)。

對(duì)于同一SQL語句，其字段列表、where子句條件盡量按規(guī)范和順序填寫。字段列要寫在運(yùn)算符的左邊，值寫運(yùn)算符右邊，如：where年齡＞22和where 22＜年齡兩個(gè)語句含義和結(jié)果一樣，執(zhí)行效率卻不一樣，如果年齡字段有索引的話，后者因不符合規(guī)范，不但增加了語句分析的次數(shù)，還放棄索引進(jìn)行全表掃描，降低了執(zhí)行效率。另外，同樣的語句按規(guī)范和順序書寫，第一次用這個(gè)語句時(shí)可能耗時(shí)長，但再次調(diào)用該語句時(shí)速度大大加快，否則增加了數(shù)據(jù)庫系統(tǒng)分析語句所需的時(shí)間和服務(wù)器資源占用。例如：在一個(gè)有900多萬條的一個(gè)Oracle數(shù)據(jù)表里運(yùn)行“select字段列表from表名where條件1 and條件2”查詢語句，第一次運(yùn)行耗時(shí)10.687 s，第二次運(yùn)行時(shí)僅用2.218 s。

通過case、decode等一些函數(shù)減少對(duì)數(shù)據(jù)庫訪問的次數(shù)。例在雇員表employee中有雇員編號(hào)bh,出生日期birth兩個(gè)字段，要求統(tǒng)計(jì)各個(gè)年齡段的人數(shù)。

2.2.2 操作符的優(yōu)化

減少IN、NOT IN操作符的使用，對(duì)于能轉(zhuǎn)換成多表連接的要轉(zhuǎn)換成多表連接。因?yàn)镺RACLE遇到IN時(shí)會(huì)試圖將IN操作符轉(zhuǎn)換成多個(gè)表的連接，用IN操作符將增加轉(zhuǎn)換時(shí)間。

避免對(duì)索引列NULL值進(jìn)行判斷，對(duì)NULL值的判斷將產(chǎn)生全表掃描，不如在數(shù)據(jù)庫設(shè)計(jì)時(shí)給字段設(shè)置默認(rèn)值，沒有默認(rèn)值的盡量用其它更優(yōu)的等價(jià)語句替代。

“不等于”操作符不用索引，它的處理將會(huì)產(chǎn)生全表掃描，對(duì)于已建立索引的列建議將“＜＞”或“！=”操作符改用其他操作符替代。

例如：字段名a為已建立索引列，建議將a＜＞0改為(a＜0 or a＞0)

避免在操作符的左邊對(duì)索引列進(jìn)行各種運(yùn)算，那樣系統(tǒng)將放棄索引而進(jìn)行全表掃描。

盡量使用UNION ALL操作符，而不是UNION操作符。

2.2.3 其它

要盡量避免大的事務(wù)，尤其不能在事務(wù)中間與用戶交互。大的事務(wù)占用資源多，耗時(shí)長，還常常是獨(dú)占資源狀態(tài)，這樣影響其他事務(wù)的響應(yīng)速度，如果事務(wù)中間有與用戶交互信息，這時(shí)如果用戶不操作，事務(wù)將一直占用資源到用戶操作為止，容易發(fā)生死鎖。

3 結(jié)束語

在項(xiàng)目設(shè)計(jì)和后續(xù)的維護(hù)過程中，通過對(duì)這些優(yōu)化技術(shù)和方法的合理運(yùn)用，提高了系統(tǒng)運(yùn)行速度，減少了系統(tǒng)故障，用戶反饋良好。尤其是公司銷售系統(tǒng)，在初期運(yùn)行階段經(jīng)常報(bào)數(shù)據(jù)溢出或查詢吊死錯(cuò)誤，通過對(duì)選取字段的控制和大數(shù)據(jù)量表的水平分割，問題得到解決，系統(tǒng)運(yùn)行兩年來沒有再出現(xiàn)類似問題，查詢的平均響應(yīng)時(shí)間也縮短了30%左右。另外SQL的優(yōu)化，各種數(shù)據(jù)庫之間都是互通的，但也不僅相同，有些還與所采用的優(yōu)化器的不同而需區(qū)別對(duì)待，在工作中還要根據(jù)實(shí)際情況來具體對(duì)待。

SQL Optimizing Technology and Application

Xu Xinjing

With informatization progress acceleration and management gradual specifying,SQL optimization directly affects the running state and speed of the system.Basing on the technology and experience of database design and program development,the author summarizes the method and techniques of database access SQL optimization and describes its application in projects by example.After optimization,the system ran with higher speed and less failure and

good response from the customer.

database,main key,external key,index,connection,optimization

（收稿 2010－12－10責(zé)編趙實(shí)鳴）

徐新靜，女，1996年畢業(yè)于河北科技大學(xué)計(jì)算機(jī)及應(yīng)用專業(yè)，高級(jí)工程師?，F(xiàn)在天鐵集團(tuán)計(jì)控電訊廠計(jì)算機(jī)中心從事軟件開發(fā)工作。