结合索引优化SQL语句提高数据库查询效率

 

数据库系统是管理信息系统的核心，基于数据库的联机事务处理（OLTP）和联机分析处理(OLAP)是各使用单位最为重要的计算机应用之一。从大多数系统的应用实例来看，查询、分析、统计是系统的最终应用，而查询、分析、统计操作所基于的SELECT语句在SQL语句中又是付出资源代价最大的语句。举个具体的例子，比如一个数据库表有上百万甚至上千万条记录，全表扫描一次往往需要数十分钟，甚至数小时。如果采用比全表扫描更好的查询策略，往往可以使查询时间降为几分钟甚至几秒钟，由此可见查询优化技术的重要性。本文以应用实例为基础，结合数据库操作（以MS SQL Server为例），介绍优化查询技术在现实系统中的运用。

1 大型数据库查询的工作原理

    一个好的查询计划往往可以使程序性能提高数十倍。查询计划是用户所提交的SQL语句的集合，查询规划是经过优化处理之后所产生的语句集合。DBMS（数据库管理系统）处理查询计划的过程是这样的：在做完查询语句的词法、语法检查之后，将语句提交给DBMS的查询优化器，优化器做完代数优化和存取路径的优化之后，由预编译模块对语句进行处理并生成查询规划，然后提交给系统处理执行，最后将执行结果返回给用户。在实际的数据库产品(如MS SQLServer)的高版本中都是采用“基于语法的查询优化器”和“基于开销的查询优化器”。“基于语法的查询优化器”为获得对 SQL 查询的应答结果创建一个过程计划，但是它选择的特定计划取决于查询的确切语法及查询中的子句顺序。无论数据库中记录的数目或组合是否随时间变化而更改，基于语法的查询优化器每次都执行同样的计划。与基于开销的查询优化器不同，它不查看或维护数据库的统计记录。“基于开销的查询优化器”在备选计划中选择应答 SQL 查询的计划。选择是基于对执行特殊计划的开销估算（I/O 操作数、CPU 秒数，等等）而作出的。它通过记录表或索引中记录的数目和构成的统计数字估算这些开销。与基于语法的查询优化器不同，它不依赖于查询的确切语法或查询中的子句顺序。虽然现在的数据库产品在查询优化方面已经做得越来越好，但由用户提交的SQL语句是系统优化的基础，很难设想一个原本糟糕的查询语句经过系统的优化之后会变得高效，因此用户所写语句的优劣至关重要。“基于开销的查询优化器”的优化方法我们暂不讨论，下面重点说明“基于语法的查询优化器”的解决方案。

2 合理建立索引提高查询效率

    索引是数据库中重要的数据结构，它的根本目的就是为了提高查询效率。采用索引来加快数据处理速度也成为广大数据库用户一致接受的优化方法。索引的使用要恰到好处，其使用原则如下：  

2.1  在经常进行连接，但是没有指定为外键的列上建立索引。  

2.2  在频繁进行排序或分组（即进行group by或order by操作）的列上建立索引。  

2.3  在条件表达式中经常用到的不同值较多的列上建立索引，在不同值少的列上不要建立索引。比如在雇员表的“性别”列上只有“男”与“女”两个不同值，因此就没有必要建立索引。如果建立索引不但不会提高查询效率，反而会严重降低更新速度。  

2.4  如果待排序的列有多个，可以在这些列上建立复合索引（compound index）。  

2.5  不能用null作索引，任何包含null值的列都将不会被包含在索引中。也就是说如果某列存在空值，即使对该列建索引也不会提高性能。

2.6  对查询型的表，建立多个索引会大大提高查询速度，对更新型的表，如果索引过多，会增大开销。

在实际应用中可以使用系统工具帮助分析建立索引。如MS SQL Server的查询分析器。

3  避免或简化排序(order by)  

应当简化或避免对大型表进行重复的排序。当能够利用索引自动以适当的次序产生输出时，优化器就避免了排序的步骤。以下是一些影响因素：  

3.1  索引中不包括一个或几个待排序的列；  

3.2  group by或order by子句中列的次序与索引的次序不一样；  

3.3  排序的列来自不同的表。  

为了避免不必要的排序，就要正确地增建索引，合理地合并数据库表（尽管有时可能影响表的规范化，但相对于效率的提高是值得的）。如果排序不可避免，那么应当试图简化它，如缩小排序的列的范围等。  

4  使用连接避免对数据表的顺序存取  

在嵌套查询中，对表的顺序存取可能会对查询效率产生致命的影响。比如采用顺序存取策略，一个嵌套3层的查询，如果每层都查询1000行，那么这个查询就要查询10亿行数据。避免这种情况的主要方法就是对连接的列进行索引。例如，两个表：学生表（学号、姓名、年龄……）和选课表（学号、课程号、成绩）。如果两个表要做连接，就要在“学号”这个连接字段上建立索引。还可以使用并集来避免顺序存取。尽管在所有的检查列上都有索引，但某些形式的where子句强迫优化器使用顺序存取。下面的查询将强迫对table1表执行顺序操作：  

SELECT * FROM table1 WHERE (user_num=104 AND user_id>1001) OR user_id =1008  

虽然在user_num和user_id上建有索引，但是在上面的语句中优化器还是使用顺序存取路径扫描整个表。因为这个语句要检索的是分离的行的集合，所以应该改为如下语句：  

SELECT * FROM table1 WHERE user_num =104 AND user_id>1001  

UNION  

SELECT * FROM table1 WHERE user_id=1008  

这样就能利用索引路径处理查询。  

5 避免困难的正规表达式  

某些关键字的应用是正确的，技术上叫正规表达式，但有时搭配不当会非常耗费时间，特别是在大型数据表中体现的尤为突出，我们把这种正规表达式称为困难的正规表达式。

5.1  支持通配符的CHARINDEX和LIKE关键字。例如：

SELECT * FROM table1 WHERE user_id LIKE “98_ _ _”  

即使在user_id字段上建立了索引，在这种情况下也还是采用顺序扫描的方式。如果把语句改为SELECT * FROM table1 WHERE user_id >“98000”

在执行查询时就会利用索引来查询，显然会大大提高速度。  

比如查找用户名包含有“c”的所有用户，可以用 

SELECT * FROM table1 WHERE user_name LIKE “%c%” 

下面是完成上面功能的另一种写法：

SELECT * FROM table1 WHERE CHARINDEX (“c”user_name)>0 

这种方法理论上比上一种方法多了一个判断语句，即>0， 但这个判断过程是最快的， 我想信80%以上的运算都是花在查找字符串及其它的运算上。 用这种方法也有好处， 那就是对“%”、“|”等在不能直接用LIKE 查找到的字符中可以直接在这CHARINDEX中运用， 如下：

SELECT * FROM table1 WHERE CHARINDEX (“%”,user_name)>0 

5.2  少使用“*”。例如语句：

SELECT COUNT (*)  FROM table1 

这时用“*”和一个实际的列名得到的都是一个行数的结果，但是用“*”会统计所有列，显然要比用一个实际的列名效率慢。同样，尽管很多开发人员都习惯采用“SELECT * FROM TBL”的模式进行查询，但是为了提高系统的效率，如果你只需要其中某几个字段的值的话，最好把这几个字段直接写出来。

5.3  尽量不要在WHERE子句中对字段使用函数或参与表达式计算，这样会导致无法使用索引进行全表扫描。

5.4  不要使用NOT。查询时可以在WHERE子句使用一些逻辑表达式，如大于、小于、等于以及不等于等等，也可以使用and（与）、or（或）以及not（非）。NOT可用来对任何逻辑运算符号取反。下面是一个NOT子句的例子：

WHERE NOT (col =“VALID”) 

NOT运算符包含在另外一个逻辑运算符中，这就是不等于（<>）运算符。换句话说，即使不在查询where子句中显式地加入NOT词，NOT仍在运算符中，见下例：

SELECT * FROM table1 WHERE  user_id<>3000; 

对这个查询，可以改写为不使用NOT：

SELECT * FROM table1 WHERE  user_id <3000 OR user_id >3000; 

虽然这两种查询的结果一样，但是第二种查询方案会比第一种查询方案更快些。第二种查询允许对user_id列使用索引，而第一种查询则不能使用索引。

5.5  IN和EXISTS。EXISTS要远比IN的效率高，里面关系到full table scan和range scan。同时应尽可能使用NOT EXISTS来代替NOT IN，尽管二者都使用了NOT（不能使用索引而降低速度），但是NOT EXISTS要比NOT IN查询效率更高。

5.6  慎用游标。在某些必须使用游标的场合，可考虑将符合条件的数据行转入临时表中，再对临时表定义游标进行操作，这样可使性能得到明显提高。

5.7  在海量查询时尽量少用格式转换。

5.8  IN、OR子句常会使工作表的索引失效。如果不产生大量重复值，可以考虑把子句拆开，拆开的子句中应该包含索引。

6．使用临时表加速查询  

把表的一个子集进行排序并创建临时表，有时能加速查询。它有助于避免多重排序操作，而且在其他方面还能简化优化器的工作。例如：  

SELECT table1.name，table2.money，……other columns  

FROM table1，table2

WHERE table1.user_id = table2.user_id

AND table2.sign>0  

AND table1.num>“98000”  

ORDER BY table1.name  

如果这个查询要被执行多次，可以把所有sign>0的记录找出来放在一个临时文件中，并按name进行排序：  

SELECT table1.name，table2.money，……other columns  

FROM table1，table2  

WHERE table1.user_id = table2.user_id

AND table2.sign >0  

ORDER BY table1.name  

INTO TEMP temp_table  

然后以下面的方式在临时表中查询：  

SELECT * FROM temp_table  

WHERE num >“98000”  

临时表中的行要比主表中的行少，而且物理顺序就是所要求的顺序，减少了磁盘I/O，所以查询工作量可以得到大幅减少。  

注意：临时表创建后不会反映主表的修改。在主表中数据频繁修改的情况下，注意不要丢失数据。

7 小结  

20％的代码用去了80％的时间，这是程序设计中的一个著名定律，在数据库应用程序中也同样如此。对于数据库应用程序来说，重点在于SQL的执行效率，所谓优化的重点环节即WHERE子句利用了索引，不可优化即发生了全表扫描或额外开销。经验显示，SQL Server性能的最大改进得益于逻辑的数据库设计、索引设计和查询设计方面。反过来说，最大的性能降低问题常常是由这些方面中的不足引起的。其实SQL优化的实质就是在结果正确的前提下，用优化器可以识别的语句，充份利用索引，减少表扫描的I/O次数，尽量避免全表搜索的发生。其实SQL的查询性能优化是一个复杂的过程，上述这些只是在应用层面的一种体现，深入研究还会涉及数据库层的资源配置、网络层的流量控制以及操作系统层的总体设计。