Shulou(Shulou.com)06/01 Report--

This paper introduces how to analyze the correlation between an index to be created and an existing index from the table level.

Briefly analyze what aspects to consider in creating an index:

1. Related sql statements

2. Table

3. The influence of performance

Steps:

1. Analyze the fields extracted from the table and index from the relevant SQL statements

2. Combined with the actual situation of the table (existing indexes, data density, hot table types, etc.), determine the fields of the index and whether it is suitable to create

3. If it is suitable for creation, analyze and compare the performance differences before and after creation

(inadequacies, make up by your own brain):

Let the PowerShell code implement an idea similar to that of a DBA (myself) to analyze the data density of point 2 and the existing index

1. Data density

Data density refers to the number of records with unique key value, that is, data density = 1 / number of records with unique key value. When the data density is smaller, that is, the uniqueness of key value is higher, it means that the field is more suitable for indexing.

PowerShell implements fields to be sorted by data density from small to large:

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 ($naughty list.murf 1-ge 0-1) {

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

If ($n-gt 0) {

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

Else {

$str+=$column_arr [$num]

}

}

Return $str

}

Test results:

Before sorting: sku,shipmentID,PackageNo,AsnNo

After sorting: PackageNo,shipmentID,AsnNo,sku

2. Index analysis

Compare the fields of the index to be built with the existing indexes, analyze and compare the fields and the corresponding order, and judge the common fields of the existing index and the index to be built.

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 / o / n / n-lt $indexes1.length;$n=$n+1) {

$index2_same_arr=@ ()

$index1_same_arr=@ ()

$new_arr=@ ()

$same_arr=@ ()

$str=''

# # removing spaces and (-) between index fields

$idx_1_tmp=$indexes1 [$n] .index _ keys-replace',''

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

# # splitting Index Fields into arrays

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)

}

# # compare the number of fields in the two indexes

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

For ($x / x / s / x-lt $in_index_arr.length;$x=$x+1) {

# # record the number of fields with the same index

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

}

# # record the matching position of two indexes

$index2_same_arr+=$x

$index1_same_arr+=$xx

}

}

}

}

Else {

For ($yaw0witch / 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

}

# # record the matching position of two indexes

$index1_same_arr+=$y

$index2_same_arr+=$yy

}

}

}

}

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

# # get the matching fields in order

For ($zong0witz-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

# # Index fields to be built minus matched fields

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

$new_index=$new_arr+$diff_arr

# # reorganization of Index Fields to be built

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: $table_name"

Echo "Fields to be indexed: $in_index"

Echo "Index involving related fields:"

Foreach ($an in $db_indexes_used_arr) {

Echo $a

}

Echo "extraneous index:"

Foreach ($b in $db_indexes_unused_arr) {

Echo $b

}

}

Test results:

Note: the index to be built is an index that has not yet been created, and the order of fields is adjustable.

Call code:

$server='' # # instance

$db_name='' # # Database

$table_name='' # # Table

$in_index='' # # Index field, multiple fields separated by commas

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

Echo "before sorting: $in_index"after sorting: $sort_index"

Index_analysis $server $db_name $table_name $sort_index

From the test results, we can successfully determine the existing indexes related to the index to be built, and we no longer have to query and sp_helpindex table_name in the database, but to be a "lazy" DBA.

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.