Expert 诊断优化系列------------------语句调优三板斧

关注

转载最新推荐文章于 2019-11-18 00:41:02 发布 295 阅读 AI 写同款· GEO 优化 ›

SqlSugar 是 .NET 开源的 ORM 框架，由 Fructose 大数据技术团队维护和更新，开箱即用最易用的 ORM 低代码高性能超级简单功能全面 [多数据库兼容] SqlSugar 是 .NET 开源的 ORM 框架，由 Fructose 大数据技术团队维护和更新，开箱即用最易用的 ORM。[低代码] [高性能] [超级简单] [功能全面] [多数据库兼容] [合适产品]。支持.NET.net framework.net core3.1.ne5.net6.net7.net8 .net9 .net10。支持数据库 MySql、SqlServer、Sqlite、Oracle、postgresql、达梦、Mongodb 人大金仓（国产推荐）、神通数据库、瀚高、Access DB2、DuckDb、Hana、OceanBase TDengine QuestDb Clickhouse MySqlConnector、华为 GaussDB 南大通用 GBase、MariaDB、Tidb、Odbc、Percona Server， Amazon Aurora、Azure Database for MySQL、 Google Cloud SQL for MySQL、自定义数据库。真正实现零SQL ORM表构建、索引和CRUD支持。Support.NET 数百万大数据写入、更新、子表，拥有数十亿查询统计数据成熟解决方案。支持SAAS完整应用：跨数据库查询、审计、租户子数据库、租户子表和租户数据隔离。支持低代码 + 工作流程（动态类构建、动态表构建、非实体多库兼容 CRUD、JSON TO SQL、CUSTOM XML 等）。支持 ValueObject、判别器、仓库、UnitOfWork、DbContext、AOP 立即下载

前面三篇通过CPU、内存、磁盘三巨头，讲述了如何透过现在看本质，怎样定位服务器三巨头反映出的问题。为了方便阅读给出链接：

SQL SERVER全面优化-------Expert for SQL Server 诊断系列

　　　　通过三篇文章的基本介绍，可以看出系统的语句如果不优化，可能会导致三巨头都出现异常的表现。所以本篇开始介绍系统中的重头戏--------------SQL语句！

开篇前的啰嗦　

什么是SQL 语句？

　　　这就是SQL 语句！帅气吧！还有呢！

　　　这也是SQL语句！

　　　博主真能骗人，我读书少也知道，这是“车、马、炮”的 “车” ！没错，此篇文章里会以“车”来代表你的SQL 语句，让你知道怎样让你的“车”从16手报废车改装成------------------ |法拉利|

　　　注：SQL语句优化的细节，一本书都写不全，所以这里只讲述“改装思想！”

改装有顺序------常开的爱车下手

　　　你的系统中有成千上万的语句，那么优化语句从何入手呢？当然是系统中运行最频繁，最核心的语句了。废话不多说，上例子：

　　　这是一天的语句执行情况，里面柱状图表示的是对应执行时间段内语句的次数，总体看起来长时间语句非常多。

　　　下面看一下具体的语句执行情况：

　　　排位第一的语句执行次数38508次，是一个存储过程（RPC:Completed 表示存储过程结束，不知道这个的请看profiler的使用说明）。其中的一条语句（SP:StmtCompleted）也就是排在第二位的语句，存储过程的执行时间大部分消耗这条子语句上！

这个例子可以看出业务系统中使用最频繁，且远远高于其他处理的语句就是这个执行3W8Q多次的语句。

　　　那么看到这样的数据，想做优化当然要从这条语句下手了！它就是你最常开的车了！这个例子中只要解决了这条语句的性能问题，整个系统性能就可以有一个质的飞跃。

--------------------------------上面的情况，你很专一，只喜欢开一种车！----------------------------

　　　这个例子中，你喜欢开的车就比较多了，也就是说需要你关注，并且优化的语句较多。（很多语句执行次数都很频繁，也就是系统中使用到的频繁功能较多）

　　　系统优化需要循序渐进，从系统最频繁的语句出发，逐个解决语句问题。

　　　有人看到这会说，博主你有工具，能收集、能统计，我啥也没有咋整？不要急后文脚本都会奉上！

改装前的知识储备

　　知识储备很重要，语句的优化涉及的地方很多很多，要么为什么说可以写本书呢？

你知道什么是执行计划么？如何在语句执行的同时，看到执行计划？
你知道索引有几种？有什么区别么？
你知道有索引和没索引，语句执行的区别么？
你知道什么是统计信息么？
你知道什么是临时表，表变量，CTE？有什么区别？
什么是事务？什么是隔离级别？
你知道什么是逻辑读，什么是物理读，什么是预读么？怎么查看你执行消耗的IO资源？
你知道什么是等待？怎么查看你运行的语句是否在等待？等待反应出的问题是什么？
你了解SQL的锁机制么？
你了解TempDB么？什么样的语句会使用TempDB？
编译与重编译？
查询提示是干什么的？
.....
.....
.....
.....

常见的改装方式

------------------------------------新手区-----------高手勿进-------------------------------------　　

　　是不是就没法短时间内，掌握大部分语句的优化技巧呢？这是可以的，简单介绍一下语句简单粗暴的调优方式：

开启执行计划，让执行计划告诉你，语句慢的原因

透过计划，一眼看出索引

　　　当语句执行后，执行计划中会提示你这条运行的语句中是否缺少索引，右键绿色部分"缺少索引提示"，点击缺少索引详细信息，生成对应的索引脚本，创在在数据库中。

　　　在次执行语句验证是否有效，如果还继续提示索引缺失，继续按照此方法创建索引。

　　　索引对于一个语句的影响很大，一个有效的索引可以缩短语句的执行时间，并且降低CPU、IO、内存等消耗。也就是说不但让你的语句执行快，更降低了宝贵的系统资源消耗！

　　　执行计划中除了可以看出缺失的索引，也可以看出语句的主要消耗在哪。知道了主要消耗，我们也就可以针对这个消耗进行优化。如例子中94%的开销在表的扫描上。当看到这个开销很大并且是一个扫描的时候，第一反应要看扫描的表，有没有筛选条件“where”条件，或 “关联条件join" 如果有条件，那就看为什么没有先用条件过滤数据！是不是没有索引？是不是创建的索引不能用（隐式转换？列上有函数？等等，具体为什么不能使用索引，请自行百度）

　　　高能提示：不要小看索引，感觉这都是小儿科。在我亲身经历的众多客户之中，大面积缺少索引的系统可以占到三层以上。或是软件开发完，对数据库就没有建立索引，或是随着系统的日积月累，数据量、功能也随之增加，系统得不到一个及时的跟踪优化导致。

降低语句的复杂度

　　讲一个我自己的故事，我刚从业的时候对数据库的优化了解不深，一度认为自己写的SQL 好牛逼，因为现在给我，我真是看不懂。一个语句两张A4纸都打印不下！各种子查询，视图嵌套，函数嵌套，UNION ALL等等等。

不能否认这种语句写出来以后，有种小自豪感！因为别人根本看不懂，改也改不了！这种语句在对于SQL 的优化器来说就是灾难，下面简单的说下优化器拿到一条语句怎么样作出执行计划：

　　首先传入一个语句，如果有视图，则会把你视图内的代码和外层代码经过二次编译变成一个大语句（多层视图都会编译成一个），然后从表连接开始，优化器会根据统计信息，和一些预查询（如执行所需要的字段类型长度，数据量等）针对你的条件选用一个表作为驱动表，然后继续和其他的表关联，并选用关联方式（hash、merge、nested loop）等，每次关联顺序和方式的都基于SQL的预估，也就是关联的越多，最后的预估可能越不准确，进而导致选用一个比较差的计划。为什么有好的不选却选出一个差的呢？因为优化器不会把你所有执行的可能都验证一次，然后选择一个最好的。这里选出来的“最优”的只是一个相对值。

　　介绍的有点跑题了，下面我们说一下降低语句复杂度的常用方式：最常用的就是临时表，比如先把条件筛选性较强的几张表关联，然后把结果放入临时表，在用临时表和其他表关联。可以理解成我有10张表关联，我先拿5张表出来关联，然后把结果放入临时表，再跟另外5张表关联。这样这个查询的复杂度由10张表的联合变成 5+6，这样降低了复杂语句复杂度。

　　复杂视图也是如此，在视图和外层关联前，放入临时表，再跟外层关联。

　　子查询也是如此，可以分离出来成为临时表的子查询，先分离出来。

　　情况很多种，最终目的就是降低语句复杂性，让语句分多个步骤执行，这样也可以让优化器每次选出一个比较稳定的计划（一个语句执行有时快有时慢，也很可能是语句的复杂性导致的）。

　　高能提示：部分系统核心处理的语句比较复杂，且已经很多年前留下的遗产了，经历了一代又一代，我真心不敢碰。那么恭喜你中奖了，好好分析下业务，通过临时表拆分语句还是有可能的！

　　　　　　　临时表和表变量，最大的区别是表变量作为中间过程表不能插入太多数据，如果数据插入的多严重影响性能。

降低并行度，或使用并行提升性能

　　这个小标题好像有些矛盾！解释一下降低并行度是因为现在的服务器配置CPU数都很大64或更多的随处可见，系统选用并行计划时，使用过多的CPU 反而会使性能下降具体请参见：Expert 诊断优化系列------------------你的CPU高么？

　　首先看一个等待： CXPACKET

　　　CXPACKET　是最常见的等待之一，等待并行计划 CPU的调度，或线程上的资源等待，请参见

sys.dm_os_waiting_tasks 引发的疑问(上)

sys.dm_os_waiting_tasks 引发的疑问(中)

sys.dm_os_waiting_tasks 引发的疑问(下)

　　　当你看见如图的等待情况时，说明你系统中并行度需要调整了！请参见系列中的CPU篇，这里不过多介绍。

　　　另一种情况，语句可以通过并行来提升执行时间，这里也不过多介绍，请参见SQL提示介绍-强制并行

使用一切方法降低读次数

　　一个语句运行起来消耗的读次数也少，说可以间接说明这个语句优化程度较高，读取的页数少也会降低内存和磁盘的压力。

　　　优化时可以开set statistics io on 来观察语句的IO消耗情况。降低IO的主要方式就是添加索引和降低语句复杂度。

　　　注：重点关注读次数多的表！

　　　这里就不细说了！

不能忽视的硬件问题

　　前三篇一直在强调语句很影响服务器资源。但不能忽略的一点就是，语句的运行好坏也很依赖于资源，硬件资源就好比路面环境。语句这车再好，路没有那么宽，也不平坦，再好的车也跑不起来。

反过来就算硬件足够好，路够宽也够好，没有好车也是跑不起来的！

--------------博客地址---------------------------------------------------------------------------------------

Expert 诊断优化系列 http://www.cnblogs.com/double-K/

-----------------------------------------------------------------------------------------------------

　　总结：语句运行的效率是系统的关键，而运行最频繁的语句就是关键中的关键。找出系统运行频率高且效率较差的语句进行优化，是优化思路中的核心。

　　　　　80%的优化不需要你有高深的技术积累，程咬金的三板斧轮上去，也会扫倒一大片的。请参见 ”常见改装“中的三种手段。

　　　　　剩下20%的优化就需要对知识的不断积累，在实际场景中获得更好的知识提升。

　　　　　硬件和语句相互依赖，都是最优的那自然是好，但是作为技术人员我们保证系统语句是最优的，也是一种责任的体现！

　　　　　本文只是非常简单的介绍常规优化的思路和方法，不足之处请谅解。后续文章中也会针对等待、执行计划、tempDB等继续细说系统的优化。

　　　PS: 优化需要使用各种手段，反复尝试才能达到一个最好的效果，优化无止境。

-------------------------干货到了---------------------------------------------------------------------------　　　　

没有自己的SQL工具怎么找出执行频繁的语句呢？

profiler 对系统进行监控（不会的小伙伴，快去百度吧）　　
DMV视图　，筛选条件请自行修改

with aa as (
SELECT  
--执行次数 
QS.execution_count, 
--查询语句 
SUBSTRING(ST.text,(QS.statement_start_offset/2)+1, 
((CASE QS.statement_end_offset WHEN -1 THEN DATALENGTH(st.text) 
ELSE QS.statement_end_offset END - QS.statement_start_offset)/2) + 1 
) AS statement_text, 
--执行文本 
ST.text, 
--执行计划 
qs.last_elapsed_time,
qs.min_elapsed_time,
qs.max_elapsed_time,
QS.total_worker_time, 
QS.last_worker_time, 
QS.max_worker_time, 
QS.min_worker_time 
FROM 
sys.dm_exec_query_stats QS 
--关键字 
CROSS APPLY 
sys.dm_exec_sql_text(QS.sql_handle) ST 
WHERE 
QS.last_execution_time > '2016-02-14 00:00:00' and  execution_count > 500

-- AND ST.text LIKE '%%' 
--ORDER BY 
--QS.execution_count DESC

)
select text,max(execution_count) execution_count --,last_elapsed_time,min_elapsed_time,max_elapsed_time 
from aa
where [text] not  like '%sp_MSupd_%' and  [text] not like '%sp_MSins_%' and  [text] not like '%sp_MSdel_%' 
group by text
order by 2  desc

怎么查看自己系统缺失的索引？适合大批量创建索引

这里的DMV信息只是记录自上次SQL Server启动以后的信息项，也就是说每次重启之后这部分信息就丢失了，所以对于生产系统，建议确保运行了一段周期之后再进行查看。

在我们重新创建聚集索引的时候，SQL Server会默认的重新生成全部非聚集索引，如果表数据量特别大，这个过程会很漫长，如果不指定ONLINE的话，这个过程会是锁定索引B-Teee的，这就意味着是阻塞的，业务就要停下来等待完成操作。

------------------缺失索引-----------------------
SELECT migs.group_handle, mid.* 
FROM sys.dm_db_missing_index_group_stats AS migs 
INNER JOIN sys.dm_db_missing_index_groups AS mig 
ON (migs.group_handle = mig.index_group_handle) 
INNER JOIN sys.dm_db_missing_index_details AS mid 
ON (mig.index_handle = mid.index_handle) 
WHERE migs.group_handle = 2
----------------------------------无用索引----------------------
SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED 
SELECT 
DB_NAME() AS DatbaseName 
, SCHEMA_NAME(O.Schema_ID) AS SchemaName 
, OBJECT_NAME(I.object_id) AS TableName 
, I.name AS IndexName 
INTO #TempNeverUsedIndexes 
FROM sys.indexes I INNER JOIN sys.objects O ON I.object_id = O.object_id 
WHERE 1=2 
EXEC sp_MSForEachDB 'USE [?]; INSERT INTO #TempNeverUsedIndexes 
SELECT 
DB_NAME() AS DatbaseName 
, SCHEMA_NAME(O.Schema_ID) AS SchemaName 
, OBJECT_NAME(I.object_id) AS TableName 
, I.NAME AS IndexName 
FROM sys.indexes I INNER JOIN sys.objects O ON I.object_id = O.object_id 
LEFT OUTER JOIN sys.dm_db_index_usage_stats S ON S.object_id = I.object_id 
AND I.index_id = S.index_id 
AND DATABASE_ID = DB_ID() 
WHERE OBJECTPROPERTY(O.object_id,''IsMsShipped'') = 0 
AND I.name IS NOT NULL 
AND S.object_id IS NULL' 
SELECT * FROM #TempNeverUsedIndexes 
ORDER BY DatbaseName, SchemaName, TableName, IndexName 
DROP TABLE #TempNeverUsedIndexes

--------------------------经常被大量更新，但是却基本不适用的索引项--------------------
SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED 
SELECT 
DB_NAME() AS DatabaseName 
, SCHEMA_NAME(o.Schema_ID) AS SchemaName 
, OBJECT_NAME(s.[object_id]) AS TableName 
, i.name AS IndexName 
, s.user_updates 
, s.system_seeks + s.system_scans + s.system_lookups 
AS [System usage] 
INTO #TempUnusedIndexes 
FROM sys.dm_db_index_usage_stats s 
INNER JOIN sys.indexes i ON s.[object_id] = i.[object_id] 
AND s.index_id = i.index_id 
INNER JOIN sys.objects o ON i.object_id = O.object_id 
WHERE 1=2 
EXEC sp_MSForEachDB 'USE [?]; INSERT INTO #TempUnusedIndexes 
SELECT TOP 20 
DB_NAME() AS DatabaseName 
, SCHEMA_NAME(o.Schema_ID) AS SchemaName 
, OBJECT_NAME(s.[object_id]) AS TableName 
, i.name AS IndexName 
, s.user_updates 
, s.system_seeks + s.system_scans + s.system_lookups 
AS [System usage] 
FROM sys.dm_db_index_usage_stats s 
INNER JOIN sys.indexes i ON s.[object_id] = i.[object_id] 
AND s.index_id = i.index_id 
INNER JOIN sys.objects o ON i.object_id = O.object_id 
WHERE s.database_id = DB_ID() 
AND OBJECTPROPERTY(s.[object_id], ''IsMsShipped'') = 0 
AND s.user_seeks = 0 
AND s.user_scans = 0 
AND s.user_lookups = 0 
AND i.name IS NOT NULL 
ORDER BY s.user_updates DESC' 
SELECT TOP 20 * FROM #TempUnusedIndexes ORDER BY [user_updates] DESC 
DROP TABLE #TempUnusedIndexes