浅尝辄止谈 MySQL 中优化 SQL 语句询问常用之 30 栽方法

发布时间:2018-11-15  栏目:MySQL  评论:0 Comments

**1、对查询进行优化,应尽可能避免全表扫描,首先应考虑于
where 及 order by 涉及的列上建立目录。 

1.对准查询进行优化,应尽量避免全表扫描,首先应考虑在 where 及 order by
涉及的列上建立目录。

2、应尽可能避免在 where
子句被采用!=或<>操作符,否则用引擎放弃采用索引而进展全表扫描。 

2.许竭尽避免以 where
子句被使用!=或<>操作符,否则将引擎放弃下索引而进展全表扫描。

3、许竭尽避免在 where 子句被对字段进行 null
值判断,否则用招致发动机放弃采用索引而进行全表扫描,如: 
select id from t where num is null 
足以num上安默认值0,确保表中num列没有null值,然后这样查询: 
select id from t where num=0 

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 

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

5、下的询问也用招致全表扫描: 
select id from t where name like ‘%abc%’ 
假设要提高效率,可以考虑全文检索。 

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 

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
子句被利用参数,也会促成全表扫描。因为SQL只发在运转时才会分析局部变量,但优化程序不可知拿做客计划之挑推迟至运行时;它必须于编译时展开分选。然而,如果以编译时起访问计划,变量的价还是未知之,因而无法作为目录选择的输入项。如下面语句以开展全表扫描: 
select id from t where num=@num 
足更改也要挟查询利用索引: 
select id from t with(index(索引名)) where num=@num 

7.如果在 where
子句被使用参数,也会招全表扫描。因为SQL只出以运行时才见面分析局部变量,但优化程序不可知将做客计划的取舍推迟至运行时;它必须以编译时进行选择。然而,如果当编译时确立访问计划,变量的价值还是未知之,因而无法作为目录选择的输入项。如下面语句以展开全表扫描:
select id from t where
[email protected]
足变动也要挟查询利用索引:
select id from t with(index(索引名)) where
[email protected]

8、应竭尽避免在 where
子句被针对字段进行表达式操作,这将促成发动机放弃下索引而进展全表扫描。如: 
select id from t where num/2=100 
应改为: 
select id from t where num=100*2 

8.许竭尽避免以 where
子句被针对字段进行表达式操作,这将造成发动机放弃下索引而开展全表扫描。如:
select id from t where num/2=100
应改为:
select id from t where num=100*2

9、许尽量避免在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’ 

9.应尽可能避免以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’

10、无须当 where
子句被的“=”左边进行函数、算术运算或任何表达式运算,否则系统将可能无法正确运用索引。 

10.不用当 where
子句被之“=”左边进行函数、算术运算或其他表达式运算,否则系统将可能无法正确运用索引。

11、在用索引字段作为条件时,如果该索引是复合索引,那么必须动及该索引中之率先独字段作为规范时才能够保证系统使用该索引,否则该索引将不见面吃使用,并且应尽可能的于字段顺序及索引顺序相平等。 

11.在行使索引字段作为基准时,如果该索引是复合索引,那么得动及该索引中之首先单字段作为标准时才会保证系统使用该索引,否则该索引将无见面给应用,并且应尽量的被字段顺序与索引顺序相平等。

12、毫不写有从未意思之询问,如需要分外成一个空表结构: 
select col1,col2 into #t from t where 1=0 
立马类代码不见面回外结果集,但是会损耗系统资源的,应改成为这样: 
create table #t(…) 

12.并非写一些未曾意义的查询,如用分外成一个空表结构:
select col1,col2 into #t from t where 1=0
当下看似代码不会见返回外结果集,但是会吃系统资源的,应改化这么:
create table #t(…)

13、洋洋上用 exists 代替 in 是一个好之选项: 
select num from a where num in(select num from b) 
故而底的言辞替换: 
select num from a where exists(select 1 from b where num=a.num) 

13.多时节用 exists 代替 in 是一个吓之选择:
select num from a where num in(select num from b)
用脚的语句替换:
select num from a where exists(select 1 from b where num=a.num)

14、并无是怀有索引对查询都使得,SQL是冲表中数据来拓展询问优化的,当索引列有大量数据更时,SQL查询可能不会见去用索引,如一申明中出字段sex,male、female几乎每一半,那么即使在sex上建了目录也对查询效率起未了作用。 

14.并无是负有索引对查询都有效,SQL是依据表中数据来开展查询优化的,当索引列有大气多少再次时,SQL查询可能无见面去下索引,如一表中发出字段sex,male、female几乎各个一半,那么即便以sex上打了目录也本着查询效率起未了意向。

15、目录并无是越多越好,索引固然好增进相应的 select
的频率,但还要也降低了 insert 及 update 的频率,因为 insert 或 update
时有或会见重建索引,所以怎么建索引需要慎重考虑,视具体情况而自然。一个发明底索引数最好不要过6独,若太多则答应考虑有不常使用及之列上建的目录是否出必不可少。 

15.索唤起并无是越多越好,索引固然可以增进相应的 select
的效率,但还要也下降了 insert 及 update 的频率,因为 insert 或 update
时产生或会见重建索引,所以如何建索引需要慎重考虑,视具体情况而自然。一个申明的索引数最好不用超过6只,若太多则承诺考虑部分不常使用到的列上建之目是否生必不可少。

16、答应竭尽的免更新 clustered 索引数据列,因为 clustered
索引数据列的依次就是发明记录之物理存储顺序,一旦该列值改变将造成整个表记录之相继的调动,会损耗一定好的资源。若采用体系要数更新
clustered 索引数据列,那么用考虑是不是应以该索引建为 clustered 索引。 

16.许竭尽的免更新 clustered 索引数据列,因为 clustered
索引数据列的逐一就是说明记录之物理存储顺序,一旦该列值改变将招致整表记录之逐条的调动,会耗费一定好的资源。若使体系要数更新
clustered 索引数据列,那么用考虑是否应拿该索引建为 clustered 索引。

17、尽心尽力以数字型字段,若只包含数值信息之字段尽量不要设计啊字符型,这会稳中有降查询以及连接的属性,并会追加存储开销。这是为引擎在处理查询与连接时见面相继个比较字符串中每一个字符,而对于数字型而言就需要比相同潮就够了。 

17.尽量施用数字型字段,若单含有数值信息的字段尽量不要设计为字符型,这会降查询和连续的性,并会大增存储开销。这是盖引擎在处理查询以及连续时会相继个比字符串中各一个字符,而于数字型而言就需要比平软就足足了。

18、尽心尽力的使 varchar/nvarchar 代替 char/nchar
,因为首先变长字段存储空间小,可以节省存储空间,其次对于查询来说,在一个相对比小之字段内找频率斐然要后来居上数。 

18.尽可能的施用 varchar/nvarchar 代替 char/nchar
,因为首先变长字段存储空间小,可以节约存储空间,其次对于查询来说,在一个针锋相对比小之字段内找频率明显要大数。

19、外地方都毫无使 select * from t
,用现实的字段列表代替“*”,不要回用不顶的其余字段。 

19.其他地方还毫无使 select * from t
,用现实的字段列表代替“*”,不要回用不至之旁字段。

20、尽心尽力采用表变量来代替临时表。如果表变量包含大量数码,请小心索引非常有限(只有主键索引)。 

20.尽量采用表变量来取代临时表。如果表变量包含大量数,请留心索引非常少(只有主键索引)。

21、避频繁创建与去临时表,以压缩系统表资源的损耗。 

21.幸免频繁创建和去临时表,以调减系统表资源的消耗。

22、临时表并无是不足下,适当地采用她可以假设少数例程更使得,例如,当用重新引用大型表或常用表中的之一数集时。但是,对于一次性事件,最好用导出表。 

22.临时表并无是不行采用,适当地使它得以使某些例程更实惠,例如,当需要再行引用大型表或常用表中的某数集时。但是,对于一次性事件,最好以导出表。

23、每当新建临时表时,如果一次性插入数据量很要命,那么得应用 select
into 代替 create table,避免造成大量 log
,以提高速度;如果数据量不慌,为了缓和系统表的资源,应先create
table,然后insert。 

23.当新建临时表时,如果一次性插入数据量很特别,那么得使 select into
代替 create table,避免造成大量 log
,以增强速度;如果数据量不老,为了降温系统表的资源,应先create
table,然后insert。

24、假设采取到了临时表,在储存过程的最后要以享有的临时表显式删除,先
truncate table ,然后 drop table ,这样可避免系统表的较长时间锁定。 

24.比方用到了临时表,在囤过程的末梢得以装有的临时表显式删除,先
truncate table ,然后 drop table ,这样可免系统表的较长时间锁定。

25、尽量避免使用游标,因为游标的频率比差,如果游标操作的数据超过1万尽,那么尽管应该考虑改写。 

25.尽量避免使用游标,因为游标的效率比较差,如果游标操作的数额超过1万执行,那么就是相应考虑改写。

26、行使基于游标的章程或者临时表方法之前,应先行找基于集的化解方案来缓解问题,基于集的道一般还有效。 

26.动基于游标的措施要临时表方法之前,应事先物色基于集的缓解方案来缓解问题,基于集的方法一般还管用。

27、跟临时表一样,游标并无是不可动用。对小型数据集使用 FAST_FORWARD
游标通常如优于其它逐行处理办法,尤其是于得引用几独说明才能够博得所要的数码常常。在结果集中包括“合计”的例程通常如果比较采用游标执行之快快。如果出时间允许,基于游标的方式以及冲集的方法还可以品尝一下,看哪一样种艺术的功效还好。 

27.及临时表一样,游标并无是不足使用。对小型数据集使用 FAST_FORWARD
游标通常如优于外逐行处理方式,尤其是以必须引用几单说明才会取得所要的多寡时。在结果集中包括“合计”的例程通常如果于使用游标执行的速快。如果开时间允许,基于游标的点子及因集的点子都得以尝尝一下,看哪一样栽方法的功力还好。

28、以颇具的蕴藏过程以及触发器的初始处于安装 SET NOCOUNT ON
,在了时设置 SET NOCOUNT OFF
。无需以履行存储过程以及触发器的每个语句后往客户端发送 DONE_IN_PROC
消息。 

28.在具备的蕴藏过程以及触发器的起处于安装 SET NOCOUNT ON ,在截止时设置
SET NOCOUNT OFF 。无需于履行存储过程及触发器的每个语句后朝着客户端发送
DONE_IN_PROC 消息。

29、尽量避免向客户端返回大数据量,若数据量过怪,应该考虑相应要求是否合理。 

29.尽量避免向客户端返回大数据量,若数据量过十分,应该考虑相应要求是否成立。

30、尽量避免大事务操作,提高系统出现能力。**

30.尽量避免大事务操作,提高系统出现能力。

当网上发现了相同首好之章,但笔者不详,就厚着脸皮扒过来了,仅作个人学习用

http://www.bkjia.com/Mysql/352464.htmlwww.bkjia.comtruehttp://www.bkjia.com/Mysql/352464.htmlTechArticle1.对查询进行优化,应尽量避免全表扫描,首先应考虑在
where 及 order by 涉及的列上建立目录。 2.应尽量避免在 where
子句被行使!=或操作符,…

留下评论

网站地图xml地图