0
0

【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 的耗时操作存在的类型

SELECTTOP10*FROM SYS.dm_os_wait_stats ORDERBY wait_time_ms DESC
(2) 查看数据库统计信息：
setstatistics io onsetstatisticstimeonselect*from table1;setstatistics io offsetstatisticstimeoff
(3) 查看表格执行计划
  • 【Rows】：表示在一个执行步骤中，所产生的记录条数。
  • 【Executes】：表示某个执行步骤被执行的次数。
  • 【Stmt Text】：表示要执行的步骤的描述。
  • 【EstimateRows】：表示要预期返回多少行数据。
setstatistics profile onselect*,name from test_index where name='Tom'unionALLselect*,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
        GROUPBY S.PLAN_HANDLE
    )AS SS
CROSSAPPLY SYS.dm_exec_sql_text(SS.PLAN_HANDLE)T
ORDERBY SUM_TOTAL_LOGICAL_READS DESC
（5）查看CPU
  • 1）查看当前的数据库用户连接有多少
USE master
GO
SELECT*FROM sys.[sysprocesses]WHERE[spid]>50--AND DB_NAME([dbid])='gposdb'SELECTCOUNT(*)FROM[sys].[dm_exec_sessions]WHERE[session_id]>50
  • 2）选取前10个最耗CPU时间的会话
SELECTTOP10[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 
CROSSAPPLY sys.[dm_exec_sql_text](der.[sql_handle])AS dest 
WHERE[session_id]>50AND DB_NAME(der.[database_id])='gposdb'ORDERBY[cpu_time]DESC
  • 3）查询前10个最耗CPU时间的SQL语句
SELECTTOP10 dest.[text]AS'sql语句'FROM sys.[dm_exec_requests]AS der 
CROSSAPPLY  sys.[dm_exec_sql_text](der.[sql_handle])AS dest 
WHERE[session_id]>50ORDERBY[cpu_time]DESC
  • 4）查询会话中有多少个worker在等待
SELECTTOP10[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 
 INNERJOIN[sys].[dm_os_wait_stats]AS dows  ON der.[wait_type]=[dows].[wait_type]CROSSAPPLY  sys.[dm_exec_sql_text](der.[sql_handle])AS dest 
 WHERE[session_id]>50ORDERBY[cpu_time]DESC
  • 5）查询CPU占用高的语句
SELECTTOP10
    total_worker_time/execution_count AS avg_cpu_cost, plan_handle,
      execution_count,(SELECT     SUBSTRING(text, statement_start_offset/2+1,(CASEWHEN statement_end_offset =-1THENLEN(CONVERT(nvarchar(max),text))*2ELSE 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
ORDERBY[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]groupby[job]having job='PRESIDENT'or job='MANAGER';(高效)select[job],avg([sal])from[emp]where[job]='PRESIDENT'or job='MANAGER'groupby[job];

2 避免全表扫描

(1) in 和 not in 要慎用，否则会导致全表扫描

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

(低效)SELECT PUB_NAME 
FROM   PUBLISHERS 
WHERE  PUB_ID  NOTIN(SELECT PUB_ID 
                          FROM TITLES 
                          WHERETYPE='BUSINESS')(高效)SELECT A.PUB_NAME 
FROM PUBLISHERS A 
LEFTJOIN TITLES B ON B.TYPE='BUSINESS'AND A.PUB_ID=B. PUB_ID 
WHERE B.PUB_ID ISNULL
(2) 应尽量避免在 where 子句中使用 != 或 <> 操作符，否则将引擎放弃使用索引而进行全表扫描
select id from t where num <>1;
(3) is null或is not null操作

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

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

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

(低效)select loc_id,loc_desc,begion from location where loc_id=10or 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 between1and3;
(3) 使用exists 代替 in
(低效)select num from a where num in(select num from b);(高效)select num from a whereexists(select1from 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 NOTIN(SELECT PUB_ID 
                      FROM TITLES 
                      WHERETYPE='BUSINESS')(高效)SELECT A.PUB_NAME 
FROM PUBLISHERS A 
LEFTJOIN TITLES B ON B.TYPE='BUSINESS'AND  A.PUB_ID=B. PUB_ID 
WHERE B.PUB_ID ISNULL--NOT EXISTS(低效)SELECT TITLE 
FROM TITLES 
WHERENOTEXISTS(SELECT TITLE_ID 
                      FROM SALES 
                      WHERE TITLE_ID = TITLES.TITLE_ID)(高效)SELECT TITLE 
FROM TITLES LEFTJOIN SALES ON SALES.TITLE_ID = TITLES.TITLE_ID 
WHERE SALES.TITLE_ID ISNULL
  • 保证子查询没有重复 ，IN、EXISTS的相关子查询可以用INNER JOIN 代替
(低效)SELECT PUB_NAME 
FROM PUBLISHERS 
WHERE PUB_ID IN(SELECT PUB_ID 
                  FROM TITLES 
                  WHERETYPE='BUSINESS')(高效)SELECTDISTINCT A.PUB_NAME 
FROM PUBLISHERS A INNERJOIN 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 
                  WHERETYPE='BUSINESS')(高效)SELECT PUB_NAME 
FROM PUBLISHERS 
WHEREEXISTS(SELECT1FROM TITLES 
                  WHERETYPE='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) 事务
  • 尽量避免大事务操作，提高系统并发能力
标签： sqlserver 性能优化 优化
本文转载自: https://blog.csdn.net/weixin_44553044/article/details/124362203
版权归原作者 小管呀 所有， 如有侵权，请联系我们删除。
登录后发布评论

“【SQLServer】| 查询性能优化”的评论:

还没有评论
关于作者
...
overfit同步小助手
文章同步
相关阅读

机器人技术在安全领域的重要作用

安全学习记录——网络篇（一）

图腾柱PFC：HP1010为您的电动两轮车之旅提供绿色，高效，安全的动力

单元测试数据库回滚问题

这才是单元测试，之前我们都错了

【C语言基础】：字符函数和字符串函数

ChatGPT-Next-Web SSRF漏洞+XSS漏洞复现(CVE-2023-49785)

文章导航