當前位置：首頁 > 编程资源 > 编程问答 >内容正文

编程问答

漫游测试之性能测试(5.3-索引分析)

發布時間：2023/12/8 编程问答 30 豆豆

生活随笔收集整理的這篇文章主要介紹了漫游测试之性能测试(5.3-索引分析) 小編覺得挺不錯的,現在分享給大家,幫大家做個參考.

11.1.自動索引分析

優點：數據庫引擎調整顧問，是用來分析已有索引的有效性并且為SQL的索引建立提供參考意見。

缺點：只能針對查詢語句起作用。

步驟一：啟動數據庫引擎調整顧問

步驟二：設置配置選項

步驟三：自動分析中

步驟四：生成的建議

11.1.索引理論

11.1.1什么是索引

類似詞典查找，詞典是按照字母順序排列的一個不同單詞的列表，數據被排序，但是仍然可能存在重復，通常稱之為聚集索引。

類似書籍查找，以不同的關鍵字所對應的頁數那樣進行排列，通常稱之為非聚集索引。

堆表，以行標識符作為指向存儲位置的指針，這中間沒有數據順序，也不能進行搜索，只能逐行遍歷數據來進行查找，這一過程被定義為掃描。

11.1.2.優化器默認的最佳數據訪問機制

11.1.3.判斷某列是否適合索引的Sql

select?COUNT?(DISTINCT?待查看的列名)?as?"重復的行數",

???????COUNT?(待查看的列名)?as?"所有的行數",

???????(CAST?(?Count(Distinct?待查看的列名)?as?DECIMAL?)?/?CAST(Count(待查看的列名)??as?DECIMAL?))?as?"索引選擇率"

???????from?待查看的表名

11.1.4.聚簇索引與非聚簇索引的使用原則

聚簇索引：

a、在創建任何非聚簇索引鍵之前應建立聚簇索引。

b、聚簇索引總體長度應最小，故應考慮窄索引。

c、檢索一定范圍的數據使用聚簇索引。

d、讀取預先排序的數據，使用聚簇索引[即order by 的字段]。

何時不使用聚簇索引：

頻繁更新的列。

聚簇索引頻繁更新，將導致所有非聚簇索引的行定位器相應更新，從而顯著增加相關查詢的開銷，并將阻塞這段時間引用相同部分的非聚簇索引的其它查詢，從而影響數據庫的并行性。

寬的關鍵字。

太多并行的順序插入。

非聚簇索引：

非聚簇索引在需要從一個大表上讀取少量的行時最有效。

頻繁更新的列更適合非聚簇索引。

寬的關鍵字的列更適合非聚簇索引。

11.1.5.?非聚簇索引－－覆蓋索引

覆蓋索引是在所有為滿足SQL查詢不用到達基本表所需的列上建立的非聚簇索引。如果查詢遇到一個索引并且完全不需要引用底層數據表，那么該索引可以被認為是覆蓋索引。[不懂]

覆蓋索引對減少查詢的邏輯讀次數是一種有用的技術。覆蓋索引對請求一定范圍的行、或者排序輸出的查詢能夠得到比聚簇索引更好的性能。

create?nonclustered?index?[IX_覆蓋索引的名字]?on?表名?(字段?desc/asc)?include?(字段)

覆蓋索引能夠幫助解決阻塞和死鎖的問題。

11.1.6.?非聚簇索引－－過濾索引

過濾索引是使用過濾器的非聚簇索引，這個過濾器基本上是一個Where子句，用來在可能沒有很好選擇性的一個或多個列上創建一個高選擇性的關鍵字組。例如，一個具有大量null值的列可能被存儲為稀疏列來降低這些null值的開銷，在這個列添加一個過濾索引將使你擁有在不是null的數據上的索引。

create?nonclustered?index?[IX_覆蓋索引的名字]?on?表名?(字段?desc/asc)?include?(字段)??where?條件

11.1.7. 丟失索引的SQL語法

待補充

11.1.8. 書簽查找的定義

查詢所引用的其他行不在非聚簇索引中，要讀取這些列的值，必須通過聚簇索引從非聚簇索引行導航到對應的數據行，這個操作就是一個書簽查找。如果查詢的各部分（不只是選擇列表）中引用的列不都包含在使用的非聚簇索引中，就會發生書簽查找操作。

缺點：書簽查找增加了查詢邏輯讀操作的次數。并且如果頁面不在內存中，書簽查找可能需要在磁盤上的一個隨機I/O操作來從索引頁面跳轉到數據頁面，還需要必需的CPU能力來匯集這一數據并執行必要的操作。這一特性使得書簽查找的數據檢索操作的開銷相當的大。這個開銷因素是非聚簇索引更適合于返回較小的行集的原因。隨著查詢檢索的行數的增加，書簽查找的開銷將變得無法接受。

關鍵字查詢－〉右鍵－〉Output List property(輸出列表屬性)-〉單擊省略號，就可以查看到“不在非聚簇索引中的所需列”的內容。

11.1.9. 書簽查找的解決方法

a)、使用一個聚簇索引

即將當前引用到的列改為聚簇索引。

b)、使用一個覆蓋索引

b.1將查詢引用中的其它列添加到非聚簇索引中，構建成一個覆蓋索引。

b.2另一個達到覆蓋索引的方法不需要添加關鍵字列，而是使用包含列，如下所示：

create unique nonclustered index [索引名稱] on 列名 (字段) include (其它字段) with drop_existing

c)、使用索引連接

11.2.0. 索引列上的統計應保持自動更新

索引的有效性完全取決于索引列的統計，沒有統計，Sql Server基于開銷的查詢優化器不能決定使用一個索引的最有效方式，為了符合這一要求，Sql Server自動創建索引鍵的統計，不管索引是何時建立的。

查看自動統計是否開啟：

屬性－〉選項－〉數據庫自動更新統計設置（ Auto Update Statistics setting of a database ）

關閉/開啟自動更新統計功能：

alter database 數據庫名 set auto_update_statistics off/on

11.2.1. 索引碎片分析查看的手段

當插入或者更新表中的數據時，表的對應聚簇索引和受影響的聚簇索引被修改，如果對索引的修改不能容納于同一頁面中（即8KB頁），可能導致索引葉子頁面分割。一個新的葉子頁面將被添加以包含原來頁面的部分，并且維持索引鍵中行的邏輯順序，雖然新的葉子頁面維護原始頁面中行的邏輯順序，但是這個新的頁面通常在磁盤上不與原來頁面相鄰。

內部碎片和外部碎片對數據檢索性能都有負面的影響。外部碎片導致磁盤上的索引頁面不連續，新的葉子頁面和原始葉子頁面離得很遠，物理順序與邏輯順序不同。

可以使用以下SQL進行查詢：

select??s.avg_fragmentation_in_percent?as?'表示索引和堆的邏輯平均碎片百分比'?,

????????s.fragment_count?as?'碎片的數量',

????????s.page_count?as?'組成統計的索引或數據頁面數量的計數',

????????s.avg_page_space_used_in_percent??as?'索引頁面中分配的空間量',

????????s.record_count??as?'統計代表的記錄數',

????????avg_record_size_in_bytes??as?'在索引或堆記錄中存儲的數據量'

from?sys.dm_db_index_physical_stats?(DB_ID('數據庫'),OBJECT_ID(N'表名'),null,null,'Sampled')?as?s

s.avg_fragmentation_in_percent：

1.平均碎片小于10-20%，碎片不成問題。

2.碎片在20-40%，碎片可能成為問題，但是可以通過索引重組來消除索引碎片。

3.碎片>40%，需要索引重建。

11.2.2. 自動化維護索引碎片

１.確定當前數據庫中所有需要分析碎片的用戶表。

２.確定所有用戶表和索引的碎片。

３.考慮以下因素以確定需要進行碎片整理的用戶表和索引。

　高的碎片水平－－avg_fragmentation_in_percent大于%

　不是非常小的表/索引－－也就是page_count大于的

腳本解釋：

１.遍歷系統上所有數據庫并確認符合碎片條件的每個數據庫中用戶表上的索引，并將它們保存到一個臨時表中。

２.根據碎片水平，重新整理碎片較少的索引并重建碎片很多的索引。

/* 執行存儲過程*/

use?AdventureWorks?

execute?IndexDefrag

create?procedure?IndexDefrag?as

declare?@DBName?nvarchar(255),

????????@TableName?nvarchar(255),

????????@SchemaName?nvarchar(255),

????????@IndexName?nvarchar(255),

????????@PctFrag?DECIMAL

DECLARE?@Defrag?nvarchar(max)

if?exists(select?*?from?sys.objects?where?object_id?=object_id?(N'#Frag'))

???Drop?Table?#Frag

???

create?table?#Frag

(?DBName?nvarchar(255),

??TableName?nvarchar(255),

??SchemaName?nvarchar(255),

??IndexName?nvarchar(255),

??AvgFragment?DECIMAL)

exec?sp_MSforeachdb?'insert into #Frag(

??DBName,

??TableName,

??SchemaName,

??IndexName,

??AvgFragment

) select ''?'' as DBName,

??t.Name as TableName,

??sc.Name as SchemaName,

??i.name as IndexName,

??s.avg_fragmentation_in_percent

from ?.sys.dm_db_index_physical_stats(DB_ID(''?''),null,null,null,''Sampled'') as s

?join ?.sys.indexes i

?on s.Object_Id=i.Object_id

?and s.Index_Id=i.Index_id

?join ?.sys.tables t

?on i.Object_id=t.Object_id

?join ?.sys.schemas sc

?on t.schema_id=sc.SCHEMA_ID

where s.avg_fragmentation_in_percent>20

and t.TYPE=''U''

and s.page_count>8

order by TableName,IndexName '

declare?cList?Cursor?for?select?*?from?#Frag

open?cList

FETCH?NEXT?From?cList

into?@DBName,@TableName,@SchemaName,@IndexName,@PctFrag

while?@@FETCH_STATUS?=0

BEGIN?

if?@PctFrag?Between?20.0 and?40.0 –-比例可以自己調整

begin

??set?@Defrag=N'ALTER INDEX '+@IndexName?+' ON '+@DBName?+'.'?+@SchemaName?+'.'+@TableName?+' REORGANIZE '

??exec?sp_executesql?@Defrag

??PRINT?'該索引碎片率在%~40%之間，修改索引：Reorganize index:'+@DBName+'.'+@SchemaName+'.'+@TableName+'.'+@IndexName

END

ELSE?if?@PctFrag?>40.0 –-比例可以自己調整

begin?

set?@Defrag=N'ALTER INDEX '?+@IndexName+' ON '+@DBName?+'.'+@SchemaName?+'.'+@TableName?+' REBUILD '

EXEC?sp_executesql?@Defrag

Print?'該索引碎片率在大于%之間，重建索引：Reuild index:'+@DBName?+'.'+@SchemaName+'.'+@TableName+'.'+@IndexName

End?

FETCH?Next?from?cList