温馨提示×

温馨提示×

您好,登录后才能下订单哦!

密码登录×
登录注册×
其他方式登录
点击 登录注册 即表示同意《亿速云用户服务条款》

PowerShell分析SQL Server待创建索引的字段与已有索引之间的相关性

发布时间:2020-07-07 18:55:55 来源:网络 阅读:1194 作者:易语随风去 栏目:关系型数据库

本文介绍如何从表的层面去分析一个待创建的索引与已有索引之间的相关性

简要分析创建索引考虑哪些方面:

1、相关sql语句

2、表

3、性能的影响

步骤:

1、从相关的SQL语句中分析提取表及索引的字段

2、结合表的实际情况(已有索引、数据密度、热点表类型等等),确定索引的字段以及是否适合创建

3、如果适合创建,分析、对比创建前后的性能差异

(不足之处,自行脑补PowerShell分析SQL Server待创建索引的字段与已有索引之间的相关性):


让PowerShell代码实现类似于某DBA(我自己PowerShell分析SQL Server待创建索引的字段与已有索引之间的相关性)的思路去分析第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

}

测试结果:

PowerShell分析SQL Server待创建索引的字段与已有索引之间的相关性

排序前: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

}

}

测试结果:

PowerShell分析SQL Server待创建索引的字段与已有索引之间的相关性

注:待建索引是尚未创建的索引,字段顺序是可调整的


调用代码:

$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),要当一个会"偷懒"的DBAPowerShell分析SQL Server待创建索引的字段与已有索引之间的相关性

向AI问一下细节

免责声明:本站发布的内容(图片、视频和文字)以原创、转载和分享为主,文章观点不代表本网站立场,如果涉及侵权请联系站长邮箱:is@yisu.com进行举报,并提供相关证据,一经查实,将立刻删除涉嫌侵权内容。

AI