SQL索引及SQL語句的應用技巧分析

摘? 要：隨著信息技術應用的日益廣泛，數(shù)據(jù)量越來越大，數(shù)據(jù)的查詢效率顯著影響信息系統(tǒng)的性能。文章在分析SQL語句執(zhí)行過程的基礎上，對SQL索引的建立技巧及SQL語句的優(yōu)化技巧進行了探索，為SQL索引和語句優(yōu)化提供了一定的參考技巧。

關鍵詞：SQL索引;SQL語句;SQL語句優(yōu)化

中圖分類號：TP311.13? TP316.8? ? 文獻標識碼：A 文章編號：2096-4706（2019）19-0026-02

Abstract：With the increasing application of information technology，the data volume is increasing，and the query efficiency of data has a significant impact on the performance of information systems. On the basis of analyzing the execution process of SQL statement，this paper explores the techniques of establishing SQL index and optimizing SQL statement，and provides some reference techniques for SQL index and SQL statement optimization.

Keywords：SQL index;SQL statement;SQL statement optimization

0? 引? 言

信息技術中，數(shù)據(jù)的存儲與使用占據(jù)著至關重要的地位，大數(shù)據(jù)和人工智能時代尤其如此。面對如此大量的數(shù)據(jù)，查找數(shù)據(jù)的速度對提高系統(tǒng)的性能有著重要的作用。為了提高查詢效率，DBMS（數(shù)據(jù)庫管理系統(tǒng)）采用數(shù)據(jù)字典存儲了數(shù)據(jù)的元信息，同時設計了查詢分析子系統(tǒng)來優(yōu)化查詢操作。但這些不足以在大數(shù)據(jù)量的情況下提升改善查詢效率，必須寫出高效的SQL語句，同時創(chuàng)建合適的索引來提高查詢性能。SQL索引技術在數(shù)據(jù)庫的查詢優(yōu)化中效果顯著，好的索引設計甚至能上千倍地提高查詢效率。文獻[1]、[2]對SQL Server數(shù)據(jù)庫中的索引效率進行了分析，通過案例指出了索引建立時要考慮的基本因素;文獻[3]對非聚集索引的空間大小進行了估算，為索引的建立提供了相應的參考;文獻[4]以Oracle數(shù)據(jù)庫為例討論了索引技術的使用技巧。本文通過對SQL查詢的執(zhí)行過程分析，探討了SQL語句編寫和SQL索引創(chuàng)建的技巧。

1? SQL索引概述

SQL索引是數(shù)據(jù)庫中特殊的表，是對數(shù)據(jù)庫中某一列或多列的一個排序，目的是加快數(shù)據(jù)庫的查詢速度。從物理存儲上看，索引分為聚集索引和非聚集索引。聚集索引的數(shù)據(jù)排序和物理磁盤上的數(shù)據(jù)存儲完全一致，可以極大地提高磁盤操作效率。非聚集索引又分為主鍵索引、唯一性索引和普通索引。按照索引包含的字段數(shù)可分為單個索引和復合索引。

索引的優(yōu)點如下：

（1）唯一性索引，可以保證某列數(shù)據(jù)不重復，可以提高查詢效率;

（2）將建立索引的數(shù)據(jù)列為條件查詢可以加快數(shù)據(jù)的檢索速度;

（3）連接字段上建立索引可以加快表之間的連接;

（4）建立索引，可以讓DBMS的查詢優(yōu)化子系統(tǒng)結合數(shù)據(jù)字典對SQL查詢語句優(yōu)化，提高系統(tǒng)的性能。

索引的缺點如下：

（1）空間代價：創(chuàng)建索引需要占物理空間，索引建立得越多，所需的額外物理空間越大;隨著數(shù)據(jù)量的增加，索引占用的空間也會隨之增加;

（2）時間代價：索引必須和基表數(shù)據(jù)實時對應，因此當對表中數(shù)據(jù)進行更新時，需要占用系統(tǒng)時間對索引進行動態(tài)維護，降低了數(shù)據(jù)更新的整體速度。

索引可以提高檢索性能，其原因是建立了索引表對相關列進行排序，降低了掃描的數(shù)據(jù)量。但隨著索引的增加，其維護代價也隨之增加。因此需要在提高檢索性能和降低維護代價之間進行平衡。

2  創(chuàng)建SQL索引的技巧

通過對索引原理的探討，索引的建立大致有以下原則：

（1）主鍵用來保證實體完整性，在更新數(shù)據(jù)時需要用主鍵作為條件掃描數(shù)據(jù)表，因此主鍵數(shù)據(jù)列要建立索引。一般可以在主鍵上建立聚集索引;

（2）外鍵用來保證參照完整性，并且經(jīng)常作為表的連接條件對表進行合并，因此外鍵上要建立索引。在數(shù)據(jù)量較大的情況下還可以減少磁盤IO（輸入輸出）次數(shù)，對提高表連接效率效果顯著;

（3）需要經(jīng)常查詢的數(shù)據(jù)列可以建立索引;

（4）需要經(jīng)常進行BETWEEN…AND操作的數(shù)據(jù)列可以建立索引;

（5）經(jīng)常作為條件用在WHERE子句中的數(shù)據(jù)列可以建立索引;

（6）經(jīng)常作為排序和分組依據(jù)的數(shù)據(jù)列可以建立索引;如果這些排序和分組所依據(jù)的數(shù)據(jù)列不止一個，可以在這些列上建立復合索引;

（7）經(jīng)常計算最大值或最小值的數(shù)據(jù)列可以建立索引，這樣基本不用掃描基表，在索引表中就能查到結果;

（8）查詢中很少涉及到的數(shù)據(jù)列不要建立索引;

（9）重復值比較多的數(shù)據(jù)列排序后對提高檢索效率的作用不明顯，不要建立索引;

（10）對于經(jīng)常更新的數(shù)據(jù)列盡量不建立索引，因為更新數(shù)據(jù)的同時需要同步更新索引表，增加索引維護代價;

（11）對于定義為text、image和bit的數(shù)據(jù)列不要建立索引;

（12）在多字段上建立復合索引，應當按照字段在查詢條件中出現(xiàn)的頻度建立索引。因為復合索引中，記錄是按照復合索引的字段先后順序作為關鍵字進行排序，先按照第一關鍵字排序，該字段值相同時再按照第二關鍵字排序，以此類推。因此按照查詢條件中出現(xiàn)的頻度建立復合索引，才能最大限度地發(fā)揮索引的作用。對于復合索引，應當根據(jù)具體應用仔細考慮，如果復合索引的字段經(jīng)常以AND方式作為條件使用，并且單個字段極少作為條件，可以建立復合索引，否則應該考慮單一索引。

3? SQL語句的優(yōu)化技巧

當數(shù)據(jù)量比較大時，DBMS查詢優(yōu)化子系統(tǒng)會根據(jù)SQL查詢語句及數(shù)據(jù)字典信息來確定是否用索引以達到提高查詢效率的目的，但是不應該過分依賴查詢優(yōu)化子系統(tǒng)，如果SQL語句寫得不合適，則會導致系統(tǒng)無法使用索引，嚴重影響查詢效率。在實際應用中，通過分析系統(tǒng)對索引的使用方式，總結出以下SQL語句的優(yōu)化技巧。

（1）避免對建立索引的列進行表達式計算，否則可能無法使用索引。例如下面兩條SQL語句：

select * from t where price/3>5;

select * from t where price>15;

對第一個SQL語句，price/3的值只能在掃描的過程中才能得到其值，因此不能使用索引，導致只能進行全表掃描。

（2）縮小查詢的范圍，減少掃描范圍。例如語句select * from t where x>5;如果知道x列的上限為100，則可以加上范圍select * from t where x>5 and x<100;

（3）盡量避免使用in運算和or運算。使用這兩種運算，系統(tǒng)會進行全表掃描，使索引失效。在集合中的值不多的情況下可以分拆語句，然后合并結果。例如select * from t where x in（2，3）;可以分拆為select * from t where x=2;和select * from t where x=3;然后將兩個查詢結果合并，效率會提高很多;

（4）盡量避免用<>運算，該運算也會導致全表掃描，可以轉化為<和>兩種運算，然后合并結果;如果數(shù)據(jù)列上是枚舉值并且不重復的值個數(shù)比較少，可以轉化為in運算符，然后再根據(jù)第三點進行轉化。例如select * from t where x<> 3;假設x列上只有1、2、3三種枚舉值，可以轉化為select * from t where x in（1，2）;

（5）盡量避免使用like運算。如果是精確匹配，則轉化為=運算，這樣可以使用索引提高效率;如果是模糊匹配，則最好寫成前端匹配。例如模式”%mn%”的匹配效率比”mn%”的匹配效率要低;

（6）避免使用null值判斷。實際應用中應根據(jù)具體語義轉化成其它等價的判斷形式;

（7）將一個帶輸入變量值的查詢分解為常量查詢。例如select * from t where x>y;其中x是數(shù)據(jù)列，y是外來輸入變量。如果y的值是固定的某幾個值，比如1、2、3，則可以分解為三個查詢，并用if結構進行分支：

select * from t where x>1;

select * from t where x>2;

select * from t where x>3;

4? 結? 論

以上技巧通過實際數(shù)據(jù)驗證，大部分都能很好地提高查詢效率，通過查看SQL執(zhí)行計劃，在SQL語句優(yōu)化后，可以看到系統(tǒng)使用索引的次數(shù)明顯增加。

對于索引的建立，并不需要一味地遵循以上原則。有些情況可能非常符合以上原則，但全表掃描可能比使用索引更好。如果數(shù)據(jù)量比較大，全表掃描可能會導致更多的磁盤IO，但是如果并行化程度比較高，則可能使用全表掃描反而執(zhí)行得更快。另外如果需要查詢的記錄數(shù)超過了數(shù)據(jù)總數(shù)的40%，則使用全表掃描可能更快一些。

以上SQL語句的優(yōu)化方式完全可以相互結合使用。其實SQL語句的優(yōu)化是一個復雜多變的綜合過程，其影響因素也很多，比如數(shù)據(jù)量的多少、表結構的變化、業(yè)務邏輯的變更等等，這些因素相互影響，各種因素之間既可能一致也可能矛盾，當發(fā)生矛盾時，需要根據(jù)實際情況進行具體分析，不存在固定模式。同時，優(yōu)化也不是一成不變的，必須隨著應用的不同進行相應的調整。

參考文獻：

[1] 高海賓.基于SQL Server數(shù)據(jù)庫索引的創(chuàng)建與優(yōu)化分析 [J].九江學院學報（自然科學版），2017，32（2）：74-76.

[2] 王桃群.SQL Server索引效率分析 [J].電腦知識與技術，2017，13（26）：11-12+19.

[3] 岳莉.在SQL Server中估算非聚集索引的大小 [J].教育教學論壇，2018（16）：268-270.

[4] 王宇博.SQL語言中的索引技術的使用技巧 [J].智庫時代，2019（16）：264+268.

作者簡介：岳彬森（1998-），男，漢族，湖北咸寧人，就讀于計算機工程學院軟件工程專業(yè)，本科在讀，研究方向：信息系統(tǒng)。