PowerShell分析SQL Server待創(chuàng)建索引的字段與已有索引之間的相關(guān)性

本文介紹如何從表的層面去分析一個(gè)待創(chuàng)建的索引與已有索引之間的相關(guān)性

簡(jiǎn)要分析創(chuàng)建索引考慮哪些方面：

1、相關(guān)sql語句

2、表

3、性能的影響

步驟：

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

2、結(jié)合表的實(shí)際情況(已有索引、數(shù)據(jù)密度、熱點(diǎn)表類型等等)，確定索引的字段以及是否適合創(chuàng)建

3、如果適合創(chuàng)建，分析、對(duì)比創(chuàng)建前后的性能差異

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

讓PowerShell代碼實(shí)現(xiàn)類似于某DBA(我自己)的思路去分析第2點(diǎn)的 數(shù)據(jù)密度和已有索引

1、數(shù)據(jù)密度

數(shù)據(jù)密度是指鍵值惟一的記錄條數(shù)分之一，即：數(shù)據(jù)密度=1/鍵值惟一的記錄數(shù)量，當(dāng)數(shù)據(jù)密度越小，即鍵值惟一性越高時(shí)，代表該字段越適合建立索引。

PowerShell實(shí)現(xiàn)字段按數(shù)據(jù)密度從小到大排序：

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

}

測(cè)試結(jié)果：

排序前：sku,shipmentID,PackageNo,AsnNo

排序后：PackageNo,shipmentID,AsnNo,sku

2、索引分析

待建索引的字段對(duì)比已有索引，從字段及相應(yīng)順序進(jìn)行分析比較，判斷已有索引與待建索引的共同字段

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 '\(-\)',''

##索引字段拆分成數(shù)組

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)

}

##對(duì)比兩個(gè)索引的字段個(gè)數(shù)

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

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

##記錄兩個(gè)索引相同的字段個(gè)數(shù)

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

}

##記錄兩個(gè)索引的匹配位置

$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

}

##記錄兩個(gè)索引的匹配位置

$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 "待創(chuàng)建索引的字段：$in_index"

echo "涉及相關(guān)字段的索引："

foreach($a in $db_indexes_used_arr){

echo $a

}

echo "無關(guān)的索引："

foreach($b in $db_indexes_unused_arr){

echo $b

}

}

測(cè)試結(jié)果：

注：待建索引是尚未創(chuàng)建的索引，字段順序是可調(diào)整的

調(diào)用代碼：

$server=''    ##實(shí)例

$db_name=''   ##數(shù)據(jù)庫(kù)

$table_name=''   ##表

$in_index='' ##索引字段，多個(gè)字段以逗號(hào)間隔

$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

從測(cè)試的結(jié)果來看，成功判斷出與待建索引相關(guān)的已有索引，再也不用去數(shù)據(jù)庫(kù)里面查詢和自己判斷了(sp_helpindex table_name)，要當(dāng)一個(gè)會(huì)"偷懶"的DBA