数据库查询优化方法研究

摘要：随着数据库技术的高速发展，数据库系统已成为现代计算机信息系统和计算机应用系统的基础和核心，信息社会的发展已越来越离不开数据库。该文针对当前使用数据库查询在效率方面遇到的一些问题，主要从SQL语句的优化方面提出一些策略和方法，并提出使用SQL语句时需要注意的事项。

关键词：数据库查询；SQL；索引；优化

中图分类号：TP311文献标识码：A文章编号：1009-3044(2011)17-4011-02

Studies on the Optimiztion of Database Retrieval

YANG Chang-yao

(Zhanjiang Institute of Education, Zhanjiang 524037, China)

Abstract: With the rapid development of Database Technology, Database System has become the foundation and core of modern computer information system and computer application system, as Database is a must in the development of an information society. The current paper targets on some efficiency problems in Database retrieval, offers some strategies and solutions from the perspective of optimizing SQL statement, and proposes some issues need to be paid attention to when using SQL statement.

Key words: database retrieval; SQL; index; optimization

由于现代信息技术的重要组成部分――数据库技术的飞速发展，使数据库系统作为管理信息系统的核心，成为现代计算机信息系统和计算机应用系统的基础和核心。随着社会发展对信息技术的依赖，特别是网络的不断普及，各种应用业务需要处理的数据量日益增长，各种形式的海量数据应用不断产生。因而提高数据库的查询效率，优化数据库查询操作便成了提高数据库管理系统乃至管理信息系统的关键所在。

1 优化数据库查询方法

数据查询效率，主要受以下因素的影响：首先，机器硬件及网络通讯设备方面，陈旧落后的硬件设备直接影响到查询效率，是造成堵车的重要原因之一；其次是否使用专用的通讯网络，网络带宽也可能是影响数据库查询性能的瓶颈所在；再次，就是要充分利用数据库的引擎，优化数据库的查询。本文主要研究针对第三种情况提高数据库查询效率的策略和方法。

1.1 合理建立索引

1.1.1 索引是数据库中重要的数据结构

其根本目的就是为了提高查询效率，是有效使用数据库系统的基础，索引建立是否适当是性能好坏的关键。索引可以大大提高查询效率，若索引建少了，查找数据效率就低下，索引建得太多则不利于插入、删除和修改等操作。建立索引的原则主要有以下几个：

1）索引要建立在使用率高的字段上，这样可以提高索引的使用率。

2）建立复合索引时要注意复合索引的顺序要按照使用的频度来确定。

3）在经常需要排序（ORDER BY）和分组（GROUP BY）的字段上建立索引，以加快排序和分组的时间。

4）一般不在数据值较少的字段上建立索引。因为如果数据值少的话有没有索引对查询效率影响并不明显。

5）对于经常存取修改的字段尽量不建立索引，因为这样会带来较大的维护工作。

6）可在主键和外键上建立索引。

1.1.2 索引有助于提高检索性能，但过多或不当的索引也会导致系统性能低效

因为用户在表中每建立一个索引，数据库就要做更多的工作。过多的索引会导致索引碎片，所以我们要合理建立一个索引体系，在衡量是否值得建索引时，可参考以下三个方面：

1）选择性。

选择性指符合查询条件的记录占总记录的百分比。选择性越高，即该值越小，表示越适合建索引，在选择性很低时，通过非聚集索引存取是非常没有效率的存取方式，还不如直接做数据表扫描。

2）数据密度。

数据密度为键值唯一的记录笔数的倒数。数据密度越小，该字段越适合建立索引，平均查询到的记录数=数据密度*总记录数。

3）数据分布。

数据分布表示多笔数据记录组成的方式。表示数据记录是平均散布在一段范围内还是集中在部分区域。如均匀分布，正态分布等，需进一步确定其选择性

1.1.3 创建索引的语法格式

CREATEINDEX 索引名 ON 表名（列名）

例如：Create Index index_empid on employees (empid)。

1.2 通过优质SQL语句提高查询效率

访问数据库时要频繁地使用SQL语句，使用索引时可以很有效的提高查询速度，但是如果SQL 语句使用不恰当的话，索引就不能发挥其作用。实践证明， 查询语句往往消耗了相当一部分的数据库资源，尤其是对于海量数据，劣质的SQL语句与优质的SQL语句执行起来效率相差甚至达到上百倍。所以有必要对查询语句进行优化，从而达到提升数据库性能的目的。下面从以下四个方面提出优化查询语句的方法策略：

1.2.1 不要对数据字段做运算

无运算的字段可以引用索引，有运算的字段将无法引用索引进行优化而需要扫描整个表，很大程度影响数据的查询效率。例如：

SELECT * FROM Order_Details WHERE Quantity+10=110的执行效率就不如

SELECT * FROM Order_Details WHERE Quantity=100

这里指的运算还包括其它的运算，如字符连接等。

1.2.2 不要使用负向查询

负向查询包括NOT、!=、、!、NOTEXISTS、NOTIN、NOTLIKE等。负向查询不能充分利用索引进行二分查找，需要扫描整张表，所以使用负向查询同样也会使查询效率低下。例如：

SELECT * FROM Order_Details WHERE Quantity!=100的执行效率就不如

SELECT * FROM Order_Details WHERE Quantity>100 OR Quantity

1.2.3 不对数据字段使用函数

对数据字段使用函数本身也就是一种运算，将会使效率低。例如：

SELECT * FROM [Order Details] WHERE ABS(Quantity-100)

SELECT * FROM [Order Details] WHERE Quantity>99 AND Quantity

SELECT * FROM Employees WHERE SUBSTRING (LastName,1,1)=‘D’的执行效率不如SELECT * FROM Employees WHERE LastName LIKE ‘D%’。

1.2.4 使用AND 和 OR运算符

AND运算符可以充分利用索引，但是使用OR运算符时，要对参与查询的多个字段都要建立索引，否则将可能扫描全表，从而导致查询效率的下降。例如：

SELECT * FROM Orders WHERE CustomerID=‘ISO1308’ AND OrderDate=‘20101001’

只需要在CustomerID上建索引就可以了。

SELECT * FROM Orders WHERE CustomerID=‘ISO1308’ OR OrderDate=‘20101001’

需要在CustomerID和OrderDate两个属性上都建合适的索引,否则将扫描整个数据表。

2 使用SQL语句时需要注意的事项

2.1 使用SELECT查询语句时

1）尽量不要传回数据表的所有字段，只需要传回需要的字段即可，也不要不使用过滤条件，否则将极大地增加网络负担，从而造成网络堵车。

2）若使用复合索引，索引顺序上的第一个字段才适合当作过滤条件。

3）使用DISTINCT、ORDER BY等语法时要谨慎，尽量等到查询需要时才使用，因为它们需要SQL SERVER做额外的计算。

2.2 大量数据加载时

1）大量加载某个数据表时，应考虑先删掉索引，加载完毕再重建索引，特别是多个用户端同时在做大量数据加载时要注意这点。

2）BULK INSERT是用来将外部文件以一种特定的格式加载到数据库表的T-SQL命令。该命令使开发人员能够直接将数据加载到数据库表中，它的加载速度要比命令提示符大容量复制实用工具bcp要快。

3）大量数据加载时，应设参数采用数据表锁定，而不要采用默认的记录锁。

4）如果数据表的记录需要先做转换，应先导入临时表中，经过处理再大量加载到目的数据表中。

2.3 使用INSERT、DELETE和UPDATE等语句时

1）SELECTINTOFROM语句和INSERTINTOSELECT语句的语法形式分别是：

SELECT value1, value2 INTO Table2 FROM Table1 和

INSERTINTO Table2(a, c, d)SELECTa,c,5 FROM Table1。

这两个语句都可以往另外一个表里面复制表数据，但是对于大量数据，SELECTINTOFROM比INSERT INTOSELECT要快。

2）DELETE会产生ROLLBACK，如果删除大数据量的表速度会更慢，同时还会占用很多的ROLLBACK SEGMENTS。而TRUNCATE是DDL操作，不产生ROLLBACK，所以速度较前者要快。

3）UPDATE和DELETE采用WHERE子句时，条件要符合WHERE的有效格式。

3 结束语

当前数据库使用规模越来越大，数据量呈几何指数级上升，海量数据不断出现，数据库查询性能优化越来越被重视。对于数据库的优化问题，我们要抓住问题的关键所在，针对性提出可行性的解决方案，这样才能真正使数据库服务得到根本提高。此外，数据库设计者也必须从实际出发，很好地了解客户的需求，综合考虑各方面因素，使设计出来的数据库能最大限度地发挥其固有的数据库性能。

参考文献：

[1] 蒋晓科.数据库查询优化策略研究[J].科技资讯,2011(6).

[2] 黄志真.数据库SQL查询优化方法的研究[J].兵工自动化,2001,20(1).

[3] 刘爱云.数据库查询优化方法研究[J].河南科技,2010(10).

[4] 周建鸿.海量数据库的查询优化研究及实现[J].西南民族大学学报,2010(4).