您当前的位置:首页 > 电脑百科 > 数据库 > 百科

SQL语句的优化分析

时间:2019-12-19 11:24:45  来源:  作者:
SQL语句的优化分析

 

一、开门见山,问题所在

sql语句性能达不到你的要求,执行效率让你忍无可忍,一般会时下面几种情况。

  • 网速不给力,不稳定。
  • 服务器内存不够,或者SQL 被分配的内存不够。
  • sql语句设计不合理
  • 没有相应的索引,索引不合理
  • 没有有效的索引视图
  • 表数据过大没有有效的分区设计
  • 数据库设计太2,存在大量的数据冗余
  • 索引列上缺少相应的统计信息,或者统计信息过期
  • ....

那么我们如何给找出来导致性能慢的的原因呢?

  • 首先你要知道是否跟sql语句有关,确保不是机器开不开机,服务器硬件配置太差,没网你说p啊
  • 接着你使用我上一篇文章中提到的2柯南sql性能检测工具--sql server profiler,分析出sql慢的相关语句,就是执行时间过长,占用系统资源,cpu过多的
  • 然后是这篇文章要说的,sql优化方法跟技巧,避免一些不合理的sql语句,取暂优sql
  • 再然后判断是否使用啦,合理的统计信息。sql server中可以自动统计表中的数据分布信息,定时根据数据情况,更新统计信息,是很有必要的
  • 确认表中使用啦合理的索引,这个索引我前面博客中也有提过,不过那篇博客之后,还要进一步对索引写篇文章
  • 数据太多的表,要分区,缩小查找范围

 

二、分析比较执行时间计划读取情况

select * from dbo.Product

执行上面语句一般情况下只给你返回结果和执行行数,那么你怎么分析呢,怎么知道你优化之后跟没有优化的区别呢。

下面给你说几种方法。

1.查看执行时间和cpu占用时间

set statistics time on
select * from dbo.Product
set statistics time off

打开你查询之后的消息里面就能看到啦。

SQL语句的优化分析

 

2.查看查询对I/0的操作情况

set statistics io on
select * from dbo.Product
set statistics io off

执行之后

SQL语句的优化分析

 

  • 扫描计数:索引或表扫描次数
  • 逻辑读取:数据缓存中读取的页数
  • 物理读取:从磁盘中读取的页数
  • 预读:查询过程中,从磁盘放入缓存的页数
  • lob逻辑读取:从数据缓存中读取,image,text,ntext或大型数据的页数
  • lob物理读取:从磁盘中读取,image,text,ntext或大型数据的页数
  • lob预读:查询过程中,从磁盘放入缓存的image,text,ntext或大型数据的页数

如果物理读取次数和预读次说比较多,可以使用索引进行优化。

如果你不想使用sql语句命令来查看这些内容,方法也是有的,哥教你更简单的。

查询--->>查询选项--->>高级

SQL语句的优化分析

 

被红圈套上的2个选上,去掉sql语句中的set statistics io/time on/off 试试效果。哦也,你成功啦。。

3.查看执行计划,执行计划详解

选中查询语句,点击

SQL语句的优化分析

 

然后看消息里面,会出现下面的图例

SQL语句的优化分析

 

首先我这个例子的语句太过简单,你整个复杂的,包涵啊。

分析:鼠标放在图标上会显示此步骤执行的详细内容,每个表下面都显示一个开销百分比,分析站百分比多的的一块,可以根据重新设计数据结构,或这重写sql语句,来对此进行优化。如果存在扫描表,或者扫描聚集索引,这表示在当前查询中你的索引是不合适的,是没有起到作用的,那么你就要修改完善优化你的索引。

 

三、select查询艺术

1.保证不查询多余的列与行。

尽量避免select * 的存在,使用具体的列代替*,避免多余的列

使用where限定具体要查询的数据,避免多余的行

使用top,distinct关键字减少多余重复的行

2.慎用distinct关键字

distinct在查询一个字段或者很少字段的情况下使用,会避免重复数据的出现,给查询带来优化效果。

但是查询字段很多的情况下使用,则会大大降低查询效率。

SQL语句的优化分析

 

由这个图,分析下:

很明显带distinct的语句cpu时间和占用时间都高于不带distinct的语句。原因是当查询很多字段时,如果使用distinct,数据库引擎就会对数据进行比较,过滤掉重复数据,然而这个比较,过滤的过程则会毫不客气的占用系统资源,cpu时间。

3.慎用union关键字

此关键字主要功能是把各个查询语句的结果集合并到一个结果集中返回给你。用法

<select 语句1>
union
<select 语句2>
union
<select 语句3>
...

满足union的语句必须满足:1.列数相同。 2.对应列数的数据类型要保持兼容。

执行过程:

依次执行select语句-->>合并结果集--->>对结果集进行排序,过滤重复记录。

select * from((orde o left join orderproduct op on o.orderNum = op.orderNum) inner join product p on op.proNum = p.productnum) where p.id < 10000
union
select * from((orde o left join orderproduct op on o.orderNum = op.orderNum) inner join product p on op.proNum = p.productnum) where p.id < 20000 and p.id >= 10000
union
select * from((orde o left join orderproduct op on o.orderNum = op.orderNum) inner join product p on op.proNum = p.productnum) where p.id > 20000-- -这里可以写p.id > 100 结果一样, 因为他筛选过啦
 -- -- -- -对比上下两个语句-- -- -- --
 select * from((orde o left join orderproduct op on o.orderNum = op.orderNum) inner join product p on op.proNum = p.productnum)
SQL语句的优化分析

 

由此可见效率确实低,所以不是在必要情况下避免使用。其实有他执行的第三部:对结果集进行排序,过滤重复记录。就能看出不是什么好鸟。然而不对结果集排序过滤,显然效率是比union高的,那么不排序过滤的关键字有吗?答,有,他是union all,使用union all能对union进行一定的优化。。

4.判断表中是否存在数据

select count(*) from product 
select top(1) id from product

很显然下面完胜

5.连接查询的优化

首先你要弄明白你想要的数据是什么样子的,然后再做出决定使用哪一种连接,这很重要。

各种连接的取值大小为:

内连接结果集大小取决于左右表满足条件的数量

左连接取决与左表大小,右相反。

完全连接和交叉连接取决与左右两个表的数据总数量

select * from 
( (select * from orde where OrderId>10000) o left join orderproduct op on o.orderNum=op.orderNum )
select * from 
( orde o left join orderproduct op on o.orderNum=op.orderNum )
where o.OrderId>10000
SQL语句的优化分析

 

由此可见减少连接表的数据数量可以提高效率。

四、insert插入优化

--创建临时表
create table# tb1
 (
 id int,
 name nvarchar(30),
 createTime datetime
 )
declare@ i int
declare@ sql varchar(1000)
set@ i = 0
while (@i < 100000) --循环插入10w条数据
begin
set@ i = @i + 1
set@ sql = ' insert into #tb1 values ('+convert(varchar(10),@i)+', ''
 erzi '+
 convert(nvarchar(30), @i) + ''
 ','
 '' +
 convert(nvarchar(30), getdate()) + ''
 ')'
 exec(@sql) end

我这里运行时间是51秒

--创建临时表
create table# tb2
 (
 id int,
 name nvarchar(30),
 createTime datetime
 )
declare@ i int
declare@ sql varchar(8000)
declare@ j int
set@ i = 0
while (@i < 10000) --循环插入10w条数据
begin
set@ j = 0
set@ sql = ' insert into #tb2 select ' +
 convert(varchar(10), @i * 100 + @j) + ','
'erzi' + convert(nvarchar(30), @i * 100 + @j) + ''
','
'' + convert(varchar(50), getdate()) + ''
''
set@ i = @i + 1
while (@j < 10)
 begin
set@ sql = @sql + ' union all select ' +
 convert(varchar(10), @i * 100 + @j) + ','
'
erzi '+convert(nvarchar(30),@i*100+@j)+'
'',
''
'+convert(varchar(50),getdate())+'
''
'
set@ j = @j + 1
end
exec(@sql)
end
drop table# tb2
select count(1) from# tb2

我这里运行时间大概是20秒

分析说明:insert into select批量插入,明显提升效率。所以以后尽量避免一个个循环插入。

五、优化修改删除语句

如果你同时修改或删除过多数据,会造成cpu利用率过高从而影响别人对数据库的访问。

如果你删除或修改过多数据,采用单一循环操作,那么会是效率很低,也就是操作时间过程会很漫长。

这样你该怎么做呢?

折中的办法就是,分批操作数据。

delete product where id<1000
delete product where id>=1000 and id<2000
delete product where id>=2000 and id<3000
.....

当然这样的优化方式不一定是最优的选择,其实这三种方式都是可以的,这要根据你系统的访问热度来定夺,关键你要明白什么样的语句是什么样的效果。

总结:优化,最重要的是在于你平时设计语句,数据库的习惯,方式。如果你平时不在意,汇总到一块再做优化,你就需要耐心的分析,然而分析的过程就看你的悟性,需求,知识水平啦。



Tags:SQL 优化   点击:()  评论:()
声明:本站部分内容及图片来自互联网,转载是出于传递更多信息之目的,内容观点仅代表作者本人,如有任何标注错误或版权侵犯请与我们联系(Email:2595517585@qq.com),我们将及时更正、删除,谢谢。
▌相关推荐
单表优化除非单表数据未来会一直不断上涨,否则不要一开始就考虑拆分,拆分会带来逻辑、部署、运维的各种复杂度,一般以整型值为主的表在千万级以下,字符串为主的表在五百万以下是...【详细内容】
2020-04-17  Tags: SQL 优化  点击:(73)  评论:(0)  加入收藏
当MySQL单表记录数过大时,增加、删除、修改和查询的性能将急剧下降,您可以参考以下步骤进行优化。单表优化 除非你预计未来你的单表数据会不断的持续上涨,否则不要一开始就考虑...【详细内容】
2020-04-14  Tags: SQL 优化  点击:(70)  评论:(0)  加入收藏
谁在消耗cpu?用户+系统+IO等待+软硬中断+空闲 祸首是谁?用户用户空间CPU消耗,各种逻辑运算正在进行大量tps函数/排序/类型转化/逻辑IO访问&hellip;用户空间消耗大量cpu,产生...【详细内容】
2020-03-09  Tags: SQL 优化  点击:(144)  评论:(0)  加入收藏
千万级大表如何优化,这是一个很有技术含量的问题,通常我们的直觉思维都会跳转到拆分或者数据分区,在此我想做一些补充和梳理,想和大家做一些这方面的经验总结,也欢迎大家提出建议...【详细内容】
2020-02-11  Tags: SQL 优化  点击:(60)  评论:(0)  加入收藏
之前的文章是从实例的角度进行数据库优化,通过配置一些参数让数据库性能达到最优。但是一些“不好”的SQL也会导致数据库查询变慢,影响业务流程。本文从SQL角度进行数据库优化,提升SQL运行效率。...【详细内容】
2020-01-02  Tags: SQL 优化  点击:(107)  评论:(0)  加入收藏
一、开门见山,问题所在sql语句性能达不到你的要求,执行效率让你忍无可忍,一般会时下面几种情况。 网速不给力,不稳定。 服务器内存不够,或者SQL 被分配的内存不够。 sql语句设计...【详细内容】
2019-12-19  Tags: SQL 优化  点击:(95)  评论:(0)  加入收藏
概述优化SQL,是DBA常见的工作之一。如何高效、快速地优化一条语句,是每个DBA经常要面对的一个问题。对于一名DBA来说,掌握一门语言配合自己的工作是非常必要的。相对于shell的...【详细内容】
2019-10-09  Tags: SQL 优化  点击:(607)  评论:(0)  加入收藏
mysql5.6之前处理客户端连接的方式会触发mysql 新建一个线程来处理新的连接,新建的线程会处理该连接所发送的所有 SQL 请求,即 one-thread-per-connection 的方式,其创建连接的堆栈为:...【详细内容】
2019-09-06  Tags: SQL 优化  点击:(160)  评论:(0)  加入收藏
1 前言在进行 MySQL 的优化之前必须要了解的就是 MySQL 的查询过程,很多的查询优化工作实际上就是遵循一些原则让 MySQL 的优化器能够按照预想的合理方式运行而已。 2 优化...【详细内容】
2019-09-05  Tags: SQL 优化  点击:(151)  评论:(0)  加入收藏
MySQL 对于很多 Linux 从业者而言,是一个非常棘手的问题,多数情况都是因为对数据库出现问题的情况和处理思路不清晰。在进行 MySQL 的优化之前必须要了解的就是 MySQL 的查询过程,很多的查询优化工作实际上就是遵循一些...【详细内容】
2019-09-03  Tags: SQL 优化  点击:(157)  评论:(0)  加入收藏
▌简易百科推荐
1增1.1【插入单行】insert [into] <表名> (列名) values (列值)例:insert into Strdents (姓名,性别,出生日期) values (&#39;开心朋朋&#39;,&#39;男&#39;,&#39;1980/6/15&#3...【详细内容】
2021-12-27  快乐火车9d3    Tags:SQL   点击:(2)  评论:(0)  加入收藏
最近发现还有不少做开发的小伙伴,在写存储过程的时候,在参考已有的不同的写法时,往往很迷茫, 不知道各种写法孰优孰劣,该选用哪种写法,以及各种写法的优缺点,本文以一个简单的查询...【详细内容】
2021-12-23  linux上的码农    Tags:sql   点击:(9)  评论:(0)  加入收藏
《开源精选》是我们分享Github、Gitee等开源社区中优质项目的栏目,包括技术、学习、实用与各种有趣的内容。本期推荐的HasorDB 是一个全功能数据库访问工具,提供对象映射、丰...【详细内容】
2021-12-22  GitHub精选    Tags:HasorDB   点击:(5)  评论:(0)  加入收藏
作者丨Rafal Grzegorczyk译者丨陈骏策划丨孙淑娟【51CTO.com原创稿件】您是否还在手动对数据库执行各种脚本?您是否还在浪费时间去验证数据库脚本的正确性?您是否还需要将...【详细内容】
2021-12-22    51CTO  Tags:Liquibase   点击:(4)  评论:(0)  加入收藏
场景描述:由于生产环境的表比较复杂,字段很多。这里我们做下简化,只为说明今天要聊的问题。有两张表 tab1,tab2: tab1 数据如下: tab2 数据如下: 然后给你看下,我用来统计 name=&#3...【详细内容】
2021-12-20  Bald    Tags:SQL   点击:(7)  评论:(0)  加入收藏
前言知识无底,学海无涯,知识点虽然简单,但是比较多,所以将MySQL的基础写出来,方便自己以后查找,还有就是分享给大家。一、SQL简述1.SQL的概述Structure Query Language(结构化查...【详细内容】
2021-12-16  谣言止于独立思考    Tags:SQL基础   点击:(13)  评论:(0)  加入收藏
前言作为一名测试工程师,工作中在对测试结果进行数据比对的时候,或多或少要和数据库打交道的,要和数据库打交道,那么一些常用的 SQL 查询语法必须要掌握。最近有部分做测试小伙...【详细内容】
2021-12-14  柠檬班软件测试    Tags:SQL   点击:(15)  评论:(0)  加入收藏
话说C是面向内存的编程语言。数据要能存得进去,取得出来,且要考虑效率。不管是顺序存储还是链式存储,其寻址方式总是很重要。顺序存储是连续存储。同质结构的数组通过其索引表...【详细内容】
2021-12-08  小智雅汇    Tags:数据存储   点击:(18)  评论:(0)  加入收藏
概述DBConvert Studio 是一款强大的跨数据库迁移和同步软件,可在不同数据库格式之间转换数据库结构和数据。它将成熟、稳定、久经考验的 DBConvert 和 DBSync 核心与改进的现...【详细内容】
2021-11-17  雪竹聊运维    Tags:数据库   点击:(26)  评论:(0)  加入收藏
一、前言 大家好,我是小诚,《从0到1-全面深刻理解MySQL系列》已经来到第四章,这一章节的主要从一条SQL执行的开始,由浅入深的解析SQL语句由客户端到服务器的完整执行流程,最...【详细内容】
2021-11-09  woaker    Tags:SQL   点击:(35)  评论:(0)  加入收藏
最新更新
栏目热门
栏目头条