PowerShell分析SQL Server待創建索引的字段與已有索引之間的相關性

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

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

1、相關sql語句

2、表

3、性能的影響

步驟：

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

2、結合表的實際情況(已有索引、數據密度、熱點表類型等等)，確定索引的字段以及是否適合創建

3、如果適合創建，分析、對比創建前后的性能差異

(不足之處，自行腦補)：

讓PowerShell代碼實現類似于某DBA(我自己)的思路去分析第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{

$in_index_arr=@($in_index)

}

$column_arr=@()

$density_arr=@()

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

}

測試結果：

排序前：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{

$in_index_arr=@($in_index)

}

$db_indexes_used_arr=@()

$db_indexes_unused_arr=@()

$db_indexes_serial_arr=@()

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

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

$index2_same_arr=@()

$index1_same_arr=@()

$new_arr=@()

$same_arr=@()

$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;

$idx_1_arr=@($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

}

}

測試結果：

注：待建索引是尚未創建的索引，字段順序是可調整的

調用代碼：

$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)，要當一個會"偷懶"的DBA