我的编程空间,编程开发者的网络收藏夹
学习永远不晚

挽救数据库性能的 30 条黄金法则

短信预约 信息系统项目管理师 报名、考试、查分时间动态提醒
省份

北京

  • 北京
  • 上海
  • 天津
  • 重庆
  • 河北
  • 山东
  • 辽宁
  • 黑龙江
  • 吉林
  • 甘肃
  • 青海
  • 河南
  • 江苏
  • 湖北
  • 湖南
  • 江西
  • 浙江
  • 广东
  • 云南
  • 福建
  • 海南
  • 山西
  • 四川
  • 陕西
  • 贵州
  • 安徽
  • 广西
  • 内蒙
  • 西藏
  • 新疆
  • 宁夏
  • 兵团
手机号立即预约

请填写图片验证码后获取短信验证码

看不清楚,换张图片

免费获取短信验证码

挽救数据库性能的 30 条黄金法则

挽救数据库性能的 30 条黄金法则

1.优化查询,应尽量避免全表扫描,应该在用于检索数据和排序数据的字段上建立索引,如where子句用于搜索,order by子句用于排序,所以在这两个子句涉及到的字段上需要建立索引。

2. 应该尽量避免在where子句中使用否定的操作符,如不等于(!=或)、否则数据库引擎将放弃使用索引而进行全表扫描。

3. 在尽量避免在where子句中使用或(or)作为连接条件,否则数据库引擎将放弃使用索引而进行全表扫描。

如下面的SQL语句可能会带来性能问题:

selectid,name,agefrompersons

wherename="Bill"orage >30

由于这条SQL语句使用了or,所以数据库引擎会进行全表扫描,为了避免全表扫描,可以将这条SQL语句改成下面的形式。

selectid,name,agefrompersonswherename="Bill"

unionall

selectid,name,agefrompersonswherenum=20

4. 应该尽量避免在where子句中使用null进行判断,否则数据库引擎将放弃使用索引而进行全表扫描。先看下面的SQL语句:

selectid,name,agefrompersonswhereageisnull

为了避免使用null,可以设置age字段的默认值为0,这样就可以通过下面的SQL语句达到同样的结果。

selectid,name,agefrompersonswhereage =

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

例如,下面的SQL语句执行的效率会非常低:

selectid,name,agefrompersonswherenamelike"%John%"

如果真想进行模糊查询,可以使用全文检索。

6. 在where子句中应尽量避免在字段中使用表达式(包括函数运算、算数运算等),否则据库引擎将放弃使用索引而进行全表扫描。例如,下面的SQL语句执行的性能比较差:

selectid,name,agefrompersons age /2>12

应该利用表达式变换,改成下面的形式:

selectid,name,agefrompersons age >2*12

或者干脆改成下面的形式:

selectid,name,agefrompersons age >24

7. 应尽量避免使用in和not in,否则也会导致全表扫描。

如并不推荐下面的写法:

selectid,name,agefrompersonswhereagein(22,23,24)

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

selectid,name,agefrompersonswhereagebetween22and24

selectid,name,agefrompersonswhereage =22

unionall

selectid,name,agefrompersonswhereage =26

unionall

selectid,name,agefrompersonswhereage =30

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

不使用索引的SQL语句:

selectid,name,agefrompersonswherename= @name

为了使用索引,可以改成下面强制使用索引的方式:

selectid,name,agefrompersonswith(index(name_index))wherename= @name

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

9. 尽量不要执行一些没意义的查询,如条件完全为false的查询:

selectid,name,ageintopersons1frompersonswhereage

这样的代码会返回一个空结果集,而且会大量消耗系统资源,如果真的想建一个空表,应该直接用create table语句。

10.如果使用的索引是符合索引,只有使用该符合索引的第1个字段作为条件时才能保证数据库引擎使用该符合索引,否则该符合索引不会被使用。并且应该尽可能让字段顺序与索引顺序一致。例如,name_index是first_name和last_name字段的符合索引,使用下面的SQL语句会使用该索引。

selectid,first_name,last_name

frompersons

wherefirst_name ="Bill"

11.如果非要在SQL语句中使用in,那么使用exists代替in是一个好主意:

selectid,numfromt

wherenumin(selectnumfromh)

应该用下面的SQL语句代替:

selectid,numformt

whereexists(selectfromhwherenum= t.num)

12.索引并不是在任何时候都有效,如果索引列有大量重复的数据,那么数据库引擎可能不会去利用索引。例如,SEX字段的值只有两种可能:male和female,可能这两个值各占一半,这样在SEX字段上建立索引就没有任何意义。

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

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

15.尽量按需返回字段和记录,例如:

selectid,name,agefrompersonswhereage >20

尽量如要使用“*”返回所有不需要的字段,也不需要一下就查询出所有的记录,如下面的SQL语句在数据量很大时查询效率是非常低的。

select*frompersons

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 ,以提高执行效率;如果数据量不大,为了缓和系统表的资源,应先createtable,然后使用insert插入数据。

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

25.尽量避免大事务操作,提高系统并发能力。

26.应尽量一次性插入多条数据,例如,使用下面的SQL语句性能会很低:

insertintopersons(id,name,age)values("Bill",24)

insertintopersons(id,name,age)values("Mike",26)

insertintopersons(id,name,age)values("John",20)

为了提升性能,可以一次性插入这3条记录。

insertintopersons(id,name,age)values("Bill",24),("Mike",26),("John",20)

27.如果不得不使用like进行模糊查询时,不要在关键字前面加%

反例:

selectid,name,agefrompersonswherenamelike"%abc%"

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

正例:

selectid,name,agefrompersonswherenamelike"abc%"

28.尽量用unionall代替union

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

29.尽量使用等值连接

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

先看下面的SQL语句:

selecta.id,a.name,b.id,b.namefromaleftjoinbona.id = b.id

selecta.id,a.name,b.id,b.namefromarightjoinbona.id = b.id

selecta.id,a.name,b.id,b.namefromainnerjoinbona.id = b.id

上面的3条SQL语句,前两条分别使用了左连接和右连接,而最后一条使用了内连接,经过实际运行,使用内连接的SQL语句的执行效率明显优于左连接和右连接。所以在能满足需求的前提下,应该尽可能使用内连接(等值连接)。

30.尽量用外连接来替换子查询。反例:

selectid,namefromawhereexists(selectidfrombwhereid>=10anda.product_id=b.product_id)

在上面的SQL语句中,数据库引擎会先对外表a执行全表查询,然后根据product_id逐个执行子查询,如果外层表(a表)中的数据非常多,查询性能会非常糟糕。所以应该将SQL语句改成下面的形式:

selectid,namefromainnerjoinbonA.product_id=b.product_idwhereb.id>=10

免责声明:

① 本站未注明“稿件来源”的信息均来自网络整理。其文字、图片和音视频稿件的所属权归原作者所有。本站收集整理出于非商业性的教育和科研之目的,并不意味着本站赞同其观点或证实其内容的真实性。仅作为临时的测试数据,供内部测试之用。本站并未授权任何人以任何方式主动获取本站任何信息。

② 本站未注明“稿件来源”的临时测试数据将在测试完成后最终做删除处理。有问题或投稿请发送至: 邮箱/279061341@qq.com QQ/279061341

挽救数据库性能的 30 条黄金法则

下载Word文档到电脑,方便收藏和打印~

下载Word文档

猜你喜欢

挽救数据库性能的 30 条黄金法则

1.优化查询,应尽量避免全表扫描,应该在用于检索数据和排序数据的字段上建立索引,如where子句用于搜索,order by子句用于排序,所以在这两个子句涉及到的字段上需要建立索引。 2. 应该尽量避免在where子句中使用否定的操作符,如不等于(!=或)、否则
挽救数据库性能的 30 条黄金法则
2014-09-27

MySQL性能飞跃:揭秘高效数据库优化的黄金法则

在创建数据库时,选择合适的存储引擎是至关重要的。InnoDB因其支持事务处理、行级锁定和外键约束而被广泛推荐。它提供了高性能和数据完整性的保证,适合需要处理大量短期事务的应用。

攻克数据库性能调优难题:5个必须掌握的黄金法则

数据库性能调优是一项复杂的工作,需要考虑多个因素。本文总结了5个数据库性能调优的黄金法则,帮助您快速提升数据库性能。
攻克数据库性能调优难题:5个必须掌握的黄金法则
2024-02-10

Oracle数据库调优实战:优化SQL查询的黄金法则!

本文将介绍Oracle数据库调优的黄金法则,帮助您提高SQL查询的性能和效率。
Oracle数据库2024-11-30

一致性哈希:数据分片与负载均衡的黄金法则

一致性哈希巧妙地将节点和数据映射到一个环状的哈希空间上。节点的哈希值确定了其在环上的位置,而数据的哈希值则找到了对应的环上位置。为了提高均衡性,一致性哈希引入了虚拟节点的概念,进一步优化了节点与数据的分布。

HTML 元数据优化:20 条黄金法则,让你的网站成为搜索引擎的宠儿

HTML 元数据是告知搜索引擎有关网站页面的关键信息,用以帮助搜索引擎对网页进行索引和排名,优化 HTML 元数据有助于提高网站在搜索结果中的可见性和排名,让网站成为搜索引擎的宠儿。
HTML 元数据优化:20 条黄金法则,让你的网站成为搜索引擎的宠儿
2024-02-03

编程热搜

目录