总结52条SQL语句性能优化策略,千万要收藏好

  • A+
所属分类:体育平台

总结52条SQL语句性能优化策略,千万要收藏好

总结了52条SQL语句性能优化策略,希望对您有所帮助,文章有点长,一次没有阅读完的话,可以收藏起来,有空的时候,拿出来慢慢研究,如果有不对的地方,也欢迎留言区交流学习,大家一起共同进步。

下面是正文开始。

1、优化查询,尽量避免全表扫描,首先,考虑在where和order by涉及的列上建立索引。

2、尽量避免where子句中字段的空值判断。

创建表时,NULL是默认值,但大多数时候,您应该使用NOT NULL,或者使用一个特殊的值,比如0,-1作为默认值。

3、尽量避免在 where 子句中使用 != 或 <> 运算符。MySQL 仅对以下运算符使用索引:<、<=、=、>、>=、BETWEEN、IN,有时还有 LIKE。

4、尽量避免在where子句中使用in来连接条件,否则会导致引擎放弃使用索引而进行全表扫描。

您可以使用 UNION 来合并查询:select id from t where num=10 union all select id from t where num=20。

5、in和notin要慎用,否则会造成全表扫描。对于连续值,如果可以使用 between,则不要使用 in:从 t 中选择 id,其中 num 介于 1 和 3 之间。

6、下面的查询也会导致全表扫描:select id from t where name like '%abc%' 或者 select id from t where name like '%abc' 如果想提高效率,可以考虑full-文本搜索。

并从 t 中选择 id,其中像 ‘abc%’ 这样的名称仅使用索引。

7、如果在where子句中使用参数,也会引起全表扫描。

8、应尽量避免对where子句中的字段进行表达式操作,应尽量避免对where子句中的字段进行函数操作。

9、在很多情况下,使用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)。

10、索引虽然可以提高对应select的效率,但是,也会降低insert和update的效率,因为insert或者update的时候可能会重建索引,所以,如何建索引需要慎重考虑,具体取决于具体情况。

一个表的索引个数不要超过6个,如果太多,就要考虑是否需要在不常用的列上建索引。

11、尽量避免更新聚簇索引数据列,因为聚簇索引数据列的顺序就是表记录的物理存储顺序。

一旦列值发生变化,整个表中记录的顺序就会发生调整,这会消耗相当大的资源。如果应用系统需要频繁更新聚集索引数据列,那么,就需要考虑是否应该将索引构建为聚集索引。

12、尽量使用数字字段,尽量不要将只包含数字信息的字段设计成字符类型,这样会降低查询和连接的性能,增加存储开销。

13、尽量使用varchar/nvarchar代替char/nchar,因为变长字段的存储空间小,可以节省存储空间。

其次,对于查询而言,在相对较小的领域内的搜索效率明显更高。

14、最好不要用“”返回全部:从t中选择,使用特定的字段列表代替“*”,不要返回任何未使用的字段。

15、尽量避免向客户端返回大量数据。如果数据量过大,就要考虑相应的需求是否合理。

16、使用表别名(Alias):在SQL语句中连接多个表时,请使用表别名,并在每一列上加上别名前缀。这样可以减少解析时间,减少Column歧义引起的语法错误。

17、使用“临时表”临时存储中间结果:

简化 SQL 语句的一个重要方法是使用临时表来临时存储中间结果,但临时表的好处远不止这些。

临时结果临时存放在临时表中,后续查询在tempdb中,可以避免程序中的多次扫描主表也大大减少了程序执行过程中的“共享锁”阻塞和“更新锁”,减少了阻塞,并提高并发性能。

18、一些SQL查询语句应该加入Nolock。读和写会互相阻塞。为了提高并发性能,可以在一些查询中加入nolock,这样可以在读的时候允许写,但缺点是可能读了不提交脏数据。

使用 nolock 有 3 个原则:

  • 如果查询结果用于“插入、删除、修改”,则不能添加nolock;
  • 查询表属于频繁分页,慎用nolock;
  • 临时表可以用来保存“数据前影”,其功能类似于Oracle的undo表空间。临时表可以用来提高并发性能,不要用nolock。

19、常见的简化规则如下:

不要超过5个表连接(JOIN),考虑使用临时表或表变量来存储中间结果。谨慎使用子查询,视图嵌套不应太深。通常,视图的嵌套不应超过两个。

20、将需要查询的结果预先计算好放入表中,查询时再选择。这是SQL7.0之前最重要的方法,比如医院住院费的计算。

21、OR的写法可以分解成多个查询,多个查询可以通过UNION连接起来。它们的速度只与是否使用索引有关。如果查询需要使用联合索引,UNION all 的执行效率更高。

多个 OR 子句不使用索引,因此以 UNION 的形式重写并尝试匹配索引。一个关键问题是是否使用索引。

22、在IN后面的值列表中,将出现频率最高的值放在最上面,将出现次数最少的值放在最后,减少判断次数。

23、尽量把数据处理放在服务器上,减少网络开销,比如使用存储过程。

存储过程是经过编译、优化、组织成执行计划并存储在数据库中的 SQL 语句。它是控制流语言的集合,当然速度也很快。

对于重复执行的动态SQL,可以使用临时存储过程,将进程(临时表)放在Tempdb中。

24、当服务器有足够的内存时,配置线程数=最大连接数+5,这样可以最大化效率;否则,使用配置线程数<最大连接数启用SQL SERVER线程池来解决问题,如果还是数量=最大连接数+5,严重损害服务器性能。

25、查询关联的写法:

select a.personMemberID, * from chineseresume a,personmember b where personMemberID = b.referenceid and a.personMemberID = ‘JCNPRH39681’ (A = B ,B = ‘number’)
select a.personMemberID, * from chineseresume a,personmember b where a.personMemberID = b.referenceid and a.personMemberID =’JCNPRH39681' and b.referenceid =’JCNPRH39681' (A = B ,B =’number’, A = ‘ Number’)
select a.personMemberID, * from chineseresume a,personmember b where b.referenceid = ‘JCNPRH39681’ and a.personMemberID = ‘JCNPRH39681’ (B = ‘Number’, A = ‘Number’)

26、尽量使用exists而不是select count(1)来判断是否有记录。count函数只在对表中所有行进行计数时使用,count(1)比count(*)效率更高。

27、尝试使用“>=”而不是“>”。

28、指标使用标准:

应结合应用程序考虑创建索引。建议大型OLTP表不要超过6个索引;

尽量使用索引字段作为查询条件,尤其是聚集索引。如果需要,可以使用 index index_name 来强制指定索引;

查询大表时避免表扫描,必要时考虑新建索引;

使用索引字段作为条件时,如果索引是联合索引,那么必须以索引中的第一个字段作为条件,保证系统使用该索引,否则不会使用该索引;

注意索引的维护,定期重建索引,重新编译存储过程。

29、以下SQL条件语句中的列都正确索引,但执行速度很慢:

SELECT * FROM record WHERE substrINg(card_no,1,4)=’5378’ (13 seconds)
SELECT * FROM record WHERE amount/30< 1000 (11 seconds)
SELECT * FROM record WHERE convert(char(10),date,112)=’19991201’ (10 seconds)

分析:

WHERE 子句中对列的任何操作的结果都是在 SQL 运行时逐列计算的,因此它必须在不使用该列的索引的情况下执行表搜索。

如果在编译查询时能得到这些结果,就可以通过SQL优化器进行优化,使用索引,避免查表,所以将SQL改写如下:

SELECT * FROM record WHERE card_no like ‘5378%’ (< 1 second)
SELECT * FROM record WHERE amount< 1000*30 (< 1 second)
SELECT * FROM record WHERE date= ‘1999/12/01’ (< 1 second)

30、当有批量插入或更新时,使用批量插入或批量更新,从不更新每条记录。

31、在所有存储过程中,都可以使用SQL语句,我绝不会使用循环来实现。

例如:要列出上个月的每一天,我将使用connect by来递归查询,我永远不会使用从上个月的第一天到最后一天的循环。

32、选择最有效的表名顺序(仅在基于规则的优化器中有效):

Oracle 的解析器按从右到左的顺序处理 FROM 子句中的表名。最后在FROM子句中写入的表(基本表驱动表)会先处理,在FROM子句中包含多个表。在这种情况下,必须选择记录数最少的表作为基表。

如果表连接查询超过3个,则需要选择交表作为基表。交叉表是指被其他表引用的表。

33、通过在GROUP BY之前过滤掉不需要的记录来提高GROUP BY语句的效率。以下两个查询返回相同的结果,但第二个查询显然要快得多。

低效:

SELECT JOB, AVG(SAL)
FROM EMP
GROUP BY JOB
HAVING JOB =’PRESIDENT’
OR JOB =’MANAGER’

高效的:

SELECT JOB, AVG(SAL)
FROM EMP
WHERE JOB =’PRESIDENT’
OR JOB =’MANAGER’
GROUP BY JOB

34、SQL语句使用大写,因为Oracle总是先解析SQL语句,然后将小写字母转换为大写字母再执行。

35、别名的使用。别名是大型数据库的一种应用技术。查询中表名和列名用字母作别名,查询速度比建连接表快1.5倍。

36、避免死锁。在存储过程和触发器中始终以相同的顺序访问同一个表;交易尽量短,交易涉及的数据量尽量减少;从不等待用户在交易中输入。

37、 避免使用临时表。除非你真的需要它们,否则你应该尽量避免使用临时表。相反,您可以使用表变量;大部分时间(99%),表变量驻留在内存中,所以速度比临时表快,临时表驻留在TempDb数据库中,所以对临时表的操作需要跨库通信,速度为自然慢。

38、最好不要使用触发器:

触发触发器,执行触发器事件本身就是一个消耗资源的过程;

如果可以使用约束来实现,尽量不要使用触发器;

不要对不同的触发事件(插入、更新和删除)使用相同的触发器;

不要在触发器中使用事务代码。

39、索引创建规则:

  • 表的主键和外键必须有索引;
  • 超过300个数据量的表应该有索引;
  • 对于经常与其他表连接的表,应在连接字段上建立索引;
  • Where子句中经常出现的字段,尤其是大表的字段,应该进行索引;
  • 索引应该建立在高度选择性的字段上;
  • 索引应该建立在小字段上。不要为大文本字段甚至长字段建立索引;
  • 建立复合索引需要仔细分析,尽量考虑用单字段索引代替;
  • 正确选择复合索引中的主列字段,一般是选择性较好的字段;
  • AND模式下,一个复合索引的多个字段是否经常同时出现在Where子句中?是否有很少或没有单字段查询?如果是这样,你可以建立一个复合索引;否则,考虑单字段索引;
  • 如果复合索引中包含的字段经常单独出现在Where子句中,则会分解为多个单字段索引;
  • 如果复合索引包含3个以上的字段,则慎重考虑其必要性,考虑减少复合字段;
  • 如果这些字段上既有单字段索引又有复合索引,一般可以删除复合索引;
  • 不要为频繁执行数据操作的表建立过多的索引;
  • 删除无用索引,避免对执行计划产生负面影响;
  • 在表上创建的每个索引都会增加存储开销,索引也会增加插入、删除和更新操作的处理开销。另外,复合索引太多,在单字段索引的情况下,一般没有价值;反之,在数据增删时也会降低性能,尤其是对于频繁更新的表,负面影响更大。
  • 尽量不要索引数据库中包含大量重复值的字段。

40、MySQL查询优化总结:

使用慢查询日志查找慢查询,使用执行计划来判断查询是否正常运行,始终测试您的查询,看看它们是否运行在最佳状态。

性能总是会随着时间而改变。避免在整个表上使用 count(*)。它可能会锁定整个表并保持查询一致,以便后续类似的查询可以使用查询缓存。在适当的情况下使用 GROUP BY 而不是 DISTINCT。

在 WHERE、GROUP BY 和 ORDER BY 子句中使用索引列,保持索引简单,不要在多个索引中包含同一列。

有时 MySQL 会使用错误的索引。在这种情况下,使用 USE INDEX 并检查使用 SQL_MODE=STRICT 的问题。对于少于 5 条记录的索引字段,在 UNION 时使用 LIMIT 而不是 OR。

为了避免更新前的SELECT,使用INSERT ON DUPLICATE KEY或INSERT IGNORE,不要使用UPDATE来实现,不要使用MAX,使用索引字段和ORDER BY子句,LIMIT M,N在某些情况下,实际上可以减慢查询速度, 谨慎使用。

在 WHERE 子句中使用 UNION 而不是子查询。重启MySQL后记得预热数据库,保证数据在内存中,查询速度快。考虑持久连接而不是多个连接,减少开销。

基准查询包括使用服务器上的负载。有时,一个简单的查询会影响其他查询。当服务器上的负载增加时,使用 SHOW PROCESSLIST 查看缓慢和有问题的查询。在开发环境生成的镜像数据中测试所有可疑查询。

41、MySQL备份过程:

  • 从辅助复制服务器备份;
  • 备份时停止复制,避免数据依赖和外键约束不一致;
  • 完全停止 MySQL 并从数据库文件备份;
  • 如果使用MySQL dump进行备份,请同时备份二进制日志文件——确保复制不被中断;
  • 不要相信LVM快照,这很可能造成数据不一致,给你以后带来麻烦;
  • 为了更容易进行单表恢复,以表为单位导出数据——如果数据与其他表隔离。
  • 使用mysqldump时请使用-opt;
  • 备份前对表进行检查和优化;
  • 为了更快地导入,在导入过程中暂时禁用外键约束。;
  • 为了更快的导入,导入时暂时关闭唯一性检测;
  • 计算每次备份后的数据库、表和索引的大小,以便更好地监控数据大小的增长;
  • 通过自动调度脚本监控复制实例的错误和延迟;
  • 定期执行备份。

42、查询缓冲区不会自动处理空格。因此,在编写SQL语句时,应尽量减少空格的使用,尤其是在SQL的开头和结尾(因为查询缓冲区不会自动截取首尾空格)。

43、会员使用mid作为分表查询的标准方便吗?一般业务需求基本都是以用户名作为查询依据。通常,用户名应该用作哈希模数来划分表。

在分表的情况下,MySQL的分区函数就是这样做的,对代码是透明的;在代码级别实现它似乎不合理。

44、我们应该为数据库中的每个表设置一个ID作为其主键,最好是INT类型(推荐UNSIGNED),并设置自动添加的AUTO_INCREMENT标志。

45、在所有存储过程和触发器的开头设置 SET NOCOUNT ON,并在结尾设置 SET NOCOUNT OFF。执行完存储过程和触发器的每个语句后,无需向客户端发送 DONE_IN_PROC 消息。

46、MySQL查询可以启用高速查询缓存。这是提高数据库性能的有效 MySQL 优化方法之一。当多次执行同一个查询时,从缓存中提取数据并直接从数据库返回数据要快得多。

47、EXPLAIN SELECT查询用于跟踪查看效果:

使用 EXPLAIN 关键字让您知道 MySQL 如何处理您的 SQL 语句。这可以帮助您分析查询或表结构的性能瓶颈。EXPLAIN 查询结果还将告诉您如何使用索引主键,如何搜索和排序数据表。

48、当只需要一行数据时使用LIMIT 1:

当您查询一段时间的表时,你已经知道只会有一个结果,但是,因为您可能需要获取游标,或者您可能会检查返回的记录数。

在这种情况下,添加 LIMIT 1 可以提高性能。这样MySQL数据库引擎在找到一条数据后就会停止搜索,而不是继续寻找下一条与记录匹配的数据。

49、为表选择合适的存储引擎:

myisam:主要应用是读和插入操作,只有少量的更新和删除,对事务的完整性和并发性要求不是很高。

InnoDB:事务处理,并发条件下需要数据一致性。除了插入和查询之外,还包括许多更新和删除。(InnoDB 有效减少了删除和更新导致的锁)。

对于支持事务的InnoDB类型的表,影响速度的主要原因是AUTOCOMMIT的默认设置是开启的,程序没有显式调用BEGIN启动事务,导致每次插入都自动提交,这严重影响速度。

可以在执行SQL前调用begin,多条SQL组成一个事务(即使开启了autocommit),性能会大大提升。

50、优化表的数据类型,选择合适的数据类型:

原则:通常越小越好,越简单越好,所有字段必须有默认值,尽量避免为空。

例如:设计数据库表时,尽量使用较小的整数类型,以占用较小的磁盘空间。(mediumint 比 int 更合适)

例如时间字段:datetime和timestamp,datetime占8个字节,timestamp占4个字节,只占一半,timestamp范围1970-2037适合更新时间

MySQL 可以很好地支持大量数据的访问,但一般来说,数据库中的表越小,在其上执行查询的速度就越快。

因此,在创建表时,为了获得更好的性能,我们可以将表中字段的宽度设置得尽可能小。

例如:定义邮政编码字段时,如果设置为CHAR(255),显然给数据库增加了不必要的空间。即使使用 VARCHAR 也是多余的,因为 CHAR(6) 可以很好地完成任务。

同样,如果可能的话,我们应该使用MEDIUMINT而不是BIGIN来定义整数字段,并且我们应该尝试将字段设置为NOT NULL,以便数据库在以后执行查询时不需要比较NULL值。

对于某些文本字段,例如“省”或“性别”,我们可以将它们定义为 ENUM 类型。因为在 MySQL 中,ENUM 类型被视为数字数据,数字数据的处理速度比文本类型快得多。这样,我们就可以提高数据库的性能。

51、字符串数据类型:char、varchar、文本选择区别。

52、对列的任何操作都会引起表扫描,包括数据库函数、计算表达式等,查询时尽量将操作移到等号的右边。

发表评论

:?: :razz: :sad: :evil: :!: :smile: :oops: :grin: :eek: :shock: :???: :cool: :lol: :mad: :twisted: :roll: :wink: :idea: :arrow: :neutral: :cry: :mrgreen: