sql服务器关系数据库自动查询优化技术研究

sql服务器关系数据库自动查询优化技术研究

1合理使用查询优化技术在数据库应用程序中，检索是最基本、最常用的操作。通常，对于特定的搜索请求，某些搜索语句可以用作不同的格式。不同的表达将导致数据库的快速响应。因此，在实际应用中，我们需要使用索引优化技术来分析不同形式的索引语句，并选择高效合理的索引语句，以降低服务所需的系统开支。我们现在有很多流行的搜索优化方法。从应用的角度来看，本文主要基于对数据库中的索引优化。在本文中，我们以关系到系统的sql服务器为例，重点讨论基于索引的可靠事务库的优化方法。2查询优化器的实现过程SQLServer关系数据库系统的自动查询优化技术是通过基于开销的查询优化器来实现的.查询优化器是服务器的一个组成部分,其主要任务是评估每一条SQL语句,并确定最有效的执行计划.在用户提交查询之后,查询优化器需要采用不同的处理步骤对原始查询进行变换,这些变换工作必须在关系数据库系统处理查询请求和返回结果前处理完毕,其过程主要包括解析处理、查询优化处理和执行三个步骤.2.1解析处理.解析是分析SQL语句并将其转换成可被编译的数据结构的过程.解析处理对引入的查询进行语法检查,并将该语法分解成多个关系数据库引擎能够响应的部分.解析处理的结果是解析后的查询树.2.2查询优化器和连接选择查询优化是为查询选择最有效的查询执行计划的过程.在这一阶段里,查询优化所做的工作很复杂,首先需要标识出查询的搜索和连接标准,通过限制搜索,查询优化器减少处理行的数量、减少索引数和读取的数据页数.然后进行索引选择,检测对于给定的子句是否存在索引,并对存在的索引进行有效性评估.索引的有效性由返回的记录行数决定,记录行数信息来自对索引或列的统计.如果是对多表进行查询或是对表进行自连接操作,查询优化器还要评估连接选择,并选择开销最低的连接策略.确定连接策略需要考虑的因素有选择性、密度和处理查询所需的内存大小等.优化器必须考虑各关系的统计信息来对计划做出最佳估计.2.3执行执行就是将优化器所选择的执行计划执行,执行引擎从相应的数据库文件中筛选数据,并将结果输出.3聚簇索引和非聚簇索引索引是对数据库表中一列或多列的值进行排序的结构,是一种特殊类型的数据库对象,它与表有着密切的关系.索引中包含指向表中物理地址的逻辑指针.数据库使用索引时,可以通过逻辑指针直接到物理数据页中提取数据,从而提高数据访问速度,改善数据库工作性能.SQLServer采用B树结构的索引,根据索引的顺序与数据表的物理顺序是否相同可以分为聚簇索引(ClusteredIndex)和非聚簇索引(Non-clusteredIndex).尽管索引能够提高检索速度,但每种索引对于特定的查询任务产生的效率却有很大差别.聚簇索引中索引存储值的顺序和表中数据的物理存储顺序完全一致.建立聚簇索引时,系统将对表的物理数据页中的数据按列进行排序,然后再重新存储到磁盘上,即聚簇索引与数据是一体的,它的叶节点中存储的是实际的数据.由于聚簇索引指定了表中数据的物理顺序,因此,每张表只能有一个聚簇索引,通常基于主键来创建.聚簇索引比较适用于以下几种查询:值在某一范围内的查询、返回大量结果的查询、使用join子句的查询、使用orderby或groupby子句的查询.非聚簇索引并不在物理上排列数据,即索引中的逻辑顺序并不等同于表中行的物理顺序,索引仅仅记录指向表中行位置的指针,这些指针本身是有序的,通过这些指针可以在表中快速地定位数据.非聚簇索引作为与表分离的对象存在.因此,一个表可以有多达249个非聚簇索引.非聚簇索引比较适合于需要精确匹配的查询.一般来说,建立索引应考虑以下原则.(1)在经常进行连接的列上建立索引,而不经常连接的列则由优化器自动生成索引.(2)在数据量较少的表上不宜建立索引,因为在小表上进行表扫描通常速度更快.(3)在频繁进行排序或分组(即进行groupby或orderby操作)的列上建立索引.如果待排序的列有多个,可以在这些列上建立复合索引,并且注意复合索引中列的顺序,使第一列有较高的选择性,以加快查询速度.(4)在不同值较少的列上不宜建立索引,如“性别”列上只有“男”、“女”两种取值,重复值太多,建立索引不会提高查询效率.(5)选择长度较短的列建立索引.因为在较短列上建立索引,缓存中能放置更多的索引页,可以减少I/O操作,提高查询速度.(6)不宜在经常进行插入、删除和修改操作的列上建立索引.因为更新数据时,也将增加对表索引处理的开销.4合理使用索引建立索引后,应当对具体的SQL语句进行分析,以检查SQL语句是否使用了索引,并且当SQL语句中的where子句包含多个带有索引的字段时,还要注意索引的选择是否合理,选择了错误的索引不仅不会带来性能的提高,而且往往会导致性能的降低.下面以实例说明如何在SQL语句中使用合理的索引.在图书管理系统中存在book表和bookcategory表两个数据表,其字段名称及其类型分别如表1和表2所示.4.1索引排除器是否可选择索引不能用null作索引,任何包含null值的列都不会被包含在索引中,即使索引有多列,只要这些列中有一列含有null,该列就会从索引中排除,也就是说如果某列存在空值,即使对该列创建索引也不会提高性能.任何在where子句中使用isnull或isnotnull的语句,优化器是不允许使用索引的.4.2生成条件推导where条件子句中任何对列的操作(计算表达式、数据库函数等)都有可能导致全表扫描,查询时要尽量将操作移至等式的右边,或用其他方式来代替函数,例如下列查询.select*frombookswhere价格*0.8>20select*frombookswhereyear(上架时间)=2006由于where子句中对列的操作结果都是由SQL语句运行时逐行计算得到,因此,即使该表有针对“价格”和“上架时间”列的索引,也不能利用索引进行优化,可以将上述条件表达式改写如下.select*frombookswhere价格>20/0.8select*frombookswhere上架时间>=′2006-01-01′and上架时间<=′2006-12-31′两者查询的结果是一样的,但后者能很好地利用索引,可以提高查询效率.4.3连接运算的执行代价在嵌套查询中,表的顺序存取对查询效率可能产生致命的影响.如一个嵌套3层的查询,如果每层查询都产生1000行数据,则整个查询就要查询10亿行数据.避免这种情况的主要方法就是对连接的列进行索引,按索引路径完成连接运算,从而大大降低连接运算的执行代价.例如,books表和bookcategory表要进行连接,就要在“图书类编号”这个连接字段上建立索引.尽管在所有的检索列上都有索引,但某些形式的where子句强迫优化器使用顺序存取,如下列语句.select*frombookswhere(图书编号>1101and图书编号<1150)or价格=30虽然在“图书编号”和“价格”列上都建有索引,但优化器对该语句的处理还是使用顺序存取路径扫描整个表,因为这个语句要检索的数据行是分离行的集合,可以使用并集来避免顺序存取,改写后的形式如下.select*frombookswhere(图书编号>1101and图书编号<1150)unionselect*frombookswhere价格=304.4多使用索引包含not、<>或!=运算的where子句对于优化器的索引选择来说没有用处,因为这样的子句是排斥性的,而不是包括性的,所以在扫描整个数据表之前无法确定子句的选择性,如下列语句.select*frombookswhere价格<>25上述语句可以改为如下语句.select*frombookswhere价格>25unionselect*frombookswhere价格<25虽然这两种查询的结果一样,但是第二种查询方案会比第一种查询方案更快些.第二种查询允许对“价格”列使用索引,而第一种查询则不能使用索引.4.5文本检索功能在select查询语句中,“%”可进行字符串的匹配,但进行匹配时一定要注意其位置,如果“%”出现在搜寻字符串首部,则查询无法使用索引,如下列语句.select*frombookswhere图书名称like′%计算机′即使在“图书名称”列上建立了索引,优化器还是会采用顺序扫描方式处理该select语句,使查询速度降低.然而当通配符出现在字符串其他位置时,优化器就能利用索引来执行查询.例如下面的查询,在“图书名称”列上的索引就可以得到使用.select*frombookswhere图书名称like′计算机%′4.6简化了查询前的索引覆盖查询是指创建索引的列包含了查询语句的所有数据列.例如,如果在一个表的a、b、c列上创建了组合索引,则从该表中检索a、b、c列的查询被视为覆盖的查询.由于符合查询要求的全部数据都存在于索引本身,所以查询时只需在索引页上产生I/O操作,而不需要在表的数据页或聚簇索引的数据页上产生I/O操作,减少了总体磁盘的I/O操作.例如,books表上“图书编号”和“价格”列的组合非聚簇索引将覆盖下列查询.select图书编号,价格frombookswhere价格=20在查询时,由于“图书编号”和“价格”键值都包含