您当前位置:主页 > 资讯攻略 >
来源:未知
2020-04-07 09:27:24 浏览: 分类:资讯攻略

百万数据查询优化技巧三十则

1.为了优化查询,我们应该尽量避免扫描整个表。首先,我们应该考虑在where和order by中涉及的列上建立索引。

2.尽量避免判断where子句中字段的空值,否则会导致引擎放弃使用索引,扫描整个表,比如:

从t中选择id,其中num为空

您可以将num的默认值设置为0,以确保表中的num列没有空值,然后进行如下查询:

从t中选择id,其中num=0

3.尽量避免在where子句中使用!=或< & gt运算符,否则,引擎将放弃索引并扫描整个表。

4.尽量避免在where子句中使用or连接条件,否则会导致引擎放弃使用索引并扫描整个表,例如:

从t中选择id,其中num=10或num=20

您可以像这样查询:

从t中选择id,其中num=10

联合所有

从t中选择id,其中num=20

5.in和not in也要慎用,否则会导致全表扫描,比如:

select id from t where num in(1,2,3)

对于连续值,如果可以使用between,请不要使用in:

从t中选择id,其中num介于1和3之间

6.以下查询也将导致全表扫描:

从t中选择id,其中名称如“%abc%”

为了提高效率,可以考虑全文检索。

7.如果在where子句中使用参数,也会导致全表扫描。因为SQL只能在运行时解析局部变量,而优化器不能把访问计划的选择延迟到运行时;必须在编译时选择它。但是,如果访问计划是在编译时建立的,那么变量的值仍然是未知的,因此它不能用作索引选择的输入。以下语句将扫描整个表:

select id from t where num = @ num & ltmailto:num = @ num & gt;

您可以强制查询使用索引:

Select from t with (index))其中num = @ num < mailto:num = @ num & gt;

8.您应该尽量避免在where子句中对字段进行表达式操作,这将导致引擎放弃使用索引并扫描整个表。比如:

从t中选择id,其中num/2=100

应改为:

从t中选择id,其中num=100*2

9.您应该尽量避免对where子句中的字段执行函数操作,这将导致引擎放弃使用索引并扫描整个表。比如:

select id from t where substring(name,1,3)= ' abc '-以ABC开头的名称id

select id from t where datediff(day,createdate,' 2005-11-30 ')= 0-& lsquo;2005年11月30日生成的id

应改为:

从t中选择id,其中名称如“abc%”

select id from t where create date & gt;='2005-11-30 '和创建日期& lt'2005-12-1'

10.where子句中的“不要”= & quot执行left函数、算术运算或其他表达式运算,否则系统可能无法正确使用索引。

11.使用索引字段作为条件时,如果索引是复合索引,则必须使用索引中的第一个字段作为条件,以保证系统可以使用该索引,否则不会使用该索引,并且字段顺序应尽可能与索引顺序一致。

12.不要写一些无意义的查询,比如生成空表结构:

select col1,col2 into #t from t其中1=0

这种代码不会返回任何结果集,但是会消耗系统资源。应该改成这样:

创建表#t(...)

13.很多时候,用exists替换in是个不错的选择:

从a中选择编号,其中编号在(从b中选择编号)

替换为以下语句:

从存在的a中选择num(从b中选择1,其中num=a.num)

14.并非所有索引都对查询有效,SQL是根据表中的数据进行优化的。当索引列中有大量重复数据时,SQL查询可能不会使用索引。比如一个表中有字段性别,男性和女性几乎各占一半,那么即使索引建立在性别上,也不会对查询效率起到作用。

15.索引越多越好。虽然索引可以提高相应select的效率,但也降低了insert和update的效率。因为索引可能会在插入或更新期间重建,所以如何建立索引需要根据具体情况仔细考虑。一个表中的索引数不应超过6。如果索引太多,就要考虑是否有必要在一些不常用的列上建立索引。

16.我们应该尽可能避免更新聚集索引数据列,因为聚集索引数据列的顺序就是表记录的物理存储顺序。一旦该列的值发生变化,就会导致整个表记录顺序的调整,会消耗相当大的资源。如果应用程序系统需要频繁更新聚集索引数据列,就需要考虑是否应该将索引构建为聚集索引。

17.尽可能多地使用数字字段。如果只包含数字信息的字段不尽可能设计成字符,会降低查询和连接的性能,增加存储开销。这是因为引擎在处理查询和连接时会逐个比较字符串中的每个字符,但对于数值类型,只需进行一次比较。

18.尽可能用varchar/nvarchar代替char/nchar,因为首先变长字段的存储量空小,可以节省存储量空。其次,对于查询来说,相对较小领域的搜索效率明显更高。

19.不要在任何地方使用select * from t,用特定的字段列表替换“”。* & quot,不要返回任何不需要的字段。

20.尽量使用表变量,而不是临时表。如果表变量包含大量数据,请注意索引非常有限(只有主键索引)。

21.避免频繁创建和删除临时表,以减少系统表资源的消耗。

22.临时表并非不可用。正确使用它们可以使一些例程更加有效,例如,当需要重复引用大型表或常用表中的数据集时。但是,对于一次性事件,最好使用导出表。

23.创建新的临时表时,如果一次插入大量数据,可以用select into代替create table,避免造成大量日志,提高速度;如果数据量不大,为了减轻系统表的资源,应该先创建表再插入。

24.如果使用临时表,则必须在存储过程结束时显式删除所有临时表,首先truncate table,然后drop table,以避免系统表的长期锁定。

25.尽量避免使用游标,因为游标的效率很差。如果游标操作的数据超过10000行,那么就要考虑重写了。

26.在使用基于游标的方法或临时表方法之前,应该先寻找一个基于集合的解决方案来解决问题。基于集合的方法通常更有效。

27.与临时表一样,游标也不是不可用的。对小型数据集使用FAST_FORWARD游标通常比其他逐行处理方法更好,尤其是在必须引用多个表来获取所需数据的情况下。Include " in the result set Total "的例程执行起来通常比使用游标更快。如果开发时间允许,基于光标的方法和基于集合的方法都可以尝试一下,看哪种效果更好。

28.在所有存储过程和触发器的开头设置SET NOCOUNT ON,在结尾设置SET NOCOUNT OFF。在执行完存储过程和触发器的每一条语句后,不需要向客户端发送DONE_IN_PROC消息。

29.尽量避免大的事务操作,提高系统的并发性。

30.尽量避免向客户端返回大量数据。如果数据量过大,要考虑对应的需求是否合理。

关键词:百万数据查询优化

优侠库网站每天提供最新最热门的安卓手机软件、游戏下载、分享热门手游资讯攻略教程、手机软件教程步骤,专注安卓手机游戏软件下载。

本文链接:http://www.123down.cn/gonglue/227190.html

攻略资讯
安卓软件