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-），男，汉族，湖北咸宁人，就读于计算机工程学院软件工程专业，本科在读，研究方向：信息系统。