前两天腾出点时间，打算整理一下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
程序的调用部分
需要具体咨询脚本问题的，可以加群。
