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