数据查询优化策略

转载 2016年05月30日 14:19:21

这里有个数据优化的网址-------点我


1  引言
随着计算机应用技术的不断普及和发展,数据库系统正越来越多的走进人们的日常生活。在要求查询结果正确无误的同时,人们越来越关心查询的效率问题。影响查询效率的因素很多,诸如处理器的速度、I/O速度、存储器的容量、操作系统、采取何种的数据库服务系统等。但是对于特定服务器来说查询的效率主要取决于DBA(数据库管理员)所给定的查询语句。
2   合理使用索引 
数据库服务器对数据进行访问一般采用下面的两种方式:①索引扫描,通过索引访问数据;②表扫描,读表中的所有页。当对一个表进行查询时,如果返回的行数占全表总行数的10%到15%时,使用索引可以极大的优化查询的性能。但是如果查询涉及到全表40%以上的行时,表扫描的效率比使用索引扫描的效率高。在具体使用的过程中,要结合实际的数据库和用户的需求来确定要不要索引以及在什么字段上建立什么样的索引。下面给出一些通用的规则:
 1.       在经常用作过滤器或者查询频率较高字段上建立索引;
 2.       在SQL语句中经常进行GROUP BY、ORDER BY的字段上建立索引;
 3.       在不同值较少的字段上不必要建立索引,如性别字段;
 4.       对于经常存取的列避免建立索引;
 5.       用于联接的列(主健/外健)建立索引;
 6.       在经常存取的多个列上建立复合索引,但要注意复合索引的建立顺序要按照使用的频度来确定。 
2.1  聚集索引
聚集索引是指行的物理顺序与行的索引顺序相同的索引。一个表只能有一个聚集索引。非聚集索引是指定表的逻辑顺序的索引,行的物理顺序与索引顺序不尽相同,每个表可以有多个非聚集索引。缺省情况下建立的是非聚集索引,但是在一些特定的情况下建立非聚集索引会极大的缩短查询的时间。有大量重复值、且经常有范围查询(between,>,<,>=,<=)和orderby、groupby发生的列,可考虑建立聚集索引,而对于频繁修改的列、或者返回小数目的不同值的这些情况应该避免建立聚集索引。
 
使用聚集索引的最大好处就是能够根据查询要求,迅速缩小查询范围,避免全表扫描。比如要返回2004年4月1日到2004年10月1日之间的数据,如果在日期的字段建立了聚集索引,那么数据本来就是按照日期的顺序排列的,只要找到开始和结尾日期的数据就可以了,可以极大的节省时间。而如果使用非聚集索引,必须查到这个时间段中每个日期对应的位置,然后在根据位置存取数据,明显效率很低。显而易见,使用聚集索引的优势很明显。一个表只能按照一个固定的顺序来存储数据,因此,在建立聚集索引的时候一定要和实际查询相结合,看哪个字段对于查询贡献大,而且操作不是很频繁。
 
索引有助于提高检索性能,但过多或不当的索引也会导致系统低效。因为用户在表中每添加一个索引,数据库就要做更多的工作。过多的索引甚至会导致索引碎片。所以说,我们要合理使用索引体系,特别是对索引的创建,更应精益求精,使数据库的性能得到更好的发挥。
 3   书写高效的SQL语句 
虽然特定的数据库服务器都会对输入的查询语句进行一定的优化操作,但是查询效率主要取决于DBA所书写的SQL语句的好坏。为确保编写的SQL语句有较好的性能,应考虑以下的优化方法:
 
⑴尽量减少使用负逻辑的操作符和函数,因为它们会导致全表扫描,而且容易出错。可以把含有NOT、<> 、!= 等负逻辑的条件表达式转化为意思相当的正逻辑。
 
⑵字段提取要多少,取多少,避免使用“select *”格式,因为在数据量较大的时候,影响查询性能的最大因素不在与数据的查找,而在于物理I/O的操作。
 
⑶避免使用LIKE、EXISTS、IN等标准表达式,他们会使字段上的索引无效,引起全表扫描。尽量减少表的联接操作,不可避免的时候要适当增加一些冗余条件,使参与联接的字段集尽量少。
 
⑷ OR会使字段上的索引失效,引起全表扫描。下面的例子中,可以把or子句分开,在把结果做加法和算,也可以编写一个存储过程来避免索引的失效。
Select work-name, work-dept from work where work-id=’2’ or work-id=’3’;
 
⑸尽量减少使用联接字段而把所有的条件分列出来用and来进行连接,可以充分的利用在某些字段上已经存在的索引。
select work-id from salary where work-salary||”|| work-dept=’$2000 teacher’ ;
如果把条件分开来写成下面的格式,系统的查询性能可以得到一定的提高。
select work-no from salary where work-salary=$2000 and work-dept=’ teacher ’;
 
⑹尽量避免使用相关的嵌套查询,
 
3.1  Where字句的影响 
Where子句说明查询的条件,直接决定查询的性能。因此在where子句的书写及应用中要多加注意。书写where 子句时尽量避免使用不兼容的数据类型,避免对where 子句中的条件参数使用其他的数学操作符,尽可能的把操作转化到式子的左边,这样可以有效的利用已有的索引技术。对于where字句中的多个选择条件,要选取结果集小的先执行。下面给出一些不规范书写。
select work-id from salary where work-salary>4000;
select work-id from salary where work-salary*2>$4000;
 
对于第一个查询来说,4000是整数,而工人的工资时money格式的,系统在查询的时候需要耗费时间来进行格式转化。对于第二个例子,任何在运算符左边的操作都会使SQL采用全表扫描,对表中的每个数据项做相应的操作来比较是否满足条件,如果这个字段有索引,则索引失效。因此上面两个例子最好可以写成下面的格式:
select work-no from salary where work-salary>$4000;
select work-no from salary where work-salary>$2000;
 
4   存储过程的使用 
存储过程由SQL语句和SPL语言的语句组成,创建后转换为可执行代码,作为数据库的一个对象存储在数据库中,存储过程的代码驻留在服务器端,因而执行时不需要将应用程序代码向服务器端传送,可以大大减轻网络负载,加快系统响应时间。同时,由于存储过程已编译为可执行代码,不需要每次执行时进行分析和优化工作,从而减少了预处理所花费的时间,提高了系统的效率。
在工程中,我们可以把经常用到的查询动作编写成一个存储过程,并利用参数实现动态查询过程来响应客户的要求;可以实现在服务器端进行批量数据处理等操作;可以使用存储过程作为强制安全性工具;还可以利用系统为用户定义的管理级别存储过程实现数据的管理、配置和监控等。合理使用存储过程可以有效的提高系统效率。
5    视图的应用 
利用视图不仅可以提高数据的保密性,方便的设置用户的权限,而且也可以提高数据的精炼性。在DBMS中有着许多不同的角色,他们对数据的要求是不同的,针对不同类别的用户分别建立合适的视图,可以在有效的条件下提高数据的有用性,提高系统对不同用户的查询响应时间。此外用户访问数据库一般要求得到的是最近的数据,比如查询话费,最常用的数据是最近三个月的。因此在许多情况下,可以按照时间对数据库中的数据进行水平分片,把最近一段时间的数据呈现给用户。当用户需要查找“过期”数据时再把相应的块调进来。由于这种情况极少发生,在一定的情况下,可以有效的减少数据量,缩小数据查找范围。使用这种方法要注意分区数据的维护,因此一定要在权衡维护和查询代价的基础上确定是否要使用分片。如果经常要访问全库数据进行综合对比的话,这种方法就不适用。
6     小结
关系数据库的优化是一个和实际数据库结构密切相关的问题,在实际应用中应该结合具体的数据库服务器,深入的理解服务器的运作模式、资源配置,优化服务器的运行环境,选择合适的操作系统,最大限度的发挥服务器的性能。

关系数据库的查询优化策略----总结了一些查询优化的方法,希望可以对大家有所帮助(原创)

1         引言随着计算机应用技术的不断普及和发展,数据库系统正越来越多的走进人们的日常生活。在要求查询结果正确无误的同时,人们越来越关心查询的效率问题。影响查询效率的因素很多,诸如处理器的速...
  • ttfwind
  • ttfwind
  • 2005年10月11日 17:19
  • 7956

数据库查询优化策略

数据库查询优化逻辑层查询优化策略1.尽可能的早做选择和投影(基本思路):可以使中间结果变小,节省几个数量级的执行时间 2.把选择和投影串接起来:一元运算序列可一起执行,只需对整个关系进行一趟扫描 ...
  • qq_25235807
  • qq_25235807
  • 2017年05月04日 21:32
  • 438

大数据表的查询优化方案

如果有一张大表,表中的数据有几百万、几千万甚至上亿,要实现实时查询,查询的结果要在十秒钟之内出来,怎么办?如何做优化? 本人现在做的项目中,有个表的数据超过1千万行,超过3G的数据。现在需要对表中的...
  • brushli
  • brushli
  • 2014年11月02日 17:08
  • 1326

oracle千万级数据查询优化

环境:表数据量千万级 需求:组合查询,按条件统计某几个字段取前100条记录 问题:没建索引导致查询结果耗时10多秒,不能忍受。 解决方法: 1.建索引,在哪个字段建?    在这里先提下Oracl...
  • csmnjk
  • csmnjk
  • 2016年10月19日 17:23
  • 6426

Oracle 查询优化的基本准则详解

Oracle 查询优化的基本准则详解 1:在进行多表关联时,多用 Where 语句把单个表的结果集最小化,多用聚合函数汇总结果集后再与其它表做关联,以使结果集数据量最小化 2:在两张表进行关联...
  • u014421556
  • u014421556
  • 2016年06月28日 21:18
  • 1232

《MySql》--百万级数据优化查询

前言 众所周知,当一个数据库的单表数据量很大时,比如说是百万数量级的,如果我们使用普通的查询语句的话,耗时会非常多(相比加上索引),今天小编带领着大家做一下实验,见证一下具有二百多万条数据的单表,...
  • u013067402
  • u013067402
  • 2017年01月18日 07:42
  • 8254

mysql千万级大数据SQL查询优化

1.对查询进行优化,应尽量避免全表扫描,首先应考虑在 where 及 order by 涉及的列上建立索引。 2.应尽量避免在 where 子句中对字段进行 null 值判断,否则将导致引擎...
  • u014421556
  • u014421556
  • 2016年07月29日 13:27
  • 33868

mysql千万级数据量根据索引优化查询速度

(一)索引的作用 索引通俗来讲就相当于书的目录,当我们根据条件查询的时候,没有索引,便需要全表扫描,数据量少还可以,一旦数据量超过百万甚至千万,一条查询sql执行往往需要几十秒甚至更多,5秒以上就已经...
  • qq_33556185
  • qq_33556185
  • 2016年08月15日 18:05
  • 22605

Informix-Online数据库查询优化策略

Informix-Online 动态服务器(IDS,Informix-Online Dynamic Server)作为Informix数据库产品技术的核心,以其动态可伸缩体系结构,高效的并行处理能力、...
  • joanna_chen
  • joanna_chen
  • 2009年01月09日 15:44
  • 407

Hibernate优化策略

引言:  Hibernate是目前Java领域事实上的ORM实现标准,它以优雅的方式解决了面向对象和面向Table之间的不匹配状态,是解决Java应用中数据持久化的框架。由于Hibernate自行管理...
  • blueheart20
  • blueheart20
  • 2016年09月17日 21:49
  • 2453
内容举报
返回顶部
收藏助手
不良信息举报
您举报文章:数据查询优化策略
举报原因:
原因补充:

(最多只允许输入30个字)