论文部分内容阅读
摘要:随着计算机技术的不断发展,各种日常应用数据呈指数级增长,如何有效的管理数据是急需解决的问题。因此,数据库系统应运而生,查询操作又是最多的数据库操作,鉴于此,如何提高数据库查询效率至关重要。该文通过分析数据库查询步骤提出相应的优化策略,包括基于索引、SQL语句和其他优化方法。
关键词:数据库;查询优化;索引;SQL语句
中图分类号:TP391 文献标识码:A 文章编号:1009-3044(2013)32-7183-03
随着计算机技术的日趋成熟,应用程序产生的数据快速增长,数据库显得尤为重要。数据库系统管理数据的有效工具,数据库操作中查询操作又是最多的,通过数据库查询可以方便地获得所需的数据。然而,很多数据查询操作效率很低,在大数据集查询时表现得更为明显,查询所花时间长,响应慢,甚至导致查询结果不精确,主要原因一方面是由于硬件处理能力不够所导致,另一方面是由于查询方法不好,影响查询效率。查询方法的好处可能影响数十倍的查询效率。虽然有些数据库内置优化方法,但远远还不够,用户提交的查询语句也是影响查询效率的关键。
1 关系数据库的查询处理
1.1 查询处理步骤
关系型数据库管理系统(RDBMS)查询处理包括四个阶段,如图1所示,分别是查询分析,查询检查,查询优化和查询执行。
1)查询分析
查询分析是第一步,主要进行查询语句扫描,词法分析和语法分析。识别语言符号、属性名、SQL关键词等,再进行语法检查和分析判断是否满足SQL语法规则要求。
2)查询检查
查询检查就是对SQL语句进行语义检查,根据数据字典检查数据库对象,包括关系名、属性名等。还要进行用户存取权限检查,如果违反完整性约束或没有权限,查询操作不执行。检查通过后把查询语句转变成关系代数表达式,用语法分析树来表示。
3)查询优化
各个查询语句都有很多操作算法和执行策略可供选择,查询优化就是如何从中选择高效的查询方法。查询优化方法有很多种,比如物理优化和代数优化。物理优化是指根据规则选择底层操作和存取路径;代数优化是指按照规则改变操作的组合和次序,使得查询效率更好。
实际 RDBMS 查询优化器使用这些优化技术的组合,以获得最佳的查询优化的结果。实际应用中很多查询优化采用多种优化技术,实现更好的查询效果。
4)查询执行
查询执行根据优化策略生成查询计划,再由代码生成器生成所要查询计划代码。
2 SQL查询优化方法
在关系数据库系统中,查询优化的作用很明显,随着查询优化技术的进步,关系型数据库系统和SQL查询发展快速。查询优化对关系型数据库系统性能影响巨大,用户只需要说明做什么,而不必考虑怎么做,都交给查询优化来做。
2.1 基于索引的优化
索引是一种物理的数据库结构,根据表中的列建立列值与记录行的对应关系,索引包含键值和指向数据页面和行的指针。每个表包含两部分,分别存放表的数据页面和索引页面,索引页面上存放索引。数据检索时先检索索引页面,找到对应指针,再根据获得的指针读取数据。索引用来加快检索速度,但索引占用系统资源,要适当建立,不能每一列都建索引。对索引进行插入删除操作也需要更多的时间。
1) 索引作用
索引用来加快检索速度,查找数据时不必搜索整个数据库。主要内容包括:① 通过创建唯一性索引,保证每一行数据唯一。② 提高检索速度,这是索引的关键特性。③ 实现参考完整性,使得表间快速链接。④采用排序子句和分组检索数据时,缩短查询时排序和分组时间。 ⑤ 借助索引在查询过程中采用优化器隐藏,提高性能。
2)索引类型
如果一个表不建索引,数据行存储不按任何特定顺序,通常称为堆集。数据库中根据数据存储结果将索引分为簇索引和非簇索引。
簇索引根据数据行的键值排序,再将有用的数据记录存储。簇索引查找速度快是因为簇索引对表中数据排了序,但需要更大的空间来容纳所有重新排序的数据,另外,每个表只有一个簇索引。
非聚集索引结构独立于数据行,并且不对数据行键值排序。具有完全独立的数据线结构,不使用非聚集索引对表进行排序的数据行键。非簇索引借助B-树叶节点存储了行定位器和组成非簇索引的键值,数据存储方式决定行定位器的存储内容和结构,行定位器中存储指向数据行的指针。
非簇索引存储通过索引页,所需的空间更大,检索速度慢,但需要建立多个索引时,只能采用非簇索引,因为每个表簇索引只能建一个,非簇索引可以建248个。如何选择簇索引和非簇索引如表1所示。
2.2 SQL 语句优化
索引有助于提高检索速度,数据库操作是根据SQL语句执行的,数据库性能很大程度上由SQL语句执行效率决定。因此,有效的SQL语句是很重要的,针对SQL语句优化,主要包括where子句优化和避免相关子查询等。
1)here子句优化
where子句优化在SQL语句优化中起重要作用,有一些主要的优化原则。
1 where子句中避免采用操作符!=或<>,不然会检索表中所有行。
2 where子句中谨慎使用in 和 not in,in使系统只能直接检索表中数据,不能使用索引。
3 where子句中避免采用or来连接,不然会检索表中所有行。
4 where子句中避免null值判断,不然系统不能使用索引,要检索表中所有行。
5 where子句中避免表达式操作,不然系统不能使用索引,要检索表中所有行。
6 where子句中避免函数操作,不然系统不能使用索引,要检索表中所有行。 7 where子句中左边避免函数或算术运算,不然系统不能正确使用索引。
2)避免相关子查询
主查询和where子句中的查询不能同时出现一个列标签,另外,查询层次多导致效率低下,子查询尽量少用为好,至少也要尽可能过滤掉子查询。
3)化表连接条件
有些情况下,表间可能有多个连接,where子句利用这些连接可以有效改善查询速度。
2.3 其他SQL语句优化
除了索引和优化SQL语句的方法外,还有其他方法可以提高查询效率,比如能用DISTINCT的就不用GROUP BY;能用UNION ALL就不要用UNION。
1)避免或简化排序
大型表重复排序要尽量避免或简化,借助索引产生输出时,优化器可以避免排序。合理的增加和减少索引可以避免不重要的排序,另外,还要合理地合并数据表。加入不能避免排序,就只能简化排序。
2)采用临时表来加快查询
临时表中的行少于主表,物理顺便正好符合要求,可以减少输入输出操作,大大降低查询量。排序表的子集,并建立临时表,也有利于提高查询速度,还有利于避免多重排序。主表更新频繁时注意数据丢失。
3)优化表中的数据类型
表中采用的数据最好具有很好的兼容性,不兼容可能导致优化器不能完全发挥作用。比如char和varchar无法兼容。
4)使用排序取代非顺序访问
非顺序磁盘存取很慢,影响整个过程。SQL语句隐藏这个情况,在查询过程中非顺序查询量很大,查询速度明显降低,可以借助排序来代替非顺序存取。很多情况下,使用这种替代方案可以有效的提高查询速度。
5)数据服务器存储
数据处理最好在服务器上进行,可以降低网络开销,采用存储过程有利于提高速度,存储过程代表编译好和优化过的,且存储在数据库的SQL语句。
3 结论
数据库优化问题需要找出关键的影响因素,才能真正提高查询效率。该文首先分析数据库查询优化方法,提出相应的解决办法,有效的提高数据库查询效率。俗话说,20%的代码花费了80%的时间,这个数据库查询同样适用,对于SQL执行效率关键在于少从磁盘读取数据,并且采取顺序读页。
参考文献:
[1] 樊新华.关系数据库的查询优化技术[J].计算机与数字工程,2009,37(12):188-192.
[2] 张德,董逸生.Internet 上的数据库联合查询优化[J].计算机学报,2000,23(2):171-176.
[3] 肖捷,肖正新,袁华强.关系数据库查询优化策略的分析与应用[J].计算机与现代化,2006(11):36.
[4] MySQL数据库查询的三种优化方法[EB/OL].http://wenku.baidu.com/view/b1709fa7f524 ccbff121846f.html
[5] 对SQL Server数据库查询语句优化的研究[EB/OL].http://wenku.baidu.com/view/0894 696e561252d380eb6e87.html.
[6] 浅谈如何优化SQL语句提高数据库系统效率[EB/OL].http://wenku.baidu.com/view/767337 65e518964bcf847cbc.html.
[7] 数据库查询优化策略分析[EB/OL]. http://wenku.baidu.com/view/3698eb5177232f60 ddcca1f4.html.
关键词:数据库;查询优化;索引;SQL语句
中图分类号:TP391 文献标识码:A 文章编号:1009-3044(2013)32-7183-03
随着计算机技术的日趋成熟,应用程序产生的数据快速增长,数据库显得尤为重要。数据库系统管理数据的有效工具,数据库操作中查询操作又是最多的,通过数据库查询可以方便地获得所需的数据。然而,很多数据查询操作效率很低,在大数据集查询时表现得更为明显,查询所花时间长,响应慢,甚至导致查询结果不精确,主要原因一方面是由于硬件处理能力不够所导致,另一方面是由于查询方法不好,影响查询效率。查询方法的好处可能影响数十倍的查询效率。虽然有些数据库内置优化方法,但远远还不够,用户提交的查询语句也是影响查询效率的关键。
1 关系数据库的查询处理
1.1 查询处理步骤
关系型数据库管理系统(RDBMS)查询处理包括四个阶段,如图1所示,分别是查询分析,查询检查,查询优化和查询执行。
1)查询分析
查询分析是第一步,主要进行查询语句扫描,词法分析和语法分析。识别语言符号、属性名、SQL关键词等,再进行语法检查和分析判断是否满足SQL语法规则要求。
2)查询检查
查询检查就是对SQL语句进行语义检查,根据数据字典检查数据库对象,包括关系名、属性名等。还要进行用户存取权限检查,如果违反完整性约束或没有权限,查询操作不执行。检查通过后把查询语句转变成关系代数表达式,用语法分析树来表示。
3)查询优化
各个查询语句都有很多操作算法和执行策略可供选择,查询优化就是如何从中选择高效的查询方法。查询优化方法有很多种,比如物理优化和代数优化。物理优化是指根据规则选择底层操作和存取路径;代数优化是指按照规则改变操作的组合和次序,使得查询效率更好。
实际 RDBMS 查询优化器使用这些优化技术的组合,以获得最佳的查询优化的结果。实际应用中很多查询优化采用多种优化技术,实现更好的查询效果。
4)查询执行
查询执行根据优化策略生成查询计划,再由代码生成器生成所要查询计划代码。
2 SQL查询优化方法
在关系数据库系统中,查询优化的作用很明显,随着查询优化技术的进步,关系型数据库系统和SQL查询发展快速。查询优化对关系型数据库系统性能影响巨大,用户只需要说明做什么,而不必考虑怎么做,都交给查询优化来做。
2.1 基于索引的优化
索引是一种物理的数据库结构,根据表中的列建立列值与记录行的对应关系,索引包含键值和指向数据页面和行的指针。每个表包含两部分,分别存放表的数据页面和索引页面,索引页面上存放索引。数据检索时先检索索引页面,找到对应指针,再根据获得的指针读取数据。索引用来加快检索速度,但索引占用系统资源,要适当建立,不能每一列都建索引。对索引进行插入删除操作也需要更多的时间。
1) 索引作用
索引用来加快检索速度,查找数据时不必搜索整个数据库。主要内容包括:① 通过创建唯一性索引,保证每一行数据唯一。② 提高检索速度,这是索引的关键特性。③ 实现参考完整性,使得表间快速链接。④采用排序子句和分组检索数据时,缩短查询时排序和分组时间。 ⑤ 借助索引在查询过程中采用优化器隐藏,提高性能。
2)索引类型
如果一个表不建索引,数据行存储不按任何特定顺序,通常称为堆集。数据库中根据数据存储结果将索引分为簇索引和非簇索引。
簇索引根据数据行的键值排序,再将有用的数据记录存储。簇索引查找速度快是因为簇索引对表中数据排了序,但需要更大的空间来容纳所有重新排序的数据,另外,每个表只有一个簇索引。
非聚集索引结构独立于数据行,并且不对数据行键值排序。具有完全独立的数据线结构,不使用非聚集索引对表进行排序的数据行键。非簇索引借助B-树叶节点存储了行定位器和组成非簇索引的键值,数据存储方式决定行定位器的存储内容和结构,行定位器中存储指向数据行的指针。
非簇索引存储通过索引页,所需的空间更大,检索速度慢,但需要建立多个索引时,只能采用非簇索引,因为每个表簇索引只能建一个,非簇索引可以建248个。如何选择簇索引和非簇索引如表1所示。
2.2 SQL 语句优化
索引有助于提高检索速度,数据库操作是根据SQL语句执行的,数据库性能很大程度上由SQL语句执行效率决定。因此,有效的SQL语句是很重要的,针对SQL语句优化,主要包括where子句优化和避免相关子查询等。
1)here子句优化
where子句优化在SQL语句优化中起重要作用,有一些主要的优化原则。
1 where子句中避免采用操作符!=或<>,不然会检索表中所有行。
2 where子句中谨慎使用in 和 not in,in使系统只能直接检索表中数据,不能使用索引。
3 where子句中避免采用or来连接,不然会检索表中所有行。
4 where子句中避免null值判断,不然系统不能使用索引,要检索表中所有行。
5 where子句中避免表达式操作,不然系统不能使用索引,要检索表中所有行。
6 where子句中避免函数操作,不然系统不能使用索引,要检索表中所有行。 7 where子句中左边避免函数或算术运算,不然系统不能正确使用索引。
2)避免相关子查询
主查询和where子句中的查询不能同时出现一个列标签,另外,查询层次多导致效率低下,子查询尽量少用为好,至少也要尽可能过滤掉子查询。
3)化表连接条件
有些情况下,表间可能有多个连接,where子句利用这些连接可以有效改善查询速度。
2.3 其他SQL语句优化
除了索引和优化SQL语句的方法外,还有其他方法可以提高查询效率,比如能用DISTINCT的就不用GROUP BY;能用UNION ALL就不要用UNION。
1)避免或简化排序
大型表重复排序要尽量避免或简化,借助索引产生输出时,优化器可以避免排序。合理的增加和减少索引可以避免不重要的排序,另外,还要合理地合并数据表。加入不能避免排序,就只能简化排序。
2)采用临时表来加快查询
临时表中的行少于主表,物理顺便正好符合要求,可以减少输入输出操作,大大降低查询量。排序表的子集,并建立临时表,也有利于提高查询速度,还有利于避免多重排序。主表更新频繁时注意数据丢失。
3)优化表中的数据类型
表中采用的数据最好具有很好的兼容性,不兼容可能导致优化器不能完全发挥作用。比如char和varchar无法兼容。
4)使用排序取代非顺序访问
非顺序磁盘存取很慢,影响整个过程。SQL语句隐藏这个情况,在查询过程中非顺序查询量很大,查询速度明显降低,可以借助排序来代替非顺序存取。很多情况下,使用这种替代方案可以有效的提高查询速度。
5)数据服务器存储
数据处理最好在服务器上进行,可以降低网络开销,采用存储过程有利于提高速度,存储过程代表编译好和优化过的,且存储在数据库的SQL语句。
3 结论
数据库优化问题需要找出关键的影响因素,才能真正提高查询效率。该文首先分析数据库查询优化方法,提出相应的解决办法,有效的提高数据库查询效率。俗话说,20%的代码花费了80%的时间,这个数据库查询同样适用,对于SQL执行效率关键在于少从磁盘读取数据,并且采取顺序读页。
参考文献:
[1] 樊新华.关系数据库的查询优化技术[J].计算机与数字工程,2009,37(12):188-192.
[2] 张德,董逸生.Internet 上的数据库联合查询优化[J].计算机学报,2000,23(2):171-176.
[3] 肖捷,肖正新,袁华强.关系数据库查询优化策略的分析与应用[J].计算机与现代化,2006(11):36.
[4] MySQL数据库查询的三种优化方法[EB/OL].http://wenku.baidu.com/view/b1709fa7f524 ccbff121846f.html
[5] 对SQL Server数据库查询语句优化的研究[EB/OL].http://wenku.baidu.com/view/0894 696e561252d380eb6e87.html.
[6] 浅谈如何优化SQL语句提高数据库系统效率[EB/OL].http://wenku.baidu.com/view/767337 65e518964bcf847cbc.html.
[7] 数据库查询优化策略分析[EB/OL]. http://wenku.baidu.com/view/3698eb5177232f60 ddcca1f4.html.