如何免费拥有一个自己的网站:挽救数据库性能的30条黄金法则
本文摘要: 1. 优化查询,应尽量防止全表扫描,应该在用于检索数据和排序数据的字段上建立索引,如where子句用于查找,order by子句用于排序,所以在这两个子句触及到的字段上需要建立索引。2. 应该防止在where子句中使用否定的操作符,如不
1. 优化查询,应尽量防止全表扫描,应该在用于检索数据和排序数据的字段上建立索引,如where子句用于查找,order by子句用于排序,所以在这两个子句触及到的字段上需要建立索引。

2. 应该防止在where子句中使用否定的操作符,如不等于(!=或<>)、不然数据库引擎将抛弃使用索引而进行全表扫描。

3. 在尽量防止在where子句中使用或(or)作为连接条件,不然数据库引擎将抛弃使用索引而进行全表扫描。如下面的SQL语句可能会带来性能问题
select id,name,age from persons where name = 'Bill' or age > 30

因为这条SQL语句使用了or,所以数据库引擎会进行全表扫描,为了防止全表扫描,可以将这条SQL语句改成下面的形式。
select id,name,age from persons where name = 'Bill'union allselect id,name,age from persons where num = 20

4. 应该尽量防止在where子句中使用null进行判断,不然数据库引擎将抛弃使用索引而进行全表扫描。先看下面的SQL语句:
select id,name,age from persons where age is null

为了防止使用null,可以设置age字段的默许值为0,这样就能够通过下面的SQL语句达到相同的成果。
select id,name,age from persons where age = 0

5. 尽量不用使用like检索数据,因为也会导致数据库引擎将抛弃使用索引而进行全表扫描。

例如,下面的SQL语句执行的功率会十分低:
select id,name,age from persons where name like '%John%'

假如真想进行模糊查询,能够使用全文检索。

6. 在where子句中应尽量防止在字段中使用表达式(包括函数运算、算数运算等),不然据库引擎将抛弃使用索引而进行全表扫描。例如,下面的SQL语句执行的性能比较差
select id,name,age from persons age / 2 > 12

应该使用表达式变换,改成下面的形式:
select id,name,age from persons age > 2 * 12

或者爽性改成下面的形式:
select id,name,age from persons age > 24


7. 应尽量防止使用in和not in,不然也会导致全表扫描。

如其实不引荐下面的写法:
select id, name,age from persons where age in (22,23,24)

假如数值是接连的,应该使用between,而不要用in,假如数值是不接连的,可以分红多个SQL,用union all连接查询成果。

select id,name,age from persons where age between 22 and 24select id,name,age from persons where age = 22union allselect id,name,age from persons where age = 26union allselect id,name,age from persons where age = 30


8. 应该尽量防止在where子句中使用参数,不然也将导致全表扫描。这是因为参数需要在SQL运转时才进行替换,而SQL优化(使用索引属于优化的一部分)是在编译时进行的。所以数据库引擎在检索到参数时,因为参数的详细指是未知的,所以也就无法优化了,当然也就无法使用索引了。

不使用索引的SQL语句:
select id,name,age from persons where name = @name

为了使用索引,可以改成下面强制使用索引的方式:
select id,name,age from persons with(index(name_index)) where name = @name

其间name_index是建立在name字段上的索引名。

9. 尽量不要执行一些没意义的查询,如条件完全为false的查询:
select id,name,age into persons1 from persons where age < 0

这样的代码会返回一个空成果集,并且会很多耗费体系资源,假如真的想建一个空表,应该直接用create table语句。

10. 假如使用的索引是契合索引,只有使用该契合索引的第1个字段作为条件时才干保证数据库引擎使用该契合索引,不然该契合索引不会被使用。并且应该尽量让字段顺序与索引顺序一致。例如,name_index是first_name和last_name字段的契合索引,使用下面的SQL语句会使用该索引。
select id,first_name,last_namefrom personswhere first_name = 'Bill'

11. 假如非要在SQL语句中使用in,那么使用exists替代in是一个好主意:
select id,num from t where num in (select num from h)

应该用下面的SQL语句替代:
select id,num form twhere exists(select 0 from h where num = t.num)

12. 索引其实不是在任何时分都有用,假如索引列有很多重复的数据,那么数据库引擎可能不会去使用索引。例如,sex字段的值只有两种可能:male和female,可能这两个值各占一半,这样在sex字段上建立索引就没有任何意义。

13. 能使用数值型字段就使用数值型字段。因为比较数值型字段的功率要远比字符型字段的功率高,这是因为比较字符型的值,要一个字母一个字母地比较,而数值型的值,只是比较一个数。所以假如只包括数值信息的值,应该尽量使用数值类型的字段。例如,age、salary等。

14. 应尽量防止使用固定长度的字段,如char、nchar。使用可变长度的字段是一个十分好的选择。因为可变长度字段占用的空间是按需分配的,所以占用空间比较少。关于查询来说,毫无疑问,当然是占用空间小的字段的查询功率更高了。

15. 尽量按需返回字段和记载,例如:
select id,name,age from persons where age > 20

尽量防止使用“*”返回所有不需要的字段,也不需要一下就查询出所有的记载,如下面的SQL语句在数据量很大时查询功率对错常低的。
select * from persons

16. 索引有利有弊,添加索引,可以提高select的执行功率,但支付的价值是在进行insert和update操作时,可能会下降功率。因为进行insert和update操作时通常需要重建索引。所以在一个表中其实不是索引越多越好。我的建议如下:(1)假如一个表大大都时进行的是select操作,那么索引多一些大大都时分确实可以提高性能,但这有一个条件,就是不能频频进行insert和update操作。(2)一个表中的索引数不能太多,最好不要超过6个,不然就好考虑优化一下数据库了。

17. 应尽量的防止更新 clustered 索引数据列,因为 clustered 索引数据列的顺序就是表记载的物理存储顺序,一旦该列值改变将导致整个表记载的顺序的调整,会消耗适当大的资源。若应用体系需要频频更新 clustered 索引数据列,那么需要考虑是否应将该索引建为 clustered 索引。

18. 应尽量防止向客户端返回大理数据,假如数据量过大,应该改变一下需求,或选用分页返回的方式,如使用MySQL中的limit子句现在返回的数据。

19. 尽量防止使用游标,因为游标的功率较差,假如游标操作的数据超过1万行,那么就应该选用其他方案。

20. 使用基于游标的方法或暂时表方法之前,应先寻找基于数据集的解决方案来解决问题,基于数据集的方法通常更有用。

21. 假如使用到了暂时表,在存储过程的终究务必将所有的暂时表显式删除,先用 truncate table清除表中的数据 ,然后 用drop table完全删除物理表 ,这样可以防止体系表的较长时间锁定。

22. 防止频频创建和删除暂时表,以减少体系表资源的耗费。

23. 在新建暂时表时,假如一次性刺进的数据量很大,那么能够使用 select into 替代 create table,防止形成很多 log ,以提高执行功率;假如数据量不大,为了平缓体系表的资源,应先create table,然后使用insert刺进数据。

24. 在所有的存储过程和触发器的开始处设置 SET NOCOUNT ON ,在完毕时设置 SET NOCOUNT OFF 。无需在执行存储过程和触发器的每一个语句后向客户端发送 DONE_IN_PROC 音讯。

25. 尽量防止大事务操作,提高体系并发能力。

26. 应尽量一次性刺进多条数据,例如,使用下面的SQL语句性能会很低:
insert into persons(id,name,age) values('Bill',24)insert into persons(id,name,age) values('Mike',26)insert into persons(id,name,age) values('John',20)

为了提高性能,可以一次性刺进这3条记载。
insert into persons(id,name,age) values('Bill',24),('Mike',26),('John',20)

27. 假如不能不使用like进行模糊查询时,不要在要害字前面加%。

反例:
select id,name,age from persons where name like '%abc%'

假如在要害字前面加%,那么查询是肯定要走全表查询的。

正例:
select id,name,age from persons where name like 'abc%'

28. 尽量用union all替代union

union和union all的差异主要是前者需要将两个(或者多个)成果集合并后再进行仅有性过滤操作,这就会触及到排序,添加很多的cpu运算,加大资源耗费及延迟。所以当我们可以确认不可能呈现重复成果集或者不在乎重复成果集的时分,尽量使用union all而不是union。

29.尽量使用等值连接

等值连接就是inner join,也称为内联进,而left join和right join是外连接。

先看下面的SQL语句
select a.id,a.name,b.id,b.name from a left join b on a.id = b.idselect a.id,a.name,b.id,b.name from a right join b on a.id = b.idselect a.id,a.name,b.id,b.name from a inner join b on a.id = b.id

上面的3条SQL语句,前两条分别使用了左连接和右连接,而终究一条使用了内连接,通过实践运转,使用内连接的SQL语句的执行功率显着优于左连接和右连接。所以在能满足需求的条件下,应该尽量使用内连接(等值连接)。

30. 尽量用外连接来替换子查询反例
select id,name from a where exists (select id from b where id>=10 and a.product_id=b.product_id)


在上面的SQL语句中,数据库引擎会先对表面a执行全表查询,然后依据product_id逐个执行子查询,假如外层表(a表)中的数据十分多,查询性能会十分糟糕。所以应该将SQL语句改成下面的形式:
select id,name from a inner join b on A.product_id=b.product_id where b.id>=10

【免责声明】本文仅代表作者或发布者个人观念,不代表(www.lmnkf.cn)及其所属公司官方发声,对文章观念有疑义请先联络作者或发布者自己修正,若内容触及侵权或违法信息,请先联络发布者或作者删除,若需我们协助请联络平台管理员,Emailcxb5918(本平台不支撑其他投诉反馈渠道,谢谢合作)。若需要学习以上相关常识请到巨推学院观看视频教程,网站地址www.tsllg.cn。

相关内容