PowerShell分析SQL Server待建立索引的欄位與已有索引之間的相關性

標籤：powershell   mssql   索引分析   

本文介紹如何從表的層面去分析一個待建立的索引與已有索引之間的相關性

簡要分析建立索引考慮哪些方面：

1、相關sql語句

2、表

3、效能的影響

步驟：

1、從相關的SQL語句中分析提取表及索引的欄位

2、結合表的實際情況(已有索引、資料密度、熱點表類型等等)，確定索引的欄位以及是否適合建立

3、如果適合建立，分析、對比建立前後的效能差異

(不足之處，自行腦補650) this.width=650;" src="http://img.baidu.com/hi/jx2/j_0004.gif" alt="j_0004.gif" />)：

讓PowerShell代碼實作類別似於某DBA(我自己650) this.width=650;" src="http://img.baidu.com/hi/jx2/j_0019.gif" alt="j_0019.gif" />)的思路去分析第2點的 資料密度和已有索引

1、資料密度

資料密度是指索引值惟一的記錄條數分之一，即：資料密度=1/索引值惟一的記錄數量，當資料密度越小，即索引值惟一性越高時，代表該欄位越適合建立索引。

PowerShell實現欄位按資料密度從小到大排序：

function sort_index_columns($server,$db_name,$table_name,$in_index){

if($in_index.Contains(‘,‘)){

$in_index_arr=$in_index.split(‘,‘)

}

else{

[email protected]($in_index)

}

[email protected]()

[email protected]()

foreach($column in $in_index_arr){

$indexes1=invoke-sqlcmd "use $db_name;select count(distinct $column) as count from $table_name with(nolock)" -ServerInstance $server

$column_arr+=$column

$density_arr+=$indexes1.count

}

$list=$density_arr | Sort-Object

$str=‘‘

for($n=$list.length-1;$n -ge 0;$n=$n-1){

$num=$density_arr.indexof([int]$list[$n])

if($n -gt 0){

$str+=$column_arr[$num]+‘,‘}

else{

$str+=$column_arr[$num]

}

}

return $str

}

測試結果：

650) this.width=650;" src="https://s3.51cto.com/oss/201710/26/f4e318749631fd7f1ba3a79b70ed3ad6.png-wh_500x0-wm_3-wmp_4-s_3614710700.png" title="排序.PNG" alt="f4e318749631fd7f1ba3a79b70ed3ad6.png-wh_" />

排序前：sku,shipmentID,PackageNo,AsnNo

排序後：PackageNo,shipmentID,AsnNo,sku

2、索引分析

待建索引的欄位對比已有索引，從欄位及相應順序進行分析比較，判斷已有索引與待建索引的共同欄位

function index_analysis($server,$db_name,$table_name,$in_index){

if($in_index.Contains(‘,‘)){

$in_index_arr=$in_index.split(‘,‘)

}

else{

[email protected]($in_index)

}

[email protected]()

[email protected]()

[email protected]()

$indexes1=invoke-sqlcmd "use $db_name;Exec sp_helpindex $table_name" -ServerInstance $server

for($n=0;$n -lt $indexes1.length;$n=$n+1){

[email protected]()

[email protected]()

[email protected]()

[email protected]()

$str=‘‘

##去掉索引欄位之間的空格及(-)

$idx_1_tmp=$indexes1[$n].index_keys -replace ‘ ‘,‘‘

$idx_1=$idx_1_tmp -replace ‘\(-\)‘,‘‘

##索引欄位拆分成數組

if($idx_1.Contains(‘,‘)){

$idx_1_arr=$idx_1.split(‘,‘)

$idx_1_size=$idx_1_arr.length

}

else{

$idx_1_size=1;

[email protected]($idx_1)

}

##對比兩個索引的欄位個數

if($idx_1_size -gt $in_index_arr.length){

for($x=0;$x -lt $in_index_arr.length;$x=$x+1){

##記錄兩個索引相同的欄位個數

for($xx=0;$xx -lt $idx_1_size;$xx+=1){

if($in_index_arr[$x] -eq $idx_1_arr[$xx]){

if($x -eq $xx){

$same_arr+=$x

}

##記錄兩個索引的匹配位置

$index2_same_arr+=$x

$index1_same_arr+=$xx

}

}

}

}

else{

for($y=0;$y -lt $idx_1_size;$y=$y+1){

for($yy=0;$yy -lt $in_index_arr.length;$yy+=1){

if($idx_1_arr[$y] -eq $in_index_arr[$yy]){

if($y -eq $yy){

$same_arr+=$y

}

##記錄兩個索引的匹配位置

$index1_same_arr+=$y

$index2_same_arr+=$yy

}

}

}

}

if($index1_same_arr[0] -eq 0){

##按順序取匹配到的欄位

for($z=0;$z -lt $index1_same_arr.length;$z++){

if($z -eq $index1_same_arr[$z]){

$new_arr+=$in_index_arr[$index2_same_arr[$z]]

$count=$z

}

}

$db_indexes_serial_arr+=$count+1

##待建索引欄位減去已匹配欄位

$diff_arr=Compare-Object -ReferenceObject $in_index_arr -DifferenceObject $new_arr |Select-Object -ExpandProperty InputObject

$new_index=$new_arr+$diff_arr

##待建索引欄位重組

for($zz=0;$zz -lt $new_index.length;$zz++){

if($zz -lt $new_index.length-1){

$str+=$new_index[$zz]+‘,‘

}

else{

$str+=$new_index[$zz]

}

}

$db_indexes_used_arr+="$($indexes1[$n].index_name)($idx_1)"

}else{

$db_indexes_serial_arr+=0

$db_indexes_unused_arr+="$($indexes1[$n].index_name)($idx_1)"

}

}

echo "表：$table_name"

echo "待建立索引的欄位：$in_index"

echo "涉及相關欄位的索引："

foreach($a in $db_indexes_used_arr){

echo $a

}

echo "無關的索引："

foreach($b in $db_indexes_unused_arr){

echo $b

}

}

測試結果：

650) this.width=650;" src="https://s1.51cto.com/oss/201710/26/cc47902f3dd1c199fcfe1d88259ad747.png-wh_500x0-wm_3-wmp_4-s_2381678252.png" title="PowerShell索引對比.PNG" alt="cc47902f3dd1c199fcfe1d88259ad747.png-wh_" />

註：待建索引是尚未建立的索引，欄位順序是可調整的

調用代碼：

$server=‘‘    ##執行個體

$db_name=‘‘   ##資料庫

$table_name=‘‘   ##表

$in_index=‘‘ ##索引欄位，多個欄位以逗號間隔

$sort_index=sort_index_columns $server $db_name $table_name $in_index

echo "排序前：$in_index" "排序後：$sort_index"

index_analysis $server $db_name $table_name $sort_index

從測試的結果來看，成功判斷出與待建索引相關的已有索引，再也不用去資料庫裡面查詢和自己判斷了(sp_helpindex table_name)，要當一個會"偷懶"的DBA650) this.width=650;" src="http://img.baidu.com/hi/jx2/j_0003.gif" alt="j_0003.gif" />

本文出自 “aimax” 部落格，請務必保留此出處http://aimax.blog.51cto.com/11610508/1976456

PowerShell分析SQL Server待建立索引的欄位與已有索引之間的相關性