SqlServer 索引和执行计划

SET STATISTICS PROFILE ON

无论是聚集索引还是非聚集索引都是B树结构

SELECT * From Person WHERE Name = 'aaa'
表扫描，顾名思义就是整张表扫描，表没有创建索引

在设置主键时，SQL Server会默认在主键列创建聚集索引
主键最好是自增的，聚集索引的最佳数据类型，smallint、int、bigint、datetme

一张表只能够有一个聚集索引，因为一张表只能够按一种方式排序

如果表上有聚集索引，则非聚集索引中存储着聚集索引的引用，然后通过利用聚集索引来获取数据。
因此这就是为什么返回少量数据的时候使用非聚集索引的性能较好，
而返回大量的数据的时候使用非聚集索引还不如直接全表扫描来得快。
如果表上没有聚集索引的时候，则引用行号。

唯一索引与唯一约束始终一同存在，删除唯一索引必须删除唯一约束
只有当唯一索引列不为NULL的时候才检测重复值。

CREATE UNIQUE CLUSTERED INDEX IX_Id ON Person(Id)
添加了聚集索引之后，表就由堆表变成了聚集表，聚集表的数据存在于聚集索引的叶级节点。

聚集索引扫描：与表扫描其实差别不大
SELECT * From Person WHERE Name = 'aaa'

聚集索引查找：扫描聚集索引中特定范围的行
SELECT * From Person WHERE id =10

CREATE NONCLUSTERED INDEX IX_Name ON Person(Name) 
索引扫描：整体扫描非聚集索引。
SELECT Name FROM Person

聚合函数(如COUNT(),MAX())都会有流聚合的出现，但是其不会消耗IO，只有消耗CPU

Group by的子句，因为需要数据按照group by 后面的列有序，就需要Sort来保证排序。注意,Sort操作是占用内存的操作，当内存不足时还会去占用tempdb

多表连接：SQL Server会采用三类不同的连接方式：循环嵌套连接，合并连接，散列连接

嵌套循环
当两个Join的表外部输入结果集比较小，而内部输入所查找的表非常大时，查询优化器更倾向于选择循环嵌套方式

合并连接
合并连接是从每个表仅仅执行一次访问。从这个原理来看，合并连接要比循环嵌套要快了不少，但需要数据有序。

哈希连接
散列连接同样仅仅只需要只访问1次双方的数据。散列连接通过在内存中建立散列表实现。这比较消耗内存，如果内存不足还会占用tempdb。但并不像合并连接那样需要双方有序

源网址：

https://www.cnblogs.com/qixuejia/p/4046086.html https://www.cnblogs.com/qixuejia/p/7821836.html

SQL Server 重新组织生成索引脚本： https://www.cnblogs.com/chenmh/p/4384525.html

SET NOCOUNT ON
DECLARE @Objectid INT, @Indexid INT,@schemaname VARCHAR(100),@tablename VARCHAR(300),@ixname VARCHAR(500),@avg_fip float,@command VARCHAR(4000)
DECLARE IX_Cursor CURSOR FOR
SELECT  A.object_id,A.index_id,QUOTENAME(SS.NAME) AS schemaname,QUOTENAME(OBJECT_NAME(B.object_id,B.database_id))as tablename ,QUOTENAME(A.name) AS ixname,
B.avg_fragmentation_in_percent AS avg_fip FROM sys.indexes A inner join  sys.dm_db_index_physical_stats(DB_ID(),NULL,NULL,NULL,'LIMITED') AS B 
ON A.object_id=B.object_id and A.index_id=B.index_id 
INNER JOIN SYS.OBJECTS OS ON A.object_id=OS.object_id
INNER JOIN sys.schemas SS ON OS.schema_id=SS.schema_id
WHERE B.avg_fragmentation_in_percent>10 and B.page_count>20    AND A.index_id>0 AND A.IS_DISABLED<>1
--AND OS.name='book'
ORDER BY tablename,ixname
OPEN IX_Cursor
FETCH NEXT FROM IX_Cursor INTO @Objectid,@Indexid,@schemaname,@tablename,@ixname,@avg_fip
WHILE @@FETCH_STATUS=0
BEGIN

    IF @avg_fip<30.0
    SET @command=N'ALTER  INDEX '+@ixname+N' ON '+@schemaname+N'.'+ @tablename+N' REORGANIZE ';
    IF @avg_fip>=30.0 AND @Indexid=1
        BEGIN
        IF EXISTS (SELECT * FROM SYS.columns WHERE OBJECT_ID=@Objectid AND max_length in(-1,16))
        SET @command=N'ALTER  INDEX '+@ixname+N' ON '+@schemaname+N'.'+ @tablename+N' REBUILD ';
        ELSE
        SET @command=N'ALTER  INDEX '+@ixname+N' ON '+@schemaname+N'.'+ @tablename+N' REBUILD '+N' WITH (ONLINE = ON)';
        END
    IF @avg_fip>=30.0 AND @Indexid>1
        BEGIN    
        IF EXISTS (SELECT * FROM  SYS.index_columns IC INNER JOIN SYS.columns CS ON CS.OBJECT_ID=IC.OBJECT_ID AND CS.column_id=IC.column_id
                   WHERE  IC.OBJECT_ID=@Objectid AND IC.index_id=@Indexid AND CS.max_length in(-1,16) )
        SET @command=N'ALTER  INDEX '+@ixname+N' ON '+@schemaname+N'.'+ @tablename+N' REBUILD ';
        ELSE
        SET @command=N'ALTER  INDEX '+@ixname+N' ON '+@schemaname+N'.'+ @tablename+N' REBUILD '+N' WITH (ONLINE = ON)';
        END
    --PRINT @command
    EXEC(@command)

 FETCH NEXT FROM IX_Cursor INTO @Objectid,@Indexid,@schemaname,@tablename,@ixname,@avg_fip
END

CLOSE IX_Cursor
DEALLOCATE IX_Cursor

索引优化： https://www.cnblogs.com/zhijianliutang/p/4224932.html

-- 缺失的索引
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(s.[object_id]) AS TableName 
, i.name AS IndexName 
, ROUND(s.avg_fragmentation_in_percent,2) AS [Fragmentation %] 
INTO #TempFragmentation 
FROM sys.dm_db_index_physical_stats(db_id(),null, null, null, null) 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 #TempFragmentation 
SELECT TOP 20 
DB_NAME() AS DatbaseName 
, SCHEMA_NAME(o.Schema_ID) AS SchemaName 
, OBJECT_NAME(s.[object_id]) AS TableName 
, i.name AS IndexName 
, ROUND(s.avg_fragmentation_in_percent,2) AS [Fragmentation %] 
FROM sys.dm_db_index_physical_stats(db_id(),null, null, null, null) 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 i.name IS NOT NULL 
AND OBJECTPROPERTY(s.[object_id], ''IsMsShipped'') = 0 
ORDER BY [Fragmentation %] DESC' 
SELECT top 20 * FROM #TempFragmentation ORDER BY [Fragmentation %] DESC 
DROP TABLE #TempFragmentation

-- 未使用的索引：
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


-- 查找过期的统计信息
DECLARE
@day_before int = 30;SELECT 

Object_name = OBJECT_NAME(object_id)
,Stats_Name = [name]
,Stats_Last_Updated = STATS_DATE([object_id], [stats_id])
FROM sys.stats WITH(NOLOCK)
WHERE STATS_DATE([object_id], [stats_id]) <= DATEADD(day, -@day_before, getdate())
order by Stats_Last_Updated desc;

-- 手动更新统计信息
USE test01
GO
--update statistcis for a specify statistic
UPDATE STATISTICS dbo.Rose PK_Rose;
GO

--update statistcis for a specify table
UPDATE STATISTICS dbo.Rose WITH FULLSCAN;
GO

-- update statistcis for a specify database
USE test01
GO
EXEC sys.sp_updatestats
GO

-- 更新实例级别统计信息

USE master
GO

DECLARE
@sql NVARCHAR(MAX)
;

SET
@sql = N'
USE [?]
IF ''?'' NOT IN(''master'', ''model'', ''msdb'', ''tempdb'', ''distribution'')
BEGIN
RAISERROR(N''--------------------------------------------------------------
Search on database: ?'', 10, 1) WITH NOWAIT
EXEC sys.sp_updatestats
END
'
;

EXEC SYS.SP_MSFOREACHDB @sql,@replacechar=N'?'

在天堂 - 技术老男 博主简介

数据库技术点滴累积，个人运维经验分享

在天堂 - 技术老男 ›

SqlServer ›
SqlServer 索引和执行计划

SET STATISTICS PROFILE ON

您还没有登录，请您注册 登录后发表评论。

SET STATISTICS PROFILE ON

您还没有登录，请您 注册 登录后发表评论。

您还没有登录，请您注册登录后发表评论。