SQL優(yōu)化技術及應用

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

SQL優(yōu)化技術及應用

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

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

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

1 前言

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

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

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

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

2.1.1 建表

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

例：select車號，日期，凈重from＜稱重表＞where凈重＞60

Select車號，日期，毛重–皮重from＜稱重表＞where毛重–皮重＞60

2.1.2 主鍵的選擇

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

2.1.3 外鍵

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

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

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

2.1.4 索引

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

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

2.1.5 表的分割

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

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

2.2 程序設計階段的優(yōu)化

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

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

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

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

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

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

減少IN、NOT IN操作符的使用，對于能轉換成多表連接的要轉換成多表連接。因為ORACLE遇到IN時會試圖將IN操作符轉換成多個表的連接，用IN操作符將增加轉換時間。

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

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

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

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

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

2.2.3 其它

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

3 結束語

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

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責編趙實鳴）

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