【SQLServer】| 查询性能优化

SQL Server 性能优化

Report SQL容易实现,性能优化却是个大难题,因工作中涉及SQL Server相关code的编写,当数据量大逻辑复杂时会涉及性能问题,故写篇性能优化入门博文

SQLSERVER 的性能优化是一个复杂的过程,其中的核心关键包括三个:

  • -减少全表检索的次数
  • -减少数据获取的数量
  • -尽可能的采用线程池实现数据库链接,并及时的关闭数据链接,方式内存溢出.当发现瓶颈后,针对性的优化算法或者硬件吞吐量做出针对性的扩展.

相关参考:

目标链接
索引和执行计划https://zhuanlan.zhihu.com/p/489273950
查看执行计划https://blog.csdn.net/lm3758/article/details/119920277
索引操作https://blog.csdn.net/whc888666/article/details/86172753

一、定位阶段

(1) 查看等待类型

通过等待类型,分析SQLSERVER 的耗时操作存在的类型

SELECT TOP 10 * FROM SYS.dm_os_wait_stats ORDER BY wait_time_ms DESC  
(2) 查看数据库统计信息:
set statistics io on  set statistics time on

select  * from table1;

set statistics io off set statistics time off
(3) 查看表格执行计划
  • 【Rows】:表示在一个执行步骤中,所产生的记录条数。
  • 【Executes】:表示某个执行步骤被执行的次数。
  • 【Stmt Text】:表示要执行的步骤的描述。
  • 【EstimateRows】:表示要预期返回多少行数据。
set statistics profile on 

select *,name from test_index where name='Tom' 
union  ALL    
select *,name from test_index where age>=12
(4) 查看耗时SQL

通过此语句可以查看耗时的SQL语句,根据SQL优化的规则进行针对性的SQL优化。

  • 低质量的索引
  • 不精确的统计
  • 过多的阻塞和死锁
  • 低质量的查询涉及
SELECT 
      	SS.SUM_EXECUTION_COUNT,
		T.TEXT,
		SS.SUM_TOTAL_ELAPSED_TIME,
		SS.SUM_TOTAL_WORKER_TIME,
		SS.SUM_TOTAL_LOGICAL_READS,
		SS.SUM_TOTAL_LOGICAL_WRITES
FROM (
    	SELECT 
    			S.PLAN_HANDLE,
				SUM(S.EXECUTION_COUNT)SUM_EXECUTION_COUNT,
				SUM(S.TOTAL_ELAPSED_TIME)SUM_TOTAL_ELAPSED_TIME,
				SUM(S.TOTAL_WORKER_TIME)SUM_TOTAL_WORKER_TIME,
				SUM(S.TOTAL_LOGICAL_READS)SUM_TOTAL_LOGICAL_READS,
				SUM(S.TOTAL_LOGICAL_WRITES)SUM_TOTAL_LOGICAL_WRITES
		FROM SYS.DM_EXEC_QUERY_STATS S
		GROUP BY S.PLAN_HANDLE
	) AS SS
CROSS APPLY SYS.dm_exec_sql_text(SS.PLAN_HANDLE)T
ORDER BY SUM_TOTAL_LOGICAL_READS DESC
(5)查看CPU
  • 1)查看当前的数据库用户连接有多少
USE master
GO
SELECT * FROM sys.[sysprocesses] WHERE [spid]>50 --AND DB_NAME([dbid])='gposdb'
SELECT COUNT(*) FROM [sys].[dm_exec_sessions] WHERE [session_id]>50
  • 2)选取前10个最耗CPU时间的会话
SELECT TOP 10
	[session_id],
	[request_id],
	[start_time] AS '开始时间',
	[status] AS '状态',
	[command] AS '命令',
	dest.[text] AS 'sql语句', 
	DB_NAME([database_id]) AS '数据库名',
	[blocking_session_id] AS '正在阻塞其他会话的会话ID',
	[wait_type] AS '等待资源类型',
	[wait_time] AS '等待时间',
	[wait_resource] AS '等待的资源',
	[reads] AS '物理读次数',
	[writes] AS '写次数',
	[logical_reads] AS '逻辑读次数',
	[row_count] AS '返回结果行数'
FROM sys.[dm_exec_requests] AS der 
CROSS APPLY sys.[dm_exec_sql_text](der.[sql_handle]) AS dest 
WHERE [session_id]>50 AND DB_NAME(der.[database_id])='gposdb'  
ORDER BY [cpu_time] DESC
  • 3)查询前10个最耗CPU时间的SQL语句
SELECT TOP 10 dest.[text] AS 'sql语句'
FROM sys.[dm_exec_requests] AS der 
CROSS APPLY  sys.[dm_exec_sql_text](der.[sql_handle]) AS dest 
WHERE [session_id]>50  
ORDER BY [cpu_time] DESC
  • 4)查询会话中有多少个worker在等待
SELECT TOP 10
 	[session_id],
 	[request_id],
 	[start_time] AS '开始时间',
 	[status] AS '状态',
 	[command] AS '命令',
 	dest.[text] AS 'sql语句', 
 	DB_NAME([database_id]) AS '数据库名',
 	[blocking_session_id] AS '正在阻塞其他会话的会话ID',
 	der.[wait_type] AS '等待资源类型',
 	[wait_time] AS '等待时间',
	[wait_resource] AS '等待的资源',
 	[dows].[waiting_tasks_count] AS '当前正在进行等待的任务数',
 	[reads] AS '物理读次数',
 	[writes] AS '写次数',
 	[logical_reads] AS '逻辑读次数',
 	[row_count] AS '返回结果行数'
 FROM sys.[dm_exec_requests] AS der 
 INNER JOIN [sys].[dm_os_wait_stats] AS dows  ON der.[wait_type]=[dows].[wait_type]
 CROSS APPLY  sys.[dm_exec_sql_text](der.[sql_handle]) AS dest 
 WHERE [session_id]>50  
 ORDER BY [cpu_time] DESC
  • 5)查询CPU占用高的语句
SELECT TOP 10
	total_worker_time/execution_count AS avg_cpu_cost, plan_handle,
  	execution_count,
   (SELECT 	SUBSTRING(text, statement_start_offset/2 + 1,
      		(CASE WHEN statement_end_offset = -1 THEN LEN(CONVERT(nvarchar(max), text)) * 2 ELSE statement_end_offset END - statement_start_offset)/2)
    FROM sys.dm_exec_sql_text(sql_handle)) AS query_text
FROM sys.dm_exec_query_stats
ORDER BY [avg_cpu_cost] DESC

二、数据库查询常用优化

1 减小数据集–只返回需要的数据

返回数据到客户端至少需要数据库提取数据、网络传输数据、客户端接收数据以及客户端处理数据等环节,如果返回不需要的数据,就会增加服务器、网络和客户端的无效劳动,其害处是显而易见的,需要注意避免这类事件。

(1) select需要的列,不要使用select *(横向)
任何地方都不要使用select *,用具体的字段列表代替,不要返回用不到的字段,select中指定所需要的列,将带来的好处:

(1)减少内存耗费和网络的带宽 
(2)更安全 
(3)给查询优化器机会从索引读取所有需要的列
(2) 优化group by, 先条件过滤再进行group by (纵向)

提高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];
2 避免全表扫描
(1) in 和 not in 要慎用,否则会导致全表扫描

NOT IN、NOT EXISTS的相关子查询可以改用LEFT JOIN代替写法

(低效)
SELECT PUB_NAME 
FROM   PUBLISHERS 
WHERE  PUB_ID  NOT IN (
    					SELECT PUB_ID 
  						FROM TITLES 
  						WHERE TYPE = 'BUSINESS') 

(高效)
SELECT A.PUB_NAME 
FROM PUBLISHERS A 
LEFT JOIN TITLES B ON B.TYPE = 'BUSINESS' AND A.PUB_ID=B. PUB_ID 
WHERE B.PUB_ID IS NULL

(2) 应尽量避免在 where 子句中使用 != 或 <> 操作符,否则将引擎放弃使用索引而进行全表扫描
select id from t where num <> 1;
(3) is null或is not null操作

应尽量避免在 where 子句中对字段进行 null 值判断,否则将导致引擎放弃使用索引而进行全表扫描,如:

select id from t where num is null;
(4) 用union替换or(适用于索引列)

通常情况下,用union替换where子句中的or将会起到较好的效果。如果一个字段有索引,一个字段没有索引,对索引列使用or将造成全表扫描。注意:这个规则只针对多个索引列有效。如果有column没有被索引,查询效率可能会因为你没有选择or而降低。而进行全表扫描下面的例子中loc_id和region上都有建索引

(低效) select loc_id,loc_desc,begion from location where loc_id=10 or begion='MELBOURNE'; 

(高效) select loc_id,loc_desc,begion from location where loc_id=10
	  union
	  select loc_id,loc_desc_begion from location where begion='MELBOURNE'; 
(5) 尽量避免在where子句中对字段进行表达式操作,否则将导致全表扫描
(低效) select id from t where num/2=100  

(高效) select id from t where num=100*2 
(6) 尽量避免在where子句中对字段进行函数操作,否则将导致全表扫描
(低效) select id from t where substring(name,1,3)='abc'

(高效) select id from t where name like 'abc%' 
3 其他值得避免的场景

(1) 在 where 子句中使用= 操作符时,尽量使用数字型字段,避免字符型

尽量使用数字型字段,若只含数值信息的字段尽量不要设计为字符型,这会降低查询和连接的性能,并会增加存储开销。因为引擎在处理查询和连接时会逐个比较字符串中每个字符,而对于数字型而言只需要比较一次就够了。

(低效) select id from t where role ='Borrower';

(高效) select id from t where role = 3;
(2) 对于连续的数值,能用 between and 就不要用 in
(低效) select id from t where num in(1,2,3);

(高效) select id from t where num between 1 and 3;
(3) 使用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);
(4) 大于(>)及小于(<)操作

大于或小于一般情况不用调整,因为它有索引就会采用索引查找,但有的情况下可以对它进行优化。如一个表有100万记录,那么执行>2与>=3的效果就有很大区别了

(低效) select * from [emp] where [deptno]>2;

(高效) select * from [emp] where [deptno]>=3;
(5) 尽量避免使用前置百分号
select id from t where name like '%abc%' 
(6) 子查询
  • NOT IN、NOT EXISTS的相关子查询可以改用LEFT JOIN代替写法
--NOT IN
(低效)
SELECT PUB_NAME 
FROM PUBLISHERS 
WHERE PUB_ID NOT IN (
    				SELECT PUB_ID 
  					FROM TITLES 
  					WHERE TYPE = 'BUSINESS') 
  
(高效)
SELECT A.PUB_NAME 
FROM PUBLISHERS A 
LEFT JOIN TITLES B ON B.TYPE = 'BUSINESS' AND  A.PUB_ID=B. PUB_ID 
WHERE B.PUB_ID IS NULL


--NOT EXISTS
(低效)
SELECT TITLE 
FROM TITLES 
WHERE NOT EXISTS (
    				SELECT TITLE_ID 
  					FROM SALES 
  					WHERE TITLE_ID = TITLES.TITLE_ID)
(高效)
SELECT TITLE 
FROM TITLES LEFT JOIN SALES ON SALES.TITLE_ID = TITLES.TITLE_ID 
WHERE SALES.TITLE_ID IS NULL

  • 保证子查询没有重复 ,IN、EXISTS的相关子查询可以用INNER JOIN 代替
(低效)
SELECT PUB_NAME 
FROM PUBLISHERS 
WHERE PUB_ID IN (
    			SELECT PUB_ID 
  				FROM TITLES 
  				WHERE TYPE = 'BUSINESS')

(高效)
SELECT DISTINCT A.PUB_NAME 
FROM PUBLISHERS A INNER JOIN TITLES B  ON B.TYPE = 'BUSINESS' AND  A.PUB_ID=B. PUB_ID
  • IN的相关子查询用EXISTS代替
(低效)
SELECT PUB_NAME 
FROM PUBLISHERS 
WHERE PUB_ID IN (
    			SELECT PUB_ID 
  				FROM TITLES 
  				WHERE TYPE = 'BUSINESS')

(高效)
SELECT PUB_NAME 
FROM PUBLISHERS 
WHERE EXISTS (
    			SELECT 1 
  				FROM TITLES 
  				WHERE TYPE = 'BUSINESS' AND  PUB_ID= PUBLISHERS.PUB_ID)
(6) 索引
  • 不是越多越好,索引虽然可以提高相应的 select 的效率,但同时也降低insertupdate 的效率。因为 insert 或 update 时有可能会重建索引,所以怎样建索引需要慎重考虑,视具体情况而定。

  • 一个表的索引数最好不要超过6个,若太多则应考虑一些不常使用到的列上建的索引是否有必要。

  • 并不是所有索引对查询都有效,SQL根据表中数据来进行查询优化,当索引列大量数据重复时,SQL查询可能不会利用索引,不在大量重复数据列建立索引

  • 对查询进行优化,要尽量避免全表扫描,首先应考虑在 where 及 order by 涉及的列上建立索引

  • 尽量使用表变量来代替临时表。如果表变量包含大量数据,请注意索引非常有限(只有主键索引)

(7) 临时表
  • 在新建临时表时
    • 如果一次性插入数据量很大,那么可以使用 select into 代替 create table,避免造成大量 log ,以提高速度;
    • 如果数据量不大,为了缓和系统表的资源,应先create table,然后insert;
  • 尽量使用表变量来代替临时表。如果表变量包含大量数据,请注意索引非常有限(只有主键索引);
  • 如果使用到了临时表,在存储过程的最后务必将所有的临时表显式删除,这样可以避免系统表的较长时间锁定;
    • 先 truncate table ;
    • 然后 drop table 。
  • 使用基于临时表或者游标的方法方法之前,应先寻找基于集的解决方案来解决问题,基于集的方法通常更有效
(8) 游标
  • 尽量避免使用游标,因为游标的效率较差,如果游标操作的数据超过1万行,那么就考虑改写
(9) 事务
  • 尽量避免大事务操作,提高系统并发能力
  • 5
    点赞
  • 60
    收藏
    觉得还不错? 一键收藏
  • 0
    评论

“相关推荐”对你有帮助么?

  • 非常没帮助
  • 没帮助
  • 一般
  • 有帮助
  • 非常有帮助
提交
评论
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值