【SQLServer】

SQL Server 性能优化

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

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

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

相关参考:

一、定位阶段

(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 onselect  * 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_WRITESFROM SYS.DM_EXEC_QUERY_STATS SGROUP 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 10total_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=10unionselect 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 的效率，但同时也降低了 insert 及 update 的效率。因为 insert 或 update 时有可能会重建索引，所以怎样建索引需要慎重考虑，视具体情况而定。

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

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

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

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

(7) 临时表

• 在新建临时表时
  • 如果一次性插入数据量很大，那么可以使用 select into 代替 create table，避免造成大量 log ,以提高速度;
  • 如果数据量不大，为了缓和系统表的资源，应先create table，然后insert；
• 尽量使用表变量来代替临时表。如果表变量包含大量数据，请注意索引非常有限(只有主键索引);
• 如果使用到了临时表，在存储过程的最后务必将所有的临时表显式删除，这样可以避免系统表的较长时间锁定;
  • 先 truncate table ；
  • 然后 drop table 。
• 使用基于临时表或者游标的方法方法之前，应先寻找基于集的解决方案来解决问题，基于集的方法通常更有效

(8) 游标

• 尽量避免使用游标，因为游标的效率较差，如果游标操作的数据超过1万行，那么就考虑改写

(9) 事务

• 尽量避免大事务操作，提高系统并发能力