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

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

關鍵詞：SQL索引;SQL語句;SQL語句優化

中圖分類號：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? 引? 言

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

1? SQL索引概述

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

索引的優點如下：

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

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

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

（4）建立索引，可以讓DBMS的查詢優化子系統結合數據字典對SQL查詢語句優化，提高系統的性能。

索引的缺點如下：

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

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

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

2  創建SQL索引的技巧

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

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

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

（3）需要經常查詢的數據列可以建立索引;

（4）需要經常進行BETWEEN…AND操作的數據列可以建立索引;

（5）經常作為條件用在WHERE子句中的數據列可以建立索引;

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

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

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

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

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

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

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

3? SQL語句的優化技巧

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

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

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

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

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

（7）將一個帶輸入變量值的查詢分解為常量查詢。例如select * from t where x>y;其中x是數據列，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? 結? 論

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

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

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

參考文獻：

[1] 高海賓.基于SQL Server數據庫索引的創建與優化分析 [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-），男，漢族，湖北咸寧人，就讀于計算機工程學院軟件工程專業，本科在讀，研究方向：信息系統。