覆盖索引和大数据量的讨论

me_child 2015-12-02 03:43:57

RT，近来遇到项目上有部分表有千万级的数据。导致在查询时经常超时。

现有的调优知识告诉我非聚集索引在大数据量上只有"覆盖索引"是查询最快的。于是我针对部分耗时的SQL,存储过程等建立了覆盖索引，并在查询中直接指定索引：select xxx from xxx (with(index)xxxxxx) ，性能提升很明显，

1. 于是我在想，能否对所有耗时的查询都用这种覆盖索引来优化？（当然我不会在ntext,text这种字段上建索引，也建不了。因为索引对字段有长度限制。）

2. 对于这种千万级，亿级的数据量如何做优化才是最终解决方案？

3.后来我发现一个更加奇怪的问题，一个存储过程在刚建立的时候速度很快，大概3秒左右，过几天后aspx页上显示超时，用profile跟踪后看到duration要30几秒，但直接把profile里面的执行存储过程的语句拿到SSMS里面执行又只要3秒，再刷页面，依然超时，然后更奇怪的是把这个存储过程alter一下什么都不改，速度又上去了，页面也不超时了， 2个语句一莫一样，参数也一样，各位有这样的经历没有？

...全文

375 18 打赏收藏转发到动态举报

写回复

用AI写文章

18 条回复

切换为时间正序

请发表友善的回复…

发表回复

LongRui888 2015-12-04

打赏
举报

总之，这个先想办法解决，然后再搜集信息，分析本质

LongRui888 2015-12-04

打赏
举报

1. 于是我在想，能否对所有耗时的查询都用这种覆盖索引来优化？（当然我不会在ntext,text这种字段上建索引，也建不了。因为索引对字段有长度限制。）当然可以用覆盖索引，而且对于不需要排序的字段，直接用include就可以。 2. 对于这种千万级，亿级的数据量如何做优化才是最终解决方案？如果你指的是查询的优化，不考虑其他的方面，那就多建点索引，如果查询不需要实时，可以查询昨天之前的数据，可以考虑结存一些表，这样的好处是减少表的关联，进一步提高速度，因为表关联会消耗一些性能。我看了你们上面的讨论，每天增加几万条数据，比如5w条吧，一个月就是150万条，新增数据量不是很大，索引多了会减慢插入数据的速度，但是应该不会特别慢。 3.后来我发现一个更加奇怪的问题，一个存储过程在刚建立的时候速度很快，大概3秒左右，过几天后aspx页上显示超时，用profile跟踪后看到duration要30几秒，但直接把profile里面的执行存储过程的语句拿到SSMS里面执行又只要3秒，再刷页面，依然超时，然后更奇怪的是把这个存储过程alter一下什么都不改，速度又上去了，页面也不超时了， 2个语句一莫一样，参数也一样，各位有这样的经历没有？这个不好判断是什么原因导致的，但这个至少说明在参数完全一样的情况下，2次执行一次慢，一次快，一般都是因为执行计划不稳定导致的，而执行计划不稳定，除了像统计信息之列的变化，还有像 ssms中对于一些基本参数的设置是否相同，比如： ansi_nulls，ansi_padding等，这个在dmv里，你也可以看到。另外，你要排除如阻塞导致的问题，以及其他瞬时的sql占用了大量的系统资源，导致你的sql在1秒前执行很快，突然就慢了。就是用排除法，你要问微软，他也不会告诉你这个是什么原因导致的，不过他们可能会用一些内部的工具，来抓取一些内核的信息来分析。。。

me_child 2015-12-04

打赏
举报

引用 14 楼 kk185800961 的回复:

[quote=引用 13 楼 me_child 的回复:] [quote=引用 12 楼 kk185800961 的回复:] [quote=引用 9 楼 me_child 的回复:] [quote=引用 7 楼 kk185800961 的回复:] 1. 可以用，只是有字段总字节长度的限制 2.首先是分库或者分区，剩下的就是怎么优化每条sql语句了，业务上也尽量重复读写 3.有可能是执行计划不一样。虽然传的值是一样的，看楼主怎么执行了，固定值可能又重新成才新的执行计划了。

第二条，同一张表的查询逻辑都不一样的，条件有时间，有ID，有人，有code 不好分区和分库的吧，不然效率不是更低？第三条，我也怀疑aspx页面发送的执行计划和我直接执行的不一样，但我没办法看到profile里面的计划，有办法知道aspx页面具体的执行计划吗？这样问题就清楚了 [/quote] 上面我说错了一点：业务上尽量减少重复读写！对于大表，最好的就是分，分成小的数据，水平或垂直分库分表。若无法实现，考虑读写分离吧，当然会有延迟，OLTP的系统就不要读写分离了。再不行，就分区多一些吧。第三点，意思就是sniff现象。因为带参数的存储过程执行计划是以第一次编译为准的，若存储过程内部没有再把参数复制给其他参数，楼主可以在aspx慢的时候，试着在执行存储过程时的后加上 WITH RECOMPILE 让其重新编译，如果快了，就是这问题了。 [/quote] 各种查询条件的表也适合做分区表吗？有些人说如果查询条件无规律反而表分区会降低性能对于第三点，我很肯定决不会以第一次编译为准，原因很简单，第一次编译后很快，页面也很快，过个几天之后才慢，这期间没人修改过。 [/quote] 大数据考虑的就是划分更小来处理。分区的话不好说，像我们是每个用户都有一个字段标识为他的数据，按用户分区，该用户所有表都是在同一个分区。使用分区，可以并行处理。还可以设置当某个操作记录达五六千行的时候，行锁/键锁不会升级为表锁而升级为分区锁，这样不影响其他分区操作。 3.你这几天数据变化大吗？慢的时候如果能改程序，建议再调用存储过程加WITH RECOMPILE 看看。或者干脆把该存储过程的缓存计划全部找出来清除掉：

SELECT * FROM sys.syscacheobjects WHERE sql LIKE '%EXEC PROCNAME%'

SELECT * FROM sys.dm_exec_cached_plans A 
CROSS APPLY sys.dm_exec_sql_text(plan_handle) B
WHERE B.text LIKE '%EXEC PROCNAME%'

DBCC FREEPROCCACHE(plan_handle)

[/quote] 第二条，我们没有明显的数据划分界限，比如有时候需要按人查，查这个用户下面的数据，有时候要按日期查不管人，有时候要按区域查不管人和时间就是说这些条件都是由自己自己选择的，这种情况怎么分区？分区条件是啥？不好分吧。第三条。数据变化多大叫大？目前的增长率是每天多几万条而已，不能改程序，也不要改，因为根本问题我们没有找到，不能把头埋到地里对吧，另外清除缓存计划更是不可行的招。因为这种重新编译没区别，更不可能每隔几天就上去清一次，根本原因还是没有找到

吉普赛的歌 2015-12-04

打赏
举报

引用楼主 me_child 的回复:

RT，近来遇到项目上有部分表有千万级的数据。导致在查询时经常超时。现有的调优知识告诉我非聚集索引在大数据量上只有"覆盖索引"是查询最快的。于是我针对部分耗时的SQL,存储过程等建立了覆盖索引，并在查询中直接指定索引：select xxx from xxx (with(index)xxxxxx) ，性能提升很明显， 1. 于是我在想，能否对所有耗时的查询都用这种覆盖索引来优化？（当然我不会在ntext,text这种字段上建索引，也建不了。因为索引对字段有长度限制。） 2. 对于这种千万级，亿级的数据量如何做优化才是最终解决方案？ 3.后来我发现一个更加奇怪的问题，一个存储过程在刚建立的时候速度很快，大概3秒左右，过几天后aspx页上显示超时，用profile跟踪后看到duration要30几秒，但直接把profile里面的执行存储过程的语句拿到SSMS里面执行又只要3秒，再刷页面，依然超时，然后更奇怪的是把这个存储过程alter一下什么都不改，速度又上去了，页面也不超时了， 2个语句一莫一样，参数也一样，各位有这样的经历没有？

1. 覆盖索引当然好，但表过大时，索引也随之变得巨大，占用空间不可小视，带来数据库的备份、压缩等变得困难；此外，索引过多过大，会引起增、删、改效率的下降，如果表的dml操作频繁，需要慎重；索引会因dml操作产生碎片，必须定期重建维护，否则有索引和没索引没有区别。 2. 观察生产环境执行慢的、频繁的sql ，只对执行频繁、执行慢的sql进行优化。所有的sql都加索引那还不如不加。而有的查询, 加索引和不加索引没有区别（表过小、查询记录占表总记录百分比过多……）。

IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].Proc_DBA_GetSlowSQL_ByCPU') AND type in (N'P', N'PC'))
	DROP PROCEDURE [dbo].Proc_DBA_GetSlowSQL_ByCPU
GO
-- =============================================
-- Author:		yenange
-- Create date: 2015-08-18
-- Description:	取得最近执行占用CPU时间最长的SQL语句
-- =============================================
CREATE PROCEDURE dbo.Proc_DBA_GetSlowSQL_ByCPU
	@topNum INT = 50						--取前多少条记录
	,@lastExecutionTime DATETIME = NULL		--最后执行时间, 默认为NULL. 为 NULL 则忽略此条件; 不为NULL 则取大于此参数之后执行的
	,@excludeNight BIT=1					--是否 排除晚上的信息? 默认=1 排除。选择是=1，后面两参数才有意义
	,@nightBegin CHAR(5)='22:00'			--"晚上"开始时间点, 默认 22:00
	,@nightEnd CHAR(5)='06:00'				--"晚上"结束时间点, 默认 06:30
	,@containSQL NVARCHAR(300)=NULL			--语句中包含的SQL. 默认为NULL. 为NULL则忽略此条件; 不为NULL则取包含此参数的记录
AS
BEGIN
	SET NOCOUNT ON
	DECLARE @nightBegin_num INT
	DECLARE @nightEnd_num INT
	SET @nightBegin_num=CAST( replace(@nightBegin,':','') AS INT)
	SET @nightEnd_num=CAST( replace(@nightEnd,':','') AS INT)
	
	SET ROWCOUNT @topNum
	SELECT 
		st.text AS SQL_Full										--父级完整语句
		,SUBSTRING(st.text, (qs.statement_start_offset/2) + 1,
			((CASE statement_end_offset 
			WHEN -1 THEN DATALENGTH(st.text)
			ELSE qs.statement_end_offset END 
			- qs.statement_start_offset)/2) + 1) as SQL_Part	--统计对应的部分语句
		, CAST( ((qs.total_elapsed_time / 1000000.0)/qs.execution_count) AS DECIMAL(28,2) ) AS [平均消耗秒数]
		, CAST(qs.last_elapsed_time / 1000000.0 AS DECIMAL(28, 2)) AS [最后完成消耗秒数]
		, qs.last_execution_time AS [最后执行时间]
		, CAST(qs.min_elapsed_time / 1000000.0 AS DECIMAL(28, 2)) AS [最小消耗秒数]
		, CAST(qs.max_elapsed_time / 1000000.0 AS DECIMAL(28, 2)) AS [最大消耗秒数]
		, CAST(qs.total_elapsed_time / 1000000.0 AS DECIMAL(28, 2)) AS [总消耗秒数]
		, (qs.execution_count) AS [总执行次数]
		, creation_time AS [编译计划的时间]
		, CAST(qs.last_worker_time / 1000000.0 AS DECIMAL(28, 2)) AS [最后完成占用CPU秒数]
		, sql_handle,statement_start_offset,statement_end_offset
		,plan_generation_num,plan_handle,creation_time
		,last_execution_time,execution_count
		,total_worker_time,last_worker_time,min_worker_time,max_worker_time
		,total_physical_reads,last_physical_reads,min_physical_reads,max_physical_reads,total_logical_writes,last_logical_writes,min_logical_writes,max_logical_writes,total_logical_reads,last_logical_reads,min_logical_reads,max_logical_reads
		,total_clr_time,last_clr_time,min_clr_time,max_clr_time
    from sys.dm_exec_query_stats qs
        CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) AS st
	WHERE
		--1. 最后执行时间 
		(@lastExecutionTime IS NULL OR qs.last_execution_time>=@lastExecutionTime)
		--2. 包含 SQL 条件 
		AND
		(@containSQL IS NULL OR st.[text] LIKE '%'+@containSQL+'%')
		AND
		--3. 是否排除晚上
		(
			@excludeNight=0 
			OR
			(
				--2.1 开始大于结束 22:00->6:30 [ x >=6:30(end) and x<22:00(begin) ]
				@nightBegin_num>@nightEnd_num AND
				(
					CAST( replace(CONVERT(varchar(5),last_execution_time,108),':','') AS INT)>=@nightEnd_num
					AND
					CAST( replace(CONVERT(varchar(5),last_execution_time,108),':','') AS INT)<@nightBegin_num
				)
			)
			OR
			(
				--2.2 开始小于结束 1:00->6:30 [ x>=0:00 and x<1:00 or x>=6:30 and x<24:00 ]
				@nightBegin_num<@nightEnd_num AND
				(
					(
						CAST( replace(CONVERT(varchar(5),last_execution_time,108),':','') AS INT)>=0
						AND
						CAST( replace(CONVERT(varchar(5),last_execution_time,108),':','') AS INT)<@nightBegin_num
					)
					OR
					(
						CAST( replace(CONVERT(varchar(5),last_execution_time,108),':','') AS INT)>=@nightEnd_num
						AND
						CAST( replace(CONVERT(varchar(5),last_execution_time,108),':','') AS INT)<2400
					)
				)		
			)
		)
	ORDER BY qs.last_worker_time DESC
	
	SET NOCOUNT OFF
	SET ROWCOUNT 0
END
GO
EXEC sys.sp_addextendedproperty 
	@name=N'Version', @value=N'1.1' , 
	@level0type=N'SCHEMA',@level0name=N'dbo', 
	@level1type=N'PROCEDURE',@level1name=N'Proc_DBA_GetSlowSQL_ByCPU'

3. 这个很可能是参数嗅探。如果这个存储过程执行不是特别频繁（重编译主要使cpu变高），直接在存储过程后面加：WITH RECOMPILE

CREATE PROCEDURE dbo.Sample_Procedure 
    @param1 int = 0,
    @param2 int  
WITH RECOMPILE
AS
    SELECT @param1,@param2 
RETURN 0

如果频繁，而且压力较大，可以参考： http://www.cnblogs.com/lyhabc/archive/2013/03/02/2941144.html 简单来说，你可以直接指定索引或者Plan Guide，使执行计划按走你想要的方式。

薛定谔的DBA 2015-12-03

打赏
举报

引用 9 楼 me_child 的回复:

[quote=引用 7 楼 kk185800961 的回复:] 1. 可以用，只是有字段总字节长度的限制 2.首先是分库或者分区，剩下的就是怎么优化每条sql语句了，业务上也尽量重复读写 3.有可能是执行计划不一样。虽然传的值是一样的，看楼主怎么执行了，固定值可能又重新成才新的执行计划了。

me_child 2015-12-03

打赏
举报

引用 10 楼 me_child 的回复:

[quote=引用 8 楼 szx1999 的回复:] 问题3是个明显的sniff现象，sp内部重新定义参数转接一下即可。

感谢！不瞒你说我也考虑过是参数的sniff现象，但是我针对DMV中抓到的缓存计划做了优化来屏蔽sniff现象：把有可能会导致sniff的SQL用动态SQL来执行，但过几天还是慢了，话说避免sniff的不外乎3招吧： 1.重新定义变量。 2.动态SQL 3.子查询我理解的有问题没？[/quote] 第3点说错是子存储过程查询

me_child 2015-12-03

打赏
举报

引用 8 楼 szx1999 的回复:

问题3是个明显的sniff现象，sp内部重新定义参数转接一下即可。

感谢！不瞒你说我也考虑过是参数的sniff现象，但是我针对DMV中抓到的缓存计划做了优化来屏蔽sniff现象：把有可能会导致sniff的SQL用动态SQL来执行，但过几天还是慢了，话说避免sniff的不外乎3招吧： 1.重新定义变量。 2.动态SQL 3.子查询我理解的有问题没？

me_child 2015-12-03

打赏
举报

引用 7 楼 kk185800961 的回复:

1. 可以用，只是有字段总字节长度的限制 2.首先是分库或者分区，剩下的就是怎么优化每条sql语句了，业务上也尽量重复读写 3.有可能是执行计划不一样。虽然传的值是一样的，看楼主怎么执行了，固定值可能又重新成才新的执行计划了。

第二条，同一张表的查询逻辑都不一样的，条件有时间，有ID，有人，有code 不好分区和分库的吧，不然效率不是更低？第三条，我也怀疑aspx页面发送的执行计划和我直接执行的不一样，但我没办法看到profile里面的计划，有办法知道aspx页面具体的执行计划吗？这样问题就清楚了

等不到来世 2015-12-03

打赏
举报

问题3是个明显的sniff现象，sp内部重新定义参数转接一下即可。

薛定谔的DBA 2015-12-03

打赏
举报

引用 13 楼 me_child 的回复:

[quote=引用 12 楼 kk185800961 的回复:] [quote=引用 9 楼 me_child 的回复:] [quote=引用 7 楼 kk185800961 的回复:] 1. 可以用，只是有字段总字节长度的限制 2.首先是分库或者分区，剩下的就是怎么优化每条sql语句了，业务上也尽量重复读写 3.有可能是执行计划不一样。虽然传的值是一样的，看楼主怎么执行了，固定值可能又重新成才新的执行计划了。

SELECT * FROM sys.syscacheobjects WHERE sql LIKE '%EXEC PROCNAME%'

SELECT * FROM sys.dm_exec_cached_plans A 
CROSS APPLY sys.dm_exec_sql_text(plan_handle) B
WHERE B.text LIKE '%EXEC PROCNAME%'

DBCC FREEPROCCACHE(plan_handle)

me_child 2015-12-03

打赏
举报

引用 12 楼 kk185800961 的回复:

[quote=引用 9 楼 me_child 的回复:] [quote=引用 7 楼 kk185800961 的回复:] 1. 可以用，只是有字段总字节长度的限制 2.首先是分库或者分区，剩下的就是怎么优化每条sql语句了，业务上也尽量重复读写 3.有可能是执行计划不一样。虽然传的值是一样的，看楼主怎么执行了，固定值可能又重新成才新的执行计划了。

薛定谔的DBA 2015-12-02