POSTGRESQL 自動搜索所有邏輯庫中的無用索引自動化腳本實現

開頭還是介紹一下羣，如果感興趣 polardb ,mongodb ,mysql ,postgresql ,redis 等有問題，有需求都可以加羣羣內有各大數據庫行業大咖，CTO，可以解決你的問題。加羣請聯繫 liuaustin3 ，在新加的朋友會分到 2 羣（共 600 人左右 1 + 2）。

前兩天騰出點時間，打算整理一下 POSTGRESQL 公司的數據庫的無用的索引的問題，寫了一個 SQL 通過 SQL 來獲取這些數據庫的無用索引，但頭疼的是，我們整個 TEAM 到來的時候，很多壞習慣已經養成了，所以我們目前就是在一個治標的過程，看上去我們的工作有點，“幼稚”，但誰讓那些開發的部分必須讓我們先改變他們的幼稚。

然後我們的一個同事，剛剛，發現了工作中的難點，並進行了超級改進，將整體的工作自動化，而且還是一個成本很低的方案，SHELL ，我知道，說起 SHELL 很多人不屑，現在都是 PYTHON, GO 的天下，誰還用 SHELL。下面我就展示一下這個 SHELL 的功底，以及設計結構。

POSTGRESQL 的基礎，這裏是幾十個 POSTGRESQL 的實例，每個實例下面有不固定的數據庫，每個數據庫有幾百張表，同時每張表裏面有衆多的沒有被使用過的索引。

結果如下，會根據每個命令的執行時間，以及數據庫的名字建立文件夾，然後開始針對每一個數據庫進行掃描，併產生無用索引的記錄以及清理和回滾的語句。

下圖信息字符已經替換或更改

整體的工作量大幅度削減。

下面是整體的數據庫中獲取無用索引的層次圖

整體的 SHELL 的設計中，大致的結構如下圖

SELECT s.schemaname,
       s.relname AS tablename,
       s.indexrelname AS indexname,
       s.idx_scan,
       s.idx_tup_fetch,
       x.indexdef || ';' as index_create_statement,
       pg_size_pretty(pg_relation_size(s.indexrelid)) AS index_size,
       'drop index' || s.indexrelname || 'on' || s.relname || ';' as del_statement
FROM pg_catalog.pg_stat_user_indexes s
JOIN pg_catalog.pg_index i ON s.indexrelid = i.indexrelid
JOIN pg_indexes as x on x.indexname = s.indexrelname
WHERE s.idx_scan = 0
   and s.idx_tup_fetch = 0
   AND 0 <> ALL(i.indkey)
   AND NOT i.indisunique
   AND NOT EXISTS (SELECT 1
          FROM pg_catalog.pg_constraint c
         WHERE c.conindid = s.indexrelid)
   AND NOT EXISTS (SELECT 1
          FROM pg_catalog.pg_inherits AS inh
         WHERE inh.inhrelid = s.indexrelid)
ORDER BY pg_relation_size(s.indexrelid) DESC;

——————————————————————————————
#!/bin/bash
hosts=/data/pg_batch_script/hosts_cy
xjsql=/data/pg_batch_script/pgindex.sql
dt=$(date '+%Y%m%d')
xudir=/data/pg_batch_script/${dt}pgindex
if [! -d $xudir]; then
    mkdir -p $xudir
fi
log=/data/pg_batch_script/${dt}pgindex/${dt}.log
#sqlFile=$3
function getDbs(){
local res=$(echo $sql |psql -h $host -p $port -U $user -d $defaultDb -At -v -w)
echo $res
}
function getTime(){
local res=$(date '+%Y%m%d%H:%M:%S')
echo $res
}
function printLog(){
local res="$(getTime)\t$*"
echo -e $res
echo -e $res >> $log
}
function getDbInfo(){
#echo enter getDbInfo:$1
host=$(jq ".${1}.host" $hosts | sed 's/"//g')
#echo host:$host
port=$(jq ".${1}.port" $hosts)
user=$(jq ".${1}.user" $hosts | sed 's/"//g')
pass=$(jq ".${1}.pass" $hosts | sed 's/"//g')
defaultDb=$(jq ".${1}.defaultdb" $hosts | sed 's/"//g')
export PGPASSWORD=$pass
#echo getDbInfo:$host,$port,$user,$pass
}
function getSingleDefault(){
   instanceName=$1
   getDbInfo $instanceName
   psql -h $host -p $port -U $user -d $defaultDb -w   -f $xjsql > ${xudir}/${instanceName}${defaultDb}.log
   printLog $instanceName $defaultDb $res
}
function getAllInstances(){
function getDbs(){
local sql="select datname from pg_database where datname not like'test%'and datname not like'backup%'and datname not in ('template0','template1','template2','postgres','template_db','cy7SaasCenter','cy7SaasCenterTest','cy7server','tcposroot','rdsadmin') order by datname;"
local res=$(echo $sql |psql -h $host -p $port -U $user -d $defaultDb -At -v -w)
echo $res
}
function getTime(){
local res=$(date '+%Y%m%d_%H:%M:%S')
echo $res
}
function printLog(){
local res="$(getTime)\t$*"
echo -e $res
echo -e $res >> $log
}
function getDbInfo(){
#echo enter getDbInfo:$1
host=$(jq ".${1}.host" $hosts | sed 's/"//g')
#echo host:$host
port=$(jq ".${1}.port" $hosts)
user=$(jq ".${1}.user" $hosts | sed 's/"//g')
pass=$(jq ".${1}.pass" $hosts | sed 's/"//g')
defaultDb=$(jq ".${1}.defaultdb" $hosts | sed 's/"//g')
export PGPASSWORD=$pass
#echo getDbInfo:$host,$port,$user,$pass
}
function getSingleDefault(){
   instanceName=$1
   getDbInfo $instanceName
   psql -h $host -p $port -U $user -d $defaultDb -w   -f $xjsql > ${xudir}/${instanceName}_${defaultDb}.log
   printLog $instanceName $defaultDb $res
}
function getAllDbs(){
instanceName=$1
getDbInfo $instanceName
local dbs=$(getDbs)
for db in $dbs
do

程序的調用部分

本文由 Readfog 進行 AMP 轉碼，版權歸原作者所有。
來源：https://mp.weixin.qq.com/s/rVMafZGy1n4iSHd_QMFGYg

猜你喜歡