欧易

欧易(OKX)

国内用户最喜爱的合约交易所

火币

火币(HTX )

全球知名的比特币交易所

币安

币安(Binance)

全球用户最多的交易所

挽救数据库性能的30条黄金法则|原力计划

时间:2022-10-10 18:11:53 | 浏览:2204

作者 | geekguy责编 | 王晓曼出品 | CSDN博客1. 优化查询,应尽量避免全表扫描,应该在用于检索数据和排序数据的字段上建立索引,如where子句用于搜索,order by子句用于排序,所以在这两个子句涉及到的字段上需要建立索

作者 | geekguy

责编 | 王晓曼

出品 | CSDN博客

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 all

select id,name,age from persons where num = 20

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

select id,name,age from persons where age is

为了避免使用,可以设置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 24

select id,name,age from persons where age = 22

union all

select id,name,age from persons where age = 26

union all

select 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_name

from persons

where 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 ,以提高执行效率;如果数据量不大,为了缓和系统表的资源,应先createtable,然后使用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.尽量用unionall代替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.id

select a.id,a.name,b.id,b.name from a right join b on a.id = b.id

select 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

版权声明:本文为CSDN博主「geekguy」的原创文章,遵循CC 4.0 BY-SA版权协议,转载请附上原文出处链接及本声明。

原文链接:

https://blog.csdn.net/nokiaguy/article/details/106330867

☞中国 AI 应用元年来了!

☞年仅 5 岁的 Rust 如何成为最受欢迎的编程语言?

☞15 岁黑进系统,发挑衅邮件意外获 Offer,不惑之年捐出全部财产,Twitter CEO 太牛了!

☞避坑!使用 Kubernetes 最易犯的 10 个错误

☞必读!53个Python经典面试题详解

☞赠书 | 1月以来 Tether 增发47亿 USDT,美元都去哪儿了?

相关资讯

数据库:什么是数据库,数据库管理系统,数据库系统,数据库管理员?

数据库 : 数据库(DataBase 简称 DB)就是信息的集合或者说数据库是由数据库管理系统管理的数据的集合。数据库管理系统 : 数据库管理系统(Database Management System 简称 DBMS)是一种操纵和管理数据库

数据库超详细讲解,MySQL数据库的简介、及常用数据库介绍

数据库简介数据库:顾名思义,就是数据的仓库,它是长期存储在计算机内,有组织的、可共享的数据的集合。数据库管理系统(DBMS: 用来对数据进行存储、管理等操作的软件)数据库分类数据库通常分为:层次式数据库、网络式数据库和关系式数据库三种。而不

什么是数据库DataBase?数据库和数据记录的概念简单讲解

大家好,在讲数据库之前,我用了很长的时间,和大家分享了很多VBA方面的一些知识点,其中很多是我个人对VBA的理解。从这讲开始我们要在原先的基础上深入的讲解一些VBA的提高利用,就是我们要开始讲解的数据库。在《VBA与数据库利用》中我会讲解到

挽救数据库性能的30条黄金法则|原力计划

作者 | geekguy责编 | 王晓曼出品 | CSDN博客1. 优化查询,应尽量避免全表扫描,应该在用于检索数据和排序数据的字段上建立索引,如where子句用于搜索,order by子句用于排序,所以在这两个子句涉及到的字段上需要建立索

数据库看这一篇就够了!MySQL、Redis、Mongodb等常见数据库教程

数据库是系统健康和用户行为健康的重要指标。数据库中的异常行为可能会引起应用程序中的问题。或者当应用程序中存在异常时,都可以使用数据库指标来帮助加快调试过程。先来认识下市面上常用的数据库:关系型数据库1. MySQL数据库2. Microso

为什么要使用数据库,什么是数据库

1、为什么要使用数据库持久化(persistence):把数据保存到可掉电式存储设备中以供之后使用。持久化的大多数时候是将内存中的数据存储在数据库中,当然也可以存储在磁盘文件、XML数据文件中。方便管理数据(例如:快速的检索等)2、什么是数

重塑数据库发展路径业界聚焦我国数据库产业发展

人民网北京12月27日电 (记者乔雪峰)近日发布的《软件和信息技术服务业十四五规划》中,明确提出我国“十四五”时期要加快实施国家软件发展战略,不断提升软件产业创新活力,聚力攻坚基础软件,有效满足多层次、多样化市场需求,为构建以国内大循环为主

常见的数据库类型及各种数据库特点的简单介绍

大家好,在上一讲中,讲了数据库的概念及表的概念,通过上讲的内容,我们大概了解了一些数据库的基本知识。其实,在我的日常生产中,数据库和我们也是息息相关的,当我们打电话、上网、去银行交易等等,都要访问不同的数据库,这些数据库各有自己的特点,但都

数据库篇-第一章:数据库基本概念

基础知识学习,面试必备,关注吧 骚年01 第一,什么是数据库?维基百科上是这样定义的:所谓“数据库”是以一定方式储存在一起、能予多个用户共享、具有尽可能小的冗余度、与应用程序彼此独立的数据集合。一个数据库由多个表空间(Tablespace)

C++基础语法梳理:数据库!带你深入浅出了解数据库

基本概念数据(data):描述事物的符号记录称为数据。数据库(DataBase,DB):是长期存储在计算机内、有组织的、可共享的大量数据的集合,具有永久存储、有组织、可共享三个基本特点。数据库管理系统(DataBase Management

数据库指南:一文让你浅显易懂地了解数据库

在数据分析的技能中,数据库与SQL会是性价比最高的技能之一。数据库是逻辑上的概念,它是一堆互相关联的数据,放在物理实体上,是一堆写在磁盘上的文件,文件中有数据。这些最基础的数据组成了表(table),我们把它想象成一张Excel的sheet

数据库技术新版图-Serverless数据库

数据库的发展已走过近四十年,作为基础软件之一,数据库称得上是一个“古老”的领域。而随着新技术的涌现,这个传统的领域也正不断焕发出新的生机。如果说云时代的到来推动了数据库的变革,那么,与 Serverless 的结合,则再次为数据库的发展添了

什么是数据库?用最简单的方法讲明白数据库

数据库基本概念数据库是一个以某种有组织的方式存储的数据集合。数据库(database)是保存有组织的数据的容器。数据库管理系统(DBMS)是一种数据库软件,MySQL是一种DBMS,即它是一种数据库软件,作者使用的数据库管理系统是MySQL

一文读懂Access数据库,从此不用Access数据库

1992年11月,Microsoft Access 1.0版本发布。同时,这也是Access数据库,第一次进入大家的视野。起初,Access的原名并不叫Access,而叫Cirrus。Ciruus开发于Visual Basic之前,当时的窗

闲聊数据库发展历史三个阶段、分类、数据库规范及趋势

概述数据库(Database)是存储与管理数据的软件系统,就像一个存入数据的物流仓库。在商业领域,信息就意味着商机,取得信息的一个非常重要的途径就是对数据进行分析处理,这就催生了各种专业的数据管理软件,数据库就是其中的一种。当然,数据库管理

友情链接

网址导航 SEO域名抢注宝宝起名网妈妈知道币圈宠物猪品种网安溪铁观音官网电影票资讯网餐饮加盟网澳大利亚旅游网雅安新闻头条网今日烟台绍兴黄酒资讯网马蜂窝旅游攻略网长城坦克评测网滑板车资讯网燃气灶品牌网瑜伽资讯网黄金大盘走势今日合肥
禧六福珠宝-深圳市禧六福珠宝有限公司集中运营、研发、设计等诸多优势,致力于将珠宝消费普及化,以产业为背景、以人文为内涵、以创意为灵魂,禧六福珠宝率先将中国传统的“福”文化概念引进到珠宝首饰每个细节。禧六福珠宝每一件首饰产品、每一个营销设计理念都讲究原创性和独特性。
禧六福珠宝 fengyuege.cn ©2022-2028版权所有