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

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个语句一莫一样,参数也一样,各位有这样的经历没有?
...全文
314 18 点赞 打赏 收藏 举报
写回复
18 条回复
切换为时间正序
当前发帖距今超过3年,不再开放新的回复
发表回复
总之,这个先想办法解决,然后再搜集信息,分析本质
  • 打赏
  • 举报
回复
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.有可能是执行计划不一样。虽然传的值是一样的,看楼主怎么执行了,固定值可能又重新成才新的执行计划了。
第二条,同一张表的查询逻辑都不一样的, 条件有时间,有ID,有人,有code 不好分区和分库的吧, 不然效率不是更低? 第三条,我也怀疑aspx页面发送的执行计划和我直接执行的不一样, 但我没办法看到profile里面的计划, 有办法知道aspx页面具体的执行计划吗? 这样问题就清楚了 [/quote] 上面我说错了一点:业务上尽量减少重复读写! 对于大表,最好的就是分,分成小的数据,水平或垂直 分库分表。若无法实现,考虑读写分离吧,当然会有延迟,OLTP的系统就不要读写分离了。再不行,就分区多一些吧。 第三点,意思就是sniff现象。因为带参数的存储过程执行计划是以第一次编译为准的,若存储过程内部没有再把参数复制给其他参数,楼主可以在aspx慢的时候,试着在执行存储过程时的后加上 WITH RECOMPILE 让其重新编译,如果快了,就是这问题了。
  • 打赏
  • 举报
回复
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.有可能是执行计划不一样。虽然传的值是一样的,看楼主怎么执行了,固定值可能又重新成才新的执行计划了。
第二条,同一张表的查询逻辑都不一样的, 条件有时间,有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)
  • 打赏
  • 举报
回复
me_child 2015-12-03
引用 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] 各种查询条件的表也适合做分区表吗?有些人说如果查询条件无规律反而表分区会降低性能 对于第三点,我很肯定 决不会以第一次编译为准 , 原因很简单, 第一次编译后很快, 页面也很快, 过个几天之后才慢, 这期间没人修改过。
  • 打赏
  • 举报
回复
薛定谔的DBA 2015-12-02
1. 可以用,只是有字段总字节长度的限制 2.首先是分库或者分区,剩下的就是怎么优化每条sql语句了,业务上也尽量重复读写 3.有可能是执行计划不一样。虽然传的值是一样的,看楼主怎么执行了,固定值可能又重新成才新的执行计划了。
  • 打赏
  • 举报
回复
me_child 2015-12-02
引用 5 楼 xdashewan 的回复:
数据量巨大可以2008前考虑分区分表,2008后可以用压缩
等一下, 即时是统计信息过期, 为啥profile里面执行的耗时和ssms里面执行的耗时相差20几秒? 同样的存储过程 同样的参数, 如果统计信息过时应该是一样慢才对吧。
  • 打赏
  • 举报
回复
xdashewan 2015-12-02
数据量巨大可以2008前考虑分区分表,2008后可以用压缩
  • 打赏
  • 举报
回复
me_child 2015-12-02
引用 3 楼 xdashewan 的回复:
推荐你看黄版的一篇文章http://blog.csdn.net/dba_huangzj/article/details/8702351
感谢, 其他的问题呢, come on, say something!
  • 打赏
  • 举报
回复
xdashewan 2015-12-02
推荐你看黄版的一篇文章http://blog.csdn.net/dba_huangzj/article/details/8702351
  • 打赏
  • 举报
回复
me_child 2015-12-02
引用 1 楼 xdashewan 的回复:
目测是统计信息过期
good idea, 之前有遇到过一个超慢的SQL 跟新统计信息后秒查的, 有什么办法可以确认是否因为这条导致的吗?
  • 打赏
  • 举报
回复
xdashewan 2015-12-02
目测是统计信息过期
  • 打赏
  • 举报
回复
相关推荐
发帖
疑难问题
创建于2007-09-28

2.1w+

社区成员

MS-SQL Server 疑难问题
申请成为版主
帖子事件
创建了帖子
2015-12-02 03:43
社区公告
暂无公告