2018_04_16 数据库SQL优化方案(二)

原创 2018年04月16日 11:18:14

处理百万级以上的数据提高查询速度的方法:

(1)应尽量避免在 where 子句中使用!=或<>操作符,否则将引擎放弃使用索引而进行全表扫描。

(2)对查询进行优化,应尽量避免全表扫描,首先应考虑在 where 及 order by 涉及的列上建立索引。

(3)应尽量避免在 where 子句中对字段进行 null 值判断,否则将导致引擎放弃使用索引而进行全表扫描,如:
     select id from t where num is null
     可以在num上设置默认值0,确保表中num列没有null值,然后这样查询:

     select id from t where num=0

(4)应尽量避免在 where 子句中使用 or 来连接条件,否则将导致引擎放弃使用索引而进行全表扫描,如:
     select id from t where num=10 or num=20
     可以这样查询:
     select id from t where num=10
     union all

     select id from t where num=20

union和union all的用法及区别?

UNION 操作符用于合并两个或多个 SELECT 语句的结果集。

请注意,UNION 内部的 SELECT 语句必须拥有相同数量的列。列也必须拥有相似的数据类型。同时,每条 SELECT 语句中的列的顺序必须相同。

SQL UNION 语法

    
SELECT column_name(s) FROM table_name1
UNION
SELECT column_name(s) FROM table_name2

注释:默认地,UNION 操作符选取不同的值。如果允许重复的值,请使用 UNION ALL。

SQL UNION ALL 语法

SELECT column_name(s) FROM table_name1
UNION ALL
SELECT column_name(s) FROM table_name2

另外,UNION 结果集中的列名总是等于 UNION 中第一个 SELECT 语句中的列名。

下面的例子中使用的原始表:

Employees_China:

E_IDE_Name
01Zhang, Hua
02Wang, Wei
03Carter, Thomas
04Yang, Ming

Employees_USA:

E_IDE_Name
01Adams, John
02Bush, George
03Carter, Thomas
04Gates, Bill

使用 UNION 命令

实例

列出所有在中国和美国的不同的雇员名:

SELECT E_Name FROM Employees_China
UNION
SELECT E_Name FROM Employees_USA

结果

E_Name
Zhang, Hua
Wang, Wei
Carter, Thomas
Yang, Ming
Adams, John
Bush, George
Gates, Bill

注释:这个命令无法列出在中国和美国的所有雇员。在上面的例子中,我们有两个名字相同的雇员,他们当中只有一个人被列出来了。UNION 命令只会选取不同的值。

UNION ALL

UNION ALL 命令和 UNION 命令几乎是等效的,不过 UNION ALL 命令会列出所有的值。

SQL Statement 1
UNION ALL
SQL Statement 2

使用 UNION ALL 命令

实例:

列出在中国和美国的所有的雇员:

SELECT E_Name FROM Employees_China
UNION ALL
SELECT E_Name FROM Employees_USA

结果

E_Name
Zhang, Hua
Wang, Wei
Carter, Thomas
Yang, Ming
Adams, John
Bush, George
Carter, Thomas
Gates, Bill


(5)下面的查询也将导致全表扫描:(不能前置百分号)
     select id from t where name like ‘%abc%’

    若要提高效率,可以考虑全文检索。

(6)in 和 not in 也要慎用,否则会导致全表扫描,如:
     select id from t where num in(1,2,3)
     对于连续的数值,能用 between 就不要用 in 了:

     select id from t where num between 1 and 3

(7)应尽量避免在where子句中对字段进行函数操作,这将导致引擎放弃使用索引而进行全表扫描。如:
     select id from t where substring(name,1,3)=’abc’         –name以abc开头的id
     select id from t where datediff(day,createdate,’2005-11-30′)=0         –’2005-11-30′生成的id
     应改为:
     select id from t where name like ‘abc%’

     select id from t where createdate>=’2005-11-30′ and createdate<’2005-12-1′

(8)索引并不是越多越好,索引固然可以提高相应的 select 的效率,但同时也降低了 insert 及 update 的效率,因为 insert 或 update 时有可能会重建索引,所以怎样建索引需要慎重考虑,视具体情况而定。一个表的索引数最好不要超过6个,若太多则应考虑一些不常使用到的列上建的索引是否有 必要。

(9)尽量使用数字型字段,若只含数值信息的字段尽量不要设计为字符型,这会降低查询和连接的性能,并会增加存储开销。这是因为引擎在处理查询和连接时会逐个比较字符串中每一个字符,而对于数字型而言只需要比较一次就够了。

(10)尽可能的使用 varchar/nvarchar 代替 char/nchar ,因为首先变长字段存储空间小,可以节省存储空间,其次对于查询来说,在一个相对较小的字段内搜索效率显然要高些。

(11)任何地方都不要使用 select * from t ,用具体的字段列表代替“*”,不要返回用不到的任何字段。

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

(13)临时表并不是不可使用,适当地使用它们可以使某些例程更有效,例如,当需要重复引用大型表或常用表中的某个数据集时。但是,对于一次性事件,最好使用导出表。

(14)在新建临时表时,如果一次性插入数据量很大,那么可以使用 select into 代替 create table,避免造成大量 log ,以提高速度;如果数据量不大,为了缓和系统表的资源,应先create table,然后insert。

(15)如果使用到了临时表,在存储过程的最后务必将所有的临时表显式删除,先 truncate table ,然后 drop table ,这样可以避免系统表的较长时间锁定。

(16)尽量避免向客户端返回大数据量,若数据量过大,应该考虑相应需求是否合理。

(17)尽量避免大事务操作,提高系统并发能力。

数据库sql常见优化方案

为什么要优化:      随着实际项目的启动,数据库经过一段时间的运行,最初的数据库设置,会与实际数据库运行性能 会有一些差异,这时我们就需要做一个优化调整。 数据库优化这个课题较大,可分为四大类...
  • miachen520
  • miachen520
  • 2016-08-24 14:54:47
  • 2066

2018_04_16 数据库SQL优化方案(一)

(1)选择最有效率的表名顺序Oracle的解析器是按照从右到左的顺序处理From子句中的表名,From子句中写在最后的表(基础表)将被最先处理,当From子句中包含多个表的情况时,应该选择记录条数最少...
  • liujiaxin_lei
  • liujiaxin_lei
  • 2018-04-16 10:27:38
  • 47

数据库SQL优化大总结1之- 百万级数据库优化方案

转自:https://mp.weixin.qq.com/s?__biz=MzIxMjg4NDU1NA==&mid=2247483684&idx=1&sn=f5abc60e696b2063e43cd9c...
  • wuhuagu_wuhuaguo
  • wuhuagu_wuhuaguo
  • 2017-06-06 09:55:51
  • 7254

SQL语句优化方案

 1. 高效地进行SQL语句设计: 通常情况下,可以采用下面的方法优化SQL对数据操作的表现: (1)减少对数据库的查询次数,即减少对系统资源的请求,使用快照和显形图等分布式数据库对象可...
  • davistang
  • davistang
  • 2015-03-27 15:08:07
  • 3316

SQLServer 优化SQL语句:in 和not in的替代方案

原文出处:http://www.cnblogs.com/luoht/archive/2010/03/01/1676049.html用IN写出来的SQL的优点是比较容易写及清晰易懂,这比较适合现代软件开...
  • ylqmf
  • ylqmf
  • 2010-03-12 14:08:00
  • 3249

海量数据环境下的Oracle数据库系统架构设计和优化思路

  • 2016年02月18日 10:24
  • 2.62MB
  • 下载

大数据量下的SQL Server数据库自身优化

原文: http://www.d1net.com/bigdata/news/284983.html 1.1:增加次数据文件          从SQL SERVER 2005开始,数据库不...
  • ren6370
  • ren6370
  • 2017-06-25 18:47:47
  • 508

数据库SQL优化——使用EXIST代替IN

查询进行优化,应尽量避免全表扫描 对查询进行优化,应尽量避免全表扫描,首先应考虑在 where 及 order by 涉及的列上建立索引. 尝试下面的技巧以避免优化器错选了表扫描:· 使用...
  • SmuEdward
  • SmuEdward
  • 2017-01-18 16:02:30
  • 5146

Oracle 数据库性能优化3日实战(企业培训)

课程名称一: Oracle性能优化及调整 课程时长 1天 课程深度: 高级 上机实验: 10%-30% 授课对象: Oracle开发人员、Oracle数据库管理人员,应用程序开发人员 课程描述...
  • robinson_0612
  • robinson_0612
  • 2017-04-05 11:49:28
  • 2721

数据库优化操作及方案

MySQL数据量大时,优化可以进行的操作:MySQL分库分表,MySQL缓存,MySQL索引,MySQL优化查询语句? 数据库优化方案:缓存;数据库表的大字段剥离;恰当的使用索引;表库的拆分;字段冗余...
  • ShareUs
  • ShareUs
  • 2017-05-12 12:58:39
  • 1002
收藏助手
不良信息举报
您举报文章:2018_04_16 数据库SQL优化方案(二)
举报原因:
原因补充:

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