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